לְהִצטַיֵן

פונקציית XLOOKUP של Excel

Excel Xlookup Function

פונקציית XLOOKUP של Excelסיכום

הפונקציה Excel XLOOKUP היא תחליף מודרני וגמיש לפונקציות ישנות יותר כמו VLOOKUP, HLOOKUP ו- LOOKUP. XLOOKUP תומך בהתאמה משוערת ומדויקת, תווים כלליים (*?) להתאמות חלקיות וחיפושים בטווחים אנכיים או אופקיים.





ערכי חיפוש למטרה בטווח או מערך ערך החזרה התאמת ערך (ים) מערך החזרה Syntax = XLOOKUP (חיפוש, חיפוש_ערך, החזרה_מערך, [not_found], [match_mode], [search_mode]) ארגומנטים
  • הבט מעלה - ערך החיפוש.
  • חיפוש_מערך - המערך או הטווח לחיפוש.
  • מערך החזרה - המערך או הטווח שיש להחזיר.
  • לא נמצא - [אופציונלי] ערך להחזרה אם לא נמצאה התאמה.
  • מצב התאמה - [אופציונלי] 0 = התאמה מדויקת (ברירת מחדל), -1 = התאמה מדויקת או הקטנה הבאה, 1 = התאמה מדויקת או גדולה יותר, 2 = התאמת תווים כלליים.
  • מצב חיפוש_ -[אופציונלי] 1 = חיפוש מהראשון (ברירת מחדל), -1 = חיפוש מהאחרון, 2 = חיפוש בינארי עולה, -2 = חיפוש בינארי יורד.
גִרְסָה Excel 365 הערות שימוש

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)

XLOOKUP - דוגמה בסיסית להתאמה מדויקת

הסבר מפורט יותר כאן .

דוגמה מספר 2 - התאמה בסיסית משוערת

כדי לאפשר התאמה משוערת, ספק ערך עבור הארגומנט 'התאמת_מצב'. בדוגמה למטה, XLOOKUP משמש לחישוב הנחה על בסיס כמות, הדורשת התאמה משוערת. הנוסחה ב- F5 מספקת -1 עבור match_mode כדי לאפשר התאמה משוערת עם התנהגות 'התאמה מדויקת או הקטנה הבאה':

 
= XLOOKUP (E5,B5:B9,C5:C9,,-1)

XLOOKUP - דוגמה בסיסית להתאמה משוערת

הסבר מפורט יותר כאן .

דוגמה מס '3 - ערכים מרובים

XLOOKUP יכול להחזיר יותר מערך אחד בו זמנית לאותה התאמה. הדוגמה להלן מראה כיצד ניתן להגדיר XLOOKUP להחזיר שלושה ערכים תואמים עם נוסחה אחת. הנוסחה ב- C5 היא:

 
= XLOOKUP (B5,B8:B15,C8:E15)

XLOOKUP - דוגמה לערך מרובה

שימו לב מערך ההחזרה (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))

XLOOKUP - דוגמא לחיפוש דו כיווני

פרטים נוספים כאן .

דוגמה מס '5 - הודעה לא נמצאה

בדומה לפונקציות חיפוש אחרות, אם XLOOKUP אינו מוצא ערך, הוא מחזיר את השגיאה #N/A. כדי להציג הודעה מותאמת אישית במקום #N/A, ספק ערך עבור הארגומנט 'לא נמצא' האופציונלי, המצורף במרכאות כפולות (''). לדוגמה, כדי להציג את 'לא נמצא' כאשר לא נמצא סרט תואם, בהתבסס על גליון העבודה שלהלן, השתמש ב:

 
= XLOOKUP (H4,B5:B9,E5:E9,'Not found')

XLOOKUP - דוגמה לא נמצאה

אתה יכול להתאים אישית את ההודעה הזו כרצונך: 'אין התאמה', 'סרט לא נמצא' וכו '.

טקסט התאמה מצטיין בשתי עמודות

דוגמה מס '6 - קריטריונים מורכבים

עם היכולת להתמודד עם מערכים באופן מקורי, ניתן להשתמש ב- XLOOKUP עם קריטריונים מורכבים. בדוגמה למטה, XLOOKUP תואם את הרשומה הראשונה שבה: החשבון מתחיל ב- 'x' ו האזור הוא 'מזרח' ו החודש אינו אפריל:

 
= XLOOKUP (1,( LEFT (B5:B16)='x')*(C5:C16='east')* NOT ( MONTH (D5:D16)=4),B5:E16)

XLOOKUP - דוגמה לקריטריונים מורכבים

פרטים: (1) דוגמא פשוטה , (2) דוגמא מורכבת יותר .

הטבות XLOOKUP

XLOOKUP מציע מספר יתרונות חשובים, במיוחד בהשוואה ל- VLOOKUP:

  • XLOOKUP יכול לחפש נתונים מימין או שמאלה של ערכי חיפוש
  • XLOOKUP יכול להחזיר מספר תוצאות (דוגמה מס '3 למעלה)
  • ברירת המחדל של XLOOKUP היא התאמה מדויקת (ברירת מחדל של VLOOKUP לערך משוער)
  • XLOOKUP יכול לעבוד עם נתונים אנכיים ואופקיים
  • XLOOKUP יכול לבצע חיפוש הפוך (אחרון לראשון)
  • XLOOKUP יכול להחזיר שלם שורות או עמודות , לא רק ערך אחד
  • XLOOKUP יכול לעבוד עם מערכים באופן מקורי ליישם קריטריונים מורכבים

הערות

  1. XLOOKUP יכול לעבוד עם מערכים אנכיים ואופקיים כאחד.
  2. XLOOKUP יחזיר #לא רלוונטי אם ערך החיפוש לא נמצא.
  3. ה חיפוש_מערך חייב להיות בעל ממד התואם את מערך החזרה טיעון, אחרת XLOOKUP יחזיר את #VALUE!
  4. אם נעשה שימוש ב- XLOOKUP בין חוברות עבודה, שני חוברות העבודה חייבות להיות פתוחות, אחרת XLOOKUP יחזיר את #REF !.
  5. כמו פונקציית INDEX , XLOOKUP מחזיר א התייחסות כתוצאה.
XLOOKUP היא פונקציה חדשה הזמינה ב- Excel 365 רק.


^