HotXLS, det indfødte Excel-bibliotek til Delphi og C++Builder, udfører inkrementel formelgenberegning via TXLSXWorkbook.Recalculate. Det første kald opbygger en formel-afhængighedsgraf og evaluerer alle formelceller; ethvert efterfølgende kald genberegner kun de celler, der er påvirket af værditilskrivninger siden den seneste kørsel, i topologisk rækkefølge. Dette sker i en enkelt kørsel, hvis omkostning is proportional med antallet af ændrede (dirty) celler frem for arbejdsbogens samlede størrelse
Denne ene designbeslutning udgør forskellen mellem en finansiel model, der reagerer på en ændret forudsætning på få millisekunder, og en, der går i stå i flere sekunder. Hvis du genererer rapporter, hvor en håndfuld inputceller føder tusindvis af efterfølgende formler, forklarer resten af denne artikel, hvad grafen gør, hvilke funktioner der fravælger inkrementering, og hvordan cirkulære referencer rapporteres i stedet for at gå i uendelig løkke
Hvorfor genberegner ændring af én celle hundrede tusinde formler?
En naiv formelmotor husker ikke, hvem der afhænger af hvem, så dens eneste sikre træk efter en redigering er at evaluere det hele igen. Hvad værre er, så vil den klassiske rekursive strategi — når formel A refererer til formel B, så evalueres B med det samme — genberegne refererede celler betingelsesløst og ignorere eventuelle cachede værdier. En kæde af n formler, som hver refererer til den foregående, koster O(n²) evalueringer pr. fuld kørsel, og en cirkulær reference sender rekursionen ud over en afgrund. Enhver regnearksudvikler, der har forbundet en kaskademodel til en rekursiv evaluator, har set begge fejltilstande ske
Hvordan afhængighedsgrafen gør en redigering til en enkelt kørsel
HotXLS-afhængighedsgrafen giver hver formelcelle én knude (node) med kanter (edges), der går fra præcedens til afhængig. Når din kode skriver en celleværdi, registrerer arbejdsbogen cellen som ændret (dirty); når Recalculate kører, spreder ændringen sig langs kanterne til enhver efterfølgende formel, og den ændrede undergraf evalueres nøjagtigt én gang i topologisk rækkefølge ved hjælp af Kahns algoritme. Da en formel aldrig besøges før sine forudsætninger, kræver hver knude kun en enkelt evaluering — det er det, der gør kørslen O(dirty)
Topologisk rækkefølge løser også rekursionsproblemet ved roden. Under en genberegningskørsel skifter motoren til en dedikeret tilstand, hvor enhver reference to en anden formelcelle læser den pågældende celles cachede værdi direkte i stedet for at genberegne den — rækkefølgen garanterer, at cachen allerede er opdateret. Den samme mekanisme betyder, at en referencecyklus ikke kan udløse ubegrænset rekursion: Intet i kørslen genindtaster nogensinde evaluatoren for en nabocelle
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;Hvert resultat lander i cellens cachede Value, so efter at Recalculate har returneret, læser du output på samme måde, som du læser enhver anden celle. I en rapportgenereringsløkke er mønsteret nøjagtigt som koden ovenfor: Indlæs eller opbyg modellen én gang, og skift derefter mellem at skrive i et par inputceller og kalde Recalculate, så du kun betaler for de formler, der rent faktisk afhænger af det, der blev ændret
Hvilke Excel-funktioner fremtvinger genberegning ved hver kørsel?
HotXLS behandler NOW, TODAY, RAND, OFFSET og INDIRECT som volatile: Enhver formel, der indeholder en af dem, genberegnes ved hver Recalculate-kørsel, uanset om der er ændret noget opstrøms. De første tre er volatile af samme grund, som de er i Excel — deres resultat afhænger af evalueringstidspunktet, ikke af andre celler. OFFSET og INDIRECT er volatile af en mere subtil årsag: De celler, de læser, beregnes under kørslen, så grafen kan ikke vide statisk, hvilke kanter den skal tegne for dem
Den samme konservative regel gælder for referencer, som grafbyggeren ikke kan fastlåse to et enkelt rektangel. En formel, der går gennem et navngivet område med flere områder (multi-area), eller en, der refererer til en ekstern arbejdsbog, nedgraderes ligeledes til volatil og genberegnes ved hver kørsel. Denne politik er bevidst: En ekstra evaluering koster lidt tid, men a manglende afhængighedskant betyder en lydløst forældet værdi i en afsendt rapport, hvilket er en langt værre fejl. Hvis din model læner sig op ad navne med arbejdsbog-omfang, dækker ledsagerartiklen om definerede navne og formler på tværs af ark, hvordan navne med et enkelt område løses — disse deltager i grafen på normal vis
Den praktiske vejledning følger direkte heraf. Hold de vigtige stier i en stor model på almindelige celle- og områdereferencer, hvor grafen kan udføre sit arbejde, og hold OFFSET og INDIRECT i karantæne til de få steder, der reelt kræver dynamisk adressering. En model med tusind volatile formler genkører disse tusind ved hver kørsel, uanset hvor lille redigeringen var — præcis den adfærd, Excel-brugere kender fra arbejdsbøger, der "genberegner ved hvert tastetryk"
Hvordan rapporterer HotXLS cirkulære referencer?
TXLSXWorkbook.Recalculate returnerer lxOk ved en fejlfri kørsel og lxErrorRef, når den registrerer en referencecyklus. Cyklusmedlemmer identificeres under den topologiske sortering — det er de knuder (nodes), som Kahns algoritme aldrig kan frigøre — og de springes over frem for at køre i løkke: Deres cachede værdier forbliver, hvad de var, mens enhver formel uden for cyklussen stadig evalueres normalt i rækkefølge. Dit kaldested modtager en bestemt fejlkode i stedet for at gå i baglås
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;At finde ud af, hvilke celler der danner cyklussen, er en fejlfindingsopgave, og formelevaluerings-traceren er det rigtige værktøj til det: Spore den mistænkelige formel, hvorefter referencekæden, der folder sig tilbage i sig selv, bliver synlig trin for trin. Cyklusser i rigtige modeller er næsten altid en formuleringsfejl — en opsummeringsrække, der utilsigtet er inkluderet i sit eget SUM-område — så en tydelig fejlkode på genberegningstidspunktet er præcis, hvad du ønsker
Matrixformler, sporing af ændringer og hvornår grafen genopbygges
CSE-matrixformler får én knude for hele det forankrede rektangel, ikke én knude pr. celle. Rodformlen evalueres én gang pr. kørsel; den resulterende matrix skrives direkte ind i hver enkelt medlemscelle, og en formel, der refererer til en hvilken som celler i det forankrede område — ikke kun det øverste venstre anker — opsamler en afhængighedskant fra denne rodknude. Skalære resultater udsendes over rektanglet på den måde, som Excels ældre matrixsemantik foreskriver
Sporing af ændringer (dirty tracking) hooker de almindelige egenskabs-settere, så intet ændrer sig i din kode. Skrivning af Value på en celle giver besked til arbejdsbogen og markerer afhængige celler som ændrede; tildeling af en ny Formula er en strukturel ændring, så det markerer hele grafen som forældet, og den næste Recalculate genopbygger den før evaluering. Tilføjelse, sletning eller flytning af ark ugyldiggør også grafen, da knudeidentiteten koder arkindekset. Når ingen graf er aktiv — en arbejdsbog, du aldrig kalder Recalculate på — koster disse hooks kun et enkelt nil-tjek pr. tildeling, så almindelige læse-skrive-arbejdsgange er upåvirkede
En grænse, der er værd at nævne ærligt: Grafen sporer afhængigheder mellem celler, så en brugerdefineret funktion registreret via OnUserFunction genberegnes, når de celler, der føder dens argumenter, ændrer sig, ligesom enhver anden formel. Hvis du udvider motoren på den måde, gennemgår artiklen om brugerdefinerede funktioner i HotXLS-formelmotoren kontraktforholdet for callback og hvordan argumentværdier ankommer
Inkrementel genberegning er en del af standard-XLSX-motoren i HotXLS Delphi Excel Component, sammen med formelberegneren, definerede navne og import/eksport-pipelinen, som den accelererer. Hvis din Delphi- eller C++Builder-applikation vedligeholder levende modeller — prisark, konsolideringsarbedsbøger, rapportkaskader — er Recalculate forskellen mellem at genberegne en arbejdsbog eller blot at genberegne en redigering