שלוש שגיאות אקסל, שלושה כשלים שונים: מקום תפוס, הפניה שנמחקה, וערך שלא נמצא. מה כל אחת מהן אומרת, ולמה עטיפה ב-IFERROR מוחקת את הראיות במקום לתקן.
עיקרי הדברים
- #SPILL! היא שגיאת מקום. מיקרוסופט מפרטת סיבות כמו טווח שפיכה שאינו ריק, טווח החורג מגבולות הגיליון, נוסחת מערך בתוך טבלה, ותאים ממוזגים.
- #REF! היא הרסנית: ההפניה נמחקה ולא ניתן לשחזר אותה מתוך הנוסחה. ביטול פעולה מיד אחרי המחיקה הוא לרוב הסיכוי הטוב ביותר.
- #N/A היא לעיתים קרובות תשובה נכונה. מיקרוסופט מציינת שהיא מעידה שהנוסחה לא מצאה את מה שהתבקשה לחפש.
- עטיפה גורפת ב-IFERROR הופכת את שלוש השגיאות לתא ריק ומוחקת את ההבדל ביניהן.
- אקסל מציע את הפקודה Select Obstructing Cells עבור שגיאת שפיכה, וזה חוסך את החיפוש הידני אחרי התא החוסם.
שלוש השגיאות מתארות שלושה כשלים שונים לגמרי. השגיאה הראשונה אומרת שהנוסחה תקינה אבל אין לה מקום פנוי להישפך אליו. השנייה אומרת שההפניה עצמה כבר אינה קיימת, בדרך כלל אחרי מחיקה. השלישית אומרת שהחיפוש רץ כראוי ולא מצא את הערך. רק הראשונה נפתרת בפינוי תאים.
שלוש שגיאות, שלושה סיפורים שונים
| #SPILL! | #REF! | #N/A | |
|---|---|---|---|
| מה קרה | לנוסחה אין מקום פנוי לתוצאה | ההפניה שהנוסחה מצביעה אליה אינה קיימת | החיפוש הסתיים בלי התאמה |
| מצב הנוסחה | תקינה | שבורה | תקינה |
| האם נתונים אבדו | לא | ייתכן שכן | לא |
| התיקון האופייני | לפנות את טווח השפיכה | לשחזר את ההפניה או לבטל את המחיקה | לבדוק את ערך החיפוש ואת המקור |
| כמה דחוף | נמוך | גבוה | תלוי הקשר |
השורה השלישית היא ההבדל המעשי. שתיים מהשגיאות הן הודעות; השלישית היא עדות לכך שמשהו במבנה הגיליון כבר נהרס.
מה גורם לשגיאת שפיכה ואיך מנקים אותה?
הסיבה תמיד אחת מבחינה מהותית - התוצאה אינה נכנסת - אבל מיקרוסופט מפרטת כמה וריאנטים, וכל אחד מהם דורש טיפול אחר:
- טווח השפיכה אינו ריק. תא אחד או יותר בטווח כבר מכיל נתון. מיקרוסופט מציינת שהטווח עשוי להיראות ריק ובכל זאת להכיל תווים בלתי נראים או נוסחאות שמחזירות מחרוזת ריקה.
- הטווח חורג מגבולות הגיליון. לגיליון יש 1,048,576 שורות ו-16,384 עמודות, וכל שפיכה שחורגת מכך תיכשל. זה קורה כשמפנים לעמודה שלמה מתא שאינו בשורה הראשונה.
- נוסחת מערך בתוך טבלה. נוסחאות מערך נשפכות אינן נתמכות בטבלאות אקסל. הפתרון הוא להוציא את הנוסחה מהטבלה או להמיר את הטבלה לטווח.
- תאים ממוזגים בדרך. שפיכה אינה יכולה להיכנס לתאים ממוזגים. יש לבטל את המיזוג או להעביר את הנוסחה לטווח שאינו חופף להם.
- חריגה מזיכרון. הנוסחה מנסה לייצר מערך גדול מדי. יש לצמצם את הטווח שאליו מפנים.
הכלי המהיר: לחיצה על סמן השגיאה ובחירה ב-Select Obstructing Cells מסמנת בדיוק את התאים שחוסמים את השפיכה, במקום לחפש אותם ידנית.
שגיאת הפניה אינה תקלה - היא נזק
השגיאה מופיעה כשנוסחה מפנה לתא שאינו תקף עוד, וזה קורה לרוב כשתאים שהוזכרו בנוסחה נמחקו או נדרסו. ההבדל המהותי משתי האחרות: המידע על מה שהיה שם אינו קיים יותר בנוסחה. אקסל אינו זוכר לאן הפניתם קודם.
הצורות הנפוצות, כפי שמיקרוסופט מתארת אותן:
- מחיקת שורה או עמודה שהוזכרו בהפניה מפורשת לתאים בודדים. הפניה לטווח שלם עמידה יותר מרשימת תאים מופרדת בפסיקים.
- VLOOKUP שמבקש מספר עמודה גדול ממספר העמודות בטווח החיפוש.
- INDEX שמבקש שורה או עמודה מחוץ לגבולות הטווח שהוגדר.
- INDIRECT שמפנה לחוברת עבודה סגורה.
- קישורי OLE או DDE שבורים אל תוכנית שאינה פועלת.
הכלל התפעולי: אם ראיתם שגיאת הפניה שהופיעה כרגע, אל תמשיכו לעבוד ואל תשמרו. ביטול הפעולה מיד אחרי המחיקה הוא הדרך היחידה שמחזירה את ההפניה בלי לבנות אותה מחדש ביד.
מתי #N/A היא בעצם התשובה הנכונה?
כשהערך באמת אינו קיים במקור. מיקרוסופט מגדירה את השגיאה כאינדיקציה לכך שהנוסחה לא מצאה את מה שהתבקשה לחפש, ובמודל תקין זו לעיתים קרובות בדיוק המסקנה שרציתם: לקוח בלי הזמנה, פריט שאינו בקטלוג, חודש שטרם נסגר.
הסיבות שכן דורשות תיקון, לפי מיקרוסופט, ניתנות להפרדה מהירה:
- ערך החיפוש והמקור אינם מאותו סוג נתונים - מספר מול טקסט הוא המקרה הקלאסי.
- רווחים מובילים או נגררים בתאים. TRIM פותר, אבל רק אחרי שווידאתם שזו אכן הסיבה.
- חיפוש מקורב במקום מדויק, כלומר TRUE במקום FALSE בארגומנט ההתאמה.
- טווח מערך שאינו באותו מספר שורות או עמודות כמו הטווח שמכיל את הנוסחה.
- ערך שהוזן ידנית באמצעות NA() ומעולם לא הוחלף בנתון אמיתי.
- ארגומנט חסר בפונקציה.
למה IFERROR הוא בדרך כלל התיקון הלא נכון
כי הוא תופס את שלוש השגיאות יחד ומחזיר את אותו ערך לכולן. מודל שעטוף ב-IFERROR יציג אפס עבור לקוח שלא נמצא, אפס עבור הפניה שנמחקה, ואפס עבור נוסחה שנשברה בגלל מיזוג תאים - ואין שום דרך להבדיל ביניהם בדיעבד.
- אם אתם רוצים לטפל בערך שלא נמצא בלבד, IFNA מצומצם יותר ומשאיר שגיאות הפניה גלויות.
- החליטו מה הערך החלופי אומר. אפס ו"לא נמצא" הן שתי טענות שונות בדוח.
- אל תעטפו לפני שאבחנתם. עטיפה היא החלטה על אופן ההצגה, לא כלי אבחון.
סדר שחזור ששומר על הראיות
- עצרו. אל תשמרו ואל תמשיכו לערוך חוברת עבודה שהתמלאה זה עתה בשגיאות הפניה.
- נסו ביטול פעולה מיידי. זו הפעולה היחידה שמחזירה הפניה שנמחקה בלי בנייה מחדש.
- אם השמירה כבר בוצעה, שחזרו גרסה קודמת לקובץ חדש ואל תדרסו את הקיים.
- מפו את הפגיעה לפי סוג וספרו כמה מכל שגיאה. הספירה קובעת סדר עדיפויות.
- תקנו את שגיאות ההפניה ראשונות, כי כל תיקון אחר מסתמך על מבנה תקין.
- אחר כך פנו את טווחי השפיכה, ורק בסוף טפלו בערכי החיפוש.
- השאירו ערך שאינו שגיאה אמיתית גלוי, או החליפו אותו בטקסט מפורש כמו "לא נמצא".
הסדר הזה אינו שרירותי. תיקון ערכי חיפוש לפני תיקון ההפניות מייצר עבודה כפולה, כי כל נוסחה שתוקנה תישבר שוב ברגע שתשחזרו את המבנה שמתחתיה.
איך מונעים את הסבב הבא
- החליפו הפניות לתאים בודדים בהפניות לטווח או בטבלאות עם שמות. מחיקת שורה בתוך טווח אינה שוברת אותו כפי שהיא שוברת רשימה של תאים.
- הפרידו בין גיליון קלט לגיליון חישוב. מחיקות מתרחשות כמעט תמיד בגיליון שאנשים עורכים ידנית.
- אל תשתמשו בתאים ממוזגים בטווחים שנוסחאות כותבות אליהם. זו הסיבה השקטה ביותר לשגיאות שפיכה.
- ודאו שיש היסטוריית גרסאות פעילה לקובץ. בלעדיה, ביטול פעולה הוא ההזדמנות היחידה שלכם.
קשור: עריכה מקבילית של גיליון ובקרת התנגשויות, מתי גיליון כבר לא מספיק, שיטה לאיחוד עשרות גיליונות.
מקורות
שאלות נפוצות
הטווח נראה ריק ועדיין מתקבלת שגיאת שפיכה. למה?
מיקרוסופט מציינת שטווח השפיכה עשוי להיראות ריק ובכל זאת להכיל תווים בלתי נראים או נוסחאות שמחזירות מחרוזת ריקה. הדרך המהירה לאתר את החוסם היא הפקודה Select Obstructing Cells מתוך סמן השגיאה, שמסמנת את התאים עצמם.
אפשר לשחזר את ההפניה המקורית אחרי שנמחקה?
לא מתוך הנוסחה. אקסל אינו שומר לאן ההפניה הצביעה לפני שנמחקה. שתי הדרכים היחידות הן ביטול הפעולה מיד אחרי המחיקה, או שחזור גרסה קודמת של הקובץ ובנייה מחדש של ההפניה על בסיסה.
האם צריך לתקן כל ערך שלא נמצא?
לא. השגיאה מעידה שהחיפוש לא מצא התאמה, וזו לעיתים קרובות התוצאה הנכונה - למשל פריט שאינו קיים בקטלוג. יש לתקן אותה כשהיא נובעת מאי-התאמת סוגי נתונים, מרווחים מיותרים, מחיפוש מקורב במקום מדויק, או מארגומנט חסר.
למה נוסחת מערך עובדת מחוץ לטבלה ונכשלת בתוכה?
כי נוסחאות מערך נשפכות אינן נתמכות בתוך טבלאות אקסל. זו מגבלה מוצהרת ולא תקלה. שתי האפשרויות הן להעביר את הנוסחה אל מחוץ לטבלה, או להמיר את הטבלה לטווח רגיל ולוותר על התכונות שהטבלה מספקת.
להמשך קריאה
שירות רלוונטי
הצלת מערכות אקסס ואקסל
לייצב את הקובץ שהעסק רץ עליו, ואז לתקן אותו או להעביר אותו לווב.
על הכותב
יהונתן סעדיה
מפתח פרילנסר לאוטומציה, אתרים ו-MVP
אני יהונתן סעדיה, מפתח בכיר שבונה אוטומציה עסקית, אתרים מותאמים ומוצרי MVP לעסקים קטנים ובינוניים בארה"ב, אירופה וישראל. המדריכים האלה נכתבים מתוך עבודה אמיתית עם לקוחות, לא מתיאוריה.
בוא נעבוד יחדיש לך פרויקט דומה?
ספר לי מה אתה רוצה לבנות או איזה תהליך להפוך לאוטומטי. אני חוזר תוך 24 שעות עסקים עם כמה שאלות ממוקדות, ואז עוברים על זה יחד בשיחת היכרות חינמית של 30 דקות, בלי התחייבות. בסוף יש לך היקף עבודה, לוח זמנים ומחיר קבוע - או תשובה כנה שלא שווה לבנות את זה.