Krokové trasování výpočtu vzorců Excelu v Delphi

Excel skrývá malý ladicí nástroj přímo před vašima očima. Vyberte buňku, otevřete Vzorce a klikněte na Vyhodnocení vzorce, a zobrazí se dialogové okno s podtrženým jedním dílčím výrazem. Stiskněte Vyhodnotit a tento výraz se zredukuje na svou hodnotu, poté se podtrhne další a vy tak můžete sledovat, jak se dlouhý výraz postupně redukuje na jediné číslo. Je to nejrychlejší způsob, jak zjistit, která větev vnořené funkce IF se skutečně provedla, nebo který odkaz způsobil chybný součet. HotXLS reprodukuje přesně toto chování pomocí TXLSFormulaTracer, takže program v Delphi nebo C++Builder může vykreslit stejný seznam kroků pro audit sešitu, ladění vygenerovaného vzorce nebo pro vysvětlení, proč výsledek dopadl zrovna takto. Každý zaznamenaný krok nese text dílčího výrazu a hodnotu, na kterou se zredukuje

Jak redukční jádro prochází výraz

Nástroj pro trasování (tracer) nezasahuje přímo do výpočetního jádra. Místo toho rozdělí vzorec na tokeny a zpracuje jej analyzátorem s rekurzivním sestupem, poté strom redukuje do hloubky, počínaje nejvnitřnějším vyhodnotitelným výrazem. Když se uzel zredukuje na hodnotu, tato hodnota je dosazena zpět do okolního výrazu jako literál a jádro požádá skutečný kalkulátor o přepočítání nyní jednoduššího výrazu. Protože se každý krok vyhodnocuje prostřednictvím veřejné metody Calculate daného listu namísto použití privátní zkratky, každý krok se přesně shoduje s tím, co by vyprodukoval úplný přepočet buňky. Analyzátor je záměrně navržen jako neinvazivní, což mu umožňuje běžet nad jakýmkoli listem bez narušení jeho stavu

Analyzátor se řídí žebříčkem priority operátorů, přičemž každé úrovni priority odpovídá jedna rekurzivní úroveň. Od nejnižší vazby k nejvyšší jsou tyto úrovně následující: úroveň 0 porovnání (=, <>, <, >, <=, >=), úroveň 1 zřetězení řetězců (&), úroveň 2 sčítání a odčítání, úroveň 3 násobení a dělení, úroveň 4 umocňování a konečně unární plus a minus pod ní. Každá úroveň analyzuje úroveň nad sebou pro své operandy, takže vyšší skupina se váže těsněji. Jedná se o stejnou prioritu, jakou uplatňuje Excel, a proto se ve vzorci A1*B1+A2*B1 nejprve redukují oba součiny a až poté součet: násobení se nachází na úrovni 3, sčítání na úrovni 2, takže násobení je ve stromu hlouběji a redukuje se jako první

Trasování vzorce a procházení kroků

Použití odpovídá dodávané ukázce v Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Vytvořte list (nebo otevřete existující sešit), sestavte tracer nad daným listem, zavolejte Trace a projděte vrácené pole. Každý TXLSFormulaStep poskytuje Depth pro odsazení, Source pro původní dílčí výraz, Expression pro dílčí výraz s již dosazenými operandy a Value pro výsledek 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;

Nejprve se vyhodnotí odkazy na buňky, které se objeví jako samostatné kroky, poté se zredukují součiny, následně zdanění v závorce a vše zakončí konečné násobení. Pole Depth umožňuje odsazení, takže nejvnitřnější redukce jsou vizuálně uloženy nejhlouběji, přesně tak, jak Excel podtrhává nejvnitřnější člen před jakýmkoli vnějším

Záludnost literálů nezávislých na národním prostředí

Nejnebezpečnější detail v celém tomto schématu je na anglickém počítači neviditelný, ale na německém nebo českém způsobí okamžitý pád. Když je vypočítané číslo dosazeno zpět do textu vzorce, musí být zapsáno jako řetězec a poté znovu analyzováno výpočetním jádrem, které považuje . za desetinnou tečku. Pokud by se pro dosazení použilo národní prostředí systému, české nebo německé nastavení TFormatSettings by pro faktor zdanění zapsalo 1,08, čárka by byla interpretována jako oddělovač argumentů a přepočet A1*B1*1,08 by se buď špatně rozložil, nebo by rovnou selhal

Tracer se tomuto vyhýbá tím, že formátuje každý číselný literál pomocí privátního objektu TFormatSettings, který nastaví při konstrukci s DecimalSeparator vynuceným na . a ThousandSeparator nastaveným na #0, takže se nikdy nevypisuje znak pro seskupování. Funkce FloatToStr pak vygeneruje literál, který jádro dokáže vždy přečíst zpět bez ohledu na regionální nastavení uživatele

// 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 přesně ten typ chyby, který se při vlastním testování autora nikdy neprojeví a vyplave na povrch až ve chvíli, kdy zákazník v jiném národním prostředí spustí stejný kód, a proto stojí za to jasně uvést: obousměrný převod hodnoty přes text vzorce je problém serializace a serializace musí být nezávislá na národním prostředí

Pravdivostní hodnoty se redukují na 1 a 0

Související rozhodnutí při dosazování se týká logických hodnot. Když se dílčí výraz vyhodnotí jako pravdivostní hodnota (boolean), tracer ji zapíše zpět jako 1 nebo 0, nikoli jako TRUE nebo FALSE. Důvodem je to, že zredukovaný literál se musí čistě analyzovat v jakémkoli kontextu, který jej obklopuje, přičemž aritmetika je ten náročnější případ. Pokud by se porovnání jako A1>A2 zredukovalo na text TRUE a tento text by se ocitl uvnitř TRUE*B1, přepočítání by záviselo na tom, zda jádro přijme holé klíčové slovo boolean v rámci násobení. Nahrazení za 1 se této otázce zcela vyhne, protože 1*B1 je jednoznačné v jakékoli aritmetické pozici. Odpovídá to také vlastnímu převodu Excelu, kde se TRUE chová jako 1 a FALSE jako 0 ve chvíli, kdy se očekává číslo

Volání funkcí se redukuje atomicky

Naivní krokovací engine by zredukoval nejprve argumenty funkce a teprve potom samotné volání. To je u Excelu nesprávné a tracer to záměrně nedělá. Volání funkce je vyhodnoceno jako celek ze svého původního textu v jediném kroku. Důvodem je sémantika zkráceného vyhodnocování. Funkce IF, CHOOSE a IFERROR vyhodnocují pouze tu větev, kterou vyberou, a dřívější redukce argumentů by donutila jádro počítat větve, kterých se Excel nikdy nedotkne. Klasickou obětí je ochrana proti dělení nulou jako například IF(B1=0,0,A1/B1): pokud by tracer zredukoval A1/B1 před vyhodnocením IF, ochrana by selhala a vyvolala by právě tu chybu, které má zabránit. Tím, že se celé volání vyhodnotí atomicky, zachovává tracer líné vyhodnocování, díky kterému tyto 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.

Nevýhodou je, že nevidíte dovnitř volání funkce jako na samostatné kroky, ale jedná se o správné chování. Zobrazování redukcí argumentů, které Excel nikdy neprovádí, by poskytlo více zavádějící stopu, než když se s voláním zachází jako s jedinou vyhodnocovací jednotkou, kterou ve skutečnosti je

Oddělovače argumentů a nedotčené rozsahy

Další dvě normalizace udržují přepočet v korektním stavu. Kompilátor výpočetního jádra očekává jako oddělovač argumentů funkce ;, takže když tracer znovu sestavuje volání funkce ze svého analyzovaného stromu, spojí argumenty pomocí ;, i když uživatel původně zadal ,. Vzorec napsaný jako SUM(A1,A2,A3) se přepočítá jako SUM(A1;A2;A3), což jádro bez problémů přijme. Právě dosazování hodnot činí toto znovusestavení nezbytným a správné použití oddělovače zaručí, že znovusestavený vzorec půjde analyzovat

Odkazy na oblasti (rozsahy) představují další případ. Oblast jako A1:A3 není skalár a nesmí být rozdělena na tři samostatné hodnoty, protože funkce, která ji zpracovává, očekává jako argument celou oblast. Tracer udržuje oblast nedotčenou v její původní podobě a nechává nadřazenou funkci redukovat jako celek. Ve výrazu SUM(A1:A3)*B1 zůstává rozsah vcelku, SUM(A1:A3) se zredukuje na jedno číslo v jediném atomickém kroku a teprve poté se provede vnější násobení. Toto je stejná hranice, kterou Excel nakreslí mezi operandem oblasti a skalárem, kterým ve výsledku přispěje

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

Díky těmto pravidlům je seznam kroků věrným obrazem příkazu Vyhodnocení vzorce z Excelu namísto jeho pouhé aproximace. K redukcím dochází v pořadí, v jakém je provádí Excel, dosazené literály přežijí jakékoli národní prostředí, pravdivostní hodnoty se převádějí stejným způsobem, jak to dělá Excel, a líné funkce zůstávají línými. Pokud chcete posunout schopnosti jádra dále pomocí vlastních funkcí, článek o výpočetním jádru vzorců a vlastních funkcích ukazuje, jak je zaregistrovat. Pro náročnější numerické úlohy pak článek o statistických distribučních funkcích v Delphi pokrývá vestavěnou knihovnu, vůči které tracer provádí vyhodnocování. To vše je součástí komponenty HotXLS pro Delphi a C++Builder spolu s rozhraními API pro čtení, zápis, formátování a výpočty popsanými jinde na tomto blogu