השתמש בפונקציה XLOOKUP כדי למצוא דברים בטבלה או בטווח לפי שורה. לדוגמה, חפש את המחיר של חלק מכלי רכב לפי מספר החלק, או חפש שם עובד בהתבסס על מזהה העובד שלו. באמצעות XLOOKUP, באפשרותך לחפש מונח חיפוש בעמודה אחת ולהחזיר תוצאה מאותה שורה בעמודה אחרת, ללא קשר לאיזה צד נמצאת עמודת ההחזרה.
הערה
XLOOKUP אינה זמינה ב- Excel 2016 וב- Excel 2019. עם זאת, אתה עשוי להיתקל במצב של שימוש בחוברת עבודה ב- Excel 2016 או Excel 2019 עם פונקציית XLOOKUP בתוכה, אם היא נוצרה על-ידי מישהו אחר באמצעות גירסה חדשה יותר של Excel.
תחביר
הפונקציה 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 בסדר יורד . אם הפונקציה לא ממוינת, יוחזרו תוצאות לא חוקיות. |
דוגמאות
דוגמה 1 משתמשת בפונקציה XLOOKUP כדי לחפש שם מדינה בטווח ולאחר מכן להחזיר את קידומת המדינה של הטלפון שלה. הוא כולל את הארגומנטים lookup_value (תא F2), lookup_array (טווח B2:B11) ו - return_array (טווח D2:D11). היא אינה כוללת את הארגומנט match_mode , מכיוון ש- XLOOKUP מייצרת התאמה מדויקת כברירת מחדל.
הערה
פונקציית XLOOKUP משתמשת במערך בדיקת מידע ובמערך החזרות, בעוד שהפונקציה VLOOKUP משתמשת במערך טבלה יחיד ואחריו מספר אינדקס של עמודה. נוסחת VLOOKUP המקבילה במקרה זה תהיה: =VLOOKUP(F2,B2:D11,3,FALSE)
———————————————————————————
דוגמה 2 בודקת מידע על עובד בהתבסס על מספר מזהה עובד. בניגוד ל- VLOOKUP, פונקציית XLOOKUP יכולה להחזיר מערך עם פריטים מרובים, כך שנוסחה אחת יכולה להחזיר הן את שם העובד והן את המחלקה מהתאים C5:D14.
———————————————————————————
דוגמה 3 מוסיפה ארגומנט if_not_found לדוגמה הקודמת.
———————————————————————————
דוגמה 4 מחפשת בעמודה C את ההכנסה האישית שהוזנה בתא E2, ומוצאת שיעור מס תואם בעמודה B. היא מגדירה את הארגומנט if_not_found להחזיר 0 (אפס) אם לא נמצא דבר. הארגומנט match_mode מוגדר ל 1- , כלומר הפונקציה תחפש התאמה מדויקת, ואם לא תמצא התאמה, היא תחזיר את הפריט הגדול הבא. לסיום, הארגומנט search_mode מוגדר ל - 1, שפירושו שהפונקציה תחפש מהפריט הראשון לאחרון.
הערה
העמודה lookup_array של XARRAY נמצאת משמאל לעמודה return_array , בעוד שהפונקציה VLOOKUP יכולה להסתכל רק משמאל לימין.
———————————————————————————
דוגמה 5 משתמשת בפונקציית XLOOKUP מקוננת כדי לבצע התאמה אנכית ואופקית. תחילה היא מחפשת רווח גולמי בעמודה B, לאחר מכן מחפשת את Qtr1 בשורה העליונה של הטבלה (בטווח C5:F5) ולבסוף מחזירה את הערך בהצטלבות של השניים. הדבר דומה לשימוש בפונקציות INDEX ו- MATCH יחד.
עצה
באפשרותך גם להשתמש בפונקציה XLOOKUP כדי להחליף את הפונקציה HLOOKUP .
הערה
הנוסחה בתאים D3:F3 היא: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17)).
———————————————————————————
דוגמה 6 משתמשת בפונקציה SUM ובשתי פונקציות XLOOKUP מקוננות כדי לסכם את כל הערכים בין שני טווחים. במקרה זה, אנו רוצים לסכם את הערכים עבור ענבים ובננות ולכלול אגסים, שנמצאים ביניהם.
הנוסחה בתא E3 היא: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
איך זה עובד? XLOOKUP מחזירה טווח, כך שכאשר היא מחושבת, הנוסחה נראית בסופו של דבר כך: =SUM($E$7:$E$9). באפשרותך לראות זאת בעצמך על-ידי בחירת תא עם נוסחת XLOOKUP הדומה לנוסחה זו, בחירת נוסחאות> ביקורת >נוסחאותהערכת נוסחה ולאחר מכן בחירת 'הערכה' כדי לבצע את שלב החישוב.
הערה
תודה ל- MVP של Microsoft Excel, ביל ג'לן, שהציע דוגמה זו.
———————————————————————————