Rastrear a Avaliação de Fórmulas do Excel Passo a Passo em Delphi

O Excel esconde um pequeno depurador num local visível. Selecione uma célula, abra a secção Fórmulas e clique em Avaliar Fórmula; surge uma caixa de diálogo que apresenta a fórmula com uma subexpressão sublinhada. Ao clicar em Avaliar, essa subexpressão contrai-se no seu valor, a seguinte passa a estar sublinhada, e observa uma expressão longa a reduzir-se a um único número, uma redução de cada vez. É a forma mais rápida de descobrir qual o ramo de um IF aninhado que foi efetivamente executado, ou qual a referência que alimentou um total incorreto. O HotXLS reproduz exatamente esse comportamento através de TXLSFormulaTracer, permitindo que uma aplicação em Delphi ou C++Builder renderize a mesma lista de passos para auditoria de um livro, depuração de uma fórmula gerada ou para explicar como um resultado foi obtido. Cada passo registado contém o texto da subexpressão e o valor ao qual se reduz

O motor de redução não interfere no motor de cálculo. Segmenta a fórmula em tokens e analisa-a com um analisador sintático descendente recursivo (recursive-descent parser), reduzindo depois a árvore com pesquisa em profundidade (depth-first), começando pela subexpressão avaliável mais interna. Quando um nó se reduz a um valor, esse valor é substituído na expressão envolvente como um literal, e o motor solicita à calculadora real que recalcule a expressão simplificada. Como cada passo é avaliado através do método público Calculate da folha de cálculo, em vez de um atalho privado, cada passo corresponde exatamente ao que um recálculo completo da célula produziria. O analisador sintático é não invasivo por design, o que permite a sua execução em qualquer folha de cálculo sem perturbar o seu estado

O analisador segue uma escala de precedência de operadores, com um nível recursivo por faixa de precedência. Do menor vínculo para o maior, as faixas são: nível 0 comparação (=, <>, <, >, <=, >=), nível 1 concatenação de cadeias de caracteres (&), nível 2 adição e subtração, nível 3 multiplicação e divisão, nível 4 exponenciação, e finalmente os operadores unários mais e menos abaixo desses. Cada nível analisa o nível superior para obter os seus operandos, pelo que uma faixa mais elevada se vincula com maior força. Esta é a mesma precedência que o Excel aplica, razão pela qual A1*B1+A2*B1 reduz os dois produtos antes da soma: a multiplicação situa-se no nível 3 e a adição no nível 2, pelo que as multiplicações encontram-se em posições mais profundas na árvore e reduzem-se primeiro

Rastrear uma fórmula e percorrer os passos

A utilização assemelha-se à demonstração fornecida em Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Construa uma folha de cálculo (ou abra um livro existente), crie um rastreador sobre a folha, chame Trace e itere sobre a matriz devolvida. Cada estrutura TXLSFormulaStep expõe Depth para recuo de texto, Source para a subexpressão de origem, Expression para essa subexpressão com os 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;    // unidades A1
    Sheet.Cells[1, 2].Value := 25;    // preço unitário B1
    Sheet.Cells[1, 3].Value := 0.08;  // taxa de imposto C1

    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 às células são resolvidas primeiro e surgem como os seus próprios passos; depois, reduzem-se os produtos, seguidos do fator de imposto entre parênteses, fechando com a multiplicação final. O campo Depth permite-lhe efetuar recuos de texto para que as reduções mais internas fiquem visivelmente mais profundas, tal como o Excel sublinha o termo mais interno antes de qualquer outro exterior

A armadilha dos literais dependentes de definições regionais

O detalhe mais perigoso em todo este processo é invisível numa máquina configurada em inglês e falha visivelmente numa em alemão. Quando um número calculado é substituído de volta no texto da fórmula, deve ser escrito como uma cadeia de caracteres e novamente analisado pelo motor de cálculo, que trata o . como o ponto decimal. Se a substituição usasse as definições regionais do sistema, um TFormatSettings alemão escreveria 1,08 para o fator de imposto; a vírgula seria interpretada como um separador de argumentos, e o recálculo de A1*B1*1,08 seria analisado com a estrutura incorreta ou falharia por completo

O rastreador evita esta situação ao formatar cada literal numérico através de um TFormatSettings privado que fixa na construção, com a propriedade DecimalSeparator forçada a . e ThousandSeparator definida como #0 para que nenhum carácter de agrupamento seja emitido. A função FloatToStr produz então um literal que o motor consegue ler de volta, independentemente das definições regionais do operador

// Concetualmente o que o rastreador fixa uma vez, na construção
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// cada número reduzido é escrito com: FloatToStr(Double(V), FFloatFmt)

Este é o tipo de bug que nunca surge nos testes do próprio autor e apenas se manifesta quando um cliente noutra definição regional executa o mesmo código, pelo que convém ser claro: a passagem de ida e volta de um valor através do texto da fórmula constitui um problema de serialização, e a serialização deve ser isenta de definições regionais (locale-free)

Os booleanos reduzem-se a 1 e 0

Uma decisão de substituição relacionada diz respeito aos valores lógicos. Quando uma subexpressão é avaliada como um booleano, o rastreador escreve 1 ou 0 de volta, e não TRUE ou FALSE. O motivo reside no facto de o literal reduzido ter de ser novamente analisado sem problemas em qualquer contexto envolvente, sendo a aritmética o caso mais exigente. Se uma comparação como A1>A2 se reduzisse ao texto TRUE e esse texto ficasse inserido em TRUE*B1, o recálculo dependeria de o motor aceitar uma palavra-chave booleana pura numa multiplicação. A substituição por 1 contorna a questão por completo, visto que 1*B1 é inequívoco em qualquer posição aritmética. Corresponde também à coerção do próprio Excel, onde TRUE se comporta como 1 e FALSE como 0 no momento em que um número é esperado

As chamadas de funções reduzem-se de forma atómica

Um motor de passos simples reduziria primeiro os argumentos de uma função e só depois a chamada. Isso é incorreto para o Excel, e o rastreador deliberadamente não o faz. Uma chamada de função é avaliada como um todo, a partir do seu texto original, num único passo. O motivo reside na semântica de curto-circuito (short-circuit). As funções IF, CHOOSE e IFERROR avaliam apenas o ramo que selecionam, e reduzir os argumentos primeiro forçaria o motor a calcular ramos aos quais o Excel nunca acede. A vítima clássica é uma proteção contra a 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 falharia e geraria precisamente o erro que visa evitar. Ao avaliar toda a chamada de forma atómica, o rastreador preserva a avaliação preguiçosa (lazy evaluation) que permite o funcionamento destas proteções

// IF é um passo atómico; apenas o ramo selecionado é avaliado
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 é verdadeiro, pelo que o passo regista A1*B1 como o resultado escolhido;
// A2*B1 nunca é calculado, exatamente como o Excel faria.

A desvantagem é que não vê o interior da chamada da função como passos separados, mas este é o comportamento correto. Apresentar reduções de argumentos que o Excel nunca executa seria um rastreamento mais enganador do que tratar a chamada como a unidade de avaliação única que ela realmente é

Separadores de argumentos e intervalos intactos

Mais duas normalizações mantêm a fidelidade do recálculo. O compilador do motor de cálculo espera ; como separador de argumentos de funções, pelo que, quando o rastreador reconstrói uma chamada de função a partir da sua árvore analisada, une os argumentos com ;, mesmo que o utilizador tenha originalmente digitado ,. Uma fórmula escrita como SUM(A1,A2,A3) é recalculada como SUM(A1;A2;A3), formato que o motor aceita. A substituição de valores é o que torna esta reconstrução necessária, e a definição correta do separador é o que viabiliza a análise da reconstrução

As referências a intervalos constituem o outro cenário. Um intervalo como A1:A3 não é um escalar e não deve ser dividido em três valores distintos, porque a função que o consome espera um argumento do tipo intervalo. O rastreador mantém o intervalo intacto com o seu texto original e permite que a função envolvente se reduza como um todo. Em SUM(A1:A3)*B1 o intervalo permanece completo, SUM(A1:A3) reduz-se a um único número num passo atómico, e só depois é executada a multiplicação externa. Esta é a mesma fronteira que o Excel define entre um operando de intervalo e o escalar que este acaba por fornecer

// O intervalo A1:A3 nunca é dividido; SUM é uma redução atómica,
// e o produto por B1 reduz-se a partir daí.
Final := Tracer.Trace('SUM(A1:A3)*B1', Steps);
for I := 0 to High(Steps) do
  Writeln(Steps[I].Source, ' = ', VarToStr(Steps[I].Value));

Em conjunto, estas regras fazem com que a lista de passos seja um reflexo fiel do comando Avaliar Fórmula do Excel e não uma mera aproximação. As reduções ocorrem na ordem que o Excel aplica, os literais substituídos são resilientes a quaisquer definições regionais, os booleanos seguem as mesmas coerções do Excel, e as funções lazy continuam lazy. Se pretender estender as capacidades do motor com as suas próprias funções, o artigo sobre o motor de fórmulas e funções personalizadas mostra como as registar, e para processamento numérico mais avançado o artigo sobre funções de distribuição estatística em Delphi aborda a biblioteca incorporada contra a qual o rastreador efetua a avaliação. Todas estas funcionalidades são fornecidas como parte do HotXLS spreadsheet component para Delphi e C++Builder, juntamente com as APIs de leitura, escrita, formatação e cálculo abordadas noutras secções deste blog