Śledzenie oceny formuł Excel krok po kroku w Delphi

Excel ukrywa na widoku mały debuger. Zaznacz komórkę, otwórz zakładkę Formuły i kliknij Oblicz formułę - pojawi się okno dialogowe z formułą, w której podkreślone jest jedno podwyrażenie. Naciśnij Oblicz, a podwyrażenie zwijnie się do swojej wartości, następnie podkreślone zostanie kolejne i możesz obserwować, jak długie wyrażenie kurczy się do pojedynczej liczby, jeden krok redukcji na raz. To najszybszy sposób na znalezienie, która gałąź zagnieżdżonego IF naprawdę się wykonała albo które odwołanie dostarczyło błędnej sumy. HotXLS odwzorowuje dokładnie to zachowanie przez TXLSFormulaTracer, tak że program w Delphi lub C++Builder może wyświetlić tę samą listę kroków przy audycie skoroszytu, debugowaniu wygenerowanej formuły lub wyjaśnianiu komuś, dlaczego wynik wyszedł taki, a nie inny. Każdy zarejestrowany krok zawiera tekst podwyrażenia i wartość, do której się redukuje

Jak silnik redukcji przechodzi przez wyrażenie

Tracer nie wnika do silnika obliczeniowego. Tokenizuje formułę i parsuje ją rekurencyjnym parserem zstępującym, a następnie redukuje drzewo metodą depth-first - najpierw najbardziej wewnętrzne, obliczalne podwyrażenie. Gdy węzeł redukuje się do wartości, ta wartość jest podstawiana z powrotem do otaczającego wyrażenia jako literał, a silnik prosi właściwy kalkulator o przeliczenie teraz prostszego wyrażenia. Ponieważ każdy krok jest obliczany przez publiczną metodę Calculate arkusza, a nie przez prywany skrót, każdy krok dokładnie zgadza się z tym, co pełne przeliczenie komórki by wyprodukowało. Parser jest z założenia nieinwazyjny, co pozwala mu działać na dowolnym arkuszu bez naruszania jego stanu

Parser stosuje drabinę pierwszeństwa operatorów z jednym poziomem rekurencji na pasmo pierwszeństwa. Od najsłabiej do najsilniej wiążącego: poziom 0 to porównania (=, <>, <, >, <=, >=), poziom 1 to konkatenacja ciągów (&), poziom 2 to dodawanie i odejmowanie, poziom 3 to mnożenie i dzielenie, poziom 4 to potęgowanie, a poniżej tego jednoargumentowe plus i minus. Każdy poziom parsuje poziom powyżej jako swoje operandy, więc wyższe pasmo wiąże mocniej. To takie samo pierwszeństwo jak w Excelu, dlatego A1*B1+A2*B1 redukuje oba iloczyny przed sumą: mnożenie jest na poziomie 3, dodawanie na poziomie 2, więc mnożenia są głębiej w drzewie i redukują się pierwsze

Śledzenie formuły i przechodzenie przez kroki

Użycie jest wzorowane na dostarczonym demo w Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Zbuduj arkusz (lub otwórz istniejący skoroszyt), skonstruuj tracer nad arkuszem, wywołaj Trace i iteruj po zwróconej tablicy. Każdy TXLSFormulaStep udostępnia Depth do wcięcia, Source dla oryginalnego podwyrażenia, Expression dla tego podwyrażenia z już podstawionymi operandami i Value dla wyniku 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 liczba sztuk
    Sheet.Cells[1, 2].Value := 25;    // B1 cena jednostkowa
    Sheet.Cells[1, 3].Value := 0.08;  // C1 stawka podatku

    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;

Odwołania do komórek redukują się najpierw i pojawiają jako własne kroki, następnie redukują się iloczyny, potem ujęty w nawiasy czynnik podatkowy i na koniec zamyka się ostatnie mnożenie. Pole Depth pozwala tworzyć wcięcia, dzięki czemu najbardziej wewnętrzne redukcje widocznie leżą najgłębiej - dokładnie tak jak Excel podkreśla najbardziej wewnętrzny człon przed jakimkolwiek zewnętrznym

Pułapka literałów niezależnych od ustawień regionalnych

Najgroźniejszy szczegół w całym tym schemacie jest niewidoczny na angielskim komputerze i głośno się psuje na niemieckim. Gdy obliczona liczba jest podstawiana z powrotem do tekstu formuły, musi być zapisana jako ciąg, a następnie ponownie sparsowana przez silnik obliczeniowy, który traktuje . jako separator dziesiętny. Jeśli podstawienie używałoby ustawień regionalnych systemu, niemieckie TFormatSettings zapisałoby czynnik podatkowy jako 1,08, przecinek byłby odczytany jako separator argumentów i ponowne obliczenie A1*B1*1,08 albo sparsowałoby się w złą strukturę, albo całkowicie by się nie powiodło

Tracer unika tego przez formatowanie każdego literału numerycznego przez prywatne TFormatSettings, które przypinane są w momencie konstrukcji: DecimalSeparator wymuszony na ., a ThousandSeparator ustawiony na #0, żeby żaden znak grupowania nie był emitowany. Wtedy FloatToStr produkuje literał, który silnik zawsze może z powrotem odczytać - niezależnie od regionalnych ustawień operatora

// Konceptualnie to, co tracer przypina raz, przy konstrukcji
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// każda zredukowana liczba jest zapisywana przez: FloatToStr(Double(V), FFloatFmt)

To taki błąd, który nigdy nie pojawia się w testach autora i ujawnia się dopiero gdy klient w innym regionie uruchomi ten sam kod - warto więc powiedzieć wprost: round-tripping wartości przez tekst formuły to problem serializacji, a serializacja musi być niezależna od ustawień regionalnych

Wartości logiczne redukują się do 1 i 0

Pokrewna decyzja dotyczy wartości logicznych. Gdy podwyrażenie ocenia się do wartości logicznej, tracer zapisuje ją z powrotem jako 1 lub 0, nie jako TRUE ani FALSE. Powodem jest to, że zredukowany literał musi się z powrotem czysto sparsować w kontekście otaczającym go, a najbardziej wymagającym przypadkiem jest arytmetyka. Gdyby porównanie takie jak A1>A2 redukowało się do tekstu TRUE i ten tekst lądował wewnątrz TRUE*B1, ponowne obliczenie zależałoby od tego, czy silnik akceptuje goły słownik kluczowy boolean w pozycji mnożenia. Podstawienie 1 całkowicie omija to pytanie, bo 1*B1 jest jednoznaczne w dowolnej pozycji arytmetycznej. Odpowiada też własnej koercji Excela, gdzie TRUE zachowuje się jak 1, a FALSE jak 0 w momencie, gdy oczekiwana jest liczba

Wywołania funkcji redukują się atomowo

Naiwny silnik kroków redukowałby najpierw argumenty funkcji, a potem wywołanie. To jest błędne dla Excela i tracer celowo tego nie robi. Wywołanie funkcji jest obliczane jako całość, z oryginalnego tekstu, w jednym kroku. Powodem jest semantyka krótkiego zwarcia. IF, CHOOSE i IFERROR obliczają tylko wybraną gałąź, a redukcja argumentów najpierw zmusiłaby silnik do obliczenia gałęzi, których Excel nigdy nie dotyka. Klasyczną ofiarą jest zabezpieczenie przed dzieleniem przez zero jak IF(B1=0,0,A1/B1): gdyby tracer zredukował A1/B1 przed oceną IF, zabezpieczenie by zawiodło i wywołało właśnie ten błąd, przed którym ma chronić. Obliczając całe wywołanie atomowo, tracer zachowuje leniwe obliczanie, które sprawia, że takie zabezpieczenia działają

// IF to jeden atomowy krok; obliczana jest tylko wybrana gałąź
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 jest prawdą, więc krok rejestruje A1*B1 jako wybrany wynik;
// A2*B1 nigdy nie jest obliczane, dokładnie tak jak Excel by to zrobił.

Kompromisem jest to, że nie widzisz wewnętrznych kroków wywołania funkcji jako oddzielnych kroków - ale to jest prawidłowe zachowanie. Pokazywanie redukcji argumentów, których Excel nigdy nie wykonuje, byłoby bardziej mylącym śladem niż traktowanie wywołania jako pojedynczej jednostki obliczeniowej, jaką naprawdę jest

Separatory argumentów i nienaruszone zakresy

Dwie kolejne normalizacje dbają o rzetelność ponownego obliczania. Kompilator silnika obliczeniowego oczekuje ; jako separatora argumentów funkcji, więc gdy tracer odbudowuje wywołanie funkcji z parsowanego drzewa, łączy argumenty przez ; - nawet jeśli użytkownik pierwotnie wpisał ,. Formuła zapisana jako SUM(A1,A2,A3) jest przeliczana jako SUM(A1;A2;A3), co silnik akceptuje. Podstawienie wartości sprawia, że ta przebudowa jest konieczna, a właściwy separator sprawia, że przebudowa się parsuje

Odwołania do zakresów to drugi przypadek. Zakres taki jak A1:A3 nie jest skalarem i nie może być rozbity na trzy osobne wartości, bo funkcja, która go konsumuje, oczekuje argumentu zakresu. Tracer zachowuje zakres nienaruszony jako oryginalny tekst i pozwala otaczającej funkcji redukować się jako całość. W SUM(A1:A3)*B1 zakres pozostaje cały, SUM(A1:A3) redukuje się do jednej liczby w jednym atomowym kroku, a dopiero wtedy uruchamia się zewnętrzne mnożenie. To ta sama granica, jaką Excel rysuje między operandem zakresu a skalarem, do którego ostatecznie się przyczynia

// Zakres A1:A3 nigdy nie jest dzielony; SUM to jedna atomowa redukcja,
// a następnie na jej podstawie redukuje się iloczyn z B1.
Final := Tracer.Trace('SUM(A1:A3)*B1', Steps);
for I := 0 to High(Steps) do
  Writeln(Steps[I].Source, ' = ', VarToStr(Steps[I].Value));

Razem te zasady sprawiają, że lista kroków jest wiernym odzwierciedleniem polecenia Oblicz formułę Excela, a nie jego przybliżeniem. Redukcje następują w kolejności, w jakiej Excel je wykonuje, podstawiane literały przeżywają każde ustawienia regionalne, wartości logiczne koercjonują tak jak Excel i leniwe funkcje pozostają leniwe. Jeśli chcesz rozbudować silnik o własne funkcje, artykuł o silniku formuł i funkcjach niestandardowych pokazuje, jak je rejestrować, a dla bardziej zaawansowanych obliczeń numerycznych artykuł o statystycznych funkcjach rozkładu w Delphi omawia wbudowaną bibliotekę, względem której tracer oblicza. Wszystko to wchodzi w skład komponentu HotXLS dla Delphi i C++Builder, wraz z API do odczytu, zapisu, formatowania i obliczania omówionymi w pozostałych artykułach tego bloga