VLOOKUP (הפונקציה VLOOKUP)

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

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

בצורתה הפשוטה ביותר, הפונקציה VLOOKUP מציינת:

=VLOOKUP(מה שברצונך לחפש, היכן שברצונך לחפש אותו, מספר העמודה בטווח המכיל את הערך שיש להחזיר, החזר התאמה משוערת או התאמה מדויקת – המצוין כ- 1/TRUE או 0/FALSE).

הדפדפן שלך אינו תומך בווידאו. התקן את Microsoft Silverlight‏, Adobe Flash Player או Internet Explorer 9.

עצה: הסוד ל- VLOOKUP הוא ארגון הנתונים שלך כך שהערך שתחפש (Fruit) יהיה מימין לערך המוחזר (Amount) שברצונך למצוא.

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

תחביר

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])‎

לדוגמה:

=VLOOKUP(A2,A10:C20,2,TRUE)
‎=VLOOKUP("Fontana",B2:E7,2,FALSE)‎
=VLOOKUP(A2,'Client Details'! A:F,3,FALSE)

שם ארגומנט	תיאור
lookup_value (חובה)	הערך שברצונך לחפש לפיו. הערך שברצונך לחפש חייב להיות בעמודה הראשונה של טווח התאים שאתה מציין *table_array הארגומנט* . לדוגמה, אם *table-array* כולל את התאים B2:D7, lookup_value חייב להיות בעמודה B. *Lookup_value* יכול להיות ערך או הפניה לתא.
Table_array (חובה)	טווח התאים שבו VLOOKUP יחפש את *lookup_value* ואת הערך המוחזר. באפשרותך להשתמש בטווח בעל שם או בטבלה, ובאפשרותך להשתמש בשמות בארגומנט במקום בהפניות לתאים. העמודה הראשונה בטווח התאים חייבת להכיל *את lookup_value.* בנוסף, טווח התאים צריך לכלול את הערך המוחזר שברצונך למצוא. למד כיצד לבחור טווחים בגליון עבודה.
col_index_num (חובה)	מספר העמודה (החל מ- 1 עבור העמודה הימנית ביותר *table_array*) המכילה את הערך המוחזר.
range_lookup (אופציונלי)	ערך לוגי המציין אם ברצונך שהפונקציה VLOOKUP תאתר התאמה משוערת או התאמה מדויקת: התאמה משוערת - 1/TRUE מניח שהעמודה הראשונה בטבלה ממוינות בסדר מספרי או אלפביתי, ולאחר מכן היא תחפש את הערך הקרוב ביותר. זוהי שיטת ברירת המחדל אם אינך מציין שיטה. לדוגמה, =VLOOKUP(90,A1:B100,2,TRUE). התאמה מדויקת - 0/FALSE מחפש את הערך המדויק בעמודה הראשונה. לדוגמה, =VLOOKUP("Smith",A1:B100,2,FALSE).

כיצד להתחיל בעבודה

קיימות ארבע פיסות מידע הדרושות כדי לבנות את התחביר של VLOOKUP:

הערך שברצונך לחפש, הנקרא גם ערך בדיקת המידע.
הטווח שבו ממוקם ערך בדיקת המידע. זכור כי ערך בדיקת המידע חייב להימצא תמיד בעמודה הראשונה בטווח על מנת שהפונקציה VLOOKUP תפעל נכון. לדוגמה, אם ערך בדיקת המידע שלך נמצאת בתא C2, הטווח שלך אמור להתחיל ב- C.
מספר העמודה בטווח המכיל את הערך המוחזר. לדוגמה, אם תציין את B2:D11 כטווח, עליך לספור את B כעמודה הראשונה, כעמודה השניה וכן הלאה.
באופן אופציונלי, באפשרותך לציין ערך TRUE אם אתה מעוניין בהתאמה משוערת או ערך FALSE אם אתה מעוניין בהתאמה מדויקת של הערך המוחזר. אם לא תציין דבר, ערך ברירת המחדל יהיה תמיד TRUE או התאמה משוערת.

כעת חבר את כל מה שצוין לעיל באופן הבא:

=VLOOKUP(ערך בדיקת מידע, טווח המכיל את ערך בדיקת המידע, מספר העמודה בטווח המכיל את הערך המוחזר, התאמה משוערת (TRUE) או התאמה מדויקת (FALSE)).

דוגמאות

להלן כמה דוגמאות של VLOOKUP:

דוגמה 1

=VLOOKUP (B3,B2:E7,2,FALSE)

הפונקציה VLOOKUP חפשת את פונטנה בעמודה הראשונה (עמודה B) ב- table_array B2:E7, ומחזירה את Olivier מהעמודה השניה (עמודה C) של table_array. False מחזיר התאמה מדויקת.

דוגמה 2

=VLOOKUP (102,A2:C7,2,FALSE)

הפונקציה VLOOKUP מחפשת התאמה מדויקת (FALSE) של שם המשפחה עבור 102 (lookup_value) בעמודה השניה (עמודה B) בטווח A2:C7, ומחזירה את Fontana.

דוגמה 3

=IF(VLOOKUP(103,A1:E7,2,FALSE)="Souse","Located","Not found")

הפונקציה IF בודקת אם הפונקציה VLOOKUP מחזירה את Sousa כשם המשפחה של העובד המתאם ל- 103 (lookup_value) ב- A1:E7 (table_array). מאחר ושם המשפחה התואם ל- 103 הוא Leal, תנאי IF הוא False, והערך לא נמצא מוצג.

דוגמה 4

=INT(YEARFRAC(DATE(2014,6,30),VLOOKUP(105,A2:E7,5,FLASE),1))

הפונקציה VLOOKUP מחפש את תאריך הלידה של העובד המתאים ל- 109 (lookup_value) בטווח A2:E7 (table_array), ומחזירה את 04/03/1955. לאחר מכן, YEARFRAC מחסיר תאריך לידה זה מ- 2014/6/30 ומחזיר ערך, אשר מומר לאחר מכן על-ידי INY למספר השלם 59.

דוגמה 5

IF(ISNA(VLOOKUP(105,A2:E7,2,FLASE))=TRUE,"Employee not found",VLOOKUP(105,A2:E7,2,FALSE))

הפונקציה IF בודקת אם הפונקציה VLOOKUP מחזירה ערך עבור שם משפחה מעמודה B עבור 105 (lookup_value). אם הפונקציה VLOOKUP מוצאת שם משפחה, הפונקציה IF תציג את שם המשפחה, אחרת, הפונקציה IF מחזירה את הערך Employee not found. ISNA מבטיח שאם הפונקציה VLOOKUP מחזירה #N/A, השגיאה מוחלפת על-ידי העובד לא נמצא, במקום #N/A.

בדוגמה זו, הערך המוחזר הוא ברק, שהוא שם המשפחה התואם ל- 105.

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

גליון עבודה עם עמודות המשתמשות ב- VLOOKUP כדי לקבל נתונים מטבלאות אחרות

כאן, עמודות A-F ו- H כוללות ערכים או נוסחאות המשתמשים רק בערכים בגליון העבודה, ושאר העמודות משתמשות ב- VLOOKUP ובערכים של עמודה A (קוד לקוח) ועמודה B (Attorney) כדי לקבל נתונים מטבלאות אחרות.

העתק את הטבלה המכילה את השדות המשותפים לגליון עבודה חדש ותן לה שם.
לחץ על כלי > נתונים עבור > קשרי גומלין כדי לפתוח את תיבת הדו-שיח ניהול קשרי גומלין.
עבור כל קשר גומלין ברשימה, שים לב לפעולות הבאות:
- השדה המקשר בין הטבלאות (מופיע בסוגריים בתיבת הדו-שיח). זוהי הנוסחה lookup_value VLOOKUP שלך.
- שם טבלת בדיקת המידע הקשורה. זוהי הנוסחה table_array בנוסחת VLOOKUP.
- השדה (עמודה) בטבלת בדיקת המידע הקשורה המכילה את הנתונים הרצויים בעמודה החדשה. מידע זה אינו מוצג בתיבת הדו-שיח ניהול קשרי גומלין - יהיה עליך לעיין בטבלת בדיקת המידע הקשורה כדי לראות איזה שדה ברצונך לאחזר. ברצונך לציין את מספר העמודה (A=1) - זהו col_index_num הנוסחה .
כדי להוסיף שדה לטבלה החדשה, הזן את נוסחת VLOOKUP בעמודה הריקה הראשונה באמצעות המידע שנאסף בשלב 3.

בדוגמה שלנו, עמודה G משתמשת ב- Attorney ( lookup_value) כדי לקבל את נתוני תעריף החיוב מהעמודה הרביעית (col_index_num = 4) מטבלת גליון העבודה Attorneys, tblAttorneys ( table_array), עם הנוסחה =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).

הנוסחה עשויה גם להשתמש בהפניה לתא ולהפניה לטווח. בדוגמה שלנו, היא תהיה =VLOOKUP(A2,'Attorneys'! A:D,4,FALSE).
המשך להוסיף שדות עד שכל השדות הדרושים לך יתווספה. אם אתה מנסה להכין חוברת עבודה המכילה תכונות נתונים המשתמשות בטבלאות מרובות, שנה את מקור הנתונים של תכונת הנתונים לטבלה החדשה.

בעיה	מה השתבש
הערך המוחזר שגוי	אם *range_lookup* מקבל את הערך TRUE או אינו מצוין, העמודה הראשונה חייבת להיות ממוינת בסדר אלפביתי או מספרי. אם העמודה הראשונה אינה ממוינת, הערך המוחזר עלול להיות בלתי צפוי. מיין את העמודה הראשונה או השתמש ב- FALSE לקבלת התאמה מדויקת.
‎ #N/A בתא	אם *range_lookup* מקבל את הערך TRUE, אזי אם הערך ב- *lookup_value* קטן יותר מהערך הקטן ביותר בעמודה הראשונה של *table_array, תקבל את ערך השגיאה ‎#N/A. אם הערך של range_lookup* הוא FALSE, ערך השגיאה ‎#N/A פירושו שהמספר המדויק לא נמצא. לקבלת מידע נוסף אודות פתרון שגיאות ‎#N/A בפונקציה VLOOKUP, ראה כיצד לתקן שגיאת ‎#N/A בפונקציה VLOOKUP.
שגיאת ‎#REF!‎ בתא	אם *col_index_num* גדול ממספר העמודות ב- *table-array*, תקבל את #REF! ערך שגיאה‎. לקבלת מידע נוסף אודות פתרון #REF! ב- VLOOKUP, ראה כיצד לתקן שגיאת #REF!.
השגיאה ‎#VALUE!‎ בתא	אם *table_array* קטן מ- 1, תקבל את #VALUE! ערך שגיאה‎. לקבלת מידע נוסף אודות פתרון #VALUE! ב- VLOOKUP, ראה כיצד לתקן שגיאת #VALUE! בפונקציה VLOOKUP.
‎#NAME?‎ בתא	השגיאה ‎#NAME?‎ ערך שגיאה פירושו בדרך כלל שחסרים בנוסחה מרכאות. כדי לחפש שם של אדם, הקפד להשתמש במרכאות סביב השם בנוסחה. לדוגמה, הזן את השם כ- "Fontan" ב- =VLOOKUP("Fon",B2:E7,2,FALSE). לקבלת מידע נוסף, ראה כיצד לתקן שגיאת #NAME!.
שגיאת ‎#SPILL!‎ בתא	שגיאה #SPILL ! זו בדרך כלל פירושה שהנוסחה שלך מסתמכות על חיתוך משתמע עבור ערך בדיקת המידע, ושימוש בעמודה שלמה כהפניה. לדוגמה, =VLOOKUP(A:A,A:C,2,FALSE). באפשרותך לפתור את הבעיה על-ידי עיגון ההפניה לבדיקת מידע באמצעות האופרטור @ באופן הבא: =VLOOKUP(@A:A,A:C,2,FALSE). לחלופין, באפשרותך להשתמש בפעולת השירות המסורתית VLOOKUP ולהפנות לתא בודד במקום לעמודה שלמה: =VLOOKUP(A2,A:C,2,FALSE).

בצע פעולה זו	מדוע
השתמש בהפניות מוחלטות עבור range_lookup	הפניות מוחלטות מאפשרות לך למלא כלפי מטה את הנוסחה כך שהיא תבדוק תמיד את אותו טווח בדיקת מידע. למד כיצד להשתמש בהפניות מוחלטות לתאים.
אל תאחסן ערכי מספר או תאריך כטקסט.	בעת חיפוש ערכי מספר או תאריך, ודא שהנתונים בעמודה הראשונה של *table_array* אינם מאוחסנים כערכי טקסט. במקרה כזה, הפונקציה VLOOKUP עלולה להחזיר ערך שגוי או לא צפוי.
מיין את העמודה הראשונה	מיין את העמודה הראשונה של הטבלה *table_array* לפני השימוש בפונקציה VLOOKUP כאשר range_lookup הוא TRUE.
השתמש בתווים כלליים	אם *range_lookup* הוא FALSE ו- *lookup_value* הוא טקסט, באפשרותך להשתמש בתווים הכלליים – סימן שאלה (?) וכוכבית () – ב- lookup_value. סימן שאלה מתאים לתו בודד כלשהו. כוכבית מתאימה לרצף כלשהו של תווים. אם ברצונך למצוא סימן שאלה או כוכבית בפועל, הקלד ~ לפני התו. לדוגמה, =VLOOKUP("Fontan?",B2:E7,2,FALSE) יחפש את כל המופעים של Fontan* עם אות אחרונה שעשויה להשתנות.
ודא שהנתונים שלך לא מכילים תווים שגויים.	בעת חיפוש ערכי טקסט בעמודה הראשונה, ודא שהנתונים בעמודה הראשונה לא מכילים רווחים מובילים, רווחים נגררים, שימוש לא עקבי בגרשיים רגילים (' או ") ובגרשיים מסולסלים (‘ או “) או תווים שאינם מודפסים. במקרים אלה, הפונקציה VLOOKUP עלולה להחזיר ערך בלתי צפוי. כדי לקבל תוצאות מדויקות, נסה להשתמש בפונקציה CLEAN או בפונקציה TRIM כדי להסיר רווחים נגררים לאחר ערכי טבלה בתא.