Excel formulių podėlių auditas su HotXLS Deep Recalc

HotXLS atsako į klausimą, kurį kiekvienas skaičiuoklės konvejeris galiausiai turi užduoti: ar darbo knygoje saugomi skaičiai vis dar atitinka formules, kurias juos pagamino. CalculateAndVerify perskaičiuoja visą priklausomybių grafą į izoliuotą overlay, palygina kiekvieną rezultatą su podėlio reikšme, jau esančia langelyje, ir praneša nesutapimus. Pagal nutylėjimą jis nieko nekeičia

Kodėl tai svarbu: skaičiuoklės failas saugo du dalykus kiekvienam formulės langeliui – formulę ir paskutinę reikšmę, kurią kam nors jai skaičiuoti teko. Excel laiko juos sinchronizuotus. Viskas kiti pasaulyje – nebūtinai. Failas, perėjęs per senesnę biblioteką, dalinį perskaičiavimą, rankomis redaguotą XML dalį ar įrankį, rašiusį reikšmes neperskaičiavus jų, mielai pateiks sumą, kuri daugiau nebeseka iš savo įėjimų, ir niekas failo formate to nepažymi

Kodėl podėlio reikšmė, nesutampanti su savo formule, tokia pavojinga?

Nes ji nematoma kiekviename įprastame skaitymo kelyje. Atverkite failą peržiūroklėje, perskaitykite langelį per API, eksportuokite į CSV ar PDF, ir gausite podėlio skaičių. Formulė ten pat tame pačiame langelyje, ir nė kas jų nepalygina. Nesutapimas iškyla tik tada, kai kas nors atveria darbo knygą Excel, kuris pagal daugumą nustatymų perskaičiuoja pakraudamas, ir staiga pranešimas, patvirtintas pernai ketvirtį, rodo kitokias sumas

Auditas egzistuoja tam, kad tas palyginimas būtų sąmoninga, suplanuota operacija, o ne atsitiktinumas. Tai skaičiuoklės atitikmuo kontrolinės sumos patikrinimui: pigu pakankamai, kad bėgtų priėmimo konvejeryje, ir vienintelis dalykas, pavertęs tylią duomenų vientisumo problemą pranešimu, kuriuo galima remtis

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;

Yra trys perkrajos ir jos atsako į tris skirtingus klausimus. Beparametrė CalculateAndVerify grąžina nesutapimų skaičių – to ir užtenka sveikatos patikrai. Perkraja su out masyvu nesutapimų duoda jums langelius. Perkraja, imanti TXLSRecalcAuditOptions, grąžina pilną TXLSCalculationAuditReport – ją ir imkite, kai reikia žinoti ne tik tai, kad reikšmė nesutampa, bet ir kodėl auditas negalėjo kažko įvertinti

Overlay ir kodėl auditas nerašo

Kiekviena perskaičiuota reikšmė nusileidžia į overlay, o ne į langelio podėlį, ir overlay įšvirkščiamas pačiame langelio skaitymo atgalinio iškvietimo priekyje abiejuose darbo knygų varikliuose. Ta padėtis ir daro auditą savaime suderinamą: kai B1 perskaičiuojama, o C1 priklauso nuo B1, C1 mato šio audito perėjimo reikšmę, o ne pasenusią podėlio. Be to, viena aukštyn kylančio klaida būtų pranešta kartą ir tada sugerta, ir kiekvienas žemyn esantis langelis atrodytų sutampantis su neteisinga įvestimi

Langeliai, kurių perskaičiuota reikšmė sutampa su podėliu, į overlay neįeina visai. Tai ne mikrooptimizacija – tai tai, kas laiko auditą prieinamą. Švari darbo knyga su šimtu tūkstančių formulių atlieka nulį overlay rašymų, o perėjimas lieka 1.35x biudžeto viduje prieš pilną perskaičiavimą, kas yra skirtumas tarp to, ką gali bėgti kiekvieną priėmimą, ir to, ką bėgi kartą per ketvirtį

Vertinimas seka nuoseklią topologinę tvarką, išvestą iš priklausomybių grafo, su kiekvienu mazgu, pirmiausia pažymėtu dirty, tad kiekvienas langelis skaičiuojamas tiksliai kartą po savų įėjimų. Jei norite inkrementinės mašinos, laikančios gyvą darbo knygą dabartine, vietoj saugomos audito, tai kitas mechanizmas, aprašytas straipsnyje inkrementinis perskaičiavimas ir priklausomybių grafas

Nesėkmės klasifikuojamos, o ne metamos į vieną krūvą

Langelis, kurio auditas negali įvertinti, nėra tas pats radinys, kaip langelis, kurio reikšmė nesutampa, ir TXLSCalculationAuditIssueKind laiko kategorijas atskirtas. xlcaiCacheMismatch yra reikšmės nesutapimas. xlcaiMissingFunction ir xlcaiMissingName sako, kad vertintojas susitiko kažką, ko jis nerealizuoja arba negali išspręsti. xlcaiUnsupportedArguments dengia argumento formas už palaikomo poaibio. xlcaiExternalReferenceDenied ir xlcaiExternalReferenceMissing atskiria politikos atsisakymą nuo dingusios darbo knygos. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled ir xlcaiInternalFailure užbaigia rinkinį

Vienas skirtumas vertas išsakymo, nes jis apverčia dažną prielaidą. Teigiamas Excel klaidos kodas yra rezultatas, o ne nesėkmė. Langelis, teisėtai įvertintas į #DIV/0!, perskaičiavo teisingai, tad auditas tą klaidą saugo overlay ir lygina su podėliu kaip bet kurią kitą reikšmę. Darbo knyga, pilna sąmoningų klaidos langelių, duoda nulį radinių, o darbo knyga, kurioje klaida atsirado ar dingo, kai reikšmės buvo padėtos į podėlį, duoda tiksliai tuos radinius, kurių norite

Žiedinės nuorodos gauna savąjį tvarkymą. Mazgai cikle niekada neįeina į topologinę tvarką, tad kiekvienas pranešamas atskirai kaip xlcaiCircularReference, ir auditas nebėga iteratyvaus sprendėjo. Tai sąmoninga tik skaitoma sutartis: tai, ar iteracija įjungta, veikia tai, kaip rezultato kodą reikia aiškinti, o ne tai, ką daro auditas. Iteratyvaus vertinimo mechanika atskirai aprašyta straipsnyje iteratyvus skaičiavimas ir žiedinės nuorodos

Nesėkmės grandinės skaitymas

Kai formulė nepajėgia įvertinti, žinoti, kuris langelis nepajėgė, retai užtenka, nes nesėkmė paprastai trys lygiai žemiau nuorodų grandinėje. Kiekvienas radinys todėl neša Stack eilutę, atvaizduotą išoriniu rėmeliu pirmiausia, pavidalu Sheet1!A1 > Sheet1!B2 > Data!C7, tad pranešimas rodo į langelį, kuris tikrai sulūžo, o ne į langelį, kurį atsitiktinai žiūrėjote

Įrašinėtojas yra apibrėžtas. MaxStackFrames pagal nutylėjimą 64 su žeme ties 8, ir giliausia nesėkminga grandinė yra ta, kuri išlaikoma: vidinis rėmelis užrašo grandinę, kai nesėkmė ten kyla, o išoriniai rėmeliai, vėliau atsivyniojantys, jos neperrašo. Jei bet kuri grandinė viršijo biudžetą, Report.StackTruncated nustatomas, kas pasako skirtumą tarp trumpo grandinės ir grandinės, kurios nematėte visos

// Pagal nutylėjimą tik skaitoma. ApplyResults įpareigoja overlay tik po
// visiškai sėkmingo audito, su rašymo sargu, atmetančiu įpareigojimą, jei
// darbo knygos struktūra pasikeitė, kol auditas bėgo
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // tikslus palyginimas, iškelia dreifą
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;   // auditas sustoja prie kito mazgo ribos
end;

Kada verta leisti auditui sutvarkyti darbo knygą?

Tik tada, kai auditas grįžo visiškai švarus nuo nesėkmių klasės radinių, ir būtent tą sąlygą ApplyResults jums priverčia. Įpareigojimas vyksta po visiškai sėkmingo perėjimo, nebuvo atšauktas, ir praeina struktūrinį sargą: dvinarinis variklis stebi darbo knygos pasikeitimo identifikatorių, OOXML variklis fotografuoja kiekvieno darbalapio struktūros kartą. Jei kas nors pajudėjo, kol auditas bėgo, rezultatai aprašo darbo knygą, kurios nebėra, ir įpareigojimas atmetamas

Pastebėkite sąmoningą asimetriją. Podėlio nesutapimai neužblokuoja taikymo, nes jie yra tiksliai tai, ką įpareigojimas yra ten sutvarkyti. Nesėkmių klasės radiniai jį blokuoja, nes darbo knyga, kurioje kai kurios formulės negalėjo būti įvertintos, būtų pusiau sutvarkyta, o pusiau sutvarkyta darbo knyga yra blogiau už nesutvarkytą, kurios žinote nepasitikėti

Leistina paklaida yra politikos sprendimas, o ne numatytasis

Numatytasis palyginimas yra 1E-6 absoliuti paklaida su išjungta reliatyviąja paklaida, kas išsaugo klasikinį elgesį ir tyliai priima 4E-7 dreifą. Tai paprastai teisinga: slankiojo kablelio vertinimo tvarkos skirtumai tarp to, kas pagamino failą, ir dabartinio vertintojo duos to dydžio skirtumus ilgose sumose, ir jų pranešimas kaip vientisumo radinių yra triukšmas

Nustatykite abi paklaidas į nulį, kai klausimas kitoks: kai bandote išsiaiškinti, ar vertintojas pasikeitė elgesiu tarp versijų, ar trečiosios šalies įrankis perrašo reikšmes subtiliai kitokiu būdu. Ties nuliu tas pats 4E-7 dreifas pasidaro matomas, ir viskas kita taip pat. Rinkitės paklaidą pagal tai, kokį klausimą užduodate, ir užrašykite pasirinkimą šalia pranešimo, nes pranešimas be savo paklaidos nėra aiškinamas

Dvi gretimos galimybės užbaigia paveikslą. Kai norite žinoti, kodėl viena formulė pagamina tą reikšmę, kurią pagalba, žingsnis po žingsnio vaizdas straipsnyje formulės vertinimo seklys yra tinkamas įrankis. Kai sąmoningai norite, kad podėlio reikšmės būtų gerbiamos be jokio perskaičiavimo, pavyzdžiui, priėmimo kelyje, kuris turi atkurti failą tiksliai taip, kaip jis atėjo, tas režimas aprašytas straipsnyje podėlio formulės reikšmių skaitymas be perskaičiavimo. Auditas yra tai, kas sėdi tarp tų dviejų: jis pasako, ar podėliu pasitikėti saugu. Jis keliauja su HotXLS Delphi skaičiuoklės komponentu abiem dvinariam ir OOXML varikliams