Excel v sobě ukrývá malý integrovaný debugger. Vyberte buňku, otevřete kartu Vzorce a klikněte na Vyhodnocení vzorce; zobrazí se dialogové okno se vzorcem, v němž je podtržen jeden dílčí výraz. Klikněte na Vyhodnotit a tento dílčí výraz se zredukuje na svou hodnotu, poté se podtrhne další a vy sledujete, jak se dlouhý výraz postupně zmenšuje na jediné číslo. Je to nejrychlejší způsob, jak zjistit, která větev vnořené funkce IF se skutečně vykonala nebo který odkaz způsobil nesprávný součet. Knihovna HotXLS toto chování přesně reprodukuje prostřednictvím třídy TXLSFormulaTracer, takže program v Delphi nebo C++Builderu může vypsat stejný seznam kroků pro auditování sešitu, ladění generovaného vzorce nebo vysvětlení, proč výsledek dopadl právě takto. Každý zaznamenaný krok nese text dílčího výrazu a hodnotu, na kterou se zredukoval
Jak redukční jádro prochází výraz
Trasovač nezasahuje přímo do výpočetního jádra. Rozloží vzorec na tokeny, analyzuje jej rekurzivním sestupným parserem a poté redukuje strom metodou depth-first, přičemž začíná u nejvnitřnějšího vyhodnotitelného dílčího výrazu. Když se uzel zredukuje na hodnotu, nahradí se tato hodnota zpět do okolního výrazu jako literál a jádro požádá skutečný kalkulátor o přepočtení tohoto zjednodušeného výrazu. Protože se každý krok vyhodnocuje pomocí veřejné metody listu Calculate a nikoli přes interní zkratku, odpovídá každý krok přesně tomu, co by vygeneroval plný přepočet buňky. Parser je záměrně neinvazivní, což mu umožňuje běžet nad jakýmkoli listem, aniž by narušil jeho stav
Parser se řídí žebříčkem priorit operátorů s jednou rekurzivní úrovní pro každé pásmo priority. Od nejnižší priority k nejvyšší jsou to: úroveň 0 porovnání (=, <>, <, >, <=, >=), úroveň 1 spojování řetězců (&), úroveň 2 sčítání a odčítání, úroveň 3 násobení a dělení, úroveň 4 umocňování a nakonec unární plus a mínus pod nimi. Každá úroveň analyzuje úroveň nad ní z hlediska jejích operandů, takže vyšší pásmo má větší prioritu. Jde o stejnou prioritu, jakou uplatňuje Excel, a proto výraz A1*B1+A2*B1 nejprve zredukuje oba součiny a až pak jejich součet: násobení je na úrovni 3, sčítání na úrovni 2, takže operace násobení jsou ve stromu hlouběji a redukují se jako první
Trasování vzorce a procházení kroků
Použití odpovídá přibalené ukázce v Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Sestavte list (nebo otevřete stávající sešit), vytvořte trasovač nad tímto listem, zavolejte metodu Trace a projděte vrácené pole. Každý krok TXLSFormulaStep zpřístupňuje vlastnost Depth pro odsazení, Source for the original subexpression, Expression for that subexpression with its operands already substituted, and Value for the result of the step
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 zobrazí jako samostatné kroky, poté se zredukují součiny, následně daňový faktor v závorkách a vše uzavře závěrečné násobení. Pole Depth umožňuje odsazení výpisu tak, aby nejvnitřnější redukce byly vizuálně nejhlouběji, přesně tak, jak Excel podtrhává nejvnitřnější výraz před jakýmkoli vnějším
Past v podobě literálů zá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 nevisible, ale na německém systému se projeví kritickou chybou. Když se vypočtené číslo dosadí zpět do textu vzorce, musí se zapsat jako řetězec a poté znovu analyzovat výpočetním jádrem, které považuje znak . za desetinnou tečku. Pokud by dosazení použilo systémové národní prostředí, německé nastavení TFormatSettings by pro daňový faktor zapsalo 1,08. Čárka by pak byla přečtena jako oddělovač argumentů a přepočet výrazu A1*B1*1,08 by se buď analyzoval do chybné podoby, nebo zcela selhal
Trasovač se tomu vyhýbá tak, že každý číselný literál formátuje pomocí vlastního nastavení TFormatSettings, které si zafixuje při konstrukci, přičemž DecimalSeparator je vynucen na . a vlastnost ThousandSeparator nastavena na #0, takže se nikdy negeneruje znak seskupování tisíců. Funkce FloatToStr pak vyprodukuje literál, který jádro dokáže vždy správně 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)Jedná se o typ chyby, který se při vlastním testování autora nikdy neobjeví a vypluje na povrch až v okamžiku, kdy zákazník v jiném národním prostředí spustí stejný kód. Proto je třeba říci na rovinu: převod hodnoty tam a zpět přes text vzorce představuje serializační problém a serializace musí být nezávislá na národním prostředí
Logické hodnoty se redukují na 1 a 0
Související rozhodnutí o nahrazování se týká logických hodnot. Když se dílčí výraz vyhodnotí jako logická hodnota, trasovač jej zapíše zpět jako 1 nebo 0 a nikoli jako TRUE nebo FALSE. Důvodem je, že zredukovaný literál se musí správně analyzovat v jakémkoli okolním kontextu, přičemž aritmetika je tím náročnějším případem. Pokud by se porovnání jako A1>A2 zredukovalo na text TRUE a tento text se ocitl uvnitř TRUE*B1, přepočet by závisel na tom, zda jádro akceptuje samotné logické klíčové slovo při násobení. Dosazení hodnoty 1 tuto otázku zcela obchází, protože 1*B1 je v jakékoli aritmetické pozici jednoznačné. Odpovídá to také vlastnímu přetypování v Excelu, kde se TRUE chová jako 1 a FALSE jako 0 v okamžiku, kdy se očekává číslo
Volání funkcí se redukují atomicky
Jednoduché jádro by nejprve zredukovalo argumenty funkce a až poté její volání. To je však pro Excel nesprávně a trasovač to záměrně nedělá. Volání funkce se vyhodnocuje jako celek z původního textu v jediném kroku. Důvodem je sémantika zkráceného vyhodnocování (short-circuit). Funkce IF, CHOOSE a IFERROR vyhodnocují pouze tu větev, kterou vyberou, a redukce argumentů jako první by jádro nutila počítat větve, kterých se Excel vůbec nedotkne. Klasickou obětí je ochrana před dělením nulou jako IF(B1=0,0,A1/B1): pokud by trasovač zredukoval A1/B1 před vyhodnocením IF, tato ochrana by selhala a vyvolala přesně tu chybu, které má zabránit. Tím, že trasovač vyhodnocuje celé volání atomicky, zachovává líné vyhodnocování (lazy evaluation), díky ně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.Kompromisem je, že nevidíte vnitřek volání funkce jako samostatné kroky, ale to je správné chování. Zobrazování redukce argumentů, které Excel nikdy neprovádí, by bylo zavádějícím trasováním oproti tomu, když se s voláním zachází jako s jedinou jednotkou vyhodnocení, kterou ve skutečnosti je
Oddělovače argumentů a nedotčené rozsahy
Další dvě normalizace udržují přepočet správný. Kompilátor výpočetního jádra očekává jako oddělovač argumentů funkce znak ;, takže když trasovač znovu sestavuje volání funkce ze svého syntaktického stromu, spojuje argumenty pomocí ;, i když uživatel původně napsal čárku ,. Vzorec zapsaný jako SUM(A1,A2,A3) se přepočítává jako SUM(A1;A2;A3), což jádro akceptuje. Dosazování hodnot je tím, co toto nové sestavení vyžaduje, a správný oddělovač je klíčem k tomu, aby šlo výraz znovu analyzovat
Druhým případem jsou odkazy na rozsahy. Rozsah jako A1:A3 není skalár a nesmí být rozdělen na tři samostatné hodnoty, protože funkce, která jej spotřebovává, očekává jako argument rozsah. Trasovač ponechává rozsah nedotčený v jeho původním textu a nechává obalující funkci zredukovat jako celek. Ve výrazu SUM(A1:A3)*B1 zůstává rozsah celý, SUM(A1:A3) se zredukuje na jedno číslo v jednom atomickém kroku a teprve potom se provede vnější násobení. Jde o stejnou hranici, jakou Excel kreslí mezi operandem rozsahu a skalárem, kterým nakonec přispívá
// 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));Všechna tato pravidla dohromady dělají ze seznamu kroků věrný odraz příkazu Vyhodnocení vzorce v Excelu a nikoli pouze jeho přibližnou verzi. Redukce probíhají v pořadí, v jakém je provádí Excel, dosazené literály obstojí v jakémkoli národním prostředí, logické hodnoty se přetypovávají způsobem, jakým je přetypovává Excel, a líné funkce zůstávají líné. Pokud chcete jádro rozšířit o vlastní funkce, článek o jádru vzorců a vlastních funkcích popisuje způsob jejich registrace, a pro náročnější matematické výpočty se článek o statistických rozdělovacích funkcích v Delphi věnuje vestavěné knihovně, vůči níž trasovač vyhodnocuje. To vše se dodává jako součást produktu HotXLS spreadsheet component pro Delphi a C++Builder, společně s rozhraními API pro čtení, zápis, formátování a výpočty popsanými na jiných místech tohoto blogu