נוסחת / פונקציית VLOOKUP באקסל היא פונקציית חיפוש והשבת נתונים מטבלה אחרת.
משמעות האות V, היא "Vertical"
דמיינו טבלה שחסרים בה נתונים אותם ניתן להשלים מטבלה אחרת ע"י שימוש בנתון משותף שנמצא בשתי הטבלאות.
נתון זה נקרא "נתון מפתח" וכאמור לרוב הוא נתון ייחודי, כגון תעודת זהות/מס דרכון/מק"ט וכד'….
לדוגמא:
טבלה עם נתונים חסרים
טבלה מקבילה עם נתונים להשלמה
בשתי הטבלאות יש את עמודת תעודת זהות,
לכן "ניקח" את נתון תעודת הזהות מהטבלה הראשונה ונחפש אותו בעמודה הראשונה של טווח התאים של הטבלה השנייה.
נוסחת VLOOKUP בנויה מארבעה פרמטרים:
- Lookup_value
בעזרת מה לחפש? מהו נתון המפתח שאנחנו מחפשים - Table_array
איפה לחפש?
מהו טווח התאים שבעמודה הראשונה שלו נחפש את נתון המפתח? - Col_index_num
מה מספר העמודה בטווח התאים המכיל את הערך אותו אנו רוצים להחזיר
- Range_lookup
0 או 1, כלומר False או True (לרוב מדובר על 0)
0 אומר "האם לחפש בדיוק את המספר מסעיף אחד"? לדוגמא, האם לחפש בדיוק את אותו מספר תעודת זהות מסעיף 2?
* למראית עין השאלה ברורה מאליו, הרי ברור שאנחנו מחפשים בדיוק את המספר, לכן 0 יותר שכיח… אבל באופן אבסורדי ברירת המחדל היא 1, אז חייבים לכתוב "0".
איך עושים Vlookup באקסל?
1.
יש לעמוד על התא עם הנתון החסר אותו רוצים להשלים מטבלה אחרת.
*שימו לב שניתן להוריד קובץ לתרגול כאן או בתחילת המדריך.
2.
יש ללחוץ על תפריט נוסחאות -> בדיקת מידע ועיון -> Vlookup
3.
בחלון שנפתח יש:
א.
בשדה הראשון Lookup_value לסמן בעזרת העכבר את התא שמכיל את נתון המפתח (כאמור, במקרה של דוגמה זו, ת.ז היא נתון המפתח)
ב.
בשדה השני Table_array בעזרת העכבר יש לסמן את טווח התאים המבוקש כאשר העמודה הראשונה של טווח התאים המסומן תכיל את הנתון
*שימו לב שמומלץ לקבע את את הטווח במקרה ומעוניינים לגרור את הנוסחה כלפי מטה ע"י לחיצה על F4 במקלדת
ג.
בשדה השלישי Col_index_num יש להקליד את מספר העמודה שמכילה את את הנתון אותו רוצים להחזיר, במקרה הזה נקליד "3", מאחר ועמודה 3 מתוך הטווח שסומן היא העמודה המכילה את נתון הטלפון החסר
ד.
בשדה הרביעי והאחרון יש להקליד 0 או 1, כאמור ברוב הפעמים מחפשים בדיוק את הנתון, בדוגמה זו נקליד "0" מאחר ואנו מחפשים בדיוק את מספר תעודת הזהות בעמודה הראשונה.
והתוצאה:
מספר הטלפון מוצג בתא עם הנתון החסר.
בתמונה זו מוצג התהליך כולו, חיפוש תעודת הזהות בעמודה הראשונה של הטווח, הצלבה עם עמודה מספר 3 וקבלת התוצאה בתא ששם ממוקמת הנוסחה.
*יש לגרור את התא עם התוצאה כלפי מטה ע"י לחיצה על הנקודה הירוקה (זכור! אם הטבלה ממנה שולפים את הנתונים לא מקובעת בנוסחה התוצאות יהיו שגויות)
טריק ב-VLOOKUP שכדאי לדעת (מומלץ!):
הגדרת שם לטווח תאים "נצחי".
הטבלה בה אנו מחפשים את הנתון החסר, כלומר "הטבלה השנייה" ממנה אנו משלימים את הנתונים, לרוב דינאמית ומשתנה, נתונים נמחקים, מתווספים וגודלה משתנה.
במצב כזה צריך לספק לאקסל טווח חיפוש דינאמי (סעיף 3 א למעלה) , כלומר "שטח" שהוא נתון גבולות ולנתונים בתוכו יש את החופש לגדול ולקטון.
הגדרת שם לטווח מבוסס עמודות:
1.
יש לסמן את אותיות העמודות של הטבלה בה נחפש את הנתונים
שימו לב שסימון אות העמודה מסמן את כל העמודה "עד הסוף שלה" למטה = "נצח"
2.
בצד שמאל, בקוביית כתובת התא, יש לסמן את המלל, להקליד שם כגון "נתונים" או "פרטי_עובדים" (שם ללא רווח) וללחוץ על ENTER
3.
על מנת להשתמש בשם "פרטי_עובדים" בפונקציית Vlookup יש להקליד את השם באזור Table_array המגדיר את טווח התאים לחיפוש
*כמו שניתן לראות התהליך חוסך את תהליך הקיבוע עם ה-$ ומאפשר שימוש מהיר וקל עם שם ולא עם טווח שצריך לסמן.
תרגיל ב-VLOOKUP
השלימו את שארית העמודות הריקות בטבלה המרכזית של גיליון "עובדים" מתוך הנתונים המופיעים בלשונית "נתונים"
אהבתם את המדריך?
שתפו והפיצו! אפשר גם להצטרף לקבוצת אופיס וכל השאר בפייסבוק על מנת לשאול שאלות ולקבל עדכונים שוטפים.
עוד קישורים
- שיעורי אקסל חינם
- IQU הדרכת מחשבים וקורסי אקסל בכל הרמות
