HotXLS, izvorna Delphi i C++Builder Excel biblioteka, vrši inkrementalni ponovni proračun formula preko metode TXLSXWorkbook.Recalculate. Prvi poziv gradi graf zavisnosti formula i evaluira svaku ćeliju sa formulom; svaki kasniji poziv ponovo procenjuje samo one ćelije na koje utiču upisane vrednosti od poslednjeg prolaza, u topološkom redosledu, u jednom prolazu čiji je trošak proporcionalan broju izmenjenih (dirty) ćelija a ne veličini radne sveske
Ta jedna odluka u dizajnu predstavlja razliku između finansijskog modela koji na izmenjenu pretpostavku reaguje u milisekundama i onog koji se zamrzne na nekoliko sekundi. Ako generišete izveštaje gde nekoliko ulaznih ćelija hrani hiljade nizvodnih formula, ostatak ovog članka objašnjava šta graf zavisnosti radi, koje funkcije ne učestvuju u inkrementalnosti i kako se kružne reference prijavljuju umesto da upadnu u beskonačnu petlju
Zašto promena jedne ćelije ponovo izračunava sto hiljada formula?
Naivan mehanizam formula nema memoriju o tome ko od koga zavisi, tako da je njegov jedini bezbedan korak nakon bilo koje izmene da ponovo evaluira sve. Što je još gore, klasična rekurzivna strategija — kada formula A referencira formulu B, evaluiraj B na licu mesta — ponovo evaluira referencirane ćelije bezuslovno, ignorišući bilo koju keširanu vrednost. Lanac od n formula gde svaka referencira prethodnu košta O(n²) evaluacija po punom prolazu, a kružna referenca šalje rekurziju preko ivice ponora. Svaki programer tabela koji je povezao kaskadni model u rekurzivni evaluator posmatrao je kako se oba ova načina otkazivanja dešavaju
Sam Excel je ovo rešio pre nekoliko decenija sa svojim lancem proračuna: redosled ćelija sa formulama se održava tako da izmena označava mali skup ćelija kao promenjene (dirty), a mehanizam prolazi samo kroz pogođeni kraj lanca. HotXLS primenjuje istu ideju u vidu eksplicitnog grafa zavisnosti, koji se gradi jednom iz kompajliranih stabala formula i ponovo koristi kroz prolaze ponovnog proračuna. Poenta nije u domišljatosti, već u tome da bi trošak ponovnog proračuna trebao pratiti veličinu vaše izmene, a ne veličinu vaše radne sveske
Kako graf zavisnosti pretvara izmenu u jedan prolaz
HotXLS graf zavisnosti svakoj ćeliji sa formulom dodeljuje jedan čvor, sa granama koje vode od prethodnika do zavisnog čvora. Kada vaš kod upiše vrednost ćelije, radna sveska označava tu ćeliju kao promenjenu (dirty); kada se pokrene Recalculate, promena se propagira duž grana do svake nizvodne formule, a promenjeni podgraf se evaluira tačno jednom u topološkom redosledu koristeći Kanov (Kahn) algoritam. Pošto se formula nikada ne poseti pre svojih prethodnika, svakom čvoru je potrebna samo jedna evaluacija — to je ono što prolaz čini O(dirty)
Topološki redosled takođe rešava problem rekurzije u samom korenu. Tokom prolaza ponovnog proračuna, mehanizam prelazi u namenski režim u kojem bilo koje referenciranje druge ćelije sa formulom direktno čita keširanu vrednost te ćelije umesto da je ponovo evaluira — redosled garantuje da je keš već svež. Isti mehanizam sprečava da ciklus referenci pokrene neograničenu rekurziju: ništa unutar prolaza nikada ponovo ne ulazi u evaluator za susednu ćeliju
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;Svaki rezultat završava u keširanom svojstvu Value ćelije, tako da nakon povratka metode Recalculate čitate izlazne vrednosti na isti način na koji čitate bilo koju drugu ćeliju. U petlji generisanja izveštaja, šablon je tačno onakav kako je prikazano u gornjem kodu: učitajte ili izgradite model jednom, a zatim naizmenično upisujte nekoliko ulaznih ćelija i pozivajte Recalculate, plaćajući samo one formule koje zaista zavise od onoga što se promenilo
Koje Excel funkcije primoravaju ponovni proračun u svakom prolazu?
HotXLS tretira funkcije NOW, TODAY, RAND, OFFSET i INDIRECT kao volatilne (nestabilne): svaka formula koja sadrži neku od njih se ponovo evaluira u svakom prolazu metode Recalculate, bez obzira na to da li se išta uzvodno promenilo. Prve tri su volatilne iz istog razloga iz kojeg su to i u Excel-u — njihov rezultat zavisi od trenutka evaluacije, a ne od drugih ćelija. Funkcije OFFSET i INDIRECT su volatilne iz suptilnijeg razloga: ćelije koje one čitaju se računaju u vreme izvršavanja (run time), pa graf zavisnosti ne može statički znati koje grane treba da nacrta za njih
Isto konzervativno pravilo se odnosi i na reference koje graditelj grafa ne može fiksirati na jedan pravougaonik. Formula koja koristi definisano ime sa više oblasti (multi-area), ili ona koja referencira spoljnu radnu svesku, se takođe degradira na volatilnu i ponovo evaluira u svakom prolazu. Ova politika je namerna: dodatna evaluacija košta malo vremena, ali grana zavisnosti koja nedostaje znači tiho zastarelu vrednost u isporučenom izveštaju, što je daleko gori ishod. Ako se vaš model oslanja na imena na nivou radne sveske, prateći članak o definisanim imenima i međulistnim formulama pokriva kako se razrešavaju imena sa jednom oblašću — ona normalno učestvuju u grafu
Praktične smernice slede direktno. Držite kritične putanje (hot paths) velikog modela na običnim referencama ćelija i opsega gde graf zavisnosti može da radi svoj posao, i stavite u karantin OFFSET i INDIRECT na onih nekoliko mesta koja zaista zahtevaju dinamičko adresiranje. Model sa hiljadu volatilnih formula ponovo pokreće tih hiljadu u svakom prolazu bez obzira na to koliko je mala izmena bila — upravo ono ponašanje koje korisnici Excel-a znaju iz radnih svezaka koje se "ponovo izračunavaju pri svakom pritisku na taster"
Kako HotXLS prijavljuje kružne reference?
Metoda TXLSXWorkbook.Recalculate vraća lxOk kod čistog prolaza i lxErrorRef kada detektuje ciklus referenci. Članovi ciklusa se identifikuju tokom topološkog sortiranja — to su čvorovi koje Kanov algoritam nikada ne može osloboditi — i oni se preskaču umesto da se upadne u petlju: njihove keširane vrednosti ostaju one koje su bile, dok se svaka formula van ciklusa i dalje normalno evaluira po redosledu. Vaše mesto poziva dobija definisan kod greške umesto zamrzavanja programa
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;Pronalaženje ćelija koje čine ciklus je posao otklanjanja grešaka (debugging), a tracer evaluacije formula je pravi alat za to: pratite sumnjivu formulu i lanac referenci koji se savija sam na sebe postaje vidljiv korak po korak. Ciklusi u stvarnim modelima su skoro uvek greška pri pisanju formula — zbirni red koji je slučajno uključen u sopstveni SUM opseg — tako da je jasan kod greške u vreme ponovnog proračuna upravo ono što želite
Formule niza, praćenje promena (dirty tracking) i kada se graf ponovo gradi
CSE formule niza dobijaju jedan čvor za čitav fiksirani pravougaonik, a ne jedan čvor po ćeliji. Korena formula se evaluira jednom po prolazu; rezultujuća matrica se upisuje direktno u svaku ćeliju člana, a formula koja referencira bilo koju ćeliju unutar fiksiranog opsega — ne samo gornje levo sidro — dobija granu zavisnosti od tog korenog čvora. Skalarne vrednosti se emituju preko čitavog pravougaonika onako kako to propisuje tradicionalna semantika niza u Excel-u
Praćenje promena (dirty tracking) se povezuje sa uobičajenim seterima svojstava (property setters), tako da se ništa u vašem kodu ne menja. Upisivanje svojstva Value u ćeliju obaveštava radnu svesku i označava zavisne čvorove kao promenjene (dirty); dodeljivanje nove Formula je strukturna promena, pa označava čitav graf kao zastareo, a sledeći poziv Recalculate ga ponovo gradi pre evaluacije. Dodavanje, brisanje ili pomeranje listova takođe poništava graf zavisnosti, jer identitet čvora kodira indeks lista. Kada nijedan graf zavisnosti nije aktivan — na primer kod radne sveske gde nikada ne pozivate Recalculate — ove kuke koštaju samo jednu proveru na nil po dodeljivanju vrednosti, tako da to ne utiče na obične poslove čitanja i pisanja
Jedno ograničenje koje vredi iskreno navesti: graf prati zavisnosti između ćelija, pa se korisnički definisana funkcija registrovana preko OnUserFunction ponovo evaluira kada se ćelije koje hrane njene argumente promene, baš kao i svaka druga formula. Ako na taj način proširujete mehanizam, članak o prilagođenim funkcijama u HotXLS mehanizmu formula vodi vas kroz ugovor o povratnom pozivu (callback contract) i opisuje kako vrednosti argumenata stižu
Inkrementalni ponovni proračun je deo standardnog XLSX mehanizma u HotXLS Delphi Excel komponenti, zajedno sa kalkulatorom formula, definisanim imenima i import/export cevovodom koji ubrzava. Ako vaša Delphi ili C++Builder aplikacija održava žive modele — tarifne listove, konsolidacione radne sveske, kaskadne izveštaje — metoda Recalculate predstavlja razliku između ponovnog proračuna radne sveske i ponovnog proračuna same izmene