איך מתקנים את #N/A ושגיאות אחרות ב-Excel
כדי לתקן שגיאה ב-Excel, קרא מה אומר קוד השגיאה, כי לכל אחד מהם סיבה אחרת. #N/A פירושו שחיפוש לא מצא כלום, #DIV/0! פירושו חילוק באפס, ו-#VALUE! פירושו ערך מסוג לא נכון. תקן קודם את הסיבה. השתמש ב-IFERROR כדי להסתיר שגיאה רק כשאתה מצפה לה.
1. #N/A: החיפוש לא מצא כלום
הפונקציות VLOOKUP, XLOOKUP ו-MATCH מחזירות #N/A כשהערך שמחפשים אינו ברשימה. בדוק שלושה דברים: רווח בסוף הערך (נקה אותו עם TRIM), מספר שמאוחסן כטקסט בצד אחד בלבד, וטווח טבלה שלא כולל את השורה. כשערך חסר הוא מצב רגיל, השתמש ב-IFNA כדי להחליף רק את השגיאה הזו.
דוגמה: קוד חסר הופך להודעה ברורה
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | קוד | מוצר | חפש | תוצאה | |
| 2 | P-100 | עט | P-999 | לא נמצא | |
| 3 | P-101 | בלוק | |||
| 4 | P-102 | דיו |
P-999 לא נמצא ברשימה, לכן VLOOKUP מחזירה #N/A, ו-IFNA מחליפה אותה בהודעה.
2. #DIV/0!: חילוק באפס
Excel לא יכול לחלק באפס או בתא ריק. מצא את המכנה שהוא אפס או ריק. אם אפס הוא מצב רגיל, כמו לקוח שעדיין אין לו הזמנות, טפל בו בעזרת IFERROR.
דוגמה: ממוצע בלי הזמנות
| A | B | C | |
|---|---|---|---|
| 1 | סך הכול | הזמנות | ממוצע |
| 2 | 10 | 0 | 0 |
10 חלקי 0 הוא #DIV/0!, לכן IFERROR מחזירה את ה-0 שבחרת.
3. #VALUE!, #REF!, #NAME? ו-#NUM!
#VALUE! פירושו ערך מסוג לא נכון, כמו טקסט בתוך חישוב או רווח בתא מספר. בדוק תא עם ISNUMBER. #REF! פירושו שהפניה מצביעה על תא שנמחק, לכן בטל את המחיקה או כתוב מחדש את ההפניה. #NAME? פירושו ש-Excel לא מכיר מילה בנוסחה: פונקציה שנכתבה בשגיאה, גרשיים חסרים סביב טקסט, או שם שאינו קיים. #NUM! פירושו שמספר בלתי אפשרי, כמו שורש ריבועי של מספר שלילי.
4. #SPILL! ו-####
#SPILL! מופיע כששורת נוסחה דינמית צריכה מקום, ומשהו חוסם את התאים שמתחתיה או לצדה. נקה אותם. הסימן #### בתא אינו שגיאה בנוסחה: העמודה צרה מדי למספר או לתאריך, לכן הרחב אותה.
5. מצא את הסיבה עם Evaluate Formula
עבור אל Formulas > Evaluate Formula ולחץ על Evaluate. Excel מבצע את הנוסחה חלק אחר חלק, כך שרואים בדיוק איזה חלק הופך לשגיאה. המשולש הירוק בפינת התא מציג גם תפריט עם אפשרויות לבדיקת שגיאות.
טעויות נפוצות
עטיפת כל נוסחה ב-IFERROR מההתחלה. זה מסתיר בעיות אמיתיות, כמו טווח שגוי, והגיליון מציג בשקט אפסים.
תיקון: מצא ותקן קודם את הסיבה. השתמש ב-IFNA רק כשצפוי חיפוש שלא נמצא.
חיפוש מספר מול טקסט. המספר 100 והטקסט "100" נראים אותו דבר, אבל חיפוש לא מתאים ביניהם.
תיקון: הפוך את שני הצדדים לאותו סוג, למשל בעזרת VALUE או בכפל ב-1.
מחיקת עמודה שהנוסחאות משתמשות בה. הנוסחאות מציגות #REF!.
תיקון: בטל מיד עם Ctrl+Z, או נקה את התאים במקום למחוק אותם.
שאלות נפוצות
מה ההבדל בין IFERROR ל-IFNA?
IFERROR מטפלת בכל שגיאה. IFNA מטפלת רק ב-#N/A, וזה בטוח יותר לחיפושים כי היא לא מסתירה טווח שגוי או הקלדה שגויה.
למה אני מקבל #N/A כשהערך נמצא ברשימה?
בדרך כלל יש רווח בסוף, מספר שמאוחסן כטקסט, או התאמה מדויקת על ערך שנבדל בתו אחד. בדוק את שני הצדדים עם TRIM ו-ISNUMBER.
איך מוצאים את כל השגיאות בגיליון?
לחץ על Ctrl+G, לחץ על Special, בחר Formulas וסמן Errors only. Excel בוחר כל תא שמציג שגיאה.
פונקציות קשורות
- IFERRORIFERROR מחזירה ערך שתבחר כשנוסחה מייצרת שגיאה כמו #N/A או #DIV/0!, ואחרת את התוצאה הרגילה.
- VLOOKUPVLOOKUP מחפשת ערך בעמודה הראשונה של טבלה, ומחזירה ערך מאותה שורה בעמודה אחרת.
- XLOOKUPXLOOKUP מחפשת בעמודה או בשורה אחת, ומחזירה את הערך המתאים מעמודה אחרת, לכל כיוון, עם הודעת "לא נמצא" מובנית.
- TRIMTRIM מסירה רווחים מיותרים מטקסט: בהתחלה, בסוף, וכפולים בין מילים.
- IFIF בודקת תנאי ומחזירה ערך אחד אם הוא מתקיים וערך אחר אם לא.
עוד על שגיאות ב-Excel
המדריך מסביר כל שגיאה וכיצד לתקן אותה. החלק הזה מוסיף דרך מהירה למצוא את הסיבה בנוסחה ארוכה, איך לבדוק תאים לפני החישוב, ואיך לשמור שגיאות מחוץ לסכומים.
מצא את הסיבה בנוסחה ארוכה
סמן חלק מהנוסחה בשורת הנוסחאות ולחץ על F9. Excel מציג את הערך של החלק הזה בלבד, כך שרואים איזה חלק מחזיר את השגיאה. לחץ על Esc אחר כך, אחרת תחליף את הנוסחה בערך שלה. Formulas > Evaluate Formula עושה את אותו הדבר שלב אחר שלב, בלי הסיכון הזה.
בדוק את התאים לפני החישוב
פונקציות IS מחזירות TRUE או FALSE בלי להציג שגיאה. ISNUMBER מראה אם ערך הוא מספר אמיתי, ו-ISBLANK מראה אם תא ריק. השתמש בהן כדי להחליט מה לעשות.
=IF(ISNUMBER(B2),B2*2,"check input")שמור על שגיאות מחוץ לסכומים
שגיאה אחת בטווח הופכת גם את SUM של הטווח לשגיאה. התיקון הוא לתקן את תא המקור, או להשתמש ב-AGGREGATE, שיכול להתעלם משגיאות. לסכום מהיר, עטוף כל תא ב-IFERROR והחזר 0, אבל רק כשאפס הוא התשובה הנכונה לשורה הזו. תא ריק או מקף הם לעתים כנים יותר מאפס, כי אפס נראה כמדידה אמיתית ומושך ממוצעים כלפי מטה. לא משנה מה תבחר, כתוב את הכלל פעם אחת והשתמש בו בכל מקום, כך שהגיליון יתנהג אותו דבר בכל עמודה.
המשך עם [שיעור השגיאות](/learn/errors-iferror), [פונקציות IS](/learn/is-functions) ו[עמוד IFERROR](/functions/iferror).