Een kolom van 10.000 formules, waarvan elke de waarde in de cel erboven laat aangroeien, is ongeveer het goedkoopste model dat een spreadsheet kan bevatten. In een evaluator die een gerefereerde formule ter plekke herberekent telkens wanneer hij er één tegenkomt, komen de totalen nog steeds goed uit, maar de rekenrekening niet: de onderste cel sleept 9.999 voorouders door de evaluatie, die erboven 9.998, en één volledige pas telt op tot circa 50 miljoen evaluaties voor 10.000 vermenigvuldigingen. Verandert u één groeipercentage bovenaan, dan moet die evaluator de hele rekening opnieuw betalen, omdat hij geen administratie bijhoudt van welke cel welke leest
Waarom één bewerkte cel een hele-werkmap-pas kan kosten
Twee shortcuts leveren dat gedrag op, en de meeste zelfgebakken formule-engines pakken er één van. De recursieve shortcut evalueert elke gerefereerde formule vers en negeert welke waarde er al gecached is, wat een keten in kwadratisch werk verandert en een circulaire verwijzing in een stack overflow. De volledige-pas-shortcut is veiliger: evalueer elke formule in een of andere vaste volgorde, zo vaak als nodig is tot er niets meer verandert. Hij is correct, en zijn kosten volgen de grootte van de werkmap in plaats van de grootte van de bewerking
Excel vermijdt beide met zijn berekeningsketen, een ordening van formulecellen waarmee een bewerking een klein stel cellen vlagt en alleen de getroffen staart afloopt. De XLSX-engine in HotXLS gebruikt hetzelfde idee in de vorm van een expliciete afhankelijkheidsgrafiek achter TXLSXWorkbook.Recalculate. De eerste aanroep bouwt de grafiek op uit elke formule op elk blad en evalueert ze allemaal. Elke latere aanroep evalueert alleen de formules stroomafwaarts van cellen waarvan de waarden sinds de vorige pas zijn veranderd, plus een korte lijst formules die elke keer moeten draaien
Hoe Recalculate een bewerking in één geordende pas verandert
Elke formulecel wordt één knooppunt, en er loopt een edge van elke formulecel die een formule leest naar de formule die haar leest. Een verwijzing als SUM(Model!B2:B500) levert een edge op van elke formulecel binnen die rechthoek, en een 3D-verwijzing over een reeks bladen wordt tegen elk blad in de reeks gematcht. Gewone constanten zijn helemaal geen knooppunten; ze tellen alleen als de plek waar een bewerking begint
Value op een cel schrijven doet op het moment van schrijven bijna niets. De werkmap zet het adres van de cel in de wachtrij, en dat is het. Zodra Recalculate start, wordt elke formule waarvan de verwijzingen een wachtrij-adres bestrijken dirty gemarkeerd, en de markering loopt langs de edges naar alle afhankelijken van die formule, en daarna naar die van hen. Een werkmap waarop Recalculate nooit is aangeroepen houdt geen grafiek bij, en diens schrijfkosten zijn één nil-check, dus code die alleen waarden leest en schrijft betaalt niets voor de feature
Het dirty deel van de grafiek wordt daarna gesorteerd met Kahns algoritme. Het detail dat de pas goedkoop maakt zit in hoe in-degrees worden geteld: alleen dirty voorgangers tellen mee. Een schone voorganger heeft al een geldige gecachte waarde staan, dus een formule wacht nooit op haar. Tijdens de pas leest een formule die naar een andere formulecel verwijst de gecachte waarde van die cel in plaats van haar opnieuw te evalueren; de sortering garandeert dat de cache al vers is. Elke dirty formule wordt exact één keer geëvalueerd
De code hieronder bouwt de keten uit de openingsalinea en bewerkt zijn enige invoer. Bladindexen in HotXLS beginnen bij 1, dus Inputs is blad 1 en Model is blad 2; rijen en kolommen in Cells zijn ook 1-based
uses
SysUtils, lxCalc, lxHandleX; // lxOk wordt gedeclareerd in lxCalc
var
Book: TXLSXWorkbook;
Inputs, Model: TXLSXWorksheet;
Row: Integer;
Last: Variant;
begin
Book := TXLSXWorkbook.Create;
try
Inputs := Book.Sheets.Add('Inputs'); // Sheets[1]
Model := Book.Sheets.Add('Model'); // Sheets[2]
Inputs.Cells[2, 2].Value := 0.05; // groeiaanname in Inputs!B2
Model.Cells[2, 2].Formula := 'Inputs!B2*1000';
for Row := 3 to 10000 do
Model.Cells[Row, 2].Formula :=
'B' + IntToStr(Row - 1) + '*(1+Inputs!$B$2)';
if Book.Recalculate <> lxOk then // bouwt de grafiek, evalueert 9.999 formules
Exit;
Inputs.Cells[2, 2].Value := 0.07; // zet één adres in de wachtrij
if Book.Recalculate = lxOk then // formules die door Inputs!B2 worden gevoerd draaien opnieuw (hier: alle 9.999)
Last := Model.Cells[10000, 2].Value; // gecacht resultaat, te lezen als elke waarde
// Eenmalige lezing: RecalcCell draait Recalculate en geeft dan de cel terug
// (blad 2 = Model, rij 10000, kolom 2)
Book.RecalcCell(2, 10000, 2, Last);
finally
Book.Free;
end;
end;
In dit specifieke model leest elke formule Inputs!$B$2, dus de tweede pas raakt nog steeds alle 9.999; de grafiek kan een bewerking die echt alles raakt niet krimpen. De besparing verschijnt in modellen met veel invoer, waarin een scenario-lus drie of vier cellen schrijft en de rest van de werkmap zijn gecachte waarden houdt
Waardeschrijvingen zijn goedkoop, formuleschrijvingen herbouwen de grafiek
Alleen waardewijzigingen blijven incrementeel. Alles wat de verzameling knooppunten of edges kan veranderen, markeert de hele grafiek als verouderd, en de volgende Recalculate herbouwt haar vanaf nul en evalueert elke formule. Dat omvat het toewijzen van Formula, het leegmaken van een formulecel door er Null of een lege waarde naartoe te schrijven, bladen toevoegen, verplaatsen of verwijderen, rijen en kolommen invoegen of verwijderen, gedefinieerde namen bewerken, en what-if-datatabellen veranderen. Een gebruikersfunctie registreren of afmelden, of OnUserFunction toewijzen, houdt de grafiek maar markeert elk knooppunt dirty
De praktische consequentie voor rapportgeneratoren is een lusvorm. Zet de formules één keer neer, roep Recalculate aan en raak daarna alleen nog waarden aan. Een lus die per klant een formulesjabloon herschrijft en dan herrekent, krijgt bij elke iteratie een volledige herbouw, en dat is precies de kosten die de grafiek moet vermijden. Is een sjabloonwijziging echt nodig, bundel dan alle formulebewerkingen vóór één Recalculate, zodat de herbouw één keer plaatsvindt. De scenario-lus zelf schrijft daarna alleen waarden
// Model eenmaal gebouwd; Recalculate al aangeroepen
for I := 0 to High(Scenarios) do
begin
Inputs.Cells[2, 2].Value := Scenarios[I].Growth; // alleen waardeschrijvingen
Inputs.Cells[3, 2].Value := Scenarios[I].Discount;
if Book.RecalcNeeded then // True wanneer cellen in de wachtrij staan of dirty zijn
if Book.Recalculate <> lxOk then
Break;
Results[I] := Model.Cells[10000, 2].Value;
end;
// Laat SaveAs alleen herberekenen als er nog iets openstaat
Book.RecalcBeforeSave := xrmIfDirty;
Book.SaveAs('scenarios.xlsx');
RecalcBeforeSave staat standaard op xrmManual, wat tijdens een opslag nooit herrekent. xrmIfDirty is de instelling voor gegenereerde bestanden: hij rekent alleen opnieuw wanneer de grafiek verouderd is of dirty formules heeft, dus een werkmap die in de lus al is herrekend, wordt ongewijzigd weggeschreven, en een werkmap met een vergeten bewerking wordt niet opgeslagen met stale gecachte waarden
Welke Excel-functies forceren herberekening bij elke pas?
Formules die NOW, TODAY, RAND, RANDBETWEEN, RANDARRAY, OFFSET, INDIRECT, CELL, INFO of GETPIVOTDATA aanroepen zijn vluchtig (volatile) in HotXLS: ze worden aan het begin van elke pas dirty gemarkeerd, of er nu stroomopwaarts iets is veranderd of niet, en al hun afhankelijken ook. De klok- en willekeurigsfuncties zijn om de voor de hand liggende reden vluchtig. OFFSET en INDIRECT zijn vluchtig omdat de cellen die zij lezen tijdens de run uit argumentwaarden worden berekend, en geen statische wandeling door de formule kan zeggen welke edges er getekend moeten worden
Dezelfde regel geldt voor verwijzingen die de graafbouwer niet aan een concrete rechthoek kan vastpinnen, voor een gedefinieerde naam die zichzelf via andere namen raadpleegt, voor dynamische-matrix-spill-verwijzingen, en voor what-if-datatabellen. Gedefinieerde namen zijn in het algemeen geen probleem: de bouwer vouwt een naam uit naar de bereiken waarnaar hij verwijst, dus formules die via werkmap- of bladscope-namen lopen krijgen echte edges. Het artikel over gedefinieerde namen en cross-sheet-formules behandelt hoe die namen uitpakken
De conservatieve richting is hier de juiste. Een onnodige evaluatie kost wat tijd; een ontbrekende edge betekent een stale getal in een rapport dat er af ziet. Houd OFFSET en INDIRECT van de hot paths van grote modellen af en beperk ze tot de paar cellen die echt berekende adressen nodig hebben, want een vluchtige cel dicht bij de top van een keten maakt in de praktijk de hele keten vluchtig
Functies die via OnUserFunction of RegisterUserFunction worden aangeleverd zitten aan de andere kant. Ze zijn niet vluchtig, dus een handler wordt alleen opnieuw aangeroepen wanneer de cellen die haar argumenten voeden veranderen, net als elke ingebouwde functie. Een handler die een wisselkoers uit een database leest, blijft daarom over de passen heen zijn laatste antwoord teruggeven. Schrijf de koers in plaats daarvan in een invoercel en geef die cel als argument door; de grafiek ziet dan de afhankelijkheid, en de formule draait precies opnieuw wanneer de koers verandert. Het artikel over aangepaste functies in de HotXLS-formule-engine legt uit hoe handler-argumenten binnenkomen
Circulaire verwijzingen: lxErrorRef of iteratieve berekening
Met de standaard Iterate = False geeft TXLSXWorkbook.Recalculate lxErrorRef terug wanneer de sortering een cyclus vindt. Cycelleden zijn de dirty knooppunten die Kahns algoritme nooit kan vrijgeven, en elke dirty formule stroomafwaarts van hen is geblokkeerd om dezelfde reden. Die formules houden hun vorige gecachte waarden, terwijl al het andere in de dirty set op volgorde wordt geëvalueerd. Een mislukte pas markeert de grafiek ook als verouderd, dus de aanroep erna herbouwt de grafiek en evalueert elke formule nog een keer
case Book.Recalculate of
lxOk:
Book.SaveAs(OutputFile);
lxErrorRef:
// de cyclus en haar stroomafwaartse formules houden nog hun oude caches;
// LogWarning is uw eigen routine
LogWarning('Circular reference in model, report not written');
else
LogWarning('Recalculation did not complete');
end;
Iterate := True zetten schakelt dezelfde iteratieve berekening in die Excel aanbiedt. De engine evalueert de cyclus en haar geblokkeerde afhankelijken herhaaldelijk, tot IterateCount passen (standaard 100), totdat geen waarde meer met IterateDelta (standaard 0,001) of meer beweegt. Lege cycelleden starten vanaf 0. Het bereiken van de paslimiet houdt de laatste resultaten aan en geeft nog steeds lxOk terug, en daarom hoort iteratie uit te staan voor gegenereerde rapporten: cycli in echte rapportmodellen zijn bijna altijd fouten, zoals een totalenrij die in zijn eigen SUM-bereik terechtkomt, en van zulke gevallen wilt u een duidelijke foutcode. Zet haar alleen aan voor modellen die rond een bedoelde lus zijn gebouwd, zoals rente die over een gemiddeld saldo wordt berekend inclusief de rente zelf
Eén geval dat er circulair uitziet, wordt niet als cyclus gemeld. Het opzoekmatrix-argument van VLOOKUP, HLOOKUP, LOOKUP, MATCH, XLOOKUP en XMATCH maakt de formule nog steeds dirty wanneer een cel in dat bereik verandert, maar het telt niet mee voor cyclisdetectie, dus VLOOKUP(A1,B:B,1) geschreven in kolom B evalueert normaal. Voor echte cycli toont de formule-evaluatietracer de verwijzingsketen die in zichzelf terugvouwt, één stap per keer
Matrixformules en meerdradige passen
Een legacy CSE-matrixformule is één knooppunt voor zijn hele verankerde rechthoek. De wortelformule wordt één keer per pas geëvalueerd, haar matrix wordt in elke ledencel geschreven (een scalair resultaat wordt over de rechthoek gebroadcast), en een formule die naar een willekeurige cel van de rechthoek verwijst, niet alleen het anker linksboven, krijgt een edge van de wortel. Dynamische-matrixformules worden voorzichtiger behandeld: zij draaien elke pas, en wanneer een spill van grootte verandert, herbouwt Recalculate de grafiek en herhaalt de pas tot de spillvormen zich zetten. Zetten ze zich binnen 32 ronden nooit, dan geeft de aanroep lxErrorCalc terug
RecalcThreads boven 1 evalueert elke topologische laag van onafhankelijke formules over worker threads, met een eigen calculator per thread. Datatabellen draaien nog steeds serieel, en een werkmap die dynamische matrices bevat blijft op het seriële pad. Elke gebruikersfunctie-handler moet thread-safe zijn voordat u deze waarde verhoogt; voor een handler die gedeelde staat aanraakt, is RecalcThreads op de standaard 0 houden de veiligere keuze
De afhankelijkheidsgrafiek, de volatile-afhandeling en de iteratieve berekening maken deel uit van de XLSX-engine in de HotXLS Delphi Excel Component, die ook de formulecalculator, gedefinieerde namen en de lees- en schrijfpijplijn eromheen dekt. Voor Delphi- en C++Builder-applicaties die prijslijsten of consolidatiewerkmappen openhouden en de hele dag nieuwe invoer voeren, volgt het werk per bewerking daarmee de cellen die de bewerking daadwerkelijk bereikt