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

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

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

עצה: עיין בסרטוני וידאו אלה של YouTube מתוך Microsoft creators לקבלת עזרה נוספת בנושא VLOOKUP!

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

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

השאר חותם עם 'הכרזות'

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

השתמש בפונקציה 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, ' פרטי לקוח '! 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 (ערך בדיקת מידע, טווח המכיל את ערך בדיקת המידע, מספר העמודה בטווח המכיל את הערך המוחזר, התאמה משוערת (TRUE) או התאמה מדויקת (FALSE)).

דוגמאות

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

דוגמה 1

VLOOKUP דוגמה 1

דוגמה 2

VLOOKUP דוגמה 2

דוגמה 3

VLOOKUP דוגמה 3

דוגמה 4

VLOOKUP דוגמה 4

דוגמה 5

VLOOKUP דוגמה 5

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

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

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

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

  2. לחץ על data _GT_ Data Tools > קשרי גומלין כדי לפתוח את תיבת הדו ניהול קשרי גומלין.

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

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

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

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

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

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

    הנוסחה עשויה גם להשתמש בהפניה לתא ובהפניה לטווח. בדוגמה שלנו, הוא יהיה = VLOOKUP (A2, ' עורכי דין '! 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?‎ בדרך כלל, ערך השגיאה פירושו שהנוסחה חסרה. כדי לחפש שם של אדם כלשהו, הקפד להשתמש בהצעות מחיר סביב השם בנוסחה. לדוגמה, הזן את השם כ- "פונטנה" ב-= VLOOKUP ("פונטנה", 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 או להציע תכונה חדשה או שיפור ב- Excel User Voice.

למידע נוסף

כרטיס לעיון מהיר: מרענן
כרטיס לעיון מהיר: פתרון בעיות
ב-vlookup בנושאYouTube: סרטוני וידאו של vlookup מתוך Microsoft creators
כיצד לתקן שגיאת #VALUE! בפונקציה vlookup
כיצד לתקן שגיאת #N/a במבט
כולל על הפונקציה VLOOKUPעל נוסחאות ב-excel
כיצד להימנע מנוסחאות
שבורותלזהות שגיאות בנוסחאות
excel functions
excel (לפי קטגוריה)
VLOOKUP (תצוגה מקדימה ללא תשלום)

הערה:  דף זה תורגם באמצעות אוטומציה והוא עשוי לכלול שגיאות דקדוק או אי-דיוקים. מטרתנו היא כי תוכן זה יהיה שימושי עבורך. תוכל לספר לנו אם המידע היה מועיל? הנה המאמר באנגלית לעיונך.​

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

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

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

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×