בעת שימוש בנוסחאות VLOOKUP ב-Excel, לפעמים אתה עלול להיתקל בשגיאות #N/A מכוערות.זה קורה כאשר הנוסחה שלך לא יכולה למצוא את ערך הבדיקה.
במדריך זה, אני אראה לך את הדרכים השונות להשתמש ב- IFERROR ו- VLOOKUP כדי לטפל בשגיאות #N/A אלה בגיליונות עבודה.
השתמש ב-IFERROR בשילוב עם VLOOKUP כדי להציג משהו משמעותי במקום שגיאה #N/A (או כל שגיאה אחרת).
לפני שניכנס לפרטים של אופן השימוש בשילוב זה, בואו נסתכל במהירות על הפונקציה IFERROR כדי לראות כיצד היא פועלת.
תוכן
תיאור הפונקציה IFERROR
באמצעות הפונקציה IFERROR, ניתן לציין מה צריך לקרות כאשר נוסחה או הפניה לתא מחזירות שגיאה.
זהו התחביר של הפונקציה IFERROR.
=IFERROR(value, value_if_error)
- ערך - זה הפרמטר לבדיקת שגיאות.ברוב המקרים מדובר בנוסחה או בהפניה לתא.בעת שימוש ב-VLOOKUP עם IFERROR, נוסחת VLOOKUP תהיה פרמטר זה.
- value_if_error – זהו הערך המוחזר כאשר מתרחשת שגיאה.סוגי השגיאות הבאים הוערכו: #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? ו-#NULL!.
סיבות אפשריות לכך ש-VLOOKUP מחזיר שגיאות #N/A
הפונקציה VLOOKUP עשויה להחזיר שגיאת #N/A מכל אחת מהסיבות הבאות:
- ערך הבדיקה לא נמצא במערך בדיקת המידע.
- ישנם רווחים מובילים, נגררים או כפולים בערך הבדיקה (או מערך הטבלה).
- ישנה שגיאת הקלדה בערך חיפוש או ערך חיפוש במערך.
אתה יכול להשתמש ב- IFERROR בשילוב עם VLOOKUP כדי לטפל בכל גורמי השגיאה הללו.עם זאת, עליך להיות מודע לסיבות #2 ו- #3, ולתקן אותן בנתוני המקור, במקום לתת ל-IFERROR לטפל בהן.
הערה: IFERROR יטפל בשגיאה ללא קשר למה שגרם לה.אם ברצונך לטפל רק בשגיאות שנגרמו מכך ש-VLOOKUP לא יכול למצוא את ערך הבדיקה, השתמש ב-IFNA במקום זאת.זה יבטיח שגיאות אחרות מלבד #N/A לא יטופלו, ותוכל לחקור את השגיאות האחרות הללו.
אתה יכול להשתמש בפונקציית 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),"Not Found"))
שימוש ב-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 של הנוסחה בודק שגיאות (כולל #N/A שגיאות) ומחזיר TRUE אם נמצאה שגיאה, אחרת FALSE.
- אם TRUE (מציין שגיאה), הפונקציה IF מחזירה את הערך שצוין ("לא נמצא" במקרה זה).
- אם FALSE (מה שאומר שאין שגיאה), הפונקציה IF תחזיר את הערך הזה (A3 בדוגמה למעלה).
IFERROR ו-IFNA
IFERROR מטפל בכל סוגי השגיאות, בעוד ש-IFNA מטפל רק בשגיאות #N/A.
כאשר מתמודדים עם שגיאות הנגרמות על ידי VLOOKUP, עליך לוודא שאתה משתמש בנוסחה הנכונה.
כאשר אתה רוצה לטפל בשגיאות שונות, בבקשההשתמש ב- IFERROR.שגיאות יכולות להיגרם כעת ממגוון גורמים (כגון נוסחאות שגויות, טווחי שמות שגויים, ערכי חיפוש לא נמצאו וערכים שגויים שהוחזרו מטבלאות חיפוש).IFERROR לא משנה, הוא מחליף את כל השגיאות האלה בערך שצוין.
השתמש ב-IFNA כאשר אתה רוצה לטפל רק בשגיאות #N/A, שסביר יותר שנגרם מכך שנוסחת VLOOKUP לא מצליחה למצוא את ערך הבדיקה.
