כשאתה משתמש בנוסחת VLOOKUP ב- Excel, לפעמים אתה עלול להסתיים בשגיאת #N/A המכוערת. זה קורה כאשר הנוסחה שלך לא מצליחה למצוא את ערך החיפוש.
במדריך זה אראה לך דרכים שונות להשתמש ב- IFERROR עם VLOOKUP כדי לטפל בשגיאות N/A אלה שצצות בגליון העבודה שלך.
השימוש בשילוב של IFERROR עם VLOOKUP מאפשר לך להציג משהו בעל משמעות במקום השגיאה #לא רלוונטית (או כל שגיאה אחרת לצורך העניין).
לפני שנכנס לפרטים על השימוש בשילוב זה, בואו נעבור במהירות על הפונקציה IFERROR ונראה כיצד היא פועלת.
הפונקציה IFERROR מוסברת
בעזרת הפונקציה IFERROR, תוכל לציין מה צריך לקרות במקרה שנוסחה או הפניה לתא תחזיר שגיאה.
להלן התחביר של הפונקציה IFERROR.
= IFERROR (ערך, value_if_error)
- ערך - זהו הטיעון שנבדק על השגיאה. ברוב המקרים, זוהי נוסחה או הפניה לתא. בעת שימוש ב- VLOOKUP עם IFERROR, נוסחת VLOOKUP תהיה הארגומנט הזה.
- value_if_error - זהו הערך המוחזר אם יש שגיאה. סוגי השגיאות הבאים הוערכו: #לא רלוונטי, #REF !, #DIV/0 !, #ערך !, #NUM !, #NAME ?, ו- #NULL !.
סיבות אפשריות ל- VLOOKUP החזרת שגיאה #לא רלוונטית
פונקציית VLOOKUP עשויה להחזיר שגיאת #N/A עקב אחת מהסיבות הבאות:
- ערך החיפוש לא נמצא במערך החיפוש.
- יש רווח מוביל, נגרר או כפול בערך החיפוש (או במערך הטבלאות).
- יש שגיאת כתיב בערך החיפוש או בערכים במערך החיפוש.
אתה יכול להתמודד עם כל הסיבות הללו לשגיאה בעזרת השילוב של IFERROR ו- VLOOKUP. עם זאת, עליך לפקוח עין על סיבה מס '2 ו -3, ולתקן אותם בנתוני המקור במקום לתת ל- IFERROR לטפל באלה.
הערה: IFERROR תתייחס לשגיאה ללא קשר למה שגרם לה. אם אתה רק רוצה לטפל בשגיאות הנגרמות מכך ש- VLOOKUP לא מצליח למצוא את ערך החיפוש, השתמש במקום זאת ב- IFNA. זה יוודא שלא מטפלים בשגיאות אחרות מלבד מספר #לא ניתן ותוכל לחקור שגיאות אחרות אלה.
אתה יכול לטפל ברווחים מובילים, נגררים וכפולים באמצעות פונקציית TRIM.
החלפת שגיאת VLOOKUP #N/A בטקסט בעל משמעות
נניח שיש לך מערך נתונים כפי שמוצג להלן:
כפי שאתה יכול לראות כי נוסחת VLOOKUP מחזירה שגיאה מכיוון שערך החיפוש אינו ברשימה. אנו מחפשים להשיג את הציון עבור גלן, שאינו מופיע בטבלת הציונים.
למרות שמדובר במערך נתונים קטן מאוד, ייתכן שתקבל מערכי נתונים ענקיים שבהם עליך לבדוק את התרחשותם של פריטים רבים. עבור כל מקרה בו הערך לא נמצא, תקבל שגיאת #N/A.
להלן הנוסחה שבה תוכל להשתמש בכדי לקבל משהו בעל משמעות במקום שגיאת #N/A.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 10,2,0), "לא נמצא")
הנוסחה שלמעלה מחזירה את הטקסט "לא נמצא" במקום שגיאת #N/A. תוכל גם להשתמש באותה נוסחה להחזרת טקסט ריק, אפס או כל טקסט בעל משמעות אחרת.
קינון VLOOKUP עם פונקציית IFERROR
במקרה שאתה משתמש ב- VLOOKUP וטבלת החיפוש שלך מקוטעת באותו גליון עבודה או בגליונות עבודה שונים, עליך לבדוק את ערך VLOOKUP בכל הטבלאות הללו.
לדוגמה, במערך הנתונים המוצג להלן, ישנן שתי טבלאות נפרדות של שמות התלמידים והציונים.
אם אני צריך למצוא את הציון של גרייס במערך הנתונים הזה, אני צריך להשתמש בפונקציה VLOOKUP כדי לבדוק את הטבלה הראשונה, ואם הערך לא נמצא בה, בדוק את הטבלה השנייה.
להלן נוסחת IFERROR המקוננת בה ניתן להשתמש כדי לחפש את הערך:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), "לא נמצא"))
שימוש ב- VLOOKUP עם IF ו- ISERROR (גרסאות לפני Excel 2007)
הפונקציה IFERROR הוצגה ב- Excel 2007 עבור Windows ו- Excel 2016 ב- Mac.
אם אתה משתמש בגרסאות הקודמות, הפונקציה IFERROR לא תפעל במערכת שלך.
ניתן לשכפל את הפונקציונליות של הפונקציה IFERROR באמצעות השילוב של הפונקציה IF ופונקציית ISERROR.
תן לי להראות לך במהירות כיצד להשתמש בשילוב של IF ו- ISERROR במקום IFERROR.
בדוגמה שלמעלה, במקום להשתמש ב- IFERROR, תוכל גם להשתמש בנוסחה המוצגת בתא B3:
= IF (ISERROR (A3), "לא נמצא", A3)
חלק ISERROR בנוסחה בודק את השגיאות (כולל שגיאת מספר לא רלוונטי) ומחזיר TRUE אם נמצאה שגיאה ו- FALSE אם לא.
- אם זה TRUE (כלומר יש שגיאה), הפונקציה IF מחזירה את הערך שצוין ("לא נמצא" במקרה זה).
- אם הוא FALSE (כלומר אין שגיאה), הפונקציה IF מחזירה ערך זה (A3 בדוגמה שלמעלה).
IFERROR נגד IFNA
IFERROR מטפלת בכל מיני טעויות בעוד IFNA מטפלת רק בשגיאה #לא רלוונטית.
בעת טיפול בשגיאות הנגרמות על ידי VLOOKUP, עליך לוודא שאתה משתמש בנוסחה הנכונה.
השתמש ב- IFERROR כאשר אתה רוצה לטפל בכל מיני טעויות. כעת שגיאה יכולה להיגרם מגורמים שונים (כגון נוסחה שגויה, טווח שגוי בשם, אי מציאת ערך החיפוש והחזרת ערך שגיאה מטבלת החיפוש). זה לא משנה ל- IFERROR וזה יחליף את כל השגיאות האלה בערך שצוין.
השתמש ב- IFNA כאשר ברצונך לטפל רק בשגיאות N/A, אשר סביר יותר להיגרם בגלל נוסחת VLOOKUP שלא מצליחה למצוא את ערך החיפוש.
ייתכן שתמצא גם את הדרכות הבאות של Excel שימושיות:
- כיצד להפוך VLOOKUP לרגיש לרישיות.
- VLOOKUP Vs. INDEX/MATCH - הויכוח מסתיים כאן!
- השתמש ב- VLookup כדי לקבל את המספר האחרון ברשימה ב- Excel.
- כיצד להשתמש ב- VLOOKUP עם קריטריונים מרובים
- שגיאת #NAME ב- Excel - מה גורם לה וכיצד לתקן אותה!
