XLOOKUP
פונקציית XLOOKUP של אקסל היא כלי רב עוצמה המאפשר לך לחפש ערך ספציפי בטבלת נתונים ולהחזיר ערך מתאים מאותה שורה או משורה אחרת. פונקציה זו דומה ל-VLOOKUP, אך יש לה יותר גמישות ופונקציונליות.
יתרונות XLOOKUP
אחד היתרונות המרכזיים של XLOOKUP הוא שהוא מאפשר לך לחפש ערכים בטווחים אופקיים ואנכיים כאחד. זה אומר שאפשר להשתמש בו כדי לחפש ערכים בשורות וגם בעמודות, ותוכלו להחזיר ערכים משורות או עמודות. זאת בניגוד ל-VLOOKUP, שיכול לחפש רק ערכים בעמודות ויכול להחזיר רק ערכים מעמודות מימין לעמודת החיפוש.
יתרון נוסף של XLOOKUP הוא שהוא מאפשר לך לציין ערך ברירת מחדל להחזרה אם החיפוש נכשל. זה שימושי אם תרצו להבטיח שהנוסחה תמיד תחזיר תוצאה. גם אם ערך הבדיקה לא נמצא בטווח.
בנוסף לתכונות הבסיסיות הללו, ל-XLOOKUP יש גם מספר אפשרויות מתקדמות המאפשרות לך להתאים אישית את הדרך בה הוא מחפש ומחזיר ערכים. לדוגמה, אתה יכול לציין אם לחפש ערכים בטווח החיפוש בסדר עולה או יורד, או שאתה יכול להשתמש בתווים כלליים כדי לבצע התאמה חלקית.
איך להשתמש בXLOOKUP
כדי להשתמש ב-XLOOKUP, עליך לציין 3 ארגומנטים + 3 ארגומטים אפצונלייים:
- הערך שיש לחפש
- טווח החיפוש
- טווח בו נמצאת התוצרה
- לא נמצא [אופציונלי]
- מצב ההתאמה [אופציונלי] מאפשר לך לציין אם להחזיר את ההתאמה הקרובה ביותר או התאמה מדויקת,
- מצב חיפוש
לא נמצא [אופציונלי]
כאשר XLOOKUP לא יכול למצוא התאמה, הוא מחזיר את השגיאה N/A#, כמו פונקציות התאמה אחרות באקסל. בניגוד לפונקציות ההתאמה האחרות, XLOOKUP תומך בארגומנט אופציונלי בשם not_found שניתן להשתמש בו כדי לעקוף את השגיאה N/A# כאשר אחרת היא תופיע. ערכים טיפוסיים עבור not_found עשויים להיות “לא נמצא”, “אין התאמה”, “ללא תוצאה” וכו’. כאשר מספקים ערך עבור not_found, הוסיפו את הטקסט במירכאות כפולות (“”).
הערה: היזהר אם אתה תכתבו מחרוזת ריקה (“”) עבור not_found. במידה לא נמצאה התאמה, XLOOKUP לא יציג דבר במקום N/A#. אם ברצונך לראות את השגיאה N/A#, השמט/י לחלוטין את הארגומנט.
מצב התאמה [אופציונלי]
כברירת מחדל, XLOOKUP יבצע התאמה מדויקת. התנהגות ההתאמה נשלטת על ידי ארגומנט אופציונלי בשם match_type, בעל האפשרויות הבאות:
| מצב התאמה | תוצאה/התנהגות |
|---|---|
| 0 (ברירת מחדל) | התאמה מדוייקת. יחזיר N/A# אם אין התאמה. |
| 1 | התאמה מדויקת או פריט קטן הבא. |
| 2 | התאמה מדויקת או פריט גדול יותר. |
| 3 | תווים כללים (*, ?, ~) |
1. דוגמה פשוטה של XLOOKUP בהתאמה הקרובה ביותר
כברירת מחדל, XLOOKUP יבצע התאמה מדויקת. בדוגמה למטה, XLOOKUP משמש לאחזור הכנסות על סמך התאמה מדויקת בסרט. הנוסחה ב-H5 היא:
=XLOOKUP(H4,B5:B9,E5:E9)
2. דוגמה פשוטה של בהתאמה הקרובה ביותר
כדי לאפשר התאמה משוערת, ספק ערך עבור הארגומנט match_mode. בדוגמה למטה, הפונקציה משמשת לחישוב הנחה על סמך כמות, הדורשת התאמה משוערת. הנוסחה ב3F5 מספקת -1 עבור match_mode כדי לאפשר התאמה משוערת עם התנהגות “התאמה מדויקת או הקטנה הבאה”:
=XLOOKUP(F3,B3:B7,C3:C7,,-1)
3. דוגמה לערכים כפולים ב XLOOKUP
XLOOKUP מסוגל להחזיר יותר מערך אחד בו-זמנית (עבור אותה התאמה). הדוגמה להלן מראה כיצד ניתן להגדיר את XLOOKUP כך שיחזיר שלושה ערכים תואמים עם נוסחה אחת. הנוסחה ב4C5 היא:
=XLOOKUP(B4,B7:B16,C7:E1)6
שימו לב שמערך ההחזרה (C7:E16) כולל 3 עמודות: שם, משפחה, מחלקה. כל שלושת הערכים מוחזרים ונשפכים לטווח C4:E4.
4. חיפוש דו-כיווני
ניתן להשתמש בפומקציה לביצוע חיפוש דו-כיווני, על ידי קינון XLOOKUP אחד בתוך השני. בדוגמה למטה, הפונקציה ה”פנימית” מאחזרת שורה שלמה (כל הערכים עבור מתכת). השורה מועברת ל-XLOOKUP ה”חיצוני” כמערך ההחזרה. הפונקציה החיצונית מוצא את הקבוצה המתאימה (B) ומחזיר את הערך המתאים (34.14) כתוצאה הסופית.
=XLOOKUP(I5,C3:F3,XLOOKUP(I4,B4:B8,C4:F8))
5. ערך לא נמצא
אם XLOOKUP לא מוצא ערך, הוא מחזיר את השגיאה N/A#. כדי להציג הודעה מותאמת אישית במקום N/A#, ספק ערך עבור הארגומנט האופציונלי “not_found”, מוקף במירכאות כפולות (“”). לדוגמה, כדי להציג את “לא נמצא” כאשר לא נמצא סרט תואם, השתמשו בנוסחה הבא:
=XLOOKUP(H3,B4:B9,E4:E9,"לא קיים")
אפשר לבצע התאמה אישית להודעה: “אין התאמה”, “מוצר לא זמין” וכו’.
6. קריטריונים מורכבים
בשל היכולת לטפל במערכים באופן מקורי, ניתן להשתמש ב-XLOOKUP עם קריטריונים מורכבים. בדוגמה למטה, הפונקציה תואמת את הרשומה הראשונה שבה: החשבון מתחיל ב-“x” והאזור הוא “צפון” והחודש אינו אפריל:
=XLOOKUP(1,(LEFT(B5:B12)="x")*(C5:C12="צפון")*NOT(MONTH(D5:D12)=2),B5:E12)
מדוע כדאי לי להשתמש ב-XLOOKUP?
- גמישות משופרת: ל-XLOOKUP יש מספר אפשרויות מתקדמות המאפשרות לך להתאים אישית את הדרך בה הוא מחפש ומחזיר ערכים. לדוגמה, אתה יכול לציין אם לחפש ערכים בסדר עולה או יורד, או שאתה יכול להשתמש בתווים כלליים כדי לבצע התאמה חלקית.
- פונקציונליות משופרת: בניגוד ל-VLOOKUP, שיכול לחפש רק ערכים בעמודות ויכול להחזיר רק ערכים מעמודות מימין לעמודת החיפוש, XLOOKUP מאפשר לך לחפש ערכים בטווחים אופקיים ואנכיים ולהחזיר ערכים מכל אחד מהם שורות או עמודות.
- דיוק משופר: לפונקציה יש תכונה מובנית לטיפול בשגיאות המאפשרת לציין ערך ברירת מחדל להחזרה אם החיפוש נכשל. זה מבטיח שהנוסחאות שלך תמיד מחזירות תוצאה. גם אם ערך הבדיקה לא נמצא בטווח הבדיקה.
- ביצועים משופרים: XLOOKUP הוא בדרך כלל מהיר ויעיל יותר מ-VLOOKUP. במיוחד כאשר עובדים עם מסדי נתונים גדולים.
- תאימות משופרת: XLOOKUP זמין ב-Excel 365 ובגירסאות מאוחרות יותר. אם משתמשים בגרסה ישנה יותר של Excel שאינה תומכת בפונקציה, מומלץ לשקול שדרוג כדי לנצל את התכונה הזו.
