VLOOKUP

 

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

VLOOKUP ל-BigQuery

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

דוגמאות לשימוש

VLOOKUP("Apple",table_name!fruit,table_name!price)

תחביר

VLOOKUP(search_key, range,index, is_sorted)

• search_key: הערך שרוצים לחפש בעמודת החיפוש.
• search_column: עמודת הנתונים שיש לקחת בחשבון בחיפוש.
• result_column: עמודת הנתונים שיש להחזיר ממנה את התוצאה.
• is_sorted: [אופציונלי] האופן שבו יש למצוא התאמה ל-search_key.
  • FALSE: להתאמה מדויקת, האפשרות הזו מומלצת.
  • TRUE: להתאמה קרובה, זוהי ברירת המחדל אם לא צוין is_sorted.
    טיפ: לפני שמשתמשים בהתאמה קרובה, צריך למיין את מפתח החיפוש בסדר עולה. אחרת, סביר להניח שהערך המוחזר יהיה שגוי. סיבות אפשריות לקבלת ערכים מוחזרים שגויים

טיפ: לשאילתות גמישות יותר של מסדי נתונים ב-BigQuery, כדאי להשתמש ב-XLOOKUP.

תחביר

‎=VLOOKUP(search_key, range, index, [is_sorted])

קלט

1. search_key: הערך שרוצים לחפש בעמודה הראשונה של הטווח.
2. range: הערכים העליונים והתחתונים שיש לבדוק בחיפוש.
3. index: האינדקס של העמודה עם הערך המוחזר של הטווח. האינדקס חייב להיות מספר שלם חיובי.
4. is_sorted: קלט אופציונלי. יש לבחור אפשרות:
   • FALSE = התאמה מדויקת. מומלץ להשתמש באפשרות הזו.
   • TRUE = התאמה קרובה. זו ברירת המחדל אם לא צוין is_sorted.
     חשוב: לפני שמשתמשים בהתאמה קרובה, צריך למיין את מפתח החיפוש בסדר עולה. אחרת, סביר להניח שהערך המוחזר יהיה שגוי. סיבות אפשריות לקבלת ערכים מוחזרים שגויים

הערך המוחזר

הערך הראשון התואם מה-range שנבחר.

פרטים טכניים:

דוגמה:

‎=VLOOKUP(G9, B4:D8, 3, FALSE)

‎=VLOOKUP("Apple", B4:D8, 3, TRUE)

קלט	תיאור
search_key	זהו הערך שאתם מחפשים בעמודה הראשונה של ה-range. כדי לקבל ערך לא שגוי, מפתח החיפוש חייב להופיע בעמודה הראשונה של ה-range. ניתן להשתמש גם בהפניות לתאים. כדי לבצע בדיקה פשוטה: אם ה-search_key ממוקם ב-B3, אז ה-range צריך להתחיל בעמודה B.
range	זה ה-range שבו: הפונקציה מחפשת את מפתח החיפוש שצוין בעמודה הראשונה. VLOOKUP מחזירה את הערך מהעמודה שצוינה ב-index. אפשר גם להשתמש בטווח תאים בעל שם. כדי שהערך המוחזר לא יהיה שגוי, מפתח החיפוש צריך להיות בעמודה הראשונה של ה-range. כדי לבצע בדיקה פשוטה: אם ה-search_key ממוקם ב-B3, אז ה-range צריך להתחיל בעמודה B.
index	נקרא גם "מספר העמודה". זהו האינדקס של העמודה ב-range שמכילה את הערך המוחזר. האינדקס הקטן ביותר האפשרי הוא 1. האינדקס הגדול ביותר האפשרי הוא מספר העמודות המקסימלי באותו ה-range. אחרי שמגדירים את הטווח, VLOOKUP מחפשת רק בעמודה של מפתח החיפוש, כאשר index=1, או בעמודות שמצד ימין. טיפ: כשמשתמשים ב-VLOOKUP, נניח שהעמודות של ה-range ממוספרות משמאל לימין ומתחילות ב-1.
is_sorted	זהו קלט אופציונלי. שתי האפשרויות הזמינות הן TRUE ו-FALSE. אם is_sorted הוא TRUE, אז VLOOKUP משתמשת בהתאמה קרובה. חשוב: לפני שמשתמשים בהתאמה קרובה, צריך למיין את מפתח החיפוש בסדר עולה. אחרת, ייתכן שיוחזר ערך לא צפוי. סיבות אפשריות לקבלת ערכים מוחזרים שגויים אם is_sorted הוא FALSE, אז VLOOKUP משתמשת בהתאמה מדויקת. אם is_sorted לא מצוין, כברירת מחדל הוא ייחשב TRUE. מומלץ מאוד: להשתמש ב-FALSE עבור is_sorted בגלל ההתנהגות העקבית שלו, כשהעמודה של מפתח החיפוש ממוינת וכשהיא לא ממוינת. לציין תמיד את is_sorted כדי לשפר את הקריאוּת, גם אם הקלט הוא אופציונלי.

 

פלט

תיאור

הערך המוחזר

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

דוגמאות בסיסיות ל-VLOOKUP:

VLOOKUP במפתחות חיפוש שונים

נשתמש ב-VLOOKUP כדי לחפש את המחיר של תפוז ותפוח.

רוצים לנסות?

הסבר:

כשמשתמשים ב-VLOOKUP, אפשר להשתמש במפתחות חיפוש שונים כמו "Apple" ו-"Orange".

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

search_key הוא "Orange"	‎=VLOOKUP("Orange", B4:D8, 3, FALSE) הערך המוחזר = $1.01
search_key הוא "Apple"	‎=VLOOKUP("Apple", B4:D8, 3, FALSE) הערך המוחזר = $1.50
search_key שמשתמש בהפניה לתא של "Apple" ב-G9	‎=VLOOKUP(G9, B4:D8, 3, FALSE) הערך המוחזר = $1.50

VLOOKUP באינדקסים שונים של עמודות

נשתמש ב-VLOOKUP כדי למצוא את כמות התפוזים בעמודה השנייה באינדקס.

רוצים לנסות?

הסבר:

כשמשתמשים ב-VLOOKUP, נניח שהעמודות של ה-range ממוספרות משמאל לימין ומתחילות מ-1. כדי לאתר את המידע המבוקש, צריך לציין את אינדקס העמודה שלו. לדוגמה, בעמודה 2 מוצגת כמות.

‎Index = 2 מחפשים את הכמות של תפוזים, שהיא העמודה השנייה ב-range.

‎=VLOOKUP(G3, B4:D8, 2, FALSE) הערך המוחזר = 5

התאמה מדויקת או התאמה קרובה ב-VLOOKUP

• נשתמש בהתאמה מדויקת ב-VLOOKUP כדי למצוא מזהה מדויק.
• נשתמש להשתמש בהתאמה קרובה ב-VLOOKUP כדי למצוא את המזהה הקרוב.

רוצים לנסות?

הסבר:

ניתן להשתמש בהתאמה קרובה או ב-is_sorted = TRUE כשמחפשים את ההתאמה הטובה ביותר, אבל לא התאמה מדויקת.

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

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

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

כשמחפשים את ההתאמה המדויקת, למשל is_sorted = FALSE, היא מחזירה התאמה מדויקת. לדוגמה, שם הפרי של ID = 103 הוא "Banana". אם אין התאמה מדויקת, תתקבל שגיאת ‎#N/A. בגלל ההתנהגות הצפויה יותר של ההתאמה המדויקת, מומלץ להשתמש בה.

התאמה מדויקת	‎=VLOOKUP(G6, A4:D8, 2, FALSE) הערך המוחזר = "Apple"
התאמה קרובה	‎=VLOOKUP(G3, A4:D8, 2, TRUE) או ‎=VLOOKUP(G3, A4:D8, 2) הערך המוחזר = "Banana"

אפליקציות VLOOKUP נפוצות

החלפה של ערך שגוי מ-VLOOKUP

אולי תעדיפו להחליף ערך שגוי ש-VLOOKUP מחזירה כשמפתח החיפוש שלכם לא קיים. במקרה כזה, אם לא רוצים לקבל ‎#N/A, אפשר להשתמש בפונקציות IFNA()‎ כדי להחליף את ‎#N/A. מידע נוסף על IFNA()‎.

רוצים לנסות?

במקור, VLOOKUP מחזירה ‎#N/A כי מפתח החיפוש "Pencil" לא קיים בעמודה "Fruit". IFNA()‎ מחליפה את שגיאת ‎#N/A בקלט השני שמצוין בפונקציה. במקרה שלנו, "NOT FOUND".

‎=IFNA(VLOOKUP(G3, B4:D8, 3, FALSE),"NOT FOUND") הערך המוחזר = "NOT FOUND"

טיפ: אם אתם רוצים להחליף שגיאות אחרות, כמו ‎#REF!‎, כאן אפשר לקרוא מידע נוסף על IFERROR()‎.

VLOOKUP עם קריטריונים מרובים

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

רוצים לנסות?

1. כדי ליצור את העמודה התומכת, משתמשים בסימן "&" כדי לשלב בין שם פרטי לשם משפחה.	‎=C4&D4 וגרירה למטה מ-B4 ל-B8 יוצרים את העמודה התומכת.
2. שימוש בהפניה לתא B7, ‏JonLee, כמפתח החיפוש.	‎=VLOOKUP(B7, B4:E8, 4, FALSE) הערך המוחזר = "Support"

VLOOKUP עם תווים כלליים לחיפוש או עם התאמות חלקיות

ב-VLOOKUP אפשר גם להשתמש בתווים כלליים לחיפוש או בהתאמות חלקיות. ניתן להשתמש בתווים הכלליים הבאים לחיפוש:

• סימן שאלה "?" תואם לכל תו בודד.
• כוכבית "*" תואמת לכל רצף של תווים.

כדי להשתמש בתווים כלליים לחיפוש ב-VLOOKUP, צריך להשתמש בהתאמה מדויקת: "is_sorted = FALSE".

רוצים לנסות?

"St*" משמש להתאמה של כל דבר שמתחיל ב- "St", בלי קשר למספר התווים, כמו למשל "Steve"‏, "St1"‏, "Stock"‏ או "Steeeeeeve".

‎=VLOOKUP("St*", B4:D8, 3, FALSE) הערך המוחזר = "Marketing"

פתרון בעיות הקשורות לשגיאות ושיטות מומלצות:

הערך המוחזר לא נכון

• מוחזר ערך בלתי צפוי: אם מגדירים is_sorted כ-TRUE, אבל העמודה הראשונה בטווח לא ממוינת לפי סדר מספרי או אלפביתי עולה, אז צריך לשנות את is_sorted ל-FALSE.

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

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

מידע נוסף זמין בקטע בנושא שיטות מומלצות.

‎#N/A

• אם נעשה שימוש בהתאמה קרובה או ב-is_sorted = TRUE ואם מפתח החיפוש ב-VLOOKUP קטן מהערך הקטן ביותר בעמודה הראשונה, אז VLOOKUP מחזירה ‎#N/A.
• אם נעשה שימוש בהתאמה מדויקת או ב-is_sorted = FALSE, אז ההתאמה המדויקת של מפתח החיפוש ב-VLOOKUP לא נמצאת בעמודה הראשונה. אם לא רוצים לקבל ‎#N/A כשמפתח החיפוש לא מופיע בעמודה הראשונה, אפשר להשתמש בפונקציית IFNA()‎.

‎#REF!‎

יכול להיות שתציינו בטעות range עם מספר גדול יותר מהמספר המקסימלי של העמודות ב-range. כדי להימנע מכך, צריך:

• לספור את העמודות מה-range שבחרתם, ולא מהטבלה כולה.
• להתחיל את הספירה מ-1 ולא מ-0.

‎#VALUE!‎

אם קיבלתם שגיאת ‎#VALUE!‎ יכול להיות שביצעתם את אחת האפשרויות הבאות:

• הזנתם את הטקסט או את שם העמודה של ה-index בצורה לא נכונה.
• הזנתם מספר קטן מ-1 עבור ה-index. הערך של index חייב להיות שווה ל-1 לפחות, וקטן ממספר העמודות המקסימלי של ה-range. VLOOKUP יכולה לחפש רק בעמודה של מפתח החיפוש כאשר ‎index = 1 או בעמודות שמצד ימין.

חשוב:אפשר להזין ל-index מספר בלבד.

‎#NAME?‎

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

שיטות מומלצות

 

לבצע	סיבה
להשתמש בהפניות מוחלטות ל-range	צריך להשתמש: בהפניה מוחלטת אל VLOOKUP range ב-VLOOKUP(G3, $B$3:$D$7, 3, FALSE) אין להשתמש: ב-VLOOKUP(G3, B3:D7, 3, FALSE) כך ניתן למנוע שינויים לא צפויים ב-range כאשר מעתיקים אותו או גוררים אותו למטה.
כשמשתמשים בהתאמה קרובה כמו is_sorted = TRUE, צריך למיין את העמודה הראשונה בסדר עולה.	אם משתמשים בהתאמה קרובה או ב-is_sorted = TRUE, צריך למיין את העמודה הראשונה בסדר עולה. אחרת, סביר להניח שהערך המוחזר יהיה שגוי. מידע נוסף על אופן המיון
לנקות את הנתונים לפני השימוש ב-VLOOKUP	לפני השימוש ב-VLOOKUP, חשוב לנקות את הנתונים. נתונים לא נקיים עלולים לגרום ל-VLOOKUP להחזיר ערך בלתי צפוי. אלה דוגמאות לבעיות נפוצות של נתונים לא נקיים: רווחים בהתחלה: "‎ apple" רווחים בסוף: "apple ‎" מקומות ריקים או רווחים: "" ו-" " אינם זהים כדי לחתוך רווח לבן בהתחלה ובסוף, אפשר להשתמש בנתונים ניקוי נתונים חיתוך רווחים לבנים.
לא לשמור ערכים של מספרים או תאריכים כטקסט	חשוב לוודא שהערכים של התאריכים או של המספרים בעמודה הראשונה בטווח של VLOOKUP, כמו העמודה של מפתח החיפוש, לא מאוחסנים כערכי טקסט. ייתכן שיוחזר ערך בלתי צפוי. בחלק העליון של Sheets, בוחרים את העמודה של מפתח החיפוש. מקישים על תפריט העיצוב מספר. בוחרים אפשרות בהתאם לסוג הנתונים הרצוי: תאריך מספר