Provera keševa Excel formula uz HotXLS deep recalc

HotXLS odgovara na pitanje koje svaki spreadsheet pipeline pre ili kasnije mora da postavi, a to je da li se brojevi sačuvani u radnoj svesci i dalje poklapaju sa formulama koje su ih proizvele. CalculateAndVerify ponovo računa ceo graf zavisnosti u izolovan overlay, poredi svaki rezultat sa keširanom vrednošću koja je već u ćeliji, i prijavljuje neslaganja. Po defaultu ništa ne menja

Razlog zašto je ovo važno je što spreadsheet datoteka čuva dve stvari po ćeliji formule: formulu i poslednju vrednost koju je neko za nju izračunao. Excel ih drži u sinhronizaciji. Sve ostalo na svetu možda ne. Datoteka koja je prošla kroz stariju biblioteku, delimično preračunavanje, ručno uređen XML deo ili alat koji je upisao vrednosti bez ponovnog računanja rado će prikazati zbir koji više ne proizilazi iz svojih ulaza, i ništa u formatu datoteke to ne označava

Zašto je keširana vrednost koja se ne slaže sa formulom tako opasna?

Zato što je nevidljiva u svakom uobičajenom putu čitanja. Otvorite datoteku u pregledaču, pročitajte ćeliju kroz API, izvezite je u CSV ili PDF, i dobijate keširani broj. Formula je odmah tu u istoj ćeliji, i niko ih ne poredi. Neslaganje ispliva tek kad neko otvori radnu svesku u Excel-u, koji preračunava pri učitavanju pod većinom podešavanja, i odjednom izveštaj potpisan prošlog kvartala prikazuje drugačije zbirove

Provera postoji da to poređenje učini namernom, zakazanom operacijom umesto nesrećnim slučajem. To je spreadsheet pandan provere checksum-a: dovoljno jeftina da se izvršava u intake pipeline-u, i jedina stvar koja tih problem integriteta podataka pretvara u izveštaj na koji možete reagovati

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;

Postoje tri overload-a i odgovaraju na tri različita pitanja. CalculateAndVerify bez parametara vraća broj neslaganja, i to je sve što health check treba. Overload sa out nizom neslaganja daje vam ćelije. Overload koji uzima TXLSRecalcAuditOptions vraća kompletan TXLSCalculationAuditReport, i njemu posežete kad treba da znate ne samo da se vrednost ne slaže nego i zašto provera nije mogla nešto da izračuna

Overlay, i zašto provera ne upisuje

Svaka ponovo izračunata vrednost dospeva u overlay, a ne u keš ćelija, i overlay se ubacuje na samom početku cell-read callback-a u oba engine-a radnih sveski. To mesto čini proveru samousaglašenom: kad se B1 preračuna a C1 zavisi od B1, C1 vidi vrednost iz ovog prolaza provere, a ne zastarelu keširanu. Bez toga, jedna greška uzvodno bi se prijavila jednom i zatim upila, i svaka ćelija nizvodno delovala bi kao da se slaže sa pogrešnim ulazom

Ćelije čija ponovo izračunata vrednost odgovara kešu uopšte ne ulaze u overlay. To nije mikro-optimizacija, to je ono što proveru čini dovoljno jeftinom. Čista radna sveska sa sto hiljada formula izvršava nula overlay upisa i prolaz ostaje unutar budžeta od 1.35x naspram potpunog preračunavanja, što je razlika između nečega što možete vršiti na svakom unosu i nečega što vršite jednom u kvartalu

Vrednovanje prati serijski topološki redosled izveden iz grafa zavisnosti, sa svakim čvorom označenim dirty prvo, pa se svaka ćelija računa tačno jednom posle svojih ulaza. Ako želite inkrementalnu mehaniku koja živu radnu svesku drži aktuelnom umesto da proverava sačuvanu, to je drugi mehanizam, opisan u članku o inkrementalnom preračunavanju i grafu zavisnosti

Otkazi su klasifikovani, ne gurani u jednu gomilu

Ćelija koju provera ne može da izračuna nije isti nalaz kao ćelija čija se vrednost ne slaže, i TXLSCalculationAuditIssueKind drži kategorije odvojene. xlcaiCacheMismatch je neslaganje vrednosti. xlcaiMissingFunction i xlcaiMissingName kažu da je evaluator naišao na nešto što ne implementira ili ne ume da razreši. xlcaiUnsupportedArguments pokriva oblike argumenata van podržanog podskupa. xlcaiExternalReferenceDenied i xlcaiExternalReferenceMissing razdvajaju odbijanje po politici od odsutne radne sveske. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled i xlcaiInternalFailure dopunjuju skup

Jedno razlikovanje vredi izreći jer prevrće uobičajenu pretpostavku. Pozitivan Excel kod greške je rezultat, ne otkaz. Ćelija koja legitimno vrednuje u #DIV/0! računala je ispravno, pa provera tu grešku čuva u overlay-u i poredi je sa kešom kao i svaku drugu vrednost. Radna sveska puna namernih ćelija-grešaka proizvodi nula nalaza, a sveska u kojoj se greška pojavila ili nestala otkad su vrednosti keširane proizvodi baš nalaze koje želite

Kružne reference dobijaju svoje posebno rukovanje. Čvorovi u ciklusu nikada ne ulaze u topološki redosled, pa se svaki prijavljuje pojedinačno kao xlcaiCircularReference, i provera ne pokreće iterativni solver. To je namerni read-only ugovor: da li je iteracija uključena utiče na to kako treba tumačiti kod rezultata, a ne na to šta provera radi. Mehanika iterativnog vrednovanja pokrivena je posebno u članku o iterativnom računanju i kružnim referencama

Čitanje lanca otkaza

Kad formula ne uspe da se izračuna, znanje koja je ćelija pala retko je dovoljno, jer je otkaz obično tri nivoa niz lanac referenci. Svaki problem zato nosi Stack string iscrtan spoljašnjim okvirom prvim, u obliku Sheet1!A1 > Sheet1!B2 > Data!C7, pa izveštaj pokazuje na ćeliju koja je stvarno pukla, a ne na ćeliju koju ste slučajno gledali

Snimač je ograničen. MaxStackFrames je po defaultu 64 sa donjom granicom 8, i najdublji pucajući lanac je onaj koji se zadržava: unutrašnji okvir snima lanac kad otkaz tamo potiče, i spoljašnji okviri koji se odmotavaju kasnije njega ne pregaze. Ako je bilo koji lanac premašio budžet, postavlja se Report.StackTruncated, što vam govori razliku između kratkog lanca i lanca koji niste videli ceo

// Podrazumevano samo za čitanje. ApplyResults potvrđuje overlay tek posle
// potpuno uspešne provere, pod write guard-om koji odbija potvrdu
// ako se struktura radne sveske promenila dok je provera radila
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // tačno poređenje, izvodi drift na videlo
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;   // provera staje na sledećoj granici čvora
end;

Kada proveri prepustiti da popravi radnu svesku?

Samo kad je provera stigla potpuno čista od otkaznih nalaza, a to je baš uslov koji ApplyResults nameće umesto vas. Potvrda se dešava posle potpuno uspešnog prolaza koji nije otkazan, i prolazi strukturni guard: binarni engine prati identifikator izmene radne sveske, OOXML engine snima generaciju strukture po listu. Ako se išta pomerilo dok je provera radila, rezultati opisuju radnu svesku koja više ne postoji i potvrda se odbija

Primetite namernu asimetriju. Neslaganja keša ne blokiraju primenu, jer su upravo ono što potvrda postoji da popravi. Nalazi klase otkaza je blokiraju, jer bi radna sveska u kojoj se neke formule nisu mogle izračunati bila upola popravljena, a upola popravljena sveska je gora od nepopravljene za koju znate da ne treba verovati

Tolerancija je odluka politike, a ne podrazumevana vrednost

Podrazumevano poređenje je apsolutna tolerancija 1E-6 sa isključenom relativnom tolerancijom, što čuva klasično ponašanje i tiho prihvata odstupanje od 4E-7. To je obično ispravno: razlike u redosledu floating-point vrednovanja između onoga što je datoteku proizvelo i trenutnog evaluator-a proizveće odstupanja te veličine na dugim zbirovima, i njihovo prijavljivanje kao nalaza integriteta je buka

Postavite obe tolerancije na nulu kad je pitanje drugačije: kad pokušavate da saznate da li se ponašanje evaluator-a promenilo između verzija, ili da li alat treće strane prepisuje vrednosti na suptilno drugačiji način. Na nuli, isto odstupanje od 4E-7 postaje vidljivo, i sve ostalo takođe. Birajte toleranciju prema pitanju koje postavljate, i zapišite izbor uz izveštaj, jer izveštaj bez svoje tolerancije nije interpretabilan

Dve susedne mogućnosti dopunjuju sliku. Kad želite da znate zašto jedna formula proizvodi baš tu vrednost, korak-po-korak prikaz iz formula evaluation tracer-a je prava alatka. Kad namerno želite da se keširane vrednosti poštuju bez ijednog preračunavanja, na primer na intake putanji koja mora da reprodukuje datoteku tačno onakvom kakva je stigla, taj režim je opisan u članku o čitanju keširanih vrednosti formula bez preračunavanja. Provera je ono što sedi između ta dva: govori vam da li je verovati kešu bezbedno. Isporučuje se uz HotXLS Delphi spreadsheet komponentu za oba engine-a, binarni i OOXML