Nativní knihovna Excelu pro Delphi a C++Builder, HotXLS, provádí inkrementální přepočet vzorců prostřednictvím metody TXLSXWorkbook.Recalculate. První volání sestaví graf závislostí vzorců a vyhodnotí každou buňku se vzorcem; každé další volání přepočítává v topologickém uspořádání pouze buňky ovlivněné zápisem hodnot od posledního průchodu, a to v jediném cyklu, jehož náročnost odpovídá počtu změněných buněk a nikoli velikosti celého sešitu
Toto jediné rozhodnutí v návrhu představuje rozdíl mezi finančním modelem, který na změněný předpoklad reaguje v milisekundách, a modelem, který zamrzne na celé sekundy. Pokud generujete reporty, ve kterých hrstka vstupních buněk plní tisíce následných vzorců, zbytek tohoto článku vám vysvětlí, co tento graf dělá, které funkce se z inkrementálního režimu vymykají a jak se hlásí cyklické odkazy, místo aby docházelo k nekonečnému smyčkování
Proč změna jedné buňky vyžaduje přepočet sta tisíc vzorců?
Jednoduché jádro vzorců si nepamatuje, kdo na kom závisí, takže jeho jediným bezpečným krokem po jakékoli úpravě je vyhodnotit vše znovu. Co hůř, klasická rekurzivní strategie — kdy vzorec A odkazuje na vzorec B, takže se B vyhodnotí na místě — přepočítává odkazované buňky bezpodmínečně a ignoruje jakoukoli uloženou hodnotu v mezipaměti. Řetězec n vzorců, z nichž každý odkazuje na předchozí, vyžaduje O(n²) vyhodnocení na jeden plný průchod a cyklický odkaz pošle rekurzi do nekonečné smyčky. Každý vývojář tabulkových procesorů, který zapojil kaskádový model do rekurzivního vyhodnocovače, byl svědkem obou těchto chybových stavů
Samotný Excel to vyřešil již před desítkami let pomocí svého výpočetního řetězce (calculation chain): udržuje uspořádání buněk se vzorci tak, aby úprava označila malou sadu buněk jako změněné (dirty) a jádro pak procházelo pouze dotčený konec řetězce. HotXLS uplatňuje stejnou myšlenku v podobě explicitního grafu závislostí, který se sestaví jednou z kompilovaných stromů vzorců a znovu se používá při dalších přepočtech. Nejde o samoúčelnou složitost; cílem je, aby náklady na přepočet odpovídaly rozsahu vaší úpravy, nikoli velikosti celého sešitu
Jak graf závislostí mění úpravu v jediný průchod
Graf závislostí HotXLS přiděluje každé buňce se vzorcem jeden uzel, přičemž hrany vedou od předchůdce k následníkovi. Když váš kód zapíše hodnotu buňky, sešit ji zaznamená jako změněnou (dirty); při spuštění metody Recalculate se tato změna šíří po hranách ke každému následnému vzorci a změněný podgraf se vyhodne přesně jednou v topologickém uspořádání pomocí Kahnova algoritmu. Protože se vzorec nikdy nevyhodnocuje dříve než jeho předchůdci, vyžaduje každý uzel jediné vyhodnocení — díky tomu je průchod v čase O(dirty)
Topologické uspořádání také řeší problém rekurze přímo u kořene. Během průchodu přepočtem se jádro přepne do vyhrazeného režimu, ve kterém jakýkoli odkaz na jinou buňku se vzorcem čte přímo hodnotu této buňky uloženou v mezipaměti, místo aby ji znovu vyhodnocovalo — uspořádání zaručuje, že mezipaměť je již aktuální. Stejný mechanismus znamená, že cyklus odkazů nemůže vyvolat neomezenou rekurzi: nic uvnitř průchodu nikdy znovu nevstupuje do vyhodnocovače pro sousední buňku
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ždý výsledek se uloží do hodnoty Value buňky v mezipaměti, takže po dokončení metody Recalculate čtete výstupy stejně jako u jakékoli jiné buňky. V cyklu generování reportů odpovídá vzorec chování přesně výše uvedenému kódu: jednou načíst nebo sestavit model a poté střídavě zapisovat do několika vstupních buněk a volat Recalculate, přičemž platíte časově pouze za vzorce, které skutečně závisí na tom, co se změnilo
Které funkce Excelu vynucují přepočet při každém průchodu?
HotXLS považuje funkce NOW, TODAY, RAND, OFFSET a INDIRECT za volatilní: jakýkoli vzorec obsahující jednu z nich se přepočítává při každém spuštění Recalculate, bez ohledu na to, zda se na předcházejících prvcích něco změnilo. První tři jsou volatilní ze stejného důvodu jako v Excelu — jejich výsledek závisí na okamžiku vyhodnocení, nikoli na jiných buňkách. Funkce OFFSET a INDIRECT jsou volatilní z jemnějšího důvodu: buňky, které čtou, se určují za běhu, takže graf nemůže staticky vědět, které hrany pro ně zakreslit
Stejné konzervativní pravidlo se vztahuje na odkazy, které tvůrce grafu nedokáže vymezit do jediného obdélníku. Vzorec procházející pojmenovaným rozsahem s více oblastmi nebo vzorec odkazující na externí sešit je rovněž degradován na volatilní a přepočítává se při každém průchodu. Tento přístup je záměrný: jedno vyhodnocení navíc stojí trochu času, ale chybějící hrana závislosti znamená tiše neaktuální hodnotu v odeslaném reportu, což je mnohem horší chyba. Pokud se váš model opírá o názvy v rozsahu sešitu, doprovodný článek o definovaných názvech a vzorcích napříč listy popisuje, jak se vyhodnocují názvy s jednou oblastí — ty se grafu účastní běžným způsobem
Praktické doporučení z toho plyne přímo. Udržujte kritické cesty velkého modelu na jednoduchých odkazech na buňky a rozsahy, kde graf může dělat svou práci, a funkce OFFSET a INDIRECT izolujte na těch několik míst, která skutečně vyžadují dynamické adresování. Model s tisícem volatilních vzorců spustí těchto tisíc při každém průchodu, bez ohledu na to, jak malá úprava byla — což je přesně chování, které uživatelé Excelu znají ze sešitů přepočítávajících se „při každém stisku klávesy“
Jak HotXLS hlásí cyklické odkazy?
Metoda TXLSXWorkbook.Recalculate vrací hodnotu lxOk při úspěšném průchodu a lxErrorRef při detekci cyklu odkazů. Členové cyklu jsou identifikováni během topologického uspořádání — jsou to uzly, které Kahnův algoritmus nemůže nikdy uvolnit — a jsou přeskočeni, namísto aby se smyčkovali: jejich hodnoty v mezipaměti zůstanou takové, jaké byly, zatímco každý vzorec mimo cyklus se nadále vyhodnocuje běžným způsobem a v daném pořadí. Vaše volající místo tak obdrží jasný chybový kód namísto zamrznutí 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;Hledání buněk tvořících cyklus je úkolem pro ladění a trasovač vyhodnocování vzorců je pro to tím pravým nástrojem: trasujte podezřelý vzorec a řetězec odkazů, který se uzavírá sám do sebe, se vám krok za krokem zviditelní. Cykly v reálných modelech jsou téměř vždy chybou autora — například souhrnný řádek nechtěně zahrnutý do vlastního rozsahu SUM —, takže jasný chybový kód v době přepočtu je přesně to, co chcete
Maticové vzorce, sledování změn a kdy se graf znovu sestavuje
Maticové vzorce CSE získávají jeden uzel pro celý ukotvený obdélník, nikoli jeden uzel pro každou buňku. Kořenový vzorec se vyhodnocuje jednou za průchod; výsledná matice se zapíše přímo do každé členské buňky a vzorec odkazující na jakoukoli buňku uvnitř ukotveného rozsahu — nikoli pouze na levé horní ukotvení — přebírá hranu závislosti od tohoto kořenového uzlu. Skalární výsledky se šíří po celém obdélníku způsobem, který předepisuje starší sémantika matic v Excelu
Sledování změn (dirty tracking) se napojuje na běžné settery vlastností, takže se na vašem kódu nic nemění. Zápis hodnoty Value do buňky upozorní sešit a označí závislé prvky jako změněné; přiřazení nového vzorce Formula představuje strukturální změnu, takže označí celý graf za neplatný a při příštím spuštění Recalculate se graf před vyhodnocením znovu sestaví. Přidávání, mazání nebo přesouvání listů rovněž zneplatňuje graf, protože identita uzlů kóduje index listu. Pokud není aktivní žádný graf — sešit, ve kterém nikdy nevoláte Recalculate —, stojí tyto háčky (hooks) pouze jednu kontrolu hodnoty nil na přiřazení, takže běžné operace čtení a zápisu nejsou ovlivněny
Jedno omezení je třeba uvést na rovinu: graf sleduje závislosti mezi buňkami, takže uživatelsky definovaná funkce registrovaná přes OnUserFunction se vyhodnocuje znovu, když se změní buňky plnící její argumenty, stejně jako jakýkoli jiný vzorec. Pokud takto rozšiřujete jádru, článek o vlastních funkcích v jádru vzorců HotXLS popisuje kontrakt zpětného volání a způsob předávání hodnot argumentů
Inkrementální přepočet je součástí standardního jádra XLSX v produktu HotXLS Delphi Excel Component, společně s kalkulátorem vzorců, definovanými názvy a importním/exportním rozhraním, které zrychluje. Pokud vaše aplikace v Delphi nebo C++Builderu spravuje živé modely — ceníky, konsolidační sešity, kaskády reportů —, představuje metoda Recalculate rozdíl mezi přepočítáváním celého sešitu a přepočítáváním jedné úpravy