HotXLS, de native Delphi- en C++Builder Excel-bibliotheek, voert incrementele formule-herberekening uit via TXLSXWorkbook.Recalculate. De eerste aanroep bouwt een formule-afhankelijkheidsgrafiek en evalueert elke formulecel; elke latere aanroep evalueert alleen de cellen die zijn beïnvloed door waarde-updates (value writes) sinds de laatste werkgang, in topologische volgorde. Dit gebeurt in een enkele sweep waarvan de kosten evenredig zijn met het aantal gewijzigde ('dirty') cellen in plaats van de omvang van de werkmap
Die ene ontwerpbeslissing maakt het verschil tussen een financieel model dat binnen milliseconden reageert op een aangepaste aanname, en een model dat secondenlang vastloopt. Als u rapporten genereert waarin een handvol invoercellen duizenden stroomafwaartse formules voedt, de rest van dit artikel legt uit wat de grafiek doet, welke functies afzien van incrementaliteit en hoe circulaire verwijzingen worden gemeld in plaats van oneindig door te lopen
Waarom herberekenen honderdduizend formules als er één cel verandert?
Een naïeve formule-engine heeft geen geheugen van wie van wie afhankelijk is, dus de enige veilige actie na elke bewerking is om alles opnieuw te evalueren. Erger nog, de klassieke recursieve strategie — wanneer formule A verwijst naar formule B, evalueer B ter plekke — evalueert verwezen cellen onvoorwaardelijk opnieuw, waarbij elke gecachte waarde wordt genegeerd. Een keten van n formules die elk naar de vorige verwijzen, kost O(n²) evaluaties per volledige bewerking, en een circulaire verwijzing leidt tot een oneindige recursie. Elke spreadsheetontwikkelaar die een trapsgewijs model in een recursieve evaluator heeft ingebouwd, heeft beide faalmodi zien optreden
Excel heeft dit decennia geleden zelf opgelost met zijn berekeningsketen (calculation chain): een ordening van formulecellen die zo wordt bijgehouden dat een bewerking een kleine set cellen als dirty markeert en de engine alleen de getroffen staart van de keten doorloopt. HotXLS past hetzelfde idee toe als een expliciete afhankelijkheidsgrafiek, eenmalig gebouwd op basis van de gecompileerde formulebomen en hergebruikt bij herberekeningsslagen. Het gaat hierbij niet om slimheid; het gaat erom dat de herberekeningskosten de omvang van uw bewerking moeten volgen, niet de omvang van uw werkmap
Hoe de afhankelijkheidsgrafiek een bewerking omzet in een enkele werkgang
De afhankelijkheidsgrafiek van HotXLS geeft elke formulecel één knooppunt (node), met verbindingen (edges) die lopen van antecedent naar consequent. Wanneer uw code een celwaarde schrijft, registreert de werkmap de cel als dirty; wanneer Recalculate wordt uitgevoerd, verspreidt de status 'dirty' zich langs de verbindingen naar elke stroomafwaartse formule, en de dirty subgrafiek wordt exact één keer in topologische volgorde geëvalueerd met behulp van het algoritme van Kahn. Omdat een formule nooit wordt bezocht voor zijn precedenten, heeft elk knooppunt een enkele evaluatie nodig — dat is wat de bewerking O(dirty) maakt
Topologische volgorde lost het recursieprobleem ook bij de wortel op. Tijdens een herberekeningsslag schakelt de engine over naar een speciale modus waarin elke verwijzing naar een andere formulecel direct de gecachte waarde van die cel leest in plaats van deze opnieuw te evalueren — de ordening garandeert dat de cache al actueel is. Hetzelfde mechanisme zorgt ervoor dat een verwijzingscyclus geen onbegrensde recursie kan veroorzaken: niets binnen de werkgang start de evaluator opnieuw voor een naburige cel
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;Elk resultaat landt in de gecachte Value van de cel, dus nadat Recalculate klaar is, leest u de uitvoer op dezelfde manier als bij elke andere cel. In een rapportgeneratielus is het patroon exact de code hierboven: laad of bouw het model eenmaal, en wissel vervolgens af tussen het schrijven naar een paar invoercellen en het aanroepen van Recalculate, waarbij u alleen betaalt voor de formules die daadwerkelijk afhankelijk zijn van wat er is gewijzigd
Welke Excel-functies dwingen bij elke werkgang herberekening af?
HotXLS behandelt NOW, TODAY, RAND, OFFSET en INDIRECT als volatiel: elke formule die een van deze functies bevat, wordt bij elke Recalculate-werkgang opnieuw geëvalueerd, of er stroomopwaarts nu iets is gewijzigd of niet. De eerste drie zijn volatiel om dezelfde reden als in Excel — hun resultaat hangt af van het moment van evaluatie, niet van andere cellen. OFFSET en INDIRECT zijn volatiel om een subtielere reden: de cellen die ze lezen worden tijdens runtime berekend, waardoor de grafiek niet statisch kan weten welke verbindingen hij daarvoor moet tekenen
Dezelfde conservatieve regel geldt voor verwijzingen die de grafiekbouwer niet kan vastpinnen op een enkele rechthoek. Een formule die door een benoemd bereik met meerdere gebieden (multi-area) loopt, of een formule die naar een externe werkmap verwijst, wordt eveneens gedegradeerd tot volatiel en bij elke werkgang opnieuw geëvalueerd. Dit beleid is opzettelijk: een extra evaluatie kost een beetje tijd, maar een ontbrekende afhankelijkheidsverbinding betekent een stilletjes verouderde waarde in een verzonden rapport, en dat is een veel ergere fout. Als uw model leunt op werkmap-brede namen, behandelt het bijbehorende artikel over gedefinieerde namen en cross-sheet formules hoe namen met een enkel gebied worden opgelost — die nemen normaal deel aan de grafiek
De praktische richtlijn volgt hier direct uit. Houd kritieke paden van een groot model op gewone cel- en bereiksverwijzingen waar de grafiek zijn werk kan doen, en beperk OFFSET en INDIRECT tot de weinige plaatsen die echt dynamische adressering vereisen. Een model met duizend volatiele formules voert die duizend bij elke werkgang opnieuw uit, hoe klein de bewerking ook was — exact het gedrag dat Excel-gebruikers kennen van werkmappen die "bij elke toetsaanslag herberekenen"
Hoe meldt HotXLS circulaire verwijzingen?
TXLSXWorkbook.Recalculate retourneert lxOk bij een schone werkgang en lxErrorRef wanneer het een verwijzingscyclus detecteert. Leden van de cyclus worden geïndexeerd tijdens de topologische sortering — het zijn de knooppunten die het algoritme van Kahn nooit kan vrijgeven — en ze worden overgeslagen in plaats van dat er doorheen wordt geloopt: hun gecachte waarden blijven zoals ze waren, terwijl elke formule buiten de cyclus nog steeds normaal op volgorde wordt geëvalueerd. Uw aanroeplocatie krijgt een duidelijke foutcode in plaats van een vastloper
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;Uitzoeken welke cellen de cyclus vormen is een taak voor foutopsporing (debugging), en de formule-evaluatietracer is het juiste hulpmiddel hiervoor: traceer de verdachte formule en de verwijzingsketen die in zichzelf terugloopt wordt stap voor stap zichtbaar. Cycli in echte modellen zijn bijna altijd een ontwerpfout — een samenvattingsrij die per ongeluk is opgenomen in het eigen SUM-bereik — dus een duidelijke foutcode bij het herberekenen is precies wat u wilt
Matrixformules (array formulas), dirty-tracking en wanneer de grafiek opnieuw wordt opgebouwd
CSE-matrixformules (array-formules) krijgen één knooppunt voor de gehele verankerde rechthoek, niet één knooppunt per cel. De wortelformule wordt eenmaal per werkgang geëvalueerd; de resulterende matrix wordt rechtstreeks in elke lidcel geschreven, en een formule die naar een cel binnen het verankerde bereik verwijst — niet alleen de anker-cel linksboven — krijgt een afhankelijkheidsverbinding van dat wortelknooppunt. Scalaire resultaten verspreiden zich over de rechthoek zoals de klassieke matrix-semantiek van Excel voorschrijft
Dirty-tracking haakt in op de gewone property-setters, dus er verandert niets aan uw code. Het schrijven van Value in een cel stelt de werkmap op de hoogte en markeert afhankelijke cellen als dirty; het toewijzen van een nieuwe Formula is een structurele wijziging, dus markeert dit de hele grafiek als verouderd (stale) en de volgende Recalculate bouwt deze opnieuw op alvorens te evalueren. Het toevoegen, verwijderen of verplaatsen van tabbladen (sheets) maakt de grafiek eveneens ongeldig, aangezien de identiteit van knooppunten de index van het tabblad codeert. Wanneer er geen grafiek actief is — een werkmap waarop u Recalculate nooit aanroept — kosten de hooks een enkele nil-controle per toewijzing, zodat eenvoudige lees- en schrijftaken niet worden beïnvloed
Eén grens die eerlijk moet worden vermeld: de grafiek volgt afhankelijkheden tussen cellen, dus een door de gebruiker gedefinieerde functie (UDF) geregistreerd via OnUserFunction wordt opnieuw geëvalueerd wanneer de cellen die de argumenten voeden veranderen, net als elke andere formule. Als u de engine op die manier uitbreidt, behandelt het artikel over aangepaste functies in de HotXLS formule-engine het callback-contract en hoe argumentwaarden binnenkomen
Incrementele herberekening maakt deel uit van de standaard XLSX-engine in het HotXLS Delphi Excel Component, naast de formulecalculator, gedefinieerde namen en de import/export-pijplijn die het versnelt. Als uw Delphi- of C++Builder-applicatie levende modellen onderhoudt — prijsbladen, omzetberekeningen, trapsgewijze rapporten — is Recalculate het verschil tussen het herberekenen van een werkmap en het herberekenen van een bewerking