✅ כיצד להפוך את Excel VLOOKUP לרגיש לרישיות

תוכן העניינים

הפיכת VLOOKUP לרגיש לרשתות

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

הפיכת VLOOKUP לרגיש לרשתות

נניח שיש לך את הנתונים כפי שמוצג להלן:

כפי שאתה יכול לראות, ישנם שלושה תאים עם אותו שם (A2, A4 ו- A5) אך עם אותיות שונות. מימין (ב- E2: F4), יש לנו את שלושת השמות (Matt, MATT, and matt) יחד עם הציונים שלהם במתמטיקה.

פונקציית VLOOKUP של Excel אינה מצוידת להתמודד עם ערכי חיפוש תלויי רישיות. בדוגמה זו לעיל, לא משנה מהו ערך החיפוש (Matt, MATT או matt), הוא תמיד יחזיר 38 (שהוא ערך ההתאמה הראשון).

במדריך זה תלמד כיצד להפוך את רשימת VLOOKUP לרגישה על ידי:

שימוש בעמוד עוזר.
ללא שימוש בעמוד עוזר ושימוש בנוסחה.

הפיכת VLOOKUP לרגיש לרישיות - שימוש בעמוד עוזר

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

להלן השלבים לביצוע פעולה זו:

הכנס עמודת עזר משמאל לעמודה ממנה ברצונך להביא את הנתונים. בדוגמה למטה, עליך להכניס את עמודת העזר בין עמודה A ו- C.
בעמודה עוזר, הזן את הנוסחה = ROW (). הוא יכניס את מספר השורות בכל תא.
השתמש בנוסחה הבאה בתא F2 כדי לקבל את תוצאת החיפוש התלויית רישיות.
```
= VLOOKUP (MAX (EXACT (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))), $ B $ 2: $ C $ 9,2,0)
```
העתק הדבק אותו עבור התאים הנותרים (F3 ו- F4).

הערה: מכיוון שזו נוסחת מערך, השתמש ב- Control + Shift + Enter במקום פשוט הזן.

איך זה עובד?

בואו נשבור את הנוסחה כדי להבין כיצד היא פועלת:

EXACT (E2, $ A $ 2: $ A $ 9) - חלק זה משווה את ערך החיפוש ב- E2 עם כל הערכים ב- A2: A9. הוא מחזיר מערך של TRUEs/FALSEs כאשר TRUE מוחזר כשיש התאמה מדויקת. במקרה זה, כאשר הערך ב- E2 הוא מאט, הוא יחזיר את המערך הבא:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
EXACT (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9) - חלק זה מכפיל את מערך ה- TRUEs/FALSE עם מספר השורות של A2: A9. בכל מקום בו יש TRUE, הוא נותן את מספר השורה, אחרת הוא נותן 0. במקרה זה הוא יחזיר {2; 0; 0; 0; 0; 0; 0; 0}.
MAX (EXACT (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9)))) - חלק זה מחזיר את הערך המרבי ממערך המספרים. במקרה זה, הוא יחזיר 2 (שהוא מספר השורה שבו יש התאמה מדויקת).
כעת אנו פשוט משתמשים במספר זה כערך החיפוש ומשתמשים במערך החיפוש כ- B2: C9.

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

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

הפיכת VLOOKUP לרגיש לרישיות - ללא טור המסייע

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

להלן הנוסחה שתתן לך את התוצאה ללא עמודת העזר:

= VLOOKUP (MAX (EXACT (D2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9)))), CHOOSE ({1,2}, ROW ($ A $ 2: $ A $ 9) , $ B $ 2: $ B $ 9), 2,0)

איך זה עובד?

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

להלן החלק שעובד כנתוני העזר (המודגשים בכתום):

= VLOOKUP (MAX (EXACT (D2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9)))),בחר ({1,2}, ROW ($ A $ 2: $ A $ 9), $ B $ 2: $ B $ 9),2,0)

תן לי להראות לך למה אני מתכוון בנתוני עוזר וירטואליים.

באיור לעיל, כשאני בוחר את החלק CHOOSE של הנוסחה ולחץ על F9, הוא מראה את התוצאה שתיתן נוסחת ה- CHOOSE.

התוצאה היא {2,38; 3,88; 4,57; 5,82; 6,55; 7,44; 8,75; 9,38}

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

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

הורד את קובץ הדוגמה