XLOOKUP
פונקציית XLOOKUP של אקסל היא כלי רב עוצמה המאפשר לך לחפש ערך ספציפי בטבלת נתונים ולהחזיר ערך מתאים מאותה שורה או משורה אחרת. פונקציה זו דומה ל-VLOOKUP, אך יש לה יותר גמישות ופונקציונליות.
אחד היתרונות המרכזיים של XLOOKUP הוא שהוא מאפשר לך לחפש ערכים בטווחים אופקיים ואנכיים כאחד. זה אומר שאפשר להשתמש בו כדי לחפש ערכים בשורות וגם בעמודות, ותוכלו להחזיר ערכים משורות או עמודות. זאת בניגוד ל-VLOOKUP, שיכול לחפש רק ערכים בעמודות ויכול להחזיר רק ערכים מעמודות מימין לעמודת החיפוש.
יתרון נוסף של 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 באקסל:
=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 שאינה תומכת בפונקציה, מומלץ לשקול שדרוג כדי לנצל את התכונה הזו.
