המרתם את הטווח לטבלה, כי ככה עושים נכון. מיזגתם את שורת הכותרת, כי ככה הדוח נראה מסודר. הצבעתם על עמודה שלמה, כי ככה הנוסחה תכסה גם שורות שיתווספו בעתיד.
שלוש החלטות סבירות לגמרי, ושלושתן מופיעות ברשימה של מיקרוסופט כסיבות לאותה שגיאה בדיוק.
התסמין
בתא מופיעה שגיאת SPILL במקום התוצאה. אין הפניה שבורה, אין נתון חסר, ואין שגיאת כתיב בשם הפונקציה.
וזה בדיוק מה שמבלבל. השגיאה הזו אינה אומרת שהנוסחה שגויה, אלא שאין לה לאן לשים את התוצאות שהיא כבר חישבה.
רגע, מה זה בכלל מערך דינמי
נוסחה רגילה מחזירה ערך אחד לתא אחד. נוסחת מערך דינמית מחזירה כמה ערכים בבת אחת, ואקסל פורס אותם לתאים שמסביב לתא שבו היא נכתבה.
הפונקציות שכל איש כספים כבר משתמש בהן עובדות כך. אחת מחזירה את כל השורות שעומדות בתנאי, אחרת מחזירה טבלה ממוינת, ושלישית מחזירה רשימה בלי כפילויות. אף אחת מהן אינה מחזירה ערך אחד, ולכן לכל אחת מהן צריך להיות מקום פנוי לידה.
שבע הסיבות, וכולן באותו עמוד
מיקרוסופט מונה שבע סיבות, ולכל אחת ניסוח משלה שמופיע בתוך התא כשלוחצים על משולש השגיאה.
טווח הפריסה אינו ריק. תא אחד בטווח היעד מכיל משהו. לעיתים קרובות זה רווח בודד בתא שנראה ריק לחלוטין.
הגודל אינו ניתן לקביעה. בתיעוד כתוב שאקסל לא הצליח לקבוע את גודל המערך מפני שהוא תנודתי ומשנה את גודלו בין מעברי חישוב. זה קורה כשמעורבת פונקציה שמחזירה ערך אקראי.
הפריסה חורגת מגבול הגיליון. נוסחת חיפוש שמצביעה על עמודה שלמה מחזירה תוצאה לכל שורה בעמודה. בתיעוד כתוב שאקסל מגיע כך לסוף הרשת, ומספר השורות שמופיע שם הוא 1,048,576.
הנוסחה יושבת בתוך טבלה. בתיעוד כתוב שנוסחאות מערך נפרסות אינן נתמכות בטבלאות של אקסל.
חוסר זיכרון. המערך גדול מכדי להיכנס לזיכרון הפנוי, וההצעה בתיעוד היא להצביע על טווח קטן יותר.
פריסה לתוך תאים ממוזגים. בתיעוד כתוב שנוסחאות מערך נפרסות אינן יכולות להיפרס לתוך תאים ממוזגים.
סיבה שאקסל אינו מזהה. ניסוח כללי שמופיע כשהמנוע אינו מצליח לסווג את המקרה. במצב הזה כדאי לבדוק שכל הארגומנטים שהנוסחה דורשת אכן נמסרו לה.
שתי סיבות שמפילות דוח כספי, ואף אחת מהן אינה טעות
שתיים מהשבע ראויות לתשומת לב מיוחדת, כי הן אינן שגיאה של אף אחד. הן תוצאה של החלטות שנחשבות עבודה נכונה.
הראשונה היא הטבלה. טבלה באקסל מתרחבת לבד, שומרת על העיצוב, ומאפשרת להפנות אליה בשם. בכל קורס לומדים להמיר טווח לטבלה, ובצדק. אבל בעמוד התיעוד על נוסחאות מערך דינמיות מיקרוסופט כותבת שנוסחאות מערך נפרסות אינן נתמכות בתוך טבלאות, ושיש למקם אותן בגיליון מחוץ לטבלה.
שימו לב מה זה אומר. הבעיה כאן אינה שהטווח תפוס. הטווח יכול להיות ריק לגמרי, והשגיאה תופיע בכל מקרה.
השנייה היא המיזוג. תא ממוזג אחד באזור שאליו התוצאות אמורות להיפרס עוצר את הפריסה כולה. בדוח מעוצב זה כמעט תמיד שורת הכותרת, וזה גם המקום האחרון שמישהו יבדוק, כי מיזוג נראה כמו עיצוב ולא כמו תוכן.
סדר האבחון
ארבע בדיקות, וכולן יחד לוקחות פחות מדקה.
ראשית, סמנו את התא שמראה את השגיאה. מסביב לטווח שאליו הנוסחה ניסתה להיפרס מופיעה מסגרת מקווקוות. היא מראה כמה מקום היה צריך, ולכן היא מראה גם איפה יושב מה שמפריע.
שנית, פתחו את התפריט שנפתח ליד התא. מיקרוסופט מתעדת שם פעולה שקופצת ישירות אל התאים החוסמים. זו הדרך היחידה שמוצאת רווח בודד בלי לחפש אותו בעיניים.
שלישית, בדקו אם התא יושב בתוך טבלה. אם כשהסמן בתא מופיעה ברצועה לשונית של עיצוב הטבלה, זו התשובה, ואין טעם להמשיך לחפש.
רביעית, בדקו מיזוגים באזור היעד. שורת כותרת ממוזגת מעל טווח פנוי היא המקרה שתופס אנשים הכי הרבה פעמים.
התיקון
לכל סיבה יש תשובה משלה, ואף אחת מהן אינה נוסחה חלופית.
כשהטווח תפוס, קופצים אל התאים החוסמים מתוך תפריט השגיאה, ומוחקים או מזיזים את מה שיושב שם.
כשהנוסחה בתוך טבלה, מיקרוסופט מציעה שתי דרכים: להעביר את הנוסחה אל הגיליון מחוץ לטבלה, או להמיר את הטבלה לטווח. הראשונה עדיפה כמעט תמיד. ההמרה אכן פותרת את השגיאה, אבל במחיר של ביטול כל מה שבגללו הטבלה נבנתה מלכתחילה, והנוסחה יכולה להמשיך להצביע על הטבלה גם כשהיא יושבת מחוץ לה.
כשיש תאים ממוזגים, מבטלים את המיזוג או מעבירים את הנוסחה לטווח שאינו חותך תאים ממוזגים. בדוחות שיוצאים מהמחלקה עדיף לוותר על המיזוג באזור שאליו נפרסות תוצאות, ולהשאיר את העיצוב לשורות שמעליו.
כשההפניה היא לעמודה שלמה, מצביעים על הטווח הנדרש בלבד. בתיעוד מופיעות שלוש דרכים לאותו מצב: להצביע רק על הערכים שצריך, להצביע על ערך יחיד באותה שורה, או להשתמש באופרטור החיתוך המשתמע שמבקש מהנוסחה להחזיר ערך אחד. הראשונה נכונה ברוב המקרים, כי היא גם מזרזת את החישוב.
המלכודת שמופיעה רק אצל מי שקיבל את הקובץ
זה החלק שנעלם כמעט מכל מדריך, והוא זה שמפיל דוחות שעוברים בין אנשים.
מיקרוסופט מתעדת שכאשר קובץ עם נוסחה כזו נפתח בגרסה שאינה מכירה מערכים דינמיים, הנוסחה נראית שם כנוסחת מערך מהסוג הישן. ובאותו עמוד כתוב שההבדל העיקרי הוא שנוסחאות מהסוג הישן אינן יכולות לשנות את גודלן, ואין להן מסגרת פריסה.
תרגמו את זה לשפה של סגירת חודש. אצלכם הטווח גדל מעצמו כשנוספה שורה. אצל מי שקיבל את הקובץ הוא נשאר בגודל שהיה ביום ששלחתם אותו, והשורה החדשה פשוט אינה מופיעה.
אף אחד משני הצדדים אינו מקבל הודעה על כך.
באותו עמוד מיקרוסופט מפנה אל בודק התאימות, ככלי שמאתר מראש את הנוסחאות שיתנהגו כך בגרסאות ישנות יותר. שווה להריץ אותו על כל קובץ שיוצא מהמחלקה, ולא רק אחרי שמשהו כבר נשבר.
הכלל
שגיאת SPILL היא ההודעה המדויקת ביותר שאקסל יודע לתת, ורוב האנשים קוראים אותה הפוך.
היא אינה אומרת שהנוסחה שגויה. היא אומרת שהנוסחה בסדר גמור, ושהגיליון סביבה בנוי כך שאין לתוצאה לאן להיכנס.
מי שמבין את ההבדל מפסיק לחפש נוסחה חלופית ומתחיל לבדוק את המבנה. זה מה שהופך תעלומה של רבע שעה לבדיקה של דקה.
והשאלה שנשארה פתוחה
נניח שהזזתם את הנוסחה מחוץ לטבלה, ביטלתם את המיזוג, ותחמתם את ההפניות. הדוח עובד, והוא גם מתרחב לבד כשנוספות שורות.
וכאן מתחילה השאלה שהמאמר הזה לא עונה עליה. טווח שגדל מעצמו הוא יתרון עד הרגע שבו משהו יושב מתחתיו, ומאותו רגע הוא סיכון. איפה עובר הגבול בין דוח שמסתגל לנתונים לבין דוח שהמבנה שלו חייב להישאר קבוע, מה נכון לקבע במכוון גם כשאפשר לא לקבע, ואיך מסבירים את ההחלטה הזו למי שרק רוצה לפתוח את הקובץ ולראות מספר.
את הדיון הזה אני מנהל בתוך מועדון The UNIQUE Way, ושם נמצא גם עץ ההחלטה המלא, עם שבע הסיבות בניסוח המדויק שלהן, ארבע בדיקות האבחון לפי הסדר, ורשימת בדיקה לפני שליחת קובץ החוצה. ההצטרפות חינם.
