Audyt cache formuł Excela przez HotXLS Deep Recalc

HotXLS odpowiada na pytanie, które każdy potok arkuszowy prędzej czy później musi zadać: czy liczby zapisane w skoroszycie wciąż zgadzają się z formułami, które je wyprodukowały. CalculateAndVerify przelicza cały graf zależności do izolowanej nakładki, porównuje każdy wynik z wartością już siedzącą w cache komórki i raportuje niezgodności. Domyślnie niczego nie zmienia

Powód, dla którego to ważne: plik arkusza przechowuje dwie rzeczy na komórkę z formułą — formułę i ostatnią wartość, którą ktoś dla niej policzył. Excel trzyma je w synchronizacji. Cała reszta świata już niekoniecznie. Plik, który przeszedł przez starszą bibliotekę, częściowe przeliczenie, ręcznie edytowaną część XML albo narzędzie zapisujące wartości bez ich przeliczania, z przyjemnością pokaże sumę, która już nie wynika z jej wejść, a format pliku niczego takiego nie flaguje

Dlaczego wartość w cache niezgodna ze swoją formułą jest tak groźna?

Bo jest niewidoczna na każdej zwykłej ścieżce odczytu. Otwórz plik w przeglądarce, przeczytaj komórkę przez API, wyeksportuj do CSV albo PDF — dostaniesz liczbę z cache. Formuła jest przecież w tej samej komórce, a nikt ich nie porównuje. Niezgodność wychodzi na jaw dopiero, gdy ktoś otworzy skoroszyt w Excelu, który przy większości ustawień przelicza przy ładowaniu, i nagle raport podpisany w zeszłym kwartale pokazuje inne sumy

Audyt istnieje po to, by to porównanie było świadomą, zaplanowaną operacją, a nie wypadkiem. To arkuszowy odpowiednik weryfikacji sumy kontrolnej: na tyle tani, by chodził w potoku przyjęcia, i jedyna rzecz, która zamienia cichy problem integralności danych w raport, na którym można coś zrobić

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;

Są trzy przeciążenia i odpowiadają na trzy różne pytania. Bezzargumentowe CalculateAndVerify zwraca liczbę niezgodności, czyli wszystko, czego potrzebuje health check. Przeciążenie z tablicą out niezgodności daje ci komórki. Przeciążenie biorące TXLSRecalcAuditOptions zwraca pełny TXLSCalculationAuditReport — po niego sięgasz, gdy musisz wiedzieć nie tylko, że wartość się nie zgadza, ale też dlaczego audyt czegoś nie umiał policzyć

Nakładka, czyli dlaczego audyt nie zapisuje

Każda przeliczona wartość ląduje w nakładce, a nie w cache komórki, i nakładka jest wstrzykiwana na samym początku callbacku odczytu komórki w obu silnikach skoroszytów. To umiejscowienie robi audyt samospójnym: gdy B1 jest przeliczane, a C1 zależy od B1, C1 widzi wartość z tego przebiegu audytu, a nie nieświeżą z cache. Bez tego pojedynczy błąd upstream zostałby zgłoszony raz i potem wchłonięty, a każda komórka downstream wyglądałaby na zgodną ze złym wejściem

Komórki, których przeliczona wartość zgadza się z cache, w ogóle nie wchodzą do nakładki. To nie mikrooptymalizacja, to to, co trzyma audyt w granicach opłacalności. Czysty skoroszyt ze stu tysiącami formuł wykonuje zero zapisów do nakładki, a przebieg mieści się w budżecie 1.35x względem pełnego przeliczenia, czyli jest to różnica między czymś, co możesz puszczać przy każdym przyjęciu, a czymś, co puszczasz raz na kwartał

Obliczenia idą w szeryjnym porządku topologicznym wyprowadzonym z grafu zależności, z każdym węzłem najpierw oznaczonym jako dirty, więc każda komórka jest liczona dokładnie raz po swoich wejściach. Jeśli chcesz maszynerię przyrostową, która trzyma żywy skoroszyt na bieżąco, zamiast audytować zapisany, to inny mechanizm, opisany w artykule przeliczanie przyrostowe i graf zależności

Awarie są klasyfikowane, nie wrzucane do jednego worka

Komórka, której audyt nie umie policzyć, to nie to samo odkrycie co komórka, której wartość się nie zgadza, i TXLSCalculationAuditIssueKind trzyma te kategorie osobno. xlcaiCacheMismatch to niezgodność wartości. xlcaiMissingFunction i xlcaiMissingName mówią, że ewaluator spotkał coś, czego nie implementuje albo czego nie umie rozwiązać. xlcaiUnsupportedArguments obejmuje kształty argumentów poza wspieranym podzbiorem. xlcaiExternalReferenceDenied i xlcaiExternalReferenceMissing oddzielają odmowę polityki od nieobecnego skoroszytu. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled i xlcaiInternalFailure dopełniają zestaw

Jedno rozróżnienie warto wypowiedzieć, bo odwraca popularne założenie. Dodatni kod błędu Excela to wynik, nie awaria. Komórka, która legalnie liczy się do #DIV/0!, została policzona poprawnie, więc audyt zapisuje ten błąd w nakładce i porównuje go z cache jak każdą inną wartość. Skoroszyt pełny celowych komórek błędu produkuje zero odkryć, a skoroszyt, w którym błąd się pojawił albo zniknął od momentu zapisania wartości do cache, produkuje dokładnie te odkrycia, których chcesz

Referencje cykliczne dostają własne potraktowanie. Węzły w cyklu nigdy nie wchodzą do porządku topologicznego, więc każdy jest zgłaszany indywidualnie jako xlcaiCircularReference, a audyt nie odpala solvera iteracyjnego. To świadomy kontrakt tylko do odczytu: to, czy iteracja jest włączona, wpływa na to, jak interpretować kod wyniku, a nie na to, co audyt robi. Mechanikę obliczeń iteracyjnych opisano osobno w artykule obliczenia iteracyjne i referencje cykliczne

Czytanie łańcucha awarii

Gdy formuła nie daje się policzyć, znajomość komórki, która padła, rzadko wystarcza, bo awaria zwykle siedzi trzy poziomy w głąb łańcucha referencji. Każde odkrycie niesie więc ciąg Stack renderowany od skrajnej ramki, w postaci Sheet1!A1 > Sheet1!B2 > Data!C7, więc raport wskazuje komórkę, która faktycznie pękła, a nie tę, na którą akurat patrzyłeś

Rejestrator jest ograniczony. MaxStackFrames domyślnie 64 z podłogą 8, a zatrzymywany jest najgłębszy łańcuch awarii: wewnętrzna ramka rejestruje łańcuch, gdy awaria tam powstaje, a zewnętrzne ramki zwijające się potem go nie nadpisują. Jeśli jakikolwiek łańcuch przekroczył budżet, ustawiane jest Report.StackTruncated, co mówi ci różnicę między krótkim łańcuchem a łańcuchem, którego nie widziałeś w całości

// Domyślnie tylko do odczytu. ApplyResults zatwierdza nakładkę dopiero po
// w pełni udanym audycie, pod strażą zapisu odrzucającą zatwierdzenie,
// jeśli struktura skoroszytu zmieniła się w trakcie audytu
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // dokładne porównanie, wychodzi na jaw dryf
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;   // audyt zatrzymuje się na następnej granicy węzłów
end;

Kiedy pozwolić audytowi naprawić skoroszyt?

Tylko wtedy, gdy audyt wrócił całkowicie czysty od odkryć klasy awarii, i to jest dokładnie warunek, którego ApplyResults pilnuje za ciebie. Zatwierdzenie dzieje się po w pełni udanym przebiegu, który nie został anulowany, i przechodzi straż strukturalną: silnik binarny obserwuje identyfikator zmiany skoroszytu, silnik OOXML robi migawkę generacji struktury per arkusz. Jeśli cokolwiek drgnęło w trakcie audytu, wyniki opisują skoroszyt, który już nie istnieje, i zatwierdzenie zostaje odmówione

Zauważ świadomą asymetrię. Niezgodności cache nie blokują aplikacji, bo są dokładnie tym, co zatwierdzenie ma naprawiać. Odkrycia klasy awarii ją blokują, bo skoroszyt, w którym część formuł nie dała się policzyć, byłby naprawiony w połowie, a skoroszyt naprawiony w połowie jest gorszy niż nienaprawiony, o którym wiesz, że nie należy mu ufać

Tolerancja to decyzja polityki, nie domyślne

Domyślne porównanie to tolerancja bezwzględna 1E-6 z wyłączoną tolerancją względną, co zachowuje klasyczne zachowanie i po cichu akceptuje dryf 4E-7. Zwykle to słuszne: różnice w kolejności obliczeń zmiennoprzecinkowych między tym, co wyprodukowało plik, a bieżącym ewaluatorem dadzą na długich sumach różnice właśnie tej wielkości, a zgłaszanie ich jako odkryć integralności to szum

Ustaw obie tolerancje na zero, gdy pytanie jest inne: gdy próbujesz się dowiedzieć, czy ewaluator zmienił zachowanie między wersjami, albo czy narzędzie trzecie przepisuje wartości w subtelnie inny sposób. Na zerze ten sam dryf 4E-7 staje się widoczny, i wszystko inne też. Dobierz tolerancję według pytania, które zadajesz, i zapisz wybór obok raportu, bo raport bez swojej tolerancji nie jest interpretowalny

Dwie sąsiednie możliwości dopełniają obraz. Gdy chcesz wiedzieć, dlaczego pojedyncza formuła produkuje taką, a nie inną wartość, właściwym narzędziem jest widok krok po kroku z tracera obliczeń formuł. Gdy świadomie chcesz uszanować wartości z cache bez żadnego przeliczania, na przykład na ścieżce przyjęcia, która musi odtworzyć plik dokładnie tak, jak przyszedł, ten tryb opisuje odczyt wartości formuł z cache bez przeliczania. Audyt to to, co siedzi między nimi: mówi ci, czy ufać cache jest bezpiecznie. Jest częścią komponentu arkuszy HotXLS dla Delphi dla obu silników, binarnego i OOXML