כיצד להשתמש בפונקציה XLOOKUP ב- Microsoft Excel

ה- XLOOKUP החדש של Excel יחליף את VLOOKUP, ויספק תחליף חזק לאחת הפונקציות הפופולריות ביותר של Excel. פונקציה חדשה זו פותרת חלק מהמגבלות של VLOOKUP ויש לה פונקציונליות נוספת. הנה מה שאתה צריך לדעת.

מה זה XLOOKUP?

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

נכון לעכשיו, XLOOKUP זמין רק למשתמשים בתוכנית Insiders. כל אחד יכול להצטרף לתוכנית Insiders כדי לגשת לתכונות Excel החדשות ברגע שהן זמינות. מיקרוסופט תתחיל בקרוב להפיץ אותה לכל משתמשי Office 365.

כיצד להשתמש בפונקציה XLOOKUP

בואו נצלול ישר עם דוגמה של XLOOKUP בפעולה. קח את נתוני הדוגמה שלהלן. אנו רוצים להחזיר את המחלקה מעמודה F עבור כל מזהה בעמודה A.

זוהי דוגמה קלאסית לחיפוש התאמה מדויקת. הפונקציה XLOOKUP דורשת רק שלוש פיסות מידע.

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

  • Lookup_value:  מה שאתה מחפש.
  • Lookup_array:  איפה לחפש.
  • Return_array:  הטווח המכיל את הערך שיש להחזיר.

הנוסחה הבאה תפעל לדוגמא זו: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

בואו נבדוק כעת כמה יתרונות ל- XLOOKUP לעומת VLOOKUP כאן.

אין עוד מספר אינדקס עמודות

הטיעון השלישי הידוע לשמצה של VLOOKUP היה לציין את מספר העמודה של המידע שיש להחזיר ממערך טבלה. זו כבר לא בעיה מכיוון ש- XLOOKUP מאפשר לך לבחור את הטווח לחזור ממנו (עמודה F בדוגמה זו).

ואל תשכח, XLOOKUP יכול להציג את הנתונים שנותרו מהתא שנבחר, בניגוד ל- VLOOKUP. עוד על כך בהמשך.

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

התאמה מדויקת היא ברירת המחדל

זה תמיד היה מבלבל כאשר למדו את VLOOKUP מדוע עליכם לציין התאמה מדויקת.

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

אז בקיצור, XLOOKUP שואל פחות שאלות מ- VLOOKUP, הוא יותר ידידותי למשתמש, וגם עמיד יותר.

XLOOKUP יכול להסתכל שמאלה

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

VLOOKUP הוגבל על ידי חיפוש בעמודה השמאלית ביותר בטבלה ואז חזרה ממספר מוגדר של עמודות ימינה.

בדוגמה למטה, עלינו לחפש מזהה (עמודה E) ולהחזיר את שם האדם (עמודה D).

הנוסחה הבאה יכולה להשיג זאת: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

מה לעשות אם לא נמצא

משתמשים בפונקציות בדיקה מכירים היטב את הודעת השגיאה # N / A שמקבלת את פניהם כאשר VLOOKUP או פונקציית ה- MATCH שלהם לא מצליחות למצוא את מה שהיא זקוקה לה. ולעתים קרובות יש לכך סיבה הגיונית.

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

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

הנוסחה הבאה תציג את הטקסט "מזהה שגוי" במקום הודעת השגיאה: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

שימוש ב- XLOOKUP לחיפוש טווח

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

הפעם אנחנו לא מחפשים ערך ספציפי. עלינו לדעת היכן הערכים בעמודה B נכנסים לטווחים בעמודה E. אשר יקבעו את ההנחה שנצברה.

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

אתה יכול לראות של- XLOOKUP יכולות גדולות יותר עם התאמות משוערות מזו של VLOOKUP.

יש אפשרות למצוא את ההתאמה הקרובה ביותר קטנה מ- (-1) או הקרובה יותר מ- (1) שהערך חיפש. יש גם אפשרות להשתמש בתווים כלליים (2) כמו? או ה *. הגדרה זו אינה מופעלת כברירת מחדל כפי שהייתה עם VLOOKUP.

הנוסחה בדוגמה זו מחזירה את הקרוב פחות מהערך שחיפשת אם לא נמצא התאמה מדויקת: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

עם זאת, ישנה טעות בתא C7 שבו מוחזרת השגיאה # N / A (לא נעשה שימוש בארגומנט 'אם לא נמצא'). זה אמור היה להחזיר 0% הנחה מכיוון שההוצאה 64 אינה מגיעה לקריטריונים להנחה כלשהי.

יתרון נוסף של הפונקציה XLOOKUP הוא שהיא אינה דורשת שטווח החיפוש יהיה בסדר עולה כפי שעושה VLOOKUP.

הזן שורה חדשה בתחתית טבלת החיפוש ואז פתח את הנוסחה. הרחב את הטווח המשומש על ידי לחיצה וגרירה של הפינות.

הנוסחה מתקנת מיד את השגיאה. אין בעיה עם "0" בתחתית הטווח.

באופן אישי, עדיין הייתי ממיין את הטבלה לפי עמודת החיפוש. שיש "0" בתחתית ישגע אותי. אבל העובדה שהנוסחה לא נשברה היא מבריקה.

XLOOKUP מחליף גם את פונקציית HLOOKUP

כאמור, פונקציית XLOOKUP כאן גם כדי להחליף את HLOOKUP. פונקציה אחת להחלפת שתיים. מְעוּלֶה!

פונקציית HLOOKUP היא התצפית האופקית המשמשת לחיפוש לאורך שורות.

לא ידוע כמו אחיו VLOOKUP, אך שימושי לדוגמאות כמו להלן כאשר הכותרות נמצאות בעמודה A, והנתונים הם לאורך שורות 4 ו -5.

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

בדוגמה זו, הנוסחה משמשת להחזרת ערך המכירה המתייחס לשם בתא A2. זה נראה לאורך שורה 4 כדי למצוא את השם ומחזיר את הערך משורה 5:=XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP יכול להיראות מלמטה למעלה

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

בדוגמה שלהלן נרצה למצוא את רמת המלאי של כל מוצר בעמודה A.

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

הטיעון השישי של הפונקציה XLOOKUP מספק ארבע אפשרויות. אנו מעוניינים להשתמש באפשרות "חפש אחרון-ראשון".

הנוסחה המלאה מוצגת כאן: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

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

לאסוף

הפונקציה XLOOKUP היא היורש המיוחל לפונקציות VLOOKUP ו- HLOOKUP.

מגוון דוגמאות שימשו במאמר זה כדי להדגים את היתרונות של XLOOKUP. אחד מהם הוא שניתן להשתמש ב- XLOOKUP על פני גיליונות, חוברות עבודה וגם עם טבלאות. הדוגמאות נשמרו פשוטות במאמר כדי לעזור להבנתנו.

בשל מערכים דינמיים שיוכנסו ל- Excel בקרוב, הוא יכול גם להחזיר טווח ערכים. זה בהחלט משהו שכדאי לבחון עוד.

ימי VLOOKUP מנויים. XLOOKUP כבר כאן ובקרוב תהיה נוסחת החיפוש בפועל.