פונקציית XLOOKUP

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

גרום להשפעה גדולה באמצעות 'הכרזות'

הערה: פונקציה זו זמינה כעת למנויי Microsoft 365 בערוץ הנוכחי. היא תהיה זמינה למנויי Microsoft 365 בערוץ הארגוני החצי שנתי החל מיולי 2020. לקבלת מידע נוסף אודות האופן שבו תכונות מסוכמות למנויים Microsoft 365, ראה מתי אוכל לקבל את התכונות החדשות ביותר עבור Microsoft 365.

הפונקציה 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 (range B2: B11) וreturn_array (range D2: D11). הוא אינו כולל את הארגומנט match_mode, כברירת מחדל של XLOOKUP להתאמה מדויקת.

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

הערה: XLOOKUP שונה מ-VLOOKUP בכך שהוא משתמש במערכים נפרדים של בדיקת מידע והחזרה, כאשר 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)

הערה: בשונה מ-VLOOKUP, העמודה lookup_array נמצאת משמאל לעמודה 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 הדומה לזו, ולאחר מכן עבור אל נוסחאות _GT_ ביקורת נוסחאות > הערכת נוסחהולאחר מכן הקש על לחצן הערכה כדי לעבור בין החישוב.

הערה: הודות ל-Microsoft Excel MVP, Bill Jelen, המציעה דוגמה זו.

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

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

למידע נוסף

הפונקציה XMATCH

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

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

הערה:  דף זה תורגם באמצעות אוטומציה והוא עשוי לכלול שגיאות דקדוק או אי-דיוקים. מטרתנו היא כי תוכן זה יהיה שימושי עבורך. תוכל לספר לנו אם המידע היה מועיל? הנה המאמר באנגלית לעיונך.​

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

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

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

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

×