Artigo Técnico

Recálculo Incremental de Fórmulas no HotXLS para Delphi

Uma coluna de 10.000 fórmulas, cada uma fazendo crescer o valor da célula acima dela, é um dos modelos mais baratos que uma planilha pode ter. Em um avaliador que computa uma fórmula referenciada na hora, toda vez que a encontra, os totais saem certos, mas a conta de aritmética não sai: a última célula arrasta 9.999 ancestrais pela avaliação, a de cima 9.998, e uma única passada completa soma cerca de 50 milhões de avaliações para 10.000 multiplicações. Mude uma taxa de crescimento no topo e esse avaliador precisa pagar a conta inteira de novo, porque ele não guarda registro de qual célula lê qual

Por que uma célula editada pode custar uma passada pela pasta de trabalho inteira

Dois atalhos produzem esse comportamento, e a maioria dos motores de fórmulas feitos à mão pega um deles. O atalho recursivo avalia de novo cada fórmula referenciada e ignora qualquer valor já em cache, o que transforma uma cadeia em trabalho quadrático e uma referência circular em stack overflow. O atalho de passada completa é mais seguro: avalie toda fórmula em alguma ordem fixa, quantas vezes for preciso até nada mudar. Ele está correto, e o custo dele acompanha o tamanho da pasta de trabalho em vez do tamanho da edição

O Excel evita os dois com a calculation chain dele, uma ordenação de células com fórmula que permite a uma edição marcar um pequeno conjunto de células e percorrer só a cauda afetada. O motor XLSX do HotXLS usa a mesma ideia na forma de um grafo de dependência explícito por trás do TXLSXWorkbook.Recalculate. A primeira chamada monta o grafo a partir de toda fórmula em toda planilha e avalia todas. Cada chamada posterior avalia só as fórmulas a jusante das células cujos valores mudaram desde a passada anterior, mais uma lista curta de fórmulas que precisam rodar toda vez

Como o Recalculate transforma uma edição em uma passada ordenada

Cada célula com fórmula vira um nó, e uma aresta liga cada célula com fórmula que uma fórmula lê à fórmula que a lê. Uma referência como SUM(Model!B2:B500) produz uma aresta de cada célula com fórmula dentro daquele retângulo, e uma referência 3D através de uma sequência de planilhas é confrontada com cada planilha da sequência. Constantes puras não são nós; elas só importam como o lugar onde uma edição começa

Escrever Value em uma célula quase não faz nada no momento da escrita. A pasta de trabalho enfileira o endereço da célula, e é isso. Quando o Recalculate começa, toda fórmula cujas referências cobrem um endereço enfileirado é marcada como suja, e a marca flui pelas arestas para todos os dependentes dessa fórmula, depois para os deles. Uma pasta de trabalho em que o Recalculate nunca foi chamado não mantém grafo, e as escritas nela custam uma única checagem de nil, então código que só lê e escreve valores não paga nada pelo recurso

A parte suja do grafo é então ordenada com o algoritmo de Kahn. O detalhe que torna a passada barata é como os in-degrees são contados: só precedentes sujos contam. Um precedente limpo já tem um valor em cache válido, então uma fórmula nunca espera por ele. Durante a passada, uma fórmula que referencia outra célula com fórmula lê o valor em cache dessa célula em vez de avaliá-la de novo; a ordenação garante que o cache já está fresco. Toda fórmula suja é avaliada exatamente uma vez

Fluxograma de uma passada de Workbook.Recalculate do HotXLS em Delphi, da coleta de dirty flags e ordenação topológica à avaliação e reporte de ciclos
Cada passada coleta o conjunto sujo, o ordena topologicamente e avalia cada célula afetada uma vez; com o cálculo iterativo desligado, um ciclo de referência volta como lxErrorRef em vez de ficar em loop

O código abaixo monta a cadeia do parágrafo de abertura e edita a única entrada dela. Índices de planilha no HotXLS começam em 1, então Inputs é a planilha 1 e Model é a planilha 2; linhas e colunas em Cells são baseadas em 1 também

uses
  SysUtils, lxCalc, lxHandleX;   // lxOk é declarado em lxCalc

var
  Book: TXLSXWorkbook;
  Inputs, Model: TXLSXWorksheet;
  Row: Integer;
  Last: Variant;
begin
  Book := TXLSXWorkbook.Create;
  try
    Inputs := Book.Sheets.Add('Inputs');   // Sheets[1]
    Model  := Book.Sheets.Add('Model');    // Sheets[2]

    Inputs.Cells[2, 2].Value := 0.05;      // premissa de crescimento em Inputs!B2
    Model.Cells[2, 2].Formula := 'Inputs!B2*1000';
    for Row := 3 to 10000 do
      Model.Cells[Row, 2].Formula :=
        'B' + IntToStr(Row - 1) + '*(1+Inputs!$B$2)';

    if Book.Recalculate <> lxOk then      // monta o grafo, avalia 9.999 fórmulas
      Exit;

    Inputs.Cells[2, 2].Value := 0.07;      // enfileira um endereço
    if Book.Recalculate = lxOk then        // fórmulas alimentadas por Inputs!B2 rodam de novo (aqui: todas as 9.999)
      Last := Model.Cells[10000, 2].Value; // resultado em cache, lido como qualquer valor

    // Leitura pontual: RecalcCell roda o Recalculate e devolve a célula
    // (planilha 2 = Model, linha 10000, coluna 2)
    Book.RecalcCell(2, 10000, 2, Last);
  finally
    Book.Free;
  end;
end;

Nesse modelo em particular toda fórmula lê Inputs!$B$2, então a segunda passada ainda toca todas as 9.999; o grafo não consegue encolher uma edição que genuinamente afeta tudo. A economia aparece em modelos com muitas entradas, em que um loop de cenários escreve três ou quatro células e o resto da pasta de trabalho mantém os valores em cache

Exemplo de grafo de dependências mostrando uma célula de entrada editada no HotXLS marcando apenas suas fórmulas de modelo Delphi a jusante como sujas para reavaliação
Editar Inputs!B2 semeia um nó sujo; a sujeira flui pelas arestas de precedente para dependente, e apenas esse subgrafo roda de novo

Escritas de valor são baratas, escritas de fórmula reconstruem o grafo

Só edições de valor permanecem incrementais. Qualquer coisa que possa mudar o conjunto de nós ou arestas marca o grafo inteiro como desatualizado, e o próximo Recalculate o reconstrói do zero e avalia toda fórmula. Isso cobre atribuir Formula, limpar uma célula com fórmula escrevendo Null ou um valor vazio nela, adicionar, mover ou excluir planilhas, inserir ou excluir linhas e colunas, editar nomes definidos e mudar tabelas de dados what-if. Registrar ou descadastrar uma função de usuário, ou atribuir OnUserFunction, mantém o grafo mas marca todo nó como sujo

A consequência prática para geradores de relatórios é um formato de loop. Disponha as fórmulas uma vez, chame Recalculate, e daí em diante toque só em valores. Um loop que reescreve um template de fórmula por cliente e depois recalcula leva uma reconstrução completa a cada iteração, que é exatamente o custo que o grafo existe para evitar. Quando uma mudança de template for mesmo necessária, agrupe todas as edições de fórmula antes de um Recalculate, para que a reconstrução aconteça uma vez. O loop de cenários em si, então, só escreve valores

// Modelo montado uma vez; Recalculate já chamado
for I := 0 to High(Scenarios) do
begin
  Inputs.Cells[2, 2].Value := Scenarios[I].Growth;    // só escritas de valor
  Inputs.Cells[3, 2].Value := Scenarios[I].Discount;
  if Book.RecalcNeeded then          // True quando há células enfileiradas ou sujas
    if Book.Recalculate <> lxOk then
      Break;
  Results[I] := Model.Cells[10000, 2].Value;
end;

// Deixe o SaveAs recalcular só se algo ainda estiver pendente
Book.RecalcBeforeSave := xrmIfDirty;
Book.SaveAs('scenarios.xlsx');

RecalcBeforeSave tem default xrmManual, que nunca recalcula durante um salvamento. xrmIfDirty é a configuração para arquivos gerados: recalcula só quando o grafo está desatualizado ou tem fórmulas sujas, então uma pasta de trabalho já recalculada no loop é gravada como está, e uma com uma edição esquecida não é salva com valores em cache velhos

Quais funções do Excel forçam recálculo a cada passada?

Fórmulas que chamam NOW, TODAY, RAND, RANDBETWEEN, RANDARRAY, OFFSET, INDIRECT, CELL, INFO ou GETPIVOTDATA são voláteis no HotXLS: elas são marcadas como sujas no começo de cada passada, tenha ou não mudado algo upstream, e todos os dependentes delas também. As funções de relógio e aleatórias são voláteis pelo motivo óbvio. OFFSET e INDIRECT são voláteis porque as células que leem são computadas a partir dos valores de argumento em run time, e nenhuma caminhada estática pela fórmula consegue dizer quais arestas desenhar

A mesma regra vale para referências que o construtor do grafo não consegue fixar em um retângulo concreto, para um nome definido que se refere a si mesmo através de outros nomes, para referências de spill de dynamic array e para tabelas de dados what-if. Nomes definidos em geral não são problema: o construtor expande um nome para os intervalos a que ele se refere, então fórmulas que passam por nomes com escopo de pasta de trabalho ou de planilha ganham arestas reais. O artigo sobre nomes definidos e fórmulas entre planilhas cobre como esses nomes se resolvem

A direção conservadora é a certa aqui. Uma avaliação desnecessária custa um pouco de tempo; uma aresta faltando significa um número velho em um relatório com cara de pronto. Mantenha OFFSET e INDIRECT fora dos hot paths de modelos grandes e confine-os às poucas células que realmente precisam de endereços computados, porque uma célula volátil perto do topo de uma cadeia torna a cadeia inteira volátil na prática

Funções fornecidas por OnUserFunction ou RegisterUserFunction ficam no outro extremo. Elas não são voláteis, então um handler é chamado de novo só quando as células que alimentam os argumentos dele mudam, como qualquer função built-in. Um handler que lê uma taxa de câmbio de um banco de dados, portanto, segue devolvendo a última resposta dele entre passadas. Escreva a taxa em uma célula de entrada e passe essa célula como argumento; o grafo então enxerga a dependência, e a fórmula roda de novo precisamente quando a taxa muda. O artigo sobre funções personalizadas no motor de fórmulas do HotXLS explica como os argumentos do handler chegam

Referências circulares: lxErrorRef ou cálculo iterativo

Com o default Iterate = False, TXLSXWorkbook.Recalculate devolve lxErrorRef quando a ordenação encontra um ciclo. Os membros do ciclo são os nós sujos que o algoritmo de Kahn nunca consegue liberar, e qualquer fórmula suja a jusante deles fica bloqueada pelo mesmo motivo. Essas fórmulas mantêm os valores em cache anteriores, enquanto todo o resto do conjunto sujo é avaliado em ordem. Uma passada que falha também marca o grafo como desatualizado, então a chamada seguinte o reconstrói e avalia toda fórmula mais uma vez

case Book.Recalculate of
  lxOk:
    Book.SaveAs(OutputFile);
  lxErrorRef:
    // o ciclo e as fórmulas a jusante dele ainda guardam os caches antigos;
    // LogWarning é rotina sua
    LogWarning('Circular reference in model, report not written');
else
  LogWarning('Recalculation did not complete');
end;

Definir Iterate := True liga o mesmo cálculo iterativo que o Excel oferece. O motor avalia o ciclo e os dependentes bloqueados dele repetidamente, até IterateCount passadas (default 100), até que nenhum valor se mova em IterateDelta (default 0.001) ou mais. Membros de ciclo vazios começam em 0. Chegar ao limite de passadas mantém os últimos resultados e ainda devolve lxOk, que é por que a iteração deveria ficar desligada para relatórios gerados: ciclos em modelos de relatório reais são quase sempre erros, como uma linha de total presa dentro do próprio intervalo de SUM dela, e um código de erro definitivo é o que você quer deles. Ligue só para modelos construídos em torno de um loop intencional, como juros cobrados sobre um saldo médio que inclui os juros

Um caso que parece circular não é reportado como ciclo. O argumento lookup-array de VLOOKUP, HLOOKUP, LOOKUP, MATCH, XLOOKUP e XMATCH ainda torna a fórmula suja quando uma célula daquele intervalo muda, mas não conta para a detecção de ciclo, então VLOOKUP(A1,B:B,1) escrita na coluna B avalia normalmente. Para ciclos reais, o rastreador de avaliação de fórmulas mostra a cadeia de referências que se dobra sobre si mesma, um passo por vez

Fórmulas de matriz e passadas multithread

Uma fórmula de array CSE legada é um nó para o retângulo ancorado inteiro. A fórmula raiz é avaliada uma vez por passada, a matriz dela é escrita em cada célula membro (um resultado escalar é transmitido pelo retângulo), e uma fórmula que referencia qualquer célula do retângulo, não só a âncora superior esquerda, ganha uma aresta da raiz. Fórmulas de dynamic array são tratadas com mais cautela: elas rodam a cada passada, e quando um spill muda de tamanho, o Recalculate reconstrói o grafo e repete a passada até que as formas dos spills se assentem. Se nunca se assentarem em 32 rodadas, a chamada devolve lxErrorCalc

RecalcThreads acima de 1 avalia cada nível topológico de fórmulas independentes em worker threads, com uma calculadora separada por thread. Tabelas de dados ainda rodam serialmente, e uma pasta de trabalho que contém dynamic arrays fica no caminho serial. Qualquer handler de função de usuário precisa ser thread-safe antes de você subir esse valor; para um handler que toca estado compartilhado, manter RecalcThreads no default de 0 é a escolha mais segura

O grafo de dependência, o tratamento de voláteis e o cálculo iterativo fazem parte do motor XLSX do HotXLS Delphi Excel Component, que também cobre a calculadora de fórmulas, nomes definidos e o pipeline de leitura e escrita ao redor deles. Para aplicações Delphi e C++Builder que mantêm planilhas de preços ou pastas de trabalho de consolidação abertas e as alimentam com novas entradas o dia todo, o trabalho por edição passa a seguir as células que a edição realmente alcança