Scans de lookup do HotXLS e referências circulares falsas

Coloque =VLOOKUP(A1,B:B,1) em uma célula na coluna B e o Excel calcula sem reclamar. Entregue a mesma pasta de trabalho a um motor de recálculo de grafo de dependências e você provavelmente receberá um erro de referência circular, porque a fórmula depende de um intervalo que contém a fórmula. O HotXLS reportava exatamente isso até a v2.361.98. A correção não é um caso especial para intervalos de coluna inteira; é uma distinção entre dois tipos de aresta de dependência que um motor de planilhas precisa e um grafo direcionado simples não tem

O argumento lookup-array da família de lookups, LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP e XMATCH, agora é marcado como uma referência de scan. Uma referência de scan ainda semeia sujeira (dirty), então editar uma célula dentro do intervalo recalcula a fórmula, mas nunca contribui para a detecção de ciclos nem para a ordenação de avaliação. Ciclos reais ainda são encontrados; os falsos sumiram

Por que o Excel permite que um intervalo de lookup contenha a fórmula?

Porque esse argumento não é consumido do jeito que um operando aritmético é. A família de lookups varre o intervalo por valores em cache e retorna uma correspondência; não exige que o intervalo tenha sido avaliado até o fim primeiro. O Excel trata um intervalo de lookup que se sobrepõe a si como lendo o que quer que essas células atualmente guardem, que é a mesma semântica que aplica a qualquer pasta de trabalho não iterativa: células que não foram recalculadas nesta passada contribuem com seu último valor calculado

Referências de coluna inteira tornam isso o caso comum em vez de exótico. B:B é o jeito idiomático de escrever "a tabela de lookup inteira" em uma aba em que linhas são anexadas, e qualquer fórmula que viva na coluna B então está dentro de seu próprio intervalo de lookup. Modelos financeiros, abas de reconciliação e pastas de auditoria fazem isso o tempo todo, geralmente sem ninguém notar que o intervalo se sobrepõe

O que um grafo de dependências faz com a mesma fórmula

O HotXLS recalcula incrementalmente, o que exige um grafo de dependências real: nós para células, arestas para referências, uma ordem topológica para avaliação e uma passada de componentes fortemente conectados para classificar ciclos. Essa maquinaria é descrita em o artigo de recálculo incremental, e é precisamente por isso que o falso positivo apareceu

Extraia as dependências de =VLOOKUP(A1,B:B,1) na célula B7 e o segundo argumento produz um intervalo contendo a própria B7. O grafo agora tem um self-loop. O in-degree desse nó nunca chega a zero, então a passada topológica nunca consegue agendá-lo, e a passada de componentes o classifica como um ciclo. O motor está raciocinando corretamente sobre o grafo que recebeu. O grafo é o modelo errado, porque codifica um tipo de aresta onde a planilha tem dois

Duas classes de aresta, um grafo

A mudança adiciona um flag ao record de referência resolvida, TXLSDepRange.LookupScan, que o extrator de dependências define ao percorrer o argumento lookup-array de uma das seis funções. A jusante, arestas originadas dessas referências são armazenadas aparte das arestas ordinárias: o nó do grafo mantém listas ScanDependents e ScanPrecedents ao lado de suas listas normais de dependentes e precedentes

A separação é o que torna a semântica certa. Arestas de scan são percorridas pela propagação de dirty, então uma edição em qualquer lugar de B:B ainda marca B7 como dirty e B7 recalcula. Arestas de scan nunca são contadas no in-degree e nunca entram no construtor de componentes, então não podem criar um deadlock topológico nem ser classificadas como ciclo. Ambas as implementações de grafo na biblioteca, o grafo clássico por pasta de trabalho e o grafo de workspace entre pastas que carrega a análise de componentes, foram mudadas juntas; deixá-las divergir produziria uma pasta de trabalho que recalcula de forma diferente dependendo de ter sido aberta sozinha ou como parte de um workspace

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Ledger');
    Sheet.Cells[1, 1].Value := 'ACC-4471';
    Sheet.Cells[1, 2].Value := 1200.00;
    // O intervalo de lookup cobre a coluna B, e esta fórmula vive nela
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // Antes da v2.361.98 este ramo era inalcançável para esta aba
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

O que você abre mão ao excluir arestas de scan da ordenação

Exatamente uma coisa, e vale enunciar de forma clara em vez de esconder. Como arestas de scan não participam da ordem topológica, uma fórmula de lookup pode ser avaliada na mesma passada antes de algumas células de seu intervalo de lookup terem sido recalculadas, e então lerá seus valores anteriores. O resultado converge no próximo recálculo

Isso é aceitável porque é o que o Excel faz. Para uma pasta de trabalho sem cálculo iterativo habilitado, a própria resposta do Excel para um valor ainda não recalculado na passada atual é o último valor calculado, então um motor que reproduz esse comportamento está igualando a implementação de referência em vez de aproximá-la. Se você precisa de uma resposta genuinamente convergida sobre um modelo autorreferente, o mecanismo para isso é o cálculo iterativo com um limite de iteração explícito, coberto em o artigo de cálculo iterativo, e se aplica a ciclos reais e não a sobreposições de scan

O risco de regressão escondido dentro da correção

Adicionar LookupScan a TXLSDepRange introduziu um risco que não tem nada a ver com lookups e tudo a ver com Pascal. TXLSDepRange é um record não gerenciado, então uma variável local desse tipo não é inicializada com zeros. Todo lugar na base de código que constrói um à mão, incluindo os blocos de dependência de data table e vários helpers de teste, portanto precisou ser atualizado para definir o novo campo explicitamente. Perder um e o byte que por acaso estava na pilha decide se aquela referência é tratada como aresta de scan, o que produz um bug de recálculo que aparece e desaparece com mudanças de código sem relação

// Um novo campo Boolean em um record não gerenciado torna todo ponto
// de construção manual um bug latente. Dois idiomas seguros:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // zere tudo, depois preencha
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // ou defina todo campo, incluindo o novo, em cada ponto
  R.LookupScan := False;
end;

A regra geral que isso rendeu: adicionar um campo a um record construído na pilha em mais do que um punhado de lugares é uma mudança de risco maior do que parece, e o compilador não vai ajudar a encontrar os pontos. Se o record é alcançável de um hot path, prefira um helper que o inicialize por completo a confiar que todo call site será atualizado

Distinguindo um ciclo real de uma sobreposição de scan

Nada nesta mudança enfraquece a detecção de ciclos. =B7+1 em B7 ainda é um ciclo, uma cadeia de três fórmulas que se fecha sobre si ainda é um ciclo, e ambos ainda são reportados pelo resultado de recálculo com os membros do ciclo retendo seus valores em cache anteriores enquanto tudo fora do ciclo continua atual. O que mudou é apenas que o argumento lookup-array não fabrica mais ciclos que o Excel não vê

Se você está auditando uma pasta de trabalho e quer saber quais referências o motor de fato resolveu e em que ordem, o tracer de avaliação é a ferramenta para isso; o artigo do tracer de avaliação de fórmulas cobre como ler sua saída. O HotXLS é um componente de planilhas Delphi e C++Builder nativo que lê e escreve XLS, XLSX, ODS e CSV sem Excel instalado, e o motor de recálculo é o mesmo em todo formato; a cobertura atual de funções e do motor está listada na página de produto do HotXLS Delphi spreadsheet component