Interseção implícita de nomes definidos no HotXLS Delphi

Um nome definido que aponta para uma coluna inteira é lido pelo Excel como uma única célula quando aparece numa posição escalar: =Vertical+1 na linha 7 significa a célula da linha 7 de Vertical, e não a área inteira. O HotXLS Delphi Component aplica essa interseção implícita na v2.382.4 em dois níveis, na avaliação e na extração de dependências, porque um modelo de empréstimo com 4805 fórmulas mostrou que acertar o valor não basta. Quando o walker de dependências expande o nome para a área completa, uma fórmula a jusante que alimenta qualquer célula dessa área fecha um ciclo que não existe, e o TXLSXWorkbook.Recalculate recusa a pasta de trabalho inteira

O modelo em questão é uma pasta de trabalho padrão de amortização de empréstimo. Com todo valor em cache envenenado para 777 e uma rodada completa de Recalculate, as duas arquiteturas do motor retornaram 23, que é lxErrorRef, o código de referência circular. 3842 das 4805 fórmulas não bateram com a expectativa independente, B18 segurava #VALUE!, E18 continuava 777, e a contagem de pagamentos em J7 tinha lido os placeholders numa coluna de saldo inacabada. Três defeitos distintos se escondiam atrás de um único código de retorno, e este artigo percorre cada um deles com o código-fonte que o corrigiu

Por que uma referência escalar a um nome de coluna cria um ciclo falso?

Porque um grafo de dependências só conhece arestas, e uma aresta de uma fórmula para uma área de 480 linhas são 480 arestas, uma das quais aponta de volta por uma célula que depende da fórmula. Considere =IF(TRUE,Vertical+1,0) em B1 com Vertical definido como Inputs!$A$1:$A$2, e =B1+1 em A2. O Excel avalia B1 como A1+1 e A2 como B1+1, uma cadeia direta. Um walker que registra B1 como dependente de A1:A2 faz de A2 um precedente de B1, A2 já lista B1 como precedente, e a fila de Kahn que conduz a recalculação incremental no HotXLS nunca vê nenhum dos dois nós chegar a grau de entrada zero. Esse é o padrão de que modelos de empréstimo são feitos: cada linha de período referencia colunas nomeadas para o saldo, a taxa e a contagem de pagamentos, cada nome abrange o cronograma inteiro, e cada linha também escreve nessas colunas. Expanda os nomes e o grafo vira um componente fortemente conectado gigante. Avalie-os com interseção implícita e o grafo vira um conjunto de cadeias curtas, uma por linha, que é o que a ECMA-376 Part 1 §18.17.2 descreve para um operando de referência consumido onde um valor único é exigido

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Inputs');
    Book.DefinedNames.Add('Vertical', 'Inputs!$A$1:$A$2');
    Book.DefinedNames.Add('Alias', '=Vertical');
    Sheet.Cells[1, 1].Value := 1;
    // Posição escalar: Vertical colapsa para A1 porque a fórmula está na linha 1
    Sheet.Cells[1, 2].Formula := '=IF(TRUE,Vertical+1,0)';
    Sheet.Cells[2, 1].Formula := '=B1+1';
    // Um nome cuja definição é outro nome ainda intersecta, então isto é A2
    Sheet.Cells[2, 2].Formula := '=Alias';
    // Argumento de classe referência: a área inteira é somada, sem interseção
    Sheet.Cells[3, 2].Formula := '=SUM(Vertical)';
    // A linha 6 está fora de A1:A2, a interseção é vazia e o IFERROR a captura
    Sheet.Cells[6, 2].Formula := '=IFERROR(Vertical,42)';

    if Book.Recalculate = lxOk then
    begin
      // B1 = 2, A2 = 3, B2 = 3, B3 = 4, B6 = 42
      // Antes da v2.382.4 este ramo era inalcançável: B1 -> A2 -> B1 era um ciclo
    end;
  finally
    Book.Free;
  end;
end;

Como o HotXLS decide que um argumento é escalar?

O HotXLS lê a resposta da tabela de funções, e não do formato do argumento. Toda entrada em TXLSFormula.InitFuncHash é registrada por THashFunc.SetValue com uma string opcional de classe por argumento: 'IF' carrega '100', 'SUMIF' carrega '010', 'VLOOKUP' carrega '1011', e 'SUM' não carrega nenhuma, então todos os seus argumentos caem na classe 0, a de nível de função. O novo TXLSFormula.FunctionArgumentClass(APtg, AArgument) expõe esse byte por THashFuncEntry.ArgClass, e um resultado 1 significa classe de valor. São as mesmas três classes que a [MS-XLS] §2.2.2 atribui a tokens de operando, e o encoder já dependia delas: ao gravar uma referência ele calcula o ptg como $24 + $20 * aClass, o que dá PtgRef para a classe 0, PtgRefV para a classe 1 e PtgRefA para a classe 2. Um arquivo BIFF escrito pelo Excel guarda essa classe em todo token de referência, então um motor cuja tabela bate com a especificação consegue responder se um argumento é escalar sem olhar os dados. O argumento do meio de SUMIF é o critério, um valor; o primeiro e o terceiro são áreas, referências. SUMPRODUCT é registrada com classe 2, a de matriz, no nível de função, e é por isso que =SUMPRODUCT(Vertical,Vertical) continua multiplicando a área inteira

Três funções não consultam a própria entrada da tabela para nada além do primeiro argumento. IF (ptg 1), CHOOSE (ptg 100) e IFERROR (ptg 255) repassam o que quer que selecionem, então seus argumentos de ramo herdam a classe da posição que a função ocupa. Essa única regra é o que permite que =CHOOSE(1,Vertical,0) em G2 resolva para A2 enquanto =SUMIF(Vertical,">0",Vertical) logo ao lado continua somando as duas linhas, e é a regra que um cronograma de amortização mais exercita, porque suas células de período se apoiam no IF para testar se o empréstimo ainda está aberto

Levando a classe pela travessia de dependências

O extrator de dependências em lxCalc.pas é um Walk recursivo sobre a árvore de sintaxe compilada, e ele existe duas vezes, uma em TXLSCalculator.ExtractDependencies para o grafo por pasta de trabalho e outra em ExtractWorkspaceDependencies para o grafo entre pastas de trabalho. A v2.382.4 dá aos dois walkers dois parâmetros extras. AScalar começa como True na raiz de uma fórmula, é recalculado para cada filho de função a partir de FunctionArgumentClass, e passa inalterado para os argumentos de ramo de ptg 1, 100 e 255. ANameRoot só vira True quando o walker desce na definição compilada de um nome, e sobrevive apenas por nós SA_GROUP, os parênteses, de modo que um nome definido como =A1:A2+1 não é confundido com uma área simples. Quando as duas flags são True num nó SA_RANGE, AddResolvedRange estreita a área com o mesmo helper que o avaliador usa antes de registrar a dependência. O helper é curto o bastante para ser citado por inteiro

function IntersectNamedScalarRange(CurRow, CurCol: Integer;
  var Row1, Row2, Col1, Col2: Integer): Boolean;
begin
  Result := False;
  if (Row1 = Row2) and (Col1 = Col2) then Exit(True);   // já é uma célula
  if (Col1 = Col2) and (CurRow >= Row1) and (CurRow <= Row2) then
  begin
    Row1 := CurRow; Row2 := CurRow;                     // coluna única: pega esta linha
    Exit(True);
  end;
  if (Row1 = Row2) and (CurCol >= Col1) and (CurCol <= Col2) then
  begin
    Col1 := CurCol; Col2 := CurCol;                     // linha única: pega esta coluna
    Result := True;
  end;
end;

Tudo o que o helper rejeita — uma área bidimensional, uma referência entre várias planilhas ou uma fórmula cuja linha cai fora da coluna nomeada — produz #VALUE! no lado da avaliação e dependência nenhuma no lado do grafo, que é o que o Excel faz para uma interseção vazia. O lado da avaliação mora em TXLSCalculator.GetValueItemName: ele tira os invólucros SA_GROUP da definição compilada e, se a raiz for um SA_RANGE, chama GetRangeInfo, intersecta e busca a célula única por FGetValue em vez de avaliar a definição inteira. Referências externas seguem pelo caminho antigo, porque não existe linha local contra a qual intersectar. De onde vêm o armazenamento e o escopo de um nome está no artigo sobre nomes definidos e fórmulas entre planilhas; o ponto aqui é só o que o motor faz depois que o nome resolve

Por que o MATCH sobre uma coluna meio calculada leu 777?

Porque o argumento de matriz de busca do MATCH é uma referência de scan, e referências de scan foram deliberadamente excluídas da ordem de avaliação. O artigo sobre scan de lookup apresentou TXLSDepRange.LookupScan e fechou com uma seção intitulada o que você abre mão ao excluir arestas de scan da ordenação: uma fórmula de lookup pode rodar antes de toda célula do seu intervalo ter sido recalculada e ler valores velhos. Numa sessão interativa isso converge na passada seguinte. Num recálculo em lote de um modelo envenenado, não, e PaymentCount, definido como =MATCH(0.01,Balances,-1)+1, leu os placeholders 777 ainda parados na coluna de saldo e devolveu uma contagem de períodos que não podia estar certa

O TXLSDepGraph.TopoOrder agora trata arestas de scan como arestas suaves de ordenação. Ao lado do grau de entrada rígido ele mantém um array ScanInDeg, contando precedentes de scan sujos por nó e decrementando conforme esses precedentes são emitidos, usando as listas ScanPrecedents, ScanDependents e ScanPrecedentCount que a mudança anterior já armazenava. A cada iteração a fila de Kahn varre sua janela pronta em busca do primeiro nó cujo ScanInDeg é zero e o troca para a cabeça; se todo nó pronto ainda espera por um precedente de scan, a cabeça é retirada na ordem estável. Arestas de scan nunca entram no grau de entrada rígido, então um VLOOKUP autorreferente sobre a própria coluna continua legal, mas um lookup que poderia esperar por um precedente finalizável agora espera. A regressão que fixa isso, LookupScan_WaitsForDirtyFormulaValues, envenena três células de saldo para 777 e espera que PaymentCount volte como 3, depois zera a entrada e espera que =IFERROR(PaymentCount,99) veja o #N/A e retorne 99

De onde veio o truncamento de quatro casas decimais?

Da aritmética de Variant do Delphi, e só em posições aninhadas. Os operadores binários em TXLSCalculator.GetValueItem já copiavam um + ou - de primeiro nível para duas variáveis locais Double, então =B1-A1 estava certo. Dentro de =IF(TRUE,B1-A1,0) a mesma subtração rodava como Value := Value - SubValue sobre dois Variants e, quando um operando era um valor de célula Int64 e o outro um Double, o resultado que observamos era um Currency, um tipo de ponto fixo com quatro casas decimais, então 1066.1854641400994 menos 120 voltava truncado para quatro decimais. Num cronograma em que cada pagamento é composto a partir da linha anterior, esse erro atravessa centenas de períodos antes de chegar aos totais

// TXLSCalculator.GetValueItem, ramo de aritmética binária (lxCalc.pas)
if VarIsNull(Value) then Value := 0;
if VarIsNull(SubValue) then SubValue := 0;
// Aritmética Variant mista de Int64/Double pode promover para Currency.
// A aritmética de planilha precisa manter a precisão de ponto flutuante.
if VarIsNumeric(Value) then Value := Double(Value);
if VarIsNumeric(SubValue) then SubValue := Double(SubValue);

A guarda roda antes de SA_ADD, SA_SUB, SA_MUL e SA_DIV igualmente, e a regressão Arithmetic_MixedInt64AndDoubleKeepsPrecision guarda Int64(120) em A1 e 1066.1854641400994 em B1, depois confere a diferença e a soma aninhadas até 1E-10 e o produto e o quociente até 1E-8 e 1E-12. O HotXLS não afirma conhecer toda regra de promoção que a RTL aplica a tipos Variant mistos entre versões de compilador; ele afirma que aritmética de planilha é double IEEE, e agora torna os dois operandos double antes de o operador vê-los, o que elimina a dúvida

O que a correção garante, e o que não garante

Depois da v2.382.4 as duas arquiteturas do motor retornam lxOk para o modelo envenenado, todos os 4805 valores em cache batem com a expectativa independente linha a linha dentro de 1E-7, e valem as asserções de que os caches realmente foram envenenados, de que o hash da origem não mudou e de que toda fórmula continua presente. Nenhuma iteração foi habilitada e nenhum código de erro foi suprimido para chegar lá. Um ciclo de verdade por um nome — =B1 em A1 com B1 ainda lendo Vertical — continua retornando erro, e o teste NamedScalarRanges_IntersectWithoutFalseCycles termina afirmando exatamente isso

Vale dizer os limites sem rodeios. A interseção implícita se aplica só a um nome cuja definição compilada, depois de tirar os parênteses, é uma área de uma coluna ou de uma linha numa única planilha; um nome bidimensional numa posição escalar dá #VALUE!, como no Excel, e uma função que a tabela não conhece recebe classe 0 de FunctionArgumentClass, então seus argumentos de nome continuam sendo expandidos por inteiro. A ordenação suave é uma preferência, não uma garantia: um ciclo só de scan ainda é avaliado em ordem estável e lê o que estiver em cache, que é o comportamento que o artigo sobre scan de lookup aceitou de propósito. E o resultado do modelo inteiro é verificado contra um script de expectativa independente, não contra outro motor de planilha, porque o pacote de escritório usado como referência não terminou de recalcular o modelo original dentro de um orçamento de 60 segundos. O HotXLS é um componente de planilha nativo para Delphi e C++Builder que lê, recalcula e grava XLS, XLSX, ODS e CSV sem o Excel instalado; a interseção de nomes, a tabela de classes de argumento e a ordenação suave de scan valem para todos os formatos porque o motor de cálculo é compartilhado, e a cobertura atual de funções está listada na página do produto HotXLS Delphi Spreadsheet Component