Fórmulas de matriz do HotXLS: o @ e o #VALUE! no Excel

O Excel 365 insere @ numa fórmula como =SUM(A1:B1*{10,100}) e mostra #VALUE! quando o ficheiro a grava como uma fórmula comum, porque o Excel aplica então a interseção implícita à moda antiga a todos os operandos de operadores. Desde a v2.384.68, o HotXLS Delphi Component grava estas fórmulas com operadores de matriz da forma como o Excel 365 o 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. O seu serviço Delphi grava um livro, o HotXLS recalcula-o e põe 210 em cache para =SUM(A1:B1*{10,100}), e o cliente abre-o no Excel 16 para encontrar =SUM(@A1:B1*@{10,100}) na barra de fórmulas e #VALUE! na célula. Nada no ficheiro está malformado. O que falta são os metadados que dizem ao Excel que a fórmula foi escrita sob as regras de matriz dinâmica, e sem eles o Excel retrocede para o seu modelo de avaliação anterior às matrizes dinâmicas

Porque é que o Excel 365 acrescenta @ a uma fórmula que o HotXLS calculou corretamente?

O Excel 365 acrescenta @ porque uma fórmula sem marcação de matriz dinâmica é, por definição, uma fórmula legada, e as 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 toma a célula do intervalo que partilha a linha da fórmula (num intervalo vertical) ou a coluna (num intervalo horizontal), e se tal célula não existe o resultado é #VALUE!. O Excel 365 mantém esse significado para fórmulas à moda antiga e mostra @ para tornar a redução visível

Meta =SUM(A1:B1*{10,100}) em E5 e a leitura legada torna-se óbvia. A1:B1 é um intervalo horizontal, a fórmula senta-se na coluna E, o intervalo não tem célula nenhuma na coluna E, por isso @A1:B1 é #VALUE! e todo o SUM herda o erro. 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 avalia à maneira de matriz dinâmica desde as versões v2.384.61 e v2.384.63; o formato de ficheiro simplesmente não o dizia. Com A1:B2 a conter 1, 2, 3 e 4, estas são as fórmulas de teste e o que o Excel 16 mostra:

Fórmula	Resultado HotXLS	Excel 16, gravada como fórmula simples	Gravação 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 simples, inalterada

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

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

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

Em XLS não há parte de metadados nenhuma, por isso o HotXLS usa o único construto que o BIFF8 tem para avaliação de matrizes: uma fórmula de matriz de uma célula. A célula recebe um registo FORMULA cujo stream de tokens é um único PtgExp que aponta para ela própria, seguido de um registo ARRAY ($0221) que transporta a fórmula analisada real sobre o intervalo de uma célula. O Excel 365 escreve fórmulas de matriz dinâmica para XLS da mesma maneira, e uma versão mais antiga do Excel que leia o ficheiro vê uma clássica fórmula de matriz com Ctrl+Shift+Enter

Nenhuma API nova entra em cena. A marcação acontece quando atribui a fórmula através da API normal de células, em ambos os motores. Do lado XLSX isso é 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 matriz 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 diretamente a uma função: fica 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 raiz da matriz mantém o seu 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, TXLSXCell.Formula devolve o texto sem =, a mesma forma que TXLSXRange.SetDynamicArrayFormula grava, por isso código que compare strings de fórmulas após a atribuição deve normalizar o = inicial

O motor clássico segue a mesma regra através de IXLSRange.Formula numa única célula. Atribuir a fórmula redireciona-a internamente para o caminho de matriz de uma célula, por isso o XLS gravado 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})';  // registo ARRAY
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // registo ARRAY
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // FORMULA simples

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

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

Que fórmulas é que o HotXLS marca como matrizes dinâmicas?

O HotXLS marca uma fórmula apenas quando um operador tem uma subárvore de operandos que produz uma matriz. A verificação corre na árvore de sintaxe compilada, e um operando produz uma matriz se for um intervalo multicélula, uma constante de matriz inline, ou outra expressão de operador que tenha ela própria tal operando. Os parênteses são transparentes. Os operadores que contam são os aritméticos (+ - * / ^), a concatenação (&), as seis comparações, o mais unário e o menos unário, e a percentagem:

• 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, incluindo dentro de SUMPRODUCT
• SUM(A1:B2) e SUMPRODUCT(A1:A2,{1;10}) não são marcadas, porque o intervalo e a matriz vão diretamente para um argumento de função e nenhum operador as toca
• A1*2 ou SUM(A1,B1)*2 não são marcadas: referências de célula única e resultados de funções são escalares para esta verificação

Três fronteiras são deliberadas. Primeiro, a marcação acontece apenas quando uma fórmula é introduzida através da API, ou seja TXLSXCell.Formula no motor XLSX e uma atribuição Formula ou Value de célula única no motor clássico. Fórmulas carregadas de um ficheiro são escritas 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 { é saltado sem uma segunda compilação. Terceiro, uma fórmula que derramaria, como =A1:B1*2 sozinha, é marcada como matriz dinâmica de célula única ancorada onde a puser. O HotXLS não a derrama, e o Excel estenderá o resultado às células vizinhas da próxima vez que recalcular

Esta regra de operandos é a irmã da regra de classe de argumentos coberta em interseção implícita para nomes definidos no HotXLS. Aquele artigo trata de parâmetros de função declarados como classe de valor; este trata de operadores, que no modelo legado exigem sempre valores

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

A correção de gravação na v2.384.68 apoia-se no motor de fórmulas do HotXLS já devolver os valores do Excel 365, o que levou várias correções anteriores em ambos os motores. A mais visível foi o SUMPRODUCT: até à v2.384.61 aceitava apenas dois ou mais intervalos simples, por isso SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) e até o SUMPRODUCT de um único argumento SUMPRODUCT(B1:B2) devolviam #N/A. O HotXLS avalia agora argumentos de expressão elemento a elemento com as regras do Excel:

• todos os argumentos têm de 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 resultado
• elementos de texto e lógicos contam como 0, por isso ainda é preciso (B1:B2>0)*1 ou -- para tornar TRUE em 1
• argumentos que sejam todos intervalos simples mantêm o ciclo de streaming original, por isso intervalos grandes não são materializados como matrizes

A família 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, por isso =SUM((B1:B2>0)*1) conta ambas as linhas em vez de olhar apenas para 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 não se sobrepõem, por isso =SUM(A1:B2 B1:B2) é 6 e não 2, e o resultado pode alimentar parâmetros de referência como ROWS e INDEX. A v2.384.63 acrescentou ao parser constantes de matriz 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). As comparações elemento a elemento também dão a um elemento em branco o tipo do outro lado, FALSE contra um lógico, a condizer com a regra escalar da v2.384.53 descrita em cadeias de comparação e células em branco no HotXLS

var
  V: Variant;
begin
  // Book é o TXLSXWorkbook do primeiro exemplo;
  // a sua folha ativa contém A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, um único argumento
  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 folha ativa sem a gravar, uma forma rápida de verificar 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 verdadeira. 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 tratarem da semântica de matriz dinâmica

Porque é que o Excel recusou abrir o ficheiro 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 próprio apanharia, porque o HotXLS lia a sua própria saída corretamente em todos os casos. Cada uma foi encontrada abrindo a saída do HotXLS no Excel 16 e substituindo uma variável de cada vez:

1. O GUID da extensão tem de estar todo em minúsculas. O ext uri em xl/metadata.xml tem de ser exatamente {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Um template antigo do HotXLS escrevia-o com maiúsculas e minúsculas misturadas, e o Excel 16 recusou-se a abrir o pacote inteiro, não apenas a célula. Livros criados com TXLSXRange.SetDynamicArrayFormula antes da v2.384.68 tinham o mesmo problema
2. O texto da raiz da matriz não transporta um = inicial. O escritor XLSX emite o texto gravado de uma raiz de matriz verbatim para <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 ao abrir. O HotXLS remove-o durante a conversão, razão pela qual TXLSXCell.Formula lê de volta sem ele
3. Double(True) é -1 em Delphi. A conversão de Variant segue a convenção COM em que TRUE são 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 matriz serem classificados como números, por isso uma comparação como (B1:B2>0)=TRUE saía errada. O HotXLS agora testa varBoolean antes de tratar um Variant como número na aritmética escalar, na aritmética de matrizes e na classificação de elementos de matriz, e TRUE conta como 1

Classes de operandos BIFF8: os detalhes ao nível de byte para implementadores de formato

No BIFF8, cada token operando transporta 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. O [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 matriz. Os cinco bits baixos nomeiam o token, por isso a mesma referência de área tem três grafias:

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

O HotXLS errou três destas em sítios diferentes, e cada uma produziu um sintoma distinto no Excel enquanto o HotXLS lia de volta sem problemas:

• Constantes de matriz em classe de referência. O codificador escolhia a classe a partir do contexto, e os parâmetros de SUM ou ROWS são de classe de referência, por isso =SUM({1,2}) era escrito com PtgArray como $20. O Excel mostra a fórmula inteira como =#N/A. Uma constante de matriz nunca pode ser uma referência, por isso desde a v2.384.63 o HotXLS escreve classe de matriz $60 onde quer que o contexto peça uma referência
• Operandos de classe de valor de PtgIsect e PtgUnion. Os operadores binários tomavam operandos de classe de 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 escritos em classe de referência, $25
• Operandos de classe de valor dentro do registo ARRAY. O Excel aplica interseção implícita mesmo dentro de uma fórmula de matriz quando um operando é de classe de valor. O HotXLS escrevia $45 aí, por isso a fórmula de matriz de uma célula para =SUM(A1:B1*{10,100}) avaliava a 10 no Excel. Desde a v2.384.68, o stream de tokens de um registo ARRAY promove toda a referência de classe de valor e constante de matriz à classe de matriz, $65 e $60, que é o que o Excel escreve

Um leitor que ignore os bits de classe faz round-trip dos três felizes, por isso se mantém o seu próprio escritor BIFF8, compare os bits de classe de cada token operando com um ficheiro gravado pelo Excel da mesma fórmula, não apenas os números dos tokens

Referência rápida

• O Excel 365 mostra @ quando um operador numa fórmula simples, sem marcação, recebe um intervalo multicélula ou uma matriz inline
• O HotXLS v2.384.68 e posteriores grava tais fórmulas como matrizes dinâmicas XLSX de célula única (cm="1", t="array", metadados XLDAPR) e como fórmulas de matriz XLS de uma célula (FORMULA com PtgExp mais ARRAY $0221)
• Só operandos de operadores contam; um intervalo passado diretamente a um argumento de função fica uma fórmula simples
• Só fórmulas introduzidas através de TXLSXCell.Formula ou do 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 do ext uri de matriz dinâmica tem de estar em minúsculas ou o Excel rejeita o pacote
• Em Delphi, Double(True) é -1; teste varBoolean antes da conversão numérica
• BIFF8: constantes de matriz nunca em classe de referência, operandos de PtgIsect / PtgUnion em classe de referência, operandos do registo ARRAY em classe de matriz

O HotXLS lê, escreve e calcula livros XLS e XLSX nativamente a partir de Delphi e C++Builder, e grava fórmulas com operadores de matriz para que o Excel 365 as abra com os mesmos valores que o HotXLS calculou. Veja o componente de folhas de cálculo Delphi HotXLS para edições, documentação e uma transferência de avaliação