Sledenje ovrednotenju Excelovih formul korak za korakom v Delphiju

Excel skriva majhen razhroščevalnik pred našimi očmi. Izberite celico, odprite zavihek Formule in kliknite Ovrednoti formulo. Prikaže se pogovorno okno s formulo, v kateri je en podizraz podčrtan. Kliknite Ovrednoti in ta podizraz se skrči v svojo vrednost, nato se podčrta naslednji in tako opazujete, kako se dolg izraz zmanjšuje do ene same številke, redukcijo za redukcijo. To je najhitrejši način, da ugotovite, katera veja vgnezdene funkcije IF se je dejansko sprožila ali katera referenca je napajala napačno vsoto. HotXLS poustvari to natančno vedenje prek razreda TXLSFormulaTracer, tako da lahko program v Delphiju ali C++Builderju izriše enak seznam korakov za revizijo delovnega zvezka, iskanje napak v generirani formuli ali učenje nekoga, zakaj je rezultat takšen, kot je. Vsak zabeležen korak nosi besedilo podizraza in vrednost, v katero se ta skrči

Sledilnik ne posega v računski mehanizem. Formulo razčleni na žetone in jo analizira z razčlenjevalnikom z rekurzivnim spustom (recursive-descent parser), nato pa drevo reducira po globini (depth-first), pri čemer najprej vzame najgloblji ovrednotljivi podizraz. Ko se vozlišče reducira v vrednost, se ta vrednost nadomesti nazaj v okoliški izraz kot literal, mehanizem pa od pravega kalkulatorja zahteva ponovni izračun zdaj preprostejšega izraza. Ker se vsak korak ovrednoti prek javne metode Calculate delovnega lista in ne prek privatne bližnjice, se vsak korak natančno ujema s tem, kar bi proizvedel celoten ponovni izračun celice. Razčlenjevalnik je po zasnovi neinvaziven, kar mu omogoča delovanje na katerem koli delovnem listu, ne da bi motil njegovo stanje

Kako mehanizem za redukcijo prehodi izraz

Razčlenjevalnik sledi lestvici prednosti operatorjev z eno rekurzivno ravnjo na pas prednosti. Od najnižje do najvišje vezave so pasovi naslednji: stopnja 0 primerjava (=, <>, <, >, <=, >=), stopnja 1 spajanje nizov (&), stopnja 2 seštevanje in odštevanje, stopnja 3 množenje in deljenje, stopnja 4 potenciranje ter na koncu unarni plus in minus pod tem. Vsaka stopnja razčleni stopnjo nad njo za svoje operande, so višji pas veže močneje. To je enaka prednost, kot jo uporablja Excel, zato A1*B1+A2*B1 reducira produkta pred vsoto: množenje je na stopnji 3, seštevanje pa na stopnji 2, zato so množenja globlje v drevesu in se reducira najprej

Sledenje formuli in prehod skozi korake

Uporaba zrcali priloženo predstavitev na poti Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Zgradite delovni list (ali odprite obstoječ delovni zvezek), ustvarite sledilnik nad listom, pokličite Trace in iterirajte vrnjeno matriko. Vsak TXLSFormulaStep izpostavlja Depth za zamik, Source za prvotni podizraz, Expression za ta podizraz z že nadomeščenimi operandi ter Value za rezultat koraka

uses
  SysUtils, Variants, lxHandle, lxHandleX, lxFormulaTrace;

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Tracer: TXLSFormulaTracer;
  Steps: TXLSFormulaStepArray;
  Final: Variant;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Order');
    Sheet.Cells[1, 1].Value := 10;    // A1 units
    Sheet.Cells[1, 2].Value := 25;    // B1 unit price
    Sheet.Cells[1, 3].Value := 0.08;  // C1 tax rate

    Tracer := TXLSFormulaTracer.Create(Sheet);
    try
      Final := Tracer.Trace('A1*B1*(1+C1)', Steps);
      for I := 0 to High(Steps) do
        Writeln(StringOfChar(' ', Steps[I].Depth * 2),
                Steps[I].Source, ' -> ', Steps[I].Expression,
                ' = ', VarToStr(Steps[I].Value));
      Writeln('result = ', VarToStr(Final));
    finally
      Tracer.Free;
    end;
  finally
    Book.Free;
  end;
end;

Najprej se razrešijo reference celic in se prikažejo kot samostojni koraki, nekatere pa se reducirajo produkti, zatem faktor davka v oklepaju, končno množenje pa vse skupaj zaključi. Polje Depth vam omogoča zamik, tako da najgloblje redukcije vidno sedijo najgloblje, natanko tako, kot Excel podčrta najgloblji izraz pred katerim koli zunanjim

Past literalov brez lokalnih nastavitev

Najbolj nevarna podrobnost v celotni shemi je nevidna na angleškem računalniku in se glasno pokvari na nemškem. Ko se izračunana številka nadomesti nazaj v besedilo formule, mora biti zapisana kot niz in nato ponovno razčlenjena s strani računskega mehanizma, ki obravnava . kot decimalno piko. Če bi nadomeščanje uporabilo sistemske lokalne nastavitve, bi nemški TFormatSettings zapisal 1,08 za davčni faktor, veja z vejico pa bi se prebrala kot ločilo argumentov, ponovni izračun A1*B1*1,08 pa bi se bodisi razčlenil v napačno obliko bodisi popolnoma spodletel

Sledilnik se temu izogne tako, da vsak numerični literal oblikuje prek privatnega TFormatSettings, ki ga določi ob ustvarjanju, pri čemer je DecimalSeparator prisiljen v ., ThousandSeparator pa je nastavljen na #0, tako da se znak za ločevanje tisočic nikoli ne izda. FloatToStr nato proizvede literal, ki ga mehanizem lahko vedno prebere nazaj, ne glede na regionalne nastavitve uporabnika

// Conceptually what the tracer pins once, at construction
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// every reduced number is written with: FloatToStr(Double(V), FFloatFmt)

To je vrsta hrošča, ki se nikoli ne pojavi pri lastnem testiranju avtorja in se pokaže šele, ko stranka z drugimi lokalnimi nastavitvami zažene isto kodo, zato je vredno povedati odkrito: prenos vrednosti skozi besedilo formule je problem serializacije, serializacija pa mora biti brez lokalnih nastavitev

Logične vrednosti se reducirajo v 1 in 0

Sorodna odločitev o nadomeščanju se nanaša na logične vrednosti. Ko se podizraz ovrednoti v logično vrednost, jo sledilnik zapiše nazaj kot 1 ali 0 in ne kot TRUE ali FALSE. Razlog je v tem, da se mora reducirani literal čisto ponovno razčleniti v kakršnem koli kontekstu, ki ga obdaja, aritmetika pa je zahteven primer. Če bi se primerjava, kot je A1>A2, reducirala v besedilo TRUE in bi to besedilo pristalo znotraj TRUE*B1, bi bil ponovni izračun odvisen od tega, ali mehanizem sprejme golo logično ključno besedo v množenju. Nadomeščanje z 1 se temu vprašanju popolnoma izogne, saj je 1*B1 enoznačen v vsakem aritmetičnem položaju. Ujema se tudi z Excelovo lastno prisilo, kjer se TRUE obnaša kot 1 in FALSE kot 0 takoj, ko se pričakuje število

Klici funkcij se reducirajo atomarno

Naiven mehanizem korakov bi najprej reduciral argumente funkcije in šele nato klic. To je za Excel napačno in sledilnik tega namerno ne počne. Klic funkcije se ovrednoti kot celota iz prvotnega besedila v enem samem koraku. Razlog je semantika kratkega stika. Funkcije IF, CHOOSE in IFERROR ovrednotijo le tisto vejo, ki jo izberejo, reduciranost argumentov najprej pa bi prisilila mehanizem v preračunavanje vej, ki se jih Excel sploh ne dotakne. Klasična žrtev je zaščita pred deljenjem z nič, kot je IF(B1=0,0,A1/B1): če bi sledilnik reduciral A1/B1 pred vrednotenjem IF, bi zaščita odpovedala in sprožila točno tisto napako, zaradi katere sploh obstaja. Z atomarnim vrednotenjem celotnega klica sledilnik ohranja lenobno vrednotenje (lazy evaluation), zaradi katerega takšne zaščite sploh delujejo

// IF is one atomic step; only the selected branch is evaluated
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 is true, so the step records A1*B1 as the chosen result;
// A2*B1 is never computed, exactly as Excel would do it.

Kompromis je v tem, da ne vidite notranjosti klica funkcije kot ločenih korakov, vendar je to pravilno obnašanje. Prikazovanje redukcij argumentov, ki jih Excel nikoli ne izvede, bi bila bolj zavajajoča sled kot obravnavanje klica kot ene same enote vrednotenja, kar v resnici tudi je

Ločila argumentov in nedotaknjeni obsegi

Še dve normalizaciji skrbita za točnost ponovnega izračuna. Prevajalnik računskega mehanizma pričakuje ; kot ločilo argumentov funkcije, zato sledilnik ob obnovitvi klica funkcije iz njegovega analiziranega drevesa združi argumente z ;, četudi je uporabnik prvotno vpisal ,. Formula, zapisana kot SUM(A1,A2,A3), se ponovno izračuna kot SUM(A1;A2;A3), kar mehanizem sprejme. Nadomeščanje vrednosti dela to obnovo potrebno, pravilno ločilo pa poskrbi, da se obnova uspešno razčleni

Reference obsegov so drug primer. Obseg, kot je A1:A3, ni skalar in se ne sme razdeliti na tri ločene vrednosti, saj funkcija, ki ga porabi, pričakuje argument obsega. Sledilnik ohrani obseg nedotaknjen kot njegovo prvotno besedilo in pusti, da se obkrožujoča funkcija reducira kot celota. V izrazu SUM(A1:A3)*B1 obseg ostane cel, SUM(A1:A3) se reducira v eno številko v enem atomarnem koraku, šele nato pa se izvede zunanje množenje. To je enaka meja, kot jo Excel potegne med operandom obsega in skalarjem, ki ga na koncu prispeva

// The range A1:A3 is never split; SUM is one atomic reduction,
// then the product with B1 reduces on top of it.
Final := Tracer.Trace('SUM(A1:A3)*B1', Steps);
for I := 0 to High(Steps) do
  Writeln(Steps[I].Source, ' = ', VarToStr(Steps[I].Value));

Skupaj ta pravila naredijo seznam korakov za zvesto zrcalo Excelovega ukaza Ovrednoti formulo in ne le za njegov približek. Redukcije se zgodijo v vrstnem redu, kot jih izvaja Excel, nadomeščeni literali preživijo katero koli lokalno nastavitev, logične vrednosti se prisilijo na način, kot jih prisili Excel, lenobne funkcije pa ostanejo lenobne. Če želite mehanizem še bolj razširiti z lastnimi funkcijami, članek o mehanizmu za formule in funkcijah po meri opisuje, kako jih registrirati, za zahtevnejše numerično delo pa članek o statističnih porazdelitvenih funkcijah v Delphiju pokriva vgrajeno knjižnico, glede na katero sledilnik vrednoti. Vse to se prinaša kot del komponente za preglednice HotXLS spreadsheet component za Delphi in C++Builder, skupaj z API-ji za branje, pisanje, oblikovanje in računanje, obravnavanim drugje na tem blogu