Pratite evaluaciju Excel formula korak po korak u Delphi-ju

Excel krije mali debager pred našim očima. Izaberite ćeliju, otvorite tab Formule (Formulas) i kliknite na Evaluacija formule (Evaluate Formula), i pojaviće se dijalog koji prikazuje formulu sa jednim podvučenim podizrazom. Pritisnite dugme Evaluacija i taj podizraz se skuplja u svoju vrednost, zatim se sledeći podvlači, i posmatrate kako se dugi izraz smanjuje na jedan broj, korak po korak. To je najbrži način da saznate koja je grana ugnježdenog uslova IF zapravo izvršena, ili koja je referenca dala pogrešan ukupni zbir. HotXLS reprodukuje upravo to ponašanje kroz klasu TXLSFormulaTracer, tako da program napisan u Delphi-ju ili C++Builder-u može da generiše istu listu koraka za potrebe revizije radne sveske, otklanjanja grešaka u generisanoj formuli ili objašnjavanja nekome zašto je rezultat ispao onakav kakav jeste. Svaki zabeleženi korak nosi tekst podizraza i vrednost na koju se on svodi

Kako mehanizam redukcije prolazi kroz izraz

Tracer (pratilac) ne zadire u mehanizam proračuna. On deli formulu na tokene i analizira je pomoću parsera rekurzivnog spusta (recursive-descent parser), a zatim redukuje stablo po dubini (depth-first), počevši od najdubljeg evaluabilnog podizraza. Kada se čvor redukuje na vrednost, ta vrednost se zamenjuje nazad u okolni izraz kao literal, a mehanizam traži od stvarnog kalkulatora da ponovo izračuna sada jednostvniji izraz. Pošto se svaki korak evaluira kroz javnu metodu radnog lista Calculate, a ne kroz neku privatnu prečicu, svaki korak se tačno poklapa sa onim što bi proizveo puni ponovni proračun ćelije. Parser je po dizajnu neinvazivan, što mu omogućava da radi nad bilo kojim radnim listom bez menjanja njegovog stanja

Parser prati hijerarhiju prioriteta operatora, sa jednim rekurzivnim nivoom po opsegu prioriteta. Od najslabijeg vezivanja do najjačeg, opsezi su: poređenje nivoa 0 (=, <>, <, >, <=, >=), spajanje stringova nivoa 1 (&), sabiranje i oduzimanje nivoa 2, množenje i deljenje nivoa 3, stepenovanje nivoa 4 i na kraju unarni plus i minus ispod toga. Svaki nivo analizira nivo iznad sebe tražeći operande, tako da se viši opseg jače vezuje. Ovo je isti prioritet koji primenjuje i Excel, zbog čega izraz A1*B1+A2*B1 redukuje dva proizvoda pre zbira: množenje se nalazi na nivou 3, sabiranje na nivou 2, pa se množenja nalaze dublje u stablu i prva se redukuju

Praćenje formule i prolazak kroz korake

Upoteba odražava priloženi demo na putanji Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Izgradite radni list (ili otvorite postojeću radnu svesku), kreirajte tracer nad listom, pozovite metodu Trace i prođite kroz vraćeni niz. Svaki TXLSFormulaStep izlaže svojstvo Depth za uvlačenje reda, Source za originalni podizraz, Expression za taj podizraz sa već zamenjenim operandima i Value za rezultat tog 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;

Prvo se razrešavaju reference ćelija i pojavljuju se kao sopstveni koraci, zatim se redukuju proizvodi, pa poreski faktor u zagradi, a završno množenje sve zatvara. Polje Depth vam omogućava da izvršite uvlačenje reda tako da najdublje redukcije vizuelno stoje najdublje, baš kao što Excel podvlači najdublji pojam pre bilo kog spoljašnjeg

Zamka literala zavisnog od lokaliteta (locale)

Najopasniji detalj u čitavoj ovoj šemi je nevidljiv na mašini sa engleskim lokalitetom, ali glasno puca na nemačkom. Kada se izračunati broj vrati u tekst formule, on mora biti napisan kao string a zatim ponovo analiziran od strane mehanizma proračuna, koji tretira znak . kao decimalnu tačku. Ako je zamena koristila sistemski lokalitet, nemački TFormatSettings bi za poreski faktor napisao 1,08, zarez bi bio protumačen kao separator argumenata, a ponovni proračun izraza A1*B1*1,08 bi se ili analizirao u pogrešan oblik ili bi potpuno otkao

Tracer ovo izbegava formatiranjem svakog numeričkog literala kroz privatna podešavanja TFormatSettings koja fiksira pri kreiranju, pri čemu je DecimalSeparator primoran na . a ThousandSeparator postavljen na #0 tako da se karakter grupisanja nikada ne emituje. Funkcija FloatToStr zatim proizvodi literal koji mehanizam uvek može ponovo da pročita, bez obzira na regionalna podešavanja operatera

// 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 nikada ne pojavljuje tokom autorovog sopstvenog testiranja, već izlazi na videlo tek kada klijent u drugom lokalitetu pokrene isti kod, pa je vredi jasno navesti: kružni prenos vrednosti kroz tekst formule je problem serijalizacije, a serijalizacija mora biti nezavisna od lokaliteta

Logičke vrednosti se redukuju na 1 i 0

Srodna odluka o zameni tiče se logičkih vrednosti. Kada se podizraz evaluira u logičku vrednost (boolean), tracer ga upisuje nazad kao 1 ili 0, na kao TRUE ili FALSE. Razlog je u tome što redukovani literal mora čisto da se ponovo analizira u bilo kom kontekstu koji ga okružuje, a aritmetika je zahtevniji slučaj. Ako bi se poređenje kao što je A1>A2 svelo na tekst TRUE i taj tekst završio unutar TRUE*B1, ponovni proračun bi zavisio od toga da li mehanizam prihvata ključnu reč booleana u množenju. Zamena sa 1 u potpunosti zaobilazi ovo pitanje, jer je izraz 1*B1 nedvosmislen u bilo kojoj aritmetičkoj poziciji. To se takođe poklapa sa Excel-ovom sopstvenom prisilom tipova (coercion), gde se TRUE ponaša kao 1, a FALSE kao 0 onog trenutka kada se očekuje broj

Pozivi funkcija se redukuju atomski

Naivan mehanizam koraka bi prvo redukovao argumente funkcije, a zatim sam poziv. To je pogrešno za Excel, i tracer to namerno ne radi. Poziv funkcije se evaluira kao celina, iz svog originalnog teksta, u jednom koraku. Razlog je u semantici kratkog spoja (short-circuit). Funkcije IF, CHOOSE i IFERROR evaluiraju samo onu granu koju izaberu, a prva redukcija argumenata bi primorala mehanizam da računa grane koje Excel nikada ne dotiče. Klasična žrtva je zaštita od deljenja nulom kao što je IF(B1=0,0,A1/B1): ako bi tracer redukovao A1/B1 pre evaluacije funkcije IF, zaštita bi zakazala i podigla upravo onu grešku zbog čijeg sprečavanja i postoji. Evaluacijom celog poziva atomski, tracer čuva lenju evaluaciju (lazy evaluation) koja omogućava rad takvih zaštita

// 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 u tome što ne vidite unutrašnjost poziva funkcije kao odvojene korake, ali to je ispravno ponašanje. Prikazivanje redukcija argumenata koje Excel nikada ne vrši bio bi trag koji više zavarava nego tretiranje poziva kao jedinstvene jedinice evaluacije što on zapravo i jeste

Separatori argumenata i netaknuti opsezi

Još dve normalizacije održavaju ponovni proračun ispravnim. Kompajler mehanizma proračuna očekuje znak ; kao separator argumenata funkcije, pa kada tracer ponovo gradi poziv funkcije iz svog analiziranog stabla, on spaja argumente sa ;, čak i ako je korisnik prvobitno napisao ,. Formula napisana kao SUM(A1,A2,A3) se ponovo izračunava kao SUM(A1;A2;A3), što mehanizam prihvata. Zamena vrednosti je ono što čini ovu ponovnu izgradnju neophodnom, a ispravno postavljanje separatora je ono što omogućava da se ta ponovna izgradnja uspešno analizira

Reference opsega su drugi slučaj. Opseg kao što je A1:A3 nije skalar i ne sme se deliti na tri odvojene vrednosti, jer funkcija koja ga koristi očekuje opseg kao argument. Tracer čuva opseg netaknutim kao njegov originalni tekst i prepušta okolnoj funkciji da se redukuje kao celina. U izrazu SUM(A1:A3)*B1 opseg ostaje ceo, SUM(A1:A3) se redukuje u jedan broj u jednom atomskom koraku, i tek tada se pokreće spoljno množenje. To je ista granica koju Excel povlači između operanda opsega i skalara kojem on na kraju doprinosi

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

Sve zajedno, ova pravila čine listu koraka vernim ogledalom Excel-ove komande Evaluacija formule (Evaluate Formula), a ne njenom aproksimacijom. Redukcije se dešavaju onim redosledom kojim ih Excel obavlja, zamenjeni literali preživljavaju bilo koji lokalitet, logičke vrednosti se prisiljavaju na način na koji ih Excel prisiljava, a lenje funkcije ostaju lenje. Ako želite da proširite mehanizam sopstvenim funkcijama, članak o mehanizmu formula i prilagođenim funkcijama pokazuje kako da ih registrujete, a za složenije numeričke poslove članak o funkcijama statističke raspodele u Delphi-ju pokriva ugrađenu biblioteku u odnosu na koju tracer vrši evaluaciju. Sve se to isporučuje kao deo HotXLS komponente za tabele za Delphi i C++Builder, zajedno sa API-jima za čitanje, pisanje, formatiranje i proračune koji su pokriveni na drugim mestima na ovom blogu