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 lo documenta Microsoft. La versión 2.382.3 después evitó que esos flags de selección se filtraran a la evaluación de las mismas celdas que la función referencia. El primer defecto es vergonzoso de la forma en que siempre lo son los bugs de transcripción de tablas: las posiciones de los bits estaban intercambiadas, así que cada fórmula que usaba 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 usted va 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 trae una celda cuya fórmula todavía no se calculó. 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á mal por una cantidad que nadie puede explicar solo con el texto de la fórmula

¿Qué seleccionan realmente 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 saltearlas es lo que viene por defecto con los códigos bajos. Hay dos cosas de esto que 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 de las otras dos: solo los códigos 4 a 7 tratan como valor común a una celda cuya propia fórmula es 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, que en los archivos OOXML se guarda bajo el prefijo _xlfn., generaliza esa división dentro del 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é HotXLS tenía las opciones de AGGREGATE al revés?

Porque la 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 brecha como límite abierto y describía el mapeo viejo tal como salía entonces; la descripción era fiel al código y estaba equivocada respecto de Excel, y nadie lo notó por mucho tiempo porque las dos políticas que más se combinan, ocultas más errores, caen en los códigos 3 y 7 con las dos tablas. Solo un código de un bit dejaba al descubierto el intercambio: AGGREGATE(9,1,A1:A4) devolvía la suma sin filtrar, y AGGREGATE(9,2,...) salteaba las filas ocultas mientras seguía propagando #DIV/0!. El defecto apareció en una revisión estática de lxCalc.pas, registrado como HXLS-008 en el registro de problemas conocidos del proyecto, no a partir del archivo de un cliente, lo que dice algo sobre lo poco que aparecen los códigos de un bit en los libros de producción. La versión 2.382.0 reescribió la decodificación como tres pruebas de pertenencia a conjuntos y agregó un segundo gate para la política de anidados, cableado a través de un callback TXLSIsSubtotalCell nuevo que el workbook provee junto con TXLSIsRowHidden

// TXLSCalculator.CalcAggregateFunc, forma de 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
  // ... mapea function_num al iftab interno, recorre ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Fíjese que los dos flags se asignan de forma incondicional, en lugar de activarse 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 de filas ocultas externo en lugar de limpiarlo. Asignar el valor decodificado al entrar y restaurar el valor previo 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 por elemento, donde el código viejo solo probaba si había un double NaN y en cualquier otro caso le entregaba el array completo a ExcelSum

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

Porque FIgnoreHiddenRows y FIgnoreSubtotalCells son campos de la calculadora, y la calculadora la comparten todas las fórmulas que se evalúan durante un recálculo. Los gates se diseñaron como campos de trabajo justamente para que seis bucles de recorrido de celdas pudieran consultarlos sin pasar un parámetro por cada firma, y ese diseño es sólido mientras todo lo que corre con un gate armado pertenezca a la agregación que lo armó. La suposición se rompe en un punto concreto: FGetValue. Cuando un walker le pide al workbook el valor de una celda y esa celda contiene una fórmula sin resultado cacheado, el workbook compila la fórmula y la evalúa en el momento, sobre la misma TXLSCalculator, con los gates externos todavía puestos. El fixture de regresión en HotXLS.WorkbookApiTests.pas muestra la falla 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úe =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 2.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 activa 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 volvía como 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 filtraba igual en la otra dirección. Con los códigos 0 a 3, FIgnoreSubtotalCells está armado, y el walker genérico de rangos en GetValueItemRange lo respeta, así que un precedente cuya fórmula es =SUM(B1:B3) descartaría B2 en silencio si B2 contuviera un SUBTOTAL. Peor todavía, CalcSubtotalFunc resetea FIgnoreSubtotalCells a False al salir en lugar de restaurar el valor previo, así que un precedente SUBTOTAL sin cachear alcanzado a mitad del recorrido desarmaba el gate externo para cada celda posterior. El registro de problemas conocidos del proyecto archiva 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 transitorio global que es correcto para el marco que lo puso y equivocado para cada marco que lo hereda

Cómo aíslan el recorrido AggregateGetCellValue y AggregateGetItemValue

El fix de la v2.382.3 pone una frontera alrededor de cada punto donde AGGREGATE lee un valor que no calculó él mismo. TXLSCalculator.AggregateGetCellValue envuelve la llamada cruda a FGetValue: guarda los dos flags, los limpia, hace el fetch y los restaura en un bloque finally. La agregación externa igual aplica su propia política a la celda que acaba de traer, porque las pruebas de fila oculta y de celda anidada ocurren en el walker alrededor del fetch, 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 común en un array Variant bidimensional a través de AggregateGetCellValue, mapeando a VarAsError una celda que devolvió un código de error para que la política de errores igual se pueda aplicar por elemento, y recursa por los nodos de operadores binarios y unarios (SA_ADD, SA_DIV, SA_UNARMINUS y el resto) con ApplyArrayBinaryOp y ApplyArrayUnaryOp; cualquier otra cosa cae al GetValueItem normal. Dos guardas se sientan delante de la materialización: un rango más grande que EffectiveFormulaArrayMemoryLimit devuelve lxErrorResourceLimit, y un rango multihoja o invertido devuelve #VALUE!. Un código de límite de recursos a propósito 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 memoria agotada porque el usuario pidió saltear #N/A estaría mintiendo. Los tres walkers de AGGREGATE, AggregateCollectRange para la familia de SUM, AggregateReduceVariance para STDEV, VAR y PRODUCT, y AggregateReduceWithK para MEDIAN y las formas de cuantiles, se pasaron de FGetValue y GetValueItem a los dos wrappers, y cada uno ganó la prueba 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 bien las celdas de error, pero 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* que corresponda, ya sea que el Variant sea un varError genuino o uno de los siete strings de error, y AggregateValueIsError ahora no es más que una prueba de que el resultado sea distinto de cero. Cada walker registra el primer código de error que ve y devuelve ese código, lo que además significa que una celda cuya fórmula nunca se calculó, y cuyo error por lo tanto llega como código de retorno de FGetValue en lugar de como Variant cacheado, propaga igual que una cacheada. Dos funciones de conteo 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, sin importar 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, salvo que el código de opciones ignore errores, en cuyo caso se saltea. Esa asimetría es cómo 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" resuelve mal sin hacer ruido

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 chequea el resultado contra una expectativa derivada a mano. Los códigos 0, 1, 4 y 5 tienen que propagar el #DIV/0! de A3, ya que ninguno de ellos ignora errores. El código 2 da SUM 30 y MEDIAN 15, a partir de 10 y 20 con la celda anidada A4 salteada. 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 corrida 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 cacheado y 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 del grupo = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  se saltean ocultas, el error se propaga
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       se saltean ocultas + error + anidadas
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       solo se saltean 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 conviene conocer antes de apoyarse en esto. Primero, el predicado de agregado anidado es textual. TXLSXWorkbook.GetCalcIsSubtotalCell y su gemelo del motor clásico devuelven True cuando la fórmula de una celda empieza con SUBTOTAL(, AGGREGATE( o _xlfn.AGGREGATE(, con o sin el signo igual adelante, 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 van a contar doble donde Excel la saltearía; un generador que emita subtotales calculados debería mantener la llamada de agregación al frente de la fórmula. Segundo, el aislamiento vive en los tres walkers de AGGREGATE. CalcSubtotalFunc todavía recorre 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 pasarle 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 queda limitada a la evaluación ad hoc con Calculate y a los libros cargados sin valores cacheados, y si usted se apoya en el recálculo incremental sobre el grafo de dependencias para mantener ágiles los modelos grandes, la 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 de workbook cablean los callbacks en sus constructores, pero el código que arma un TXLSCalculator a mano con solo los dos argumentos originales obtiene el comportamiento heredado de incluir todo para cada código de opciones, en silencio. Cuando un total se ve mal y el texto de la fórmula se ve 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 acá, el decodificador de opciones, los wrappers de fetch aislados y la matriz de regresión que los fija vienen como código fuente con el componente de planilla HotXLS Delphi, que lee, escribe y recalcula libros XLS, XLSX y ODS en Delphi y C++Builder sin necesidad de tener Excel instalado