Revisjon av formel-cacher i Excel med HotXLS Deep Recalc

HotXLS svarer på spørsmålet enhver regnearkrørledning før eller siden må stille, nemlig om tallene lagret i en arbeidsbok fortsatt matcher formlene som produserte dem. CalculateAndVerify kalkulerer hele avhengighetsgrafen på nytt inn i et isolert overlay, sammenligner hvert resultat mot cache-verdien som allerede ligger i cellen, og rapporterer uenighetene. Som standard endrer det ingenting

Grunnen til at dette betyr noe, er at en regnearkfil lagrer to ting per formelcelle: formelen og siste verdi noen beregnet for den. Excel holder dem i sync. Alt annet i verden gjør kanskje ikke det. En fil som har passert gjennom et eldre bibliotek, en delvis rekalkulering, en håndredigert XML-del eller et verktøy som skrev verdier uten å beregne dem på nytt, vil glatt presentere en sum som ikke lenger følger av inndataene, og ingenting i filformatet flagger det

Hvorfor er en cache-verdi som uenig med formelen sin så farlig?

Fordi den er usynlig i hver vanlig lesevei. Åpne filen i et visningsprogram, les cellen gjennom et API, eksporter den til CSV eller PDF, og du får det cachede tallet. Formelen ligger der i samme celle, og ingen sammenligner dem. Uoverensstemmelsen kommer bare til overflaten når noen åpner arbeidsboken i Excel, som rekalkulerer ved lasting under de fleste innstillinger, og plutselig viser en rapport som ble signert forrige kvartal, andre summer

Revisjonen finnes for å gjøre den sammenligningen til en bevisst, planlagt operasjon snarere enn et uhell. Det er regneark-ekvivalenten til å verifisere en sjekksum: billig nok til å kjøre i en inntaksrørledning, og det eneste som gjør et stille dataintegritetsproblem om til en rapport du kan handle på

var
  Book: TXLSWorkbook;
  Options: TXLSRecalcAuditOptions;
  Report: TXLSCalculationAuditReport;
  I: Integer;
begin
  Book := TXLSWorkbook.Create(nil);
  try
    Book.LoadFromFile('quarterly-close.xls');
    Options := TXLSRecalcAuditOptions.Default;
    Options.MaxIssues := 500;
    Report := Book.CalculateAndVerify(Options);
    try
      for I := 0 to Report.Count - 1 do
        if Report[I].Kind = xlcaiCacheMismatch then
          Writeln(Report[I].SheetName, '!',
                  Report[I].Row, ':', Report[I].Col, '  ',
                  Report[I].Formula,
                  '  cached=', VarToStr(Report[I].Actual),
                  '  recomputed=', VarToStr(Report[I].Expected));
      if Report.Truncated then
        Writeln('issue budget reached, raise MaxIssues');
    finally
      Report.Free;
    end;
  finally
    Book.Free;
  end;
end;

Det finnes tre overloads, og de svarer på tre forskjellige spørsmål. Den parameterløse CalculateAndVerify returnerer et antall uoverensstemmelser, noe som er alt en helsetest trenger. Overload-en med en out-array av uoverensstemmelser gir deg cellene. Overload-en som tar TXLSRecalcAuditOptions returnerer en full TXLSCalculationAuditReport, som er den du bør gripe etter når du trenger å vite ikke bare at en verdi er uenig, men hvorfor revisjonen ikke kunne evaluere noe

Overlayet, og hvorfor revisjonen ikke skriver

Hver rekalkulert verdi lander i et overlay snarere enn i cellens cache, og overlayet injiseres helt fremst i cellelesings-callback-en i begge arbeidsbokmotorene. Den plasseringen er det som gjør revisjonen selvkonsistent: når B1 beregnes på nytt og C1 avhenger av B1, ser C1 verdien fra denne revisjonsrunden, ikke den foreldede cachede. Uten det ville en enkelt oppstrømsfeil blitt rapportert én gang og deretter absorbert, og hver nedstrømscelle ville virke enig med feil inndata

Celler hvis rekalkulerte verdi matcher cachen, går ikke inn i overlayet i det hele tatt. Det er ikke en mikrooptimalisering, det er det som holder revisjonen overkommelig. En ren arbeidsbok med hundre tusen formler utfører null overlay-skrivinger, og runden holder seg innenfor et budsjett på 1,35x mot en full rekalkulering, noe som er forskjellen mellom noe du kan kjøre ved hvert inntak og noe du kjører én gang i kvartalet

Evaluering følger en seriell topologisk rekkefølge utledet fra avhengighetsgrafen, med hver node merket skitten først, så hver celle beregnes nøyaktig én gang etter inndataene sine. Hvis du vil ha den inkrementelle mekanismen som holder en live arbeidsbok oppdatert i stedet for å revidere en lagret en, er det en annen mekanisme, beskrevet i inkrementell rekalkulering og avhengighetsgrafen

Feiler klassifiseres, ikke kastes i samme haug

En celle revisjonen ikke kan evaluere, er ikke samme funn som en celle hvis verdi er uenig, og TXLSCalculationAuditIssueKind holder kategoriene fra hverandre. xlcaiCacheMismatch er verdi-uoverensstemmelsen. xlcaiMissingFunction og xlcaiMissingName sier at evaluereren møtte noe den ikke implementerer eller ikke kan løse. xlcaiUnsupportedArguments dekker argumentformer utenfor støttedelmengden. xlcaiExternalReferenceDenied og xlcaiExternalReferenceMissing skiller et policyavslag fra en fraværende arbeidsbok. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled og xlcaiInternalFailure fullender settet

Ett skille er verdt å slå fast fordi det vender en vanlig antakelse. En positiv Excel-feilkode er et resultat, ikke en feil. En celle som legitimt evaluerer til #DIV/0!, har beregnet korrekt, så revisjonen lagrer den feilen i overlayet og sammenligner den mot cachen som enhver annen verdi. En arbeidsbok full av bevisste feilceller gir null funn, og en arbeidsbok der en feil dukket opp eller forsvant siden verdiene ble cachet, gir nøyaktig funnene du vil ha

Sirkulære referanser får sin egen behandling. Noder i en syklus kommer aldri inn i den topologiske rekkefølgen, så hver rapporteres individuelt som xlcaiCircularReference, og revisjonen kjører ikke den iterative løseren. Det er en bevisst skrivebeskyttet kontrakt: om iterasjon er slått på påvirker hvordan resultatkoden skal tolkes, ikke hva revisjonen gjør. Mekanikken i iterativ evaluering er dekket separat i iterativ kalkulering og sirkulære referanser

Å lese en feilkjede

Når en formel feiler å evaluere, er det sjelden nok å vite hvilken celle som feilet, for feilen ligger vanligvis tre nivåer ned i en referansekjede. Hvert funn bærer derfor en Stack-streng rendret med ytterste ramme først, i formen Sheet1!A1 > Sheet1!B2 > Data!C7, så rapporten peker på cellen som faktisk brøt sammen snarere enn cellen du tilfeldigvis så på

Opptakeren er avgrenset. MaxStackFrames er 64 som standard med et gulv på 8, og den dypeste feilende kjeden er den som beholdes: en indre ramme registrerer kjeden når feilen oppstår der, og ytre rammer som ruller tilbake etterpå overskriver den ikke. Hvis noen kjede overskred budsjettet, settes Report.StackTruncated, noe som forteller forskjellen mellom en kort kjede og en kjede du ikke så hele

// Skrivebeskyttet som standard. ApplyResults forplikter overlayet først etter en
// fullstendig vellykket revisjon, under en skrivevakt som avviser forpliktelsen
// hvis arbeidsbokstrukturen endret seg mens revisjonen kjørte
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // eksakt sammenligning, avdekker drift
Options.RelativeTolerance := 0;
Options.OnProgress := HandleProgress;

Report := Book.CalculateAndVerify(Options);
try
  if Report.Applied then
    Book.SaveToFile('quarterly-close-repaired.xls')
  else
    Writeln('not applied: ', Report.Count, ' issues blocked the commit');
finally
  Report.Free;
end;

procedure THarness.HandleProgress(ASender: TObject;
  ACurrent, ATotal: Integer; var ACancel: Boolean);
begin
  ACancel := FUserRequestedStop;   // revisjonen stopper ved neste nodegrense
end;

Når bør du la revisjonen reparere arbeidsboken?

Bare når revisjonen kom tilbake fullstendig ren for feilklasse-funn, noe som er nøyaktig betingelsen ApplyResults håndhever for deg. Forpliktelsen skjer etter en fullstendig vellykket runde, ble ikke avbrutt, og passerer en strukturell vakt: den binære motoren overvåker en arbeidsbok-endringsidentifikator, OOXML-motoren tar et øyeblikksbilde av en strukturgenerasjon per regneark. Hvis noe flyttet seg mens revisjonen kjørte, beskriver resultatene en arbeidsbok som ikke lenger finnes, og forpliktelsen avslås

Merk den bevisste asymmetrien. Cache-uoverensstemmelser blokkerer ikke anvendelsen, for de er nøyaktig det forpliktelsen er der for å reparere. Feilklasse-funn blokkerer den derimot, for en arbeidsbok der noen formler ikke kunne evalueres, ville blitt halvt reparert, og en halvt reparert arbeidsbok er verre enn en ureparert en du vet du skal mistro

Toleranse er en policybeslutning, ikke en standardverdi

Standard sammenligning er en absolutt toleranse på 1E-6 med relativ toleranse slått av, noe som bevarer den klassiske atferden og stillferdig aksepterer en drift på 4E-7. Det er vanligvis riktig: forskjeller i flyttalls-evalueringsrekkefølge mellom hva som enn produserte filen og dagens evaluerer, vil gi forskjeller av den størrelsen på lange summer, og å rapportere dem som integritetsfunn er støy

Sett begge toleransene til null når spørsmålet er annerledes, når du prøver å finne ut om en evaluerer endret atferd mellom versjoner, eller om et tredjepartsverktøy omskriver verdier på en subtilt annerledes måte. Ved null blir samme 4E-7-drift synlig, og det gjør alt annet også. Velg toleransen ut fra hvilket spørsmål du stiller, og noter valget ved siden av rapporten, for en rapport uten sin toleranse er ikke tolkbar

To nabofunksjoner fullender bildet. Når du vil vite hvorfor en enkelt formel produserer verdien den gjør, er steg-for-steg-visningen i formelevaluerings-sporeren riktig verktøy. Når du bevisst vil at cachede verdier skal æres uten noen rekalkulering, for eksempel på en inntaksvei som må reprodusere filen nøyaktig slik den ankom, er den modusen beskrevet i lesing av cachede formelverdier uten rekalkulering. Revisjonen er det som sitter mellom de to: den forteller deg om det er trygt å stole på cachen. Den følger med HotXLS Delphi regnearkkomponent for både den binære motoren og OOXML-motoren