מצא שדות מרובים של נתונים עם Excel VLOOKUP

על ידי שילוב של פונקציית VLOOKUP של Excel עם הפונקציה COLUMN אנו יכולים ליצור נוסחת חיפוש המאפשרת לך להחזיר ערכים מרובים משורה אחת של מסד נתונים או טבלת נתונים.

בדוגמה המוצגת בתמונה למעלה, נוסחת החיפוש מקלה על החזרת כל הערכים - כגון מחיר, מספר חלק וספק - הקשורים לחומרי חומרה שונים.

01 מתוך 10

החזרת מספר ערכים עם Excel VLOOKUP

בצע את השלבים המפורטים להלן יוצר את נוסחת החיפוש בתמונה לעיל כי יחזיר ערכים מרובים מרשומת נתונים אחת.

נוסחת החיפוש דורשת שהפונקציה COLUMN תהיה מקוננת בתוך VLOOKUP.

קיצור פונקציה כרוך בהזנת הפונקציה השנייה כאחת הארגומנטים עבור הפונקציה הראשונה.

במדריך זה, הפונקציה COLUMN תזין כארגומנט מספר עמודה עבור VLOOKUP.

השלב האחרון במדריך כולל העתקה של נוסחת החיפוש לעמודים נוספים על מנת לאחזר ערכים נוספים עבור החלק הנבחר.

תוכן העניינים

• הזנת נתוני המדריך
• יצירת טווח בשם טבלת הנתונים
• הפעלת פונקציית VLOOKUP
• הזנת ארגומנט ערך בדיקה באמצעות הפניות Cell מוחלטים
• הזנת ארגומנט מערך הטבלה
• הזנת פונקציית COLUMN מקוננת
• השלמת פונקציית VLOOKUP
• העתקת נוסחת חיפוש עם ידית מילוי
• אחזור נתונים עם פורמולה

02 מתוך 10

הזן את נתוני המדריך

השלב הראשון במדריך הוא להזין את הנתונים לתוך גיליון עבודה של Excel.

כדי לבצע את השלבים במדריך, הזן את הנתונים שמופיעים בתמונה למעלה בתאים הבאים.

• הזן את טווח הנתונים העליון לתאים D1 ל- G1
• הזן את הטווח השני לתוך התאים D4 ל G10

קריטריוני החיפוש ונוסחת החיפוש שנוצרו במהלך הדרכה זו יוכנסו לשורה 2 של גליון העבודה.

ערכת הלימוד אינה כוללת את העיצוב שמופיע בתמונה, אך פעולה זו לא תשפיע על האופן שבו פועלת נוסחת החיפוש.

מידע על אפשרויות עיצוב הדומות לאלה שצוין לעיל זמין זה Basic Basic עיצוב Excel .

שלבי הדרכה

1. הזן את הנתונים כפי שנראה בתמונה לעיל לתוך תאים D1 ל G10

03 מתוך 10

יצירת טווח בשם טבלת הנתונים

טווח בעל שם הוא דרך קלה להתייחס למגוון נתונים בנוסחה. במקום להקליד את הפניות התא עבור הנתונים, אתה יכול פשוט להקליד את שם הטווח.

יתרון נוסף לשימוש בטווח שם הוא שהפניות התא עבור טווח זה לעולם לא ישתנו גם כאשר הנוסחה מועתקת לתאים אחרים בגליון העבודה.

שמות טווח הם, אם כן, חלופה לשימוש התייחסות מוחלטת התא כדי למנוע שגיאות בעת העתקת נוסחאות.

הערה: שם הטווח אינו כולל את הכותרות או שמות השדות של הנתונים (שורה 4), אלא רק את הנתונים עצמם.

שלבי הדרכה

1. הדגשת תאים D5 ל G10 בגליון העבודה כדי לבחור אותם
2. לחץ על שם התיבה הממוקם מעל עמודה A
3. הקלד "טבלה" (ללא הצעות מחיר) בתיבה שם
4. הקש על המקש ENTER במקלדת
5. תאים D5 ל G10 עכשיו יש שם טווח של "טבלה". אנו נשתמש בשם עבור ארגומנט מערך הטבלה VLOOKUP במועד מאוחר יותר במדריך

04 מתוך 10

פתיחת תיבת הדו-שיח VLOOKUP

למרות שניתן פשוט להקליד את הנוסחה בדיקת ישירות לתוך תא בגליון עבודה, אנשים רבים מתקשים לשמור על תחביר ישר - במיוחד עבור נוסחה מורכבת כמו זו אנו משתמשים במדריך זה.

חלופה, במקרה זה, היא להשתמש בתיבת הדו שיח VLOOKUP. כמעט לכל הפונקציות של Excel יש תיבת דו-שיח המאפשרת לך להזין כל אחד מארגומנטים של הפונקציה בשורה נפרדת.

שלבי הדרכה

1. לחץ על תא E2 של גליון העבודה - המיקום שבו התוצאות של הנוסחה דו מימדי החיפוש יוצגו
2. לחץ על הכרטיסייה נוסחאות של הסרט
3. לחץ על האפשרות Lookup & Reference בסרט כדי לפתוח את הרשימה הנפתחת של הפונקציות
4. לחץ על VLOOKUP ברשימה כדי לפתוח את תיבת הדו שיח של הפונקציה

05 מתוך 10

הזנת ארגומנט ערך בדיקה באמצעות הפניות Cell מוחלטים

בדרך כלל, ערך החיפוש תואם לשדה נתונים בעמודה הראשונה של טבלת הנתונים.

בדוגמה שלנו, ערך החיפוש מתייחס לשם החלק של החומרה שעליו אנו רוצים למצוא מידע.

סוגי הנתונים המותרים עבור ערך החיפוש הם:

• נתוני טקסט
• ערך לוגי (TRUE או FALSE בלבד)
• מספר
• התייחסות לתא בגליון העבודה

בדוגמה זו, אנו ניכנס את התא התייחסות למקום שבו שם החלק יהיה ממוקם - תא D2.

הפניות מוחלטים Cell

בשלב מאוחר יותר במדריך, אנו להעתיק את הנוסחה בדיקת בתא E2 לתאים F2 ו G2.

בדרך כלל, כאשר נוסחאות מועתקות ב- Excel, הפניות לתא משתנות כדי לשקף את המיקום החדש שלהן.

אם זה קורה, D2 - הפניה התא עבור ערך בדיקת - ישתנה כמו הנוסחה מועתקים יצירת שגיאות בתאים F2 ו- G2.

כדי למנוע את השגיאות, אנו להמיר את התא התייחסות D2 לתוך התייחסות התא המוחלט .

הפניות מוחלטות של התא אינן משתנות כאשר נוסחאות מועתקות.

הפניות מוחלטות של תאים נוצרות על ידי לחיצה על המקש F4 במקלדת. פעולה זו מוסיפה סימנים דולר סביב הפניה התא כגון $ D $ 2

שלבי הדרכה

1. לחץ על השורה lookup_value בתיבת הדו-שיח
2. לחץ על תא D2 כדי להוסיף התייחסות זו התא לקו lookup_value . זהו התא שבו נכתוב את שם החלק שעליו אנו מחפשים מידע
3. מבלי להזיז את נקודת ההכנסה, לחץ על המקש F4 במקלדת כדי להמיר את D2 לתוך הפניה המוחלטת של התא $ D $ 2
4. השאר את תיבת הדו-שיח פונקציה VLOOKUP פתוחה עבור השלב הבא במדריך

06 מתוך 10

הזנת ארגומנט מערך הטבלה

מערך הטבלה הוא טבלת הנתונים שהנוסחה לחיפוש מחפשת כדי למצוא את המידע הרצוי.

מערך הטבלה חייב לכלול לפחות שתי עמודות של נתונים .

• העמודה הראשונה מכילה את ארגומנט ערך החיפוש (השלב ​​הקודם במדריך)
• השני, וכל עמודות נוספות, יחפש על ידי נוסחת החיפוש כדי למצוא את המידע שאנו מציינים.

יש להזין את ארגומנט מערך הטבלה כטווח המכיל את הפניות התא עבור טבלת הנתונים או כשם טווח .

בדוגמה זו, נשתמש בשם טווח שנוצרו בשלב 3 של המדריך.

שלבי הדרכה

1. לחץ על שורת table_array בתיבת הדו-שיח
2. הקלד "Table" (ללא מרכאות) כדי להזין את שם הטווח עבור ארגומנט זה
3. השאר את תיבת הדו-שיח פונקציה VLOOKUP פתוחה עבור השלב הבא במדריך

07 מתוך 10

קינון הפונקציה COLUMN

בדרך כלל, VLOOKUP מחזיר נתונים מעמודה אחת בטבלת נתונים, ועמודה זו נקבעת על ידי ארגומנט מספר העמודה .

בדוגמה זו, עם זאת, יש לנו שלוש עמודות שאנחנו רוצים להחזיר נתונים, כך שאנחנו צריכים דרך לשנות בקלות את מספר העמודה טור מבלי לערוך את נוסחת החיפוש שלנו.

זה המקום שבו הפונקציה COLUMN נכנס פנימה על ידי הזנת אותו בתור הארגומנט מספר העמודה , זה ישתנה כמו הנוסחה בדיקת מועתקים D2 התא לתאים E2 ו F2 מאוחר יותר במדריך.

פונקציות קינון

הפונקציה COLUMN, אם כן, משמשת כארגומנט מספר עמודה של VLOOKUP.

זה נעשה על ידי קינון הפונקציה COLUMN בתוך VLOOKUP בשורה Col_index_num של תיבת הדו שיח.

הזנת פונקציה COLUMN ידנית

כאשר פונקציות הקינון, Excel אינו מאפשר לנו לפתוח את תיבת הדו-שיח של הפונקציה השנייה כדי להזין את הארגומנטים שלה.

הפונקציה COLUMN, ולכן, חייבת להיות מוזנת באופן ידני בשורה Col_index_num .

לפונקציה COLUMN יש רק ארגומנט אחד - הארגומנט Reference שהוא הפניה לתא.

בחירת ארגומנט הפונקציה של פונקציית COLUMN

הפונקציה של פונקציית COLUMN היא להחזיר את מספר העמודה שניתנה כארגומנט Reference .

במילים אחרות, הוא ממיר את המכתב טור למספר עם טור A להיות העמודה הראשונה, עמודה ב השני וכן הלאה.

מאז השדה הראשון של הנתונים שאנחנו רוצים חזר הוא המחיר של הפריט - אשר בעמודה 2 של טבלת הנתונים - אנו יכולים לבחור את הפניה התא עבור כל תא בעמודה B כמו ארגומנט הפניה כדי לקבל את מספר 2 עבור הארגומנט Col_index_num .

שלבי הדרכה

1. בתיבת הדו-שיח פונקציה VLOOKUP, לחץ על השורה Col_index_num
2. הקלד את העמודה שם פונקציה ואחריה סוגר פתוח פתוח " ( "
3. לחץ על תא B1 בגליון העבודה כדי להזין את הפניה לתא כארגומנט Reference
4. הקלד סוגר עגול סוגר " ) כדי להשלים את הפונקציה COLUMN
5. השאר את תיבת הדו-שיח פונקציה VLOOKUP פתוחה עבור השלב הבא במדריך

08 מתוך 10

הזנת טווח VLOOKUP בדיקת ארגומנט

הארגומנט Range_lookup של VLOOKUP הוא ערך לוגי (TRUE או FALSE בלבד) המציין אם ברצונך ש- VLOOKUP ימצא התאמה מדויקת או משוערת ל Lookup_value.

• אם TRUE או אם ארגומנט זה מושמט, מחזירה VLOOKUP התאמה מדויקת ל- Lookup_value, או, אם לא נמצאה התאמה מדויקת, מחזירה VLOOKUP את הערך הבא בגודלו. עבור הנוסחה לעשות זאת, הנתונים בעמודה הראשונה של Table_array חייב להיות מסודר בסדר עולה .
• אם FALSE, VLOOKUP ישתמש רק בהתאמה מדויקת ל Lookup_value. אם קיימים שני ערכים או יותר בעמודה הראשונה של Table_array שתואמת את ערך החיפוש, הערך הראשון שנמצא נמצא בשימוש. אם לא נמצאה התאמה מדויקת, תוצג שגיאה # N / A.

במדריך זה, מכיוון שאנו מחפשים מידע ספציפי על פריט חומרה מסוים, אנו נקבע ש- Range_lookup שווה ל- False .

שלבי הדרכה

1. לחץ על השורה Range_lookup בתיבת הדו-שיח
2. הקלד את המילה False בשורה זו כדי לציין שאנו רוצים ש- VLOOKUP יחזיר התאמה מדויקת לנתונים שאנו מחפשים
3. לחץ על אישור כדי להשלים את נוסחת החיפוש וסגירת תיבת הדו-שיח
4. מאז אנחנו עדיין לא נכנסו הקריטריונים בדיקת לתוך תא D2 # N / A שגיאה יהיו נוכחים בתא E2
5. שגיאה זו תתוקן כאשר נוסיף את קריטריוני החיפוש בשלב האחרון של ערכת הלימוד

09 מתוך 10

העתקת נוסחת חיפוש עם ידית מילוי

נוסחת החיפוש מיועדת לאחזר נתונים מעמודות מרובות של טבלת הנתונים בבת אחת.

לשם כך, נוסחת החיפוש חייבת להתגורר בכל השדות שמהם אנו רוצים מידע.

במדריך זה אנו רוצים שהיא תאחזר נתונים מעמודות 2, 3 ו -4 של טבלת הנתונים - כלומר המחיר, מספר החלק ושם הספק כאשר אנו מזינים שם חלק כ- Lookup_value.

מאז הנתונים הוא הניח דפוס קבוע בגליון העבודה , אנחנו יכולים להעתיק את הנוסחה בדיקת בתא E2 לתאים F2 ו G2.

כאשר הנוסחה מועתקת, Excel יעדכן את הפניה התאית היחסית בפונקציה COLUMN (B1) כדי לשקף את המיקום החדש של הנוסחה.

כמו כן, Excel אינו משנה את הפניה המוחלטת של התא $ D $ 2 ואת הטבלה שם טווח כמו הנוסחה מועתקת.

יש יותר מדרך אחת להעתיק נתונים ב- Excel, אבל כנראה הדרך הקלה ביותר היא באמצעות ידית מילוי .

שלבי הדרכה

1. לחץ על תא E2 - שם את נוסחת החיפוש נמצא - כדי להפוך אותו לתא פעיל
2. הצב את מצביע העכבר מעל הריבוע השחור בפינה הימנית התחתונה. המצביע ישתנה לסימן פלוס " + " - זוהי ידית המילוי
3. לחץ על לחצן העכבר השמאלי וגרור את ידית המילוי מעבר לתא G2
4. שחרר את לחצן העכבר ואת F3 התא צריך להכיל את הנוסחה דו מימדי בדיקת
5. אם נעשה כראוי, תאים F2 ו- G2 צריך עכשיו גם להכיל את # N / A שגיאה הקיימת בתא E2

10 מתוך 10

הזנת קריטריונים לחיפוש

לאחר הנוסחה בדיקת הועתקו לתאים הנדרשים ניתן להשתמש בו כדי לאחזר מידע מטבלת הנתונים.

לשם כך, הקלד את שם הפריט שברצונך לאחזר לתא Lookup_value (D2) ולחץ על המקש ENTER במקלדת.

בסיום, כל תא המכיל את נוסחת החיפוש צריך להכיל פיסת נתונים אחרת על פריט החומרה שאתה מחפש.

שלבי הדרכה

1. לחץ על תא D2 בגליון העבודה
2. הקלד Widget בתא D2 ולחץ על המקש ENTER במקלדת
3. המידע הבא צריך להיות מוצג בתאים E2 עד G2:
   • E2 - $ 14.76 - המחיר של יישומון
   • F2 - PN-98769 - מספר החלק עבור רכיב widget
   • G2 - יישומונים בע"מ - שם הספק עבור יישומונים
4. בדוק את הנוסחה VLOOKUP מערך נוסף על ידי הקלדת שם של חלקים אחרים לתוך התא D2 ותצפית על התוצאות בתאים E2 ל G2

אם הודעת שגיאה כגון #REF! מופיע בתאים E2, F2 או G2, רשימה זו של הודעות שגיאה VLOOKUP עשויה לסייע לך לקבוע היכן הבעיה נמצאת.