חשבונית 1042 מופיעה בקובץ התשלומים שלוש פעמים. חשבונית 1043 מופיעה פעמיים. חשבונית 1044 מופיעה פעם אחת.
על פניו זו בדיוק העבודה של הכלל שמסמן ערכים כפולים. אבל הוא צובע את שתי הראשונות באותו צבע בדיוק, ואינו יודע להגיד איזו מהן חריגה ואיזו תקינה לחלוטין.
התסמין
הצבע אומר שהערך חוזר. הוא אינו אומר כמה פעמים, ואינו אומר אם זו התנהגות תקינה בקובץ הזה.
בקובץ תשלומים אמיתי חלק גדול מהחזרות לגיטימי לחלוטין. חשבונית שמשולמת בשני תשלומים מופיעה פעמיים. הוראת קבע מופיעה בכל חודש מחדש. ספק שמנפיק חשבונית שבועית מופיע ארבע פעמים בחודש. ברגע שכל אלה נצבעים באותו צבע בדיוק, החריג היחיד שבאמת מעניין אתכם יושב בתוך ים אדום, ואף אחד לא ימצא אותו בגלילה.
וזו הנקודה שבה רוב האנשים מסיקים שהעיצוב המותנה אינו מספיק חכם. הוא כן. הכלל שבחרתם הוא זה שיודע לענות על שאלה אחת בלבד.
רגע, מה זה בכלל כלל לפי נוסחה
בחלון העיצוב המותנה יש כללים מוכנים, למשל ערכים כפולים או גדול מערך מסוים. יש שם גם אפשרות שנייה: לכתוב נוסחה משלכם, ואקסל צובע כל תא שהנוסחה מחזירה עבורו אמת.
בתיעוד של מיקרוסופט כתוב שאפשר להשתמש בכל נוסחה שמחזירה ערך לוגי של אמת או שקר. זה כל התנאי. מכאן והלאה, כל שאלה שאתם יודעים לנסח כנוסחה הופכת לכלל צביעה.
למה הכלל המובנה אינו יכול לענות
שתי סיבות, ושתיהן מתועדות.
הראשונה היא שהכלל בינארי. הוא מציע ערכים כפולים או ערכים ייחודיים, ואין בו שדה להזין מספר. אין שם מקום לכתוב יותר מפעמיים, כי הוא לא נבנה לשאלה הזו.
השנייה מפתיעה יותר. בעמוד התיעוד על סינון ערכים ייחודיים והסרת כפילויות כתוב שהשוואת ערכים כפולים תלויה במה שמופיע בתא ולא בערך שמאוחסן בו, והדוגמה שם היא אותו תאריך בדיוק בשני עיצובים שונים, שנחשב לשני ערכים ייחודיים.
תרגמו את זה לקובץ שהורכב משתי מערכות. אותו תשלום, פורמט תאריך אחר בכל מערכת, והכפילות אינה מסומנת בכלל. הקובץ נראה נקי, ואין שום הודעה שאומרת אחרת.
ויש שם עוד הבדל ששווה להכיר. באותו עמוד כתוב שכאשר מסירים כפילויות, המופע הראשון ברשימה נשמר והערכים הזהים האחרים נמחקים. כלל הצביעה מתנהג הפוך: הוא צובע את כולם, כולל את הראשון. אותה מילה בדיוק, שתי התנהגויות שונות, ואף אחד לא מזהיר אתכם על ההבדל.
הכלל שכן עונה
סמנו את הטווח שרוצים לבדוק, כשהתא הפעיל הוא התא הראשון בטווח. מכאן: עיצוב מותנה, כלל חדש, ואז שימוש בנוסחה לקביעת התאים לעיצוב.
הנוסחה שנכנסת שם היא =COUNTIF($A$2:$A$1000,$A2)>2 והיא נבדקת מחדש עבור כל תא בטווח.
שימו לב לסימני הדולר, כי שם נשבר הכלל הזה אצל רוב מי שמנסה. הטווח שנספר קבוע, ולכן הוא מקובע גם בעמודה וגם בשורה. התא שנבדק משתנה משורה לשורה, ולכן הוא מקובע בעמודה בלבד. בתיעוד של העיצוב המותנה מיקרוסופט מזהירה במפורש שצריך להתאים הפניות יחסיות ומוחלטות בנוסחה של הכלל.
ואם אתם רוצים שהמופע הראשון יישאר נקי והצבע יופיע רק מהשני והלאה, הנוסחה היא =COUNTIF($A$2:$A2,$A2)>1 כאשר קצה הטווח גדל יחד עם השורה, כך שכל שורה סופרת רק את מה שמעליה.
ואם הזהות בקובץ שלכם מוגדרת לפי שני שדות ולא לפי אחד, למשל ספק ותאריך יחד, אותה נוסחה נכתבת עם הפונקציה שסופרת לפי כמה תנאים, והתנהגות הכלל זהה לחלוטין.
שימו לב גם לגבול העליון של הטווח. אם כתבתם אלף שורות והקובץ גדל מאז, כל מה שמעבר לשורה אלף אינו נספר, והכלל ימשיך להיראות תקין לגמרי. עדיף לתחום את הטווח לפי הטבלה, או להשאיר מרווח נדיב מראש.
שלוש מלכודות שהופכות את הספירה לשקר
הספירה נראית אובייקטיבית, ולכן אף אחד לא בודק אותה. בתיעוד של הפונקציה שסופרת יש שלוש מגבלות שכתובות שחור על לבן.
רישיות. בעמוד התיעוד כתוב שהקריטריונים אינם רגישים לאותיות גדולות וקטנות, ושמחרוזת באותיות קטנות ומחרוזת באותיות גדולות מתאימות לאותם תאים בדיוק. קוד ספק שהוקלד בשתי צורות נספר כערך אחד, גם כשמדובר בשני ספקים נפרדים במערכת.
אורך המחרוזת. באותו עמוד כתוב שהפונקציה מחזירה תוצאות שגויות כשמשווים מחרוזות שאורכן עולה על 255 תווים. תיאור פריט שהודבק משדה חופשי עובר את הרף הזה בלי שאף אחד ישים לב, והתוצאה אינה שגיאה אלא מספר שנראה סביר.
אורך הקוד המספרי. מיקרוסופט מתעדת שאקסל בנוי סביב תקן ייצוג המספרים IEEE 754, ושומר חמש עשרה ספרות משמעותיות בלבד. קוד בן שש עשרה ספרות ומעלה אינו נשמר במלואו, ולכן שני קודים שנבדלים רק בספרות האחרונות נראים זהים לכל השוואה שעוברת במסלול המספרי. בדקו את זה בקובץ שלכם לפני שאתם סומכים על הספירה: הקלידו שני קודים ארוכים שנבדלים בספרה האחרונה, וספרו אותם.
למלכודת הראשונה יש תשובה מדויקת. הפונקציה EXACT מתועדת אצל מיקרוסופט כמשווה שתי מחרוזות טקסט ומחזירה אמת רק אם הן זהות לחלוטין, והיא רגישה לאותיות גדולות וקטנות. ספירה שבנויה עליה מבדילה בין שני הקודים.
ובנוסף לשלוש, מופיעה שם אזהרה על רווח בתחילת הערך או בסופו, על שימוש לא עקבי במרכאות, ועל תווים שאינם ניתנים להדפסה. שלושתם נפוצים בקבצים שיצאו מייצוא של מערכת, ושלושתם משנים את הספירה בשקט.
סדר הכללים, וזה החלק שנשכח
נניח שכתבתם את הנוסחה נכון, לחצתם אישור, והעמודה נראית בדיוק כמו קודם. לפני שאתם מוחקים את הנוסחה ומחליטים שהיא לא עובדת, פתחו את ניהול הכללים.
בתיעוד של העיצוב המותנה כתוב שככל שכלל נמצא גבוה יותר ברשימה כך הוא חשוב יותר, ושכאשר שני כללים מתנגשים מוחל הכלל הגבוה ברשימה והנמוך ממנו אינו מוחל. הכלל הישן של ערכים כפולים עדיין יושב שם, מעל החדש, ועושה בדיוק את מה שעשה קודם.
באותו חלון יש גם תיבת סימון בשם עצור אם אמת. בתיעוד כתוב שהיא קיימת לתאימות לאחור, כדי לדמות את ההתנהגות של גרסאות ישנות שאינן תומכות ביותר משלושה כללים על אותו טווח, ושכדי להעריך רק את הכלל הראשון מסמנים אותה בכלל הראשון.
המסקנה פשוטה. מחקו את הכלל הישן, אל תניחו אותו מתחת לחדש.
והחלק ששום צבע לא פותר
צבע אינו מספר. מי שמקבל את הקובץ רואה תאים אדומים ואינו יודע מה נבדק, כמה מופעים נחשבו חריגה, ומה היה הטווח שנספר.
לכן בכל קובץ שיוצא מהמחלקה כדאי להוסיף עמודה גלויה עם הספירה עצמה, לצד הצבע. הצבע מושך את העין, והמספר הוא זה שאפשר להתווכח איתו. גם מי שיפתח את הקובץ בעוד חצי שנה יראה מה נבדק, בלי לפתוח את חלון הכללים.
הכלל
סימון כפילויות באקסל אינו בדיקה של הקובץ. הוא תשובה לשאלה אחת, ורוב האנשים לא שמו לב איזו שאלה בדיוק הם שאלו.
הכלל המובנה שואל האם הערך חוזר. השאלה שאיש כספים באמת צריך היא כמה פעמים הוא חוזר, ומאיזה מספר זו כבר חריגה. ההבדל בין שתי השאלות האלה הוא נוסחה אחת בשורה אחת.
והשאלה שנשארה פתוחה
נניח שהכלל עובד, הספירה גלויה לצד הצבע, והחריגים באמת בולטים. עדיין נשארת השאלה הקשה יותר, וזו שהמאמר הזה לא עונה עליה: מה נחשב כפילות בקובץ הזה מלכתחילה.
מספר חשבונית זהה אינו בהכרח תשלום כפול, ותשלום כפול אינו תמיד נושא מספר זהה. ההגדרה הזו אינה טכנית, היא החלטה של המחלקה, ומי שלא כתב אותה בשום מקום מגלה אותה מחדש בכל סגירת חודש, בדרך כלל בזמן הלא נכון.
את הדיון הזה אני מנהל בתוך מועדון The UNIQUE Way, ושם נמצאת גם רשימת הבדיקה המלאה: חמישה כללים מדויקים להעתקה, שלוש המלכודות עם התשובה לכל אחת, וסדר הבדיקה לפני שקובץ יוצא מהמחלקה. ההצטרפות חינם.
