ביקורת cache של נוסחאות Excel עם Deep Recalc ב-HotXLS

HotXLS עונה על השאלה שכל pipeline של גיליונות נאלץ לשאול בשלב מסוים: האם המספרים השמורים ב-workbook עדיין תואמים את הנוסחאות שיצרו אותם. CalculateAndVerify מחשב מחדש את כל גרף התלויות לתוך overlay מבודד, משווה כל תוצאה מול הערך שכבר שמור בתא, ומדווח על אי ההסכמות. כברירת מחדל הוא לא משנה דבר

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

למה ערך ב-cache שחולק על הנוסחה שלו כל כך מסוכן?

כי הוא בלתי נראה בכל נתיב קריאה רגיל. פותחים את הקובץ ב-viewer, קוראים את התא דרך API, מייצאים אותו ל-CSV או ל-PDF — ומקבלים את המספר מה-cache. הנוסחה נמצאת שם, באותו תא עצמו, ואף אחד לא משווה ביניהן. אי ההתאמה עולה על פני השטח רק כשמישהו פותח את ה-workbook ב-Excel, שמחשב מחדש בטעינה תחת רוב ההגדרות, ופתאום דוח שאושר ברבעון שעבר מציג סכומים אחרים

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

var
  Book: TXLSWorkbook;
  Options: TXLSRecalcAuditOptions;
  Report: TXLSCalculationAuditReport;
  I: Integer;
begin
  Book := TXLSWorkbook.Create(nil);
  try
    Book.LoadFromFile('quarterly-close.xls');
    Options := TXLSRecalcAuditOptions.Default;
    Options.MaxIssues := 500;
    Report := Book.CalculateAndVerify(Options);
    try
      for I := 0 to Report.Count - 1 do
        if Report[I].Kind = xlcaiCacheMismatch then
          Writeln(Report[I].SheetName, '!',
                  Report[I].Row, ':', Report[I].Col, '  ',
                  Report[I].Formula,
                  '  cached=', VarToStr(Report[I].Actual),
                  '  recomputed=', VarToStr(Report[I].Expected));
      if Report.Truncated then
        Writeln('issue budget reached, raise MaxIssues');
    finally
      Report.Free;
    end;
  finally
    Book.Free;
  end;
end;

יש שלושה overloads והם עונים על שלוש שאלות שונות. ה-CalculateAndVerify ללא פרמטרים מחזיר מספר אי-התאמות, וזה כל מה שבדיקת תקינות צריכה. ה-overload עם מערך out של אי-התאמות נותן לך את התאים. ה-overload שמקבל TXLSRecalcAuditOptions מחזיר TXLSCalculationAuditReport מלא, וזה המקום לפנות אליו כשצריך לדעת לא רק שערך חולק אלא גם למה הביקורת לא הצליחה להעריך משהו

ה-overlay, ולמה הביקורת לא כותבת

כל ערך שחושב מחדש נוחת ב-overlay ולא ב-cache של התא, וה-overlay מוזרק בראש ה-callback של קריאת התאים בשני מנועי ה-workbook. המיקום הזה הוא מה שהופך את הביקורת לעקבית עם עצמה: כש-B1 מחושב מחדש ו-C1 תלויה ב-B1, C1 רואה את הערך מהסבב הזה של הביקורת ולא את הערך הישן מה-cache. בלי זה, שגיאה אחת במעלה הזרם הייתה מדווחת פעם אחת ואז נבלעת, וכל תא במורד הזרם היה נראה כמסכים עם קלט שגוי

תאים שהערך המחושב מחדש שלהם תואם את ה-cache בכלל לא נכנסים ל-overlay. זו לא מיקרו-אופטימיזציה, זה מה ששומר את הביקורת בטווח המשתלם. workbook נקי עם מאה אלף נוסחאות מבצע אפס כתיבות ל-overlay והסבב נשאר בתקציב של 1.35x מול חישוב מחדש מלא, וזה ההבדל בין משהו שאפשר להריץ על כל קליטה לבין משהו שמריצים פעם ברבעון

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

כישלונות מסווגים, לא מעורבבים יחד

תא שהביקורת לא יכולה להעריך אינו ממצא זהה לתא שהערך שלו חולק, ו-TXLSCalculationAuditIssueKind שומר על הקטגוריות מופרדות. xlcaiCacheMismatch הוא חוסר ההסכמה על הערך. xlcaiMissingFunction ו-xlcaiMissingName אומרים שה-evaluator פגש משהו שהוא לא מממש או לא מצליח לפתור. xlcaiUnsupportedArguments מכסה צורות ארגומנטים מחוץ לתת-הקבוצה הנתמכת. xlcaiExternalReferenceDenied ו-xlcaiExternalReferenceMissing מפרידים סירוב מדיניות מ-workbook נעדר. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled ו-xlcaiInternalFailure משלימים את הקבוצה

הבחנה אחת שווה ניסוח כי היא הופכת הנחה נפוצה. קוד שגיאה חיובי של Excel הוא תוצאה, לא כישלון. תא שמוערך לגיטימית ל-#DIV/0! חושב נכון, ולכן הביקורת שומרת את השגיאה הזאת ב-overlay ומשווה אותה מול ה-cache כמו כל ערך אחר. workbook מלא בתאי שגיאה מכוונים מניב אפס ממצאים, ו-workbook שבו שגיאה הופיעה או נעלמה מאז שהערכים נשמרו ב-cache מניב בדיוק את הממצאים שרצית

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

קריאת שרשרת כישלון

כשנוסחה נכשלת בהערכה, לדעת איזה תא נכשל כמעט אף פעם לא מספיק, כי הכישלון בדרך כלל שלוש רמות בעומק שרשרת של הפניות. לכן כל ממצא נושא מחרוזת Stack שמוצגת כשהמסגרת החיצונית ביותר ראשונה, בצורה Sheet1!A1 > Sheet1!B2 > Data!C7, כך שהדוח מצביע על התא שבאמת נשבר ולא על התא שבמקרה הסתכלת עליו

המקליט חסום. MaxStackFrames ברירת המחדל שלו 64 עם רצפה של 8, והשרשרת הכושלת העמוקה ביותר היא זו שנשמרת: מסגרת פנימית רושמת את השרשרת כשהכישלון מקורו שם, ומסגרות חיצוניות שמתפרקות אחר כך לא דורסות אותה. אם שרשרת כלשהי חרגה מהתקציב, Report.StackTruncated מוגדר, מה שמבדיל בין שרשרת קצרה לבין שרשרת שלא ראית את כולה

// קריאה-בלבד כברירת מחדל. ApplyResults מבצע commit ל-overlay רק אחרי
// ביקורת שהצליחה במלואה, תחת write guard שדוחה את ה-commit
// אם המבנה של ה-workbook השתנה בזמן שהביקורת רצה
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // השוואה מדויקת, חושפת סטייה
Options.RelativeTolerance := 0;
Options.OnProgress := HandleProgress;

Report := Book.CalculateAndVerify(Options);
try
  if Report.Applied then
    Book.SaveToFile('quarterly-close-repaired.xls')
  else
    Writeln('not applied: ', Report.Count, ' issues blocked the commit');
finally
  Report.Free;
end;

procedure THarness.HandleProgress(ASender: TObject;
  ACurrent, ATotal: Integer; var ACancel: Boolean);
begin
  ACancel := FUserRequestedStop;   // הביקורת עוצרת בגבול הצומת הבא
end;

מתי כדאי לתת לביקורת לתקן את ה-workbook?

רק כשהביקורת חזרה נקייה לחלוטין מממצאים מסוג כישלון, וזה בדיוק התנאי ש-ApplyResults אוכף בשבילך. ה-commit מתרחש אחרי סבב שהצליח במלואו, לא בוטל, ועובר guard מבני: המנוע הבינארי עוקב אחרי מזהה שינוי של ה-workbook, ומנוע ה-OOXML מצלם snapshot של דור מבנה לכל גיליון. אם משהו זז בזמן שהביקורת רצה, התוצאות מתארות workbook שכבר לא קיים וה-commit נדחה

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

סובלנות היא החלטת מדיניות, לא ברירת מחדל

ההשוואה כברירת המחדל היא סובלנות מוחלטת של 1E-6 עם סובלנות יחסית מנוטרלת, מה שמשמר את ההתנהגות הקלאסית ומקבל בשקט סטייה של 4E-7. בדרך כלל זה נכון: הבדלי סדר הערכה בנקודה צפה בין מה שייצר את הקובץ לבין ה-evaluator הנוכחי יניבו הבדלים בגודל הזה על סכומים ארוכים, ולדווח עליהם כממצאי שלמות זה רעש

מאפסים את שתי הסובלנויות כשהשאלה אחרת — כשמנסים לגלות אם evaluator שינה התנהגות בין גרסאות, או אם כלי צד שלישי כותב ערכים מחדש בצורה שונה בעדינות. באפס, אותה סטייה של 4E-7 נהיית נראית לעין, וכך גם כל השאר. בוחרים סובלנות לפי השאלה ששואלים, ורושמים את הבחירה לצד הדוח, כי דוח בלי הסובלנות שלו אינו ניתן לפרשנות

שתי יכולות שכנות משלימות את התמונה. כשרוצים לדעת למה נוסחה בודדת מניבה את הערך שהיא מניבה, התצוגה צעד-אחר-צעד בtracer הערכת הנוסחאות היא הכלי הנכון. כשרוצים במכוון שיכבדו ערכי cache בלי שום חישוב מחדש — למשל בנתיב קליטה שחייב לשחזר את הקובץ בדיוק כפי שהגיע — המצב הזה מתואר בקריאת ערכי נוסחה מה-cache בלי חישוב מחדש. הביקורת היא מה שיושב בין השניים: היא אומרת לך אם לסמוך על ה-cache זה בטוח. היא מגיעה עם רכיב הגיליונות של HotXLS ל-Delphi גם למנוע הבינארי וגם למנוע ה-OOXML