Matriz de opciones de AGGREGATE y fuga de gates en HotXLS

HotXLS, el componente nativo de hojas de cálculo Excel para Delphi y C++Builder, publicó en septiembre de 2026 dos fixes relacionados de AGGREGATE. La versión 2.382.0 corrigió el argumento de opciones para que los códigos 1/3/5/7 ignoren las filas ocultas, 2/3/6/7 ignoren los errores, y 0 a 3 ignoren las celdas SUBTOTAL y AGGREGATE anidadas, exactamente como documenta Microsoft. La versión 2.382.3 impidió después que esos flags de selección se colaran en la evaluación de las propias celdas a las que la función hace referencia. El primer defecto da vergüenza de la forma en que siempre dan vergüenza los bugs de transcribir tablas: las posiciones de los bits estaban intercambiadas, así que cada fórmula que usara un código de opciones distinto de cero recibía una política que su autor no había pedido. El segundo es más interesante, porque es una forma que te vas a encontrar en cualquier evaluador que use un campo transitorio para pasar contexto a un recorrido recursivo. Una agregación externa arma un flag, recorre un rango, y saca una celda cuya fórmula todavía no se ha calculado. Esa fórmula corre en la misma calculadora, ve el mismo flag armado, y agrega en silencio las filas equivocadas, produciendo un número que está desviado en una cantidad que nadie puede explicar solo con el texto de la fórmula

¿Qué seleccionan en realidad las opciones 0 a 7 de AGGREGATE?

El argumento de opciones de AGGREGATE es una matriz de tres bits, y los tres bits son independientes. El bit 0 (valor 1) significa ignorar las filas ocultas, el bit 1 (valor 2) significa ignorar los valores de error, y el bit 2 (valor 4) significa dejar de ignorar las celdas SUBTOTAL y AGGREGATE anidadas, porque saltárselas es el comportamiento por defecto en los códigos bajos. Dos cosas de esto son fáciles de entender al revés. El bit de filas ocultas es el bit bajo, no el del medio, así que AGGREGATE(9,1,...) es la forma de total filtrado y AGGREGATE(9,2,...) es la tolerante a errores. Y la política de agregados anidados está invertida respecto a las otras dos: solo los códigos 4 a 7 tratan como valor ordinario una celda cuya propia fórmula sea un SUBTOTAL o un AGGREGATE. ECMA-376 Parte 1 §18.17.7 define SUBTOTAL con la misma división de incluir o excluir filas ocultas entre los códigos 1-11 y 101-111, y AGGREGATE, guardado en los archivos OOXML bajo el prefijo _xlfn., generaliza esa división en el argumento de opciones, así que la tabla que Microsoft publica para la función AGGREGATE es el contrato que un motor tiene que cumplir y no una comodidad

Opción	Filas ocultas	Valores de error	SUBTOTAL / AGGREGATE anidados
0	incluidas	propagados	ignorados
1	ignoradas	propagados	ignorados
2	incluidas	ignorados	ignorados
3	ignoradas	ignorados	ignorados
4	incluidas	propagados	incluidos
5	ignoradas	propagados	incluidos
6	incluidas	ignorados	incluidos
7	ignoradas	ignorados	incluidos

¿Por qué tenía HotXLS las opciones de AGGREGATE al revés?

Porque el TXLSCalculator.CalcAggregateFunc original se escribió a partir de una paráfrasis de la tabla y no de la tabla. Calculaba ignoreErrors := (optCode >= 4) and (optCode <= 7) y armaba el gate de filas ocultas para los códigos 2, 3, 6 y 7, mientras que la política de agregados anidados no estaba implementada en absoluto. El artículo anterior sobre filas ocultas en SUBTOTAL y AGGREGATE listaba esa laguna como límite abierto y describía el mapeo antiguo tal como se publicaba entonces; la descripción era exacta respecto al código e incorrecta respecto a Excel, y nadie se dio cuenta durante mucho tiempo porque las dos políticas que más gente combina, ocultas más errores, caen en los códigos 3 y 7 con las dos tablas. Solo un código de un único bit destapó el intercambio: AGGREGATE(9,1,A1:A4) devolvía la suma sin filtrar, y AGGREGATE(9,2,...) se saltaba las filas ocultas pero seguía propagando #DIV/0!. El defecto salió de una revisión estática de lxCalc.pas, registrado como HXLS-008 en el registro de problemas conocidos del proyecto, no de un archivo de cliente, lo que dice algo sobre lo poco que aparecen los códigos de un solo bit en libros de trabajo de producción. La versión 2.382.0 reescribió la decodificación como tres comprobaciones de pertenencia a conjuntos y añadió un segundo gate para la política de anidados, cableado a través de un nuevo callback TXLSIsSubtotalCell que el libro de trabajo proporciona junto a TXLSIsRowHidden

// TXLSCalculator.CalcAggregateFunc, tal como queda en la v2.382.3
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // Excel rechaza los códigos fuera de 0..7
  Exit;
end;
ignoreErrors := optCode in [2, 3, 6, 7];
prevIgnoreHidden := FIgnoreHiddenRows;
prevIgnoreSubtotal := FIgnoreSubtotalCells;
FIgnoreHiddenRows := (optCode in [1, 3, 5, 7]) and Assigned(FIsRowHidden);
FIgnoreSubtotalCells := (optCode in [0, 1, 2, 3]) and Assigned(FIsSubtotalCell);
try
  // ... mapear function_num al iftab interno, recorrer ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Fíjate en que los dos flags se asignan sin condición, en lugar de ponerse solo cuando la opción los pide. La versión 2.382.0 todavía usaba if ... then FIgnoreHiddenRows := True, lo que significaba que un AGGREGATE con código 4 anidado dentro de un SUBTOTAL(109, ...) heredaba el gate externo de filas ocultas en lugar de limpiarlo. Asignar el valor decodificado en la entrada y restaurar el valor anterior en el bloque finally hace que cada llamada a AGGREGATE sea dueña de su política durante su recorrido y nada más. La versión 2.382.0 también hizo honesta la forma de array: cuando un argumento evalúa a un array Variant de una o dos dimensiones, CalcAggregateFunc ahora recorre cada elemento y aplica la política de errores elemento a elemento, donde el código viejo solo comprobaba un double NaN y en caso contrario le pasaba el array entero a ExcelSum

¿Por qué se cuela un AGGREGATE externo en las fórmulas a las que hace referencia?

Porque FIgnoreHiddenRows y FIgnoreSubtotalCells son campos de la calculadora, y la calculadora la comparten todas las fórmulas evaluadas durante un recálculo. Los gates se diseñaron como campos de trabajo precisamente para que seis bucles de recorrido de celdas pudieran consultarlos sin enhebrar un parámetro por cada firma, y ese diseño es sólido siempre que todo lo que corre mientras un gate está armado pertenezca a la agregación que lo armó. La suposición se rompe en un punto concreto: FGetValue. Cuando un recorredor le pide al libro de trabajo el valor de una celda y esa celda contiene una fórmula sin resultado cacheado, el libro compila la fórmula y la evalúa al momento, en la misma TXLSCalculator, con los gates externos todavía puestos. El fixture de regresión en HotXLS.WorkbookApiTests.pas muestra el fallo con cuatro celdas. A1 contiene 10, A2 contiene 20 en una fila oculta, A3 contiene =1/0, y A4 contiene =SUBTOTAL(9,A1:A2), cuyo valor correcto es 30. Ahora evalúa =AGGREGATE(9,7,A1:A4): ignorar filas ocultas, ignorar errores, contar el subtotal anidado como valor. Excel devuelve 10 + 30 = 40. Con A4 sin cachear, el motor anterior a la v2.382.3 armaba el gate de filas ocultas, llegaba hasta A4, disparaba su evaluación, y CalcSubtotalFunc para el código 9 heredaba el gate armado, porque solo pone el flag para los códigos 101 a 111 y nunca lo limpia. A4 evaluaba a 10 en lugar de 30, y el total externo salía 20. Ninguna de las dos fórmulas menciona filas ocultas en el camino que produjo el número equivocado

El gate de agregados anidados se colaba igual en la otra dirección. Con los códigos 0 a 3, FIgnoreSubtotalCells está armado, y el recorredor genérico de rangos de GetValueItemRange lo respeta, así que un precedente cuya fórmula sea =SUM(B1:B3) se saltaría B2 en silencio si B2 resultara contener un SUBTOTAL. Peor aún, CalcSubtotalFunc resetea FIgnoreSubtotalCells a False a la salida en lugar de restaurar el valor anterior, así que un precedente SUBTOTAL sin cachear alcanzado a mitad de recorrido desarmaba el gate externo para todas las celdas posteriores. El registro de problemas conocidos del proyecto encuadra esto bajo HXLS-008 como fuga de estado de selección anidado, y ese es el nombre correcto para la clase de bug: un flag global transitorio que es correcto para el marco que lo puso y erróneo para todos los marcos que lo heredan

Cómo aíslan el recorrido AggregateGetCellValue y AggregateGetItemValue

El fix de la v2.382.3 pone una frontera alrededor de cada punto en el que AGGREGATE lee un valor que no ha calculado él mismo. TXLSCalculator.AggregateGetCellValue envuelve la llamada cruda a FGetValue: guarda los dos flags, los limpia, hace la lectura, y los restaura en un bloque finally. La agregación externa sigue aplicando su propia política a la celda que acaba de leer, porque las comprobaciones de fila oculta y de celda anidada ocurren en el recorredor alrededor de la lectura, pero la fórmula precedente en sí corre sin política alguna, que es lo que hace Excel

function TXLSCalculator.AggregateGetCellValue(SheetIndex, Row, Col: Integer;
  var Value: Variant; var OutOfRange: Boolean): Integer;
var
  Hidden, Nested: Boolean;
begin
  Hidden := FIgnoreHiddenRows;
  Nested := FIgnoreSubtotalCells;
  FIgnoreHiddenRows := False;        // una fórmula precedente es dueña de su propia política
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

AggregateGetItemValue hace lo mismo con los argumentos que no son rangos, y tiene que hacer más que limpiar flags, porque un argumento como A1:A4/(B1:B4-20) es un array calculado cuya forma de elementos tiene que sobrevivir. El wrapper materializa un rango simple en un array Variant bidimensional a través de AggregateGetCellValue, mapeando una celda que devolvió un código de error a VarAsError para que la política de errores se pueda aplicar igualmente elemento a elemento, y recursa por los nodos de operador binario y unario (SA_ADD, SA_DIV, SA_UNARMINUS y el resto) con ApplyArrayBinaryOp y ApplyArrayUnaryOp; cualquier otra cosa cae al GetValueItem normal. Dos guardias se sientan delante de la materialización: un rango mayor que EffectiveFormulaArrayMemoryLimit devuelve lxErrorResourceLimit, y un rango multi-hoja o invertido devuelve #VALUE!. Un código de límite de recursos deliberadamente no se trata como error de celda ignorable ni siquiera bajo las opciones 2/3/6/7, ya que un motor que se tragara su propia señal de falta de memoria porque el usuario pidió saltarse #N/A estaría mintiendo. Los tres recorredores de AGGREGATE, AggregateCollectRange para la familia SUM, AggregateReduceVariance para STDEV, VAR y PRODUCT, y AggregateReduceWithK para MEDIAN y las formas de cuantil, se pasaron de FGetValue y GetValueItem a los dos wrappers, y cada uno ganó la comprobación de celda anidada a través de FIsSubtotalCell

¿Qué error devuelve AGGREGATE cuando no ignora los errores?

El original, desde la v2.382.3. La versión 2.382.0 detectaba las celdas de error correctamente pero las colapsaba todas en lxErrorValue, así que AGGREGATE(9,4,A1:A3) sobre una celda #DIV/0! devolvía #VALUE!, cuando Excel propaga sin cambios el primer error que encuentra. El helper de reemplazo AggregateErrorCode mapea un Variant al código lxError* correspondiente, ya sea el Variant un varError genuino o una de las siete cadenas de error, y AggregateValueIsError ahora no es más que una comprobación de resultado distinto de cero. Cada recorredor registra el primer código de error que ve y devuelve ese código, lo que también significa que una celda cuya fórmula nunca se calculó, y cuyo error llega por tanto como código de retorno de FGetValue en lugar de como Variant cacheado, se propaga igual que una cacheada. Dos funciones de recuento reciben trato especial dentro de AggregateCollectRange, y el trato coincide con SUBTOTAL y no con SUM. Para la función interna 0, COUNT, una celda de error nunca se cuenta ni se propaga sea cual sea el código de opciones, porque COUNT solo cuenta números. Para la función interna 169, COUNTA, una celda de error es un valor no vacío y cuenta como 1 a menos que el código de opciones ignore los errores, en cuyo caso se salta. Esa asimetría es como trata Excel a COUNT y COUNTA también fuera de AGGREGATE, y es el tipo de detalle que una regla genérica de «si hay error, propagar» se salta en silencio

Qué verifica la matriz de regresión de ocho opciones

El fixture descrito arriba se ejercita como matriz completa en AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: para cada código de opciones de 0 a 7 evalúa tanto la forma SUM como la forma MEDIAN sobre A1:A4 y comprueba el resultado contra una expectativa derivada a mano. Los códigos 0, 1, 4 y 5 deben propagar el #DIV/0! de A3, ya que ninguno de ellos ignora los errores. El código 2 da SUM 30 y MEDIAN 15, a partir de 10 y 20 con la A4 anidada saltada. El código 3 da 10 y 10. El código 6 da 60 y 20, porque el 30 de A4 ahora cuenta. El código 7 da 40 y 20, que es el caso que devolvía 20 antes del fix de la fuga. La ejecución de aceptación más amplia registrada en el registro de problemas conocidos cubre los diecinueve números de función contra los ocho códigos, con cada precedente tanto cacheado como sin cachear, para 304 escenarios en Win32 y Win64

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 10;
    Sheet.Cells[2, 1].Value := 20;
    Sheet.Cells[3, 1].Formula := '=1/0';
    Sheet.Cells[4, 1].Formula := '=SUBTOTAL(9,A1:A2)';   // subtotal de grupo = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  ocultas saltadas, el error se propaga
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       ocultas + errores + anidados saltados
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       solo se saltan los errores
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       era 20 antes de la v2.382.3
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Dónde sigue estando la frontera

Hay tres límites que merece la pena conocer antes de construir sobre esto. Primero, el predicado de agregado anidado es textual. TXLSXWorkbook.GetCalcIsSubtotalCell y su gemelo del motor clásico responden True cuando la fórmula de una celda empieza por SUBTOTAL(, AGGREGATE( o _xlfn.AGGREGATE(, con o sin el signo igual delante, así que una fórmula como =IF(C1,SUBTOTAL(9,B1:B9),0) o =SUBTOTAL(9,B1:B9)*2 no se reconoce como anidada y los códigos 0 a 3 la contarán dos veces donde Excel se la saltaría; un generador que emita subtotales calculados debería mantener la llamada de agregación al principio de la fórmula. Segundo, el aislamiento vive en los tres recorredores de AGGREGATE. CalcSubtotalFunc sigue recorriendo a través de GetValueItemRange, CollectRangeValues y SubtotalReduceVariance, que llaman a FGetValue directamente, así que un SUBTOTAL(109, ...) cuyo rango contenga una fórmula precedente sin cachear todavía puede pasar su gate de filas ocultas a ese precedente. Un Recalculate completo evalúa los precedentes antes que los dependientes, así que se toma el camino cacheado y el gate nunca se hereda; la exposición se limita a la evaluación ad hoc mediante Calculate y a libros de trabajo cargados sin valores cacheados, y si te apoyas en el recálculo incremental sobre el grafo de dependencias para mantener ágiles los modelos grandes, esa misma garantía de orden es lo que mantiene esta fuga dormida. Tercero, los dos gates están condicionados a Assigned(FIsRowHidden) y Assigned(FIsSubtotalCell). Las dos fachadas del libro de trabajo cablean los callbacks en sus constructores, pero el código que construye un TXLSCalculator a mano con solo los dos argumentos originales obtiene el comportamiento heredado de incluirlo todo para cualquier código de opciones, en silencio. Cuando un total parece mal y el texto de la fórmula parece bien, trazar la evaluación paso a paso es la forma más rápida de ver si un precedente se evaluó bajo un gate heredado o si simplemente nunca se enganchó un callback

El motor de cálculo descrito aquí, el decodificador de opciones, los wrappers de lectura aislados y la matriz de regresión que los fija vienen todos como código fuente con el componente de hoja de cálculo HotXLS para Delphi, que lee, escribe y recalcula libros XLS, XLSX y ODS en Delphi y C++Builder sin necesidad de tener Excel instalado