Fórmulas Array no HotXLS: por que o Excel insere @ e #VALUE!

O Excel 365 insere @ numa fórmula como =SUM(A1:B1*{10,100}) e mostra #VALUE! quando o arquivo a grava como fórmula comum, porque o Excel então aplica interseção implícita legada a cada operando de operador. Desde a v2.384.68, o HotXLS Delphi Component grava essas fórmulas com operadores de array do jeito que o Excel 365 faz: como fórmulas de matriz dinâmica de célula única em XLSX e como fórmulas de matriz de uma célula em XLS

O sintoma sobrevive à revisão de código. Seu serviço em Delphi grava um workbook, o HotXLS recalcula e guarda 210 em cache para =SUM(A1:B1*{10,100}), e o cliente abre o arquivo no Excel 16 para encontrar =SUM(@A1:B1*@{10,100}) na barra de fórmulas e #VALUE! na célula. Nada no arquivo está malformado. O que falta é o metadado que diz ao Excel que a fórmula foi escrita sob as regras de matriz dinâmica, e sem ele o Excel volta ao modelo de avaliação anterior às matrizes dinâmicas

Por que o Excel 365 insere @ numa fórmula que o HotXLS calculou corretamente?

O Excel 365 insere @ porque uma fórmula sem marcação de matriz dinâmica é, por definição, uma fórmula legada, e fórmulas legadas reduzem um intervalo multicélula a uma célula sempre que um operador espera um valor único. Essa redução é a interseção implícita: o Excel pega a célula do intervalo que compartilha a linha da fórmula (para um intervalo vertical) ou a coluna (para um intervalo horizontal), e se essa célula não existe o resultado é #VALUE!. O Excel 365 mantém esse significado para fórmulas no estilo antigo e exibe @ para tornar a redução visível

Coloque =SUM(A1:B1*{10,100}) em E5 e a leitura legada fica óbvia. A1:B1 é um intervalo horizontal, a fórmula está na coluna E, o intervalo não tem célula na coluna E, então @A1:B1 é #VALUE! e o SUM inteiro herda isso. Sob as regras de matriz dinâmica, o mesmo texto multiplica elemento a elemento, 1 × 10 + 2 × 100, e devolve 210. O motor de fórmulas do HotXLS já avaliava do jeito de matriz dinâmica desde os releases v2.384.61 e v2.384.63; o formato de arquivo é que não dizia isso. Com A1:B2 contendo 1, 2, 3 e 4, estas são as fórmulas de sonda e o que o Excel 16 exibe:

Fórmula	Resultado no HotXLS	Excel 16, gravada como fórmula comum	Gravada desde a v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Matriz dinâmica, o Excel mostra 210
=SUM((A1:B2>2)*1)	2	Interseção implícita, errado ou erro	Matriz dinâmica, o Excel mostra 2
=SUMPRODUCT((A1:B2>2)*1)	2	Interseção implícita, errado ou erro	Matriz dinâmica, o Excel mostra 2
=MAX(A1:B2-1)	3	Interseção implícita, errado ou erro	Matriz dinâmica, o Excel mostra 3
=SUM(A1:B2)	10	10	Fórmula comum, inalterada

A última linha importa tanto quanto as quatro primeiras. SUM(A1:B2) passa um intervalo direto a um parâmetro de função que aceita referências, então nenhum operador vê um intervalo multicélula e nenhuma interseção pode acontecer. O próprio Excel 365 salva essa fórmula como fórmula comum, e o HotXLS faz o mesmo

Como o HotXLS grava fórmulas com operadores de array em XLSX e XLS

O HotXLS grava uma fórmula com operador de array em XLSX como uma matriz dinâmica de célula única: o elemento <c> carrega cm="1", a fórmula é <f t="array" ref="E5">, e o pacote ganha um xl/metadata.xml com um tipo de metadado XLDAPR cuja extensão contém dynamicArrayProperties fDynamic="1". O atributo cm é um índice de base um no bloco cellMetadata dessa part, e o registro XLDAPR por trás dele é o que diz ao Excel "avalie isso sob as regras de matriz dinâmica". É a mesma estrutura que o Excel 16 grava quando você digita a mesma fórmula e salva, e foi assim que o layout alvo foi estabelecido em primeiro lugar

Em XLS não há part de metadados, então o HotXLS usa o único constructo que o BIFF8 tem para avaliação de array: uma fórmula de array de uma célula. A célula recebe um registro FORMULA cujo token stream é um único PtgExp apontando para ela mesma, seguido de um registro ARRAY ($0221) carregando a fórmula real parseada sobre o intervalo de uma célula. O Excel 365 grava fórmulas de matriz dinâmica em XLS do mesmo jeito, e uma versão mais antiga do Excel lendo o arquivo vê uma clássica fórmula de array de Ctrl+Shift+Enter

Nenhuma API nova entra em cena. A marcação acontece quando você atribui a fórmula pela API normal de célula, nos dois motores. Do lado XLSX é o TXLSXCell.Formula:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 1;
    Sheet.Cells[1, 2].Value := 2;
    Sheet.Cells[2, 1].Value := 3;
    Sheet.Cells[2, 2].Value := 4;

    // Operador sobre um intervalo ou array inline: gravado como matriz dinâmica
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Intervalo passado direto a uma função: continua um <f> comum
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

    if Book.Recalculate = lxOk then
      Writeln(VarToStr(Sheet.Cells[5, 5].Value));   // 210

    // A célula raiz do array mantém o texto sem o '=' inicial
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

    Book.SaveAs('probe.xlsx');   // E5 e E6 recebem cm="1" + t="array"
  finally
    Book.Free;
  end;
end;

Depois da conversão, o TXLSXCell.Formula devolve o texto sem o =, a mesma forma que o TXLSXRange.SetDynamicArrayFormula grava, então código que compara strings de fórmula após a atribuição deve normalizar o = inicial

O motor clássico segue a mesma regra pelo IXLSRange.Formula numa célula única. Atribuir a fórmula a redireciona internamente para o caminho de array de uma célula, então o XLS salvo contém o par FORMULA mais ARRAY:

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['A1', 'A1'].Value := 1;
  Sh.Range['B1', 'B1'].Value := 2;
  Sh.Range['A2', 'A2'].Value := 3;
  Sh.Range['B2', 'B2'].Value := 4;

  Sh.Range['E5', 'E5'].Formula := '=SUM(A1:B1*{10,100})';  // registro ARRAY
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // registro ARRAY
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // FORMULA comum

  Writeln(VarToStr(Sh.Range['E5', 'E5'].Value));   // 210
  Writeln(VarToStr(Sh.Range['E6', 'E6'].Value));   // 3
  Wb.SaveAs('probe.xls');
end;

Se você está ancorando um resultado multicélula em vez de um agregado escalar, as APIs explícitas continuam sendo a ferramenta certa: SetArrayFormula para um retângulo pré-dimensionado, como descrito em fórmulas de spill de matriz dinâmica com o HotXLS, ou TXLSXRange.SetDynamicArrayFormula quando você quer a marcação de matriz dinâmica XLSX num intervalo que você mesmo dimensiona. O caminho automático deste artigo cobre só fórmulas digitadas numa célula

Quais fórmulas o HotXLS marca como matrizes dinâmicas?

O HotXLS marca uma fórmula só quando um operador tem uma subárvore de operando que produz um array. A checagem roda na árvore sintática compilada, e um operando produz array se for um intervalo multicélula, uma constante de array inline, ou outra expressão de operador que ela mesma tenha tal operando. Parênteses são transparentes. Os operadores que contam são os aritméticos (+ - * / ^), a concatenação (&), as seis comparações, mais e menos unários, e a porcentagem:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) e A1:B2-1 são marcadas, onde quer que apareçam na fórmula, inclusive dentro de SUMPRODUCT
• SUM(A1:B2) e SUMPRODUCT(A1:A2,{1;10}) não são marcadas, porque o intervalo e o array vão direto a um argumento de função e nenhum operador os toca
• A1*2 ou SUM(A1,B1)*2 não são marcadas: referências de célula única e resultados de função são escalares para essa checagem

Três fronteiras são deliberadas. Primeiro, a marcação acontece só quando a fórmula entra pela API, ou seja, TXLSXCell.Formula no motor XLSX e uma atribuição de Formula ou Value em célula única no motor clássico. Fórmulas carregadas de um arquivo são gravadas de volta exatamente como foram encontradas, porque uma fórmula legada de outro produtor pode depender da interseção implícita de propósito. Segundo, texto que não contém nem : nem { é pulado sem uma segunda compilação. Terceiro, uma fórmula que daria spill, como =A1:B1*2 sozinha, é marcada como matriz dinâmica de célula única ancorada onde você a colocou. O HotXLS não a espalha, e o Excel vai estender o resultado às células vizinhas na próxima recalculação

Essa regra de operando é irmã da regra de classe de argumento coberta em interseção implícita para defined names no HotXLS. Aquele artigo trata de parâmetros de função declarados como value class; este trata de operadores, que no modelo legado sempre exigem valores

O que mudou no motor de cálculo para os resultados baterem

A correção de gravação na v2.384.68 depende de o motor de fórmulas do HotXLS já devolver os valores do Excel 365, o que exigiu várias correções anteriores nos dois motores. A mais visível foi a do SUMPRODUCT: até a v2.384.61 ele aceitava só dois ou mais intervalos puros, então SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) e até o SUMPRODUCT(B1:B2) de argumento único devolviam #N/A. O HotXLS agora avalia argumentos de expressão elemento a elemento com as regras do Excel:

• todo argumento precisa ter exatamente a mesma forma, um escalar contando como 1 × 1, ou o resultado é #VALUE!
• um valor de erro dentro de qualquer argumento é devolvido como o resultado
• elementos de texto e lógicos contam como 0, então (B1:B2>0)*1 ou -- ainda é necessário para virar TRUE em 1
• argumentos que são todos intervalos puros mantêm o loop de streaming original, então intervalos grandes não são materializados como arrays

A família do SUM (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) usa o mesmo avaliador elemento a elemento quando um argumento é uma expressão de operador sobre um intervalo, então =SUM((B1:B2>0)*1) conta as duas linhas em vez de olhar só a primeira célula. A v2.384.62 fez o operador de interseção por espaço devolver o retângulo comum de duas referências, com #NULL! quando elas não se sobrepõem, então =SUM(A1:B2 B1:B2) é 6 em vez de 2 e o resultado pode alimentar parâmetros de referência como ROWS e INDEX. A v2.384.63 adicionou ao parser constantes de array inline como {1,2;3,4} (vírgulas separam colunas, ponto e vírgula separam linhas) e uniões de referências como (A1:B2,D4). Comparações elemento a elemento também dão a um elemento vazio o tipo do outro lado, FALSE contra um lógico, casando com a regra escalar da v2.384.53 descrita em cadeias de comparação e células vazias no HotXLS

var
  V: Variant;
begin
  // Book é o TXLSXWorkbook do primeiro exemplo;
  // a planilha ativa dele contém A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, argumento único
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, intervalo comum B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, sobreposição contada duas vezes
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, era -1 antes da v2.384.61
end;

O TXLSXWorkbook.Calculate avalia uma string de fórmula contra a planilha ativa sem gravá-la, um jeito rápido de checar o comportamento do motor. Uma cautela sobre o @ em si: o HotXLS historicamente aceitou @ entre duas referências como uma interseção binária, e agora avalia essa forma com semântica de interseção de verdade. No Excel 365, @ é um prefixo unário de interseção implícita. Não escreva @ no texto da fórmula esperando o significado do Excel; use um espaço para interseção e deixe as regras de gravação acima cuidarem da semântica de matriz dinâmica

Por que o Excel se recusou a abrir o arquivo ou calculou o valor errado?

Fazer o Excel aceitar a marcação de matriz dinâmica levou três correções que nenhum teste de round-trip consigo mesmo pegaria, porque o HotXLS lia a própria saída corretamente em todos os casos. Cada uma foi achada abrindo a saída do HotXLS no Excel 16 e trocando uma variável por vez:

1. O GUID da extensão precisa estar todo em minúsculas. O ext uri no xl/metadata.xml tem de ser exatamente {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Um template antigo do HotXLS o escrevia com maiúsculas e minúsculas misturadas, e o Excel 16 se recusava a abrir o pacote inteiro, não só a célula. Workbooks criados com TXLSXRange.SetDynamicArrayFormula antes da v2.384.68 tinham o mesmo problema
2. O texto da célula raiz do array não carrega = inicial. O writer XLSX emite o texto armazenado de uma raiz de array verbatim no <f>. Se a célula convertida mantivesse o seu =, o elemento leria <f t="array" ref="E5">=SUM(...)</f>, que o Excel também rejeita na abertura. O HotXLS o remove durante a conversão, e é por isso que o TXLSXCell.Formula lê de volta sem ele
3. Double(True) é -1 no Delphi. A conversão de Variant segue a convenção COM em que TRUE é todos os bits ligados, e VarIsNumeric(True) também devolve True. Antes da v2.384.61 isso fazia =TRUE*1 devolver -1 e deixava elementos lógicos de array serem classificados como números, então uma comparação como (B1:B2>0)=TRUE dava errado. O HotXLS agora testa varBoolean antes de tratar um Variant como número em aritmética escalar, aritmética de array e classificação de elementos de array, e TRUE conta como 1

Classes de operando BIFF8: os detalhes a nível de byte para implementadores de formato

No BIFF8, todo token de operando carrega a sua classe de operando no próprio byte do token, e o Excel confia nessa classe mais do que na estrutura da fórmula. A [MS-XLS] define a classe como um campo PtgDataType de dois bits nos bits 5 e 6 do token: 1 para referência, 2 para valor, 3 para array. Os cinco bits baixos nomeiam o token, então a mesma referência de área tem três grafias:

Token	Classe referência	Classe valor	Classe array
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

O HotXLS errou três desses em lugares diferentes, e cada um produziu um sintoma distinto no Excel enquanto lia de volta bem no HotXLS:

• Constantes de array em classe referência. O encoder escolhia a classe pelo contexto, e parâmetros de SUM ou ROWS são classe referência, então =SUM({1,2}) era gravado com PtgArray como $20. O Excel exibe a fórmula inteira como =#N/A. Uma constante de array nunca pode ser referência, então desde a v2.384.63 o HotXLS grava classe array $60 onde quer que o contexto peça referência
• Operandos em classe valor de PtgIsect e PtgUnion. Operadores binários tomavam operandos em classe valor, o que está certo para * mas errado para os operadores de referência. Com áreas $45 antes de PtgIsect ($0F), o Excel lia =SUM(A1:B2 B1:B2) como =SUM(@A1:B2 @B1:B2) e devolvia #VALUE!. Desde a v2.384.62 os operandos de PtgIsect e PtgUnion ($10) são gravados em classe referência, $25
• Operandos em classe valor dentro do registro ARRAY. O Excel aplica interseção implícita até dentro de uma fórmula de array quando um operando é classe valor. O HotXLS gravava $45 ali, então a fórmula de array de uma célula para =SUM(A1:B1*{10,100}) avaliava para 10 no Excel. Desde a v2.384.68, o token stream de um registro ARRAY promove toda referência em classe valor e constante de array à classe array, $65 e $60, que é o que o Excel grava

Um reader que ignora os bits de classe faz round-trip dos três felizmente, então se você mantém o seu próprio writer BIFF8, compare os bits de classe de cada token de operando contra um arquivo salvo pelo Excel da mesma fórmula, não só os números dos tokens

Referência rápida

• O Excel 365 mostra @ quando um operador numa fórmula comum, sem marcação, recebe um intervalo multicélula ou array inline
• O HotXLS v2.384.68 e posteriores grava essas fórmulas como matrizes dinâmicas de célula única XLSX (cm="1", t="array", metadado XLDAPR) e como fórmulas de array de uma célula XLS (FORMULA com PtgExp mais ARRAY $0221)
• Só operandos de operador contam; um intervalo passado direto a um argumento de função continua fórmula comum
• Só fórmulas inseridas pelo TXLSXCell.Formula ou pelo Formula / Value clássico de célula única são marcadas; fórmulas carregadas ficam intactas
• A célula raiz convertida lê de volta sem o = inicial
• O GUID ext uri de matriz dinâmica precisa estar em minúsculas ou o Excel rejeita o pacote
• No Delphi, Double(True) é -1; teste varBoolean antes da conversão numérica
• BIFF8: constantes de array nunca em classe referência, operandos de PtgIsect / PtgUnion em classe referência, operandos do registro ARRAY em classe array

O HotXLS lê, escreve e calcula workbooks XLS e XLSX nativamente do Delphi e do C++Builder, e grava fórmulas com operadores de array para que o Excel 365 as abra com os mesmos valores que o HotXLS calculou. Veja o componente de planilha HotXLS para Delphi para edições, documentação e download da versão trial