Spor evaluering av Excel-formler trinn for trinn i Delphi

Excel skjuler en liten feilsøker i fullt dagslys. Velg en celle, åpne Formler og klikk Evaluer formel, så viser en dialogboks formelen med ett underuttrykk understreket. Trykk Evaluer, og det underuttrykket kollapser til sin verdi, deretter understrekes det neste, og du ser et langt uttrykk krympe til et enkelt tall én reduksjon om gangen. Det er den raskeste måten å finne ut hvilken gren av en nestet IF som faktisk ble utløst, eller hvilken referanse som matet en feil total. HotXLS reproduserer nøyaktig den atferden gjennom TXLSFormulaTracer, slik at et Delphi- eller C++Builder-program kan gjengi den samme trinnlisten for å revidere en arbeidsbok, feilsøke en generert formel, eller lære noen hvorfor et resultat ble som det ble. Hvert registrerte trinn bærer underuttrykksteksten og verdien det reduseres til

Hvordan reduksjonsmotoren går gjennom uttrykket

Sporeren griper ikke inn i beregningsmotoren. Den tokeniserer formelen og analyserer den med en rekursiv-nedstigende parser, og reduserer deretter treet dybde-først, innerste evaluerbare underuttrykk først. Når en node reduseres til en verdi, settes den verdien tilbake inn i det omkringliggende uttrykket som en literal, og motoren ber den virkelige kalkulatoren om å beregne det nå enklere uttrykket på nytt. Fordi hvert trinn evalueres gjennom regnearkets offentlige Calculate-metode snarere enn en privat snarvei, stemmer hvert trinn nøyaktig overens med hva en fullstendig omberegning av cellen ville produsere. Parseren er ikke-invasiv av design, noe som lar den kjøre mot ethvert regneark uten å forstyrre tilstanden

Parseren følger en operatør-presedensstige, med ett rekursivt nivå per presedensbånd. Fra laveste binding til høyeste er båndene: nivå 0 sammenligning (=, <>, <, >, <=, >=), nivå 1 strengsammenføyning (&), nivå 2 addisjon og subtraksjon, nivå 3 multiplikasjon og divisjon, nivå 4 eksponentiering, og til slutt unær pluss og minus under det. Hvert nivå analyserer nivået over seg for sine operander, så et høyere bånd binder strammere. Dette er den samme presedensen Excel anvender, noe som er grunnen til at A1*B1+A2*B1 reduserer de to produktene før summen: multiplikasjon sitter på nivå 3, addisjon på nivå 2, så multiplikasjonene er dypere i treet og reduseres først

Sporing av en formel og gjennomgang av trinnene

Bruk speiler den medfølgende demoen på Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Bygg et regneark (eller åpne en eksisterende arbeidsbok), konstruer en sporer over arket, kall Trace, og iterer den returnerte matrisen. Hver TXLSFormulaStep eksponerer Depth for innrykk, Source for det opprinnelige underuttrykket, Expression for det underuttrykket med sine operander allerede erstattet, og Value for resultatet av 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;

Cellereferansene løses først og vises som sine egne trinn, deretter reduseres produktene, deretter den parentesiserte skattefaktoren, og den siste multiplikasjonen avslutter det hele. Depth-feltet lar deg rykke inn slik at de innerste reduksjonene synlig sitter dypest, akkurat slik Excel understreker det innerste leddet før et ytre

Fellen med lokalitetsfrie literaler

Den farligste detaljen i hele dette opplegget er usynlig på en engelsk maskin og bryter høylytt på en tysk en. Når et beregnet tall settes tilbake inn i formelteksten, må det skrives som en streng og deretter analyseres på nytt av beregningsmotoren, som behandler . som desimaltegn. Hvis erstatningen brukte systemlokaliteten, ville en tysk TFormatSettings skrive 1,08 for skattefaktoren, kommaet ville blitt lest som en argumentseparator, og omberegningen av A1*B1*1,08 ville enten blitt analysert til feil form eller feilet direkte

Sporeren unngår dette ved å formatere hver numerisk literal gjennom en privat TFormatSettings som den fester ved konstruksjon, med DecimalSeparator tvunget til . og ThousandSeparator satt til #0 slik at ingen grupperingskarakter noen gang sendes ut. FloatToStr produserer deretter en literal motoren alltid kan lese tilbake, uavhengig av operatørens regionale innstillinger

// 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 typen feil som aldri dukker opp i forfatterens egen testing og bare kommer til overflaten når en kunde i en annen lokalitet kjører den samme koden, så det er verdt å si det tydelig: å sende en verdi frem og tilbake gjennom formeltekst er et serialiseringsproblem, og serialisering må være lokalitetsfri

Boolske verdier reduseres til 1 og 0

En relatert erstatningsbeslutning gjelder logiske verdier. Når et underuttrykk evalueres til en boolsk verdi, skriver sporeren det tilbake som 1 eller 0, ikke som TRUE eller FALSE. Årsaken er at den reduserte literalen må analyseres rent i den konteksten som omgir den, og aritmetikk er det krevende tilfellet. Hvis en sammenligning som A1>A2 ble redusert til teksten TRUE og den teksten havnet inni TRUE*B1, ville omberegningen avhenge av at motoren aksepterer et nakent boolsk nøkkelord i en multiplikasjon. Å erstatte med 1 omgår spørsmålet fullstendig, fordi 1*B1 er utvetydig i enhver aritmetisk posisjon. Det samsvarer også med Excels egen konvertering, der TRUE oppfører seg som 1 og FALSE som 0 i det øyeblikket et tall forventes

Funksjonskall reduseres atomært

En naiv trinnmotor ville redusere en funksjons argumenter først og deretter kallet. Det er feil for Excel, og sporeren gjør det bevisst ikke. Et funksjonskall evalueres som en helhet, fra dens opprinnelige tekst, i ett enkelt trinn. Årsaken er kortslutningssemantikk. IF, CHOOSE og IFERROR evaluerer bare grenen de velger, og å redusere argumentene først ville tvinge motoren til å beregne grener Excel aldri rører. Det klassiske offeret er en dele-på-null-vakt som IF(B1=0,0,A1/B1): hvis sporeren reduserte A1/B1 før den evaluerte IF, ville vakten feile og utløse den eksakte feilen den eksisterer for å forhindre. Ved å evaluere hele kallet atomært, bevarer sporeren den late evalueringen som får slike vakter til å fungere

// 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.

Kompromisset er at du ikke ser inni funksjonskallet som separate trinn, men det er den riktige atferden. Å vise argumentreduksjoner Excel aldri utfører, ville være et mer villedende spor enn å behandle kallet som den enkle evalueringsenheten det faktisk er

Argumentseparatorer og intakte områder

To andre normaliseringer holder omberegningen ærlig. Beregningsmotorens kompilator forventer ; som funksjonens argumentseparator, så når sporeren bygger opp et funksjonskall fra det analyserte treet, slår den sammen argumentene med ;, selv om brukeren opprinnelig skrev ,. En formel skrevet som SUM(A1,A2,A3) blir omberegnet som SUM(A1;A2;A3), noe motoren aksepterer. Erstatningen av verdier er det som gjør denne gjenoppbyggingen nødvendig, og å få separatoeren riktig er det som får gjenoppbyggingen til å analyseres rett

Områdereferanser er det andre tilfellet. Et område som A1:A3 er ikke en skalar og må ikke deles opp i tre separate verdier, fordi funksjonen som forbruker det forventer et argument av typen område. Sporeren holder et område intakt som sin opprinnelige tekst og lar den omsluttende funksjonen redusere som en helhet. I SUM(A1:A3)*B1 forblir området helt, SUM(A1:A3) reduseres til ett tall i ett atomært trinn, og først deretter kjører den ytre multiplikasjonen. Dette er den samme grensen Excel trekker mellom en områdeoperand og skalaren den til slutt bidrar 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));

Til sammen gjør disse reglene trinnlisten til et trofast speilbilde av Excels Evaluer formel-kommando snarere enn en tilnærming av den. Reduksjonene skjer i den rekkefølgen Excel utfører dem, de erstattede literalene overlever enhver lokalitet, boolske verdier konverteres på den måten Excel gjør det, og late funksjoner forblir late. Hvis du vil presse motoren ytterligere med dine egne funksjoner, viser artikkelen formelmotor og tilpassede funksjoner hvordan du registrerer dem, og for tyngre numerisk arbeid dekker artikkelen statistiske fordelingsfunksjoner i Delphi det innebygde biblioteket sporeren evaluerer mot. Alt dette leveres som en del av HotXLS-regnearkkomponenten for Delphi og C++Builder, sammen med API-ene for lesing, skriving, formatering og beregning som dekkes andre steder på denne bloggen