Fórmulas de matriz HotXLS: por qué Excel agrega @ 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 sobre matriz igual que lo 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 en Delphi escribe un workbook, HotXLS lo recalcula y deja en caché 210 para =SUM(A1:B1*{10,100}), y el cliente lo abre en Excel 16 y se encuentra con =SUM(@A1:B1*@{10,100}) en la barra de fórmulas y #VALUE! en la celda. Nada del archivo está mal formado. 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 vuelve a su modelo de evaluación anterior a las matrices dinámicas

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

Excel 365 agrega @ porque una fórmula sin marca de matriz dinámica es, por definición, una fórmula heredada, y las fórmulas heredadas reducen un rango de varias celdas a una sola celda siempre que 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 que la reducción quede a la vista

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 es #VALUE! y toda la SUM lo hereda. Bajo las reglas de matriz dinámica el mismo texto multiplica elemento por elemento, 1 × 10 + 2 × 100, y devuelve 210. El motor de fórmulas de HotXLS ya evaluaba 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 de 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 primeras cuatro. 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 de varias celdas y no puede ocurrir ninguna intersección. El propio Excel 365 guarda esa fórmula como fórmula plana, y HotXLS hace lo mismo

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

HotXLS escribe una fórmula con operador sobre matriz en XLSX como una 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 un xl/metadata.xml con un tipo de metadato XLDAPR cuya extensión contiene dynamicArrayProperties fDynamic="1". El atributo cm es un índice basado en 1 dentro del bloque cellMetadata de esa parte, y el registro XLDAPR detrás de él es lo que le dice a Excel "evalúe esto bajo 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ó el layout de destino en primer lugar

En XLS no hay parte de metadatos, así que HotXLS usa el único constructo que BIFF8 tiene para evaluación de matriz: 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 clásica fórmula de matriz de Ctrl+Shift+Enter

No interviene ninguna API nueva. El marcado ocurre cuando usted asigna la fórmula por la API de celda normal, en ambos engines. Del lado XLSX se trata de 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 matriz inline: 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: queda como 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 raíz de 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;

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

El engine clásico sigue la misma regla a través de IXLSRange.Formula sobre una sola celda. Asignar la fórmula la redirige internamente a la ruta 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 suyo es anclar un resultado de varias celdas en vez de un agregado escalar, las APIs explícitas siguen siendo la herramienta correcta: SetArrayFormula para un rectángulo de tamaño predefinido, como se describe en fórmulas spill de matriz dinámica con HotXLS, o TXLSXRange.SetDynamicArrayFormula cuando quiere la marca de matriz dinámica de XLSX sobre un rango que dimensiona usted mismo. La ruta automática de este artículo solo cubre 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. El chequeo corre sobre el árbol de sintaxis compilado, y un operando produce una matriz si es un rango de varias celdas, una constante de matriz inline, u otra expresión de operador que a su vez tenga un operando así. 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 directo en un 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 este chequeo

Tres fronteras son deliberadas. Primera: el marcado ocurre solo cuando la fórmula se introduce por la API, es decir TXLSXCell.Formula en el engine XLSX y una asignación de Formula o Value de una sola celda en el engine 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 salta sin una segunda compilación. Tercera: una fórmula que haría spill, como =A1:B1*2 sola, se marca como matriz dinámica de una celda anclada donde usted la puso. HotXLS no la expande, 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 cubierta en intersección implícita para nombres definidos en HotXLS. Ese artículo va sobre parámetros de función declarados de clase value; este va sobre operadores, que en el modelo heredado siempre exigen valores

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

La corrección 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 tomó varias correcciones previas en ambos engines. La más visible fue SUMPRODUCT: hasta v2.384.61 solo aceptaba dos o más rangos planos, así que SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) y hasta el SUMPRODUCT(B1:B2) de un solo argumento devolvían #N/A. HotXLS ahora evalúa los argumentos expresión elemento por 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 todavía hace falta (B1:B2>0)*1 o -- para volver TRUE un 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 por elemento cuando un argumento es una expresión de operador sobre un rango, así que =SUM((B1:B2>0)*1) cuenta ambas filas en vez 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 vez de 2 y el resultado puede alimentar parámetros de referencia como ROWS e INDEX. v2.384.63 agregó al parser constantes de matriz inline como {1,2;3,4} (las comas separan columnas, los punto y coma separan filas) y uniones de referencias como (A1:B2,D4). Las comparaciones elemento por elemento también le dan a un elemento vacío el tipo del otro lado, FALSE contra 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, era -1 antes de v2.384.61
end;

TXLSXWorkbook.Calculate evalúa una cadena de fórmula contra la hoja activa sin guardarla, una manera rápida de chequear el comportamiento del engine. Una advertencia 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?

Lograr que Excel aceptara la marca de matriz dinámica tomó tres correcciones que ninguna prueba de round-trip consigo mismo habría detectado, porque HotXLS leía su propia salida correctamente en todos los casos. Cada una se encontró abriendo la salida de HotXLS en Excel 16 y reemplazando una variable a la vez:

1. El GUID de la extensión debe ir todo en minúsculas. El ext uri en 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 workbooks creados con TXLSXRange.SetDynamicArrayFormula antes de v2.384.68 tenían el mismo problema
2. El texto de la raíz de matriz no lleva = inicial. El writer XLSX emite el texto guardado de una raíz de matriz verbatim 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, razón por la cual 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 encendidos, y VarIsNumeric(True) también devuelve True. Antes de v2.384.61 eso hacía que =TRUE*1 devolviera -1 y permitía 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 ahora prueba 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 de formato

En BIFF8, cada token 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 reference, 2 para value, 3 para array. Los cinco bits bajos nombran el token, así que la misma referencia de área tiene tres escrituras:

Token	Clase reference	Clase value	Clase array
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

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

• Constantes de matriz de clase reference. El encoder elegía la clase según el contexto, y los parámetros de SUM o ROWS son de clase reference, así que =SUM({1,2}) se escribía con PtgArray como $20. Excel muestra la fórmula completa como =#N/A. Una constante de matriz jamás puede ser una referencia, así que desde v2.384.63 HotXLS escribe clase array $60 donde el contexto pida una referencia
• Operandos de clase value de PtgIsect y PtgUnion. Los operadores binarios tomaban operandos de clase value, 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 reference, $25
• Operandos de clase value dentro del registro ARRAY. Excel aplica intersección implícita incluso dentro de una fórmula de matriz cuando un operando es de clase value. HotXLS escribía $45 ahí, así que la fórmula de matriz de una celda para =SUM(A1:B1*{10,100}) evaluaba a 10 en Excel. Desde v2.384.68, el token stream de un registro ARRAY promueve cada referencia de clase value y cada constante de matriz a clase array, $65 y $60, que es lo que escribe Excel

Un reader que ignore los bits de clase hace round-trip de los tres felizmente, así que si mantiene su propio writer BIFF8, compare los bits de clase de cada token 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 en una fórmula plana sin marca recibe un rango de varias celdas o una matriz inline
• 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 operadores; un rango pasado directo a un argumento de función queda como fórmula plana
• Solo se marcan las fórmulas introducidas por TXLSXCell.Formula o 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 de ext uri de matriz dinámica debe ir en minúsculas o Excel rechaza el paquete
• En Delphi, Double(True) es -1; pruebe varBoolean antes de la conversión numérica
• BIFF8: las constantes de matriz jamás en clase reference, operandos de PtgIsect / PtgUnion en clase reference, operandos del registro ARRAY en clase array

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