פונקצית VLookup 2012-02-17T08:09:54+00:00

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

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

דוגמאות:

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

הכי פשוט להבין מתוך הדגמה:

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

אנו רוצים לחפש ציון של תלמיד לפי תעודת הזהות שלו.

אזי בטבלה שבנינו בצד שמאל, הקלדנו בתא G4 המכיל את הציון את הפונקציה הבאה:

=VLOOKUP(F4,A2:C15,3,0)

המבנה של הפונקציה הוא כזה :

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

נסביר על פי הדוגמא שהבאנו:

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

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

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

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

 חיפוש  מדויק או מוערך – כאן ישנן שתי אפשרויות :

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

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

חיפוש ערך שלא נמצא בעמודה הראשונה בטבלה – באמצעות VLOOKUP 

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

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

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

=VLOOKUP(F4,B2:C15,2,0)

פונקצית VLOOKUP באקסל - להגדיר רק את העמודות המתאימות

VLOOKUP עם פרמטר TRUE 

אחת היכולות החזקות של VLOOKUP היא חיפוש ערך קרוב למה שחיפשנו.

כדי לבצע זאת – הטבלה חייבת להיות ממוינת מהקטן לגדול!

ושוב, נסביר תוך כדי דוגמה:

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

בטבלה הבאה ישנה רשימת שכר לעובדים בארגון גדול.

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

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

עכשיו הוספתי עמודה למדרגת המס, ורשמתי בה את הפונקציה הבאה:

=VLOOKUP($C2,$F$2:$G$6,2,1)

גררתי את הפונקציה על כל העמודה, ואכן VLOOKUP מצאה לכל עובד את מדרגת המתאימה.

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

[do_widget_area widget_area_class=none]

page