Pesquisas HotXLS e referências circulares falsas

Coloque =VLOOKUP(A1,B:B,1) numa célula da coluna B e o Excel calcula-a sem queixa. Dê o mesmo livro a um motor de recálculo por grafo de dependências e é provável que receba 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é à 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 de que um motor de folhas de cálculo precisa e um grafo dirigido simples não dispõe

O argumento de matriz de pesquisa da família de pesquisas, LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP e XMATCH, é agora marcado como referência de varrimento. Uma referência de varrimento continua a semear sujidade, pelo que editar uma célula dentro do intervalo recalcula a fórmula, mas nunca contribui para a deteção de ciclos nem para a ordenação de avaliação. Os ciclos reais continuam a ser encontrados; os falsos desapareceram

Porque é que o Excel permite que um intervalo de pesquisa contenha a fórmula?

Porque esse argumento não é consumido da forma como um operando aritmético é. A família de pesquisas varre o intervalo à procura de valores em cache e devolve uma correspondência; não exige que o intervalo tenha sido avaliado até ao fim primeiro. O Excel trata um intervalo de pesquisa sobreposto a si próprio como a leitura do que essas células atualmente contêm, que é a mesma semântica que aplica a qualquer livro não iterativo: células que não foram recalculadas nesta passagem contribuem com o seu último valor calculado

As referências de coluna inteira tornam isto o caso comum em vez de exótico. B:B é a forma idiomática de escrever "a tabela de pesquisa inteira" numa folha onde se acrescentam linhas, e qualquer fórmula que viva na coluna B fica então dentro do seu próprio intervalo de pesquisa. Modelos financeiros, folhas de reconciliação e livros de auditoria fazem isto constantemente, normalmente 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 passagem de componentes fortemente ligados para classificar ciclos. Essa maquinaria está descrita no artigo sobre recálculo incremental, e é precisamente por isso que o falso positivo apareceu

Extraia dependências de =VLOOKUP(A1,B:B,1) na célula B7 e o segundo argumento rende um intervalo que contém a própria B7. O grafo passa a ter um laço próprio. O grau de entrada desse nó nunca chega a zero, pelo que a passagem topológica nunca o pode agendar, e a passagem de componentes classifica-o como ciclo. O motor está a raciocinar corretamente sobre o grafo que lhe foi dado. O grafo é o modelo errado, porque codifica um tipo de aresta onde a folha de cálculo tem dois

Duas classes de arestas, um grafo

A alteração acrescenta uma flag ao registo de referência resolvida, TXLSDepRange.LookupScan, que o extrator de dependências define quando percorre o argumento de matriz de pesquisa de uma das seis funções. A jusante, as arestas originadas dessas referências são guardadas separadas das arestas ordinárias: o nó do grafo mantém listas ScanDependents e ScanPrecedents ao lado das suas listas normais de dependentes e precedentes

A separação é o que torna a semântica certa. As arestas de varrimento são percorridas pela propagação de sujidade, pelo que uma edição em qualquer sítio em B:B continua a marcar B7 como suja e B7 recalcula. As arestas de varrimento nunca são contadas no grau de entrada e nunca entram no construtor de componentes, pelo que não podem criar um impasse topológico nem ser classificadas como ciclo. Ambas as implementações de grafo na biblioteca, o grafo clássico por livro e o grafo de espaço de trabalho entre livros que transporta a análise de componentes, foram alteradas em conjunto; deixá-las divergir produziria um livro que recalcula de forma diferente dependendo de ser aberto sozinho ou como parte de um espaço de trabalho

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 pesquisa 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 folha
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

O que se abdica ao excluir arestas de varrimento da ordenação

Exatamente uma coisa, e vale a pena enunciá-la de forma clara em vez de a esconder. Como as arestas de varrimento não participam na ordem topológica, uma fórmula de pesquisa pode ser avaliada na mesma passagem antes de algumas células do seu intervalo de pesquisa terem sido recalculadas, e lerá então os seus valores anteriores. O resultado converge no recálculo seguinte

Isso é aceitável porque é o que o Excel faz. Para um livro sem cálculo iterativo ativado, a resposta do próprio Excel para um valor ainda não recalculado na passagem atual é o último valor calculado, pelo que um motor que reproduza este comportamento está a igualar a implementação de referência em vez de a aproximar. Se 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 no artigo sobre cálculo iterativo, e aplica-se a ciclos reais e não a sobreposições de varrimento

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

Acrescentar LookupScan a TXLSDepRange introduziu um risco que nada tem a ver com pesquisas e tudo a ver com Pascal. TXLSDepRange é um registo não gerido, pelo que uma variável local desse tipo não é inicializada a zero. Todos os sítios na base de código que constroem um à mão, incluindo os blocos de dependências de tabelas de dados e vários auxiliares de teste, tiveram por isso de ser atualizados para definir o novo campo explicitamente. Perder um e o byte que por acaso estiver na pilha decide se essa referência é tratada como aresta de varrimento, o que produz um bug de recálculo que aparece e desaparece com alterações de código não relacionadas

// Um novo campo Boolean num registo não gerido torna todos os
// sítios de construção manuais um bug latente. Dois idiomas seguros:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // pôr tudo a zero, e depois preencher
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // ou definir todos os campos, incluindo o novo, em todos os sítios
  R.LookupScan := False;
end;

A regra geral que isto rendeu: acrescentar um campo a um registo construído na pilha em mais de meia dúzia de sítios é uma alteração de risco mais alto do que parece, e o compilador não o vai ajudar a encontrar os sítios. Se o registo for alcançável a partir de um caminho quente, prefira um auxiliar que o inicialize por completo a confiar que todos os pontos de chamada serão atualizados

Distinguir um ciclo real de uma sobreposição de varrimento

Nada nesta alteração enfraquece a deteção de ciclos. =B7+1 em B7 continua a ser um ciclo, uma cadeia de três fórmulas que se fecha sobre si própria continua a ser um ciclo, e ambos continuam a ser reportados através do resultado de recálculo com os membros do ciclo a reter os seus valores em cache anteriores enquanto tudo fora do ciclo se mantém atual. O que mudou é apenas que o argumento de matriz de pesquisa já não fabrica ciclos que o Excel não vê

Se está a auditar um livro e quer saber que referências o motor realmente resolveu e em que ordem, o rastreador de avaliação é a ferramenta para isso; o artigo sobre o rastreador de avaliação de fórmulas cobre como ler a sua saída. O HotXLS é um componente de folhas de cálculo nativo Delphi e C++Builder que lê e escreve XLS, XLSX, ODS e CSV sem Excel instalado, e o motor de recálculo é o mesmo em todos os formatos; a cobertura atual de funções e do motor está listada na página de produto do HotXLS Delphi spreadsheet component