תקציב החודש של מחלקת השיווק: 80,000 שקל. ביצוע: 74,200 שקל.
על פניו אין כאן שום חריגה, והתא בעמודת החריגה אמור להחזיר אפס. אבל בדוח כתוב בו מינוס 5,800.
המספרים כאן לדוגמה. הדפוס עצמו חוזר כמעט בכל קובץ תקציב שאני פותח.
מינוס 5,800 הוא מספר סביר. הוא לא שגיאה, לא צבוע באדום, והוא יושב בשורה אחת מתוך שלוש. בשתי השורות האחרות הנוסחה מחזירה בדיוק את מה שהיא צריכה.
הסיבה אינה באג באקסל. הסיבה היא שאותו חישוב נכתב פעמיים בתוך אותה נוסחה, ובחודש שעבר מישהו תיקן רק אחד מהם.
איך נוסחה מגיעה למצב הזה
עמודת החריגה בדוח תקציב מול ביצוע שואלת שאלה פשוטה: אם הביצוע גדול מהתקציב, בכמה. אחרת, אפס.
כדי לענות, הנוסחה צריכה את הביצוע פעמיים. פעם אחת בתנאי, כדי לבדוק אם הוא גדול מהתקציב, ופעם אחת בתוצאה, כדי לחסר ממנו את התקציב. והביצוע עצמו אינו תא. הוא סיכום לפי מחלקה וחודש מתוך גיליון התנועות.
כך נראית הנוסחה בהתחלה, והיא נכונה. אותו סיכום בדיוק כתוב בה פעמיים, מילה במילה.
החודש שבו מישהו תיקן חצי
בגרסה הקודמת של הדוח הסיכום לא סינן לפי חודש. מישהו שם לב שהעמודה מסכמת את כל השנה, פתח את התא, והוסיף את תנאי החודש.
הוא הוסיף אותו לסיכום אחד. את השני הוא לא ראה, כי הנוסחה ארוכה ושני הסיכומים נראים כמעט זהים.
מאותו רגע התנאי בודק את כל השנה והתוצאה מחשבת רק את החודש. ביצוע שנתי גדול כמעט תמיד מתקציב של חודש אחד, ולכן התנאי מתקיים בכל שורה. במחלקה שחרגה, התוצאה נכונה במקרה. במחלקה שלא חרגה, התוצאה היא מספר שלילי שנראה כמו נתון.
זה הדפוס שחוזר בכל נוסחה שכתוב בה אותו חישוב פעמיים. היא נכונה ביום שנכתבה, ונשארת נכונה רק כל עוד כל מי שנוגע בה זוכר שיש שני עותקים.
רגע, מה זה בכלל נוסחת LET באקסל
נוסחת LET באקסל נותנת שם לחישוב בתוך הנוסחה עצמה. כותבים את החישוב פעם אחת, נותנים לו שם, ומשתמשים בשם בכל מקום שצריך אותו.
בתיעוד של מיקרוסופט המבנה מתואר כזוגות של שם וערך, ואחריהם חישוב אחד שמשתמש בהם. חייבים להגדיר לפחות זוג אחד, אפשר להגדיר עד מאה עשרים ושישה, והארגומנט האחרון חייב להיות חישוב שמחזיר תוצאה. השמות קיימים רק בתוך אותה נוסחה, ולא בשום תא אחר.
בגרסה הזו הסיכום כתוב פעם אחת בלבד, בשם actual. התנאי והתוצאה משתמשים באותו שם, ולכן אין שני עותקים שיכולים להתפצל. מי שיוסיף בחודש הבא קריטריון, יוסיף אותו במקום אחד והוא יחול על שניהם.
מה עוד כתוב בתיעוד
החישוב מחושב פעם אחת. בתיעוד כתוב שכאשר אותו ביטוי נכתב כמה פעמים בנוסחה, אקסל מחשב אותו כמה פעמים, ושעם LET הוא מחושב פעם אחת ונקרא בשם. בדוגמה שבעמוד עצמו כתוב שהגרסה עם LET מחשבת פי שניים מהר יותר. בדוח עם אלפי שורות של סיכומים לפי קריטריונים, זה מורגש.
לשם יש כללים. הוא חייב להתחיל באות, ואסור שיתנגש בהפניה לתא. בתיעוד מופיעה דוגמה מפורשת: השם a תקין, והשם c אינו תקין, כי הוא מתנגש בסימון השורות והעמודות. בעמוד כללי השמות כתוב גם שאותו דבר חל על r, שאין רווחים בשם, ושאקסל אינו מבחין בין אותיות גדולות לקטנות.
שם כמו actual או budget עובד, ואומר מה יש בו. שם כמו r, בשביל שיעור, ייכשל.
הגרסאות. בעמוד התיעוד רשומות שש גרסאות: מיקרוסופט 365 ומיקרוסופט 365 למק, אקסל 2024 ואקסל 2024 למק, אקסל 2021 ואקסל 2021 למק. אקסל 2019 אינו ברשימה.
מה עושים בלי LET
מי שעובד על אקסל 2019, או ששולח את הקובץ למי שעובד עליו, צריך דרך אחרת. יש שלוש, ולכל אחת מחיר.
עמודת עזר. מוציאים את הסיכום לעמודה משלו, והנוסחה בעמודת החריגה מפנה אליה פעמיים. זה עובד בכל גרסה, והחישוב גלוי על הגיליון. המחיר הוא עמודה נוספת בדוח, שמישהו ירצה למחוק ביום שבו הוא יעשה סדר.
הערה בתא. הפתרון החלש מבין השלושה, ולפעמים היחיד כשאסור לשנות את מבנה הדוח. הערה שאומרת שהחישוב כתוב פעמיים ושכל שינוי צריך לקרות בשני המקומות. היא לא מונעת את הטעות, רק מזכירה אותה.
פונקציה בשם עם LAMBDA. אם אותו חישוב חוזר בעשרות נוסחאות בקובץ ולא בתא אחד, אפשר להגדיר אותו פעם אחת במנהל השמות ולקרוא לו כמו לכל פונקציה. בתיעוד של LAMBDA רשומות רק מיקרוסופט 365 ואקסל 2024, ולכן זו אינה חלופה ל-2019, אלא הצעד הבא למי שכבר יש לו LET.
איך הופכים נוסחה קיימת
ארבעה צעדים, ותמיד בסדר הזה.
מצאו את החישוב שחוזר. פתחו את התא וחפשו בשורת הנוסחאות רצף שמופיע יותר מפעם אחת. בנוסחאות תקציב זה כמעט תמיד סיכום לפי קריטריונים או חיפוש בטבלה.
השוו את העותקים לפני הכל. אם הם אינם זהים, מצאתם בדיוק את הבעיה שבמאמר הזה, ועכשיו צריך להחליט איזה מהם נכון. שכתוב לפני ההחלטה הזו רק יקבע את הטעות בשם אחד.
תנו לחישוב שם והחליפו כל הופעה. כל ההופעות, ולא רק זו שמולכם.
השוו לנוסחה הישנה בעמודה צמודה. בכל השורות, לפני שמוחקים את הישנה. הפרש בשורה אחת הוא בדיוק מה שהנוסחה הישנה הסתירה.
הכלל
נוסחה שאומרת את אותו דבר פעמיים תתוקן בסוף רק פעם אחת.
וכלל רחב יותר, שנכון לכל קובץ שעובר בין אנשים. כל מידע שכתוב בשני מקומות יתפצל, והשאלה היחידה היא מתי. שם אחד לחישוב אחד אינו עניין של סגנון, אלא הדרך היחידה לוודא שמי שמתקן, מתקן הכל.
והשאלה שנשארה פתוחה
נניח שעמודת החריגה תוקנה. בדוח תקציב מול ביצוע טיפוסי יש עוד עשרות נוסחאות, בגיליונות אחרים, ואף אחד לא יודע כמה מהן כתובות באותו דפוס ובכמה מהן העותקים כבר התפצלו.
השאלה איך מוצאים את כל הנוסחאות האלה בקובץ שלם, בלי לפתוח כל תא בנפרד, אינה נענית כאן.
את הדיון הזה אני מנהל בתוך מועדון The UNIQUE Way, ושם נמצאת גם ערכת LET המלאה: בדיקת חמש הדקות, שש נוסחאות כספים שכתובות פעמיים וגרסת LET של כל אחת, כללי השמות, החלופה לאקסל 2019 ורשימת הבדיקה לפני שמחליפים נוסחה בקובץ חי. ההצטרפות חינם.
