Praćenje evaluacije Excel formula korak po korak u Delphi-ju

Excel skriva mali debugger na vidljivom mjestu. Odaberite ćeliju, otvorite Formulas i kliknite Evaluate Formula, i dijalog prikazuje formulu s jednim podizrazom podvučenim. Pritisnite Evaluate i taj podizraz se sažima u svoju vrijednost, zatim se podvlači sljedeći, i gledate kako dug izraz staje do jednog broja jednom redukcijom u isto vrijeme. To je najbrži način da pronađete koja grana ugniježđene IF zapravo je pokrenuta, ili koja referenca je dala pogrešan ukupan iznos. HotXLS reprodukuje to tačno ponašanje kroz TXLSFormulaTracer, pa Delphi ili C++Builder program može prikazati isti popis koraka za reviziju radne knjige, debug generisane formule, ili poučavanje nekoga zašto je rezultat ispao onako kako je ispao. Svaki zabilježeni korak nosi tekst podizraza i vrijednost na koju se svodi

Kako engine za redukciju prolazi kroz izraz

Tracer ne ulazi u engine za izračunavanje. Tokenizira formulu i parsira je s rekurzivno-silaznim parserom, zatim svodi stablo dubinom-prvo, od najdubljih evaluabilnih podizraza ka vani. Kada se čvor svede na vrijednost, ta vrijednost se zamjenjuje nazad u okolni izraz kao literal, i engine traži od pravog kalkulatora da preračuna sada-jednostavniji izraz. Jer se svaki korak evaluira kroz javnu metodu radnog lista Calculate, a ne privatnim prečacima, svaki korak se tačno slaže s onim što bi puni ponovni izračun ćelije proizveo. Parser je po dizajnu neinvazivan, što mu omogućuje rad s bilo kojim radnim listom bez narušavanja njegovog stanja

Parser slijedi ljestvicu prioriteta operatora, s jednim rekurzivnim nivoom po opsegu prioriteta. Od najslabijeg vezivanja do najjačeg: nivo 0 poređenje (=, <>, <, >, <=, >=), nivo 1 konkatenacija niski (&), nivo 2 sabiranje i oduzimanje, nivo 3 množenje i djeljenje, nivo 4 eksponentiranje, i na kraju unarni plus i minus ispod toga. Svaki nivo parsira nivo iznad njega za svoje operande, pa viši opseg veže čvršće. Ovo je isti prioritet koji Excel primjenjuje, zato A1*B1+A2*B1 svodi dva produkta prije zbira: množenje sjedi na nivou 3, sabiranje na nivou 2, pa su množenja dublje u stablu i svode se prvo

Praćenje formule i prolazak kroz korake

Upotreba ogleda isporučenu demo aplikaciju na Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Napravite radni list (ili otvorite postojeću radnu knjigu), konstruirajte tracer nad listom, pozovite Trace i iterirajte vraćeni niz. Svaki TXLSFormulaStep izlaže Depth za uvlačenje, Source za originalni podizraz, Expression za taj podizraz s već zamijenjenim operandima, i 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;

Reference ćelija se razrješavaju prve i pojavljuju se kao vlastiti koraci, zatim se svode produkti, zatim faktor poreza u zagradama, i konačno množenje to zatvara. Polje Depth vam omogućuje uvlačenje kako bi najunutarnjije redukcije bile vizualno najdublje, tačno kao što Excel podvlači najunutarnjiji termin prije bilo kojeg vanjskog

Zamka literala bez lokalizacije

Najopasaniji detalj u cijeloj ovoj shemi je nevidljiv na engleskom računaru i glasno puca na njemačkom. Kada se izračunati broj zamijeni nazad u tekst formule, mora biti napisan kao string i zatim ponovo parsiran od strane engine za izračunavanje, koji tretira . kao decimalni separator. Ako bi zamjena koristila sistemsku lokalizaciju, njemački TFormatSettings bi napisao 1,08 za faktor poreza, zarez bi bio pročitan kao separator argumenata, i ponovni izračun A1*B1*1,08 bi ili bio parsiran u pogrešan oblik ili bi propao

Tracer to izbjegava formatiranjem svakog numeričkog literala kroz privatni TFormatSettings koji pričvršćuje pri konstrukciji, s DecimalSeparator prisilno postavljenim na . i ThousandSeparator postavljenim na #0 pa se nikad ne emitira znak za grupiranje. FloatToStr tada proizvodi literal koji engine uvijek može pročitati, bez obzira na regionalne postavke operatora

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

Ovo je vrsta greške koja se nikad ne pojavljuje u autorovom vlastitom testiranju i pojavljuje se tek kada kupac u drugoj lokalizaciji pokrene isti kod, pa je vrijedno jasno navesti: kružni put vrijednosti kroz tekst formule je problem serijalizacije, a serijalizacija mora biti bez lokalizacije

Booleani se svode na 1 i 0

Srodna odluka o zamjeni tiče se logičkih vrijednosti. Kada podizraz evaluira na boolean, tracer ga vraća kao 1 ili 0, ne kao TRUE ili FALSE. Razlog je da reducirani literal mora ponovo parsirati čisto u koji god kontekst ga okružuje, a aritmetika je zahtjevan slučaj. Ako bi poređenje kao A1>A2 reduciralo na tekst TRUE i taj tekst bi se našao unutar TRUE*B1, ponovni izračun bi ovisio o tome da li engine prihvata golu boolean ključnu riječ u množenju. Zamjenom s 1 to pitanje se u potpunosti zaobilazi, jer je 1*B1 nedvosmisleno u bilo kojoj aritmetičkoj poziciji. Ovo se također podudara s Excelovom vlastitom koercijom, gdje TRUE ponaša se kao 1 a FALSE kao 0 čim se očekuje broj

Pozivi funkcija se svode atomski

Naivni engine za korake bi svodio argumente funkcije prvo, a zatim poziv. To je pogrešno za Excel, i tracer to namjerno ne radi. Poziv funkcije se evaluira kao cjelina, iz svog originalnog teksta, u jednom koraku. Razlog je semantika kratkog spoja. IF, CHOOSE i IFERROR evaluiraju samo granu koju odaberu, a svođenje argumenata prvo bi prisililo engine da izračuna grane koje Excel nikad ne dodiruje. Klasičan oštećen slučaj je zaštita od dijeljenja nulom kao IF(B1=0,0,A1/B1): ako bi tracer svodio A1/B1 prije evaluacije IF-a, zaštita bi pogrešno reagovala i izazvala upravo grešku čije je sprečavanje njena svrha. Evaluiranjem cijelog poziva atomski, tracer čuva lijenu evaluaciju koja takve zaštite čini funkcionalnim

// 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 unutar poziva funkcije kao zasebne korake, ali to je ispravno ponašanje. Prikazivanje redukcija argumenata koje Excel nikad ne izvodi bilo bi varljivije praćenje od tretiranja poziva kao jedine evaluacijske jedinice koja zapravo jest

Separatori argumenata i netaknuti rasponi

Još dvije normalizacije drže ponovni izračun tačnim. Kompajler engine za izračunavanje očekuje ; kao separator argumenata funkcije, pa kada tracer rekonstruira poziv funkcije iz svog parsiranog stabla spaja argumente s ;, čak i ako je korisnik originalno pisao ,. Formula napisana kao SUM(A1,A2,A3) ponovo se izračunava kao SUM(A1;A2;A3), što engine prihvata. Zamjena vrijednosti je ono što čini ovu rekonstrukciju neophodnom, a ispravno dobivanje separatora je ono što rekonstrukciju čini parsabilnom

Reference raspona su drugi slučaj. Raspon kao A1:A3 nije skalar i ne smije biti razdijeljen u tri zasebne vrijednosti, jer funkcija koja ga troši očekuje argument raspona. Tracer drži raspon netaknutim kao originalni tekst i pušta okružujuću funkciju da se svede kao cjelina. U SUM(A1:A3)*B1 raspon ostaje cijel, SUM(A1:A3) se svodi na jedan broj u jednom atomskom koraku, i tek tada vanjsko množenje se pokreće. Ovo je ista granica koju Excel vuče između operanda raspona i skalara koji on u konačnici daje

// 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));

Zajedno, ova pravila čine popis koraka vjernim ogledalom Excelovog Evaluate Formula naredbe, a ne aproksimacijom. Redukcije se odvijaju u redoslijedu kojim ih Excel izvodi, zamijenjeni literali preživljavaju bilo kakvu lokalizaciju, booleani se koerciraju onako kako ih Excel koercira, i lijene funkcije ostaju lijene. Ako želite dalje proširiti engine vlastitim funkcijama, članak o engine za formule i prilagođenim funkcijama pokazuje kako ih registrovati, a za opsežniji numerički rad članak o statističkim distribucijskim funkcijama u Delphi-ju pokriva ugrađenu biblioteku protiv koje tracer evaluira. Sve se isporučuje kao dio HotXLS spreadsheet componente za Delphi i C++Builder, zajedno s API-jevima za čitanje, pisanje, formatiranje i izračunavanje obrađenim drugdje na ovom blogu