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:
| Fórmula | Resultado HotXLS | Excel 16, guardada como fórmula plain | Guardada por HotXLS |
|---|---|---|---|
=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:
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)yA1:B2-1se marcan, aparezcan donde aparezcan en la fórmula, incluido dentro de SUMPRODUCTSUM(A1:B2)ySUMPRODUCT(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 tocaA1*2oSUM(A1,B1)*2no 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)*1o--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:
- El GUID de la extensión debe ser todo minúsculas. El
ext urienxl/metadata.xmltiene 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 - 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 cualTXLSXCell.Formulase lee de vuelta sin él Double(True)es -1 en Delphi. La conversión Variant sigue la convención COM donde TRUE son todos los bits en uno, yVarIsNumeric(True)también devuelve True. Una conversión ingenua entonces hace que=TRUE*1devuelva -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)=TRUEsale mal. HotXLS pruebavarBooleanantes 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:
| Token | Clase reference | Clase value | Clase 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})conPtgArraycomo$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$60dondequiera que el contexto pida una referencia - Operandos de clase valor de
PtgIsectyPtgUnion. Dar a los operadores binarios operandos de clase valor está bien para*pero mal para los operadores de referencia. Con áreas$45antes dePtgIsect($0F), Excel lee=SUM(A1:B2 B1:B2)como=SUM(@A1:B2 @B1:B2)y devuelve#VALUE!. HotXLS escribe los operandos dePtgIsectyPtgUnion($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
$45ahí, 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,$65y$60, que es lo que escribe Excel
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", metadataXLDAPR) y como fórmulas de matriz de una celda en XLS (FORMULA conPtgExpmá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.Formulao laFormula/Valueclá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 uride matriz dinámica debe ir en minúsculas o Excel rechaza el paquete - En Delphi,
Double(True)es -1; pruebevarBooleanantes de la conversión numérica - BIFF8: las constantes de matriz jamás en clase reference, operandos de
PtgIsect/PtgUnionen 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