מעקב אחר הערכת נוסחאות Excel צעד אחר צעד ב-Delphi

Excel מסתיר מנפה שגיאות (debugger) קטן לעין כול. בחר תא, פתח את נוסחאות ולחץ על "הערך נוסחה", ותיבת דו-שיח מציגה את הנוסחה עם תת-ביטוי אחד מסומן בקו תחתון. לחץ על "הערך" ותת-ביטוי זה קורס לתוך הערך שלו, ואז הבא אחריו מסומן בקו תחתון, ואתה צופה בביטוי ארוך מתכווץ למספר יחיד, צמצום (reduction) אחד בכל פעם. זוהי הדרך המהירה ביותר למצוא איזה ענף של IF מקונן הופעל בפועל, או איזו הפניה הזינה סכום שגוי. HotXLS משחזר בדיוק את ההתנהגות הזו באמצעות TXLSFormulaTracer, כך שתוכנית Delphi או C++Builder יכולה לרנדר את אותה רשימת צעדים לצורך ביקורת על חוברת עבודה, ניפוי שגיאות של נוסחה שנוצרה, או כדי ללמד מישהו מדוע תוצאה התקבלה כפי שהתקבלה. כל צעד מוקלט נושא את הטקסט של תת-הביטוי ואת הערך שאליו הוא מצטמצם

כיצד מנוע הצמצום סורק את הביטוי

העוקב (tracer) אינו חודר לתוך מנוע החישוב. הוא מפרק את הנוסחה לאסימונים (tokenizes) ומנתח אותה בעזרת מנתח רקורסיבי (recursive-descent parser), ואז מצמצם את העץ לעומק תחילה (depth-first), תת-הביטוי הפנימי ביותר שניתן להערכה ראשון. כאשר צומת מצטמצם לערך, ערך זה מוחלף חזרה אל תוך הביטוי העוטף כערך ליטרלי (literal), והמנוע מבקש מהמחשבון האמיתי לחשב מחדש את הביטוי שכעת הוא פשוט יותר. מכיוון שכל צעד מוערך דרך המתודה הפומבית Calculate של גיליון העבודה במקום קיצור דרך פרטי, כל צעד תואם בדיוק את מה שחישוב מחדש מלא של התא היה מייצר. המנתח אינו פולשני כדרך תכנון, וזה מה שמאפשר לו לרוץ על כל גיליון עבודה מבלי להפריע למצב שלו

המנתח פועל לפי סולם קדימויות של אופרטורים, עם רמה רקורסיבית אחת לכל רצועת קדימות. מהקשירה הנמוכה ביותר לגבוהה ביותר הרצועות הן: רמה 0 השוואה (=, <>, <, >, <=, >=), רמה 1 שרשור מחרוזות (&), רמה 2 חיבור וחיסור, רמה 3 כפל וחילוק, רמה 4 העלאה בחזקה, ולבסוף פלוס ומינוס אונריים מתחת לזה. כל רמה מנתחת את הרמה שמעליה כדי להשיג את האופרנדים שלה, כך שרצועה גבוהה יותר קושרת חזק יותר. זו אותה קדימות ש-Excel מיישם, וזו הסיבה ש-A1*B1+A2*B1 מצמצם את שני המכפלות לפני הסכום: כפל יושב ברמה 3, חיבור ברמה 2, כך שהכפלים עמוקים יותר בעץ ומצטמצמים ראשונים

מעקב אחר נוסחה וסריקת הצעדים

השימוש משקף את דוגמת ההדגמה שסופקה ב-Demo/Delphi/FormulaTrace/FormulaTrace.dpr. בנה גיליון עבודה (או פתח חוברת עבודה קיימת), בנה עוקב (tracer) על הגיליון, קרא ל-Trace, ורוץ על המערך המוחזר. כל TXLSFormulaStep חושף את Depth להזחה, Source עבור תת-הביטוי המקורי, Expression עבור אותו תת-ביטוי כשהאופרנדים שלו כבר הוחלפו, ו-Value לתוצאה של הצעד

uses
  SysUtils, Variants, lxHandle, lxHandleX, lxFormulaTrace;

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Tracer: TXLSFormulaTracer;
  Steps: TXLSFormulaStepArray;
  Final: Variant;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Order');
    Sheet.Cells[1, 1].Value := 10;    // A1 units
    Sheet.Cells[1, 2].Value := 25;    // B1 unit price
    Sheet.Cells[1, 3].Value := 0.08;  // C1 tax rate

    Tracer := TXLSFormulaTracer.Create(Sheet);
    try
      Final := Tracer.Trace('A1*B1*(1+C1)', Steps);
      for I := 0 to High(Steps) do
        Writeln(StringOfChar(' ', Steps[I].Depth * 2),
                Steps[I].Source, ' -> ', Steps[I].Expression,
                ' = ', VarToStr(Steps[I].Value));
      Writeln('result = ', VarToStr(Final));
    finally
      Tracer.Free;
    end;
  finally
    Book.Free;
  end;
end;

הפניות התאים נפתרות ראשונות ומופיעות כצעדים משלהן, לאחר מכן המכפלות מצטמצמות, אז מקדם המס שבתוך הסוגריים, והכפל הסופי סוגר את העניין. השדה Depth מאפשר לך להזיח כך שהצמצומים הפנימיים ביותר יישבו באופן נראה העמוק ביותר, בדיוק כפי ש-Excel מסמן בקו תחתון את המונח הפנימי ביותר לפני כל מונח חיצוני

המלכודת של ליטרלים חסרי-אזור (locale-free)

הפרט המסוכן ביותר בכל התוכנית הזו אינו נראה על מכונה אנגלית ונשבר ברעש על מכונה גרמנית. כאשר מספר מחושב מוחלף חזרה אל טקסט הנוסחה, הוא חייב להיכתב כמחרוזת ואז להיות מנותח מחדש על ידי מנוע החישוב, שמתייחס ל-. כנקודה עשרונית. אם ההחלפה הייתה משתמשת בהגדרות האזור של המערכת (system locale), ה-TFormatSettings הגרמני היה כותב 1,08 כמקדם המס, הפסיק היה נקרא כמפריד ארגומנטים, והחישוב מחדש של A1*B1*1,08 היה מנותח לצורה שגויה או נכשל לחלוטין

העוקב נמנע מזה על ידי עיצוב של כל ליטרל מספרי דרך TFormatSettings פרטי שהוא מקבע בזמן הבנייה (construction), עם DecimalSeparator שכופה להיות . ו-ThousandSeparator המוגדר ל-#0 כדי שאף פעם לא ייפלט תו קיבוץ. לאחר מכן, FloatToStr מייצר ליטרל שהמנוע תמיד יכול לקרוא בחזרה, ללא קשר להגדרות האזוריות של המפעיל

// Conceptually what the tracer pins once, at construction
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// every reduced number is written with: FloatToStr(Double(V), FFloatFmt)

זהו סוג הבאגים שלעולם אינו מופיע בבדיקות של המחבר עצמו וצץ רק כאשר לקוח באזור (locale) אחר מריץ את אותו הקוד, כך ששווה לומר זאת בבירור: העברת ערך הלוך-ושוב דרך טקסט של נוסחה היא בעיית סריאליזציה (serialization), וסריאליזציה חייבת להיות חופשית מאזור (locale-free)

ערכים בוליאניים מצטמצמים ל-1 ול-0

החלטת החלפה קשורה נוגעת לערכים לוגיים. כאשר תת-ביטוי מוערך לערך בוליאני, העוקב כותב אותו בחזרה כ-1 או 0, לא כ-TRUE או FALSE. הסיבה היא שהליטרל המצומצם חייב להיות מנותח מחדש בצורה חלקה בכל הקשר שעוטף אותו, וחשבון הוא המקרה התובעני כאן. אם השוואה כמו A1>A2 הייתה מצטמצמת לטקסט TRUE והטקסט הזה היה נוחת בתוך TRUE*B1, החישוב מחדש היה תלוי בכך שהמנוע יקבל מילת מפתח בוליאנית חשופה בתוך פעולת כפל. החלפה ל-1 עוקפת את השאלה לחלוטין, משום ש-1*B1 הוא חד-משמעי בכל מיקום אריתמטי. זה גם תואם את הכפייה (coercion) ש-Excel עצמו עושה, כאשר TRUE מתנהג כ-1 ו-FALSE כ-0 ברגע שמצופה מספר

קריאות לפונקציות מצטמצמות באופן אטומי

מנוע צעדים נאיבי היה מצמצם את הארגומנטים של הפונקציה תחילה ואז את הקריאה עצמה. זה שגוי עבור Excel, והעוקב אינו עושה זאת במכוון. קריאה לפונקציה מוערכת בשלמותה, מהטקסט המקורי שלה, בצעד אחד. הסיבה לכך היא סמנטיקה של קצר חשמלי (short-circuit). IF, CHOOSE ו-IFERROR מעריכים רק את הענף שהם בוחרים, וצמצום הארגומנטים קודם לכן יאלץ את המנוע לחשב ענפים ש-Excel אף פעם לא נוגע בהם. הנפגע הקלאסי הוא שומר נגד חלוקה באפס (divide-by-zero guard) כמו IF(B1=0,0,A1/B1): אם העוקב היה מצמצם את A1/B1 לפני הערכת ה-IF, השומר היה מפספס ויוצר בדיוק את השגיאה שהוא קיים כדי למנוע. על ידי הערכת הקריאה כולה באופן אטומי, העוקב משמר את ההערכה העצלה (lazy evaluation) שגורמת לשומרים כאלה לעבוד

// IF is one atomic step; only the selected branch is evaluated
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 is true, so the step records A1*B1 as the chosen result;
// A2*B1 is never computed, exactly as Excel would do it.

הפשרה (trade-off) היא שאינך רואה בתוך הקריאה לפונקציה כצעדים נפרדים, אך זוהי ההתנהגות הנכונה. הצגת צמצומי ארגומנטים ש-Excel אף פעם אינו מבצע תהיה מעקב מטעה הרבה יותר מאשר טיפול בקריאה כיחידת הערכה אחת כפי שהיא באמת

מפרידי ארגומנטים וטווחים שלמים

שני נרמולים נוספים שומרים על החישוב מחדש ישר. המהדר של מנוע החישוב מצפה ל-; כמפריד ארגומנטים של פונקציה, כך שכאשר העוקב בונה מחדש קריאה לפונקציה מתוך העץ המנותח שלו, הוא מחבר ארגומנטים עם ;, אפילו אם המשתמש במקור הקליד ,. נוסחה שנכתבה כ-SUM(A1,A2,A3) מחושבת מחדש כ-SUM(A1;A2;A3), שהמנוע מקבל. החלפת הערכים היא שיוצרת את הצורך בבנייה מחדש הזו, וקבלת המפריד הנכון היא זו שגורמת לבנייה מחדש להתפרש כהלכה

הפניות לטווחים הן המקרה האחר. טווח כגון A1:A3 אינו סקלר (scalar) ואסור לפיצול לשלושה ערכים נפרדים, מכיוון שהפונקציה שצורכת אותו מצפה לארגומנט של טווח. העוקב שומר על טווח שלם כפי שהוא בטקסט המקורי שלו ונותן לפונקציה העוטפת להצטמצם בשלמותה. בתוך SUM(A1:A3)*B1 הטווח נשאר שלם, SUM(A1:A3) מצטמצם למספר אחד בצעד אטומי אחד, ורק אז פועל הכפל החיצוני. זהו אותו גבול ש-Excel משרטט בין אופרנד טווח לסקלר שהוא תורם בסופו של דבר

// The range A1:A3 is never split; SUM is one atomic reduction,
// then the product with B1 reduces on top of it.
Final := Tracer.Trace('SUM(A1:A3)*B1', Steps);
for I := 0 to High(Steps) do
  Writeln(Steps[I].Source, ' = ', VarToStr(Steps[I].Value));

בהתחברם יחד, כללים אלה הופכים את רשימת הצעדים למראה נאמנה של פקודת ה"הערך נוסחה" של Excel במקום לקירוב שלה. הצמצומים מתרחשים בסדר שבו Excel מבצע אותם, הליטרלים המוחלפים שורדים כל אזור (locale), ערכים בוליאניים נכפים בדרך שבה Excel כופה אותם, ופונקציות עצלות נשארות עצלות. אם ברצונך לדחוף את המנוע רחוק יותר עם פונקציות משלך, מאמר מנוע הנוסחאות ופונקציות מותאמות אישית מציג כיצד לרשום אותן, ולעבודה מספרית כבדה יותר, מאמר פונקציות ההתפלגות הסטטיסטיות ב-Delphi סוקר את הספרייה המובנית שמולה העוקב מבצע הערכה. כל אלה מסופקים כחלק מ-HotXLS spreadsheet component עבור Delphi ו-C++Builder, לצד ממשקי ה-API של קריאה, כתיבה, עיצוב וחישוב הנסקרים במקומות אחרים בבלוג זה