Sledovanie vyhodnocovania vzorcov Excel krok za krokom v Delphi

Excel skrýva malý debugger priamo na očiach. Vyberte bunku, otvorte Vzorce a kliknite na Vyhodnotiť vzorec – zobrazí sa dialóg so vzorcom, kde je podčiarknutý jeden výraz. Stlačte Vyhodnotiť a tento čiastkový výraz sa zredukuje na svoju hodnotu, potom sa podčiarkne ďalší, a sledujete, ako dlhý výraz krok za krokom ustupuje na jediné číslo. Je to najrýchlejší spôsob, ako zistiť, ktorá vetva vnoreného IF sa skutočne vykonala, alebo ktorý odkaz poskytol chybný súčet. HotXLS reprodukuje toto presné správanie prostredníctvom TXLSFormulaTracer, takže program v Delphi alebo C++Builder môže vykresliť rovnaký zoznam krokov na auditovanie zošita, ladenie generovaného vzorca alebo vysvetlenie niekomu, prečo výsledok dopadol práve tak. Každý zaznamenaný krok nesie text čiastkového výrazu a hodnotu, na ktorú sa redukuje

Ako redukčný engine prechádza výrazom

Tracer nezasahuje priamo do výpočtového enginu. Tokenizuje vzorec a parsuje ho rekurzívnym zostupným parserom, potom redukuje strom do hĺbky, pričom najprv spracuje najvnútornejší vyhodnotiteľný čiastkový výraz. Keď sa uzol redukuje na hodnotu, táto hodnota je nahradená späť do obklopujúceho výrazu ako literál a engine požiada skutočný kalkulátor, aby prepočítal teraz jednoduchší výraz. Keďže každý krok je vyhodnocovaný prostredníctvom verejnej metódy Calculate pracovného listu, a nie súkromnou skratkou, každý krok presne súhlasí s tým, čo by úplný prepočet bunky produkoval. Parser je zo svojej podstaty neinvazívny, čo mu umožňuje bežať na ľubovoľnom pracovnom liste bez narušenia jeho stavu

Parser sleduje rebríček priority operátorov, pričom každej úrovni priority zodpovedá jedna rekurzívna vrstva. Od najnižšej po najvyššiu väzbu sú úrovne nasledovné: úroveň 0 porovnanie (=, <>, <, >, <=, >=), úroveň 1 zreťazenie reťazcov (&), úroveň 2 sčítanie a odčítanie, úroveň 3 násobenie a delenie, úroveň 4 umocňovanie a nakoniec unárny plus a mínus. Každá úroveň parsuje vyššiu úroveň pre svoje operandy, takže vyššia úroveň viaže silnejšie. Toto je rovnaká priorita, akú aplikuje Excel, a preto sa A1*B1+A2*B1 redukuje najprv oba súčiny a potom súčet: násobenie je na úrovni 3, sčítanie na úrovni 2, teda násobenia sú hlbšie v strome a redukujú sa ako prvé

Sledovanie vzorca a prechádzanie krokmi

Použitie odráža dodaný príklad v Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Vytvorte pracovný list (alebo otvorte existujúci zošit), skonštruujte tracer nad listom, zavolajte Trace a iterujte vrátené pole. Každý TXLSFormulaStep sprístupňuje Depth pre odsadenie, Source pre pôvodný čiastkový výraz, Expression pre tento čiastkový výraz s už nahradzovanými operandmi a Value pre výsledok daného kroku

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;

Odkazy na bunky sa vyriešia ako prvé a zobrazia sa ako vlastné kroky, potom sa redukujú súčiny, potom zátvorkový daňový faktor a nakoniec záverečné násobenie uzavrie celé vyhodnocovanie. Pole Depth vám umožňuje odsadzovanie, takže najvnútornejšie redukcie sú viditeľne najhlbšie, presne tak, ako Excel podčiarkuje najvnútornejší člen skôr ako ktorýkoľvek vonkajší

Pasca literálov nezávislých od lokalizácie

Najnebezpečnejší detail celej tejto schémy je neviditeľný na anglickom stroji a hlasno zlyhá na nemeckom. Keď sa vypočítané číslo nahradí späť do textu vzorca, musí byť zapísané ako reťazec a potom znova parsované výpočtovým enginom, ktorý považuje . za desatinnú čiarku. Ak by náhrada použila systémovú lokalizáciu, nemecký TFormatSettings by zapísal 1,08 pre daňový faktor, čiarka by bola čítaná ako oddeľovač argumentov a prepočet A1*B1*1,08 by sa buď parsoval do nesprávneho tvaru, alebo by úplne zlyhal

Tracer sa tomu vyhýba tým, že formátuje každý číselný literál prostredníctvom súkromného TFormatSettings, ktorý fixuje pri konštrukcii, pričom DecimalSeparator je nútene nastavený na . a ThousandSeparator je nastavený na #0, aby sa nikdy nevypisoval žiadny oddeľovač skupín. FloatToStr potom produkuje literál, ktorý engine dokáže vždy spätne načítať, bez ohľadu na regionálne nastavenia operátora

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

Toto je druh chyby, ktorá sa nikdy neobjaví pri vlastnom testovaní autora a vyskytne sa iba vtedy, keď zákazník v inej lokalizácii spustí rovnaký kód – preto stojí za to to jasne uviesť: spätné prevedenie hodnoty cez text vzorca je problémom serializácie a serializácia musí byť nezávislá od lokalizácie

Booleovské hodnoty sa redukujú na 1 a 0

Súvisiace rozhodnutie o náhrade sa týka logických hodnôt. Keď sa čiastkový výraz vyhodnotí na booleovskú hodnotu, tracer ju zapíše späť ako 1 alebo 0, nie ako TRUE alebo FALSE. Dôvodom je, že redukovaný literál musí byť čisto znovu parsovaný v akomkoľvek kontexte, ktorý ho obklopuje, pričom aritmetika je tým náročným prípadom. Keby sa porovnanie ako A1>A2 zredukovalo na text TRUE a tento text by sa ocitol vo vnútri TRUE*B1, prepočet by závisel od toho, či engine akceptuje holé booleovské kľúčové slovo v násobení. Nahradenie hodnotou 1 túto otázku úplne obchádza, pretože 1*B1 je jednoznačné v akomkoľvek aritmetickom kontexte. Zároveň to zodpovedá vlastnej koercii Excelu, kde sa TRUE správa ako 1 a FALSE ako 0 vo chvíli, keď sa očakáva číslo

Volania funkcií sa redukujú atomicky

Naivný stepovací engine by najprv zredukoval argumenty funkcie a potom samotné volanie. To je pre Excel nesprávne a tracer to zámerne nerobí. Volanie funkcie je vyhodnotené ako celok, z pôvodného textu, v jednom kroku. Dôvodom je sémantika skratového vyhodnocovania. IF, CHOOSE a IFERROR vyhodnocujú iba vybranú vetvu a redukcia argumentov vopred by prinútila engine vypočítať vetvy, ktorých sa Excel nikdy nedotkne. Klasickým prípadom je ochrana pred delením nulou, napríklad IF(B1=0,0,A1/B1): keby tracer zredukoval A1/B1 pred vyhodnotením IF, ochrana by zlyhala a vyvolala by presne tú chybu, ktorej existencia má zabrániť. Vyhodnocovaním celého volania atomicky tracer zachováva lenivé vyhodnocovanie, vďaka ktorému takéto ochrany fungujú

// 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 spočíva v tom, že nevidíte vnútro volania funkcie ako samostatné kroky, ale to je správne správanie. Zobrazovanie redukcií argumentov, ktoré Excel nikdy nevykoná, by bolo zavádzajúcejším sledovaním ako považovanie volania za jednu vyhodnocovaciu jednotku, ktorou skutočne je

Oddeľovače argumentov a neporušené rozsahy

Ďalšie dve normalizácie zabezpečujú správnosť prepočtu. Kompilátor výpočtového enginu očakáva ; ako oddeľovač argumentov funkcií, takže keď tracer znovu zostaví volanie funkcie z parsovaného stromu, spája argumenty pomocou ;, aj keď používateľ pôvodne napísal ,. Vzorec zapísaný ako SUM(A1,A2,A3) sa prepočíta ako SUM(A1;A2;A3), čo engine akceptuje. Náhrada hodnôt je tým, čo si toto prebudovanie vyžaduje, a správny oddeľovač je tým, čo zabezpečuje správne parsovanie prebudovaného výrazu

Rozsahové odkazy sú ďalším prípadom. Rozsah ako A1:A3 nie je skalár a nesmie byť rozdelený na tri samostatné hodnoty, pretože funkcia, ktorá ho spotrebúva, očakáva argument rozsahu. Tracer zachováva rozsah nedotknute ako pôvodný text a nechá obaľujúcu funkciu redukovať ako celok. Vo výraze SUM(A1:A3)*B1 rozsah zostáva celý, SUM(A1:A3) sa zredukuje na jedno číslo v jednom atomickom kroku a až potom prebehne vonkajšie násobenie. Toto je rovnaká hranica, ktorú Excel vytyčuje medzi operandom rozsahu a skalárom, ktorý nakoniec prispieva

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

Tieto pravidlá spoločne robia zo zoznamu krokov verné zrkadlo príkazu Vyhodnotiť vzorec v Exceli, nie len jeho aproximáciu. Redukcie prebiehajú v poradí, v akom ich Excel vykonáva, nahradené literály prežijú akúkoľvek lokalizáciu, booleovské hodnoty sú koercované tak, ako ich koercuje Excel, a lenivé funkcie zostávajú lenivé. Ak chcete engine ďalej rozšíriť vlastnými funkciami, článok o formulovom engine a vlastných funkciách ukazuje, ako ich registrovať, a pre náročnejšiu numerickú prácu článok o funkciách štatistického rozdelenia v Delphi pokrýva vstavanú knižnicu, voči ktorej tracer vyhodnocuje. Všetko toto je súčasťou komponenty HotXLS spreadsheet pre Delphi a C++Builder spolu s API na čítanie, zápis, formátovanie a výpočet, ktoré sú pokryté inde na tomto blogu