10 דוגמאות VLOOKUP למתחילים ומתקדמים

פונקציית VLOOKUP - מבוא

פונקציית VLOOKUP היא המדד.

אתה יודע משהו ב- Excel אם אתה יודע כיצד להשתמש בפונקציה VLOOKUP.

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

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

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

זו הולכת להיות הדרכת VLOOKUP מאסיבית (לפי הסטנדרטים שלי).

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

אז תתכופף.

הגיע הזמן להמראה.

מתי להשתמש בפונקציית VLOOKUP ב- Excel?

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

ניקח דוגמא פשוטה כאן כדי להבין מתי להשתמש ב- Vlookup ב- Excel.

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

כך זה עבד:

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

וזה בדיוק מה שפונקציית VLOOKUP של Excel עושה עבורך (אל תהסס להשתמש בדוגמה זו בראיון הבא שלך).

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

תחביר

= VLOOKUP (חיפוש_ערך, טבלה_מערך, col_index_num, [טווח_חפש])

טענות קלט

• ערך_חיפוש - זהו ערך החיפוש שאתה מנסה למצוא בעמודה השמאלית ביותר בטבלה. זה יכול להיות ערך, הפניה לתא או מחרוזת טקסט. בדוגמה של דף הציונים, זה יהיה שמך.
• מערך שולחן - זהו מערך הטבלאות בו אתה מחפש את הערך. זו יכולה להיות התייחסות לטווח תאים או לטווח בשם. בדוגמה של דף הציונים, זו תהיה הטבלה כולה המכילה ציון לכולם עבור כל נושא
• col_index - זהו מספר אינדקס העמודות שממנו ברצונך להביא את ערך ההתאמה. בדוגמה של דף הציונים, אם אתה רוצה את הציונים עבור מתמטיקה (שהיא העמודה הראשונה בטבלה המכילה את הציונים), היית מסתכל בעמודה 1. אם אתה רוצה את הציונים לפיזיקה, היית מסתכל בעמודה 2.
• [range_lookup] - כאן אתה מציין אם אתה רוצה התאמה מדויקת או התאמה משוערת. אם הוא מושמט, הוא מוגדר כברירת מחדל ל- TRUE - התאמה משוערת (ראה הערות נוספות להלן).

הערות נוספות (משעמם, אך חשוב לדעת)

• ההתאמה יכולה להיות מדויקת (FALSE או 0 ב- range_lookup) או משוער (TRUE או 1).
• בחיפוש משוער, וודא שהרשימה ממוינת בסדר עולה (מלמעלה למטה), אחרת התוצאה עלולה להיות לא מדויקת.
• כאשר טווח_חיפוש הוא TRUE (חיפוש משוער) והנתונים ממוינים בסדר עולה:
  • אם הפונקציה VLOOKUP לא מצליחה למצוא את הערך, היא מחזירה את הערך הגדול ביותר, שהוא פחות מהערך lookup_value.
  • הוא מחזיר שגיאת #N/A אם ערך החיפוש קטן מהערך הקטן ביותר.
  • אם Lookup_value הוא טקסט, ניתן להשתמש בתווים כלליים (עיין בדוגמה להלן).

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

10 דוגמאות VLOOKUP של Excel (בסיסיות ומתקדמות)

להלן 10 דוגמאות שימושיות לשימוש ב- Excel Vlookup אשר יראו לך כיצד להשתמש בו בעבודתך השוטפת.

דוגמא 1 - מציאת ציון המתמטיקה של בראד

בדוגמה VLOOKUP למטה, יש לי רשימה עם שמות תלמידים בעמודה השמאלית ביותר וסימנים בנושאים שונים בעמודות B עד E.

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

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

להלן נוסחת VLOOKUP שתחזיר את ציון המתמטיקה של בראד:

= VLOOKUP ("בראד", $ A $ 3: $ E $ 10,2,0)

לנוסחה הנ"ל יש ארבע טיעונים:

• "בראד: - זהו ערך החיפוש.
• $ A $ 3: $ E $ 10 - זהו טווח התאים בו אנו מחפשים. זכור כי Excel מחפש את ערך החיפוש בעמודה השמאלית ביותר. בדוגמה זו, הוא יחפש את השם בראד ב- A3: A10 (המהווה את העמודה השמאלית ביותר של המערך שצוין).
• 2 - ברגע שהפונקציה תזהה את שמו של בראד, היא תעבור לעמודה השנייה של המערך ותחזיר את הערך באותה שורה כמו זה של בראד. הערך 2 כאן הצביע על כך שאנו מחפשים את הציון מהעמודה השנייה של המערך שצוין.
• 0 - זה אומר לפונקציית VLOOKUP לחפש רק התאמות מדויקות.

כך פועלת נוסחת VLOOKUP בדוגמה שלמעלה.

ראשית, הוא מחפש את הערך בראד בעמודה השמאלית ביותר. הוא עובר מלמעלה למטה ומוצא את הערך בתא A6.

ברגע שהוא מוצא את הערך, הוא עובר ימינה בעמודה השנייה ומביא את הערך שבו.

אתה יכול להשתמש באותו מבנה נוסחה כדי לקבל את הציונים של כל אחד בכל הנושאים.

לדוגמה, כדי למצוא את הציונים של מריה בכימיה, השתמש בנוסחת VLOOKUP הבאה:

= VLOOKUP ("מריה", $ A $ 3: $ E $ 10,4,0)

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

היתרון בשימוש בהתייחסות לתאים הוא בכך שהיא הופכת את הנוסחה לדינאמית.

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

אם אתה מזין ערך חיפוש שאינו נמצא בעמודה השמאלית ביותר, הוא מחזיר שגיאת #N/A.

דוגמא 2 - חיפוש דו כיווני

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

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

זוהי דוגמה לפונקציית VLOOKUP דו כיוונית.

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

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

להלן נוסחת VLOOKUP שתעשה זאת:

= VLOOKUP (G4, $ A $ 3: $ E $ 10, MATCH (H3, $ A $ 2: $ E $ 2,0), 0)

הנוסחה שלעיל משתמשת ב- MATCH (H3, $ A $ 2: $ E $ 2,0) כמספר העמודה. הפונקציה MATCH לוקחת את שם הנושא כערך החיפוש (ב- H3) ומחזירה את מיקומה ב- A2: E2. מכאן שאם אתה משתמש במתמטיקה, הוא יחזיר 2 מכיוון שמתמטיקה נמצאת ב- B2 (שהוא התא השני בטווח המערך שצוין).

דוגמה 3 - שימוש ברשימות נפתחות כערכי חיפוש

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

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

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

משהו כפי שמוצג להלן:

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

כיצד להכין זאת:

נוסחת VLOOKUP המשמשת במקרה זה היא אותה המשמשת בדוגמה 2.

= VLOOKUP (G4, $ A $ 3: $ E $ 10, MATCH (H3, $ A $ 2: $ E $ 2,0), 0)

ערכי החיפוש הוסבו לרשימות נפתחות.

להלן השלבים ליצירת הרשימה הנפתחת:

• בחר את התא שבו אתה רוצה את הרשימה הנפתחת. בדוגמה זו, ב- G4, אנו רוצים את שמות התלמידים.
• עבור אל נתונים -> כלי נתונים -> אימות נתונים.
• בתיבה דיאלוג אימות נתונים, בכרטיסיה הגדרות, בחר רשימה מהתפריט הנפתח אפשר.
• במקור, בחר $ A $ 3: $ A $ 10
• לחץ על אישור.

כעת תהיה לך הרשימה הנפתחת בתא G4. באופן דומה, אתה יכול ליצור אחד ב- H3 עבור הנבדקים.

דוגמה 4 - חיפוש תלת כיווני

מהו חיפוש תלת כיווני?

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

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

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

משהו כפי שמוצג להלן:

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

להלן הנוסחה המשמשת בתא H4:

= VLOOKUP (G4, CHOOSE (IF (H2 = "Test Unit, 1, IF (H2 =" Midterm ", 2,3)), $ A $ 3: $ E $ 7, $ A $ 11: $ E $ 15, $ A $ 19: $ E $ 23), MATCH (H3, $ A $ 2: $ E $ 2,0), 0) 

נוסחה זו משתמשת בפונקציה CHOOSE כדי לוודא שאליה מתייחסת הטבלה הנכונה. בואו ננתח את החלק CHOOSE של הנוסחה:

בחר (IF (H2 = "בדיקת יחידה", 1, IF (H2 = "אמצע", 2,3)), $ 3 $: $ E $ 7, $ A $ 11: $ E $ 15, $ A $ 19: $ E $ 23 )

הטיעון הראשון של הנוסחה הוא IF (H2 = "בדיקת יחידה", 1, IF (H2 = "אמצע טווח", 2,3)), הבודק את התא H2 ורואה לאיזו רמת בחינה מתייחסים. אם מדובר במבחן יחידה, הוא מחזיר $ 3 $: $ E $ 7, בעל הציונים עבור יחידת מבחן. אם מדובר באמצע טווח, היא מחזירה $ 11 $: $ E $ 15, אחרת היא מחזירה $ 19 $: $ E $ 23.

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

דוגמה 5 - קבלת הערך האחרון מרשימה

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

המספר החיובי הגדול ביותר שניתן להשתמש בו ב- Excel הוא 9.99999999999999E+307. זה גם אומר שמספר החיפוש הגדול ביותר במספר VLOOKUP הוא זהה.

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

נניח שיש לך מערך נתונים (ב- A1: A14) כפי שמוצג להלן ואתה רוצה לקבל את המספר האחרון ברשימה.

להלן הנוסחה בה תוכל להשתמש:

= VLOOKUP (9.99999999999999E+307, $ 1 $: $ A $ 14,נָכוֹן)

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

כך פועלת פונקציית VLOOKUP המשוערת. הוא סורק את העמודה השמאלית ביותר מלמעלה למטה.

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

בדוגמה לעיל, התרחיש השלישי נמצא בעבודה.

מאז 9.99999999999999E+307 הוא המספר הגדול ביותר שניתן להשתמש בו ב- Excel, כאשר הוא משמש כערך החיפוש, הוא מחזיר את המספר האחרון מהרשימה.

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

= VLOOKUP ("zzz", $ A $ 1: $ A $ 8,1,נָכוֹן)

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

דוגמה 6 - חיפוש חלקי באמצעות תווים כלליים ו- VLOOKUP

תווים כלליים של Excel יכולים לעזור מאוד במצבים רבים.

זה שיקוי הקסם שנותן לנוסחאות שלך כוחות על.

יש צורך בחיפוש חלקי כאשר עליך לחפש ערך ברשימה ואין התאמה מדויקת.

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

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

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

הזן את הנוסחה הבאה בתא D2 וגרור אותה לתאים האחרים:

= VLOOKUP ("*" & C2 & "*", $ A $ 2: $ A $ 8,1, FALSE)

כיצד פועלת נוסחה זו?

בנוסחה שלעיל, במקום להשתמש בערך החיפוש כפי שהוא, הוא מוקף משני הצדדים עם כוכב התווים (*) - "*" & C2 & "*"

כוכבית היא תו כללי ב- Excel ויכול לייצג כל מספר תווים.

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

לדוגמה, לתא C2 יש ABC, ולכן הפונקציה VLOOKUP מחפשת את השמות ב- A2: A8 ומחפשת ABC. הוא מוצא התאמה בתא A2, מכיוון שהוא מכיל ABC ב- ABC Ltd. לא משנה אם יש תווים משמאל או לימין של ABC. עד שלא תהיה ABC במחרוזת טקסט, היא תיחשב התאמה.

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

דוגמה 7 - VLOOKUP החזרת שגיאה למרות התאמה בערך החיפוש

זה יכול לשגע אותך כשאתה רואה שיש ערך חיפוש תואם והפונקציה VLOOKUP מחזירה שגיאה.

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

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

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

הפתרון כאן הוא פונקציית TRIM. הוא מסיר כל רווח מוביל או נגרר או רווחים נוספים בין מילים.

הנה הנוסחה שתתן לך את התוצאה הנכונה.

= VLOOKUP ("מאט", TRIM ($ A $ 2: $ A $ 9), 1,0)

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

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

דוגמה 8 - בדיקת רגישות לגיל הרך

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

אך אם ברצונך לבצע חיפוש תלוי-רישיות, עליך להשתמש בפונקציה EXACT יחד עם הפונקציה VLOOKUP.

הנה דוגמה:

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

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

כדי להפוך אותו לרגיש לאותיות, עלינו להשתמש בעמודת עזר (כפי שמוצג להלן):

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

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

= VLOOKUP (MAX (EXACT (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))), $ B $ 2: $ C $ 9,2,0)

עכשיו בואו נשבר ונבין מה זה עושה:

• EXACT (E2, $ A $ 2: $ A $ 9) - חלק זה ישווה את ערך החיפוש ב- E2 עם כל הערכים ב- A2: A9. הוא מחזיר מערך של TRUEs/FALSEs שבו TRUE מוחזר כאשר יש התאמה מדויקת. במקרה זה, הוא יחזיר את המערך הבא: {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
• EXACT (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9)) חלק זה מכפיל את מערך ה- TRUEs/FALSE עם מספר השורה. בכל מקום בו יש 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

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

דוגמה 9 - שימוש ב- VLOOKUP עם קריטריונים מרובים

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

אך לעתים קרובות יש צורך להשתמש ב- VLOOKUP ב- Excel עם מספר קריטריונים.

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

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

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

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

כעת, כדי ליצור כשיר ייחודי לכל מופע של השם, השתמש בנוסחה הבאה ב- C2: = A2 & ”|” & B2

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

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

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

להלן הנוסחה שתתן לך את התוצאה ב- G3: I8.

= VLOOKUP ($ F3 & "|" & G $ 2, $ C $ 2: $ D $ 19,2,0)

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

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

שים לב שבעוד A2 ו- A3 שונים ו- B2 ו- B3 שונים, אך השילובים בסופו של דבר זהים. אבל אם אתה משתמש במפריד, אפילו השילוב יהיה שונה (D2 ו- D3).

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

דוגמא 10 - טיפול בשגיאות תוך שימוש בפונקציית VLOOKUP

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

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

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

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

להלן הנוסחה:

= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0), "לא נמצא")

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

הפונקציה IFERROR זמינה מ- Excel 2007 ואילך. אם אתה משתמש בגרסאות לפני כן, השתמש בפונקציה הבאה:

= IF (ISERROR (VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0)), "Not Found", VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0)))

ראה גם: כיצד לטפל בשגיאות VLOOKUP ב- Excel.

זהו זה במדריך VLOOKUP זה.

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

הערה: ניסיתי כמיטב יכולתי לקרוא הגהה הדרכה הזו, אך במקרה שתמצא שגיאות או שגיאות כתיב, אנא יידע אותי 🙂

שימוש פונקציית VLOOKUP ב- Excel - וידאו

• פונקציית HLOOKUP של Excel.
• פונקציית Excel XLOOKUP
• פונקציית Excel INDEX.
• פונקציית INDIRECT של Excel.
• פונקציית Excel MATCH.
• פונקציית קיזוז Excel.