פונקציית XLOOKUP

השתמש בפונקציה XLOOKUP כדי למצוא דברים בטבלה או בטווח לפי שורה. לדוגמה, חפש את המחיר של חלק כלי רכב לפי מספר החלק, או חפש שם עובד בהתבסס על מזהה העובד שלו. באמצעות XLOOKUP, באפשרותך לחפש בעמודה אחת מונח חיפוש ולהחזיר תוצאה מאותה שורה בעמודה אחרת, ללא קשר לצד שבו נמצאת העמודה המוחזרת.

: XLOOKUP אינו זמין ב- Excel 2016 וב- Excel 2019. עם זאת, ייתכן שתיתקל במצב של שימוש בחוברת עבודה ב- Excel 2016 או ב- Excel 2019 עם הפונקציה XLOOKUP, אם היא נוצרה על-ידי מישהו אחר המשתמש בגירסה חדשה יותר של Excel.

הדפדפן שלך אינו תומך בווידאו. התקן את Microsoft Silverlight‏, Adobe Flash Player או Internet Explorer 9.

תחביר

הפונקציה XLOOKUP מחפשת טווח או מערך ולאחר מכן מחזירה את הפריט המתאים להתאמה הראשונה שהיא מוצאת. אם לא קיימת התאמה, XLOOKUP יכולה להחזיר את ההתאמה הקרובה ביותר (משוערת).

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

ארגומנט	תיאור
lookup_value חובה*	הערך לחיפוש אם הוא מושמט, הפונקציה XLOOKUP מחזירה תאים ריקים שהיא מוצאת lookup_array*.
lookup_array נדרש	המערך או הטווח לחיפוש
return_array נדרש	המערך או הטווח להחזרה
[if_not_found] אופציונלי	כאשר לא נמצאה התאמה חוקית, החזר את הטקסט [if_not_found] שאתה מספק. אם לא נמצאה התאמה חוקית, ו- [if_not_found] חסר, #N/A מוחזרת.
[match_mode] אופציונלי	ציין את סוג ההתאמה: 0 - התאמה מדויקת. אם לא נמצאו פריטים, #N/A. זו ברירת המחדל. -1 - התאמה מדויקת. אם לא נמצאו פריטים, החזר את הפריט הקטן הבא. 1 - התאמה מדויקת. אם לא נמצאו פריטים, החזר את הפריט הגדול הבא. 2 - התאמה של תווים כלליים כאשר ל- *, ?, ו- ~ יש משמעות מיוחדת.
[search_mode] אופציונלי	ציין את מצב החיפוש שבו יש להשתמש: 1 - בצע חיפוש המתחיל בפריט הראשון. זו ברירת המחדל. -1 - בצע חיפוש הפוך החל מהפריט האחרון. 2 - בצע חיפוש בינארי ש מסתמך lookup_array מיון בסדר עולה. אם לא ימוינו, יוחזרו תוצאות לא חוקיות. -2 - בצע חיפוש בינארי lookup_array ממוינות בסדר יורד. אם לא ימוינו, יוחזרו תוצאות לא חוקיות.

ארגומנט

תיאור

lookup_value

חובה*

הערך לחיפוש *אם הוא מושמט, הפונקציה XLOOKUP מחזירה תאים ריקים שהיא מוצאת lookup_array.

lookup_array

נדרש

המערך או הטווח לחיפוש

return_array

נדרש

המערך או הטווח להחזרה

[if_not_found]

אופציונלי

כאשר לא נמצאה התאמה חוקית, החזר את הטקסט [if_not_found] שאתה מספק.

אם לא נמצאה התאמה חוקית, ו- [if_not_found] חסר, #N/A מוחזרת.

[match_mode]

אופציונלי

ציין את סוג ההתאמה:

0 - התאמה מדויקת. אם לא נמצאו פריטים, #N/A. זו ברירת המחדל.

-1 - התאמה מדויקת. אם לא נמצאו פריטים, החזר את הפריט הקטן הבא.

1 - התאמה מדויקת. אם לא נמצאו פריטים, החזר את הפריט הגדול הבא.

2 - התאמה של תווים כלליים כאשר ל- *, ?, ו- ~ יש משמעות מיוחדת.

[search_mode]

אופציונלי

ציין את מצב החיפוש שבו יש להשתמש:

1 - בצע חיפוש המתחיל בפריט הראשון. זו ברירת המחדל.

-1 - בצע חיפוש הפוך החל מהפריט האחרון.

2 - בצע חיפוש בינארי ש מסתמך lookup_array מיון בסדר עולה. אם לא ימוינו, יוחזרו תוצאות לא חוקיות.

-2 - בצע חיפוש בינארי lookup_array ממוינות בסדר יורד. אם לא ימוינו, יוחזרו תוצאות לא חוקיות.

דוגמאות

דוגמה 1 משתמשת ב- XLOOKUP כדי לחפש שם מדינה בטווח ולאחר מכן להחזיר את קידומת מדינת הטלפון שלה. הוא כולל את lookup_value (תא F2), lookup_array (טווח B2:B11) וארגומנטים return_array (טווח D2:D11). היא אינה כוללת את הארגומנט match_mode , מכיוון ש- XLOOKUP מפיקה התאמה מדויקת כברירת מחדל.

דוגמה של הפונקציה XLOOKUP המשמשת להחזרת שם עובד ומחלקה בהתבסס על מזהה עובד. הנוסחה היא =XLOOKUP(B2,B5:B14,C5:C14)

: הפונקציה XLOOKUP משתמשת במערך בדיקת מידע ומערך החזרה, בעוד ש- VLOOKUP משתמשת במערך טבלה בודד ואחריו מספר אינדקס של עמודה. הנוסחה המקבילה VLOOKUP במקרה זה תהיה: =VLOOKUP(F2,B2:D11,3,FALSE)

———————————————————————————

דוגמה 2 בודקת פרטי עובד בהתבסס על מספר מזהה עובד. בניגוד ל- VLOOKUP, XLOOKUP יכולה להחזיר מערך עם פריטים מרובים, כך שנוסחה בודדת יכולה להחזיר הן שם עובד והן מחלקה מתאים C5:D14.

דוגמה של הפונקציה XLOOKUP המשמשת להחזרת שם עובד ומחלקה בהתבסס על IDt של עובד. הנוסחה היא: =XLOOKUP(B2,B5:B14,C5:D14,0,1)

———————————————————————————

דוגמה 3 מוסיפה if_not_found ארגומנט לדוגמה הקודמת.

דוגמה של הפונקציה XLOOKUP המשמשת להחזרת שם עובד ומחלקה בהתבסס על מזהה עובד עם if_not_found הארגומנט. הנוסחה היא =XLOOKUP(B2,B5:B14,C5:D14,0,1,"העובד לא נמצא")

———————————————————————————

דוגמה 4 בודקת בעמודה C את ההכנסה האישית שהוזנה בתא E2, ומוצאת שיעור מס תואם בעמודה B. היא מגדירה את if_not_found הארגומנט להחזיר 0 (אפס) אם לא נמצא דבר. הארגומנט match_mode מוגדר ל- 1, כלומר הפונקציה תחפש התאמה מדויקת, ואם היא לא יכולה למצוא התאמה, היא תחזיר את הפריט הגדול הבא. לבסוף, search_mode הארגומנט מוגדר ל - 1, כלומר הפונקציה תחפש מהפריט הראשון עד האחרון.

תמונה של הפונקציה XLOOKUP המשמשת להחזרת שיעור מס בהתבסס על הכנסה מרבית. זוהי התאמה משוערת. הנוסחה היא: =XLOOKUP(E2,C2:C7,B2:B7,1,1)

: העמודה lookup_array XARRAY נמצאת מימין לעמודת return_array, בעוד ש- VLOOKUP יכולה להיראות רק משמאל לימין.

———————————————————————————

דוגמה 5 משתמש בפונקציה XLOOKUP מקוננת כדי לבצע התאמה אנכית ואופקית. תחילה הוא מחפש את Gross Profit בעמודה B, ולאחר מכן מחפש את Qtr1 בשורה העליונה של הטבלה (טווח C5:F5), ולבסוף מחזיר את הערך בהצטלבות של שתי האפשרויות. פעולה זו דומה לשימוש בפונקציות INDEX ו - MATCH יחד.

: באפשרותך גם להשתמש ב- XLOOKUP כדי להחליף את הפונקציה HLOOKUP .

תמונה של הפונקציה XLOOKUP המשמשת להחזרת נתונים אופקיים מטבלה על-ידי קינון 2 XLOOKUPS. הנוסחה היא: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17))

: הנוסחה בתאים D3:F3 היא: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17)).

———————————————————————————

דוגמה 6 משתמש בפונקציה SUM ושתי פונקציות XLOOKUP מקוננות, כדי לסכם את כל הערכים בין שני טווחים. במקרה זה, אנחנו רוצים לסכם את הערכים עבור ענבים, בננות ולכלול אגסים, שביניהם.

שימוש ב- XLOOKUP עם SUM כדי לסכם טווח ערכים השייכים לשתי בחירות

הנוסחה בתא E3 היא: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

איך זה עובד? הפונקציה XLOOKUP מחזירה טווח, ולכן כאשר היא מחשבת, הנוסחה מסתיימת כך: =SUM($E$7:$E$9). באפשרותך לראות כיצד פעולה זו פועלת בעצמך על-ידי בחירת תא עם נוסחת XLOOKUP הדומה לזו, לאחר מכן בחר נוסחאות > ביקורתנוסחאות >הערכת נוסחה ולאחר מכן בחר הערך כדי לבצע את החישוב.

: הודות ל- Microsoft Excel MVP, ביל ג'לן, שהצעת דוגמה זו.

———————————————————————————

למידע נוסף

תוכל תמיד לשאול מומחה ב- Excel Tech Community או לקבל תמיכה בקהילת Answers.

הפונקציה XMATCH

פונקציות של Excel (בסדר אלפביתי)

פונקציות של Excel (לפי קטגוריה)

תחביר

דוגמאות

למידע נוסף

זקוק לעזרה נוספת?

מעוניין באפשרויות נוספות?

האם מידע זה היה שימושי?

תודה על המשוב!