Excel skjuler en lille debugger i fuldt dagslys. Vælg en celle, åbn Formler, og klik på Evaluer formel, så vises en dialogboks med formlen med ét deludtryk understreget. Tryk på Evaluer, og det deluttryk kollapser til sin værdi, derefter understreges det næste, og du ser et langt udtryk skrumpe til et enkelt tal ét reduktionsskridt ad gangen. Det er den hurtigste måde at finde, hvilken gren af en indlejret IF der faktisk udløste, eller hvilken reference der fodrede et forkert total. HotXLS genskaber præcis denne adfærd via TXLSFormulaTracer, så et Delphi- eller C++Builder-program kan gengive den samme trinkliste til revision af en projektmappe, fejlretning af en genereret formel eller til at forklare nogen, hvorfor et resultat blev, som det blev. Hvert registrerede skridt bærer deludtrykkets tekst og den værdi, det reducerer til
Hvordan reduktionsmotoren gennemløber udtrykket
Traceren når ikke ind i beregnings-motoren. Den tokeniserer formlen og parser den med en rekursiv-descent-parser, derefter reducerer den træet dybde-først, inderste evaluerbare deluttryk først. Når en knude reduceres til en værdi, substitueres værdien tilbage i det omgivende udtryk som en bogstavelig værdi, og motoren beder den rigtige regner om at genberegne det nu simplere udtryk. Fordi hvert trin evalueres via regnearkets offentlige Calculate-metode frem for en privat genvej, stemmer hvert trin præcis overens med, hvad en fuld genberegning af cellen ville producere. Parseren er ikke-invasiv af design, hvilket er det, der lader den køre mod et vilkårligt regneark uden at forstyrre dets tilstand
Parseren følger en operator-forrangsstige med eet rekursivt niveau per forrangsband. Fra lavest bindende til højest er bandene: niveau 0 sammenligning (=, <>, <, >, <=, >=), niveau 1 strengsammensætning (&), niveau 2 addition og subtraktion, niveau 3 multiplikation og division, niveau 4 eksponentierung og endelig unær plus og minus nedenunder. Hvert niveau parser niveauet ovenover for sine operander, så et højere band binder stærkere. Dette er den samme forrang, Excel anvender, og det er grunden til, at A1*B1+A2*B1 reducerer de to produkter før summen: multiplikation sidder på niveau 3, addition på niveau 2, så multiplikationerne er dybere i træet og reduceres først
Spåring af en formel og gennemløbning af trinene
Brugen afspejler den medfølgende demo på Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Byg et regneark (eller åbn en eksisterende projektmappe), konstruer en tracer over arket, kald Trace, og gennemløb det returnerede array. Hvert TXLSFormulaStep eksponerer Depth til indrykning, Source for det originale deluttryk, Expression for det deluttryk med dets operander allerede substitueret, og Value for resultatet af trinnet
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;Cellereferencerne opløses først og vises som egne trin, derefter reduceres produkterne, derefter den parenteserede skattefaktor, og den afsluttende multiplikation lukker det. Feltet Depth lader dig rykke ind, så de inderste reduktioner visuelt sidder dybest, præcis som Excel understreger den inderste betegnelse før nogen ydre
Faldgruben med landestandard-fri literal
Det farligste detalje i hele dette skema er usynligt på en engelsk maskine og fejler højlydt på en tysk. Når et beregnet tal substitueres tilbage i formelens tekst, skal det skrives som en streng og derefter genparses af beregnings-motoren, der behandler . som decimalkomma. Hvis substitutionen brugte systemets landestandard, ville en tysk TFormatSettings skrive 1,08 for skattefaktoren, kommaet ville læses som et argumentseparatortegn, og genberegningen af A1*B1*1,08 ville enten parse til den forkerte form eller fejle fuldstændigt
Traceren undgår dette ved at formatere alle numeriske bogstaveligheder via en privat TFormatSettings, som den låser ved konstruktionstidspunktet, med DecimalSeparator tvunget til . og ThousandSeparator sat til #0, så intet grupperingstegn nogensinde udsendes. FloatToStr producerer derefter en bogstavelig værdi, som motoren altid kan læse tilbage uanset operatstørens regionale indstillinger
// 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)Dette er den slags fejl, der aldrig viser sig i forfatterens egne tests og kun dukker op, når en kunde i et andet landstandard kører den samme kode. Så det er værd at sige klart: at transportere en værdi frem og tilbage via formelens tekst er et serialiseringsproblem, og serialisering skal være landestandard-fri
Booleanske værdier reduceres til 1 og 0
En beslutægt substitutionsbeslutning vedrører logiske værdier. Når et deluttryk evaluerer til en boolean, skriver traceren det tilbage som 1 eller 0, ikke som TRUE eller FALSE. Grunden er, at den reducerede bogstavelig værdi skal genparse rent i den omgivende sammnhæng, og aritmetik er det krævende tilfælde. Hvis en sammenligning som A1>A2 reducerede til teksten TRUE, og den tekst landede inde i TRUE*B1, ville genberegningen afhænge af, at motoren accepterer et bart booleansk nøgleord i en multiplikation. At substituere 1 omgår spørgsmålet fuldstændigt, fordi 1*B1 er utvetydig i enhver aritmetisk position. Det matcher også Excels egen tvangskonvertering, hvor TRUE opfører sig som 1 og FALSE som 0 så snart et tal forventes
Funktionskald reduceres atomisk
En naiv trins-motor ville reducere en funktions argumenter først og derefter kaldet. Det er forkert for Excel, og traceren undlader bevidst at gøre det. Et funktionskald evalueres som en helhed, fra dets originale tekst, i eet enkelt trin. Grunden er kortslutnings-semantik. IF, CHOOSE og IFERROR evaluerer kun den gren, de vælger, og at reducere argumenter først ville tvinge motoren til at beregne grene, Excel aldrig berører. Det klassiske offer er en divider-med-nul-vagt som IF(B1=0,0,A1/B1): hvis traceren reducerede A1/B1 før evaluering af IFet, ville vagten fejle og udløse præcis den fejl, den eksisterer for at forebygge. Ved at evaluere hele kaldet atomisk bevarer traceren den dovne evaluering, der får sådanne vagter til at virke
// 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.Afvejningen er, at du ikke ser ind i funktionskaldet som separate trin, men det er den korrekte adfærd. At vise argumentreduktioner, Excel aldrig udfører, ville være et mere misvisende spår end at behandle kaldet som den enkelte evalueringsenhed, det virkelig er
Argumentseparatorer og intakte områder
To yderligere normaliseringer holder genberegningen trofast. Beregnings-motorens compiler forventer ; som funktionsargumentseparator, så når traceren genopbygger et funktionskald fra sit parsede træ, sammenføjer den argumenter med ;, selv om brugeren oprindeligt tastede ,. En formel skrevet som SUM(A1,A2,A3) genberegnes som SUM(A1;A2;A3), som motoren accepterer. Substitutionen af værdier er det, der gør denne genopbygning nødvendig, og at få separatoren rigtig er det, der gør genopbygningen parsbar
Områdereferencer er det andet tilfælde. Et område som A1:A3 er ikke en skalaris værdi og må ikke opdeles i tre separate værdier, fordi den funktion, der bruger det, forventer et områdeargument. Traceren holder et område intakt som sin originale tekst og lader den omsluttende funktion reducere som helhed. I SUM(A1:A3)*B1 forbliver området helt, SUM(A1:A3) reduceres til eet tal i eet atomisk trin, og først derefter kører den ydre multiplikation. Dette er den samme grænse, Excel drægger mellem et områdeoperand og den skalaris værdi, det i sidste ende bidrager med
// 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));Tilsammen gør disse regler trinklisten til et trofast spejl af Excels Evaluer formel-kommando frem for en tilnærmelse af den. Reduktionerne sker i den rækkefølge, Excel udfører dem, de substituerede bogstaveligheder overlever enhver landestandard, booleanske værdier konverteres, som Excel konverterer dem, og dovne funktioner forbliver dovne. Hvis du ønsker at skubbe motoren videre med dine egne funktioner, viser artiklen om formelmotor og brugerdefinerede funktioner, hvordan du registrerer dem, og for tyngre numerisk arbejde dækker artiklen om statistiske fordelingsfunktioner i Delphi det indbyggede bibliotek, traceren evaluerer mod. Alt dette leveres som del af HotXLS-regnearkskomponenten til Delphi og C++Builder, ved siden af de læse-, skrive-, formaterings- og beregnings-API'er, der er dækket andetsteds på denne blog