מדריך אקסלשאלות ותשובות על תוכנת האקסל
וילוקאפ – נשמע כמילה מאוד מפחידה. אז זהו שלא, לאחר שאלמד אתכם להשתמש בפונקציה זה יישמע לכם קל יותר ממה שחשבתם.
מה עושה הפונקציה 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 (ניתן לטפל בהודעת שגיאה זו, למקרה שלא נרצה שתוצג).
התאמה מקורבת – שימושי כאשר בטבלת הנתונים נחפש מתוך טווח של מספרים ממויינים, ונרצה שיחזיר לנו את המספר הקרוב, במקרה שבו לא מצא את המספר שחיפשנו. (לא מתאים למקרה שלנו – על כך נדבר בהמשך).
וכך תראה הפונקציה, כאשר נכתוב אותה בחלון הפונקציות :
כך תראה הפונקציה אם נכתוב אותה ידנית : (אני ממליצה לעבוד עם חלון הפונקציות)
החל מגירסא אקסל 365, ניתן להחליף את השימוש בפונקציה Vlookup לפונקציה החדשה Xlookup.
הפונקציה Xlookup קלה יותר להבנה ולכתיבה והיא פותרת את כל המגבלות שהיו לפונקציה הוותיקה Vlookup.
אחד ההבדלים החשובים הוא, שהפונקציה החדשה לא מתייחסת לכל טבלת הנתונים (הארגומנט Table_array), אלא, מתייחסת לעמודות נפרדות - עמודת המפתח והעמודה שממנה נקבל את הנתונים.
נחזור לדוגמה שהשתמשתי בה בהסבר על הוילוקאפ, של טבלת השכר שבה נרצה להשלים את הנתון של שם העובד מטבלת העובדים, כאשר המפתח המקשר בין שתי הטבלאות הוא תעודת הזהות.
איך נבנה את הפונקציה Xlookup?
לפונקציה Xlookup יש 3 ארגומנטים שחובה למלא :
הארגומנט הראשון (Lookup Value )- ארגומנט זה, זהה לארגומנט הראשון של וילוקאפ. – שהוא שם פריט המפתח שאנו מחפשים. במקרה שלנו, אנו מחפשים את תעודת הזהות שבטבלת השכר.
הארגומנט השני (Lookup Array )נסמן את העמודה מתוך טבלת הנתונים, שבה יש לחפש את פריט המפתח (תעודת הזהות שבטבלת השכר, שנרשמה בארגומנט ה- lookup_value ) .
הארגומנט השלישי (Return Array)– נסמן את העמודה מתוך טבלת הנתונים, שממנה נחזיר את הערך המבוקש (את שם העובד מתוך טבלת הנתונים).
חשוב לבצע קיבוע לעמודות שסימנו, מכיוון שנצטרך אח"כ להעתיק את נוסחת ה-Xlookup גם לשורות הבאות ולא נרצה שהוא יסתכל על שורות לא רלוונטיות בטבלת הנתונים.
בפונקציה Xlookup, אין חשיבות לסדר העמודות בטבלת הנתונים.
הארגומנט הרביעי (If Not Found ) – הוא לא חובה למילוי. בארגומנט זה, נרשום טקסט שיופיע במקרה שהפריט שנחפש לא נמצא (במקום ההודעה #NA ).
הארגומנט החמישי (Match Mode)– סוג ההתאמה. כאשר ההתאמה היא התאמה מדוייקת, אין צורך למלא את ארגומנט זה, מכיוון שזוהי ברירת המחדל (המקביל לערך False בוילוקאפ).
כאשר ההתאמה היא מקורבת (המקביל לערך True בוילוקאפ), יש לרשום 1- .
וכך תראה הפונקציה, כאשר נכתוב אותה בחלון הפונקציות :
כך תראה הפונקציה אם נכתוב אותה ידנית : (אני ממליצה לעבוד עם חלון הפונקציות)
איך מוצאים איפה יש הפנייה מעגלית? מה זה הפנייה מעגלית?
קרה לכם שפתחתם גיליון אקסל וקיבלתם הודעה שיש בחוברת עבודה הפנייה מעגלית ?
אז קודם כל נסביר, מה זה הפנייה מעגלית ?
הפנייה מעגלית תקרה כאשר נרשום בתא מסויים נוסחה והנוסחה תפנה לאותו תא שבו היא מופיעה. לדוגמה, אם בתא A1 כתבתי את הנוסחה הבאה: A1+10 אקבל שגיאה על הפנייה מעגלית כי אני מנסה לבצע חישוב על תא שאמור להציג לי את תוצאת הנוסחה.
אז הבנו מה זה הפנייה מעגלית, אבל עדיין, אקסל לא מראה לנו באיזו נסוחה ובאיזה תא קיימת השגיאה
אז מה עושים ?
כדי לראות באיזה תא ובאיזה גיליון נמצאת השגיאה, בצע את הפעולות הבאות :
גש לכרטסת נוסחאות, בקבוצה ביקורת נוסחאות פתח את הרשימה שבכפתור בדיקת שגיאות ובחר ב- הפניות מעגליות. האקסל יביא אותנו בדיוק לתא שבו הנוסחה יוצרת הפנייה מעגלית
פתרונות אפשריים:
אם ברצונכם שהאקסל ישאיר את שורת הכותרות של עמודות הטבלה, קבועה, כך שכאשר תיגללו בגיליון כלפי מטה שורה מספר 1 תמיד תופיע - פעולה זו נקראת באקסל הקפאה.
באמצעות ההקפאה של השורה הראשונה נוכל תמיד לדעת מהי הכותרת של כל עמודה ולא נצטרך לגלול תמיד מעלה לתחילת הגיליון.
- במקרה שבטבלה שלנו הכותרות נמצאות בשורה מספר 1, נבחר באפשרות הקפאת שורה ראשונה :
בכרטסת תצוגה, בקבוצה חלון ליחצו על הכפתור הקפא חלוניות וביחרו באפשרות - הקפא שורה ראשונה.
(אין צורך לסמן את השורה)
- במקרה שהטבלה שלנו היא רוחבית, ונרצה להקפיא את העמודה הראשונה - עמודה A, לדוגמה, טבלה שבעמודה הראשונה מופיע שם העובד ושאר הפרטים שלו מופיעים בעמודות הבאות.
בכרטסת תצוגה, בקבוצה חלון ליחצו על הכפתור הקפא חלוניות וביחרו באפשרות - הקפא עמודה ראשונה.
(גם פה, אין צורך לסמן את העמודה)
-
מה קורה אם יש לנו יותר מאשר שורת כותרת אחת בטבלה ? במקרה כזה, נשתמש באפשרות הראשונה שמופיעה באפשרויות של כפתור הקפא חלוניות:
אבל תחילה עלינו לסמן את השורה שמתחת לשורות שנרצה להקפיא - לדוגמה, אם נרצה להקפיא שתי שורות, את השורות 1-2, אז יש לעמוד על שורה מספר 3 - ואז נבחר באפשרות הקפא חלוניות שמופיעה בלחיצה על הכפתור הקפא חלוניות
צפו בסרטון
הקפאת שורות הכותרת בטבלת אקסל
