Revizija predpomnilnikov formul Excel s HotXLS Deep Recalc

HotXLS odgovori na vprašanje, ki ga mora vsak preglednični cevovod sčasoma zastaviti: ali se številke, shranjene v delovni knjigi, še ujemajo s formulami, ki so jih proizvedle. CalculateAndVerify preračuna celoten graf odvisnosti v izolirano prekrivanje, vsak rezultat primerja s predpomnjeno vrednostjo, ki je že v celici, in sporoči nesoglasja. Privzeto ne spremeni nič

Razlog, zakaj to šteje, je, da datoteka preglednice shranjuje dve stvari na formulo celice: formulo in zadnjo vrednost, ki jo je kdo zanjo izračunal. Excel ju drži usklajena. Vse ostalo na svetu pa morda ne. Datoteka, ki je šla skozi starejšo knjižnico, delno preračunanje, ročno urejen del XML ali orodje, ki je zapisalo vrednosti brez preračuna, bo z veseljem prikazala vsoto, ki iz njenih vhodov ne sledi več — in nič v formatu datoteke tega ne označi

Zakaj je predpomnjena vrednost, ki se ne ujema s formulo, tako nevarna?

Ker je nevidna na vsaki navadni bralni poti. Datoteko odprete v pregledovalniku, celico preberete skozi API, izvozite v CSV ali PDF — in dobite predpomnjeno številko. Formula je prav tam v isti celici in nihče jih ne primerja. Neskladje pride na dan šele, ko nekdo odpre delovno knjigo v Excelu, ki se ob večini nastavitev ob nalaganju preračuna, in naenkrat poročilo, odobreno lansko četrtletje, prikazuje drugačne vsote

Revizija obstaja zato, da je ta primerjava premišljena, načrtovana operacija in ne nesreča. To je preglednična ustreznica preverjanja kontrolne vsote: dovolj poceni, da teče v sprejemnem cevovodu, in edina stvar, ki tiho problem celovitosti podatkov spremeni v poročilo, na katerem lahko ukrepate

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;

Obstajajo tri preobremenitve in odgovarjajo na tri različna vprašanja. CalculateAndVerify brez parametrov vrne število neskladij, kar je vse, kar potrebuje zdravstveni pregled. Preobremenitev z out seznamom neskladij vam da celice. Preobremenitev, ki sprejme TXLSRecalcAuditOptions, vrne poln TXLSCalculationAuditReport — tista, po kateri segate, ko morate vedeti ne le, da se vrednost ne ujema, ampak tudi zakaj revizija česa ni mogla ovrednotiti

Prekrivanje in zakaj revizija ne piše

Vsaka preračunana vrednost pristane v prekrivanju in ne v predpomnilniku celic, prekrivanje pa je vbrizgano na samem začetku povratnega klica branja celic v obeh motorjih delovnih knjig. Ta umestitev naredi revizijo samoskladno: ko se B1 preračuna in C1 visi od B1, C1 vidi vrednost iz tega revizijskega prehoda in ne zastarele predpomnjene. Brez tega bi se ena sama napaka na zgornjem toku sporočila enkrat in se nato absorbirala, vsaka celica na spodnjem toku pa bi se zdelo, da se strinja z napačnim vhodom

Celice, katerih preračunana vrednost se ujema s predpomnilnikom, v prekrivanje sploh ne zaidejo. To ni mikro-optimizacija, to je tisto, kar revizijo naredi plačljivo. Čista delovna knjiga s sto tisoč formulami izvede nič pisanij v prekrivanje in prehod ostane znotraj proračuna 1.35-krat proti polnemu preračunu — razlika med stvarjo, ki jo lahko poganjate na vsakem sprejemu, in stvarjo, ki jo poganjate enkrat na četrtletje

Vrednotenje sledi serijskemu topološkemu vrstnemu redu, izpeljanemu iz grafa odvisnosti, vsako vozlišče najprej označeno kot umazano, tako da se vsaka celica izračuna točno enkrat po svojih vhodih. Če želite inkrementalno mehaniko, ki živo delovno knjigo drži aktualno, namesto da bi revizirali shranjeno, je to drugačen mehanizem, opisan v inkrementalnem preračunu in grafu odvisnosti

Spodleteli so razvrščeni, ne zgrntani skupaj

Celica, ki je revizija ne more ovrednotiti, ni isto odkritje kot celica, katere vrednost se ne ujema, TXLSCalculationAuditIssueKind pa drži kategorije narazen. xlcaiCacheMismatch je neskladje vrednosti. xlcaiMissingFunction in xlcaiMissingName pravita, da je vrednotitelj srečal nekaj, česar ne implementira ali ne more razrešiti. xlcaiUnsupportedArguments pokriva oblike argumentov izven podprte podmnožice. xlcaiExternalReferenceDenied in xlcaiExternalReferenceMissing ločita politično zavrnitev od odsotne delovne knjige. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled in xlcaiInternalFailure dopolnijo nabor

Ena razlika si zasluži, da se izreče, ker obrne pogosto predpostavko. Pozitivna koda napake Excel je rezultat, ne spodletel. Celica, ki legitimno ovrednoti na #DIV/0!, se je izračunala pravilno, zato revizija to napako shrani v prekrivanje in jo primerja s predpomnilnikom kot katero koli drugo vrednost. Delovna knjiga polna namernih celic napak proizvede nič odkritij, delovna knjiga, kjer se je napaka pojavila ali izginila, odkar so bile vrednosti predpomnjene, pa proizvede točno tista odkritja, ki jih želite

Krožni sklici dobijo svojo obravnavo. Vozlišča v ciklu nikoli ne zaidejo v topološki vrstni red, zato se vsako sporoči posamično kot xlcaiCircularReference, revizija pa ne poganja iterativnega reševalnika. To je namerna pogodba samo-za-branje: ali je iteracija vklopljena, vpliva na to, kako naj se koda rezultata razlaga, ne na to, kaj revizija stori. Mehanika iterativnega vrednotenja je obravnavana ločeno v iterativnem računanju in krožnih sklicih

Branje verige spodletelih

Ko formula spodleti pri vrednotenju, je vedeti, katera celica je spodletela, redko dovolj, ker je spodletel običajno tri ravni navzdol po verigi sklicev. Vsako odkritje zato nosi niz Stack, izrisan z najbolj zunanjim okvirjem najprej, v obliki Sheet1!A1 > Sheet1!B2 > Data!C7, tako da poročilo kaže na celico, ki je dejansko počila, in ne na celico, na katero ste slučajno pogledali

Snemalnik je omejen. MaxStackFrames je privzeto 64 s tlemi 8 in najgloblja veriga spodletelih je tista, ki se obdrži: notranji okvir zabeleži verigo, ko tam izvira spodletel, zunanji okvirji, ki se odvijajo kasneje, je ne prepišejo. Če je katera koli veriga presegla proračun, se nastavi Report.StackTruncated, kar vam pove razliko med kratko verigo in verigo, katere vsega niste videli

// Privzeto samo za branje. ApplyResults potrdi prekrivanje šele po
// popolnoma uspešni reviziji, pod pisalnim stražarjem, ki potrditev
// zavrne, če se struktura delovne knjige med revizijo spremeni
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // točna primerjava, razkrije zdrsnitev
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;   // revizija se ustavi na naslednji meji vozlišč
end;

Kdaj naj reviziji dovolite popraviti delovno knjigo?

Šele, ko je revizija prišla nazaj popolnoma čista vrst spodletelih — natanko pogoj, ki vam ga uveljavi ApplyResults. Potrditev se zgodi po popolnoma uspešnem prehodu, ki ni bil preklican, in prestane strukturni stražar: binarni motor opazuje identifikator spremembe delovne knjige, motor OOXML posname generacijo strukture na posamezen delovni list. Če se je karkoli premaknilo, medtem ko je revizija tekla, rezultati opisujejo delovno knjigo, ki ne obstaja več, in potrditev je zavrnjena

Opazite namerno asimetrijo. Neskladja predpomnilnika ne blokirajo uporabe, ker so natanko tisto, kar je potrditev tu, da popravi. Vrste spodletelih jo blokirajo, ker bi bila delovna knjiga, kjer nekaterih formul ni bilo mogoče ovrednotiti, pol popravljena — pol popravljena delovna knjiga pa je slabša od nepopravljene, za katero veste, da ji ne zaupate

Toleranca je odločitev politike, ne privzetek

Privzeta primerjava je absolutna toleranca 1E-6 z izključeno relativno toleranco, kar ohranja klasično vedenje in tiho sprejme zdrsnitev 4E-7. To je običajno prav: razlike v vrstnem redu plavajočega vrednotenja med tistim, kar je datoteko proizvedlo, in trenutnim vrednotiteljem bodo na dolgih vsotah dale razlike te velikosti, njihovo sporočanje kot odkritij celovitosti pa je šum

Obe toleranci nastavite na nič, ko je vprašanje drugačno — ko poskušate izvedeti, ali je vrednotitelj med verzijami spremenil vedenje, ali pa tretjeosebno orodje prepisuje vrednosti na subtilno drugačen način. Pri nič isti zdrsnitev 4E-7 postane vidna in vse ostalo tudi. Toleranco izberite glede na vprašanje, ki ga zastavljate, izbiro pa zabeležite poleg poročila, ker poročilo brez svoje tolerance ni razumljivo

Dve sosednji zmožnosti dopolnita sliko. Ko želite vedeti, zakaj ena sama formula proizvede vrednost, ki jo proizvaja, je pravo orodje pogled korak-za-korakom v sledilniku vrednotenja formul. Ko namerno želite, da se predpomnjene vrednosti spoštujejo brez kakršnega preračuna — na primer na sprejemni poti, ki mora datoteko reproducirati točno takšno, kot je prispela — je ta način opisan v brananju predpomnjenih vrednosti formul brez preračuna. Revizija je tisto, kar sede med njima: pove vam, ali je zaupanje predpomnilniku varno. Pride z HotXLS Delphi spreadsheet component za oba motorja, binarnega in OOXML