פונקציית VLOOKUP תמיד הייתה אחת הפונקציות החזקות ביותר של Excel. זה מאפשר לך לחפש ערכים בעמודה הראשונה של טבלה, ולהחזיר ערכים משדות בצד ימין. אבל לאקסל יש גם פונקציה בשם XLOOKUP, המאפשרת לך לחפש ערך בכל עמודה או שורה, ולהחזיר נתונים מכל עמודה אחרת.
איך XLOOKUP עובד
הפונקציה XLOOKUP הרבה יותר קלה לשימוש מאשר הפונקציה VLOOKUP, מכיוון שבמקום לציין ערך עבור עמודת התוצאות, ניתן לציין את כל הטווח.
הפונקציה גם מאפשרת לך לחפש גם בעמודה וגם בשורה, לאתר את הערך בתא המצטלב.
הפרמטרים של הפונקציה XLOOKUP הם כדלקמן:
=XLOOKUP (ערך_חיפוש, מערך_חיפוש, מערך_החזר, [מצב_התאמה], [מצב_חיפוש])
- lookup_value: הערך שאתה רוצה לחפש
- lookup_array: המערך (העמודה) שברצונך לחפש
- return_array: התוצאה (עמודה) שברצונך לאחזר ערך מ
- match_mode (אופציונלי): בחר התאמה מדויקת (0), התאמה מדויקת או הערך הבא הכי קטן (-1), או התאמה כללית (2).
- search_mode (אופציונלי): בחר אם לחפש החל מהפריט הראשון בעמודה (1), הפריט האחרון בעמודה (-1), חיפוש בינארי עולה (2) או חיפוש בינארי יורד (-2).
להלן כמה מהחיפושים הנפוצים ביותר שתוכל לעשות עם הפונקציה XLOOKUP.
איך לחפש תוצאה בודדת באמצעות XLOOKUP
הדרך הקלה ביותר להשתמש ב-XLOOKUP היא לחפש תוצאה בודדת באמצעות נקודת נתונים מעמודה אחת.
-
גיליון אלקטרוני לדוגמה זה הוא רשימה של הזמנות שהוגשו על ידי נציגי מכירות, כולל הפריט, מספר היחידות, העלות והמכירות הכוללות.
Image -
אם ברצונך למצוא את המכירה הראשונה ברשימה שהוגשה על ידי נציג מכירות ספציפי, תוכל ליצור פונקציה XLOOKUP המחפשת שם בעמודת הנציג. הפונקציה תחזיר את התוצאה מהעמודה Total. הפונקציה XLOOKUP עבור זה היא:
=XLOOKUP(I2, C2:C44, G2:G44, 0, 1)
- I2: מצביע על שם הנציג תא החיפוש
- C2:C44: זוהי העמודה Rep, שהיא מערך בדיקת המידע
- G2:G33: זוהי העמודה הכוללת, שהיא מערך ההחזר
- 0: בוחר התאמה מדויקת
- 1: בוחר את המשחק הראשון בתוצאות
-
כאשר תלחץ Enter והקלד את השם של נציג מכירות, תא התוצאה הכוללת יראה לך את התוצאה הראשונה בטבלה עבור נציג מכירות זה.
Image -
אם ברצונך לחפש את המכירה האחרונה (מכיוון שהטבלה מסודרת לפי תאריך בסדר הפוך), שנה את הארגומנט האחרון של XLOOKUP ל-- 1, שיתחיל את החיפוש מהתא האחרון במערך החיפוש ולספק לך את התוצאה הזו במקום.
Image -
דוגמה זו מציגה חיפוש דומה שתוכל לבצע עם פונקציית VLOOKUP על ידי שימוש בעמודה Rep כעמודה הראשונה של טבלת החיפוש. עם זאת, XLOOKUP מאפשר לך לחפש כל עמודה בכל כיוון.לדוגמה, אם אתה רוצה למצוא את נציג המכירות שמכר את הזמנת ה-Binder הראשונה של השנה, תשתמש בפונקציית XLOOKUP הבאה:
=XLOOKUP(I2, D2:D44, C2:C44, 0, 1)
- D2: מצביע על תא החיפוש של פריט
- D2:D44: זוהי עמודת הפריט, שהיא מערך בדיקת המידע
- C2:C44: זוהי העמודה Rep, שהיא מערך ההחזרה משמאל למערך בדיקת המידע
- 0: בוחר התאמה מדויקת
- 1: בוחר את המשחק הראשון בתוצאות
-
הפעם, התוצאה תהיה שם נציג המכירות שמכר את הזמנת הקלסר הראשונה של השנה.
Image
בצע התאמה אנכית ואופקית עם XLOOKUP
יכולת נוספת של XLOOKUP ש-VLOOKUP לא מסוגלת לה היא היכולת לבצע גם חיפוש אנכי וגם אופקי, כלומר אתה יכול לחפש פריט במורד עמודה, וגם על פני שורה.
תכונת החיפוש הכפולה הזו היא תחליף יעיל לפונקציות אחרות של Excel כמו INDEX, MATCH או HLOOKUP.
-
בגיליון האלקטרוני לדוגמה הבא, המכירות של כל נציג מכירות מחולקות לרבעון. אם תרצה לראות את המכירות ברבעון השלישי של נציג מכירות ספציפי, ללא פונקציית XLOOKUP, חיפוש מסוג זה יהיה קשה.
Image -
עם הפונקציה XLOOKUP, סוג זה של חיפוש קל. באמצעות הפונקציה הבאה XLOOKUP, תוכל לחפש את המכירות ברבעון השלישי עבור נציג מכירות ספציפי:
=XLOOKUP(J2, B2:B42, XLOOKUP(K2, C1:H1, C2:H42))
- J2: מצביע על תא החיפוש של הנציג
- B2:B42: זוהי עמודת הפריט, שהיא מערך חיפוש העמודות
- K2: מצביע על תא החיפוש של הרבעון
- C1:H1: זהו מערך חיפוש השורות
- C2:H42: זהו מערך בדיקת הסכום הדולרי בכל רבעון
פונקציית XLOOKUP המקוננת הזו מזהה תחילה את נציג המכירות, ופונקציית XLOOKUP שלאחר מכן מזהה את הרבעון הרצוי. ערך ההחזרה יהיה התא שבו שני אלה מיירטים.
-
התוצאה עבור נוסחה זו היא הרווחים ברבעון אחד עבור הנציג עם השם תומפסון.
Image
שימוש בפונקציית XLOOKUP
פונקציית XLOOKUP זמינה רק למנויי Office Insider, אך בקרוב תושק לכל מנויי Microsoft 365.
אם אתה רוצה לבדוק את הפונקציה בעצמך, אתה יכול להיות Office Insider. בחר File > Account, ולאחר מכן בחר בתפריט הנפתח Office Insider כדי להירשם.
לאחר שתצטרף לתוכנית Office Insider, הגרסה המותקנת שלך של Excel תקבל את כל העדכונים האחרונים, ותוכל להתחיל להשתמש בפונקציית XLOOKUP.
