Auditarea cache-urilor de formule cu HotXLS Deep Recalc

HotXLS răspunde la întrebarea pe care orice pipeline de foi de calcul ajunge să și-o pună în cele din urmă: dacă numerele stocate într-un registru de lucru se mai potrivesc cu formulele care le-au produs. CalculateAndVerify recalculează întregul graf de dependențe într-un overlay izolat, compară fiecare rezultat cu valoarea din cache aflată deja în celulă și raportează dezacordurile. Implicit nu schimbă nimic

Motivul pentru care asta contează e că un fișier de foaie de calcul stochează două lucruri per celulă de formulă: formula și ultima valoare pe care cineva a calculat-o pentru ea. Excel le ține sincronizate. Orice altceva din lume s-ar putea să nu. Un fișier care a trecut printr-o bibliotecă mai veche, printr-o recalculare parțială, printr-o parte XML editată de mână sau printr-o unealtă care a scris valori fără să le recalculeze va prezenta fericit un total care nu se mai deduce din intrările lui, iar nimic din formatul fișierului nu semnalează asta

De ce e atât de periculoasă o valoare din cache care nu se potrivește cu formula ei?

Pentru că e invizibilă pe fiecare cale obișnuită de citire. Deschideți fișierul într-un vizualizator, citiți celula printr-un API, exportați-o în CSV sau PDF, și primiți numărul din cache. Formula e chiar acolo, în aceeași celulă, iar nimeni nu le compară. Nepotrivirea iese la suprafață doar când cineva deschide registrul de lucru în Excel, care recalculează la încărcare sub majoritatea setărilor, și brusc un raport semnat trimestrul trecut arată totale diferite

Auditul există tocmai ca să transforme comparația aceea într-o operație deliberată și programată, nu într-un accident. E echivalentul foi de calcul al verificării unui checksum: suficient de ieftin încât să ruleze într-un pipeline de ingestie, și singurul lucru care transformă o problemă tăcută de integritate a datelor într-un raport pe care puteți acționa

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;

Există trei overload-uri și ele răspund la trei întrebări diferite. CalculateAndVerify-ul fără parametri întoarce un contor de nepotriviri, adică tot ce are nevoie o verificare de sănătate. Overload-ul cu un vector out de nepotriviri vă dă celulele. Overload-ul care primește TXLSRecalcAuditOptions întoarce un TXLSCalculationAuditReport complet, cel de apelat când trebuie să știți nu doar că o valoare nu se potrivește, ci și de ce auditul n-a putut evalua ceva

Overlay-ul, adică de ce auditul nu scrie

Fiecare valoare recalculată ajunge într-un overlay, nu în cache-ul celulei, iar overlay-ul e injectat chiar în fața callback-ului de citire a celulei, în ambele motoare de registru de lucru. Plasarea aceea e ce face auditul auto-consecvent: când B1 e recalculat, iar C1 depinde de B1, C1 vede valoarea din această trecere de audit, nu pe cea perimată din cache. Fără asta, o singură eroare amonte ar fi raportată o dată și apoi absorbită, iar fiecare celulă aval ar părea că e de acord cu o intrare greșită

Celulele a căror valoare recalculată se potrivește cu cache-ul nu intră deloc în overlay. Nu e o micro-optimizare, ci exact ce ține auditul accesibil. Un registru de lucru curat cu o sută de mii de formule execută zero scrieri în overlay, iar trecerea rămâne într-un buget de 1,35x față de o recalculare completă, diferența dintre ceva ce poți rula la fiecare ingestie și ceva ce rulezi o dată pe trimestru

Evaluarea urmează o ordine topologică serială derivată din graful de dependențe, cu fiecare nod marcat dirty mai întâi, deci fiecare celulă se calculează exact o dată, după intrările ei. Dacă vreți mașinăria incrementală care ține un registru de lucru viu la zi, în loc să auditați unul stocat, aceea e un mecanism diferit, descris în recalcularea incrementală și graful de dependențe

Eșecurile sunt clasificate, nu împestrițate laolaltă

O celulă pe care auditul nu o poate evalua nu e același constat ca o celulă a cărei valoare nu se potrivește, iar TXLSCalculationAuditIssueKind ține categoriile separate. xlcaiCacheMismatch e dezacordul de valoare. xlcaiMissingFunction și xlcaiMissingName spun că evaluatorul a întâlnit ceva ce nu implementează sau nu poate rezolva. xlcaiUnsupportedArguments acoperă formele de argument din afara submulțimii suportate. xlcaiExternalReferenceDenied și xlcaiExternalReferenceMissing separă un refuz de politică de un registru de lucru absent. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled și xlcaiInternalFailure completează setul

O distincție merită enunțată pentru că întoarce o presupunere comună. Un cod de eroare Excel pozitiv e un rezultat, nu un eșec. O celulă care evaluează legitim la #DIV/0! a calculat corect, deci auditul stochează eroarea aceea în overlay și o compară cu cache-ul ca pe orice altă valoare. Un registru de lucru plin de celule cu eroare intenționată produce zero constate, iar unul în care o eroare a apărut sau a dispărut de când s-au stocat valorile produce exact constatele pe care le vreți

Referințele circulare primesc tratamentul lor propriu. Nodurile dintr-un ciclu nu intră niciodată în ordinea topologică, deci fiecare e raportat individual ca xlcaiCircularReference, iar auditul nu rulează solver-ul iterativ. E un contract deliberat doar-citire: dacă iterația e activă afectează cum ar trebui interpretat codul rezultat, nu ce face auditul. Mecanica evaluării iterative e acoperită separat în calculul iterativ și referințele circulare

Citirea unui lanț de eșec

Când o formulă eșuează la evaluare, să știi care celulă a eșuat rar e de ajuns, pentru că eșecul stă de obicei cu trei niveluri mai jos, într-un lanț de referințe. Fiecare constat poartă deci un șir Stack randat cu cadrul cel mai exterior primul, în forma Sheet1!A1 > Sheet1!B2 > Data!C7, astfel încât raportul arată spre celula care chiar s-a stricat, nu spre celula pe care vă întâmpla să o priviți

Recorder-ul e limitat. MaxStackFrames are implicit 64, cu un plafon minim de 8, iar lanțul care eșuează cel mai adânc e cel reținut: un cadru interior înregistrează lanțul când eșecul își are originea acolo, iar cadrele exterioare care se derulă după nu îl suprascriu. Dacă vreun lanț a depășit bugetul, Report.StackTruncated e setat, ceea ce vă spune diferența dintre un lanț scurt și un lanț pe care nu l-ați văzut în întregime

// Doar citire din start. ApplyResults comite overlay-ul doar după un audit
// pe deplin reușit, sub o gardă de scriere care respinge comiterea dacă
// structura registrului de lucru s-a schimbat cât timp auditul rula
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // comparație exactă, scoate la suprafață derivele
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;   // auditul se oprește la următoarea graniță de nod
end;

Când e cazul să lăsați auditul să repare registrul de lucru?

Doar când auditul a revenit complet curat de constate de clasă-eșec, ceea ce e exact condiția pe care ApplyResults o impune pentru dumneavoastră. Comiterea se produce după o trecere pe deplin reușită, n-a fost anulată și trece printr-o gardă structurală: motorul binar supraveghează un identificator de schimbare a registrului de lucru, motorul OOXML face un snapshot al unei generații de structură per foaie de lucru. Dacă ceva s-a mutat cât timp auditul rula, rezultatele descriu un registru de lucru care nu mai există, iar comiterea e refuzată

Observați asimetria deliberată. Nepotrivirile de cache nu blochează aplicarea, pentru că ele sunt exact lucrul pe care comiterea există să îl repare. Constatele de clasă-eșec o blochează, pentru că un registru de lucru în care unele formule n-au putut fi evaluate ar fi reparat pe jumătate, iar un registru reparat pe jumătate e mai rău decât unul nereparat, despre care știi să nu ai încredere

Toleranța e o decizie de politică, nu un implicit

Comparația implicită e o toleranță absolută de 1E-6, cu toleranța relativă dezactivată, ceea ce păstrează comportamentul clasic și acceptă pe furiș o derivă de 4E-7. De regulă e corect: diferențele de ordine de evaluare în virgulă mobilă între ceea ce a produs fișierul și evaluatorul curent vor produce diferențe de acea mărime pe sume lungi, iar raportarea lor ca constate de integritate e zgomot

Puneți ambele toleranțe pe zero când întrebarea e alta: când încercați să aflați dacă un evaluator și-a schimbat comportamentul între versiuni, sau dacă o unealtă de la terți rescrie valorile într-un mod subtil diferit. La zero, aceeași derivă de 4E-7 devine vizibilă, la fel ca tot restul. Alegeți toleranța în funcție de întrebarea la care răspundeți și notați alegerea lângă raport, pentru că un raport fără toleranța lui nu e interpretabil

Două capacități învecinate completează tabloul. Când vreți să știți de ce o singură formulă produce valoarea pe care o produce, vederea pas cu pas din tracer-ul de evaluare a formulelor e unealta potrivită. Când vreți deliberat ca valorile din cache să fie respectate fără nicio recalculare, de pildă pe o cale de ingestie care trebuie să reproducă fișierul exact cum a sosit, modul acela e descris în citirea valorilor din cache ale formulelor fără recalculare. Auditul e ceea ce stă între cele două: vă spune dacă e sigur să aveți încredere în cache. Se livrează cu HotXLS Delphi spreadsheet component, pentru ambele motoare, cel binar și OOXML