Auditovanie cache vzorcov Excel s HotXLS Deep Recalc

HotXLS odpovedá na otázku, ktorú si každý tabuľkový pipeline skôr či neskôr musí položiť: či čísla uložené v zošite ešte sedia so vzorcami, ktoré ich vyprodukovali. CalculateAndVerify prepočíta celý graf závislostí do izolovaného overlayu, porovná každý výsledok s cache hodnotou, ktorá už je v bunke, a nahlási nesúhlasy. Defaultne nemení nič

Dôvod, prečo to záleží, je ten, že tabuľkový súbor ukladá dve veci na bunku so vzorcom: vzorec a poslednú hodnotu, ktorú niekto preň vypočítal. Excel ich drží v syncu. Všetko ostatné na svete nemusí. Súbor, ktorý prešiel staršou knižnicou, čiastočným prepočtom, ručne editovanou XML časťou alebo nástrojom, ktorý zapisoval hodnoty bez prepočítania, vám s radosťou predloží súhrn, ktorý už nevyplýva zo svojich vstupov, a nič vo formáte súboru to neoznačí

Prečo je cache hodnota nesúhlasíaca so svojím vzorcom taká nebezpečná?

Pretože je neviditeľná v každej obyčajnej čítacej ceste. Otvorte súbor v prehliadači, prečítajte bunku cez API, exportujte do CSV alebo PDF a dostanete cache číslo. Vzorec je priamo tam v tej istej bunke a nikto ich neporovná. Nesúhlas vypláva na povrch až vtedy, keď niekto otvorí zošit v Exceli, ktorý sa podľa väčšiny nastavení prepočítava pri načítaní, a zrazu report, ktorý minulý kvartál podpísali, ukazuje iné súčty

Audit existuje preto, aby z toho porovnania spravil zámernú, naplánovanú operáciu, nie nehodu. Je to tabuľková podoba overenia kontrolného súčtu: lacné na spustenie v príjmovom pipeline a jediná vec, ktorá z tichého problému integrity dát urobí report, s ktorým sa dá niečo robiť

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;

Existujú tri overloady a odpovedajú na tri odlišné otázky. Bezparametrový CalculateAndVerify vráti počet nesúhlasov, čo je všetko, čo zdravotná kontrola potrebuje. Overload s out poľom nesúhlasov vám dá bunky. Overload berúci TXLSRecalcAuditOptions vráti plný TXLSCalculationAuditReport a po tom siahnite, keď potrebujete vedieť nielen to, že hodnota nesúhlasí, ale aj prečo audit nemohol niečo vyhodnotiť

Overlay a prečo audit nezapisuje

Každá prepočítaná hodnota dopadne do overlayu, nie do cache buniek, a overlay sa vstrekuje na samom začiatku callbacku čítania buniek v oboch engineoch zošita. Toto umiestnenie je to, čo robí audit sebakonzistentným: keď sa prepočíta B1 a C1 závisí na B1, C1 vidí hodnotu z tohto auditného prechodu, nie zastaralú cache. Bez toho by sa jediná chyba upstreamu nahlásila raz a potom absorbovala a každá downstream bunka by pôsobila, že súhlasí so zlým vstupom

Bunky, ktorých prepočítaná hodnota sedí s cache, do overlayu vôbec nevstupujú. To nie je mikrooptimalizácia, to je to, čo drží audit cenovo dostupný. Čistý zošit so stotisíc vzorcami vykoná nulové zápisy do overlayu a prechod zostáva v rámci rozpočtu 1,35x oproti plnému prepočtu, čo je rozdiel medzi vecou, ktorú môžete spustiť pri každom príjme, a vecou, ktorú spustíte raz za kvartál

Vyhodnocovanie nasleduje sériové topologické poradie odvodené z grafu závislostí, pričom každý uzol sa najprv označí ako dirty, takže každá bunka sa vypočíta presne raz po svojich vstupoch. Ak chcete inkrementálnu mechaniku, ktorá drží živý zošit aktuálny namiesto auditovania uloženého, to je iný mechanizmus, popísaný v článku inkrementálne prepočítavanie a graf závislostí

Zlyhania sú klasifikované, nie hŕobené dokopy

Bunka, ktorú audit nedokáže vyhodnotiť, nie je to isté zistenie ako bunka, ktorej hodnota nesúhlasí, a TXLSCalculationAuditIssueKind drží kategórie rozdelené. xlcaiCacheMismatch je nesúhlas hodnôt. xlcaiMissingFunction a xlcaiMissingName hovoria, že vyhodnocovač stretol niečo, čo neimplementuje alebo nevie resolveovať. xlcaiUnsupportedArguments kryje tvary argumentov mimo podporovanej podmnožiny. xlcaiExternalReferenceDenied a xlcaiExternalReferenceMissing oddeľujú politické zamietnutie od chýbajúceho zošitu. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled a xlcaiInternalFailure sadu dopĺňajú

Jedno rozlíšenie stojí za vyslovenie, lebo prevracia bežný predpoklad. Kladný chybový kód Excel je výsledok, nie zlyhanie. Bunka, ktorá legitímne vyhodnotí na #DIV/0!, vypočítala správne, takže audit uloží túto chybu do overlayu a porovná ju s cache ako ktorúkoľvek inú hodnotu. Zošit plný zámerných chybových buniek produkuje nulové zistenia a zošit, kde chyba od času cachovania hodnôt pribudla alebo ubudla, produkuje presne tie zistenia, ktoré chcete

Cirkulárne referencie dostávajú vlastné ošetrenie. Uzly v cykle nikdy nevstupujú do topologického poradia, takže každý sa hlási osobitne ako xlcaiCircularReference a audit nespúšťa iteratívny solver. To je zámerná read-only zmluva: to, či je iterácia zapnutá, ovplyvňuje, ako sa má kód výsledku interpretovať, nie to, čo audit robí. Mechanika iteratívneho vyhodnocovania je popísaná osobitne v článku iteratívny výpočet a cirkulárne referencie

Čítanie reťaza zlyhaní

Keď vzorec nedokáže vyhodnotiť, poznať, ktorá bunka zlyhala, nestačí, pretože zlyhanie býva tri úrovne hlboko v reťaze referencií. Každé zistenie preto nesie reťazec Stack renderovaný najvnejším rámcom na prvom mieste, v tvare Sheet1!A1 > Sheet1!B2 > Data!C7, takže report ukazuje na bunku, ktorá reálne prerušila, nie na bunku, ktorú ste náhodou pozerali

Záznamník je ohraničený. MaxStackFrames má default 64 s minimom 8 a najhlbší zlyhávajúci reťaz je ten, ktorý sa ponechá: vnútorný rámec zaznamená reťaz, keď zlyhanie vznikne v ňom, a vonkajšie rámce sa rozbaľujúce potom ho neprepíšu. Ak ktorýkoľvek reťaz prekročil rozpočet, nastaví sa Report.StackTruncated, čo vám povie rozdiel medzi krátkym reťazom a reťazom, ktorý ste nevideli celý

// Defaultne read-only. ApplyResults potvrdí overlay až po úplne
// úspešnom audite, pod ochranou zápisu, ktorá zamietne potvrdenie,
// ak sa štruktúra zošitu zmenila, kým audit bežal
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // presné porovnanie, odhalí 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;   // audit zastane na ďalšej hranici uzlov
end;

Kedy dovoliť auditu zošit opraviť?

Len keď sa audit vrátil úplne čistý od zistení triedy zlyhaní, čo je presne podmienka, ktorú vám vynucuje ApplyResults. Potvrdenie nastane po úplne úspešnom prechode, ktorý nebol zrušený, a prejde štrukturálnou ochranou: binárny engine sleduje identifikátor zmeny zošitu, OOXML engine spraví snapshot generácie štruktúry na hárok. Ak sa čokoľvek pohlo, kým audit bežal, výsledky opisujú zošit, ktorý už neexistuje, a potvrdenie sa zamietne

Všimnite si zámernú asymetriu. Cache nesúhlasy nezablokujú aplikáciu, pretože presne to je to, čo má potvrdenie opraviť. Zistenia triedy zlyhaní ju zablokujú, pretože zošit, v ktorom niektoré vzorce nebolo možné vyhodnotiť, by bol napoly opravený a napoly opravený zošit je horší než neopravený, ktorému viete, že nemáte veriť

Tolerancia je politické rozhodnutie, nie default

Predvolené porovnanie je absolútna tolerancia 1E-6 s vypnutou relatívnou toleranciou, čo zachováva klasické správanie a poticho akceptuje drift 4E-7. To býva správne: rozdiely v poradí vyhodnocovania plávajúcej čiarky medzi tým, čo súbor vyprodukovalo, a aktuálnym vyhodnocovačom vyprodukujú na dlhých súčtoch rozdiely tejto veľkosti a hlásiť ich ako zistenia integrity je šum

Nastavte obe tolerancie na nulu, keď je otázka iná, keď sa snažíte zistiť, či vyhodnocovač zmenil správanie medzi verziami, alebo či nástroj tretej strany prepisuje hodnoty jemne odlišným spôsobom. Na nule sa ten istý drift 4E-7 stane viditeľným a takisto všetko ostatné. Vyberte toleranciu podľa otázky, ktorú kladiete, a zaznamenajte voľbu vedľa reportu, pretože report bez svojej tolerancie nie je interpretovateľný

Obraz dotvárajú dve susedné schopnosti. Keď chcete vedieť, prečo jediný vzorec produkuje hodnotu, ktorú produkuje, správnym nástrojom je pohľad krok za krokom v článku tracer vyhodnocovania vzorcov. Keď zámerné chcete, aby sa cache hodnoty respektovali bez akéhokoľvek prepočtu, napríklad na príjmovej ceste, ktorá musí reprodukovať súbor presne tak, ako prišiel, tento režim popisuje článok čítanie cache hodnôt vzorcov bez prepočítavania. Audit je to, čo sedí medzi nimi: povie vám, či je dôverovanie cache bezpečné. Prichádza s HotXLS Delphi tabuľkovým komponentom pre binárny aj OOXML engine