Pratite evaluaciju Excel formule korak po korak u Delphiju

Excel skriva mali debugger svima na vidiku. Odaberite ćeliju, otvorite Formulas i kliknite Evaluate Formula, a dijalog će prikazati formulu s jednim podizrazom koji je podvučen. Pritisnite Evaluate i taj podizraz će se sažeti u svoju vrijednost, zatim se podvlači sljedeći, pa tako promatrate kako se dugi izraz smanjuje do jednog jedinog broja, redukciju po redukciju. To je najbrži način da pronađete koja je grana ugniježđenog IF-a zapravo okinula ili koja je referenca dala pogrešan zbroj. HotXLS reproducira točno to ponašanje kroz TXLSFormulaTracer, pa program u Delphiju ili C++Builderu može prikazati isti popis koraka za reviziju radne knjige, otklanjanje pogrešaka (debugging) u generiranoj formuli ili podučavanje nekoga zašto je rezultat ispao baš tako. Svaki zabilježeni korak nosi tekst podizraza i vrijednost na koju se on reducira

Kako pokretač redukcije prolazi kroz izraz

Alat za praćenje (tracer) ne zadire u sam pokretač izračuna. On dijeli formulu na tokene i parsira je pomoću rekurzivnog silaznog (recursive-descent) parsera, a zatim reducira stablo u dubinu, rješavajući prvo najdublji podizraz koji se može evaluirati. Kada se čvor reducira na neku vrijednost, ta se vrijednost vraća u okolni izraz kao literal, a pokretač traži od pravog kalkulatora da ponovno izračuna sada već jednostavniji izraz. Budući da se svaki korak evaluira kroz javnu Calculate metodu radnog lista umjesto kroz privatne prečice, svaki se korak u potpunosti poklapa s onim što bi donio puni ponovni izračun te ćelije. Parser je po samom dizajnu neinvazivan, što mu omogućuje da se pokrene na bilo kojem radnom listu, a da ne ometa njegovo stanje

Parser prati ljestvicu prioriteta operatora (operator-precedence), s jednom rekurzivnom razinom po pojasu prioriteta. Od najslabijeg do najjačeg vezivanja pojasevi su sljedeći: razina 0 za usporedbu (=, <>, <, >, <=, >=), razina 1 za spajanje nizova znakova (&), razina 2 za zbrajanje i oduzimanje, razina 3 za množenje i dijeljenje, razina 4 za potenciranje i na kraju unarni plus te minus ispod toga. Svaka razina parsira razinu iznad sebe tražeći svoje operande, tako da viši pojas veže jače. Ovo je isti prioritet koji primjenjuje i Excel, zbog čega se A1*B1+A2*B1 reducira najprije rješavajući dva umnoška, a tek onda zbroj: množenje se nalazi na razini 3, zbrajanje na razini 2, tako da su množenja dublje u stablu i stoga se reduciraju prva

Praćenje formule i prolazak kroz korake

Korištenje odražava isporučeni demo u Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Izgradite radni list (ili otvorite postojeću radnu knjigu), konstruirajte tracer nad tim listom, pozovite Trace i iterirajte kroz vraćeni niz (array). Svaki TXLSFormulaStep nudi Depth za uvlačenje, Source za originalni podizraz, Expression za taj podizraz s njegovim već zamijenjenim operandima, te 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 razrješavaju se prve i pojavljuju se kao zasebni koraci, zatim se reduciraju umnošci, pa porezni faktor u zagradama, a završno množenje sve to zatvara. Polje Depth omogućuje vam uvlačenje kako bi se najdublje redukcije vidljivo smjestile najdublje, točno onako kako Excel podvlači najdublji član prije bilo kojeg vanjskog

Zamka s literalima koji ovise o lokaciji (locale-free literal trap)

Najopasniji detalj u cijeloj ovoj shemi nevidljiv je na engleskom stroju i glasno se kvari na njemačkom. Kad se izračunati broj ponovno uvrsti natrag u tekst formule, on mora biti zapisan u obliku niza znakova (string) i ponovno ga mora parsirati pokretač za izračun, koji . tretira kao decimalnu točku. Da je zamjena koristila lokalne postavke sustava, njemački bi TFormatSettings napisao 1,08 za porezni faktor, zarez bi se pročitao kao separator argumenata i ponovni izračun za A1*B1*1,08 bi se ili krivo parsirao ili u potpunosti propao

Tracer to izbjegava tako što formatira svaki numerički literal putem privatnog TFormatSettings kojeg fiksira već pri konstruiranju, sa DecimalSeparator postavljenim na . i ThousandSeparator prilagođenim na #0, pa se lik za grupiranje nikada ne ispisuje. FloatToStr zatim proizvodi literal kojeg pokretač uvijek može oč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 (bug) koja se nikada ne pojavljuje u autorskom testiranju i izlazi na površinu tek kada kupac iz neke druge regije pokrene isti kôd. Zato je to vrijedno jasno reći: povratno vraćanje vrijednosti kroz tekst formule problem je serijalizacije, a serijalizacija mora biti nezavisna o lokalnim postavkama

Booleove vrijednosti se reduciraju na 1 i 0

Povezana odluka o zamjeni tiče se logičkih vrijednosti. Kada se podizraz evaluira u booleovu vrijednost, alat za praćenje ispisuje ga kao 1 ili 0, a ne kao TRUE ili FALSE. Razlog tome je što se reducirani literal mora ispravno ponovno parsirati u kojem god se on kontekstu nalazio, dok je aritmetika svakako zahtjevniji slučaj. Ako se usporedba kao što je A1>A2 reducira na tekst TRUE, a taj tekst sleti u okruženje TRUE*B1, ponovni izračun ovisio bi o pokretaču i o tome hoće li on uopće prihvatiti logičku ključnu riječ unutar procesa množenja. Zamjena u 1 potpuno zaobilazi to pitanje jer je 1*B1 jednoznačan pojam na bilo kojoj aritmetičkoj poziciji. Također odgovara i Excelovom vlastitom prilagođavanju (coercion), gdje se TRUE ponaša kao 1, a FALSE kao 0 u trenutku kada se očekuje broj

Pozivi funkcija reduciraju se atomski (atomically)

Naivan engine koraka najprije bi reducirao argumente neke funkcije, a tek onda i sam poziv. Za Excel je to krivo i tracer to namjerno ne radi. Poziv funkcije evaluira se kao cjelina, počevši od svog originalnog teksta, u samo jednom koraku. Razlog tome leži u kratko spojenoj semantici (short-circuit semantics). IF, CHOOSE i IFERROR procjenjuju isključivo onu granu koju su i sami izabrali, a reduciranje priloženih argumenata unaprijed natjeralo bi pokretač na računanje upravo onih ogranaka koje Excel zapravo nikada ni ne takne. Klasična žrtva jest zaštita od dijeljenja s nulom, kao što je npr. slučaj IF(B1=0,0,A1/B1): da tracer reducira A1/B1 prije same procjene cjeline IF, štit bi potpuno podbacio te izbacio samu onu grešku zbog koje je inače i stvoren. Procjenjujući cjelokupan poziv atomski, alat za praćenje čuva tu lijenu evaluaciju (lazy evaluation) zbog koje i takve zaštite uopće rade

// 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 da unutrašnjost funkcijskog poziva ne vidite kao zasebne korake, no to je ispravno ponašanje. Prikazivanje redukcija argumenata koje Excel nikada ni ne izvodi bilo bi više obmanjujuće praćenje nego samo tretiranje takvog poziva jedinstvenom evaluacijskom cjelinom, što on u biti i jest

Separatori argumenata i netaknuti rasponi

Dvije dodatne normalizacije održavaju ponovno izračunavanje pouzdanim. Prevoditelj (compiler) pokretača za izračun očekuje ; kao separator argumenata funkcije, pa kada alat za praćenje ponovno gradi poziv funkcije iz svog parsiranog stabla, on spaja argumente s ;, čak i ako je korisnik izvorno upisao ,. Formula zapisana kao SUM(A1,A2,A3) ponovno se izračunava kao SUM(A1;A2;A3), što pokretač i prihvaća. Upravo je zamjena vrijednosti ono što čini ovu ponovnu gradnju neophodnom, dok ispravno postavljanje separatora zapravo omogućuje samo parsiranje te ponovne gradnje

Reference na raspone drugi su slučaj. Raspon kao što je A1:A3 nije skalar i ne smije se rascjepkati na tri zasebne vrijednosti, jer funkcija koja ga prima očekuje argument raspona. Alat za praćenje ostavlja raspon netaknutim kao njegov izvorni tekst i dopušta da se funkcija koja ga obuhvaća reducira kao cjelina. U primjeru SUM(A1:A3)*B1 raspon ostaje cijeli, SUM(A1:A3) se reducira na jedan broj u jednom atomskom koraku, a tek tada se pokreće i vanjsko množenje. To je ista granica koju Excel iscrtava između operanda raspona i skalara kojem on na kraju i 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));

Uzeta zajedno, ova pravila čine popis koraka vjernim ogledalom Excelove naredbe Evaluate Formula, a ne samo njenom aproksimacijom. Redukcije se odvijaju redoslijedom kojim ih provodi i Excel, zamijenjeni literali preživljavaju bilo koju lokaciju, booleove se vrijednosti prilagođavaju onako kako ih prilagođava i Excel, a lijene funkcije ostaju lijene. Ako želite gurnuti pokretač i korak dalje s vlastitim funkcijama, članak o pokretaču formula i prilagođenim funkcijama pokazuje kako ih možete registrirati, a za teže numeričke operacije članak o funkcijama statističke distribucije u Delphiju pokriva ugrađenu biblioteku nad kojom alat za praćenje provodi evaluaciju. Sve to isporučuje se kao dio HotXLS komponente proračunskih tablica za Delphi i C++Builder, usporedno s API-jima za čitanje, pisanje, formatiranje te izračune, koji su pokriveni drugdje na ovom blogu