Excel-kaavavälimuistien auditointi HotXLS Deep Recalcilla

HotXLS vastaa kysymykseen, jonka jokainen taulukkolaskentaputki lopulta joutuu kysymään: vastaavatko työkirjaan tallennetut luvut yhä niitä tuottaneita kaavoja. CalculateAndVerify laskee koko riippuvuusgraafin uudelleen eristettyyn overlayyn, vertaa jokaista tulosta soluun jo tallennettuun välimuistiarvoon ja raportoi erimielisyydet. Oletuksena se ei muuta mitään

Tällä on merkitystä, koska taulukkolaskentatiedosto tallentaa kaksi asiaa kaavasolua kohden: kaavan ja viimeisimmän arvon, jonka joku sille laski. Excel pitää ne synkronoituna. Kaikki muu maailmassa ei välttämättä. Tiedosto, joka kulki vanhemman kirjaston, osittaisen uudelleenlaskennan, käsin muokatun XML-osan tai työkalun läpi, joka kirjoitti arvoja laskematta niitä uudelleen, esittää ilomielin summan, joka ei enää seuraa syötteistään, eikä mikään tiedostomuodossa liputa sitä

Miksi välimuistiarvo, joka eriää kaavastaan, on niin vaarallinen?

Koska se on näkymätön jokaisessa tavallisessa lukupolussa. Avaa tiedosto katselimessa, lue solu API:n kautta, vie se CSV:ksi tai PDF:ksi, ja saat välimuistiluvun. Kaava on juuri siinä samassa solussa, eikä kukaan vertaa niitä. Ristiriita nousee pintaan vasta, kun joku avaa työkirjan Excelissä, joka laskee uudelleen latauksen yhteydessä useimmissa asetuksissa, ja yhtäkkiä viime neljänneksellä hyväksytty raportti näyttää eri summat

Auditointi on olemassa tekemään tuosta vertailusta tahallinen, aikataulutettu operaatio onnettomuuden sijaan. Se on taulukkolaskennan vastine tarkistussumman varmistamiselle: halpa ajettavaksi sisääntuloputkessa ja ainoa asia, joka muuttaa hiljaisen data-eheysongelman raportiksi, johon voi puuttua

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;

Ylikuormia on kolme, ja ne vastaavat kolmeen eri kysymykseen. Parametriton CalculateAndVerify palauttaa erimielisyyslukumäärän, mikä riittää terveystarkistukselle. Ylikuorma, jossa on out-taulu erimielisyyksistä, antaa sinulle solut. Ylikuorma, joka ottaa TXLSRecalcAuditOptionsin, palauttaa täyden TXLSCalculationAuditReportin, ja siihen kannattaa tarttua, kun sinun on tiedettävä ei vain, että arvo eriää, vaan miksi auditointi ei pystynyt arvioimaan jotain

Overlay ja miksi auditointi ei kirjoita

Jokainen uudelleenlaskettu arvo laskeutuu overlayyn soluvälimuistin sijaan, ja overlay injektoidaan aivan solulukucallbackin eteen molemmissa työkirjamoottoreissa. Tuo sijoitus tekee auditoinnista itsensä yhtenäisen: kun B1 lasketaan uudelleen ja C1 riippuu B1:stä, C1 näkee arvon tästä auditointikierroksesta, ei vanhentunutta välimuistiarvoa. Ilman sitä yksi ylävirtavirhe raportoitaisiin kerran ja sitten nielaistaisiin, ja jokainen alavirtasolu näyttäisi olevan samaa mieltä väärän syötteen kanssa

Solujen, joiden uudelleenlaskettu arvo täsmää välimuistiin, ei tarvitse päästä overlayyn lainkaan. Tuo ei ole mikro-optimointi, se on se, mikä pitää auditoinnin kohtuuhintaisena. Puhdas työkirja sadalla tuhannella kaavalla suorittaa nolla overlay-kirjoitusta, ja kierros pysyy 1,35x budjetissa täyden uudelleenlaskennan nähden, mikä on ero jonkin, jota voi ajaa jokaisella sisääntulolla, ja jonkin, jonka ajaa kerran neljänneksessä, välillä

Arviointi noudattaa riippuvuusgraafista johdettua sarjallista topologista järjestystä, jokainen solmu merkittynä likaiseksi ensin, joten jokainen solu lasketaan täsmälleen kerran syötteidensä jälkeen. Jos haluat inkrementaalisen koneiston, joka pitää elävän työkirjan ajan tasalla tallennetun auditoinnin sijaan, kyseessä on eri mekanismi, kuvattu artikkelissa inkrementaalinen uudelleenlaskenta ja riippuvuusgraafi

Viat luokitellaan, ei pinota yhteen kasaan

Solu, jota auditointi ei pysty arvioimaan, ei ole sama löydös kuin solu, jonka arvo eriää, ja TXLSCalculationAuditIssueKind pitää kategoriat erillään. xlcaiCacheMismatch on arvojen erimielisyys. xlcaiMissingFunction ja xlcaiMissingName sanovat, että arvioija tapasi jotain, mitä se ei toteuta tai ei voi ratkaista. xlcaiUnsupportedArguments kattaa argumenttimuodot tuetun osajoukon ulkopuolelta. xlcaiExternalReferenceDenied ja xlcaiExternalReferenceMissing erottavat politiikan kiellon puuttuvasta työkirjasta. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled ja xlcaiInternalFailure täydentävät joukon

Yksi erottelu kannattaa sanoa, koska se kääntää yleisen oletuksen. Positiivinen Excel-virhekoodi on tulos, ei vika. Solu, joka laillisesti evaluoituu #DIV/0!:ksi, on laskenut oikein, joten auditointi tallentaa tuon virheen overlayyn ja vertaa sitä välimuistiin kuin mitä tahansa muuta arvoa. Työkirja, joka on täynnä tahallisia virhesoluja, tuottaa nolla löydöstä, ja työkirja, jossa virhe ilmestyi tai katosi arvojen välimuistituksen jälkeen, tuottaa täsmälleen ne löydökset, joita haluat

Kehämäiset viittaukset saavat oman kohtelunsa. Syklin solmut eivät koskaan pääse topologiseen järjestykseen, joten jokainen raportoidaan yksittäin xlcaiCircularReferenceinä, eikä auditointi aja iteroivaa ratkaisijaa. Kyseessä on tahallinen read-only-sopimus: sen, onko iterointi käytössä, vaikutus on siihen, miten tulostekoodin pitäisi tulkita, ei siihen, mitä auditointi tekee. Iteratiivisen arvioinnin koneistot on käsitelty erikseen artikkelissa iteratiivinen laskenta ja kehämäiset viittaukset

Vikaketjun lukeminen

Kun kaava ei onnistu arvioimaan, tietäminen siitä, mikä solu kaatui, riittää harvoin, koska vika on yleensä kolmen tason päässä viittausketjussa. Jokainen löydös kantaa siksi Stack-merkkijonoa, renderöitynä uloin kehys ensin, muodossa Sheet1!A1 > Sheet1!B2 > Data!C7, joten raportti osoittaa solua, joka oikeasti rikkoutui, eikä solua, jota sattuit katsomaan

Nauhoitin on rajattu. MaxStackFramesin oletusarvo on 64, lattia 8, ja syvin kaatunut ketju on se, joka säilytetään: sisempi kehys nauhoittaa ketjun, kun vika alkaa sieltä, ja ulommat kehykset eivät purkautuessaan ylikirjoita sitä. Jos mikään ketju ylitti budjetin, Report.StackTruncated asetetaan, mikä kertoo sinulle eron lyhyen ketjun ja ketjun, jota et nähnyt kokonaan, välillä

// Read-only oletuksena. ApplyResults vahvistaa overlayn vasta täysin
// onnistuneen auditoinnin jälkeen kirjoitusvahdin alla, joka hylkää
// vahvistuksen, jos työkirjan rakenne muuttui auditoinnin aikana
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // tarkka vertailu, tuo ajautumisen näkyviin
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;   // auditointi pysähtyy seuraavaan solmurajaan
end;

Milloin kannattaa antaa auditoinnin korjata työkirja?

Vasta silloin, kun auditointi palasi täysin puhtaana vikaluokan löydöksistä, mikä on täsmälleen se ehto, jonka ApplyResults valvoo puolestasi. Vahvistus tapahtuu täysin onnistuneen kierroksen jälkeen, ei ollut peruttu, ja läpäisee rakenteellisen vahdin: binäärinen moottori tarkkailee työkirjan muutostunnistetta, OOXML-moottori ottaa snapshotin taulukottaisesta rakennesukupolvesta. Jos mikään liikkui auditoinnin aikana, tulokset kuvailevat työkirjaa, jota ei enää ole, ja vahvistus hylätään

Huomaa tahallinen epäsymmetria. Välimuistierimielisyydet eivät estä sovellusta, koska ne ovat täsmälleen sitä, mitä vahvistus on olemassa korjaamaan. Vikaluokan löydökset estävät sen, koska työkirja, jossa osaa kaavoista ei voitu arvioida, olisi puoliksi korjattu, ja puoliksi korjattu työkirja on pahempi kuin korjaamaton, josta tiedät, ettei siihen kannata luottaa

Sieto on politiikkapäätös, ei oletus

Oletusvertailu on 1E-6 absoluuttinen sieto suhteellisen siedon ollessa poissa käytöstä, mikä säilyttää klassisen käytöksen ja hyväksyy hiljaa 4E-7:n ajautumisen. Tuo on yleensä oikein: liukulukuarvioinnin järjestuserot sen tuottajan ja nykyisen arvioijan välillä tuottavat kyseisen kokoisia eroja pitkissä summissa, ja niiden raportoiminen eheyslöydöksinä on kohinaa

Aseta molemmat siedot nollaan, kun kysymys on eri, kun yrität selvittää, muuttiko arvioija käytöstään versioiden välillä, tai kirjoittaako kolmannen osapuolen työkalu arvoja hienovaraisesti eri tavalla. Nollassa sama 4E-7:n ajautuminen tulee näkyviin, samoin kaikki muu. Valitse sieto sen mukaan, mitä kysymystä kysyt, ja kirjaa valinta raportin viereen, koska raportti ilman sietoaan ei ole tulkittava

Kaksi vierekkäistä kyvykkyyttä täydentävät kuvan. Kun haluat tietää, miksi yksi kaava tuottaa arvonsa, kaava-arvioinnin jäljittäjän vaihe vaiheelta -näkymä on oikea työkalu. Kun haluat tahallisesti välimuistiarvojen kunnioittamisen ilman mitään uudelleenlaskentaa, esimerkiksi sisääntulopolkua, jonka on toistettava tiedosto täsmälleen saapumisen muodossaan, tuo tila on kuvattu artikkelissa välimuistikaava-arvojen lukeminen ilman uudelleenlaskentaa. Auditointi on se, mikä istuu näiden kahden välissä: se kertoo, onko välimuistiin luottaminen turvallista. Se toimituu HotXLS Delphi -taulukkolaskentakomponentin mukana sekä binääri- että OOXML-moottorille