חישוב איטרטיבי עבור הפניות מעגליות (Circular References) בדלפי

כדי לחשב הפניות מעגליות מכוונות בדלפי, HotXLS חושפת חישוב איטרטיבי במנוע ה-XLSX שלה: הגדר את TXLSXWorkbook.Iterate ל-True ו-TXLSXWorkbook.Recalculate תעבור על כל מחזור הפניות שזוהה לקראת נקודה קבועה — עד ל-IterateCount מעברים, או עד שכל תא משתנה בפחות מ-IterateDelta — במקום להחזיר שגיאת #REF! ולהתייאש

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

מדוע הפניה מעגלית מחזירה שגיאה כברירת מחדל?

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

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

כיצד מאפשרים חישוב איטרטיבי ב-HotXLS?

HotXLS משקפת את תיבת הסימון הזו של Excel עם שלושה מאפיינים ב-TXLSXWorkbook: ‏Iterate מפעיל את המצב, וכאשר הוא מוגדר True מחזור שזוהה נמסר לפותר איטרטיבי במקום לייצר שגיאת lxErrorRef. שקול את צמד התאים הקנוני של ריבית דריבית, שבו יתרת הסגירה תלויה בריבית והריבית תלויה ביתרה

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Model');
    Sheet.Cells[1, 2].Value   := 1000;     // B1: opening principal
    Sheet.Cells[2, 2].Value   := 0.05;     // B2: period rate
    Sheet.Cells[3, 2].Formula := 'B1+B4';  // B3: balance  = principal + interest
    Sheet.Cells[4, 2].Formula := 'B3*B2';  // B4: interest = balance * rate

    Book.Iterate := True;                   // opt in to iterative calculation
    if Book.Recalculate = lxOk then
      // B3 converges to 1052.63..., B4 to 52.63...
      Report(Sheet.Cells[3, 2].Value);
  finally
    Book.Free;
  end;
end;

B3 מפנה ל-B4 ו-B4 מפנה ל-B3, ולכן גרף התלויות מדווח על מחזור של שני צמתים. כאשר Iterate נותר בברירת המחדל שלו כ-False, צמד זה יחזור כ-lxErrorRef ואף אחד מהתאים לא יתייצב. כאשר הוא True,‏ Recalculate מזינה את המחזור מהערכים הנוכחיים שנשמרו במטמון ומעריכה מחדש את איבריו מעבר אחר מעבר, ומזינה את הפלטים של כל מעבר כקלטים של המעבר הבא, עד שהמספרים מפסיקים לזוז. כאן הנוסחה הסגורה היא principal / (1 - rate), ולכן היתרה מתייצבת ב-1052.63 והריבית ב-52.63 — אותם נתונים ש-Excel מייצרת עם חישוב איטרטיבי מאופשר

מה גורם לאיטרציה לעצור?

שני תנאי עצירה עצמאיים מגבילים את הפותר, והבנת שניהם היא מה שמונע ממודל להחזיר שגיאה או להסתובב בלולאה אינסופית. IterateCount הוא התקרה הקשיחה למספר הפעמים שבהן איברי המחזור מוערכים מחדש; הוא מוגדר כ-100 כברירת מחדל, בדומה ל-Excel.‏ IterateDelta הוא סף ההתכנסות: לאחר כל מעבר הפותר מודד את השינוי המספרי הגדול ביותר בכל תאי המחזור, וברגע שהשינוי המרבי הזה יורד מתחת ל-IterateDelta — ברירת מחדל 0.001 — לולאת המעבר נשברת מוקדם יותר. התנאי שנפגש ראשון מסיים את האיטרציה

Book.Iterate := True;
Book.IterateCount := 1000;    // hard cap: at most 1000 passes over the cycle
Book.IterateDelta := 0.0001;  // convergence: stop once every cell moves < 0.0001

case Book.Recalculate of
  lxOk:
    // the cycle converged, OR it hit the 1000-pass cap and kept its
    // last-iteration values -- both paths return lxOk when Iterate is True
    SaveWorkbook(Book);
  lxErrorRef:
    // only reachable with Iterate = False: the cycle was reported, not solved
    LogWarning('Circular reference with iteration disabled');
end;

תוצאה אחת ראויה לציון ברור, מכיוון שהיא הגבול הכנה של התכונה. כאשר מגיעים לתקרה מבלי שהשינוי ירד מתחת ל-IterateDelta,‏ SolveCycleIteratively אינה מעלה שגיאה — היא מחזירה lxOk ומשאירה את התאים כשהם מחזיקים בערכי האיטרציה האחרונה שלהם, בדיוק כפי ש-Excel כותבת את המספרים המחושבים האחרונים כאשר מגבלת האיטרציה שלה נפגשת ללא התכנסות. לכן קוד החזרה מוצלח מ-Recalculate במצב איטרטיבי פירושו \"הפותר רץ\", ולא \"הפותר התכנס\". מודל שלולאת המשוב שלו מתבדרת או מתנדנדת יצרוך בשקט את כל מעברי IterateCount ויחזיר מספרים שאינם נקודה קבועה כלל, ושום חריגה (exception) לא תסמן את ההבדל

כיצד ההגדרה נשמרת בקובצי XLSX ו-XLS?

הגדרות החישוב האיטרטיבי נשמרות בשני פורמטי גיליונות העבודה, כך שחוברת עבודה הנפתחת ב-Excel מתנהגת כפי שקוד הדלפי שלך הגדיר אותה. בצד ה-XLSX הכותב פולט את אלמנט ה-OOXML ‏<calcPr> רק כאשר Iterate מוגדר True, והוא משמיט כל מאפיין שעדיין בברירת המחדל שלו כדי לשמור על פלט מינימלי: חוברת עבודה המשתמשת בברירות המחדל כותבת רק <calcPr iterate="1"/>, בעוד ש-iterateCount מופיע רק כאשר הוא שונה מ-100 ו-iterateDelta מופיע רק כאשר הוא שונה מ-0.001. בעת הפתיחה, TXLSXWorkbook קוראת את אותם שלושה מאפיינים בחזרה, כך שהסבב המלא סימטרי

מנוע ה-BIFF8 המיושן (.xls),‏ TXLSWorkbook, נושא את המצב המקביל דרך שלוש רשומות נפרדות תחת שלשה שונה של מאפיינים. EnableIteration ממופה לרשומת CalcIter‏ ($0011,‏ [MS-XLS] §2.4.33),‏ MaxIterations לרשומת CalcCount‏ ($000C,‏ [MS-XLS] §2.4.31), ו-MaxIterationChange לרשומת CalcDelta‏ ($0010,‏ [MS-XLS] §2.4.32). המגדירים (setters) אוכפים את טווחי המפרט — CalcCount חייב להיות בטווח 1..32767, ולכן MaxIterations מהודק (clamped), ו-MaxIterationChange שלילי חוזר לברירת המחדל של 0.001. הגדר אותם בחוברת עבודה טעונה מסוג .xls ושלוש רשומות החישוב ייכתבו בנאמנות בעת השמירה

var
  Book: TXLSWorkbook;   // BIFF8 (.xls) engine
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('model.xls');
    Book.EnableIteration    := True;    // CalcIter  record $0011
    Book.MaxIterations      := 500;     // CalcCount record $000C (clamped to 1..32767)
    Book.MaxIterationChange := 0.0001;  // CalcDelta record $0010
    Book.SaveAs('model.xls');           // the three calc records round-trip
  finally
    Book.Free;
  end;
end;

שים לב לפיצול השמות המכוון: מנוע ה-XLSX מדבר במונחי Iterate / IterateCount / IterateDelta (אוצר המילים של OOXML), בעוד מנוע ה-BIFF8 מדבר במונחי EnableIteration / MaxIterations / MaxIterationChange (המותאמים לשמות הרשומות של [MS-XLS]). שתי השלשות מתארות את אותם שלושה כפתורים — מתג הפעלה/כיבוי, תקרת איטרציות ודלתא של התכנסות — עם אותן ברירות מחדל של כבוי, 100 ו-0.001

מתי הפניה מעגלית היא באג ולא מודל?

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

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

הרשימה המעשית קצרה. אשר שלולאה יש נקודה קבועה אמיתית לפני שאתה מסתמך על איטרציה; שמור על IterateDelta הדוק מספיק כך ש-\"התכנס\" יספק את מה שהמודל שלך צריך; ולאחר Recalculate שאתה מצפה שיתכנס, בצע בדיקת תקינות לפלט ידוע במקום לסמוך על ה-lxOk בלבד, מכיוון שקוד זה אינו יכול להבחין בין התכנסות לבין הגעה לתקרת האיטרציות

חישוב הפניות מעגליות איטרטיבי הוא חלק ממנוע ה-XLSX ב-HotXLS Delphi Excel Component, לצד חישוב מחדש של גרף התלויות האינקרמנטלי שהוא נשען עליו ושמירת הנתונים של OOXML ו-BIFF8 הנושאת את ההגדרה לכל קובץ שאתה כותב. עבור המודלים הפיננסיים וההנדסיים שהם מעגליים בכוונה, זהו ההבדל בין קוד שגיאה לתשובה