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

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

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

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

הדפדפן שלך אינו תומך בווידאו.

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

השתמש בפונקציה 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.

לדוגמה, אם מערך הטבלה משתרע על-פני תאים 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:

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

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

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

  4. באופן אופציונלי, באפשרותך לציין ערך TRUE אם אתה מעוניין בהתאמה משוערת או ערך FALSE אם אתה מעוניין בהתאמה מדויקת של הערך המוחזר. אם לא תציין דבר, ערך ברירת המחדל יהיה תמיד TRUE או התאמה משוערת.

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

=VLOOKUP(lookup value, range המכיל את ערך בדיקת המידע, מספר עמודה בטווח המכיל את הערך ההחזרה, התאמה משוערת (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), ומחזיר 03/04/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 מחזירה את העובד לא נמצא. ISNA לוודא שאם VLOOKUP מחזיר #N/A, השגיאה מוחלפת על-ידי העובד לא נמצא, במקום #N/A.



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

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

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

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

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

  2. לחץ על >כלי נתונים > 'קשרי גומלין' כדי לפתוח את תיבת הדו-שיח ניהול 'קשרי גומלין' נתונים.

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

    • השדה המקשר בין הטבלאות (המפורט בסוגריים בתיבת הדו-שיח). זוהי הנוסחה lookup_value עבור נוסחת VLOOKUP.

    • שם טבלת בדיקת המידע הקשורה. זוהי הנוסחה table_array בנוסחת VLOOKUP.

    • השדה (עמודה) בטבלת בדיקת המידע הקשורה הכוללת את הנתונים הרצויים בטבלת המידע עמודה. מידע זה אינו מוצג בתיבת הדו-שיח ניהול 'קשרי גומלין' - יהיה עליך להסתכל על טבלת בדיקת המידע הקשורה כדי לראות איזה שדה ברצונך לאחזר. ברצונך לציין את עמודה (A=1) - זהו col_index_num בנוסחה.

  4. כדי להוסיף שדה לטבלה החדשה, הזן את הנוסחה VLOOKUP בנוסחה הריקה עמודה באמצעות המידע שאספת בשלב 3.

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

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

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

בעיה

מה השתבש

הערך המוחזר שגוי

אם 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 גדול ממספר העמודות במערךהטבלה, תוכל לקבל את #REF! ערך שגיאה‎.

לקבלת מידע נוסף אודות פתרון #REF! שגיאות ב- VLOOKUP, ראה כיצד לתקן שגיאת #REF!.

שגיאות ‎#VALUE!‎ בתא

אם table_array קטן מ- 1, תוכל לקבל את #VALUE! ערך שגיאה‎.

לקבלת מידע נוסף אודות פתרון #VALUE! שגיאות ב- VLOOKUP, ראה כיצד לתקן שגיאת #VALUE! בפונקציה VLOOKUP.

‎#NAME?‎ בתא

השגיאה ‎#NAME?‎ בדרך כלל, משמעות ערך השגיאה היא שהנוסחה חסרה במרכאות. כדי לחפש שם של אדם, הקפד להשתמש במרכאות סביב השם בנוסחה. לדוגמה, הזן את השם כ- "Fontana" ב- =VLOOKUP("Fontana",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) יחפש את כל המופעים של פונטנה עם אות אחרונה שיכולה להשתנות.

ודא שהנתונים שלך לא מכילים תווים שגויים.

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

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

זקוק לעזרה נוספת?

תוכל תמיד לשאול מומחה ב- Excel Tech Community או לקבל תמיכה בקהילת Answers.

למידע נוסף

כרטיס לעיון מהיר: מרענן VLOOKUP
כרטיס לעיון מהיר: עצות לפתרון בעיות ב-
VLOOKUP כיצד לתקן שגיאת #VALUE! בפונקציה VLOOKUP
כיצד לתקן שגיאת #N/A בפונקציה VLOOKUP
מבט כולל על נוסחאות Excel
כיצד להימנע מנוסחאות מנותקות
זיהוי שגיאות בנוסחאות
Excel פונקציות (בסדר אלפביתי)
Excel פונקציות (לפי קטגוריה)
VLOOKUP (תצוגה מקדימה ללא תשלום)

זקוק לעזרה נוספת?

הרחב את הכישורים שלך
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Microsoft insider

האם מידע זה היה שימושי?

עד כמה אתה מרוצה מאיכות התרגום?
מה השפיע על החוויה שלך?

תודה על המשוב!

×