מדריכים

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

מה עושה הפונקציה Vlookup?

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

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

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

איך הפונקציה Vlookup עובדת

את הפונקציה Vlookup נבנה עבור השורה הראשונה בטבלת המקור – טבלת שכר - ואח"כ נעתיק אותה לשאר השורות, באמצעות העתקת נוסחה.

נסתכל על השורה הראשונה של טבלת השכר :

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

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

בניית הפונקציה

הפונקציה מקבלת 4 משתנים :

המשתנה הראשון (Lookup Value ) – שם הפריט שאנו מחפשים, במקרה שלנו, אנו מחפשים את תעודת הזהות של טבלת השכר.

המשתנה השני (Table Array ) – טווח התאים שמכיל את הנתונים שאנו מחפשים. במקרה שלנו, נסמן את טבלת העובדים, ללא הכותרות שלה, (שאני קוראת לה טבלת הערכים).

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

המשתנה השלישי (Col Index Num) – גם משתנה זה מתייחס לטבלת הערכים, במקרה שלנו – טבלת העובדים - ובו נרשום את מספר אינדקס העמודה שממנה נרצה להביא את המידע (כמו שם פרטי של העובד) אל הטבלה המקורית שלנו.

אז מה זה מספר אינדקס? - נשמע לא ברור, אבל זה יותר פשוט ממה שזה נשמע: העמודה הראשון בטווח היא מספר אינדקס 1, העמודה השנייה היא מספר אינדקס 2 וכך הלאה.

לכן במקרה שלנו נרשום את המספר 2.

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

המשתנה הרביעי (Range Lookup) – נרשום בו True או False. אם אנו רוצים התאמה מדוייקת (True) או התאמה מקורבת (False).

התאמה מדויקת או התאמה מקורבת, מה זה אומר ?

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

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

וכך תראה הפונקציה, כאשר נכתוב אותה בחלון הפונקציות :

כך תראה הפונקציה אם נכתוב אותה ידנית : (אני ממליצה לעבוד עם חלון הפונקציות)

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

אז קודם כל נסביר, מה זה הפנייה מעגלית ?

הפנייה מעגלית תקרה כאשר נרשום בתא מסויים נוסחה והנוסחה תפנה לאותו תא שבו היא מופיעה. לדוגמה, אם בתא A1 כתבתי את הנוסחה הבאה: A1+10 אקבל שגיאה על הפנייה מעגלית כי אני מנסה לבצע חישוב על תא שאמור להציג לי את תוצאת הנוסחה.

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

אז מה עושים ?

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

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

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

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