HotXLS, det native Excel-biblioteket for Delphi og C++Builder, utfører inkrementell formelrekalkulering gjennom TXLSXWorkbook.Recalculate. Det første kallet bygger en formelavhengighetsgraf og evaluerer hver eneste formelcelle; hvert senere kall evaluerer bare de cellene som er berørt av verdiskrivinger siden forrige runde, i topologisk rekkefølge, i ett enkelt sveip hvis kostnad er proporsjonal med antallet skitne celler snarere enn med arbeidsbokens størrelse
Den ene designbeslutningen er forskjellen på en finansmodell som svarer på en redigert forutsetning i løpet av millisekunder, og en som stopper opp i flere sekunder. Genererer du rapporter der en håndfull inndataceller mater tusenvis av nedstrømsformler, forklarer resten av denne artikkelen hva grafen gjør, hvilke funksjoner som melder seg ut av inkrementaliteten, og hvordan sirkulære referanser rapporteres i stedet for å løkke i det uendelige
Hvorfor rekalkulerer en endring i én celle hundre tusen formler?
En naiv formelmotor har ingen hukommelse om hvem som avhenger av hvem, så dens eneste trygge trekk etter enhver redigering er å evaluere alt på nytt. Verre er det at den klassiske rekursive strategien — når formel A refererer til formel B, evaluer B på stedet — evaluerer refererte celler betingelsesløst og ignorerer enhver hurtiglagret verdi. En kjede på n formler som hver refererer til den forrige, koster O(n²) evalueringer per full runde, og en sirkulær referanse sender rekursjonen utfor stupet. Enhver regnearkutvikler som har koblet en kaskaderende modell til en rekursiv evaluator, har sett begge feilmodusene inntreffe
Excel selv løste dette for tiår siden med sin beregningskjede: en rekkefølge over formelceller som vedlikeholdes slik at en redigering merker et lite sett med celler som skitne, og motoren går bare gjennom den berørte halen av kjeden. HotXLS anvender den samme ideen som en eksplisitt avhengighetsgraf, bygget én gang fra de kompilerte formeltrærne og gjenbrukt på tvers av rekalkuleringsrunder. Poenget er ikke å være smart; det er at rekalkuleringskostnaden skal følge størrelsen på redigeringen din, ikke størrelsen på arbeidsboken
Hvordan avhengighetsgrafen gjør en redigering om til én enkelt runde
HotXLS-avhengighetsgrafen gir hver formelcelle én node, med kanter som løper fra presedens til avhengig. Når koden din skriver en celleverdi, registrerer arbeidsboken cellen som skitten; når Recalculate kjører, forplanter skittenheten seg langs kantene til hver nedstrømsformel, og den skitne delgrafen evalueres nøyaktig én gang i topologisk rekkefølge ved hjelp av Kahns algoritme. Fordi en formel aldri besøkes før sine presedenser, trenger hver node én enkelt evaluering — det er det som gjør runden O(skitne)
Topologisk rekkefølge løser også rekursjonsproblemet ved roten. Under en rekalkuleringsrunde skifter motoren til en dedikert modus der enhver referanse til en annen formelcelle leser den cellens hurtiglagrede verdi direkte i stedet for å evaluere den på nytt — rekkefølgen garanterer at hurtiglageret allerede er ferskt. Den samme mekanismen betyr at en referansesyklus ikke kan utløse ubegrenset rekursjon: ingenting inne i runden går noen gang inn i evaluatoren på nytt 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; // vekstforutsetning
Model.Cells[2, 2].Formula := 'Inputs!B2*1000'; // XLSX-formler tar ingen innledende '='
Model.Cells[3, 2].Formula := 'B2*(1+Inputs!B2)';
// ... tusenvis av flere rader som kaskaderer ut fra den samme forutsetningen ...
Book.Recalculate; // første kall: bygger grafen, full evaluering
Inputs.Cells[2, 2].Value := 0.07; // én redigering merker én celle som skitten
Book.Recalculate; // andre kall: bare nedstrømskjeden kjører
finally
Book.Free;
end;
end;Hvert resultat lander i cellens hurtiglagrede Value, så etter at Recalculate returnerer, leser du utdata på samme måte som du leser enhver annen celle. I en rapportgenereringsløkke er mønsteret nøyaktig koden ovenfor: last inn eller bygg modellen én gang, veksle så mellom å skrive noen få inndataceller og å kalle Recalculate, og betal bare for de formlene som faktisk avhenger av det som ble endret
Hvilke Excel-funksjoner tvinger frem rekalkulering i hver runde?
HotXLS behandler NOW, TODAY, RAND, OFFSET og INDIRECT som flyktige: enhver formel som inneholder en av dem, evalueres på nytt i hver Recalculate-runde, uansett om noe oppstrøms er endret eller ikke. De tre første er flyktige av samme grunn som de er det i Excel — resultatet deres avhenger av evalueringsøyeblikket, ikke av andre celler. OFFSET og INDIRECT er flyktige av en mer subtil grunn: cellene de leser, beregnes ved kjøretid, så grafen kan ikke statisk vite hvilke kanter den skal tegne for dem
Den samme forsiktige regelen strekker seg til referanser grafbyggeren ikke kan feste til ett enkelt rektangel. En formel som går gjennom et navngitt område med flere delområder, eller en som refererer til en ekstern arbeidsbok, degraderes likeledes til flyktig og evalueres på nytt i hver runde. Policyen er bevisst: en ekstra evaluering koster litt tid, men en manglende avhengighetskant betyr en stille foreldet verdi i en utsendt rapport, og det er den langt verre feilen. Lener modellen din seg på arbeidsbokomfattede navn, dekker søsterartikkelen om definerte navn og formler på tvers av ark hvordan navn med ett enkelt område løses opp — de deltar normalt i grafen
Den praktiske veiledningen følger direkte. Hold de varme stiene i en stor modell på vanlige celle- og områdereferanser der grafen kan gjøre jobben sin, og sett OFFSET og INDIRECT i karantene til de få stedene som virkelig trenger dynamisk adressering. En modell med tusen flyktige formler kjører de tusen på nytt i hver runde uansett hvor liten redigeringen var — nøyaktig den oppførselen Excel-brukere kjenner fra arbeidsbøker som «rekalkulerer ved hvert tastetrykk»
Hvordan rapporterer HotXLS sirkulære referanser?
TXLSXWorkbook.Recalculate returnerer lxOk på en ren runde og lxErrorRef når den oppdager en referansesyklus. Syklusmedlemmene identifiseres under den topologiske sorteringen — de er nodene Kahns algoritme aldri kan frigi — og de hoppes over i stedet for å løkkes: de hurtiglagrede verdiene deres forblir det de var, mens hver formel utenfor syklusen fortsatt evalueres normalt i rekkefølge. Kallstedet ditt får en entydig feilkode i stedet for en hengende prosess
case Book.Recalculate of
lxOk:
SaveReport(Book);
lxErrorRef:
// det finnes en referansesyklus; syklusmedlemmene beholdt sine forrige
// hurtiglagrede verdier, og alt utenfor syklusen er oppdatert
LogWarning('Circular reference detected - review model inputs');
end;Å finne ut hvilke celler som utgjør syklusen, er en feilsøkingsjobb, og formelevalueringssporeren er det rette verktøyet til det: spor den mistenkte formelen, så blir referansekjeden som bretter seg tilbake på seg selv, synlig steg for steg. Sykluser i virkelige modeller er nesten alltid en forfatterfeil — en sammendragsrad som ved et uhell er tatt med i sitt eget SUM-område — så en høylytt feilkode ved rekalkuleringstidspunktet er akkurat det du vil ha
Matriseformler, skittenhetssporing og når grafen bygges på nytt
CSE-matriseformler får én node for hele det forankrede rektangelet, ikke én node per celle. Rotformelen evalueres én gang per runde; den resulterende matrisen skrives direkte inn i hver medlemscelle, og en formel som refererer til hvilken som helst celle inne i det forankrede området — ikke bare det øverste venstre ankeret — plukker opp en avhengighetskant fra den rotnoden. Skalare resultater kringkastes over rektangelet slik Excels gamle matrisesemantikk foreskriver
Skittenhetssporingen kroker seg på de vanlige egenskapssetterne, så ingenting ved koden din endres. Å skrive Value på en celle varsler arbeidsboken og merker avhengige som skitne; å tilordne en ny Formula er en strukturell endring, så den merker hele grafen som foreldet, og neste Recalculate bygger den på nytt før evalueringen. Å legge til, slette eller flytte ark ugyldiggjør også grafen, siden nodeidentiteten koder arkindeksen. Når ingen graf er aktiv — en arbeidsbok du aldri kaller Recalculate på — koster krokene én enkelt nil-sjekk per tilordning, så vanlige les- og skriveoppgaver berøres ikke
Én grense er verdt å si ærlig fra om: grafen sporer avhengigheter mellom celler, så en brukerdefinert funksjon registrert gjennom OnUserFunction evalueres på nytt når cellene som mater argumentene dens, endres, som enhver annen formel. Utvider du motoren på den måten, går artikkelen om egendefinerte funksjoner i HotXLS-formelmotoren gjennom tilbakekallskontrakten og hvordan argumentverdiene kommer inn
Inkrementell rekalkulering er en del av standard XLSX-motoren i HotXLS Delphi Excel Component, sammen med formelkalkulatoren, definerte navn og import- og eksportrørledningen den akselererer. Vedlikeholder Delphi- eller C++Builder-applikasjonen din levende modeller — prisark, konsolideringsarbeidsbøker, rapportkaskader — er Recalculate forskjellen på å beregne en arbeidsbok på nytt og å beregne en redigering på nytt