„HotXLS“ – vietinė Delphi ir C++Builder Excel biblioteka – atlieka prieaugio formulių perskaičiavimą per TXLSXWorkbook.Recalculate. Pirmasis iškvietimas sukuria formulių priklausomybės grafiką ir įvertina kiekvieną formulės langelį; kiekvienas vėlesnis iškvietimas iš naujo įvertina tik tuos langelius, kuriuos paveikė verčių įrašai po paskutinio žingsnio, topologine tvarka, vienu praėjimu, kurio kaina yra proporcinga pasikeitusių langelių skaičiui, o ne darbo knygos dydžiui
Šis vienas dizaino sprendimas yra skirtumas tarp finansinio modelio, kuris reaguoja į redaguojamą prielaidą per kelias milisekundes, ir to, kuris stringa kelioms sekundėms. Jei generuojate ataskaitas, kuriose keli įvesties langeliai maitina tūkstančius vėlesnių formulių, likusioje straipsnio dalyje paaiškinama, ką daro šis grafikas, kurios funkcijos atsisako prieaugio ir kaip pranešama apie žiedines nuorodas (circular references), užuot ciklui sukusis amžinai
Kodėl pakeitus vieną langelį perskaičiuojama šimtas tūkstančių formulių?
Paprastas formulių variklis neprisimena, kas nuo ko priklauso, todėl vienintelis jo saugus veiksmas po bet kokio redagavimo yra įvertinti viską iš naujo. Dar blogiau, klasikinė rekursinė strategija – kai formulė A nurodo formulę B, įvertinti B vietoje – besąlygiškai perskaičiuoja nurodytus langelius, ignoruodama bet kokią talpykloje saugomą vertę. Grandinė iš n formulių, kurių kiekviena nurodo ankstesnę, reikalauja O(n²) perskaičiavimų per pilną praėjimą, o žiedinė nuoroda nustumia rekursiją nuo skardžio. Kiekvienas lentelių kūrėjas, kuris integravo pakopinį modelį į rekursinį vertintuvą, matė abu šiuos gedimo režimus
Pati „Excel“ tai išsprendė prieš kelis dešimtmečius su savo skaičiavimo grandine: formulių langelių tvarka palaikoma taip, kad redagavimas pažymėtų nedidelį langelių rinkinį kaip nešvarų, o variklis eitų tik per paveiktą grandinės galą. „HotXLS“ taiko tą pačią idėją kaip aiškų priklausomybės grafiką, sukurtą vieną kartą iš sukompiliuotų formulių medžių ir pakartotinai naudojamą perskaičiavimo žingsniuose. Esmė yra ne išradingumas, o tai, kad perskaičiavimo kaina turėtų priklausyti nuo jūsų redagavimo dydžio, o ne nuo darbo knygos dydžio
Kaip priklausomybės grafikas paverčia redagavimą vienu praėjimu
„HotXLS“ priklausomybės grafikas suteikia kiekvienam formulės langeliui po vieną mazgą, o briaunos eina nuo ankstesnio langelio prie priklausomo. Kai jūsų kodas įrašo langelio reikšmę, darbo knyga pažymi langelį kaip nešvarų; kai paleidžiamas Recalculate, nešvarumas plinta briaunomis į kiekvieną tolesnę formulę, o nešvarus subgrafikas įvertinamas tiksliai vieną kartą topologine tvarka, naudojant Kahno algoritmą. Kadangi formulė niekada neaplankoma prieš jos precedentus, kiekvienam mazgui reikia vieno įvertinimo – tai ir daro praėjimą O(dirty)
Topologinė tvarka taip pat išsprendžia rekursijos problemą iš pagrindų. Perskaičiavimo metu variklis persijungia į tam skirtą režimą, kuriame bet kokia nuoroda į kitą formulės langelį nuskaito to langelio talpykloje saugomą vertę tiesiogiai, užuot jį perskaičiuojanti – tvarka garantuoja, kad talpykla jau yra šviežia. Tas pats mechanizmas reiškia, kad nuorodų ciklas negali sukelti neribotos rekursijos: niekas praėjimo metu vėl neįeina į kaimyninio langelio vertintuvą
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;Kiekvienas rezultatas patenka į langelio talpykloje saugomą Value, todėl po to, kai Recalculate grąžina reikšmę, nuskaitote išvestis taip pat, kaip nuskaitote bet kurį kitą langelį. Ataskaitų generavimo cikle šablonas yra tiksliai toks pat kaip ir aukščiau pateiktame kode: vieną kartą įkelkite arba sukurkite modelį, o tada pakaitomis rašykite kelis įvesties langelius ir iškvieskite Recalculate, mokėdami tik už tas formules, kurios iš tikrųjų priuolė nuo to, kas pasikeitė
Kurios „Excel“ funkcijos priverčia perskaičiuoti kiekvieno praėjimo metu?
„HotXLS“ traktuoja NOW, TODAY, RAND, OFFSET ir INDIRECT kaip lakiąsias (volatile): bet kuri formulė, turinti vieną iš jų, yra perskaičiuojama kiekvieno Recalculate praėjimo metu, nepriklausomai nuo to, ar kas nors aukščiau pasikeitė. Pirmosios trys yra lakiosios dėl tos pačios priežasties kaip ir „Excel“ programoje – jų rezultatas priklauso nuo vertinimo momento, o ne nuo kitų langelių. OFFSET ir INDIRECT yra lakiosios dėl subtilesnės priežasties: langeliai, kuriuos jos nuskaito, apskaičiuojami vykdymo metu, todėl grafikas negali statiškai žinoti, kurias briaunas jiems nubrėžti
Ta pati konservatyvi taisyklė taikoma ir nuorodoms, kurių grafikų kūrėjas negali priskirti vienam stačiakampiui. Formulė, kuri eina per kelių sričių įvardytą diapazoną, arba ta, kuri nurodo išorinę darbo knygą, taip pat paverčiama lakiąja ir perskaičiuojama kiekvieno praėjimo metu. Ši politika yra tyčinė: papildomas įvertinimas kainuoja šiek tiek laiko, tačiau trūkstama priklausomybės briauna reiškia tyliai pasenusią vertę pateiktoje ataskaitoje, o tai yra daug blogesnė klaida. Jei jūsų modelis remiasi darbo knygos apimties pavadinimais, susijęs straipsnis apie apibrėžtus pavadinimus ir kryžmines formules aprašo, kaip išsprendžiami vienos srities pavadinimai – jie grafike dalyvauja įprastai
Praktinės gairės kyla tiesiogiai. Didelio modelio karštuosius kelius laikykite paprastose langelių ir diapazonų nuorodose, kur grafikas gali atlikti savo darbą, ir izoliuokite OFFSET bei INDIRECT tik tose keliose vietose, kurioms tikrai reikia dinaminio adresavimo. Modelis su tūkstančiu lakiųjų formulių iš naujo paleidžia tą tūkstantį per kiekvieną praėjimą, nesvarbu, koks mažas buvo redagavimas – būtent toks elgesys, kurį „Excel“ vartotojai žino iš darbo knygų, kurios „perskaičiuoja su kiekvienu klavišo paspaudimu“
Kaip HotXLS praneša apie žiedines nuorodas?
TXLSXWorkbook.Recalculate grąžina lxOk sėkmingo praėjimo atveju ir lxErrorRef, kai aptinka nuorodų ciklą. Ciklo nariai identifikuojami topologinio rūšiavimo metu – tai yra mazgai, kurių Kahno algoritmas niekada negali paleisti – ir jie yra praleidžiami, o ne sukami cikle: jų talpykloje išsaugotos vertės lieka tokios, kokios buvo, o kiekviena formulė už ciklo ribų vis tiek vertinama įprasta tvarka. Jūsų iškvietimo vieta gauna aiškų klaidos kodą, o ne pakibimą
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;Nustatyti, kurie langeliai sudaro ciklą, yra derinimo darbas, o formulių vertinimo sekiklis yra tinkamas įrankis tam: atsekite įtartiną formulę ir nuorodų grandinė, kuri grįžta į save, taps matoma žingsnis po žingsnio. Ciklai tikruose modeliuose beveik visada yra autoriaus klaida – suvestinės eilutė netyčia įtraukta į savo SUM diapazoną – todėl aiškus klaidos kodas perskaičiavimo metu yra būtent tai, ko norite
Masyvo formulės, nešvarumų sekimas ir kada grafikas persistato
CSE masyvo formulės gauna vieną mazgą visam prikabintam stačiakampiui, o ne po vieną mazgą kiekvienam langeliui. Šakninė formulė vertinama vieną kartą per praėjimą; gauta matrica įrašoma tiesiogiai į kiekvieną nario langelį, o formulė, kuri nurodo bet kurį langelį prikabintame diapazone – ne tik viršutinį kairįjį inkaro tašką – pasiima priklausomybės briauną iš to šakninio mazgo. Skaliariniai rezultatai transliuojami stačiakampyje taip, kaip reikalauja senoji „Excel“ masyvų semantika
Nešvarumų sekimas kabo ant įprastų savybių nustatymo funkcijų (setters), todėl jūsų kode niekas nesikeičia. Langelio Value įrašymas praneša darbo knygai ir pažymi priklausomus langelius kaip nešvarius; naujos Formula priskyrimas yra struktūrinis pakeitimas, zodžiu, jis pažymi visą grafiką kaip pasenusį, o kitas Recalculate jį perstato prieš vertinimą. Lapų pridėjimas, trynimas ar perkėlimas taip pat sugadina grafiką, nes mazgo tapatybė koduoja lapo indeksą. Kai joks grafikas nėra aktyvus – pavyzdžiui, darbo knygoje, kurioje niekada nekviečiate Recalculate – šie kabliukai (hooks) kainuoja vienintelį nil patikrinimą vienam priskyrimui, todėl paprastiems skaitymo-rašymo darbams įtakos neturi
Viena riba, kurią verta įvardyti sąžiningai: grafikas seka priklausomybes tarp langelių, todėl vartotojo apibrėžta funkcija, užregistruota per OnUserFunction, yra perskaičiuojama, kai pasikeičia jos argumentus maitinantys langeliai, kaip ir bet kuri kita formulė. Jei plečiate variklį tokiu būdu, straipsnis apie pasirinktines funkcijas HotXLS formulių variklyje aprašo atgalinio iškvietimo (callback) sutartį ir kaip gaunamos argumentų vertės
Prieaugio perskaičiavimas yra standartinio XLSX variklio dalis „HotXLS Delphi Excel Component“ pakete kartu su formulių skaičiuotuvu, apibrėžtais pavadinimais ir importo/eksporto srautu, kurį jis pagreitina. Jei jūsų Delphi arba C++Builder programa palaiko gyvus modelius – kainų lapus, konsolidavimo darbo knygas, ataskaitų kaskadas – Recalculate yra skirtumas tarp darbo knygos perskaičiavimo ir redagavimo perskaičiavimo