פונקציית Excel IFERROR - דוגמאות לנוסחאות + סרטון חינם

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

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

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

סוגי שגיאות ב- Excel

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

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

#N/A שגיאה

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

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

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

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

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

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

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

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

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

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

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

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

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

שגיאת שם

סביר להניח ששגיאה זו היא תוצאה של פונקציה שגויה.

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

#מספר שגיאה

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

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

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

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

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

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

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

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

= IFERROR (ערך, value_if_error)

טענות קלט

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

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

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

פונקציית IFERROR של Excel - דוגמאות

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

דוגמה 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 - שימוש ב- IFERROR מקונן עם VLOOKUP

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

לשם כך יש להשתמש ב- IFERROR מקונן עם VLOOKUP.

נניח שיש לך מערך נתונים כפי שמוצג להלן:

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

= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), "לא נמצא"))

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

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

פונקציית Excel IFERROR - וידיאו

• Excel ופונקציה.
• Excel או פונקציה.
• Excel NOT פונקציה.
• פונקציית Excel IF.
• פונקציית IFS של Excel.
• פונקציית Excel FALSE.
• פונקציית TRUE של Excel.