Excel skriva majhen razhroščevalnik na vidnem mestu. Izberite celico, odprite Formule in kliknite Ovrednoti formulo, in pogovorno okno prikaže formulo z enim podizrazom podčrtanim. Pritisnite Ovrednoti in ta podizraz se strne v svojo vrednost, nato se podčrta naslednji, in opazujete, kako dolg izraz skrči na eno samo številko, eno redukcijo naenkrat. To je najhitrejši način, da ugotovite, katera veja ugnezdenega IF se je dejansko sprožila, ali katera sklicevana vrednost je prispevala napačen seštevek. HotXLS reproducira to natančno vedenje prek TXLSFormulaTracer, tako da program Delphi ali C++Builder lahko prikaže enak seznam korakov za revizijo zvezka, razhroščevanje generirane formule ali razlago, zakaj je rezultat izšel tako, kot je izšel. Vsak zabeleženi korak nosi besedilo podizraza in vrednost, na katero se reducira
Kako motor za redukcijo hodi po izrazu
Sledilnik ne sega v kalkulator. Tokenizira formulo in jo razčleni z razčlenjevalnikom z rekurzivnim spuščanjem, nato pa drevo reducira najprej v globino -- najgloblje vrednotljivi podizraz najprej. Ko se vozlišče reducira na vrednost, se ta vrednost nadomesti nazaj v okoliški izraz kot literal, motor pa prosi pravega kalkulatorja, da preračuna zdaj preprostejši izraz. Ker se vsak korak vrednoti prek javne metode Calculate delovnega lista in ne prek zasebne bljžnjice, se vsak korak natančno ujema s tem, kar bi ustvaril celoten ponovni izračun celice. Razčlenjevalnik je zasnovan tako, da ne moti -- kar mu omogoča, da se zažene na katerem koli delovnem listu, ne da bi motil njegovo stanje
Razčlenjevalnik sledi lestvici prednosti operatorjev z eno rekurzivno ravnijo na pas prednosti. Od najšibkejšega do najtesnejšega vezanja so pasovi: raven 0 primerjava (=, <>, <, >, <=, >=), raven 1 stikanje nizov (&), raven 2 seštevanje in odštevanje, raven 3 množenje in deljenje, raven 4 potenciranje in nazadnje unarna plus in minus pod tem. Vsaka raven razčleni raven nad seboj za svoje operande, tako da se višji pas tesneje veže. To je ista prednost, ki jo uporablja Excel, kar je razlog, zakaj A1*B1+A2*B1 najprej reducira dve množitvi pred seštevanjem: množenje sedi na ravni 3, seštevanje na ravni 2, zato so množitve globlje v drevesu in reducirajo najprej
Sledenje formuli in hoja po korakih
Uporaba odraža priloženo demo pri Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Zgradite delovni list (ali odprite obstoječi zvezek), zgradite sledilnik nad listom, pokličite Trace in iterirajte vrnjeno polje. Vsak TXLSFormulaStep izpostavi Depth za zamike, Source za izvirni podizraz, Expression za ta podizraz z že nadomeščenimi operandi in 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;Sklicevanja na celice se razrešijo najprej in se pojavijo kot lastni koraki, nato se zmanjšata produkta, nato pa oklepajni davčni faktor, in zadnje množenje ga zaključi. Polje Depth vam omogoča zamik, tako da so najgloblje redukcije vidno poglobljene, natanko tako, kot Excel podčrta notranji člen pred katerim koli zunanjim
Past neodvisnih od lokalizacije literalov
Najnevarnejša podrobnost v tej shemi je nevidna na angleški napravi in glasno odpove na nemški. Ko se izračunana številka nadomesti nazaj v besedilo formule, jo je treba zapisati kot niz in nato znova razčleniti s kalkulatorskim motorjem, ki obravnava . kot decimalno vejico. Če bi nadomestitev uporabila sistemsko lokalizacijo, bi nemška TFormatSettings za davčni faktor zapisala 1,08, vejica bi bila prebrana kot ločilo argumentov in ponovni izračun A1*B1*1,08 bi se bodisi razčlenil v napačno obliko ali pa bi povsem odpovedal
Sledilnik se temu izogne tako, da vsak numerični literal oblikuje prek zasebne TFormatSettings, ki jo pritrdi ob gradnji, z DecimalSeparator prisiljenim na . in ThousandSeparator nastavljenim na #0, da se nikoli ne odda noben znak za grupiranje. FloatToStr nato ustvari literal, ki ga motor vedno zna prebrati nazaj, ne glede na regionalne nastavitve operaterja
// 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 napake, ki se nikoli ne pojavi pri testiranju avtorja in se pojavi šele, ko stranka v drugi lokalizaciji zažene isto kodo, zato je vredno jasno povedati: krožni prenos vrednosti prek besedila formule je problem serializacije in serializacija mora biti neodvisna od lokalizacije
Logične vrednosti se reducirajo na 1 in 0
Sorodna odločitev o nadomestilu zadeva logične vrednosti. Ko se podizraz vrednoti kot logična vrednost, ga sledilnik zapiše nazaj kot 1 ali 0, ne kot TRUE ali FALSE. Razlog je v tem, da se mora nadomestni literal znova razčleniti čisto v katerem koli kontekstu, ki ga obdaja, in aritmetika je zahteven primer. Če bi primerjava kot A1>A2 reducirala na besedilo TRUE in to besedilo pristalo znotraj TRUE*B1, bi bil ponovni izračun odvisen od tega, ali motor sprejme golo logično ključno besedo v množenju. Nadomestitev z 1 to vprašanje povsem obvozi, ker je 1*B1 nedvoumno na vsakem aritmetičnem mestu. Ujema se tudi z lastno prisilitvijo Excela, kjer se TRUE obnaša kot 1 in FALSE kot 0 v trenutku, ko je pričakovana številka
Klici funkcij se reducirajo atomarno
Naivni koračni motor bi najprej reduciral argumente funkcije in nato klic. To je za Excel napačno in sledilnik tega namerno ne počne. Klic funkcije se vrednoti kot celota, iz svojega izvornega besedila, v enem samem koraku. Razlog je kratičnostna semantika. IF, CHOOSE in IFERROR vrednotijo le vejo, ki jo izberejo, in predhodno reduciranje argumentov bi prisililo motor, da izračuna veje, ki jih Excel nikoli ne dotakne. Klasična žrtev je varovalka pred deljenjem z ničlo, kot je IF(B1=0,0,A1/B1): če bi sledilnik reduciral A1/B1 pred vrednotenjem IF, bi varovalka odpovedala in sprožila prav tisto napako, ki jo preprečuje. Z vrednotenjem celotnega klica atomarno sledilnik ohrani leno vrednotenje, ki takšne varovalke dela
// 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, da ne vidite v notranjosti klica funkcije kot ločenih korakov, a to je pravilno vedenje. Prikazovanje redukcij argumentov, ki jih Excel nikoli ne izvede, bi bil bolj zavajajoč sled, kot pa obravnavanje klica kot enote vrednotenja, ki dejansko je
Ločila argumentov in neokrnjeni obsegi
Dve dodatni normalizaciji sta potrebni za ohranitev pravilnosti ponovnega izračuna. Prevajalnik kalkulatorskega motorja pričakuje ; kot ločilo argumentov funkcije, zato ko sledilnik obnovi klic funkcije iz svojega razčlenjenega drevesa, argumente združi z ;, četudi je uporabnik prvotno vtipkal ,. Formula, napisana kot SUM(A1,A2,A3), se znova izračuna kot SUM(A1;A2;A3), kar motor sprejme. Nadomestitev vrednosti je tisto, kar naredi to obnovo potrebno, in pravilno ločilo je tisto, kar naredi obnovo razčlenljivo
Obsegi so drug primer. Obseg kot A1:A3 ni skalar in ne sme biti razdeljen na tri ločene vrednosti, ker funkcija, ki ga porabi, pričakuje argument obsega. Sledilnik ohrani obseg nedotaknjen kot izvorno besedilo in pusti, da se priložena funkcija reducira kot celota. V SUM(A1:A3)*B1 obseg ostane cel, SUM(A1:A3) se v enem atomarnem koraku reducira na eno številko in šele nato se zažene zunanje množenje. To je ista meja, ki jo Excel postavlja med operand obsega in skalar, ki ga ta 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 zvesten odraz Excelovega ukaza Ovrednoti formulo in ne samo njegovo approximacijo. Redukcije se dogajajo v vrstnem redu, v katerem jih Excel izvaja, nadomestni literali preživijo katero koli lokalizacijo, logične vrednosti se prisilijo tako, kot jih prisili Excel, in lene funkcije ostanejo lene. Če želite motor še naprej razširiti z lastnimi funkcijami, članek o formularnem motorju in funkcijah po meri prikazuje, kako jih registrirati, za zahtevnejše numerično delo pa članek o statističnih porazdelitvenih funkcijah v Delphiju pokriva vgrajeno knjižnico, ki jo sledilnik vrednoti. Vse skupaj se dobavi kot del komponente HotXLS za Delphi in C++Builder, skupaj z API-ji za branje, pisanje, oblikovanje in izračun, obravnavanimi drugje na tem blogu