מהי הפונקציה IFERROR של Excel?דוגמה מצורפת

כשאתה עובד עם נתונים ונוסחאות ב-Excel, אתה צפוי להיתקל בשגיאות.

כדי לטפל בשגיאות, Excel מספק פונקציה שימושית - הפונקציה IFERROR.

לפני שנבין את המכניקה של השימוש בפונקציית IFERROR באקסל, בואו נבין תחילה את סוגי השגיאות השונים שאתה עלול להיתקל בהם בעת שימוש בנוסחאות.

סוגי שגיאות באקסל

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

להלן סוגי השגיאות שאתה עשוי למצוא ב-Excel.

#ללא שגיאות

זה נקרא שגיאת "ערך לא זמין".

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

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

#DIV/0!שְׁגִיאָה

ייתכן שתראה שגיאה זו כאשר מספר מחולק ב-0.

זה נקרא שגיאת חלוקה.בדוגמה למטה זה נותן #DIV/0!שגיאה מכיוון שערך הכמות (המחלק בנוסחה) הוא 0.

#ערך!שְׁגִיאָה

שגיאות ערך מתרחשות כאשר אתה משתמש בסוג נתונים שגוי בנוסחה.

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

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

#REF! שְׁגִיאָה

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

בדוגמה למטה, למרות שהנוסחה המקורית היא =A2/B2, כאשר אני מוחק עמודה B, כל ההפניות אליה הופכות ל-#REF!זה גם נתן #REF!שגיאה כתוצאה מהנוסחה.

שגיאה #NAME?

ייתכן שגיאה זו נובעת מאיות שגוי של הפונקציה.

לדוגמה, אם אתה משתמש בטעות ב-VLOKUP במקום ב-VLOOKUP, תקבל שגיאת שם.

#NUM שגיאות

שגיאות מספר עלולות להתרחש אם תנסה לחשב ערך גדול מאוד ב- Excel.לְמָשָׁל,  = 187 ^ 549 יחזיר שגיאה מספרית.

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

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

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

הפונקציה IFERROR של Excel היא דרך מצוינת לטפל בכל סוגי השגיאות ב-Excel.

פונקציית Excel IFERROR - סקירה כללית

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

תחביר פונקציית IFERROR

=IFERROR(value, value_if_error)

קלט פרמטרים

• ערך - זה הפרמטר לבדיקת שגיאות.ברוב המקרים מדובר בנוסחה או בהפניה לתא.
• value_if_error – זהו הערך המוחזר כאשר מתרחשת שגיאה.סוגי השגיאות הבאים הוערכו: #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? ו-#NULL!.

הערות נוספות:

• אם אתה משתמש ב-"" כפרמטר value_if_error, התא אינו מציג דבר כאשר מתרחשת שגיאה.
• אם הפרמטר value או value_if_error מתייחס לתא ריק, הפונקציה IFERROR של Excel מתייחסת אליו כערך מחרוזת ריקה.
• אם ארגומנט הערך הוא נוסחת מערך, IFERROR מחזיר מערך תוצאה עבור כל פריט בטווח שצוין לפי ערך.

פונקציית Excel IFERROR - דוגמה

להלן שלוש דוגמאות לשימוש בפונקציית IFERROR באקסל.

דוגמה 1 - מחזירה תאים ריקים במקום שגיאות

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

בדוגמה המוצגת להלן, התוצאה ב-D4 היא #DIV/0!שגיאה בחלוקה ב-0.

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

=IFERROR(A1/A2,"")

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

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

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

=IFERROR(A1/A2,"שגיאה")

הערה: אם אתה משתמש ב-Excel 2003 או מוקדם יותר, לא תמצא שם את הפונקציה IFERROR.במקרה זה, עליך להשתמש בפונקציה IF בשילוב עם הפונקציה ISERROR.

דוגמה 2 - מחזירה "לא נמצא" כאשר VLOOKUP לא יכול למצוא ערך

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

לדוגמה, להלן מערך נתונים של שמות התלמידים והציונים שלהם.השתמשתי בפונקציה VLOOKUP כדי לקבל את הציונים של שלושה תלמידים (ב-D2, D3 ו-D4).

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

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

להלן הנוסחה שמחזירה "לא נמצא" במקום שגיאה.

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

שימו לב שניתן להשתמש גם ב-IFNA במקום IFERROR ב-VLOOKUP.בעוד IFERROR מטפל בכל סוגי ערכי השגיאה, IFNA חל רק על שגיאות #N/A, לא על שגיאות אחרות.

דוגמה 3 - החזר 0 בשגיאה

אם לא תציין את הערך ש-IFERROR מחזיר במקרה של שגיאה, הוא יחזיר אוטומטית 0.

לדוגמה, אם אחלק 100 ב-0 כפי שמוצג להלן, זה יחזיר שגיאה.

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

דוגמה 4 - שימוש ב- Nested IFERROR עם VLOOKUP

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

פעולה זו דורשת שימוש ב- IFERROR מקונן עם VLOOKUP.

נניח שיש לך מערך נתונים שנראה כך:

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

=IFERROR(VLOOKUP(G3,$A$2:$B$5,2,0),IFERROR(VLOOKUP(G3,$D$2:$E$5,2,0),"Not Found"))

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

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