HotXLS, natywna biblioteka Excel dla środowisk Delphi i C++Builder, wykonuje przyrostowe przeliczanie formuł za pomocą metody TXLSXWorkbook.Recalculate. Pierwsze wywołanie buduje graf zależności formuł i oblicza każdą komórkę z formułą; każde kolejne wywołanie przelicza ponownie tylko te komórki, na które wpłynęły wartości zapisane od czasu ostatniego przebiegu, w porządku topologiczny, w jednym przebiegu, którego koszt jest proporcjonalny do liczby zmienionych komórek (dirty cells), a nie do rozmiaru skoroszytu
Ta jedna decyzja projektowa decyduje o różnicy między modelu finansowym, który reaguje na zmianę założeń w milisekundy, a takim, który zawiesza się na sekundy. Jeśli generujesz raporty, w których garść komórek wejściowych zasila tysiące powiązane formuły, dalsza część tego artykułu wyjaśnia, co robi graf zależności, które funkcje są wyłączone z przyrostowości oraz jak zgłaszane są odwołania cykliczne, chroniąc przed pętlą bez końca
Dlaczego zmiana jednej komórki przelicza sto tysięcy formuł?
Naiwny silnik formuł nie pamięta, kto od kogo zależy, więc jego jedynym bezpiecznym krokiem po jakiejkolwiek edycji jest ponowne obliczenie wszystkiego. Co gorsza, klasyczna strategia rekurencyjna — gdy formuła A odwołuje się do formuły B, oblicz B na miejscu — przelicza ponownie odwołane komórki bezwarunkowo, ignorując jakąkolwiek wartość z bufora. Łańcuch n formuł, z których każda odwołuje się do poprzedniej, kosztuje O(n²) obliczeń na pełny przebieg, a odwołanie cykliczne spycha rekurencję w przepaść. Każdy programista arkuszy kalkulacyjnych, który połączył model kaskadowy z rekurencyjnym ewaluatorem, był świadkiem obu tych trybów awarii
Sam Excel rozwiązał ten problem dziesięciolecia temu za pomocą łańcucha obliczeń: uporządkowania komórek z formułami utrzymywanego tak, że edycja oznacza mały zestaw komórek jako zmienione, a silnik przechodzi tylko przez odpowiednią końcówkę łańcucha. HotXLS stosuje tę samą ideę w postaci jawnego grafu zależności, budowanego raz ze skompilowanych drzew formuł i używanego ponownie przy kolejnych przebiegach przeliczania. Nie chodzi tu o spryt; chodzi o to, aby koszt przeliczania zależał od rozmiaru edycji, a nie od rozmiaru skoroszytu
Jak graf zależności zamienia edycję w pojedynczy przebieg
Graf zależności HotXLS przypisuje każdej komórce z formułą jeden węzeł, w którym krawędzie prowadzą od poprzednika do następcy. Gdy Twój kod zapisuje wartość komórki, skoroszyt oznacza ją jako zmienioną (dirty); gdy uruchamiane jest Recalculate, ta zmiana propaguje się wzdłuż krawędzi do każdej powiązanej formuły, a zmieniony podgraf jest obliczany dokładnie raz w porządku topologicznym przy użyciu algorytmu Kahna. Ponieważ formuła nigdy nie jest odwiedzana przed swoimi poprzednikami, każdy węzeł wymaga tylko jednego obliczenia — to właśnie sprawia, że przebieg ma koszt O(dirty)
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;Każdy wynik trafia do buforowanej wartości komórki (Value), więc po zakończeniu Recalculate odczytujesz dane wyjściowe w taki sam sposób, jak w przypadku każdej innej komórki. W pętli generowania raportów schemat wygląda dokładnie tak, jak w powyższym kodzie: wczytaj lub zbuduj model raz, a następnie naprzemiennie zapisuj kilka komórek wejściowych i wywołuj Recalculate, płacąc tylko za formuły, które faktycznie zależą od wprowadzonych zmian
Które funkcje Excela wymuszają przeliczanie przy każdym przebiegu?
HotXLS traktuje funkcje NOW, TODAY, RAND, OFFSET oraz INDIRECT jako volatile (nietrwałe): każda formuła zawierająca jedną z nich jest obliczana ponownie przy każdym wywołaniu Recalculate, niezależnie od tego, czy cokolwiek zmieniło się wcześniej. Pierwsze trzy są nietrwałe z tego samego powodu, co w programie Excel — ich wynik zależy od momentu obliczenia, a nie od innych komórek. Funkcje OFFSET i INDIRECT są nietrwałe z subtelniejszego powodu: komórki, które odczytują, są obliczane w czasie wykonywania, więc graf nie może statycznie wiedzieć, które krawędzie należy narysować
Ta sama konserwatywna reguła rozciąga się na odwołania, których moduł budowania grafu nie może przypisać do pojedynczego prostokąta. Formuła przechodząca przez wieloobszarowy zakres nazwany lub formuła odwołująca się do zewnętrznego skoroszytu jest również traktowana jako volatile i obliczana przy każdym przebiegu. Ta zasada jest celowa: dodatkowe obliczenie kosztuje niewiele czasu, ale brakująca krawędź zależności oznacza niezauważalnie nieświeżą wartość w wygenerowanym raporcie, co jest znacznie gorszym problemem. Jeśli Twój model opiera się na nazwach o zasięgu skoroszytu, powiązany artykuł o nazwach zdefiniowanych i formułach między arkuszami opisuje, jak rozwiązywane są nazwy jednoobszarowe — te uczestniczą w grafie normalnie
Wnioski praktyczne nasuwają się same. Utrzymuj krytyczne ścieżki dużego modelu na zwykłych odwołaniach do komórek i zakresów, gdzie graf może wykonywać swoją pracę, a funkcje OFFSET i INDIRECT stosuj tylko w nielicznych miejscach, które naprawdę wymagają dynamicznego adresowania. Model z tysiącem funkcji nietrwałych uruchamia ten tysiąc przy każdym przebiegu, bez względu na to, jak mała była zmiana — to dokładnie takie zachowanie, jakie użytkownicy Excela znają z arkuszy, które „przeliczają się przy każdym naciśnięciu klawisza”
W jaki sposób HotXLS zgłasza odwołania cykliczne?
Metoda TXLSXWorkbook.Recalculate zwraca lxOk przy poprawnym przebiegu oraz lxErrorRef, gdy wykryje cykl odwołań. Elementy cyklu są identyfikowane podczas sortowania topologicznego — są to węzły, których algorytm Kahna nigdy nie może zwolnić — i są pomijane zamiast zapętlania: ich buforowane wartości pozostają takie, jakie były, podczas gdy każda formuła poza cyklem nadal oblicza się normalnie w odpowiedniej kolejności. Twój kod otrzymuje konkretny kod błędu zamiast zawieszenia programu
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;Znalezienie komórek tworzących cykl to zadanie debugowania, a narzędzie do śledzenia ewaluacji formuł jest do tego idealnym wyborem: prześledź podejrzaną formułę, a łańcuch odwołań, który zapętla się do samego siebie, stanie się widoczny krok po kroku. Cykle w rzeczywistych modelach są prawie zawsze błędem autora — wiersz podsumowania przypadkowo uwzględniony we własnym zakresie SUM — więc wyraźny kod błędu w momencie przeliczania jest dokładnie tym, czego potrzebujesz
Formuły tablicowe, śledzenie zmian i moment odbudowy grafu
Formuły tablicowe CSE otrzymują jeden węzeł dla całego zakotwiczonego prostokąta, a nie po jednym węźle na komórkę. Formuła główna (root) jest obliczana raz na przebieg; wynikowa macierz jest zapisywana bezpośrednio w każdej komórce składowej, a formuła odwołująca się do dowolnej komórki wewnątrz zakotwiczonego zakresu — nie tylko lewego górnego rogu — pobiera krawędź zależności z tego głównego węzła. Wyniki skalarne są propagowane po prostokącie zgodnie z regułami tradycyjnej semantyki tablicowej Excela
Śledzenie zmian (dirty tracking) podpina się pod zwykłe metody zapisu właściwości, więc w Twoim kodzie nic się nie zmienia. Zapisanie Value w komórce powiadamia skoroszyt i oznacza elementy zależne jako zmienione; przypisanie nowej formuły (Formula) to zmiana strukturalna, więc oznacza cały graf jako przestarzały, a następne wywołanie Recalculate odbudowuje go przed ewaluacją. Dodawanie, usuwanie lub przesuwanie arkuszy również unieważnia graf, ponieważ tożsamość węzła koduje indeks arkusza. Gdy graf nie jest używany — w skoroszycie, w którym nigdy nie wywołujesz Recalculate — te punkty kontrolne kosztują jedno sprawdzenie wartości nil przy każdym przypisaniu, więc zwykłe zadania odczytu i zapisu nie są spowalniane
Jedna granica zasługuje na szczere określenie: graf śledzi zależności między komórkami, więc zdefiniowana przez użytkownika funkcja zarejestrowana przez zdarzenie OnUserFunction jest przeliczana ponownie, gdy zmieniają się komórki zasilające jej argumenty, zupełnie jak każda inna formuła. Jeśli rozszerzasz silnik w ten sposób, artykuł o funkcjach niestandardowych w silniku formuł HotXLS opisuje kontrakt wywołania zwrotnego oraz sposób przekazywania wartości argumentów
Przyrostowe przeliczanie formuł jest częścią standardowego silnika XLSX w komponencie HotXLS Delphi Excel Component, obok kalkulatora formuł, nazw zdefiniowanych i potoku importu/eksportu, który przyspiesza. Jeśli Twoja aplikacja w Delphi lub C++Builder zarządza aktywnymi modelami — arkuszami wycen, skoroszytami konsolidacji, raportami kaskadowymi — metoda Recalculate decyduje o różnicy między przeliczeniem całego skoroszytu a przeliczeniem pojedynczej edycji