לפני כמה חודשים ישבתי מול בקרת שבנתה דוח התאמות ספקים. היא פתחה קובץ עם ארבעת אלפים שורות והצביעה על עמודה אחת: מספר ההזמנה, שנחלץ משדה תיאור חופשי שהגיע בייצוא מהמערכת.
העמודה נראתה מסודרת לגמרי. מספרים, בלי אף שגיאה על המסך, בלי אף סימן אדום.
שאלתי כמה תאים ריקים יש בה. היא לא ידעה, ואיש במחלקה לא ידע.
הספירה ארכה חמש שניות והחזירה מאתיים שבעים ואחת שורות.
רגע, מה זה בכלל חילוץ טקסט באקסל
חילוץ טקסט באקסל הוא הפעולה שלוקחת חלק מתוך מחרוזת אחת ומחזירה אותו לתא נפרד. בדוח פיננסי זה כמעט תמיד אותו מקרה: המערכת מחזירה שדה תיאור אחד ארוך, ובתוכו מוחבא המזהה שאתם באמת צריכים.
השדה נראה בערך כך: תשלום לספק, לוכסן, מרכז עלות ומספרו, לוכסן, המילה הזמנה ואחריה המספר.
הדרך הוותיקה היא לאתר את המילה ולקחת את התווים שאחריה. הדרך החדשה עושה את זה בפונקציה אחת שמקבלת מפריד. שתיהן עובדות, ולשתיהן יש בדיוק אותו רגע כישלון.
מה קורה כשהמילה אינה שם
בשורה שכתוב בה הזמנת רכש ולא הזמנה, פונקציית האיתור אינה מוצאת דבר.
בעמוד התיקון של מיקרוסופט לשגיאה הזו כתוב במפורש שכאשר הפונקציה אינה מוצאת את הטקסט המבוקש בתוך המחרוזת, היא מחזירה שגיאת ערך. באותו עמוד כתוב גם שהבדל באותיות רישיות מייצר את אותה שגיאה בדיוק, וזה רלוונטי לכל מי שהשדה שלו מגיע חלקו באנגלית.
בפונקציות החילוץ החדשות ההתנהגות זהה במהותה ושונה בשם. בתיעוד שלהן כתוב שאקסל מחזיר שגיאת אין ערך כאשר המפריד אינו נמצא בטקסט, ושהוא מחזיר את אותה שגיאה גם כאשר מספר המופע גדול ממספר הפעמים שהמפריד מופיע.
זו אינה תקלה. זו הפונקציה מדווחת שהיא לא הצליחה לקרוא את השורה, וזה בדיוק מה שרציתם שהיא תעשה.
והשורה שאין בה מזהה בכלל
בדוח התאמות ספקים יש תמיד שורות שאין בהן מזהה, וזה תקין. עמלת העברה בנקאית, הפרשי שער, פקודת יומן ידנית בסוף החודש.
הפונקציה מחזירה עליהן בדיוק את אותה שגיאה, מאותה סיבה טכנית: המפריד אינו שם.
וכאן נמצאת כל הבעיה. שתי עובדות שונות לגמרי מגיעות לתא באותה צורה, ומי שמסתכל על המסך אינו יכול להבדיל ביניהן.
הרגע שבו הדוח מתחיל לשקר
מי שרואה עמודה עם שגיאות עוטף אותה. זו התגובה הטבעית, וכל מי שאני מכיר עשה את זה, כולל אני.
העטיפה מקבלת שני ארגומנטים: מה לחשב, ומה להחזיר אם החישוב נכשל. ובמקום השני כמעט תמיד נכתבות שתי מרכאות ריקות.
מרגע זה העמודה נקייה. אין שגיאות, אין צבע אדום, והקובץ נראה מוכן לשליחה.
ומרגע זה גם אי אפשר לדעת כמה שורות נפלו, למה נפלו, ואם בכלל נפלו. תא ריק הוא הערך היחיד בדוח שאין לו מקור: אי אפשר לדעת אם הוא אומר שאין מזהה, או שהנוסחה לא הצליחה לקרוא אותו.
הסיכום לפי הזמנה יתעלם מהשורות האלה. הוא לא יזרוק שגיאה, לא יסמן דבר, ופשוט יחזיר סכום קטן יותר. אם איש אינו משווה אותו לסכום הכולל של הקובץ, ההפרש אינו מתגלה לעולם.
ושימו לב מה כתוב ומה לא כתוב בתיעוד. בעמוד שמסביר איך לתקן את השגיאה הזו, ההמלצות הן לתקן את התחביר, להסיר את ארגומנט ההתחלה אם אינו נדרש, ולבדוק את אורך המחרוזת. עטיפה שמחזירה ריק אינה מופיעה שם כפתרון, ולו פעם אחת.
התיקון, וכולו שינוי אחד
התיקון אינו להסיר את העטיפה. הוא להחליף את מה שהיא מחזירה.
במקום שתי מרכאות ריקות, החזירו ערך שאומר מה קרה. שתי מילים בעברית מספיקות.
השינוי נראה קטן, והוא משנה שלושה דברים בבת אחת.
אפשר לספור. ספירה אחת של הערך הזה נותנת מספר, והמספר הזה הוא הדוח על עצמו.
אפשר להסביר. שורה שנפלה שייכת תמיד לאחת משתי משפחות: מבנה שדה שלא צפיתם, או שורה שבאמת אין בה מזהה. את הראשונה מתקנים, את השנייה מדווחים.
אפשר להעביר הלאה. מי שיקבל את הקובץ אחריכם יראה מיד שהיו שורות שלא נחלצו, בלי לשאול אף אחד.
ובפונקציות החילוץ החדשות זה אפילו לא עטיפה. הערך שיוחזר כשלא נמצאה התאמה הוא הארגומנט האחרון של הפונקציה עצמה, ובתיעוד כתוב שברירת המחדל שלו היא שגיאת אין ערך. כלומר מי שאינו כותב אותו מקבל כישלון רועש, וזו ברירת מחדל טובה.
מה עושים בגרסה שאין בה את הפונקציות האלה
וכאן צריך לומר משהו פחות נוח. בעמוד התיעוד של פונקציות החילוץ רשומות ארבע גרסאות בלבד: מיקרוסופט 365, מיקרוסופט 365 למק, אקסל 2024 ואקסל 2024 למק.
מי שעובד על אקסל 2019 או על אקסל 2021 לא ימצא אותן, וזה אינו רישיון חסר. זו גרסה שקדמה להן.
יש שלוש דרכים, ולכל אחת מחיר.
הראשונה היא העטיפה שתוארה למעלה, עם ערך שאפשר לספור במקום ריק. היא עובדת בכל גרסה שיש בה פונקציית איתור, כלומר בכל גרסה.
השנייה היא מילוי מהיר. לפי התיעוד הוא קיים מאקסל 2016, ואפשר להפעיל אותו ידנית מכרטיסיית הנתונים או בקיצור המקלדת. הוא מהיר מאוד, והוא מייצר ערכים קבועים ולא כלל. בחודש הבא הוא לא ירוץ מעצמו, ולכן הוא מתאים לקובץ חד פעמי ולא לדוח חוזר.
השלישית היא Power Query.
רגע, מה זה בכלל Power Query
זהו מנוע שליפת הנתונים שיושב בתוך Excel בכרטיסיית הנתונים, וגם בתוך Power BI. מגדירים פעם אחת מאיפה הנתונים מגיעים ומה עושים איתם, והוא חוזר על אותן פעולות בכל רענון.
יש בו פעולת חילוץ לפי מפריד, ובתיעוד היא מופיעה בשלוש צורות: טקסט שלפני מפריד, טקסט שאחרי מפריד וטקסט בין שני מפרידים. זו הדרך היחידה מבין השלוש שמייצרת כלל שרץ על כל השורות בכל רענון.
ויש שם פרט אחד שכדאי לדעת לפני כן. אם אתם בונים את העמודה מתוך דוגמאות, כלומר מקלידים תוצאה אחת ונותנים למנוע להסיק את הכלל, כתוב בתיעוד במפורש שהחוויה הזו עובדת רק על מאה השורות הראשונות של התצוגה המקדימה.
כלל שנלמד ממאה שורות ורץ על ארבעת אלפים הוא בדיוק אותה בעיה מההתחלה, רק במקום אחר בקובץ. בחרו בפעולת החילוץ המפורשת ולא בדוגמאות, ותקבלו כלל שהגדרתם בעצמכם.
הכלל
שגיאה שהושתקה אינה נעלמת, היא הופכת לשורה שהפסקתם לספור.
וכלל שני, שנוגע לכל עמודה מחושבת ולא רק לחילוץ טקסט. עמודה שנראית נקייה אינה עדות לכך שהחישוב הצליח, אלא לכך שמישהו בחר איך ייראה הכישלון. הבחירה הזו לגיטימית לגמרי, בתנאי שהיא נספרת ומופיעה בדוח, ולא רק נראית טוב על המסך.
והשאלה שנשארה פתוחה
נניח שהעמודה תוקנה, שהריקים הוחלפו בערך שאפשר לספור, ושהמספר יושב בראש הגיליון. עדיין נשארת השאלה שהמאמר הזה אינו עונה עליה: כמה עמודות מחושבות אחרות באותו קובץ עוטפות שגיאה ומחזירות ריק, ואיש אינו סופר כמה פעמים הן נופלות.
בכל מחלקה שנכנסתי אליה השנה הייתה לפחות עמודה אחת כזו. כמעט תמיד היא לא עמודת החילוץ, אלא דווקא זו שנראית הכי שקטה.
את הדיון הזה אני מנהל בתוך מועדון The UNIQUE Way, ושם נמצאת גם ערכת חילוץ הטקסט המלאה: בדיקת הדקה שסופרת כמה שורות נפלו, ארבעת מבני השדה שחוזרים בייצוא מהמערכת והנוסחה לכל אחד, טבלת ההחלטה בין נוסחה, מילוי מהיר ושאילתה, ורשימת הבדיקה לפני שהדוח יוצא מהמחלקה. ההצטרפות חינם.
