פונקציית XLOOKUP
השתמש בפונקציה 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 הנדרש* |
הערך לחיפוש |
|
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 מקוננת כדי לבצע התאמה אנכית ואופקית. תחילה הוא מחפש את Gross Profit בעמודה 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 הדומה לזו, לאחר מכן בחר נוסחאות > ביקורתנוסחאות >הערכת נוסחה ולאחר מכן בחר הערך כדי לבצע את החישוב.
הערה: הודות ל- Microsoft Excel MVP, ביל ג'לן, שהצעת דוגמה זו.
———————————————————————————
למידע נוסף
תוכל תמיד לשאול מומחה ב- Excel Tech Community או לקבל תמיכה בקהילת Answers.
זקוק לעזרה נוספת?
מעוניין באפשרויות נוספות?
גלה את יתרונות המנוי, עיין בקורסי הדרכה, למד כיצד לאבטח את המכשיר שלך ועוד.
קהילות עוזרות לך לשאול שאלות ולהשיב עליהן, לתת משוב ולשמוע ממומחים בעלי ידע עשיר.
