Fórmulas matriciales HotXLS: por qué Excel añade @ y #VALUE!

Excel 365 inserta @ en una fórmula como =SUM(A1:B1*{10,100}) y muestra #VALUE! cuando el archivo la guarda como fórmula ordinaria, porque entonces Excel aplica intersección implícita heredada a cada operando de operador. Desde v2.384.68, HotXLS Delphi Component guarda estas fórmulas con operadores de matriz igual que hace Excel 365: como fórmulas de matriz dinámica de una sola celda en XLSX y como fórmulas de matriz de una celda en XLS

El síntoma sobrevive a la revisión de código. Su servicio Delphi escribe un libro, HotXLS lo recalcula y guarda en caché 210 para =SUM(A1:B1*{10,100}), y el cliente lo abre en Excel 16 y se encuentra =SUM(@A1:B1*@{10,100}) en la barra de fórmulas y #VALUE! en la celda. Nada del archivo está malformado. Lo que falta son los metadatos que le dicen a Excel que la fórmula se escribió bajo las reglas de matriz dinámica, y sin ellos Excel retrocede a su modelo de evaluación anterior a las matrices dinámicas

¿Por qué Excel 365 añade @ a una fórmula que HotXLS calculó bien?

Excel 365 añade @ porque una fórmula sin marcado de matriz dinámica es, por definición, una fórmula heredada, y las fórmulas heredadas reducen un rango multicelda a una celda allí donde un operador espera un único valor. Esa reducción es la intersección implícita: Excel toma la celda del rango que comparte la fila de la fórmula (para un rango vertical) o la columna (para un rango horizontal), y si no existe tal celda el resultado es #VALUE!. Excel 365 conserva ese significado para las fórmulas al estilo antiguo y muestra @ para hacer visible la reducción

Ponga =SUM(A1:B1*{10,100}) en E5 y la lectura heredada se vuelve evidente. A1:B1 es un rango horizontal, la fórmula está en la columna E, el rango no tiene ninguna celda en la columna E, así que @A1:B1 vale #VALUE! y toda la SUM lo hereda. Bajo las reglas de matriz dinámica el mismo texto multiplica elemento a elemento, 1 × 10 + 2 × 100, y devuelve 210. El motor de fórmulas de HotXLS evalúa a la manera de matriz dinámica desde las versiones v2.384.61 y v2.384.63; el formato de archivo simplemente no lo decía. Con A1:B2 conteniendo 1, 2, 3 y 4, estas son las fórmulas de prueba y lo que muestra Excel 16:

Fórmula	Resultado en HotXLS	Excel 16, guardada como fórmula plana	Guardado desde v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Matriz dinámica, Excel muestra 210
=SUM((A1:B2>2)*1)	2	Intersección implícita, mal resultado o error	Matriz dinámica, Excel muestra 2
=SUMPRODUCT((A1:B2>2)*1)	2	Intersección implícita, mal resultado o error	Matriz dinámica, Excel muestra 2
=MAX(A1:B2-1)	3	Intersección implícita, mal resultado o error	Matriz dinámica, Excel muestra 3
=SUM(A1:B2)	10	10	Fórmula plana, sin cambios

La última fila importa tanto como las cuatro primeras. SUM(A1:B2) pasa un rango directamente a un parámetro de función que acepta referencias, así que ningún operador ve un rango multicelda y no puede ocurrir intersección alguna. El propio Excel 365 guarda esa fórmula como fórmula plana, y HotXLS hace lo mismo

Cómo guarda HotXLS las fórmulas con operadores de matriz en XLSX y XLS

HotXLS escribe una fórmula con operador de matriz en XLSX como matriz dinámica de una sola celda: el elemento <c> lleva cm="1", la fórmula es <f t="array" ref="E5">, y el paquete gana xl/metadata.xml con un tipo de metadato XLDAPR cuya extensión contiene dynamicArrayProperties fDynamic="1". El atributo cm es un índice basado en uno dentro del bloque cellMetadata de esa parte, y el registro XLDAPR que hay detrás es lo que le dice a Excel «evalúe esto con reglas de matriz dinámica». Es la misma estructura que escribe Excel 16 cuando usted teclea la misma fórmula y guarda, que es justamente cómo se estableció la disposición de destino en primer lugar

En XLS no hay parte de metadatos, así que HotXLS usa la única construcción que BIFF8 tiene para evaluación de matrices: una fórmula de matriz de una celda. La celda recibe un registro FORMULA cuyo token stream es un único PtgExp que apunta a sí misma, seguido de un registro ARRAY ($0221) que lleva la fórmula real parseada sobre el rango de una celda. Excel 365 escribe las fórmulas de matriz dinámica en XLS de la misma manera, y una versión antigua de Excel que lea el archivo ve una fórmula de matriz clásica de Ctrl+Shift+Enter

No interviene ninguna API nueva. El marcado ocurre cuando usted asigna la fórmula por la API normal de celdas, en ambos motores. Por el lado XLSX eso es 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 un rango o array en línea: se guarda como matriz dinámica
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Rango pasado directo a una función: sigue siendo un <f> ordinario
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // La celda raíz de la matriz conserva su texto sin el '=' inicial
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

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

Tras la conversión, TXLSXCell.Formula devuelve el texto sin =, la misma forma que guarda TXLSXRange.SetDynamicArrayFormula, así que el código que compare cadenas de fórmulas tras la asignación debería normalizar el = inicial

El motor clásico sigue la misma regla a través de IXLSRange.Formula sobre una sola celda. Al asignar la fórmula, internamente se redirige al camino de matriz de una celda, así que el XLS guardado contiene el par FORMULA más 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 plana

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

Si lo que ancla es un resultado multicelda en lugar de un agregado escalar, las API explícitas siguen siendo la herramienta correcta: SetArrayFormula para un rectángulo predimensionado, como se describe en fórmulas spill de matriz dinámica con HotXLS, o TXLSXRange.SetDynamicArrayFormula cuando quiera el marcado de matriz dinámica XLSX sobre un rango que usted mismo dimensa. El camino automático de este artículo cubre solo las fórmulas tecleadas en una celda

¿Qué fórmulas marca HotXLS como matrices dinámicas?

HotXLS marca una fórmula solo cuando un operador tiene un subárbol de operando que produce una matriz. La comprobación corre sobre el árbol de sintaxis compilado, y un operando produce una matriz si es un rango multicelda, una constante de matriz en línea u otra expresión de operador que a su vez tenga tal operando. Los paréntesis son transparentes. Los operadores que cuentan son los aritméticos (+ - * / ^), la concatenación (&), las seis comparaciones, el más y el menos unarios, y el porcentaje:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) y A1:B2-1 se marcan, aparezcan donde aparezcan en la fórmula, incluido dentro de SUMPRODUCT
• SUM(A1:B2) y SUMPRODUCT(A1:A2,{1;10}) no se marcan, porque el rango y la matriz entran directamente como argumento de función y ningún operador los toca
• A1*2 o SUM(A1,B1)*2 no se marcan: las referencias de una celda y los resultados de funciones son escalares para esta comprobación

Tres fronteras son deliberadas. Primera: el marcado ocurre solo cuando una fórmula se introduce por la API, es decir TXLSXCell.Formula en el motor XLSX y una asignación de Formula o Value a una sola celda en el motor clásico. Las fórmulas cargadas desde un archivo se escriben de vuelta exactamente como se encontraron, porque una fórmula heredada de otro productor puede depender de la intersección implícita a propósito. Segunda: el texto que no contiene ni : ni { se descarta sin una segunda compilación. Tercera: una fórmula que haría spill, como =A1:B1*2 por sí sola, se marca como matriz dinámica de una celda anclada donde usted la puso. HotXLS no la derrama, y Excel extenderá el resultado a las celdas vecinas la próxima vez que recalcule

Esta regla de operandos es hermana de la regla de clase de argumento tratada en intersección implícita para nombres definidos en HotXLS. Aquel artículo va de los parámetros de función declarados de clase valor; este va de los operadores, que en el modelo heredado siempre exigen valores

Qué cambió en el motor de cálculo para que los resultados casen

El arreglo de almacenamiento de v2.384.68 se apoya en que el motor de fórmulas de HotXLS ya devolvía los valores de Excel 365, lo cual costó varios arreglos previos en ambos motores. El más visible fue SUMPRODUCT: hasta v2.384.61 aceptaba solo dos o más rangos planos, así que SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) e incluso el SUMPRODUCT(B1:B2) de un solo argumento devolvían #N/A. HotXLS evalúa ahora los argumentos de expresión elemento a elemento con las reglas de Excel:

• cada argumento debe tener exactamente la misma forma, un escalar cuenta como 1 × 1, o el resultado es #VALUE!
• un valor de error dentro de cualquier argumento se devuelve como resultado
• los elementos de texto y lógicos cuentan como 0, así que sigue haciendo falta (B1:B2>0)*1 o -- para convertir TRUE en 1
• los argumentos que son todos rangos planos conservan el bucle de streaming original, así que los rangos grandes no se materializan como matrices

La familia SUM (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) usa el mismo evaluador elemento a elemento cuando un argumento es una expresión de operador sobre un rango, así que =SUM((B1:B2>0)*1) cuenta ambas filas en lugar de mirar solo la primera celda. v2.384.62 hizo que el operador de intersección por espacio devolviera el rectángulo común de dos referencias, con #NULL! cuando no se solapan, así que =SUM(A1:B2 B1:B2) es 6 en lugar de 2 y el resultado puede alimentar parámetros de referencia como ROWS e INDEX. v2.384.63 añadió al parser constantes de matriz en línea como {1,2;3,4} (las comas separan columnas, los puntos y comas filas) y uniones de referencias como (A1:B2,D4). Las comparaciones elemento a elemento también dan a un elemento vacío el tipo del otro lado, FALSE frente a un lógico, en línea con la regla escalar de v2.384.53 descrita en cadenas de comparación y celdas vacías en HotXLS

var
  V: Variant;
begin
  // Book es el TXLSXWorkbook del primer ejemplo;
  // su hoja activa contiene A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, un solo argumento
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, rango común B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, el solape cuenta dos veces
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, antes de v2.384.61 era -1
end;

TXLSXWorkbook.Calculate evalúa una cadena de fórmula contra la hoja activa sin guardarla, una manera rápida de comprobar el comportamiento del motor. Una precaución sobre el propio @: HotXLS históricamente ha aceptado @ entre dos referencias como intersección binaria, y ahora evalúa esa forma con semántica de intersección real. En Excel 365, @ es un prefijo unario de intersección implícita. No escriba @ en el texto de la fórmula esperando el significado de Excel; use un espacio para la intersección y deje que las reglas de almacenamiento de arriba manejen la semántica de matriz dinámica

¿Por qué Excel se negaba a abrir el archivo o calculaba un valor equivocado?

Conseguir que Excel aceptara el marcado de matriz dinámica tomó tres arreglos que ninguna prueba de ida y vuelta consigo misma habría detectado, porque HotXLS leía su propia salida correctamente en todos los casos. Cada uno se encontró abriendo la salida de HotXLS en Excel 16 y cambiando una variable cada vez:

1. El GUID de la extensión debe ir todo en minúsculas. El ext uri de xl/metadata.xml tiene que ser exactamente {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Una plantilla antigua de HotXLS lo escribía con mayúsculas y minúsculas mezcladas, y Excel 16 se negaba a abrir el paquete entero, no solo la celda. Los libros creados con TXLSXRange.SetDynamicArrayFormula antes de v2.384.68 tenían el mismo problema
2. El texto de la celda raíz de la matriz no lleva = inicial. El escritor XLSX emite el texto guardado de una celda raíz tal cual dentro de <f>. Si la celda convertida conservara su =, el elemento diría <f t="array" ref="E5">=SUM(...)</f>, que Excel también rechaza al abrir. HotXLS lo quita durante la conversión, que es por lo que TXLSXCell.Formula se lee de vuelta sin él
3. Double(True) es -1 en Delphi. La conversión de Variant sigue la convención COM donde TRUE son todos los bits a uno, y VarIsNumeric(True) también devuelve True. Antes de v2.384.61 eso hacía que =TRUE*1 devolviera -1 y que los elementos lógicos de una matriz se clasificaran como números, así que una comparación como (B1:B2>0)=TRUE salía mal. HotXLS comprueba ahora varBoolean antes de tratar un Variant como número en aritmética escalar, aritmética de matrices y clasificación de elementos de matriz, y TRUE cuenta como 1

Clases de operandos BIFF8: los detalles a nivel de byte para implementadores del formato

En BIFF8, cada token de operando lleva su clase de operando en el propio byte del token, y Excel confía en esa clase más que en la estructura de la fórmula. [MS-XLS] define la clase como un campo PtgDataType de dos bits en los bits 5 y 6 del token: 1 para referencia, 2 para valor, 3 para matriz. Los cinco bits bajos nombran el token, así que la misma referencia de área tiene tres escrituras:

Token	Clase referencia	Clase valor	Clase matriz
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS se equivocó con tres de estos en sitios distintos, y cada uno produjo un síntoma diferente en Excel mientras que en HotXLS se releía bien:

• Constantes de matriz de clase referencia. El encoder elegía la clase por el contexto, y los parámetros de SUM o ROWS son de clase referencia, así que =SUM({1,2}) se escribía con PtgArray como $20. Excel muestra la fórmula entera como =#N/A. Una constante de matriz nunca puede ser una referencia, así que desde v2.384.63 HotXLS escribe clase matriz $60 allí donde el contexto pida una referencia
• Operandos de clase valor de PtgIsect y PtgUnion. Los operadores binarios tomaban operandos de clase valor, lo cual es correcto para * pero erróneo para los operadores de referencia. Con áreas $45 delante de PtgIsect ($0F), Excel leía =SUM(A1:B2 B1:B2) como =SUM(@A1:B2 @B1:B2) y devolvía #VALUE!. Desde v2.384.62 los operandos de PtgIsect y PtgUnion ($10) se escriben en clase referencia, $25
• Operandos de clase valor dentro del registro ARRAY. Excel aplica intersección implícita incluso dentro de una fórmula de matriz cuando un operando es de clase valor. HotXLS escribía $45 ahí, así que la fórmula de matriz de una celda para =SUM(A1:B1*{10,100}) evaluaba 10 en Excel. Desde v2.384.68, el token stream de un registro ARRAY promueve cada referencia de clase valor y constante de matriz a clase matriz, $65 y $60, que es lo que escribe Excel

Un lector que ignore los bits de clase hace la ida y vuelta de los tres sin quejarse, así que si usted mantiene su propio escritor BIFF8, compare los bits de clase de cada token de operando contra un archivo guardado por Excel de la misma fórmula, no solo los números de token

Referencia rápida

• Excel 365 muestra @ cuando un operador de una fórmula plana sin marcar recibe un rango multicelda o un array en línea
• HotXLS v2.384.68 y posteriores guarda tales fórmulas como matrices dinámicas XLSX de una celda (cm="1", t="array", metadatos XLDAPR) y como fórmulas de matriz XLS de una celda (FORMULA con PtgExp más ARRAY $0221)
• Solo cuentan los operandos de operador; un rango pasado directo a un argumento de función sigue siendo una fórmula plana
• Solo se marcan las fórmulas introducidas por TXLSXCell.Formula o por la Formula / Value clásica de una celda; las fórmulas cargadas quedan intactas
• La celda raíz convertida se lee de vuelta sin el = inicial
• El GUID ext uri de matriz dinámica debe ir en minúsculas o Excel rechaza el paquete
• En Delphi, Double(True) es -1; compruebe varBoolean antes de la conversión numérica
• BIFF8: las constantes de matriz jamás en clase referencia, operandos de PtgIsect / PtgUnion en clase referencia, operandos del registro ARRAY en clase matriz

HotXLS lee, escribe y calcula libros XLS y XLSX de forma nativa desde Delphi y C++Builder, y guarda las fórmulas con operadores de matriz de modo que Excel 365 las abra con los mismos valores que calculó HotXLS. Vea el componente de hojas de cálculo Delphi HotXLS para ediciones, documentación y una descarga de prueba