Aby obliczyć zamierzone odwołania cykliczne w Delphi, HotXLS udostępnia obliczenia iteracyjne w swoim silniku XLSX: ustawienie TXLSXWorkbook.Iterate na wartość True sprawia, że TXLSXWorkbook.Recalculate przechodzi każdy wykryty cykl odwołań w kierunku punktu stałego — maksymalnie do liczby przebiegów określonej przez IterateCount lub do momentu, gdy zmiana w każdej komórce będzie mniejsza niż IterateDelta — zamiast zwracać #REF! i rezygnować
To rozróżnienie ma większe znaczenie, niż sugeruje pojedyncza wartość logiczna. Ten sam silnik, który wykrywa cykl odwołań i odmawia pętli na nim, po przełączeniu jednej właściwości oceni ten cykl celowo, aż do momentu ustalenia wartości. Zachowanie jasnego podziału między tymi dwoma zachowaniami — kiedy cykl jest błędem do zgłoszenia, a kiedy modelem do rozwiązania — jest tematem tego artykułu
Dlaczego odwołanie cykliczne domyślnie powoduje błąd?
Domyślnie HotXLS traktuje każdy cykl odwołań jako błąd autora i zgłasza go zamiast obliczać. TXLSXWorkbook.Recalculate buduje wykres zależności formuł, oblicza każdą komórkę z formułą w kolejności topologicznej i zwraca lxErrorRef w momencie znalezienia cyklu — węzły, które nie mogą zostać zwolnione podczas sortowania topologicznego. Członkowie tego cyklu zachowują swoje poprzednie buforowane wartości; każda formuła poza cyklem jest nadal obliczana normalnie. Mechanika tego wykresu oraz powód, dla którego członkowie cyklu są pomijani zamiast zapętlania, zostały omówione w powiązanym artykule na temat przyrostowego ponownego obliczania formuł i wykresu zależności
Wartość domyślna jest bezpieczna, ponieważ większość cykli to błędy: wiersz podsumowania przypadkowo wciągnięty do własnego zakresu SUM, kopiowanie i wklejanie, które przesunęło odwołanie na samego siebie. Wyraźny kod błędu w czasie ponownego obliczania jest dokładnie tym, czego oczekujesz w takich przypadkach. Jednak specyficzna i ważna klasa modeli jest cykliczna celowo. Harmonogramy odsetek od odsetek, cykliczne alokacje kosztów lub narzutów między działami oraz obliczenia opłat opartych na saldzie opisują wartość, która w uzasadniony sposób wraca do własnych danych wejściowych — a Excel oblicza je dopiero po zaznaczeniu przez użytkownika opcji Plik → Opcje → Formuły → Włącz obliczenia iteracyjne
Jak włączyć obliczenia iteracyjne w HotXLS?
HotXLS odzwierciedla to pole wyboru programu Excel za pomocą trzech właściwości w TXLSXWorkbook: Iterate włącza ten tryb, a gdy ma wartość True, wykryty cykl jest przekazywany do modułu rozwiązywania iteracyjnego zamiast generować błąd lxErrorRef. Rozważmy kanoniczną parę komórek z odsetkami od odsetek, gdzie saldo końcowe zależy od odsetek, a odsetki zależą od salda
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 odwołuje się do B4, a B4 odwołuje się do B3, więc wykres zależności zgłasza cykl dwuwęzłowy. Przy pozostawieniu właściwości Iterate na domyślnej wartości False, ta para zwracałaby błąd lxErrorRef i żadna z komórek nie ustaliłaby swojej wartości. Gdy ma wartość True, Recalculate inicjuje cykl na podstawie bieżących buforowanych wartości i ponownie ocenia jego członków przebieg po przebiegu, przekazując wyniki każdego przebiegu jako dane wejściowe do następnego przebiegu, aż liczby przestaną się zmieniać. Tutaj postać zamknięta to principal / (1 - rate), so the balance settles at 1052.63 and the interest at 52.63 — to te same liczby, które Excel generuje z włączoną iteracją
Co powoduje zatrzymanie iteracji?
Dwa niezależne warunki zatrzymania ograniczają moduł rozwiązywania, a zrozumienie obu zapobiega generowaniu błędów lub zapętleniu modelu. IterateCount to sztywny limit liczby ponownych ocen członków cyklu; domyślnie wynosi 100, co odpowiada programowi Excel. IterateDelta to próg zbieżności: po każdym przebiegu moduł rozwiązywania mierzy największą zmianę liczbową we wszystkich komórkach cyklu, a gdy ta maksymalna zmiana spadnie poniżej IterateDelta — domyślnie 0,001 — pętla przebiegu kończy się przedwcześnie. Warunek, który zostanie spełniony jako pierwszy, kończy iterację
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;
Jedna z konsekwencji jest warta jasnego przedstawienia, ponieważ stanowi ona rzeczywistą granicę tej funkcji. Gdy limit zostanie osiągnięty bez spadku zmiany poniżej IterateDelta, SolveCycleIteratively nie zgłasza błędu — zwraca lxOk i pozostawia komórki z wartościami z ostatniej iteracji, dokładnie tak, jak Excel zapisuje ostatnie obliczone liczby, gdy jego własny limit iteracji zostanie osiągnięty bez zbieżności. Zatem pomyślny kod powrotu z Recalculate w trybie iteracyjnym oznacza, że „moduł rozwiązywania został uruchomiony”, a nie „moduł rozwiązywania osiągnął zbieżność”. Model, którego pętla sprzężenia zwrotnego jest rozbieżna lub oscyluje, po cichu zużyje wszystkie przebiegi IterateCount i zwróci liczby, które wcale nie są punktem stałym, a żadne wyjątki nie zaznaczają tej różnicy
Jak ustawienie jest zapisywane w plikach XLSX i XLS?
Ustawienia obliczeń iteracyjnych są zachowywane w obu formatach arkuszy kalkulacyjnych, więc skoroszyt otwarty w programie Excel zachowuje się tak, jak skonfigurował go kod Delphi. Po stronie XLSX moduł zapisu emituje element OOXML <calcPr> tylko wtedy, gdy Iterate ma wartość True, i pomija wszelkie atrybuty, które nadal mają wartości domyślne, aby ograniczyć dane wyjściowe do minimum: skoroszyt korzystający z domyślnych wartości zapisuje tylko <calcPr iterate="1"/>, podczas gdy iterateCount pojawia się tylko wtedy, gdy różni się od 100, a iterateDelta tylko wtedy, gdy różni się od 0,001. Po otworciu TXLSXWorkbook odczytuje te same trzy atrybuty z powrotem, więc konwersja w obie strony jest symetryczna
Starszy silnik BIFF8 (.xls), TXLSWorkbook, przenosi równoważny stan za pośrednictwem trzech oddzielnych rekordów w ramach innej trójki właściwości. EnableIteration mapuje się na rekord CalcIter ($0011, [MS-XLS] §2.4.33), MaxIterations na rekord CalcCount ($000C, [MS-XLS] §2.4.31), a MaxIterationChange na rekord CalcDelta ($0010, [MS-XLS] §2.4.32). Settery wymuszają zakresy specyfikacji — CalcCount must lie in 1..32767, so MaxIterations is clamped, and a negative MaxIterationChange snaps back to the 0.001 default. Ustaw je w załadowanym skoroszycie .xls, a trzy rekordy obliczeniowe zostaną wiernie zapisane podczas zapisu
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;Zwróć uwagę na celowy podział nazewnictwa: silnik XLSX obsługuje właściwości Iterate / IterateCount / IterateDelta (słownictwo OOXML), podczas gdy silnik BIFF8 obsługuje EnableIteration / MaxIterations / MaxIterationChange (zgodnie z nazwami rekordów [MS-XLS]). Obie trójki opisują te same trzy pokrętła — przełącznik wł./wył., limit iteracji i różnicę zbieżności — z tymi samymi domyślnymi wartościami: wyłączone, 100 i 0,001
Kiedy odwołanie cykliczne jest błędem, a nie modelem?
Włączenie iteracji nie jest sposobem na zniknięcie ostrzeżeń o odwołaniach cyklicznych, a traktowanie go w ten sposób jest pułapką. Włączenie właściwości Iterate globalnie konwertuje każdy przypadkowy cykl — ten, który domyślny kod błędu miał wychwycić — w cicho zbieżną lub cicho niezbieżną liczbę. Dyscyplina jest odwrotna: utrzymuj wartość Iterate jako False w normalnym trybie, aby rzeczywiste błędy autora nadal pojawiały się jako lxErrorRef, i włączaj iterację tylko w skoroszytach, których cykliczność jest celowa i zrozumiała
Kiedy pojawi się cykl i nie masz pewności, jakiego jest rodzaju, narzędzie do śledzenia obliczania formuł (formula evaluation tracer) pozwala je rozróżnić: prześledź podejrzaną formułę, a łańcuch odwołań, który się zapętla, stanie się widoczny krok po kroku, dzięki czemu możesz zdecydować, czy koduje on rzeczywistą pętlę sprzężenia zwrotnego, czy też zabłąkane odwołanie do samego siebie. Pomocne jest również pamiętanie, że komórka cyklu może wywołać dowolną wbudowaną funkcję w drodze wokół pętli — ten sam kalkulator, który rozwiązuje formułę inżynieryjną lub liczb zespolonych, ocenia członków cyklu przy każdym przebiegu — więc rozbieżny model jest często problemem z formułą wewnątrz pętli, a nie problemem z ustawieniami iteracji
Praktyczna lista kontrolna jest krótka. Potwierdź, że pętla ma rzeczywisty punkt stały, zanim zaczniesz polegać na iteracji; utrzymuj właściwość IterateDelta na tyle wąsko, aby słowo „zbieżny” oznaczało to, czego wymaga Twój model; a po operacji Recalculate, od której oczekujesz zbieżności, sprawdź poprawność znanego wyniku, zamiast ufać samemu lxOk, ponieważ ten kod nie potrafi odróżnić zbieżności od osiągniętego limitu iteracji
Iteracyjne obliczanie odwołań cyklicznych jest częścią silnika XLSX w komponencie HotXLS Delphi Excel, wraz z przyrostowym obliczaniem na wykresie zależności, na którym się opiera, oraz trwałością OOXML i BIFF8, która przenosi to ustawienie do każdego zapisywanego pliku. W przypadku modeli finansowych i inżynieryjnych, które są cykliczne z założenia, stanowi to różnicę między kodem błędu a odpowiedzią