HotXLS, izvorna knjižnica Excel za Delphi in C++Builder, izvaja inkrementalno preračunavanje formul prek metode TXLSXWorkbook.Recalculate. Prvi klic zgradi graf odvisnosti formul in ovrednoti vsako celico s formulo; vsak poznejši klic pa v topološkem vrstnem redu in v enem samem prehodu ponovno ovrednoti le tiste celice, na katere so vplivali vpisi vrednosti od zadnjega prehoda. Strošek tega prehoda je sorazmeren s številom umazanih celic in ne z velikostjo delovnega zvezka
Ta edina odločitev pri načrtovanju predstavlja razliko med finančnim modelom, ki se na spremenjeno predpostavko odzove v milisekundah, in tistim, ki zastane za nekaj sekund. Če ustvarjate poročila, kjer peščica vnosnih celic napaja na tisoče odvisnih formul, preostanek tega članka pojasnjuje, kaj graf počne, katere funkcije se izognejo inkrementalnosti in kako se poroča o krožnih referencah namesto neskončnega ponavljanja
Zakaj sprememba ene celice preračuna sto tisoč formul?
Naiven pogon za formule nima spomina o tem, kdo je odvisen od koga, zato je njegova edina varna poteza po vsakem urejanju ponovno ovrednotenje vsega. Še huje, klasična rekurzivna strategija — ko se formula A nanaša na formulo B, se B ovrednoti takoj — brezpogojno ponovno ovrednoti referenčne celice, pri čemer prezre kakršno koli predpomnjeno vrednost. Veriga n formul, od katerih se vsaka nanaša na prejšnjo, stane O(n²) ovrednotenj na celoten prehod, krožna referenca pa pošlje rekurzijo čez rob. Vsak razvijalec preglednic, ki je povezal kaskadni model v rekurzivni ocenjevalnik, je bil priča obema načinoma odpovedi
Excel je to rešil že pred desetletji s svojo verigo izračunavanja: urejenost celic s formulami se vzdržuje tako, da urejanje označi le majhen nabor celic kot umazane, pogon pa prehodi le prizadeti rep verige. HotXLS uporablja isto idejo kot ekspliciten graf odvisnosti, zgrajen enkrat iz kompiliiranih dreves formul in ponovno uporabljen skozi prehode preračunavanja. Bistvo ni v iznajdljivosti; gre za to, da bi moral strošek preračunavanja slediti velikosti urejanja in ne velikosti delovnega zvezka
Kako graf odvisnosti pretvori urejanje v en sam prehod
Graf odvisnosti HotXLS dodeli vsaki celici s formulo eno vozlišče, pri čemer povezave potekajo od predhodnika do odvisneža. Ko vaša koda zapiše vrednost celice, delovni zvezek zabeleži celico kot umazano; ko se zažene Recalculate, se umazanost razširi po povezavah do vsake naslednje formule, umazan podgraf pa se ovrednoti natanko enkrat v topološkem vrstnem redu z uporabo Kahnovega algoritma. Ker se formula nikoli ne obišče pred svojimi predhodniki, vsako vozlišče potrebuje eno samo ovrednotenje — zaradi tega je prehod časovne zahtevnosti O(umazani)
Topološki vrstni red prav tako odpravi problem rekurzije v korenu. Med prehodom ponovnega preračunavanja pogon preklopi v namenski način, v katerem vsaka referenca na drugo celico s formulo neposredno prebere predpomnjeno vrednost te celice, namesto da bi jo ponovno ovrednotila — vrstni red namreč zagotavlja, da je predpomnilnik že posodobljen. Isti mehanizem pomeni, da cikel referenc ne more sprožiti neomejene rekurzije: nič znotraj prehoda nikoli ne vstopi ponovno v ocenjevalnik za sosednjo celico
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; // predpostavka rasti
Model.Cells[2, 2].Formula := 'Inputs!B2*1000'; // Formule XLSX se začnejo brez enačaja '='
Model.Cells[3, 2].Formula := 'B2*(1+Inputs!B2)';
// ... na tisoče naslednjih vrstic, ki izhajajo iz iste predpostavke ...
Book.Recalculate; // prvi klic: zgradi graf, celotno vrednotenje
Inputs.Cells[2, 2].Value := 0.07; // eno urejanje označi eno celico kot umazano
Book.Recalculate; // drugi klic: zažene se le veriga odvisnih celic
finally
Book.Free;
end;
end;Vsak rezultat pristane v predpomnjeni vrednosti celice Value, zato po vrnitvi metode Recalculate izhode berete enako kot katero koli drugo celico. V zanki za ustvarjanje poročil je vzorec natanko tak kot v zgornji kodi: enkrat naložite ali zgradite model, nato pa izmenjaje zapisujete nekaj vnosnih celic in kličete Recalculate, pri čemer plačate le za formule, ki so dejansko odvisne od tistega, kar se je spremenilo
Katere funkcije Excela prisilijo ponovni izračun pri vsakem prehodu?
HotXLS obravnava funkcije NOW, TODAY, RAND, OFFSET in INDIRECT kot volatilne: vsaka formula, ki vsebuje eno izmed njih, se ponovno ovrednoti ob vsakem prehodu Recalculate, ne glede na to, ali se je kaj pred njimi spremenilo ali ne. Prve tri so volatilne iz enakega razloga kot v Excelu — njihov rezultat je odvisen od trenutka ovrednotenja in ne od drugih celic. OFFSET in INDIRECT sta volatilni iz subtilnejšega razloga: celice, ki jih bereta, se izračunajo med izvajanjem, zato graf ne more vnaprej statično vedeti, katere povezave mora narisati zanju
Enako konservativno pravilo velja za reference, ki jih graditelj grafa ne more omejiti na en sam pravokotnik. Formula, ki poteka skozi poimenovano območje z več deli, ali tista, ki se nanaša na zunanji delovni zvezek, se prav tako degradira v volatilno in se ponovno ovrednoti ob vsakem prehodu. Ta politika je namerna: dodatno ovrednotenje stane nekaj časa, manjkajoča povezava odvisnosti pa pomeni tiho zastarelo vrednost v odpremljenem poročilu, kar je veliko hujša napaka. Če se vaš model opira na imena v dosegu delovnega zvezka, spremljevalni članek o definiranih imenih in formulah med listi opisuje, kako se razrešujejo enodelna imena — ta v grafu sodelujejo normalno
Praktična navodila sledijo neposredno. Pomembne poti velikega modela ohranite na običajnih referencah celic in območij, kjer lahko graf opravi svoje delo, in omejite OFFSET ter INDIRECT na redka mesta, ki resnično potrebujejo dinamično naslavljanje. Model s tisoč volatilnimi formulami ponovno zažene teh tisoč formul pri vsakem prehodu, ne glede na to, kako majhno je bilo urejanje — natanko takšno obnašanje, kot ga uporabniki Excela poznajo iz delovnih zvezkov, ki "ponovno preračunavajo ob vsakem pritisku tipke"
Kako HotXLS poroča o krožnih referencah?
Metoda TXLSXWorkbook.Recalculate vrne lxOk pri čistem prehodu in lxErrorRef, ko zazna cikel referenc. Člani cikla se prepoznajo med topološkim razvrščanjem — to so vozlišča, ki jih Kahnovemu algoritmu nikoli ne uspe sprostiti — in se preskočijo, namesto da bi povzročili neskončno zanko: njihove predpomnjene vrednosti ostanejo takšne, kot so bile, medtem ko se vsaka formula izven cikla še naprej normalno ovrednoti po vrstnem redu. Vaše klicno mesto prejme določeno kodo napake namesto zamrznitve 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;Iskanje celic, ki tvorijo cikel, je naloga za razhroščevanje, sledilnik vrednotenja formul pa je pravo orodje za to: sledite sumljivi formuli in veriga referenc, ki se prepogne sama vase, postane vidna korak za korakom. Cikli v resničnih modelih so skoraj vedno napaka pri avtorstvu — zbirna vrstica, pomotoma vključena v lastno območje SUM —, zato je glasna koda napake ob preračunavanju natanko to, kar želite
Poljske (Array) formule, sledenje umazanosti in kdaj se graf znova zgradi
Poljske formule CSE dobijo eno vozlišče za celoten sidrani pravokotnik in ne enega vozlišča na celico. Korenska formula se ovrednoti enkrat na prehod; dobljena matrika se zapiše neposredno v vsako celico članico, formula, ki se nanaša na katero koli celico znotraj sidranega območja — ne le na zgornje levo sidro —, pa prevzame povezavo odvisnosti od tega korenskega vozlišča. Skalarni rezultati se porazdelijo po pravokotniku, kot to predpisuje Excelova zapuščina semantike polj
Sledenje umazanosti se priklopi na običajne nastavitve lastnosti, zato se v vaši kodi nič ne spremeni. Vpis vrednosti Value v celico obvesti delovni zvezek in označi odvisneže kot umazane; dodelitev nove formule Formula pa je strukturna sprememba, zato označi celoten graf kot zastarel, naslednji Recalculate pa ga pred vrednotenjem znova zgradi. Dodajanje, brisanje ali premikanje listov prav tako razveljavi graf, saj identiteta vozlišča kodira indeks lista. Ko ni aktivnega grafa — v delovnem zvezku, kjer nikoli ne pokličete Recalculate —, ti priklopi stanejo le en preprost preizkus nil na dodelitev, tako da običajne obremenitve branja in pisanja ostanejo nespremenjene
Ena meja, ki jo je treba odkrito povedati: graf sledi odvisnostim med celicami, zato se uporabniško definirana funkcija, registrirana prek OnUserFunction, ponovno ovrednoti, ko se spremenijo celice, ki napajajo njene argumente, kot vsaka druga formula. Če pogon razširjate na ta način, članek o prilagojenih funkcijah v pogonu formul HotXLS opisuje pogodbo o povratnem klicu in način prejemanja vrednosti argumentov
Inkrementalno ponovno preračunavanje je del standardnega pogona XLSX v komponenti HotXLS Delphi Excel Component, skupaj s kalkulatorjem formul, definiranimi imeni in uvozno/izvoznim cevovodom, ki ga pospešuje. Če vaša aplikacija v Delphiju ali C++Builderju vzdržuje žive modele — cenike, konsolidacijske delovne zvezke, kaskadna poročila —, metoda Recalculate predstavlja razliko med preračunavanjem celotnega delovnega zvezka in preračunavanjem posameznega urejanja