החל מגירסת אקסל 365, אחת הפונקציות שנוספו לאקסל היא XLOOKUP (כמו גם עוד הרבה דברים, למשל פונקציות מערך דינמיות). XLOOKUP היא תחליף ל-VLOOKUP, שלרוב – היא יותר טוב ממנה. כלומר – המטרה של הפונקציה היא למצוא ערך לפי “מפתח” כלשהוא. המדריך כולל 3 סרטונים קצרים על XLOOKUP + ולמי שמעוניין, יש גם דף תרגול, שמאפשר לתרגל את הנוסחאות גם אם אין לכם אקסל 365 במשרד\בבית (למעבר לדף תרגול על XLOOKUP לחצו כאן).
למשל :
- למצוא שם של עובד לפי תעודת זהות
- למצוא מיקום של פריט במחסן של מספר קטלוגי.
3 סרטוני XLOOKUP (וההסבר הכתוב אחריהם )
איך בנויה פונקציית XLOOKUP ?
ל-XLOOKUP יש 3 פרמטרים חובה, ו-3 פרמטרים רשות. בואו נראה אותם בתרשים יפה :
- לפי מה מחפשים ( למשל – לפי תעודת זהות )
- איפה התאים שבתוכה מחפשים ? (למשל – מתא a1 עד a6 ).
- איפה בתאים שבתוכם מוצאים ? ( למשל – מתא b1 עד b6 ).
ועכשיו אפשר להוסיף, אבל לא חייבים :
- מה להחזיר אם לא מוצאים כלום (למשל – “תעודת זהות לא תקינה”)
- חיפוש מדוייק, או מקורב.
- באיזה סדר לחפש.
הפרמטרים שהם חובה בנוסחת XLOOKUP
פונקציית XLOOKUP – דוגמה בסיסית פשוטה
המטרה שלנו – היא למצוא את הטמפרטורה בקריית שמונה, ביום שני.
אז :
לפי מה מחפשים - לפי המילים “קריית שמונה”
איפה מחפשים – בעמודה של הערים (עמודה D )
איפה מוצאים - בעמודה של יום שני (עמודה F )
דוגמה בסיסית ל- XLOOKUP של אקסל
דוגמה 2 – XLOOKUP לפי כל עמודה שתרצו
ב-XLOOKUP קל מאוד לחפש לפי כל עמודה שרוצים.
אם נניח, אנחנו רוצים לחפש לפי העמודה האחרונה, אנחנו לא צריכים טריקים כמו ב-VLOOKUP . אלא פשוט אפשר לציין שמחפשים לפי העמודה האחרונה.
להלן דוגמה :
המטרה – למצוא את העובד לפי הסניף שאליו הוא משוייך.
לפי מה מחפשים ? - לפי התוכן בתא h8 , כלומר לפי שם הסניף.
איפה מחפשים ? - בעמודה של שמות הסניפים (=המדינות).איפה מוצאים ? – בעמודה של שמות העובדים , כי זה מה שמעניין אותנו .
דוגמה ל- XLOOKUP לפי כל עמודה שרוצים.
XLOOKUP – מחזירה כמה שורות יחד
אחד הדברים היפים ב-XLOOKUP, הוא שכמו הנוסחאות החדשות האחרות של office 365 , גם XLOOKUP, יכולה להחזיר כמה שורות בבת אחת.
בואו נראה דוגמא :
נניח ויש לנו טבלה כזו :
מהטבלה הזו אנחנו נרצה להחזיר 3 מדדים בבת אחת - זה אפשרי באמצעות XLOOKUP
ואנחנו רוצים למצוא לפי היום בשבוע, את כל 3 הערכים – טמפרטורה, ביגוד מתאים, וכיוון הרוח.
אז במצב רגיל, למשל עם Index+Match , היינו צריכים לכתוב נוסחה שונה עבור כל אחד מהמדדים השונים, אבל לעומת זאת, ב-XLOOKUP, מספיק שכאשר אנחנו ממלאים את הפרמטר של “איפה למצוא” – נסמן את 3 השורות, והנוסחה בעצמה, תבין שהיא צריכה להביא את הערכים המתאימים, ולמקם אותם שורה מתחת לשורה.
המטרה – למצוא את 3 המדדים עבור יום מסוים ( טמפרטורה, ביגוד, כיוון רוח).
לפי מה מחפשים - לפי התא שמכיל את שם היום בשבוע.
איפה מחפשים - בשורה שמכילה את ימי השבוע – שורה 2.איפה מוצאים – בכל השורות 3,4,5
איך להחזיר כמה שורות בבת אחת באמצעות XLOOKUP של אקסל
סיכום - XLOOKUP
במדריך הזה, ראינו באמצעות 3 דוגמאות, איך לשלוף מידע באקסל באמצעות נוסחת XLOOKUP.
הבנו כיצד היא יותר טובה מנוסחת VLOOKUP, וראינו את 2 היתרונות המרכזיים שלה - הפרדה בין מה שמחפשים למה שמוצאים, ואפשרות יעילה להחזיר כמה שורות בבת אחת.
לדף תרגול ל- XLOOKUP לחצו כאן.
בהצלחה!
