חישוב מחדש תוספתי של נוסחאות ב-HotXLS עבור דלפי

HotXLS, ספריית ה-Excel הטבעית עבור דלפי ו-C++Builder, מבצעת חישוב מחדש תוספתי (incremental recalculation) של נוסחאות באמצעות TXLSXWorkbook.Recalculate. הקריאה הראשונה בונה גרף תלות של נוסחאות ומעריכה כל תא נוסחה; כל קריאה עוקבת מעריכה מחדש רק את התאים שהושפעו מכתיבת ערכים מאז המעבר האחרון, בסדר טופולוגי, בסריקה יחידה שעלותה יחסית למספר התאים המלוכלכים (dirty cells) ולא לגודל חוברת העבודה כולה

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

מדוע שינוי תא בודד מחשב מחדש מאה אלף נוסחאות?

למנוע נוסחאות נאיבי אין זיכרון לגבי מי תלוי במי, ולכן הצעד הבטוח היחיד שלו לאחר כל עריכה הוא להעריך הכל מחדש. גרוע מכך, האסטרטגיה הרקורסיבית הקלאסית — כאשר נוסחה א' מפנה לנוסחה ב', מעריכים את ב' בו במקום — מעריכה מחדש תאים מופנים ללא תנאי, תוך התעלמות מכל ערך שנשמר במטמון. שרשרת של n נוסחאות שכל אחת מהן מפנה לקודמתה עולה (O(n² הערכות לכל מעבר מלא, והפניה מעגלית שולחת את הרקורסיה לתהום. כל מפתח גיליונות אלקטרוניים שחיבר מודל משורשר למעריך רקורסיבי ראה את שני מצבי הכשל הללו מתרחשים

כיצד גרף התלות הופך עריכה למעבר יחיד

גרף התלות של HotXLS מעניק לכל תא נוסחה צומת (node) אחד, כאשר קשתות (edges) רצות מצמתים קודמים לצמתים תלויים. כאשר הקוד שלכם כותב ערך לתא, חוברת העבודה מתעדת את התא כמלוכלך; כאשר Recalculate מורץ, הלכלוך מתפשט לאורך הקשתות לכל נוסחה בהמשך הדרך, ותת-הגרף המלוכלך מוערך בדיוק פעם אחת בסדר טופולוגי באמצעות האלגוריתם של קאהן (Kahn's algorithm). מכיוון שנוסחה לעולם אינה זוכה לביקור לפני קודמותיה, כל צומת זקוק להערכה יחידה — זה מה שהופך את המעבר ל-(O(dirty

הסדר הטופולוגי פותר גם את בעיית הרקורסיה בשורשה. במהלך מעבר חישוב מחדש, המנוע עובר למצב ייעודי שבו כל הפניה לתא נוסחה אחר קוראת את הערך השמור במטמון של אותו תא ישירות במקום להעריך אותו מחדש — הסדר מבטיח שהמטמון כבר מעודכן. אותו מנגנון אומר שמעגל הפניות (reference cycle) אינו יכול להפעיל רקורסיה בלתי מוגבלת: שום דבר בתוך המעבר אינו נכנס מחדש למעריך עבור תא שכן

var
  Book: TXLSXWorkbook;
  Inputs, Model: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Inputs := Book.Sheets.Add('Inputs');
    Model  := Book.Sheets.Add('Model');

    Inputs.Cells[2, 2].Value := 0.05;                 // growth assumption
    Model.Cells[2, 2].Formula := 'Inputs!B2*1000';    // XLSX formulas take no leading '='
    Model.Cells[3, 2].Formula := 'B2*(1+Inputs!B2)';
    // ... thousands more rows cascading off the same assumption ...

    Book.Recalculate;                 // first call: builds the graph, full evaluation

    Inputs.Cells[2, 2].Value := 0.07; // one edit marks one cell dirty
    Book.Recalculate;                 // second call: only the downstream chain runs
  finally
    Book.Free;
  end;
end;

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

אילו פונקציות Excel מאלצות חישוב מחדש בכל מעבר?

HotXLS מתייחס לפונקציות NOW, TODAY, RAND, OFFSET ו-INDIRECT כפונקציות תנודתיות (volatile): כל נוסחה המכילה אחת מהן מוערכת מחדש בכל מעבר של Recalculate, בין אם משהו קודם השתנה ובין אם לאו. השלוש הראשונות תנודתיות מאותה סיבה שהן כאלה ב-Excel — התוצאה שלהן תלויה ברגע ההערכה, ולא בתאים אחרים. OFFSET ו-INDIRECT תנודתיות מסיבה מעודנת יותר: התאים שהן קוראות מחושבים בזמן ריצה, כך שהגרף אינו יכול לדעת באופן סטטי אילו קשתות לצייר עבורן

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

כיצד HotXLS מדווח על הפניות מעגליות?

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

case Book.Recalculate of
  lxOk:
    SaveReport(Book);
  lxErrorRef:
    // a reference cycle exists; cycle members kept their previous
    // cached values and everything outside the cycle is up to date
    LogWarning('Circular reference detected - review model inputs');
end;

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

נוסחאות מערך, מעקב לכלוך, ומתי הגרף נבנה מחדש

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

מעקב לכלוך (dirty tracking) מתחבר למאפייני ה-setters הרגילים, כך ששום דבר בקוד שלכם אינו משתנה. כתיבת Value לתא מיידעת את חוברת העבודה ומסמנת את התאים התלויים כמלוכלכים; הקצאת Formula חדשה היא שינוי מבני, ולכן היא מסמנת את הגרף כולו כמיושן, והקריאה הבאה ל-Recalculate בונה אותו מחדש לפני ההערכה. הוספה, מחיקה או העברה של גיליונות פוסלת אף היא את הגרף, מכיוון שזהות הצומת מקודדת את אינדקס הגיליון. כאשר אין גרף פעיל — חוברת עבודה שלעולם אינכם קוראים עבורה ל-Recalculate — החיבורים עולים בדיקת nil בודדת לכל הקצאה, כך שעומסי עבודה של קריאה-כתיבה רגילים אינם מושפעים

גבול אחד שראוי לציין בגלוי: הגרף עוקב אחר תלויות בין תאים, ולכן פונקציה מוגדרת משתמש הרשומה באמצעות OnUserFunction מוערכת מחדש כאשר התאים המזינים את הארגומנטים שלה משתנים, כמו כל נוסחה אחרת. אם אתם מרחיבים את המנוע באופן זה, המאמר על פונקציות מותאמות אישית במנוע הנוסחאות של HotXLS מלווה את חוזה ה-callback וכיצד מגיעים ערכי הארגומנטים

חישוב מחדש תוספתי הוא חלק ממנוע ה-XLSX הסטנדרטי ב-רכיב HotXLS Delphi Excel, לצד מחשב הנוסחאות, שמות מוגדרים וצינור הייבוא/ייצוא שהוא מאיץ. אם אפליקציית דלפי או C++Builder שלכם מתחזקת מודלים חיים — גיליונות תמחור, חוברות עבודה מאוחדות או דוחות משורשרים — הפונקציה Recalculate היא ההבדל בין חישוב מחדש של חוברת עבודה לבין חישוב מחדש של שינוי