פונקציית XLOOKUP

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

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

הערה: 28 באוגוסט 2019: XLOOKUP מהווה תכונת ביתא, והיא זמינה רק לחלק מהתושבים הפנימיים של Office בשלב זה. נמשיך למטב אותו בחודשים הקרובים. כאשר XLOOKUP מוכן, אנו נשחרר אותו לכל משרדי Office ולמנויי office 365.

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

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

ארגומנט

תיאור

lookup_value

נדרש

ערך בדיקת מידע

lookup_array

נדרש

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

return_array

נדרש

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

[match_mode]

אופציונלי

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

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

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

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

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

[search_mode]

אופציונלי

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

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

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

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

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

[if_not_found]

אופציונלי

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

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

דוגמאות

דוגמה 1

דוגמה זו היא מסרטון הווידאו שלעיל, והיא משתמשת בXLOOKUP פשוט כדי לחפש שם מדינה, ולאחר מכן להחזיר את קוד מדינת הטלפון שלה. הוא כולל רק את הארגומנטים lookup_value (cell 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. הארגומנט match_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.

×