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

Um nome definido que se refere a uma coluna inteira é lido pelo Excel como uma única célula quando aparece em uma posição escalar: =Vertical+1 na linha 7 significa "a célula da linha 7 de Vertical", não a área toda. O HotXLS Delphi Component aplica essa interseção implícita em dois níveis, durante a avaliação e durante a extração de dependências, porque acertar o valor não basta. Um percorredor de dependências que expande o nome para a área completa deixa qualquer fórmula a jusante que alimenta uma célula dessa área fechar um ciclo que não existe, e o TXLSXWorkbook.Recalculate teria então que recusar a pasta de trabalho inteira como circular

Uma pasta de trabalho de amortização de empréstimo com 4805 fórmulas é o caso de estresse que põe tudo isso em prática. Recalcular ela corretamente a partir de caches velhos depende de três mecanismos separados: interseção implícita para nomes, a ordem das varreduras de lookup, e o tipo numérico usado para aritmética aninhada. Erre qualquer um deles e o sintoma é lxErrorRef, o código de referência circular, ou totais que se afastam de um cálculo independente linha a linha. Este artigo percorre cada mecanismo com o código envolvido

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 aqui é 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 fica fora de A1:A2, a interseção é vazia e o IFERROR 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
      // Expandir Vertical para A1:A2 aqui transformaria B1 -> A2 -> B1 em um ciclo falso
    end;
  finally
    Book.Free;
  end;
end;

Como o HotXLS decide que um argumento é escalar?

O HotXLS lê a resposta da tabela de funções em vez da forma do argumento. Cada entrada no TXLSFormula.InitFuncHash é registrada via 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 argumentos dela caem na classe 0 de nível de função. O TXLSFormula.FunctionArgumentClass(APtg, AArgument) expõe esse byte via THashFuncEntry.ArgClass, e um resultado de 1 significa classe valor. Essas são as mesmas três classes que o [MS-XLS] §2.2.2 atribui a tokens de operando, e o codificador depende delas também: quando grava uma referência ele calcula o ptg como $24 + $20 * aClass, o que dá PtgRef para a classe 0, PtgRefV para a 1 e PtgRefA para a 2. Um arquivo BIFF escrito pelo Excel guarda essa classe em cada token de referência, então um motor cuja tabela bate com a spec consegue responder "este argumento é escalar" sem olhar os dados. O argumento do meio do SUMIF é o critério, um valor; o primeiro e o terceiro são áreas, referências. O SUMPRODUCT é registrado com classe 2 de nível de função, array, razão pela qual =SUMPRODUCT(Vertical,Vertical) ainda multiplica 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 no lxCalc.pas é um Walk recursivo sobre a árvore de sintaxe compilada, e ele existe duas vezes, uma no TXLSCalculator.ExtractDependencies para o grafo por pasta de trabalho e uma no ExtractWorkspaceDependencies para o grafo entre pastas de trabalho. Ambos os walkers carregam dois parâmetros extras. AScalar começa como True na raiz de uma fórmula, é recalculado para cada filho de função a partir do FunctionArgumentClass, e passa sem mudança pelos argumentos de ramo dos ptg 1, 100 e 255. ANameRoot vira True só quando o walker desce na definição compilada de um nome, e sobrevive apenas por nós SA_GROUP, os parênteses, então um nome definido como =A1:A2+1 não é confundido com uma área simples. Quando ambas as flags são True em um nó SA_RANGE, o AddResolvedRange estreita a área com o mesmo helper que o avaliador usa antes de registrar a dependência. O helper é curto o bastante para citar 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 multiformulário ou uma fórmula cuja linha fica 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 vive no TXLSCalculator.GetValueItemName: ele remove os embrulhos SA_GROUP da definição compilada, e se a raiz é um SA_RANGE ele chama o GetRangeInfo, faz a interseção e busca a única célula via FGetValue em vez de avaliar a definição inteira. Referências externas seguem o caminho regular, porque não há linha local contra a qual intersecionar. De onde vêm o armazenamento e o escopo de um nome em primeiro lugar está coberto em o artigo de 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 precisa esperar uma coluna meio calculada?

Porque o argumento lookup-array do MATCH é uma referência de varredura, e referências de varredura são deliberadamente mantidas fora da ordem dura de avaliação. O artigo sobre varredura de lookup apresenta o TXLSDepRange.LookupScan e explicita o que você abre mão ao excluir arestas de varredura da ordenação: uma fórmula de lookup pode rodar antes de toda célula do intervalo dela ter sido recalculada e ler valores velhos. Em uma sessão interativa isso converge na passada seguinte. Em um recálculo único em lote de um template cujos caches estão velhos não: o PaymentCount, definido como =MATCH(0.01,Balances,-1)+1, leria os valores velhos ainda sentados na coluna de saldos e retornaria uma contagem de períodos que não poderia estar certa

O TXLSDepGraph.TopoOrder portanto trata arestas de varredura como arestas de ordenação brandas. Além do in-degree duro, ele mantém um array ScanInDeg, contando precedentes de varredura sujos por nó e decrementando-o conforme esses precedentes são emitidos, usando as listas ScanPrecedents, ScanDependents e ScanPrecedentCount que o grafo já guarda. A cada iteração a fila de Kahn do Kahn varre a janela de prontos procurando o primeiro nó cujo ScanInDeg é zero e o troca para a cabeça; se todo nó pronto ainda espera um precedente de varredura, a cabeça sai na ordem estável dela. Arestas de varredura nunca entram no in-degree duro, então um VLOOKUP autorreferente sobre a própria coluna dele continua legal, mas um lookup que pode esperar por um precedente finalizable espera. Com três células de saldo guardando valores velhos, o PaymentCount volta como 3; mude a entrada para zero e o =IFERROR(PaymentCount,99) vê o #N/A e retorna 99

Por que a aritmética aninhada é forçada a Double

Porque a aritmética de Variant do Delphi tem uma armadilha que só aparece em posições aninhadas. Copiar um + ou - de nível superior para dois locais Double mantém =B1-A1 exato. Dentro de =IF(TRUE,B1-A1,0), a implementação óbvia roda a mesma subtração como Value := Value - SubValue sobre dois Variants, e quando um operando é um valor de célula Int64 e o outro um Double, o resultado que observamos foi um Currency, um tipo de ponto fixo com quatro casas decimais, então 1066.1854641400994 menos 120 volta truncado a quatro casas. Ao longo de um cronograma em que cada parcela é composta a partir da linha anterior, esse erro caminha por centenas de períodos antes de chegar aos totais, razão pela qual o TXLSCalculator.GetValueItem converte ambos os operandos antes de o operador rodar

// 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 proteção roda antes do SA_ADD, SA_SUB, SA_MUL e SA_DIV igualmente, então com Int64(120) em A1 e 1066.1854641400994 em B1 a diferença, a soma, o produto e o quociente aninhados mantêm precisão dupla. O HotXLS não alega conhecer toda regra de promoção que a RTL aplica a tipos Variant mistos entre versões de compilador; ele trata a aritmética de planilha como double IEEE e torna ambos os operandos double antes de o operador vê-los, o que elimina a pergunta

O que o motor garante para colunas nomeadas

Com os três mecanismos no lugar, ambas as arquiteturas de motor recalculam o template de amortização a partir de caches velhos até lxOk, e todos os 4805 valores batem com uma expectativa independente linha a linha dentro de 1E-7. Nenhuma iteração é habilitada e nenhum código de erro é suprimido para chegar lá. Um ciclo genuíno através de um nome, =B1 em A1 com B1 ainda lendo Vertical, ainda retorna erro, exatamente como deve

As regras seguem o Excel. A interseção implícita se aplica a um nome cuja definição compilada, após remover parênteses, é uma área de coluna única ou linha única em uma planilha; um nome bidimensional em posição escalar é #VALUE!, como no Excel, e uma função que a tabela não conhece recebe classe 0 do FunctionArgumentClass, então os argumentos de nome dela são expandidos por completo. A ordenação branda é uma preferência, não uma garantia: um ciclo apenas de varredura avalia em ordem estável e lê o que estiver em cache, que é o trade-off que o artigo de varredura de lookup aceita de propósito. O HotXLS é um componente de planilha Delphi e C++Builder nativo que lê, recalcula e grava XLS, XLSX, ODS e CSV sem Excel instalado; a interseção de nomes, a tabela de classes de argumento e a ordenação branda de varredura se aplicam a 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