HotXLS, izvorna Delphi i C++Builder Excel knjižnica, izvodi inkrementalno ponovno izračunavanje formula putem poziva TXLSXWorkbook.Recalculate. Prvi poziv gradi grafikon ovisnosti formula i procjenjuje svaku ćeliju s formulom; svaki kasniji poziv ponovno procjenjuje samo one ćelije na koje su utjecala upisivanja vrijednosti od posljednjeg prolaza, u topološkom redoslijedu, u jednom prolazu čiji je trošak proporcionalan broju izmijenjenih (dirty) ćelija, a ne veličini radne knjige
Ta jedna odluka o dizajnu čini razliku između financijskog modela koji na promijenjenu pretpostavku reagira u milisekundama i onoga koji zastajkuje na nekoliko sekundi. Ako generirate izvješća u kojima šačica ulaznih ćelija hrani tisuće nizvodnih formula, ostatak ovog članka objašnjava što grafikon radi, koje su funkcije izuzete iz inkrementalnosti i kako se prijavljuju kružne reference (circular references) umjesto beskonačnog petljanja
Zašto promjena jedne ćelije ponovno izračunava stotinu tisuća formula?
Jednostavan mehanizam za formule nema pamćenje o tome tko o kome ovisi, pa je njegov jedini siguran potez nakon bilo kakvog uređivanja ponovna procjena svega. Što je još gore, klasična rekurzivna strategija — kada formula A referencira formulu B, procijeni B na licu mjesta — ponovno procjenjuje referencirane ćelije bezuvjetno, zanemarujući bilo koju predmemoriranu vrijednost. Lanac od n formula od kojih svaka referencira prethodnu košta O(n²) procjena po punom prolazu, a kružna referenca šalje rekurziju preko ruba litice. Svaki programer proračunskih tablica koji je povezao kaskadni model u rekurzivni evaluator svjedočio je pojavi oba načina neuspjeha
Excel je to riješio prije nekoliko desetljeća sa svojim lancem izračuna: održava se redoslijed ćelija s formulom tako da uređivanje označava mali skup ćelija kao izmijenjene (dirty), a mehanizam prolazi samo kroz pogođeni rep lanca. HotXLS primjenjuje istu ideju kao eksplicitni grafikon ovisnosti, izgrađen jednom iz kompajliranih stabala formula i ponovno korišten u prolazima ponovnog izračuna. Poanta nije u domišljatosti; nego u tome da bi trošak ponovnog izračuna trebao pratiti veličinu vašeg uređivanja, a ne veličinu vaše radne knjige
Kako grafikon ovisnosti pretvara uređivanje u jedan prolaz
Grafikon ovisnosti HotXLS-a daje svakoj ćeliji s formulom jedan čvor, s bridovima koji vode od prethodnika (precedent) do ovisnog (dependent). Kada vaš kod upiše vrijednost ćelije, radna knjiga bilježi ćeliju kao izmijenjenu (dirty); kada se pokrene Recalculate, stanje izmjene propagira se duž bridova do svake nizvodne formule, a izmijenjeni podgrafikon se procjenjuje točno jednom u topološkom redoslijedu pomoću Kahnova algoritma. Budući da formula nikada nije posjećena prije svojih prethodnika, svaki čvor treba jednu procjenu — to je ono što prolaz čini O(dirty)
Topološki redoslijed također rješava problem rekurzije u samom korijenu. Tijekom prolaza ponovnog izračuna, mehanizam prelazi u namjenski način rada u kojem bilo koja referenca na drugu ćeliju s formulom izravno čita predmemoriranu vrijednost te ćelije umjesto da je ponovno procjenjuje — redoslijed jamči da je predmemorija već svježa. Isti mehanizam znači da ciklus referenci ne može pokrenuti neograničenu rekurziju: nothing inside the pass ever re-enters the evaluator for a neighbouring cell
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 slijeće u predmemoriranu vrijed Value ćelije, pa nakon povratka iz Recalculate čitate izlaze na isti način kao što čitate bilo koju drugu ćeliju. U petlji generiranja izvješća uzorak je točno onaj u gornjem kodu: učitajte ili izgradite model jednom, a zatim izmjenjujte upisivanje nekoliko ulaznih ćelija i pozivanje Recalculate, plaćajući samo za formule koje zapravo ovise o onome što se promijenilo
Koje Excel funkcije forsiraju ponovni izračun u svakom prolazu?
HotXLS tretira NOW, TODAY, RAND, OFFSET i INDIRECT kao nestabilne (volatile): svaka formula koja sadrži neku od njih ponovno se procjenjuje u svakom prolazu Recalculate, bez obzira na to je li se išta uzvodno promijenilo. Prve tri su nestabilne iz istog razloga kao i u Excelu — njihov rezultat ovisi o trenutku procjene, a ne o drugim ćelijama. OFFSET i INDIRECT su nestabilne iz suptilnijeg razloga: ćelije koje čitaju izračunavaju se u vremenu izvršavanja (run time), pa grafikon ne može statički znati koje bridove treba nacrtati za njih
Isto konzervativno pravilo proširuje se na reference koje graditelj grafikona ne može prikovati za jedan pravokutnik. Formula koja prolazi kroz imenovani raspon s više područja (multi-area) ili ona koja referencira vanjsku radnu knjigu također se degradira na nestabilnu i ponovno procjenjuje u svakom prolazu. Ta je politika namjerna: dodatna procjena košta malo vremena, ali brid ovisnosti koji nedostaje znači tiho zastarjelu vrijednost u isporučenom izvješću, što je daleko gori neuspjeh. Ako se vaš model oslanja na nazive u opsegu radne knjige, prateći članak o definiranim nazivima i formulama na više listova pokriva kako se razrješavaju nazivi s jednim područjem — oni normalno sudjeluju u grafikonu
Praktične smjernice slijede izravno. Držite brze staze velikog modela na jednostavnim referencama ćelija i raspona gdje grafikon može raditi svoj posao, te stavite u karantenu OFFSET i INDIRECT na onih nekoliko mjesta koja doista trebaju dinamičko adresiranje. Model s tisuću nestabilnih formula ponovno pokreće tih tisuću u svakom prolazu bez obzira na to koliko je uređivanje bilo malo — to je točno ponašanje koje korisnici Excela znaju iz radnih knjiga koje se "ponovno izračunavaju pri svakom pritisku tipke"
Kako HotXLS prijavljuje kružne reference?
TXLSXWorkbook.Recalculate vraća lxOk pri čistom prolazu i lxErrorRef kada otkrije ciklus referenci. Članovi ciklusa identificiraju se tijekom topološkog sortiranja — to su čvorovi koje Kahnov algoritam nikada ne može otpustiti — i oni se preskaču umjesto da uđu u beskonačnu petlju: njihove predmemorirane vrijednosti ostaju kakve god bile, dok se svaka formula izvan ciklusa i dalje normalno procjenjuje redom. Vaše mjesto poziva dobiva određen kod pogreške umjesto zastoja (hang)
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 tvore ciklus je posao uklanjanja pogrešaka (debugging), a tragač procjene formula je pravi alat za to: pratite sumnjivu formulu i lanac referenci koji se preklapa sam sa sobom postaje vidljiv korak po korak. Ciklusi u stvarnim modelima gotovo su uvijek autorska pogreška — redak sa sažetkom slučajno je uključen u vlastiti SUM raspon — pa je jasan kod pogreške u trenutku ponovnog izračuna točno ono što želite
Formule polja, praćenje izmjena i kada se grafikon ponovno gradi
CSE formule polja (array formulas) dobivaju jedan čvor za cijeli usidreni pravokutnik, a ne jedan čvor po ćeliji. Korijenska formula procjenjuje se jednom po prolazu; rezultirajuća matrica upisuje se izravno u svaku ćeliju članicu, a formula koja referencira bilo koju ćeliju unutar usidrenog raspona — ne samo gornji lijevi sidreni čvor — preuzima brid ovisnosti s tog korijenskog čvora. Skalarni rezultati prenose se preko pravokutnika na način na koji to propisuje Excelova naslijeđena semantika polja
Praćenje izmijenjenih ćelija (dirty tracking) priključuje se na uobičajene postavljače svojstava (setters), tako da se ništa u vašem kodu ne mijenja. Upisivanje Value u ćeliju obavještava radnu knjigu i označava ovisne ćelije kao prljave; dodjeljivanje nove formule Formula je strukturna promjena, pa označava cijeli grafikon zastarjelim, a sljedeći Recalculate ga ponovno gradi prije procjene. Budući da identitet čvora kodira indeks lista, dodavanje, brisanje ili premještanje listova također poništava grafikon. Kada grafikon nije aktivan — na primjer, u radnoj knjizi u kojoj nikada ne pozivate Recalculate — ti priključci koštaju samo jednu provjeru na nil po dodjeljivanju, tako da obična radna opterećenja čitanja i pisanja ostaju netaknuta
Jednu granicu vrijedi pošteno reći: grafikon prati ovisnosti između ćelija, pa se korisnički definirana funkcija registrirana putem OnUserFunction ponovno procjenjuje kada se promijene ćelije koje hrane njezine argumente, baš kao i svaka druga formula. Ako na taj način proširujete mehanizam, članak o korisničkim funkcijama u mehanizmu za formule HotXLS-a prolazi kroz ugovor o povratnom pozivu (callback contract) i način na koji vrijednosti argumenata stižu
Inkrementalno ponovno izračunavanje dio je standardnog mehanizma XLSX u komponenti HotXLS Delphi Excel Component, zajedno s kalkulatorom formula, definiranim nazivima i cjevovodom za uvoz/izvoz koji ubrzava. Ako vaša aplikacija u Delphiju ili C++Builderu održava žive modele — tablice cijena, konsolidacijske radne knjige, kaskade izvješća — Recalculate čini razliku između ponovnog izračunavanja cijele radne knjige i ponovnog izračunavanja samo jedne izmjene