Artículo técnico

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 Excel entonces aplica intersección implícita legada a cada operando de operador. HotXLS Delphi Component guarda estas fórmulas con operadores de matriz de la manera en que lo hace Excel 365: como fórmulas dynamic-array de una sola celda en XLSX y como fórmulas de matriz de una celda en XLS

El síntoma sobrevive al code review. Un servicio Delphi escribe un workbook, su motor de fórmulas cachea 210 para =SUM(A1:B1*{10,100}) en una celda de fórmula ordinaria, y el cliente lo abre en Excel 16 para encontrar =SUM(@A1:B1*@{10,100}) en la barra de fórmulas y #VALUE! en la celda. Nada en el archivo está malformado. Lo que falta es el metadata que le dice a Excel que la fórmula se escribió bajo reglas de dynamic array, y sin él Excel retrocede a su modelo de evaluación previo a los dynamic arrays

¿Por qué Excel 365 agrega @ a una fórmula correctamente calculada?

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 legada se vuelve obvia. A1:B1 es un rango horizontal, la fórmula se sienta en la columna E, el rango no tiene celda en la columna E, así que @A1:B1 es #VALUE! y todo el SUM lo hereda. Bajo reglas de dynamic array el mismo texto multiplica elemento por elemento, 1 × 10 + 2 × 100, y devuelve 210. El motor de fórmulas de HotXLS evalúa a la manera dynamic array, y el formato de archivo también tiene que decirlo. Con A1:B2 guardando 1, 2, 3 y 4, estas son las fórmulas de sonda y lo que muestra Excel 16:

Diagrama de HotXLS que compara la intersección implícita con la evaluación de matriz dinámica de SUM(A1:B1*{10,100}) en la celda E5: el modelo heredado no encuentra ninguna celda del rango horizontal A1:B1 en la columna E y devuelve #VALUE!, mientras que el modelo de matriz dinámica multiplica 1 por 10 y 2 por 100 y devuelve 210
Excel inserta @ en la fórmula plana y muestra #VALUE!, porque la intersección implícita no encuentra nada en la columna E; con la marca de matriz dinámica de HotXLS la misma fórmula multiplica elemento por elemento y llega a 210
FórmulaResultado HotXLSExcel 16, guardada como fórmula plainGuardada por HotXLS
=SUM(A1:B1*{10,100})210#VALUE!Matriz dinámica, Excel muestra 210
=SUM((A1:B2>2)*1)2Intersección implícita, mal resultado o errorMatriz dinámica, Excel muestra 2
=SUMPRODUCT((A1:B2>2)*1)2Intersección implícita, mal resultado o errorMatriz dinámica, Excel muestra 2
=MAX(A1:B2-1)3Intersección implícita, mal resultado o errorMatriz dinámica, Excel muestra 3
=SUM(A1:B2)1010Fó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

Diagrama de almacenamiento de HotXLS para la fórmula con operador sobre matriz SUM(A1:B1*{10,100}): el engine XLSX escribe una matriz dinámica de una celda con cm igual a 1, un elemento f de tipo array y un registro XLDAPR en xl/metadata.xml cuyo GUID en minúsculas es obligatorio, mientras que el engine XLS escribe un registro FORMULA con PtgExp más un registro ARRAY 0221
El engine XLSX marca la celda con cm=1 más un registro de metadatos XLDAPR y el engine clásico empareja un FORMULA con PtgExp y un registro ARRAY sobre una celda; Excel 365 guarda las matrices dinámicas en XLS de la misma forma

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:

uses
  SysUtils, Variants, lxCalc, lxHandle, lxHandleX;   // lxOk se declara en lxCalc

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

Cómo el motor de cálculo iguala los valores de Excel 365

El marcado de almacenamiento solo rinde si el motor de fórmulas devuelve valores de Excel 365 en primer lugar, en ambos motores. SUMPRODUCT es el caso más visible: una implementación que acepta solo dos o más rangos plain devuelve #N/A para SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) y hasta el SUMPRODUCT(B1:B2) de un solo argumento. HotXLS evalúa los argumentos de 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. El operador de intersección espacio devuelve 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. El parser acepta constantes de array 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, coincidiendo con la regla escalar 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, TRUE cuenta como 1
end;

TXLSXWorkbook.Calculate evalúa un string de fórmula contra la hoja activa sin guardarla, una forma rápida de comprobar el comportamiento del motor. Una advertencia sobre @ mismo: HotXLS acepta @ entre dos referencias como intersección binaria y evalúa esa forma con semántica de intersección de verdad. 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 intersección y deje que las reglas de almacenamiento de arriba manejen la semántica de dynamic array

Tres detalles que Excel revisa al abrir salida de dynamic array

Lograr que Excel acepte el marcado de dynamic array se reduce a tres detalles que ningún test de round trip consigo mismo atrapa, porque una librería lee su propia salida correctamente de cualquier manera. Cada uno aparece solo cuando usted abre la salida en Excel 16 y reemplaza una variable a la vez:

  1. El GUID de la extensión debe ser todo minúsculas. El ext uri en xl/metadata.xml tiene que ser exactamente {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Escríbalo con mayúsculas y minúsculas mezcladas y Excel 16 se niega a abrir el paquete entero, no solo la celda
  2. El texto de la raíz del array no lleva = inicial. El escritor XLSX emite el texto almacenado de una raíz de array textual 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 Variant sigue la convención COM donde TRUE son todos los bits en uno, y VarIsNumeric(True) también devuelve True. Una conversión ingenua entonces hace que =TRUE*1 devuelva -1 y deja que los elementos lógicos de un array se clasifiquen como números, así que una comparación como (B1:B2>0)=TRUE sale mal. HotXLS prueba varBoolean antes de tratar un Variant como número en aritmética escalar, aritmética de arrays y clasificación de elementos de array, 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:

TokenClase referenceClase valueClase array
PtgRef$24$44$64
PtgArea$25$45$65
PtgArray$20$40$60

Tres lugares de un escritor BIFF8 son fáciles de equivocar, y cada uno produce un síntoma distinto en Excel mientras el reader del propio escritor lo lee bien:

  • Constantes de array de clase referencia. Un encoder que elige la clase por el contexto ve que los parámetros de SUM o ROWS son de clase referencia y escribe =SUM({1,2}) con PtgArray como $20. Excel muestra la fórmula completa como =#N/A. Una constante de array jamás puede ser una referencia, así que HotXLS escribe clase array $60 dondequiera que el contexto pida una referencia
  • Operandos de clase valor de PtgIsect y PtgUnion. Dar a los operadores binarios operandos de clase valor está bien para * pero mal para los operadores de referencia. Con áreas $45 antes de PtgIsect ($0F), Excel lee =SUM(A1:B2 B1:B2) como =SUM(@A1:B2 @B1:B2) y devuelve #VALUE!. HotXLS escribe los operandos de PtgIsect y PtgUnion ($10) en clase referencia, $25
  • Operandos de clase valor dentro del record ARRAY. Excel aplica intersección implícita incluso dentro de una fórmula de matriz cuando un operando es de clase valor. Con $45 ahí, la fórmula de matriz de una celda para =SUM(A1:B1*{10,100}) evalúa a 10 en Excel. HotXLS entonces promueve, en el token stream de un record ARRAY, cada referencia de clase valor y constante de array a clase array, $65 y $60, que es lo que escribe Excel
Diagrama BIFF8 de HotXLS: los bits 5 y 6 de cada byte de token eligen clase referencia, valor o array, así que PtgArea se deletrea 25, 45 y 65, con tres trampas de encoding: constantes de array como 20 muestran #N/A, operandos de PtgIsect como 45 devuelven #VALUE!, y operandos del record ARRAY como 45 hacen que SUM(A1:B1*{10,100}) devuelva 10
Cada token de operando BIFF8 lleva su clase en los bits 5 y 6, y Excel confía en esos bits por encima de la estructura; HotXLS escribe constantes de array como 60, operandos de PtgIsect como 25, y promueve los tokens del record ARRAY a la clase array

Un reader que ignora los bits de clase hace round trip de los tres felizmente, 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 en una fórmula plana sin marca recibe un rango de varias celdas o una matriz inline
  • HotXLS guarda tales fórmulas como dynamic arrays de una celda en XLSX (cm="1", t="array", metadata XLDAPR) y como fórmulas de matriz de una celda en XLS (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