Пошаговая трассировка вычисления формул Excel в Delphi

Excel скрывает небольшой отладчик прямо на виду. Выберите ячейку, откройте раздел «Формулы» и нажмите «Вычислить формулу» - откроется диалог с подчёркнутым подвыражением. Нажмите «Вычислить», и это подвыражение свернётся в своё значение, затем подчёркнется следующее, и вы будете наблюдать, как длинное выражение сворачивается в одно число, шаг за шагом. Это самый быстрый способ выяснить, какая ветвь вложенного IF действительно сработала или какая ссылка дала неправильный итог. HotXLS воспроизводит именно такое поведение через TXLSFormulaTracer, поэтому программа на Delphi или C++Builder может отображать тот же список шагов для аудита книги, отладки сгенерированной формулы или объяснения причин получения того или иного результата. Каждый записанный шаг содержит текст подвыражения и значение, в которое оно сворачивается

Как механизм сворачивания обходит выражение

Трассировщик не обращается напрямую к движку вычислений. Он токенизирует формулу и разбирает её рекурсивно-нисходящим парсером, затем сворачивает дерево в глубину - сначала самое внутреннее вычислимое подвыражение. Когда узел сворачивается в значение, это значение подставляется обратно в окружающее выражение как литерал, и движок просит реальный калькулятор вычислить теперь более простое выражение. Поскольку каждый шаг вычисляется через публичный метод Calculate листа, а не через приватный обходной путь, каждый шаг точно совпадает с тем, что дало бы полное пересчитывание ячейки. Парсер неинвазивен по своему дизайну, что позволяет запускать его для любого листа без изменения его состояния

Парсер следует иерархии приоритетов операторов с одним рекурсивным уровнем для каждой полосы приоритета. От наименее связывающего к наиболее связывающему полосы таковы: уровень 0 - сравнение (=, <>, <, >, <=, >=), уровень 1 - конкатенация строк (&), уровень 2 - сложение и вычитание, уровень 3 - умножение и деление, уровень 4 - возведение в степень, и наконец унарный плюс и минус ниже этого. Каждый уровень разбирает уровень выше для своих операндов, поэтому более высокий уровень связывает сильнее. Это тот же приоритет, который применяет Excel, вот почему в A1*B1+A2*B1 два произведения сворачиваются до суммы: умножение находится на уровне 3, сложение на уровне 2, поэтому умножения глубже в дереве и сворачиваются первыми

Трассировка формулы и обход шагов

Использование отражает поставляемый пример из Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Постройте рабочий лист (или откройте существующую книгу), создайте трассировщик для листа, вызовите Trace и переберите возвращённый массив. Каждый элемент TXLSFormulaStep раскрывает: Depth для отступа, Source для исходного подвыражения, Expression для этого подвыражения с уже подставленными операндами и Value для результата шага

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;

Ссылки на ячейки разрешаются первыми и появляются как собственные шаги, затем сворачиваются произведения, затем взятый в скобки налоговый коэффициент, и финальное умножение закрывает всё. Поле Depth позволяет делать отступ, чтобы самые внутренние сворачивания были визуально глубже, точно так же, как Excel подчёркивает самый внутренний терм перед любым внешним

Ловушка локалезависимых литералов

Самая опасная деталь этой схемы невидима на английской машине и громко ломается на немецкой. Когда вычисленное число подставляется обратно в текст формулы, его нужно записать как строку, а затем снова разобрать движком вычислений, который считает . десятичным разделителем. Если бы подстановка использовала системную локаль, немецкий TFormatSettings записал бы 1,08 для налогового коэффициента, запятая воспринималась бы как разделитель аргументов, и повторное вычисление A1*B1*1,08 либо разобралось бы в неверную структуру, либо вовсе завершилось бы ошибкой

Трассировщик избегает этого, форматируя каждый числовой литерал через приватный TFormatSettings, который он фиксирует при создании, с DecimalSeparator, принудительно установленным в ., и ThousandSeparator, установленным в #0, чтобы символ группировки никогда не выводился. FloatToStr затем выдаёт литерал, который движок всегда может прочитать обратно, независимо от региональных настроек оператора

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

Это тот вид ошибки, который никогда не проявляется при тестировании автора и всплывает только тогда, когда клиент в другой локали запускает тот же код, поэтому стоит прямо сказать: передача значения через текст формулы - это задача сериализации, и сериализация должна быть независима от локали

Булевы значения сворачиваются в 1 и 0

Связанное решение по подстановке касается логических значений. Когда подвыражение вычисляется в булево значение, трассировщик записывает его обратно как 1 или 0, а не как TRUE или FALSE. Причина в том, что сведённый литерал должен чисто разбираться в любом окружающем контексте, а арифметика - самый требовательный случай. Если бы сравнение вида A1>A2 свелось к тексту TRUE и этот текст оказался внутри TRUE*B1, повторное вычисление зависело бы от принятия движком голого булева ключевого слова в позиции умножения. Подстановка 1 полностью обходит этот вопрос, потому что 1*B1 однозначно в любой арифметической позиции. Это также соответствует собственному приведению Excel, где TRUE ведёт себя как 1, а FALSE как 0, как только ожидается число

Вызовы функций сворачиваются атомарно

Наивный пошаговый движок сначала сворачивал бы аргументы функции, а затем сам вызов. Это неверно для Excel, и трассировщик намеренно так не делает. Вызов функции вычисляется целиком, из своего исходного текста, за один шаг. Причина - семантика короткой цепи. IF, CHOOSE и IFERROR вычисляют только ту ветвь, которую выбирают, и предварительное сворачивание аргументов вынуждало бы движок вычислять ветви, которые Excel никогда не трогает. Классическая жертва - защита от деления на ноль вида IF(B1=0,0,A1/B1): если бы трассировщик свернул A1/B1 до вычисления IF, защита сработала бы неверно и вызвала именно ту ошибку, которую призвана предотвращать. Вычисляя весь вызов атомарно, трассировщик сохраняет ленивые вычисления, которые делают такие защиты рабочими

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

Компромисс в том, что вы не видите внутренности вызова функции как отдельные шаги, но это правильное поведение. Показывать сворачивания аргументов, которые Excel никогда не выполняет, означало бы создавать более вводящий в заблуждение трейс, чем трактовка вызова как единого блока вычислений, которым он по сути и является

Разделители аргументов и неизменяемые диапазоны

Ещё две нормализации сохраняют корректность повторного вычисления. Компилятор движка вычислений ожидает ; в качестве разделителя аргументов функции, поэтому, когда трассировщик перестраивает вызов функции из разобранного дерева, он соединяет аргументы через ;, даже если пользователь изначально написал ,. Формула, написанная как SUM(A1,A2,A3), повторно вычисляется как SUM(A1;A2;A3), что движок принимает. Подстановка значений и делает такое перестроение необходимым, а правильный разделитель делает перестроение разбираемым

Ссылки на диапазоны - второй случай. Диапазон вроде A1:A3 не является скалярным и не должен быть разбит на три отдельных значения, потому что потребляющая его функция ожидает аргумент-диапазон. Трассировщик сохраняет диапазон целым как исходный текст и позволяет охватывающей функции свернуться как единому целому. В SUM(A1:A3)*B1 диапазон остаётся целым, SUM(A1:A3) сворачивается в одно число за один атомарный шаг, и только тогда выполняется внешнее умножение. Это та же граница, которую Excel проводит между операндом-диапазоном и скаляром, в который он в итоге вносит вклад

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

Вместе эти правила делают список шагов точным отражением команды «Вычислить формулу» в Excel, а не её приближением. Сворачивания происходят в том порядке, в котором Excel их выполняет, подставленные литералы переживают любую локаль, булевы значения приводятся так, как их приводит Excel, и ленивые функции остаются ленивыми. Если вы хотите расширить движок собственными функциями, в статье о движке формул и пользовательских функциях показано, как их зарегистрировать, а для более тяжёлых числовых вычислений в статье о функциях статистического распределения в Delphi описана встроенная библиотека, по которой трассировщик вычисляет. Всё это поставляется в составе компонента HotXLS для Delphi и C++Builder, наряду с API чтения, записи, форматирования и вычисления, описанными в других статьях этого блога