פונקציית XLOOKUP

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

תמונה של המכשיר Surface Book

תחביר

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

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

ארגומנט

תיאור

lookup_value

נדרש

ערך

בדיקת מידע * אם הושמט, תא ריק משמש להתאמה.   

הערה: מחרוזת ריקה אינה זהה לתא ריק, אינה חוקית עבור 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 (range B2: B11) וreturn_array (range 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, "Employees לא נמצא")

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

דוגמה 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 כדי לבצע התאמה אנכית ואופקית. תחילה היא מחפשת רווח ברוטו בעמודה 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, Bill Jelen, המציעה דוגמה זו.

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

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

ניתן לשאול תמיד מומחה ב- Excel Tech Community, לקבל תמיכה בקהילת Answers או להציע תכונה חדשה או שיפור ב- Excel User Voice.

למידע נוסף

הפונקציה XMATCH

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

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

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

שפר את הכישורים שלך ב- Office
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

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

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

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×