קורס אקסל – פונקציית Index, Match

בעיות בפונקציית השליפה – Vlookup

ישנן 3 בעיות עיקריות בפונקציית השליפה – Vlookup

1. כפי שניתן לקרוא בהסבר הנוסחה באשף הפונקציות, כדי למצוא את הערך שלנו (Lookup value) בטבלה או בכל בסיס נתונים חיצוני, נדרש שהעמודה בטבלה ממנה מאוחזרים הנתונים תהיה העמודה הראשונה (הימנית ביותר כשהגיליון מימין לשמאל) – מה נעשה כאשר עמודה זו איננה הראשונה?
2. הכלי החזק ביותר באקסל – הוא היכולת לגרור נוסחאות אנכית ואופקית. כאשר ננסה לגרור את הנוסחה שכתבנו בפונקציית Vlookup אופקית (לעמודות הבאות) נראה שנקבל תוצאות שגויות.
3. כאשר ישנן שתי רשומות בעלי Lookup value זהה – נניח ללקוח אחד 2 תוצאות. הפונקציה תחזיר תמיד את הערך המופיע ברשומה הראשונה מבין התוצאות.

כדי להצליח לשלוף נתונים בכל המקרים שהוזכרו לעיל – נשתמש בפונקציית ה Match   שתעמוד בזכות עצמה; או שתשולב בפונקציית ה Vlookup ; או שתשולב ביחד עם פונקציית ה Index.

Match

פונקציית ה Match – מטרתה לספור, על פי וקטור אחד בלבד (או אופקי-לפי כיוון הגיליון; או אנכי-מלמעלה),
היכן ממוקמת התוצאה אותה אני מחפש (Lookup value)

דוגמא ראשונה – Match כפונקציה בפני עצמה

יש לנו מספר תוצאות ואנו מחפשים היכן ממוקמת התוצאה 2:

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

אסביר:

לפונקציית ה Match – יש שלושה ארגומנטים:

1. Lookup_value

תפקידו לחפש את הערך אותו אני רוצה למצוא בטבלה – במקרה זה – הספרה 2.

2. Lookup_array

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

3. Match_type

בדוגמא זו נרצה תוצאה מדויקת ולכן נקליד 0.

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

נראה איך נראית הפונקצייה מהאקסל:

נעמוד עם הסמן על הנוסחה בתא  F4ונתבונן בשורת הנוסחאות – ניתן לראות שהפונקציה חיפשה את C4- כלומר את הספרה 2, בטווח התאים A2:G2 , וחיפשה התאמה מדויקת – ואכן היא החזירה את התוצאה 3

הספרה 2 ממוקמת במקום ה 3.

לְמָה נדרשת פונקציה זו, וכיצד היא עוזרת לנו לפתור את אחת מהבעיות של ה Vlookup ?

נחזור לבעיה 2 – "..כאשר ננסה לגרור את הנוסחה שכתבנו בפונקציית  Vlookup אופקית (לעמודות הבאות) נראה שנקבל תוצאות שגויות"

לדוגמא, לחברת רהיטים מסוימת יש בסיס נתונים של הזמנת לקוחות, ובה נתונים רבים:

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

אם נקליד בתא B15 פונקציית Vlookup כבעבר:  VLOOKUP($A15,$A$1:$K$9,2,FALSE)=

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

הסיבה היא, למרות שקיבענו נכון – כי אנו צריכים לעדכן בכל פעם מחדש איזה עמודה אנו רוצים למשוך – **תזכורת:
בארגומנט ה 3.   Col index num  -נכתוב את מספר העמודה בטבלה ממנה אחזרנו, ובה נמצאים הנתונים שאנו מחפשים. כייוון שבמקרה זה נרצה בפעם הראשונה את עמודה 2, לאחר מכן את עמודה 6 , ואחריה את עמודה 11 , 4  ו- 3.

בדיוק למטרה זו נגייס את פונקציית Match ונקנן אותה בתוך ה Vlookup בדיוק באגומנט הבעייתי -3 :

דוגמא שניה – Match כפונקציה מקוננת בפונקציית Vlookup

בארגומנט הראשון  – Lookup_value

אציב את הערך אותו אני רוצה למצוא בטבלה – במקרה זה – "תאריך קנייה" – אך לא אבקש למצוא את "תאריך קניה" שבתא D1  אלא אבקש למצוא את "תאריך קניה" שבתא B14  – כך שאם נגרור שמאלה הפונקציה תחפש לאחר מכן את עמודת C14 – "שם לקוח" וכך הלאה.

בארגומנט השני  – Lookup_array

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

בארגומנט השלישי  – Match_type

גם בדוגמא זו נרצה תוצאה מדויקת ולכן נקליד 0.

ולכן הפעם נקליד בתא B15 את הפונק' המקוננת, והפונקציה הסופית תראה כך:

כעת, אסביר את הארגומנט השלישי  – Match_type

לארגומנט השלישי 3 אופציות:

את השימוש בתצורה 0 כבר ראינו.

כדי להבין את השימוש בשניים האחרים להלן דוגמא לכל אחד מהם:

דוגמא שלישית – Match ארגומנט Match_type – 1 או מושמט

בדוגמא רשימת ציונים למבחני לשכת עורכי הדין – בחישוב קל, הממוצע של קבוצה זו הינו: 73.33

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

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

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

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

ניתן לראות שהציון הכי קרוב הוא: 66

והוא ממוקם במקום ה 5

כלומר – חמישה עונים על הקריטריונים!

את התוצאה 5 החזירה הנוסחה:

ניתן לראות שהשמטנו את הארגומנט השלישי,

ואותה התוצאה היינו מקבלים אם היינו מקלידים שם 1

דוגמא רביעית – Match ארגומנט Match_type  1-

בדוגמא רשימת משכורות של עורכי דין מתמחים – במשרד גדול בתל – אביב.

עו"ד הבכיר, הבעלים של אותו משרד, ביקש מהמזכירה לקבל את המידע:
כמה קיבלו משכורת מעל או שווה ל – 24,100 ₪

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

ניתן לראות שהמשכורת, במקרה זה בדיוק : 24,100 ₪

והוא ממוקמת במקום ה 7

כלומר – שבעה עונים על הקריטריונים!

את התוצאה 7 החזירה הנוסחה:

ניתן לראות שכעת הוספנו את הארגומנט השלישי,

והקלדנו שם את הערך: 1-

פתרון בעיה ראשונה ב Vlookup

** תזכורת

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

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

כעת נתבונן בטבלה שלהלן, בה מאוחסנים כל הנתונים:

אם נרצה למצוא את תאריך ההצטרפות לפי מספר חבר המועדון, ונשתמש בנוסחת Vlookup,
נקבל את התוצאה #N/A כלומר הנתון אינו קיים.

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

נצליח במקרה זה להתגבר על הבעיה אם נסמן את הטווח, ה- (Table array), שיתחיל  מעמודה K.

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

אם הנתון המבוקש יופיע מימין (בגיליון מימין לשמאל), נניח נחפש את שם חבר המועדון – שנמצא בעמודה J שמימין למספר חבר המועדון – לא נוכל לשנות דבר – אלא אם נוותר על פונקציית Vlookup, ונשתמש בשילוב הפונקציות אינדקס – Index  + Match:

Index

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

בניגוד לפונקציה רגילה, שכשאנו מקלידים את שם הפונ' ניתן ישירות להכנס אליה,

פונקציה זו שואלת אותנו באיזו מהשניים אנו מעוניינים:

האפשרות השנייה מוכרת כ- תבנית הפנייה,
נשתמש בה, אם ההפניה מורכבת מבחירות שאינן סמוכות.

במאמר זה נציג את האפשרות הראשונה, השימושית יותר, כלומר, במצב זה נלחץ על אישור.

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

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

1 – מה הטווח בו אנו מחפשים; 2 – באיזו שורה; 3 – באיזו עמודה.

נחשוב על טבלת האקסל המוגדרת כשורות (1,2,3) וכעמודות (A,B,C) שעמודה A היא העמודה הראשונה B השנייה וכך הלאה-
נתבונן בטבלה שלהלן היכן ממוקמת השחקנית גל גדות? התשובה בתא C2 !

בתא C2 , כלומר בשורה השנייה, בעמודה השלישית.

אציג שוב את הטבלה, הפעם אהפוך את העמודות למספרים –

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

כעת נציב את הנוסחה ונראה עד כמה זה פשוט:

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

שילוב הפונקציות – Index  + Match:יעזור לנו לפתור גם את הבעיה השלישית של ה Vlookup –
דוגמא זו , יתכן ותוצג בהרחבה בקורס.