A HotXLS, a natív Delphi és C++Builder Excel könyvtár, inkrementális képlet-újraszámítást végez a TXLSXWorkbook.Recalculate metóduson keresztül; az első hívás felépíti a képlet-függőségi grafikont és kiértékeli az összes képletcellát; minden későbbi hívás csak azokat a cellákat értékeli újra topológiai sorrendben, amelyeket az utolsó lépés óta történt értékírások érintettek, egyetlen lépésben, amelynek költsége a dirty (módosult) cellák számával arányos, nem pedig a munkafüzet méretével
Ez a tervezési döntés jelenti a különbséget egy olyan pénzügyi modell között, amely ezredmásodpercek alatt válaszol egy szerkesztett feltételezésre, és egy olyan között, amely másodpercekre lefagy; ha olyan jelentéseket készít, ahol néhány bemeneti cella több ezer későbbi képletet táplál, a cikk további része elmagyarázza, mit csinál a grafikon, mely függvények maradnak ki az inkrementalitásból, és hogyan kapunk hibajelzést a körkörös hivatkozásokról ahelyett, hogy végtelen ciklusba kerülnénk
Miért számol újra százezer képletet egyetlen cella megváltoztatása?
Egy naiv képletmotor nem emlékszik arra, hogy ki kitől függ, így az egyetlen biztonságos lépése bármilyen szerkesztés után az, hogy mindent újra kiértékel; ami még rosszabb, a klasszikus rekurzív stratégia — amikor az A képlet a B képletre hivatkozik, kiértékeli a B-t a helyszínen — feltétel nélkül újraértékeli a hivatkozott cellákat, figyelmen kívül hagyva a gyorsítótárazott értéket; egy n képletből álló lánc, ahol mindegyik az előzőre hivatkozik, teljes lépésenként O(n²) kiértékelésbe kerül, a körkörös hivatkozás pedig a végtelen rekurzióba taszítja a folyamatot; minden olyan táblázatkezelő fejlesztő, aki kaszkádmodellt kötött be egy rekurzív kiértékelőbe, végignézte már mindkét hibaállapotot
Maga az Excel ezt évtizedekkel ezelőtt megoldotta a számítási láncával: a képletcellák olyan rendezésével, amelyet úgy tartanak karban, hogy a szerkesztés csak a cellák egy kis részét jelöli meg dirty-nek, a motor pedig csak a lánc érintett végét járja be; a HotXLS ugyanezt az elvet alkalmazza kifejezett függőségi grafikonként, amelyet egyszer épít fel a lefordított képletfákból, és újra felhasznál az újraszámítási lépések során; a lényeg nem az okosság; hanem az, hogy az újraszámítás költsége a szerkesztés méretét kövesse, ne pedig a munkafüzetét
Hogyan teszi a függőségi grafikon a szerkesztést egyetlen lépéssé?
A HotXLS függőségi grafikon minden képletcellának egy csomópontot ad, az élek pedig a megelőzőtől a függő felé haladnak; amikor a kódja beír egy cellaértéket, a munkafüzet dirty-ként rögzíti a cellát; amikor a Recalculate lefut, a dirty állapot végigterjed az élek mentén minden későbbi képletre, és a dirty részgráf pontosan egyszer értékelődik ki topológiai sorrendben, Kahn algoritmusának felhasználásával; mivel a képletet soha nem látogatja meg a megelőzői előtt, minden csomópontnak egyetlen kiértékelésre van szüksége — ez teszi a lépést O(dirty) bonyolultságúvá
A topológiai sorrend a rekurziós problémát is a gyökerénél oldja meg; az újraszámítási lépés során a motor egy dedikált módba vált, amelyben a másik képletcellára való hivatkozás közvetlenül az adott cella gyorsítótárazott értékét olvassa be ahelyett, hogy újraértékelné azt — a rendezés garantálja, hogy a gyorsítótár már friss; ugyanez a mechanizmus jelenti azt is, hogy a hivatkozási körök nem indíthatnak el végtelen rekurziót: a lépésen belül semmi sem lép be újra a szomszédos cella kiértékelőjébe
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; // növekedési feltételezés
Model.Cells[2, 2].Formula := 'Inputs!B2*1000'; // Az XLSX képletek elejére nem kell '='
Model.Cells[3, 2].Formula := 'B2*(1+Inputs!B2)';
// ... több ezer további sor kaszkádolódik ugyanabból a feltételezésből ...
Book.Recalculate; // első hívás: felépíti a grafikont, teljes kiértékelés
Inputs.Cells[2, 2].Value := 0.07; // egy szerkesztés dirty-nek jelöl meg egy cellát
Book.Recalculate; // második hívás: csak a későbbi lánc fut le
finally
Book.Free;
end;
end;Minden eredmény a cella gyorsítótárazott Value értékébe kerül, így a Recalculate visszatérése után ugyanúgy olvashatja ki a kimeneteket, mint bármely más cellánál; a jelentéskészítési ciklusban a minta pontosan a fenti kód: egyszer töltse be vagy építse fel a modellt, majd váltakozva írjon be néhány bemeneti cellát és hívja meg a Recalculate-ot, csak azokért a képletekért fizetve, amelyek valóban függnek a változástól
Mely Excel függvények kényszerítik ki az újraszámítást minden lépésben?
A HotXLS az NOW, TODAY, RAND, OFFSET és INDIRECT függvényeket illékonynak (volatile) tekinti: minden olyan képlet, amely ezek valamelyikét tartalmazza, újraértékelődik minden Recalculate lépésnél, függetlenül attól, hogy a megelőző adatok megváltoztak-e; az első három ugyanazon okból illékony, mint az Excelben — eredményük a kiértékelés pillanatától függ, nem pedig más celláktól; az OFFSET és az INDIRECT finomabb okból illékony: az általuk olvasott cellák futásidőben számítódnak ki, így a grafikon statikusan nem tudhatja, milyen éleket kell rajzolnia hozzájuk
Ugyanez a konzervatív szabály vonatkozik azokra a hivatkozásokra is, amelyeket a grafikont építő nem tud egyetlen téglalapra leszűkíteni; az a képlet, amely több területből álló névvel ellátott tartományon megy keresztül, vagy amely külső munkafüzetre hivatkozik, szintén illékonnyá válik és minden lépésben újraértékelődik; a politika szándékos: a plusz kiértékelés egy kis időbe kerül, de a hiányzó függőségi él egy csendben elavuló értéket jelent a leszállított jelentésben, ami sokkal rosszabb hiba; ha a modellje munkafüzet-szintű nevekre támaszkodik, a definiált nevekről és a lapok közötti képletekről szóló társcikk bemutatja, hogyan oldódnak fel az egyterületű nevek — ezek normálisan részt vesznek a grafikonban
A gyakorlati útmutatás ebből egyenesen következik; a nagy modell kritikus útvonalait tartsa egyszerű cella- és tartományhivatkozásokon, ahol a grafikon elvégezheti a munkáját, a OFFSET-et és az INDIRECT-et pedig szorítsa vissza arra a néhány helyre, amely valóban dinamikus címzést igényel; egy ezer illékony képletet tartalmazó modell minden lépésben újra lefutja ezt az ezret, függetlenül attól, milyen kicsi volt a szerkesztés — pontosan az a viselkedés, amelyet az Excel felhasználók ismernek a „minden billentyűleütésre újraszámoló” munkafüzetekből
Hogyan jelenti a HotXLS a körkörös hivatkozásokat?
A TXLSXWorkbook.Recalculate tiszta lefutás esetén lxOk, hivatkozási kör észlelésekor pedig lxErrorRef értéket ad vissza; a kör tagjai a topológiai rendezés során azonosíthatók — ezek azok a csomópontok, amelyeket Kahn algoritmusa soha nem tud felszabadítani —, és a rendszer kihagyja őket a végtelen ciklusok elkerülése érdekében: a gyorsítótárazott értékeik megmaradnak annak, amik voltak, míg a körön kívüli képletek továbbra is normálisan, sorrendben értékelődnek ki; a hívás helye határozott hibakódot kap a lefagyás helyett
case Book.Recalculate of
lxOk:
SaveReport(Book);
lxErrorRef:
// hivatkozási kör áll fenn; a kör tagjai megőrizték korábbi
// gyorsítótárazott értékeiket, és a körön kívül minden naprakész
LogWarning('Circular reference detected - review model inputs');
end;Annak megkeresése, hogy mely cellák alkotják a kört, egy hibakeresési munka; a képlet-kiértékelési nyomkövető a megfelelő eszköz ehhez: kövesse nyomon a gyanús képletet, és az önmagába visszahajló hivatkozási lánc lépésről lépésre láthatóvá válik; a valós modellekben lévő körök szinte mindig szerzői hibák — egy összesítő sor véletlenül bekerül a saját SUM tartományába —, így az újraszámításkor egy hangos hibakód pontosan az, amit szeretne
Tömbképletek, dirty követés és a grafikon újjáépítésének ideje
A CSE tömbképletek egyetlen csomópontot kapnak a teljes lehorgonyzott téglalapra, nem pedig cellánként egyet; a gyökérképlet lépésenként egyszer értékelődik ki; a kapott mátrix közvetlenül beíródik minden tagcellába, és az a képlet, amely a lehorgonyzott tartományon belüli bármely cellára hivatkozik — nem csak a bal felső horgonyra —, függőségi élet kap a gyökércsomóponttól; a skaláris eredmények szétterjednek a téglalapon, ahogy az Excel hagyományos tömbszemantikája előírja
A dirty követés az egyszerű tulajdonság-beállítókat figyeli, így a kódján semmi sem változik; a Value beírása a cellába értesíti a munkafüzetet és dirty-nek jelöli a függőket; az új Formula hozzárendelése szerkezeti változás, így az egész grafikont elavultnak jelöli meg, a következő Recalculate pedig újraépíti azt a kiértékelés előtt; a lapok hozzáadása, törlése vagy áthelyezése szintén érvényteleníti a grafikont, mivel a csomópont-identitás kódolja a lapindexet; ha nincs aktív grafikon — olyan munkafüzetnél, amelynél soha nem hívja a Recalculate-ot —, a figyelők hozzárendelésenként egyetlen nil ellenőrzésbe kerülnek, így a sima írási-olvasási terheléseket ez nem érinti
Egy határt érdemes őszintén kimondani: a grafikon a cellák közötti függőségeket követi, így az OnUserFunction-en keresztül regisztrált felhasználó által definiált függvény újraértékelődik, amikor a paramétereit tápláló cellák megváltoznak, mint bármely más képlet; ha ilyen módon bővíti a motort, a HotXLS képletmotorjában használt egyedi függvényekről szóló cikk ismerteti a visszahívási szerződést és a paraméterértékek beérkezését
A javított képlet-újraszámítás a standard XLSX motor része a HotXLS Delphi Excel Component-ben, a képletszámítógéppel, a definiált nevekkel és az általa felgyorsított importálási/exportálási folyamattal együtt; ha Delphi vagy C++Builder alkalmazása élő modelleket — árképzési lapokat, konszolidációs munkafüzeteket, jelentéssorozatokat — tart fenn, a Recalculate a munkafüzet újraszámítása és a szerkesztés újraszámítása közötti különbséget jelenti