הפונקציה Excel XLOOKUP היא תחליף מודרני וגמיש לפונקציות ישנות יותר כמו VLOOKUP, HLOOKUP ו- LOOKUP. XLOOKUP תומך בהתאמה משוערת ומדויקת, תווים כלליים (*?) להתאמות חלקיות וחיפושים בטווחים אנכיים או אופקיים.
ערכי חיפוש למטרה בטווח או מערך ערך החזרה התאמת ערך (ים) מערך החזרה Syntax = XLOOKUP (חיפוש, חיפוש_ערך, החזרה_מערך, [not_found], [match_mode], [search_mode]) ארגומנטים
- הבט מעלה - ערך החיפוש.
- חיפוש_מערך - המערך או הטווח לחיפוש.
- מערך החזרה - המערך או הטווח שיש להחזיר.
- לא נמצא - [אופציונלי] ערך להחזרה אם לא נמצאה התאמה.
- מצב התאמה - [אופציונלי] 0 = התאמה מדויקת (ברירת מחדל), -1 = התאמה מדויקת או הקטנה הבאה, 1 = התאמה מדויקת או גדולה יותר, 2 = התאמת תווים כלליים.
- מצב חיפוש_ -[אופציונלי] 1 = חיפוש מהראשון (ברירת מחדל), -1 = חיפוש מהאחרון, 2 = חיפוש בינארי עולה, -2 = חיפוש בינארי יורד.
XLOOKUP הוא תחליף מודרני לפונקציית VLOOKUP. זוהי פונקציה גמישה ורב -תכליתית שניתן להשתמש בה במגוון רחב של מצבים.
XLOOKUP יכול למצוא ערכים בטווחים אנכיים או אופקיים, יכול לבצע התאמות משוערות ומדויקות, ותומך בתווים כלליים (*?) להתאמות חלקיות. בנוסף, XLOOKUP יכול לחפש נתונים החל מהערך הראשון או מהערך האחרון (ראה להלן סוג ההתאמה ופרטי מצב החיפוש). בהשוואה לפונקציות ישנות יותר כמו VLOOKUP , HLOOKUP , ו הבט מעלה , XLOOKUP מציע מספר יתרונות מרכזיים .
הודעה לא נמצאה
כאשר XLOOKUP לא מצליח למצוא התאמה, היא מחזירה את השגיאה #N/A, כמו פונקציות התאמה אחרות ב- Excel. שלא כמו פונקציות ההתאמה האחרות, XLOOKUP תומך בארגומנט אופציונלי הנקרא לא נמצא שניתן להשתמש בהם כדי לעקוף את שגיאת ה- #N/A כאשר היא תופיע אחרת. ערכים אופייניים עבור לא נמצא יכול להיות 'לא נמצא', 'אין התאמה', 'אין תוצאה' וכו 'בעת מתן ערך עבור לא נמצא , צרף את הטקסט במרכאות כפולות ('').
הערה: היזהר אם אתה מספק מחרוזת ריקה ('') עבור not_found. אם לא נמצאה התאמה, XLOOKUP לא יציג כלום במקום #N/A. אם ברצונך לראות את שגיאת ה- #N/A כאשר התאמה לא נמצאה, השמט לחלוטין את הטיעון.
סוג התאמה
כברירת מחדל, XLOOKUP יבצע התאמה מדוייקת . התנהגות ההתאמה נשלטת על ידי טיעון אופציונאלי הנקרא סוג התאמה, שיש לו את האפשרויות הבאות:
מסנן דוחות טבלת ציר גדול מ-
סוג התאמה | התנהגות |
---|---|
0 (ברירת מחדל) | התאמה מדוייקת. יחזור #N/A אם אין התאמה. |
-1 | התאמה מדויקת או פריט קטן יותר. |
1 | התאמה מדויקת או הפריט הגדול הבא. |
2 | התאמת תווים כלליים (*,?, ~) |
מצב חיפוש
כברירת מחדל, XLOOKUP יתחיל להתאים מערך הנתונים הראשון. התנהגות החיפוש נשלטת על ידי ארגומנט אופציונלי הנקרא מצב חיפוש_ , המספק את האפשרויות הבאות:
מצב חיפוש | התנהגות |
---|---|
1 (ברירת מחדל) | חפש בערך ראשון |
-1 | חיפוש מהערך האחרון (הפוך) |
2 | ערכי חיפוש בינארי ממוינים בסדר עולה |
-2 | ערכי חיפוש בינארי ממוינים בסדר יורד |
חיפושים בינאריים הם מהירים מאוד, אבל יש למיין את הנתונים כנדרש . אם הנתונים אינם ממוינים כראוי, חיפוש בינארי יכול להחזיר תוצאות לא חוקיות שנראות תקינות לחלוטין.
דוגמה מס '1 - התאמה בסיסית מדויקת
כברירת מחדל, XLOOKUP יבצע התאמה מדויקת. בדוגמה למטה, XLOOKUP משמש לאחזור מכירות בהתבסס על התאמה מדויקת ב- Movie. הנוסחה ב- H5 היא:
= XLOOKUP (H4,B5:B9,E5:E9)
הסבר מפורט יותר כאן .
דוגמה מספר 2 - התאמה בסיסית משוערת
כדי לאפשר התאמה משוערת, ספק ערך עבור הארגומנט 'התאמת_מצב'. בדוגמה למטה, XLOOKUP משמש לחישוב הנחה על בסיס כמות, הדורשת התאמה משוערת. הנוסחה ב- F5 מספקת -1 עבור match_mode כדי לאפשר התאמה משוערת עם התנהגות 'התאמה מדויקת או הקטנה הבאה':
= XLOOKUP (E5,B5:B9,C5:C9,,-1)
הסבר מפורט יותר כאן .
דוגמה מס '3 - ערכים מרובים
XLOOKUP יכול להחזיר יותר מערך אחד בו זמנית לאותה התאמה. הדוגמה להלן מראה כיצד ניתן להגדיר XLOOKUP להחזיר שלושה ערכים תואמים עם נוסחה אחת. הנוסחה ב- C5 היא:
= XLOOKUP (B5,B8:B15,C8:E15)
שימו לב מערך ההחזרה (C8: E15) כולל 3 עמודות: ראשית, אחרון, מחלקה. כל שלושת הערכים מוחזרים ו- מִשְׂחָק לטווח C5: E5.
דוגמה מס '4 - חיפוש דו כיווני
ניתן להשתמש ב- XLOOKUP לביצוע חיפוש דו כיווני, על ידי קינון XLOOKUP אחד בתוך אחר. בדוגמה למטה, ה- XLOOKUP ה'פנימי 'מאחזר שורה שלמה (כל הערכים עבור Glass), הנמסרת ל- XLOOKUP' החיצוני 'כמערך ההחזרה. XLOOKUP החיצוני מוצא את הקבוצה המתאימה (B) ומחזיר את הערך המתאים (17.25) כתוצאה הסופית.
= XLOOKUP (I6,C4:F4, XLOOKUP (I5,B5:B9,C5:F9))
פרטים נוספים כאן .
דוגמה מס '5 - הודעה לא נמצאה
בדומה לפונקציות חיפוש אחרות, אם XLOOKUP אינו מוצא ערך, הוא מחזיר את השגיאה #N/A. כדי להציג הודעה מותאמת אישית במקום #N/A, ספק ערך עבור הארגומנט 'לא נמצא' האופציונלי, המצורף במרכאות כפולות (''). לדוגמה, כדי להציג את 'לא נמצא' כאשר לא נמצא סרט תואם, בהתבסס על גליון העבודה שלהלן, השתמש ב:
= XLOOKUP (H4,B5:B9,E5:E9,'Not found')
אתה יכול להתאים אישית את ההודעה הזו כרצונך: 'אין התאמה', 'סרט לא נמצא' וכו '.
טקסט התאמה מצטיין בשתי עמודות
דוגמה מס '6 - קריטריונים מורכבים
עם היכולת להתמודד עם מערכים באופן מקורי, ניתן להשתמש ב- XLOOKUP עם קריטריונים מורכבים. בדוגמה למטה, XLOOKUP תואם את הרשומה הראשונה שבה: החשבון מתחיל ב- 'x' ו האזור הוא 'מזרח' ו החודש אינו אפריל:
= XLOOKUP (1,( LEFT (B5:B16)='x')*(C5:C16='east')* NOT ( MONTH (D5:D16)=4),B5:E16)
פרטים: (1) דוגמא פשוטה , (2) דוגמא מורכבת יותר .
הטבות XLOOKUP
XLOOKUP מציע מספר יתרונות חשובים, במיוחד בהשוואה ל- VLOOKUP:
- XLOOKUP יכול לחפש נתונים מימין או שמאלה של ערכי חיפוש
- XLOOKUP יכול להחזיר מספר תוצאות (דוגמה מס '3 למעלה)
- ברירת המחדל של XLOOKUP היא התאמה מדויקת (ברירת מחדל של VLOOKUP לערך משוער)
- XLOOKUP יכול לעבוד עם נתונים אנכיים ואופקיים
- XLOOKUP יכול לבצע חיפוש הפוך (אחרון לראשון)
- XLOOKUP יכול להחזיר שלם שורות או עמודות , לא רק ערך אחד
- XLOOKUP יכול לעבוד עם מערכים באופן מקורי ליישם קריטריונים מורכבים
הערות
- XLOOKUP יכול לעבוד עם מערכים אנכיים ואופקיים כאחד.
- XLOOKUP יחזיר #לא רלוונטי אם ערך החיפוש לא נמצא.
- ה חיפוש_מערך חייב להיות בעל ממד התואם את מערך החזרה טיעון, אחרת XLOOKUP יחזיר את #VALUE!
- אם נעשה שימוש ב- XLOOKUP בין חוברות עבודה, שני חוברות העבודה חייבות להיות פתוחות, אחרת XLOOKUP יחזיר את #REF !.
- כמו פונקציית INDEX , XLOOKUP מחזיר א התייחסות כתוצאה.