Excel képletgyorsítótárak auditálása HotXLS deep recalccal

A HotXLS megválaszolja azt a kérdést, amelyet minden táblázatfolyamat előbb-utóbb fel kell tegyen: egyeznek-e még a munkafüzetben tárolt számok azokkal a képletekkel, amelyek előállították őket. A CalculateAndVerify a teljes függőségi gráfot elkülönített overlaybe számolja újra, minden eredményt összehasonlít a cellában már lévő gyorsítótárazott értékkel, és jelenti az eltéréseket. Alapértelmezés szerint semmit sem változtat

Az, hogy ez fontos, abból ered, hogy egy táblázatfájl képletcellánként két dolgot tárol: a képletet és az utolsó értéket, amelyet valaki számára kiszámolt. Az Excel szinkronban tartja őket. A világ minden mása nem feltétlenül. Egy olyan fájl, amely átesett egy régebbi könyvtáron, egy részleges újraszámoláson, egy kézzel szerkesztett XML részen vagy egy olyan eszközön, amely értékeket írt újraszámolás nélkül, készségesen mutat olyan összeget, amely már nem következik a bemeneteiből, és a fájlformátumban semmi sem jelzi ezt

Miért ilyen veszélyes egy a képletével ellentmondó gyorsítótárazott érték?

Mert minden hétköznapi olvasási úton láthatatlan. Nyissa meg a fájlt egy megjelenítőben, olvassa a cellát API-n keresztül, exportálja CSV-be vagy PDF-be, és a gyorsítótárazott számot kapja. A képlet ott van ugyanabban a cellában, és senki nem hasonlítja őket össze. Az eltérés csak akkor bukkan fel, amikor valaki megnyitja a munkafüzetet Excelben, amely a legtöbb beállítás mellett betöltéskor újraszámol, és hirtelen egy tavaly negyedévben lezárt jelentés más összegeket mutat

Az audit azért létezik, hogy ezt az összehasonlítást tudatos, ütemezett műveletté tegye, nem véletlen ütközetté. Ez a táblázat megfelelője annak, amikor ellenőrzőösszeget verifikálnak: elég olcsó ahhoz, hogy egy beérkező folyamatsorban fussan, és az egyetlen dolog, amely egy csendes adatintegritási problémából cselekvhető jelentést csinál

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;

Három túlterhelés létezik, és három különböző kérdésre válaszolnak. A paraméternélküli CalculateAndVerify eltérésszámot ad vissza, ami mind, amire egy egészségellenőrzésnek szüksége van. Az eltérések out tömbjével bíró túlterhelés megadja a cellákat. A TXLSRecalcAuditOptions-t fogadó túlterhelés teljes TXLSCalculationAuditReport-ot ad vissza, ez az, amelyhez akkor nyúljon, amikor nemcsak azt kell tudnia, hogy egy érték eltér, hanem azt is, miért nem tudott az audit valamit kiértékelni

Az overlay, és miért nem ír az audit

Minden újraszámolt érték overlaybe kerül, a cellagyorsítótárba nem, és az overlay mindkét munkafüzetmotorban a cellaolvasási callback legelső elejébe injektálódik. Az az elhelyezés teszi az auditot önkonzisztenssé: amikor a B1-et újraszámolják, és a C1 a B1-től függ, a C1 az ezen audit menetből származó értéket látja, nem az elavult gyorsítótárazottat. Enélkül egyetlen upstream hiba egyszer megjelent volna, aztán elnyeltetett volna, és minden downstream cella úgy tűnt volna, hogy egy rossz bemenettel egyetért

Azok a cellák, amelyeknek az újraszámolt értéke egyezik a gyorsítótárral, egyáltalán nem jutnak be az overlaybe. Ez nem mikrooptimalizálás, hanem az, ami megfizethetővé teszi az auditot. Egy százezer képletű tiszta munkafüzet nulla overlay írást végez, és a menet egy teljes újraszámoláshoz képest 1,35-szörös kereten belül marad, ami az a különbség, amely eldönti, hogy valami minden beérkezéskor futtatható-e, vagy negyedévente egyszer

A kiértékelés a függőségi gráfból levezetett soros topológiai sorrendet követ, minden csomópontot először dirty-nek jelölve, tehát minden cella pontosan egyszer számolódik a bemenetei után. Ha azt az inkrementális gépezetet szeretné, amely egy élő munkafüzetet naprakészen tart egy tárolt auditálása helyett, az más mechanizmus, amelyet az inkrementális újraszámolás és a függőségi gráf ír le

A hibák osztályozottak, nem egy kupacba rendezettek

Egy cella, amelyet az audit nem tud kiértékelni, nem ugyanaz a megállapítás, mint egy cella, amelynek az értéke eltér, és a TXLSCalculationAuditIssueKind szétválasztja a kategóriákat. A xlcaiCacheMismatch az értékeltérés. A xlcaiMissingFunction és a xlcaiMissingName azt mondja, hogy a kiértékelő olyasmibe futott bele, amelyet nem implementál vagy nem tud feloldani. A xlcaiUnsupportedArguments a támogatott részhalmazon kívüli argumentumalakokat fedi le. A xlcaiExternalReferenceDenied és a xlcaiExternalReferenceMissing egy szabályzati elutasítást választ el egy hiányzó munkafüzettől. A xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled és xlcaiInternalFailure teszi teljessé a halmazt

Egy megkülönböztetést érdemes kimondani, mert felfordít egy elterjedt feltevést. Egy pozitív Excel hibakód eredmény, nem hiba. Egy cella, amely jogosan #DIV/0!-ra értékelődik, helyesen számolt, tehát az audit azt a hibát az overlaybe teszi, és úgy hasonlítja a gyorsítótárhoz, mint bármely más értéket. Egy szándékos hibacellákkal teli munkafüzet nulla megállapítást ad, és egy olyan munkafüzetben, amelyben egy hiba megjelent vagy eltűnt, mióta az értékek gyorsítótárazva voltak, pontosan azok a megállapítások jelennek meg, amelyeket akar

A körkörös hivatkozások saját kezelést kapnak. A ciklusban lévő csomópontok sosem jutnak be a topológiai sorrendbe, tehát mindegyiket egyenként xlcaiCircularReference-ként jelentik, és az audit nem futtatja az iteratív megoldót. Ez szándékos csak-olvasó szerződés: az, hogy az iteráció engedélyezett-e, azt befolyásolja, hogyan kell értelmezni az eredménykódot, nem azt, mit tesz az audit. Az iteratív kiértékelés mechanikáját külön tárgyalja az iteratív számítás és körkörös hivatkozások

Egy hibalánc olvasása

Amikor egy képlet kiértékelése elbukik, ritkán elég tudni, melyik cella bukott meg, mert a hiba általában három szinttel lejjebb van egy hivatkozási láncban. Ezért minden megállapítás hordoz egy Stack sztringet, amely először a legkülső keretet rendereli, Sheet1!A1 > Sheet1!B2 > Data!C7 formában, tehát a jelentés arra a cellára mutat, amely ténylegesen eltört, nem arra, amelyre éppen ránézett

A rögzítő korlátolt. A MaxStackFrames alapértelmezése 64, alsó határa 8, és a legmélyebben bukó lánc az, amely megmarad: egy belső keret akkor rögzíti a láncot, amikor a hiba ott ered, és az utána lezáródó külső keretek nem írják felül. Ha bármely lánc túllépte a keretet, a Report.StackTruncated beállítódik, ami megmondja a különbséget egy rövid lánc és egy olyan lánc között, amelyet nem látott egészében

// Alapból csak olvas. Az ApplyResults csak egy teljesen sikeres audit után
// zárja le az overlayt, olyan írásvédő őr alatt, amely elutasítja a lezárást,
// ha a munkafüzet szerkezete megváltozott az audit futása alatt
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // pontos összehasonlítás, a sodródás látszik
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;   // az audit a következő csomópont-határnál áll meg
end;

Mikor engedje, hogy az audit megjavítsa a munkafüzetet?

Csak akkor, ha az audit teljesen tisztaan tért vissza hibaosztályú megállapításoktól, ami pontosan az a feltétel, amelyet az ApplyResults Ön nevében érvényesít. A lezárás egy teljesen sikeres menet után történik, nem volt megszakítva, és átmenegy egy szerkezeti őrön: a bináris motor egy munkafüzet-változásazonosítót figyel, az OOXML motor munkalankénti szerkezetgenerációt pillanatfelvételként rögzít. Ha bármi elmozdult, amíg az audit futott, az eredmények egy olyan munkafüzetet írnak le, amely már nem létezik, és a lezárást elutasítják

Vegye észre a szándékos aszimmetriát. A gyorsítótáreltérések nem blokkolják az alkalmazást, mert pontosan azok, amelyeket a lezárás javítani hivatott. A hibaosztályú megállapítások blokkolják, mert egy olyan munkafüzetben, amelyben néhány képletet nem tudtak kiértékelni, féljavított lenne az eredmény, és egy féljavított munkafüzet rosszabb, mint egy megjavítatlan, amelyről tudja, hogy ne bízzon benne

A tolerancia szabályzati döntés, nem alapértelmezés

Az alapértelmezett összehasonlítás 1E-6 abszolút tolerancia kikapcsolt relatív toleranciával, ami megtartja a klasszikus viselkedést, és csendben elfogad 4E-7 nagyságú sodródást. Ez általában helyes: a lebegőpontos kiértékelési sorrendbeli különbségek aközött, ami a fájlt előállította, és az aktuális kiértékelő között, ekkora különbségeket adnak hosszú összegeknél, és ezek integritási megállapításként való jelentése zaj

Állítsa mindkét toleranciát nullára, amikor a kérdés más: amikor azt próbálja kideríteni, hogy egy kiértékelő megváltozott-e a verziók között, vagy hogy egy harmadik féltől származó eszköz finoman eltérő módon írja-e újra az értékeket. Nullánál ugyanez a 4E-7 sodródás láthatóvá válik, és minden más is. Válassza a toleranciát aszerint, hogy melyik kérdést teszi fel, és jegyezze fel a döntést a jelentés mellé, mert egy tolerancia nélküli jelentés nem értelmezhető

Két szomszédos képesség teszi teljessé a képet. Amikor tudni akarja, miért azt az értéket adja egyetlen képlet, amelyet ad, a képlet-kiértékelő tracer lépésenkénti nézete a helyes eszköz. Amikor szándékosan azt akarja, hogy a gyorsítótárazott értékek érvényesüljenek bármilyen újraszámolás nélkül, például egy olyan beérkező úton, amelynek a fájlt pontosan úgy kell visszaadnia, ahogy megérkezett, azt a módot az gyorsítótárazott képletértékek olvasása újraszámolás nélkül írja le. Az audit az, amely a kettő között ül: megmondja, hogy a gyorsítótárba vetett bizalom biztonságos-e. A HotXLS Delphi táblázatkezelő komponenssel szállul a bináris és az OOXML motorhoz egyaránt