Rastreie a Avaliação de Fórmulas Excel Passo a Passo no Delphi

O Excel esconde um pequeno depurador à vista de todos. Selecione uma célula, abra Fórmulas e clique em Avaliar Fórmula, e uma caixa de diálogo mostra a fórmula com uma subexpressão sublinhada. Pressione Avaliar e essa subexpressão colapsa para seu valor, então a próxima é sublinhada, e você observa uma expressão longa encolher até um único número uma redução de cada vez. É a forma mais rápida de descobrir qual ramificação de um IF aninhado realmente disparou, ou qual referência alimentou um total errado. O HotXLS reproduz esse comportamento exato através de TXLSFormulaTracer, para que um programa Delphi ou C++Builder possa renderizar a mesma lista de passos para auditar uma pasta de trabalho, depurar uma fórmula gerada ou ensinar alguém por que um resultado saiu do jeito que saiu. Cada passo registrado carrega o texto da subexpressão e o valor ao qual ela se reduz

Como o motor de redução percorre a expressão

O rastreador não alcança o motor de cálculo. Ele tokeniza a fórmula e a analisa com um parser de descida recursiva, depois reduz a árvore em profundidade primeiro, subexpressão avaliável mais interna primeiro. Quando um nó se reduz a um valor, esse valor é substituído de volta na expressão ao redor como um literal, e o motor pede ao calculador real que recompute a expressão agora mais simples. Como cada passo é avaliado através do método público Calculate da planilha em vez de um atalho privado, cada passo concorda exatamente com o que um recálculo completo da célula produziria. O parser é não invasivo por design, que é o que permite que ele rode contra qualquer planilha sem perturbar seu estado

O parser segue uma hierarquia de precedência de operadores, com um nível recursivo por faixa de precedência. Do vínculo mais fraco ao mais forte, as faixas são: nível 0 comparação (=, <>, <, >, <=, >=), nível 1 concatenação de string (&), nível 2 adição e subtração, nível 3 multiplicação e divisão, nível 4 exponenciação, e finalmente mais e menos unários abaixo disso. Cada nível analisa o nível acima para seus operandos, portanto uma faixa mais alta vincula mais fortemente. Esta é a mesma precedência que o Excel aplica, por isso A1*B1+A2*B1 reduz os dois produtos antes da soma: a multiplicação está no nível 3, a adição no nível 2, portanto as multiplicações ficam mais profundas na árvore e se reduzem primeiro

Rastreando uma fórmula e percorrendo os passos

O uso espelha a demo fornecida em Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Construa uma planilha (ou abra uma pasta de trabalho existente), construa um rastreador sobre a planilha, chame Trace e itere o array retornado. Cada TXLSFormulaStep expõe Depth para indentação, Source para a subexpressão original, Expression para essa subexpressão com seus operandos já substituídos e Value para o resultado do passo

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;

As referências de célula se resolvem primeiro e aparecem como seus próprios passos, depois os produtos se reduzem, depois o fator de imposto entre parênteses, e a multiplicação final fecha tudo. O campo Depth permite indentar para que as reduções mais internas fiquem visivelmente mais profundas, exatamente como o Excel sublinha o termo mais interno antes de qualquer um externo

A armadilha do literal sem localidade

O detalhe mais perigoso em todo esse esquema é invisível em uma máquina em inglês e quebra ruidosamente em uma alemã. Quando um número calculado é substituído de volta no texto da fórmula, ele tem que ser escrito como uma string e depois re-analisado pelo motor de cálculo, que trata . como o separador decimal. Se a substituição usasse a localidade do sistema, um TFormatSettings alemão escreveria 1,08 para o fator de imposto, a vírgula seria lida como um separador de argumento, e a recomputação de A1*B1*1,08 ou analisaria na forma errada ou falharia completamente

O rastreador evita isso formatando cada literal numérico através de um TFormatSettings privado que ele fixa na construção, com DecimalSeparator forçado para . e ThousandSeparator definido como #0 para que nenhum caractere de agrupamento seja emitido. FloatToStr então produz um literal que o motor sempre pode ler de volta, independentemente das configurações regionais do operador

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

Este é o tipo de bug que nunca aparece nos testes do próprio autor e só surge quando um cliente em outra localidade executa o mesmo código, portanto vale a pena afirmar claramente: fazer um valor circular através de texto de fórmula é um problema de serialização, e a serialização deve ser livre de localidade

Booleanos se reduzem a 1 e 0

Uma decisão de substituição relacionada diz respeito a valores lógicos. Quando uma subexpressão avalia para um booleano, o rastreador o escreve de volta como 1 ou 0, não como TRUE ou FALSE. O motivo é que o literal reduzido tem que re-analisar de forma limpa em qualquer contexto que o envolva, e a aritmética é o caso exigente. Se uma comparação como A1>A2 se reduzisse ao texto TRUE e esse texto caísse dentro de TRUE*B1, a recomputação dependeria de o motor aceitar uma palavra-chave booleana simples em uma multiplicação. Substituir 1 evita a questão completamente, porque 1*B1 é inequívoco em qualquer posição aritmética. Também corresponde à própria coerção do Excel, onde TRUE se comporta como 1 e FALSE como 0 no momento em que um número é esperado

Chamadas de função se reduzem atomicamente

Um motor de passos ingênuo reduziria os argumentos de uma função primeiro e depois a chamada. Isso está errado para o Excel, e o rastreador deliberadamente não faz isso. Uma chamada de função é avaliada como um todo, a partir de seu texto original, em um único passo. O motivo é a semântica de curto-circuito. IF, CHOOSE e IFERROR avaliam apenas a ramificação que selecionam, e reduzir os argumentos primeiro forçaria o motor a computar ramificações que o Excel nunca toca. A vítima clássica é uma proteção contra divisão por zero como IF(B1=0,0,A1/B1): se o rastreador reduzisse A1/B1 antes de avaliar o IF, a proteção dispararia incorretamente e geraria exatamente o erro que existe para prevenir. Ao avaliar a chamada inteira atomicamente, o rastreador preserva a avaliação preguiçosa que faz essas proteções funcionarem

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

A troca é que você não vê dentro da chamada de função como passos separados, mas esse é o comportamento correto. Mostrar reduções de argumentos que o Excel nunca executa seria um rastreamento mais enganoso do que tratar a chamada como a unidade de avaliação única que realmente é

Separadores de argumento e intervalos intactos

Duas normalizações adicionais mantêm a recomputação honesta. O compilador do motor de cálculo espera ; como separador de argumento de função, portanto quando o rastreador reconstrói uma chamada de função a partir de sua árvore analisada ele junta argumentos com ;, mesmo que o usuário originalmente tenha digitado ,. Uma fórmula escrita como SUM(A1,A2,A3) é recomputada como SUM(A1;A2;A3), que o motor aceita. A substituição de valores é o que torna essa reconstrução necessária, e acertar o separador é o que faz a reconstrução ser analisada

Referências de intervalo são o outro caso. Um intervalo como A1:A3 não é um escalar e não deve ser dividido em três valores separados, porque a função que o consome espera um argumento de intervalo. O rastreador mantém um intervalo intacto como seu texto original e deixa a função envolvente se reduzir como um todo. Em SUM(A1:A3)*B1, o intervalo permanece inteiro, SUM(A1:A3) se reduz a um número em um passo atômico, e somente então a multiplicação externa roda. Este é o mesmo limite que o Excel traça entre um operando de intervalo e o escalar que eventualmente contribui

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

Juntas, essas regras fazem da lista de passos um espelho fiel do comando Avaliar Fórmula do Excel em vez de uma aproximação dele. As reduções acontecem na ordem em que o Excel as realiza, os literais substituídos sobrevivem a qualquer localidade, os booleanos coagem da forma como o Excel os coage e as funções preguiçosas permanecem preguiçosas. Se você quiser impulsionar ainda mais o motor com suas próprias funções, o artigo sobre motor de fórmulas e funções personalizadas mostra como registrá-las, e para trabalho numérico mais pesado, o artigo sobre funções de distribuição estatística no Delphi cobre a biblioteca integrada contra a qual o rastreador avalia. Tudo isso é fornecido como parte do componente de planilha HotXLS para Delphi e C++Builder, ao lado das APIs de leitura, escrita, formatação e cálculo cobertas em outros lugares neste blog