צפה בוידאו - כיצד להשתמש בפונקציית VLOOKUP עם קריטריונים מרובים
פונקציית VLOOKUP של Excel, בצורתה הבסיסית, יכולה לחפש ערך חיפוש אחד ולהחזיר את הערך המתאים מהשורה שצוין.
אך לעתים קרובות יש צורך להשתמש ב- Excel VLOOKUP עם מספר קריטריונים.
כיצד להשתמש ב- VLOOKUP עם קריטריונים מרובים
נניח שיש לך נתונים עם שם התלמידים, סוג הבחינה וציון המתמטיקה (כפי שמוצג להלן):
שימוש בפונקציה VLOOKUP כדי לקבל את ציון המתמטיקה עבור כל תלמיד לרמות הבחינה בהתאמה עשוי להיות אתגר.
אפשר לטעון שאופציה טובה יותר תהיה לבנות מחדש את מערך הנתונים או להשתמש בטבלת ציר. אם זה עובד בשבילך, אין דבר כזה. אבל במקרים רבים, אתה תקוע עם הנתונים שיש לך וטבלת הציר אינה יכולה להיות אופציה.
במקרים כאלה, הדרכה זו מיועדת עבורך.
כעת ישנן שתי דרכים שבהן תוכל להשיג את ערך החיפוש באמצעות VLOOKUP עם מספר קריטריונים.
- שימוש בעמוד עוזר.
- שימוש בפונקציית CHOOSE.
VLOOKUP עם קריטריונים מרובים - שימוש בעמוד עוזר
אני מעריץ של עמודות עזר ב- Excel.
אני מוצא שני יתרונות משמעותיים בשימוש בעמודות עזר על פני נוסחאות מערך:
- זה מקל להבין מה קורה בדף העבודה.
- הוא הופך אותו למהיר יותר בהשוואה לפונקציות המערך (ניכר במערכות נתונים גדולות).
עכשיו, אל תבין אותי לא נכון. אני לא נגד נוסחאות מערך. אני אוהב את הדברים המדהימים שאפשר לעשות עם נוסחאות מערך. רק שאני שומר אותם לאירועים מיוחדים כאשר כל שאר האפשרויות אינן מועילות.
אם נחזור לשאלה המדויקת, יש צורך בעמוד העזרה כדי ליצור כשירות ייחודי. לאחר מכן ניתן להשתמש במכשיר ייחודי זה לחיפוש הערך הנכון. לדוגמה, ישנם שלושה מאט בנתונים, אך יש רק שילוב אחד של מאט ויחידת מבחן או מאט ואמצע טווח.
להלן השלבים:
- הכנס עמוד עוזר בין עמודה B ו- C.
- השתמש בנוסחה הבאה בעמודת העזר: = A2 & ”|” & B2
- פעולה זו תיצור מוקדמות ייחודיות לכל מופע כפי שמוצג להלן.
- פעולה זו תיצור מוקדמות ייחודיות לכל מופע כפי שמוצג להלן.
- השתמש בנוסחה הבאה ב- G3 = VLOOKUP ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
- העתק לכל התאים.
איך זה עובד?
אנו יוצרים מוקדמות ייחודיות לכל מופע של שם ובחינה. בפונקציית VLOOKUP המשמשת כאן, ערך החיפוש שונה ל- $ F3 & ”|” & G $ 2 כך ששני קריטריוני החיפוש משולבים ומשמשים כערך חיפוש יחיד. לדוגמה, ערך החיפוש של הפונקציה VLOOKUP ב- G2 הוא Matt | Unit Test. עכשיו ערך החיפוש הזה משמש כדי לקבל את הציון מ- C2: D19.
הבהרות:
ישנן מספר שאלות שצפויות לעלות בדעתך, אז חשבתי שאנסה לענות עליה כאן:
- למה השתמשתי | סמל בעת הצטרפות לשני הקריטריונים? - בכמה מצבים נדירים (אך אפשריים), ייתכן שיש לך שני קריטריונים שונים אך בסופו של דבר נותנים את אותה התוצאה בשילוב. הנה דוגמה פשוטה מאוד (סלח לי על חוסר היצירתיות שלי כאן):
שים לב שבעוד A2 ו- A3 שונים ו- B2 ו- B3 שונים, אך השילובים בסופו של דבר זהים. אבל אם אתה משתמש במפריד, אפילו השילוב יהיה שונה (D2 ו- D3).
- מדוע הכנסתי את עמוד העזר בין עמודה B ו- C ולא בצד שמאל קיצוני? - אין שום נזק בהכנסת עמוד העוזרים לשמאל הקיצוני. למעשה, אם אינך רוצה להתעשת עם הנתונים המקוריים, זו צריכה להיות הדרך. עשיתי את זה מכיוון שזה גורם לי להשתמש במספר פחות של תאים בפונקציית VLOOKUP. במקום שיהיו לי 4 עמודות במערך הטבלאות, אוכל להסתדר עם 2 עמודות בלבד. אבל זה רק אני.
עכשיו אין גודל אחד שמתאים לכולם. חלק מהאנשים עשויים להעדיף שלא להשתמש בעמודה עוזרת כלשהי בעת שימוש ב- VLOOKUP עם מספר קריטריונים.
אז הנה שיטת העמודה שאינה עוזרת עבורך.
הורד את קובץ הדוגמה
VLOOKUP עם קריטריונים מרובים - שימוש בפונקציית CHOOSE
שימוש בנוסחאות מערך במקום עמודות עוזר חוסך לך נדל"ן בגליון עבודה, והביצועים יכולים להיות טובים באותה מידה אם משתמשים בהם פחות פעמים בחוברת עבודה.
בהתחשב באותה מערך נתונים כפי שהשתמשו לעיל, הנה הנוסחה שתיתן לך את התוצאה:
= VLOOKUP ($ E3 & ”|” & F $ 2, CHOOSE ({1,2}, $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19), 2, 0)
מכיוון שזו נוסחת מערך, השתמש בה עם Control + Shift + Enter, במקום רק Enter.
איך זה עובד?
הנוסחה משתמשת גם במושג טור עוזר. ההבדל הוא שבמקום להכניס את עמודת העוזר לדף העבודה, ראו זאת כנתוני עזר וירטואליים המהווים חלק מהנוסחה.
תן לי להראות לך למה אני מתכוון בנתוני עוזר וירטואליים.
באיור לעיל, כשאני בוחר את החלק CHOOSE של הנוסחה ולחץ על F9, הוא מראה את התוצאה שתיתן נוסחת ה- CHOOSE.
התוצאה היא {"מאט | מבחן יחידה", 91; "בוב | מבחן יחידה", 52; …}
זהו מערך שבו פסיק מייצג את התא הבא באותה שורה ופסיק -פסיק מייצג כי הנתונים הבאים נמצאים בעמודה הבאה. מכאן שנוסחה זו יוצרת 2 עמודות נתונים - בעמודה אחת יש את המזהה הייחודי ולאחת יש את הציון.
כעת, כאשר אתה משתמש בפונקציית VLOOKUP, הוא פשוט מחפש את הערך בעמודה הראשונה (של נתוני עמודה וירטואליים אלה) ומחזיר את הציון המתאים.
הורד את קובץ הדוגמה
תוכל גם להשתמש בנוסחאות אחרות לביצוע חיפוש עם קריטריונים מרובים (כגון INDEX/MATCH או SUMPRODUCT).
האם יש דרך אחרת שאתה יודע לעשות זאת? אם כן, אנא שתף אותי בסעיף ההערות.
אולי תאהב גם את מדריכי ה- LOOKUP הבאים:
- VLOOKUP Vs. אינדקס/התאמה
- קבל ערכי חיפוש מרובים ללא חזרה בתא בודד.
- כיצד להפוך VLOOKUP לרגיש לרישיות.
- השתמש ב- IFERROR עם VLOOKUP כדי להיפטר משגיאות מספר לא רלוונטי.
