HotXLS, natívna delphijská a C++Builder Excel knižnica, vykonáva inkrementálne prepočítavanie vzorcov prostredníctvom metódy TXLSXWorkbook.Recalculate. Prvé volanie zostaví graf závislostí vzorcov a vyhodnotí každú bunku so vzorcom; každé ďalšie volanie prehodnotí iba bunky ovplyvnené zápismi hodnôt od posledného prechodu, v topologickom poradí, a to v jedinom prechode, ktorého náročnosť je úmerná počtu zmenených (dirty) buniek, a nie veľkosti celého zošita
Toto jediné rozhodnutie pri návrhu predstavuje rozdiel medzi finančným modelom, ktorý na zmenu predpokladu zareaguje v milisekundách, a modelom, ktorý na niekoľko sekúnd zamrzne. Ak generujete výstupy, kde hrsť vstupných buniek napája tisíce nadväzujúcich vzorcov, zvyšok tohto článku vysvetľuje, čo graf robí, ktoré funkcie sú vylúčené z inkrementality a ako sa hlásia kruhové odkazy namiesto nekonečného zacyklenia
Prečo zmena jednej bunky prepočíta stotisíc vzorcov?
Naivné jadro na spracovanie vzorcov si nepamätá, kto od koho závisí, takže jeho jediným bezpečným krokom po akejkoľvek úprave je prepočítať všetko znova. Čo je horšie, klasická rekurzívna stratégia — keď vzorec A odkazuje na vzorec B, vyhodnotiť B na mieste — prepočítava odkazované bunky bezpodmienečne a ignoruje akúkoľvek vyrovnávaciu pamäť. Reťazec n vzorcov, kde každý odkazuje na predchádzajúci, stojí O(n²) vyhodnotení na jeden plný prechod a kruhový odkaz pošle rekurziu do záhuby. Každý vývojár tabuľkových procesorov, ktorý zapojil kaskádový model do rekurzívneho vyhodnocovača, videl oba tieto chybové stavy v praxi
Ako graf závislostí mení úpravu na jediný prechod
Graf závislostí v HotXLS priraďuje každej bunke so vzorcom jeden uzol, pričom hrany vedú od predchodcu k nasledovníkovi. Keď váš kód zapíše hodnotu bunky, zošit ju označí ako zmenenú (dirty); keď sa spustí Recalculate, táto zmena sa rozšíri po hranách ku každému nadväzujúcemu vzorcu a zmenený podgraf sa vyhodne presne raz v topologickom poradí pomocou Kahnovho algoritmu. Keďže vzorec sa nikdy nenavštívi pred jeho predchodcami, každý uzol potrebuje jediné vyhodnotenie — to robí prechod závislým len od počtu zmenených uzlov O(dirty)
Topologické usporiadanie tiež rieši problém rekurzie priamo v jej základe. Počas prepočítavania sa jadro prepne do vyhradeného režimu, v ktorom akýkoľvek odkaz na inú bunku so vzorcom číta priamo hodnotu z vyrovnávacej pamäte danej bunky a neprepočítava ju znova — usporiadanie zaručuje, že pamäť je už aktuálna. Rovnaký mechanizmus znamená, že kruhový odkaz nemôže spustiť nekonečnú rekurziu: nič vo vnútri prechodu nikdy nevstupuje znova do vyhodnocovania susednej bunky
Každý výsledok skončí v uloženej hodnote Value bunky, takže po návrate Recalculate čítate výstupy rovnakým spôsobom ako z akejkoľvek inej bunky. V cykle generovania výstupov je vzor presne taký ako v kóde vyššie: načítať alebo vytvoriť model raz, a potom striedať zápis niekoľkých vstupných buniek a volanie Recalculate, čím platíte len za tie vzorce, ktoré reálne závisia od toho, čo sa zmenilo
Ktoré Excel funkcie vynucujú prepočítanie pri každom prechode?
HotXLS považuje funkcie NOW, TODAY, RAND, OFFSET a INDIRECT za volatilné: akýkoľvek vzorec, ktorý obsahuje jednu z nich, sa prepočítava pri každom prechode Recalculate bez ohľadu na to, či sa na predchodcoch niečo zmenilo. Prvé tri sú volatilné z rovnakého dôvodu ako v Exceli — ich výsledok závisí od momentu vyhodnotenia a nie od iných buniek. OFFSET a INDIRECT sú volatilné z jemnejšieho dôvodu: bunky, ktoré čítajú, sa počítajú až za behu, takže graf nemôže staticky vedieť, ktoré hrany závislostí má nakresliť
Rovnaké konzervatívne pravidlo platí aj pre odkazy, ktoré tvorca grafu nedokáže priradiť k jedinému obdĺžniku. Vzorec, ktorý prechádza pomenovaným rozsahom s viacerými oblasťami, alebo odkazuje na externý zošit, sa rovnako degraduje na volatilný a prepočítava sa pri každom prechode. Táto politika je zámerná: prepočítanie navyše stojí trochu času, ale chýbajúca hrana závislosti znamená ticho zastaranú hodnotu v odoslanom reporte, čo je oveľa horšie zlyhanie. Ak sa váš model opiera o pomenované rozsahy na úrovni zošita, sprievodný článok o pomenovaných rozsahoch a vzorcoch naprieč hárkami popisuje, ako sa vyhodnocujú rozsahy s jednou oblasťou — tie sa na grafe zúčastňujú normálne
Praktické odporúčanie z toho vyplýva priamo. Udržujte horúce cesty veľkého modelu na obyčajných odkazoch na bunky a rozsahy, kde graf môže robiť svoju prácu, a obmedzte OFFSET a INDIRECT na tých niekoľko miest, ktoré skutočne potrebujú dynamické adresovanie. Model s tisíckou volatilných vzorcov spustí týchto tisíc pri každom prechode bez ohľadu na to, aká malá bola úprava — presne to správanie, ktoré používatelia Excelu poznajú zo zošitov s nastavením „prepočítať pri každom stlačení klávesu“
Ako HotXLS hlási kruhové odkazy?
Metóda TXLSXWorkbook.Recalculate vracia lxOk pri čistom prechode a lxErrorRef, ak zistí kruhový odkaz. Členovia cyklu sú identifikovaní počas topologického triedenia — sú to uzly, ktoré Kahnov algoritmus nemôže nikdy uvoľniť — a sú preskočení namiesto cyklenia: ich uložené hodnoty zostávajú také, aké boli, zatiaľ čo každý vzorec mimo cyklu sa naďalej vyhodnocuje normálne v poradí. Miesto volania získa jasný chybový kód namiesto zamrznutia
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;Zistenie, ktoré bunky tvoria cyklus, je prácou pre ladenie, a trasovač vyhodnocovania vzorcov je pre to tým správnym nástrojom: trasujte podozrivý vzorec a reťazec odkazov, ktorý sa vracia k sebe, sa stane viditeľným krok za krokom. Cykly v reálnych modeloch sú takmer vždy chybou autora — napríklad súhrnný riadok omylom zahrnutý do svojho vlastného rozsahu SUM — so a loud error code at recalculation time is precisely what you want
Maticové vzorce, sledovanie zmien a kedy sa graf prestavuje
Maticové vzorce CSE dostávajú jeden uzol pre celý ukotvený obdĺžnik, nie jeden uzol pre každú bunku. Koreňový vzorec sa vyhodnocuje raz za prechod; výsledná matica sa zapíše priamo do každej členskej bunky a vzorec, ktorý odkazuje na akúkoľvek bunku v ukotvenom rozsahu — nielen na ľavý horný úchyt — preberá hranu závislosti od tohto koreňového uzla. Skalárne výsledky sa šíria po obdĺžniku tak, ako to predpisuje staršia maticová sémantika Excelu
Sledovanie zmien (dirty tracking) sa napája na bežné priraďovanie vlastností, takže na vašom kóde sa nič nemení. Zápis do Value bunky informuje zošit a označí závislé uzly za zmenené; priradenie nového Formula je štrukturálna zmena, takže označí celý graf za zastaraný a nasledujúci prechod Recalculate ho pred vyhodnotením znova vybuduje. Pridanie, odstránenie alebo presun hárkov tiež zneplatňuje graf, keďže identita uzla kóduje index hárka. Ak nie je aktívny žiadny graf — zošit, v ktorom nikdy nevoláte Recalculate — háky stoja len jednu kontrolu nil pri priradení, takže bežná práca s čítaním a zápisom nie je ovplyvnená
Jeden limit, ktorý stojí za to uviesť na rovinu: graf sleduje závislosti medzi bunkami, takže používateľom definovaná funkcia registrovaná cez OnUserFunction sa prepočítava vtedy, keď sa zmenia bunky napájajúce jej argumenty, rovnako ako akýkoľvek iný vzorec. Ak rozširujete jadro týmto spôsobom, článok o vlastných funkciách v jadre na vyhodnocovanie vzorcov HotXLS rozoberá zmluvu spätného volania (callback contract) a spôsob prenosu hodnôt argumentov
Inkrementálne prepočítavanie je súčasťou štandardného jadra XLSX v komponente HotXLS Delphi Excel Component, spolu s kalkulátorom vzorcov, pomenovanými rozsahmi a importnou/exportnou linkou, ktorú urýchľuje. Ak vaša aplikácia v Delphi alebo C++Builder spravuje živé modely — cenové ponuky, konsolidačné zošity, kaskády správ — metóda Recalculate je rozdielom medzi prepočítaním celého zošita a prepočítaním jednej úpravy
Domov · Hľadať · losLab.com