בעמודת הסטטוס יש 8,400 שורות, והשאלה שנשאלה פשוטה: כמה פעמים מופיע כל ערך. סימנתם את העמודה, הסתכלתם על שורת המצב בתחתית החלון, וקיבלתם מספר תוך שנייה.
על פניו זה נגמר. אבל המספר הזה עונה על שאלה אחרת מזו ששאלתם, והמסך לא יגיד לכם את זה.
בקהילת אקסל נשאלה בדיוק השאלה הזו, איך מקבלים בלחיצה אחת ספירה של כל ערך בעמודה. התשובות שהתקבלו שם היו ארבע שיטות שונות, והן אינן מחזירות אותו מספר.
התסמין, ולמה הוא לא נראה כמו תקלה
שגיאה בגיליון צועקת. מספר שגוי יושב בשקט, עם עיצוב תקין ושתי ספרות אחרי הנקודה.
שני אנשים סופרים את אותה עמודה, אחד מקבל 412 ואחד מקבל 380, ושניהם צודקים. אחד ספר שורות והשני ספר לקוחות.
זה קורה כמעט תמיד באותו מקום: קובץ שהגיע מייצוא של מערכת, שבו לכל ישות יש יותר משורה אחת. לקוח עם ארבע חשבוניות, עובד עם שתים עשרה נוכחויות, פרויקט עם שלושים תנועות. ברגע שסופרים שורות ומדווחים ישויות, המספר גדול מדי בכל דוח שיוצא מהקובץ הזה, וההפרש קבוע ולכן הוא נראה אמין.
רגע, מה זה בכלל ספירה
ספירת ערכים באקסל נשמעת כמו פעולה אחת, ובפועל היא ארבע פעולות שחולקות שם אחד.
יש ספירה של תאים שאינם ריקים, יש ספירה של תאים שהערך בהם מספרי, יש ספירה של כמה שורות יש לכל ערך בנפרד, ויש ספירה של כמה ערכים שונים קיימים בעמודה בכלל.
לפני שבוחרים כלי בוחרים את השאלה. זה נשמע טריוויאלי, וזה ההבדל בין 412 ל-380.
ארבע הדרכים, ומה כל אחת באמת סופרת
שורת המצב בתחתית החלון היא המהירה מכולן. בתיעוד כתוב שאפשר להציג בה ממוצע, ספירה, ספירה מספרית, מינימום, מקסימום וסכום, ושאפשר לבחור מה מוצג. היא מצוינת למבט, היא אינה מגיעה לדוח, והיא אינה נותנת פירוט לכל ערך.
הדרך השנייה היא נוסחה. הפונקציה שמחזירה את רשימת הערכים השונים מייצרת את הרשימה לבד ונשפכת לתאים שמתחת, והפונקציה שסופרת לפי תנאי משלימה לידה את מספר השורות לכל ערך.
הדרך השלישית היא טבלת ציר, וזו הדרך שרוב האנשים בוחרים.
הדרך הרביעית היא שלב הטעינה. רגע, מה זה בכלל שלב הטעינה. באקסל יש רכיב בשם Power Query שקורא את הקובץ לפני שהוא מגיע לגיליון, והכללים שנקבעים בו רצים מחדש בכל רענון. בקיבוץ שלו קיימות שתי אפשרויות נפרדות, ספירת שורות וספירת שורות מובחנות, והן מופיעות ברשימה אחת זו לצד זו. מי שבחר את הראשונה בזמן שהתכוון לשנייה קיבל מספר גדול יותר, ואין שום סימן שמשהו קרה.
המלכודת הראשונה, ברירת המחדל בטבלת ציר אינה ספירה
בתיעוד של טבלת הציר כתוב ששדה שמונח באזור הערכים מוצג כברירת מחדל כסכום, ורק אם אקסל מפרש את הנתונים כטקסט הוא מוצג כספירה.
תרגמו את זה לעמודה שהיא מזהה מספרי. גררתם את מספר הלקוח לאזור הערכים כדי לספור אותו, וקיבלתם סכום של מספרי לקוחות. הכותרת אמנם אומרת סכום, אבל היא נקראת אחרי שהמספר כבר נרשם, ובעמודה של מזהים בני ארבע ספרות התוצאה נראית כמו מספר סביר לגמרי.
המלכודת השנייה, ספירה מובחנת עולה בקיבוץ תאריכים
ספירה של כמה ערכים שונים יש, ולא כמה שורות, קיימת בטבלת ציר רק דרך מודל הנתונים. את התיבה שמוסיפה את הנתונים למודל מסמנים בזמן יצירת הטבלה, ולא אחריה.
ויש לזה מחיר. בפורום התמיכה של מיקרוסופט תואר בדיוק המצב הזה: כשהנתונים נוספים למודל נפתחת האפשרות לספירה מובחנת ואפשרויות הקיבוץ לפי טווח תאריכים מפסיקות לפעול, וכשהם אינם נוספים למודל הקיבוץ פעיל והספירה המובחנת אינה זמינה. המנחה בפורום אישר שההתנהגות הזו נכונה.
זו החלטה ולא תקלה, וכדאי לקבל אותה לפני שבונים את הטבלה, מכיוון שהיא נקבעת ברגע היצירה.
המלכודת השלישית, תא שנראה ריק ונספר
בתיעוד של הפונקציה שסופרת תאים לא ריקים כתוב שהיא סופרת תאים שמכילים כל סוג של מידע, כולל ערכי שגיאה ומחרוזת ריקה, ושהיא אינה סופרת תא ריק בלבד.
המשמעות בקובץ עבודה היא זו: עמודת עזר עם נוסחה שמחזירה מחרוזת ריקה כשאין מה להציג נראית ריקה על המסך ונספרת במלואה. אלף שורות בעמודה, שלוש מאות מהן מלאות באמת, והספירה מחזירה אלף. אף אחד לא יחשוד במספר עגול כל כך.
המלכודת הרביעית, סיננתם והנוסחה לא ידעה
סיננתם את הטבלה לחודש אחד, הסתכלתם על הספירה שבתחתית העמודה, וקיבלתם מספר שכולל גם את שאר החודשים. נוסחת ספירה רגילה אינה יודעת שסיננתם, והיא סופרת את כל הטווח שרשום בתוכה.
בתיעוד של הפונקציה שמחשבת סיכומי ביניים כתוב שהיא מתעלמת מכל שורה שאינה נכללת בתוצאה של סינון, ולא משנה איזה מספר פעולה נמסר לה. כתוב שם גם שמספרי הפעולה מאחד עד אחת עשרה כוללים שורות שהוסתרו ידנית, ואילו הטווח שמתחיל במאה ואחת מוציא גם אותן.
ולספירה יש שם שני מספרי פעולה שונים: אחד סופר תאים מספריים ואחד סופר תאים לא ריקים. מי שסופר עמודת סטטוסים ובחר את הראשון קיבל אפס, כי אין בעמודה הזו מספרים בכלל, והאפס הזה נראה כמו חודש בלי פעילות.
סדר האבחון, ארבע שאלות לפני שסופרים
הראשונה היא על מה המספר עונה, שורות או ישויות, ואת התשובה כותבים במילים לפני שנוגעים בגיליון.
השנייה היא האם סך השורות בעמודה מתאים לסך הרשומות שהמערכת מדווחת. פער כאן אומר שהבעיה קדמה לספירה.
השלישית היא האם יושבת בעמודה נוסחה שמחזירה מחרוזת ריקה, וזו הבדיקה שמסבירה ספירה גדולה ממה שרואים.
הרביעית היא מתי טבלת הציר רועננה לאחרונה. בתיעוד כתוב שכל טבלה שנבנתה על מקור שנוספו לו נתונים חייבת רענון, והרענון נעשה בלחיצה ימנית בתוך הטבלה.
התיקון, סיכום שמתעדכן לבד
בגרסאות Microsoft 365 קיימת פונקציה שמייצרת את הסיכום כולו בנוסחה אחת. בתיעוד שלה כתוב שהיא מאפשרת ליצור סיכום של הנתונים בנוסחה, שהיא מקבצת לפי ציר אחד ומאספת את הערכים המשויכים, ושהיא מוגדרת לגרסאות Microsoft 365 בלבד.
היתרון אינו הקיצור. טבלת ציר היא צילום מצב שדורש רענון, והנוסחה מתעדכנת בעצמה ברגע שנוספת שורה. בקובץ שנמשך מחדש בכל חודש זה ההבדל בין מספר נכון למספר שהיה נכון.
מי שעובד בגרסה שאין בה את הפונקציה הזו מקבל את אותה תוצאה משתי פונקציות: זו שמחזירה את הערכים השונים, וזו שסופרת לפי תנאי לצידה. שילוב שתיהן זמין גם בגרסאות 2021 ו-2024 לפי התיעוד.
ומתי טבלת ציר היא בכל זאת התשובה
כשצריך פירוט לפי שתי מידות ויותר, כשרוצים לצרף פרוסות ולתת למישהו אחר לסנן, וכשהקובץ נשלח החוצה ומי שמקבל אותו לא יכתוב נוסחה.
במקרים האלה מסמנים את מודל הנתונים מראש אם צריך ספירה מובחנת, ומרעננים לפני כל שליחה. שתי פעולות, ושתיהן נעשות פעם אחת.
הכלל
מספר בלי שאלה כתובה לידו הוא ניחוש מעוצב היטב. לפני שבוחרים שיטת ספירה כותבים במשפט אחד על מה היא עונה, ואת המשפט הזה משאירים בדוח ליד המספר.
וכלל שני, שקשה יותר לקבל. ספירת שורות אינה ספירת ישויות. ההפרש ביניהן הוא בדיוק הכפילויות שהמערכת יצרה בכוונה, והוא לא ייעלם מעצמו.
והשאלה שנשארה פתוחה
נניח שהשיטה נבחרה נכון, המספר נכון והדוח יצא. עדיין נשארת השאלה הקשה יותר, וזו שהמאמר הזה לא עונה עליה: מי בארגון מחליט מה נספר.
לקוח שהפסיק לרכוש לפני שנתיים, האם הוא לקוח. חשבונית שבוטלה, האם היא תנועה. ההגדרות האלה אינן טכניות, הן נקבעות פעם אחת ומשם הן זולגות לכל דוח במשך שנים. ברוב הארגונים שנכנסתי אליהם השנה הן לא היו כתובות בשום מקום, ולכן כל דוח חדש התחיל בוויכוח במקום בהגדרה.
את הדיון הזה אני מנהל בתוך מועדון The UNIQUE Way, ושם נמצאת גם ערכת הספירה המלאה: טבלת ההחלטה בין ארבע השיטות, שמונה נוסחאות מוכנות להעתקה, ארבע המלכודות כפי שהן כתובות בתיעוד, וסדר הבדיקה לפני שהמספר יוצא מהמחלקה. ההצטרפות חינם.
