=IFERROR(B2/C2,0) מחזירה את התוצאה של B2/C2, או 0 כשהתוצאה הזו היא שגיאה. הארגומנט הראשון הוא הנוסחה שאתם רוצים; השני הוא מה להציג במקום כל שגיאה שהיא מייצרת.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Revenue | Units | Plain | With IFERROR |
| 2 | Pens | $120 | 80 | $1.50 | $1.50 |
| 3 | Paper | $300 | 50 | $6.00 | $6.00 |
| 4 | Ink | $90 | 0 | #DIV/0! | $0.00 |
| 5 | Tape | $45 | 30 | $1.50 | $1.50 |
| 6 | Clips | $0 | 0 | #DIV/0! | $0.00 |
ל-Ink ול-Clips יש 0 יחידות, ולכן החילוק הרגיל בעמודה D מציג #DIV/0!. עמודה E מציגה עבורם $0.00 ואת המחיר הרגיל בכל שורה אחרת. הקלידו 15 ב-C4 ושתי העמודות יציגו את המחיר של Ink.
התחביר של IFERROR
=IFERROR(value, value_if_error)
valueהיא הנוסחה לחישוב.value_if_errorמוחזר כש-valueהיא שגיאה כלשהי: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!, והחדשות יותר כמו #CALC!.- אם
valueאינה שגיאה, IFERROR מחזירה אותה בלי שינוי.
ההחלפה יכולה להיות מספר (0), טקסט ("Not found"), טקסט ריק ("") או נוסחה אחרת, למשל חיפוש שני בטבלה אחרת: =IFERROR(VLOOKUP(E2,A2:C6,3,FALSE),VLOOKUP(E2,G2:I6,3,FALSE)).
IFERROR עם VLOOKUP
חיפוש מחזיר #N/A כשהערך לא נמצא בטבלה. עטיפה שלו ב-IFERROR מציגה הודעה במקום:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | Kiwi | Not found | |
| 4 | Carrot | Vegetable | $0.80 | Milk | $1.10 | |
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
Kiwi לא נמצא ברשימה, ולכן F3 אומר Not found. הקלידו Kiwi ב-A4 במקום Carrot ו-F3 ימצא אותו. עם XLOOKUP לא צריך IFERROR בשביל זה, כי הארגומנט הרביעי שלה הוא הערך ל"לא נמצא": =XLOOKUP(E2,A2:A6,C2:C6,"Not found").
IFNA: לתפוס רק את #N/A
IFNA עובדת כמו IFERROR אבל מחליפה רק את #N/A. בחיפושים זה בדרך כלל מה שרוצים: #N/A אומר "לא נמצא", שזו תשובה רגילה, ואילו כל שגיאה אחרת אומרת שהנוסחה עצמה שגויה. בגיליון הזה הנוסחאות מבקשות את עמודה 4 של טבלה בת שלוש עמודות, שגיאת הקלדה:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Look for | IFERROR | IFNA | |
| 2 | Apple | Fruit | 1.2 | Pear | Not found | #REF! | |
| 3 | Pear | Fruit | 1.5 | ||||
| 4 | Carrot | Vegetable | 0.8 | ||||
| 5 | Bread | Bakery | 2.4 | ||||
| 6 | Milk | Dairy | 1.1 |
Pear נמצא בטבלה, ובכל זאת F2 אומר Not found: IFERROR הפכה את ה-#REF! שנגרם ממספר העמודה השגוי לאותה הודעה כמו מוצר חסר. G2 נותנת ל-#REF! לעבור, וכך רואים שהנוסחה שבורה. שנו את ה-4 ל-3 ב-G2 והיא מחזירה 1.5. IFNA דורשת Excel 2013 ואילך.
החזרת תא ריק במקום שגיאה
כדי לא להציג כלום, השתמשו בטקסט ריק, שתי מירכאות כפולות, כהחלפה:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Last year | This year | Growth |
| 2 | Jan | 200 | 240 | 20% |
| 3 | Feb | 0 | 150 | |
| 4 | Mar | 180 | 171 | -5% |
| 5 | Apr | 90 | ||
| 6 | May | 250 | 300 | 20% |
בפברואר ובאפריל לא היו מכירות בשנה שעברה, ולכן אי אפשר לחשב את הצמיחה שלהם והתא נשאר ריק. החודשים האחרים מציגים 20%, מינוס 5% ו-20%. תא עם "" מכיל טקסט: SUM ו-AVERAGE מדלגות עליו, אבל =D3*2 נותנת #VALUE!. אם נוסחאות בהמשך עושות חשבון על העמודה, החזירו 0 במקום.
תרגול: חיפוש עם ערך חלופי
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Stock | Look for | Stock | ||
| 2 | Apple | 40 | Kiwi | |||
| 3 | Pear | 25 | ||||
| 4 | Carrot | 60 | ||||
| 5 | Bread | 12 | ||||
| 6 | Milk | 30 |
תורכם: ב-F2, חפשו את המלאי של המוצר שב-E2 מתוך A2:B6, והציגו "Not found" כשהוא לא ברשימה.
למה הסתרה של כל שגיאה יכולה להסתיר טעויות
IFERROR לא מתקנת שום דבר; היא מחליטה מה התא מציג. לפני שאתם עוטפים בה נוסחה:
- בררו למה השגיאה קורית. כשתא Units ריק גורם ל-#DIV/0!, התיקון האמיתי עשוי להיות נתון שמישהו צריך להזין, לא מחיר אפס.
- העדיפו IFNA בחיפושים, כך שמספר עמודה שגוי (#REF!), שם עם שגיאת כתיב (#NAME?) או טקסט בעמודת מספרים (#VALUE!) עדיין יוצגו.
- בחילוקים, בדקו את המקרה הספציפי.
=IF(C2=0,0,B2/C2)מטפלת במחלק אפס ובשום דבר אחר; הפניה שגויה ב-B2 עדיין מציגה את השגיאה שלה. העמוד על #DIV/0! משווה בין שתי הגישות. - בחרו החלפה שאי אפשר לבלבל עם נתונים. 0 בעמודת מחירים נראה כמו מחיר אמיתי ומוריד את הממוצע;
""או "Not found" לא.
עטפו את הנוסחה בסוף, אחרי שהיא נותנת את התוצאה הנכונה בשורות שאמורות לעבוד.
שאלות נפוצות
איך משתמשים ב-IFERROR עם VLOOKUP?
עטפו את החיפוש: =IFERROR(VLOOKUP(E2,A2:C6,3,FALSE),"Not found"). כש-E2 לא נמצא בעמודה הראשונה, התא מציג Not found במקום #N/A. =IFNA(VLOOKUP(E2,A2:C6,3,FALSE),"Not found") עושה את אותו הדבר ועדיין מציגה שגיאות אחרות.
איך גורמים ל-IFERROR להחזיר תא ריק?
השתמשו בטקסט ריק כארגומנט השני: =IFERROR(B2/C2,""). התא נראה ריק, אבל יש בו טקסט, ולכן =D2+1 עליו נותנת #VALUE!; SUM ו-AVERAGE מדלגות עליו.
מה ההבדל בין IFERROR ל-IFNA?
IFERROR מחליפה כל שגיאה: #N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! ו-#NULL!. IFNA מחליפה רק את #N/A, ה"לא נמצא" של חיפושים, ומשאירה כל שגיאה אחרת גלויה, כך שנוסחה שבורה לא מוסתרת.
איך מחליפים #N/A ב-0 באקסל?
עטפו את הנוסחה ב-IFNA עם 0 כערך: =IFNA(VLOOKUP(E2,A2:C6,3,FALSE),0). ב-XLOOKUP ההחלפה מובנית כארגומנט הרביעי: =XLOOKUP(E2,A2:A6,C2:C6,0).
באילו גרסאות של אקסל יש IFERROR ו-IFNA?
IFERROR קיימת מאז Excel 2007 ו-IFNA מאז Excel 2013. בקבצים ישנים יותר אפשר לראות =IF(ISERROR(B2/C2),0,B2/C2), שעושה את אותה עבודה כמו IFERROR אבל מחשבת את הנוסחה פעמיים.
