Opciones de AGGREGATE y gates anidados en HotXLS para Delphi

Bastan cuatro celdas para que =AGGREGATE(9,7,A1:A4) devuelva 20 en un evaluador hecho a mano mientras Excel muestra 40. A1 contiene 10, A2 contiene 20 en una fila oculta, A3 contiene =1/0 y A4 contiene =SUBTOTAL(9,A1:A2), un subtotal de grupo que vale 30. La opción 7 significa omitir filas ocultas, omitir errores y contar los subtotales anidados como valores, así que Excel suma 10 y 30. El 20 aparece cuando el ajuste «omitir filas ocultas» de la llamada externa sigue activado mientras A4 calcula su propio valor, lo que reduce A4 a 10. Obtener 40 exige dos cosas: decodificar los tres bits de opciones en las posiciones correctas y asegurarse de que una fórmula referenciada nunca vea las opciones de la fórmula que la referenció

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

El argumento de opciones es una matriz de tres bits, y los bits son independientes entre sí. El bit 0 (valor 1) omite filas ocultas, el bit 1 (valor 2) omite valores de error y el bit 2 (valor 4) deja de omitir las celdas cuya propia fórmula es un SUBTOTAL o un AGGREGATE, porque omitirlas es lo predeterminado en los códigos bajos. Dos partes de ese diseño se leen fácilmente al revés. El bit de filas ocultas es el bit bajo, de modo que AGGREGATE(9,1,...) es el total filtrado y AGGREGATE(9,2,...) es el tolerante a errores. Y la política de anidados funciona al revés que las otras dos: solo los códigos 4 a 7 tratan una agregación anidada como un número ordinario. ECMA-376 Part 1 §18.17.7 define SUBTOTAL con la misma división de incluir o excluir filas ocultas en los códigos 1–11 y 101–111; AGGREGATE, almacenado en OOXML bajo el prefijo _xlfn., pliega esa división en su argumento de opciones, y la tabla que Microsoft publica para la función es el contrato que cualquier motor debe cumplir

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

Cualquier código fuera de 0 a 7 se rechaza con #VALUE!, que es también lo que hace Excel

Por qué los códigos de un solo bit son donde fallan las decodificaciones

Una decodificación escrita a partir de un resumen de la tabla en lugar de la propia tabla suele quedar así: ignorar errores para los códigos 4 y superiores, omitir filas ocultas para 2, 3, 6 y 7, y dejar fuera por completo la política de anidados. Esa versión puede sobrevivir mucho tiempo en producción. La combinación que la gente realmente usa, filas ocultas más errores, cae en los códigos 3 y 7 bajo ambas lecturas, así que el fallo solo se ve con un código de un solo bit. AGGREGATE(9,1,A1:A4) devuelve entonces la suma sin filtrar, y AGGREGATE(9,2,...) omite filas ocultas mientras sigue propagando #DIV/0!. Los libros reales rara vez usan los códigos 1 y 2 por sí solos, que es exactamente por lo que una revisión contra la tabla publicada detecta más que esperar a un archivo de un cliente

HotXLS decodifica las opciones como tres pruebas de pertenencia a conjuntos, una por columna de la tabla, y asigna los tres resultados cuando empieza la llamada a AGGREGATE, restaurando al terminar lo que hubiera en vigor antes. La asignación es incondicional a propósito. Una decodificación escrita como «si la opción lo pide, activa el flag» permite que un AGGREGATE con código 4 situado dentro de un SUBTOTAL(109, ...) herede el ajuste de filas ocultas del exterior en lugar de limpiarlo. El esquema de abajo muestra la misma regla para quien escriba o revise su propio evaluador

// Decodificación ilustrativa para su propio evaluador: TAggPolicy y
// DecodeAggregateOptions son nombres que usted define, no API de HotXLS
type
  TAggPolicy = record
    IgnoreHidden, IgnoreErrors, SkipNested: Boolean;
  end;

function DecodeAggregateOptions(OptCode: Integer; out P: TAggPolicy): Boolean;
begin
  Result := (OptCode >= 0) and (OptCode <= 7);   // cualquier otra cosa es #VALUE!
  if not Result then
    Exit;
  P.IgnoreHidden := OptCode in [1, 3, 5, 7];       // bit 0
  P.IgnoreErrors := OptCode in [2, 3, 6, 7];       // bit 1
  P.SkipNested   := OptCode in [0, 1, 2, 3];       // bit 2 a cero
end;

El lado SUBTOTAL de las mismas reglas de filas ocultas, incluida la comparación entre filas filtradas y filas ocultas a mano, queda tratado en el artículo sobre filas ocultas de SUBTOTAL y AGGREGATE

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

Porque la forma natural de pasar «omitir filas ocultas» hacia abajo a seis o siete bucles que recorren celdas es un campo del objeto calculadora, y una calculadora evalúa cada fórmula en una pasada de recálculo. Ese diseño se sostiene mientras todo lo que se ejecuta con el campo activo pertenezca a la agregación que lo activó. Deja de sostenerse en un momento concreto: la lectura de la celda. Cuando el recorrido pide el valor de una celda y esa celda contiene una fórmula sin resultado en caché, el libro compila y evalúa esa fórmula en el momento, en la misma calculadora, con los ajustes externos aún en su sitio

Recorra el ejemplo inicial con un motor así. AGGREGATE con código 7 activa la omisión de filas ocultas y empieza en A1. A1 da 10, A2 está oculta y se omite, A3 es un error y se omite. Entonces el recorrido llega a A4, que no se ha calculado. El SUBTOTAL(9,A1:A2) de A4 se ejecuta, y una implementación de SUBTOTAL que solo activa la omisión de filas ocultas para los códigos 101 a 111 (una forma perfectamente razonable de escribirlo) nunca la desactiva para el código 9. Encuentra el ajuste ya activado, omite A2 y devuelve 10. El total externo se convierte en 10 + 10 = 20. Nada en la fórmula de A4 menciona filas ocultas

El ajuste de agregados anidados se cuela en ambas direcciones. Con los códigos 0 a 3 está activado, y un recorrido de rangos genérico que lo respetara haría que un precedente como =SUM(B1:B3) dejara fuera B2 cuando B2 contiene casualmente un SUBTOTAL. En la otra dirección, un SUBTOTAL que desactiva la omisión de anidados al terminar, en vez de restaurar el estado anterior, deja sin efecto la política del AGGREGATE externo para el resto de su recorrido. Cualquier evaluador que use un campo transitorio para llevar contexto a un recorrido recursivo tiene esta forma; el campo es correcto para el marco que lo activó y erróneo para cada marco que lo hereda

Cortar la política en la frontera de lectura

HotXLS pone una frontera alrededor de cada punto donde AGGREGATE lee un valor que no calculó él mismo. Antes de la lectura, ambos ajustes se guardan y se limpian; después, se restauran en un bloque finally. La agregación externa sigue aplicando su propia política a la celda que acaba de leer, porque las pruebas de filas ocultas y de celdas anidadas ocurren en el recorrido alrededor de la lectura. La fórmula referenciada se ejecuta sin política alguna, que es lo que hace Excel. Las tres familias de recorridos pasan por esa frontera: la familia SUM (SUM, AVERAGE, COUNT, COUNTA, MAX, MIN), las reducciones de forma cerrada (PRODUCT, STDEV, VAR) y las formas que toman un argumento k (MEDIAN, LARGE, SMALL, los percentiles y los cuartiles)

// Recorrido ilustrativo para su propio evaluador. FPolicy, FetchCell,
// IsRowHidden y IsSubtotalCell son miembros que usted define
function TMyEvaluator.FetchForAggregate(Sheet, Row, Col: Integer;
  var V: Variant): Integer;
var
  Saved: TAggPolicy;
begin
  Saved := FPolicy;
  FPolicy := Default(TAggPolicy);     // la fórmula referenciada tiene su propia política
  try
    Result := FetchCell(Sheet, Row, Col, V);
  finally
    FPolicy := Saved;
  end;
end;

// dentro del recorrido del rango: la política externa se aplica alrededor de la lectura
for R := R1 to R2 do
begin
  if FPolicy.IgnoreHidden and IsRowHidden(Sheet, R) then
    Continue;
  for C := C1 to C2 do
  begin
    if FPolicy.SkipNested and IsSubtotalCell(Sheet, R, C) then
      Continue;
    Code := FetchForAggregate(Sheet, R, C, V);
    // ... aplica la política de errores a Code / V y después acumula ...
  end;
end;

Los argumentos que no son rangos planos necesitan más que flags limpios. Un argumento como A1:A4/(B1:B4-20) es un array calculado, y su forma por elementos tiene que sobrevivir para que la opción 6 pueda descartar solo los elementos que dividieron por cero. HotXLS convierte cada rango referenciado en un array bidimensional a través de la misma frontera, guarda una celda que falló como elemento de error en vez de abortar, y aplica los operadores aritméticos y de comparación elemento a elemento. Un rango que abarca varias hojas, o uno cuyas esquinas están invertidas, devuelve #VALUE!. Un rango demasiado grande para TXLSCalculator.FormulaArrayMemoryLimit (64 MB por defecto) devuelve un resultado de límite de recursos, y ese resultado está deliberadamente fuera del conjunto de errores que las opciones 2, 3, 6 y 7 pueden omitir. Tragar la señal de memoria agotada del propio motor porque la fórmula pidió ignorar #N/A produciría un número seguro y equivocado, así que la fórmula falla

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

El primero que se encuentra, sin alterar. Plegar todos los errores en #VALUE! es el atajo fácil, y hace que AGGREGATE(9,4,A1:A3) sobre una celda #DIV/0! discrepe de Excel, que deja pasar el #DIV/0!. HotXLS mapea tanto las variantes de error genuinas como las siete cadenas de error (#NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A) de vuelta a sus propios códigos, y cada recorrido devuelve el primer código que registra. Una celda cuya fórmula nunca se calculó entrega su error como código de retorno de la lectura en lugar de como valor en caché; se propaga exactamente igual que uno en caché

Dos funciones de conteo siguen a SUBTOTAL en lugar de a SUM. Para la función 2, 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 3, COUNTA, una celda de error es un valor no vacío y cuenta como 1 salvo que el código de opciones omita errores. Esa asimetría es también cómo Excel trata COUNT y COUNTA fuera de AGGREGATE, y una regla general de «si es error, propágalo» se equivoca con ambas

Comprobar los ocho códigos en una hoja

Las cuatro celdas del ejemplo inicial forman una comprobación compacta de todos los códigos. Evalúe la forma SUM y la forma MEDIAN sobre A1:A4 para cada código de 0 a 7 y compare con valores calculados a mano. Los códigos 0, 1, 4 y 5 deben propagar el #DIV/0! de A3, porque ninguno omite errores. El código 2 da SUM 30 y MEDIAN 15, de 10 y 20 con la A4 anidada omitida. 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, y es el que un motor sin la frontera de lectura informa como 20. La comprobación solo significa algo si A4 está sin caché cuando corre el AGGREGATE, así que evalúe cada caso una vez con los precedentes ya calculados y otra sin ellos

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 omitidas, el error se propaga
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       ocultas, errores y anidados omitidos
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       solo se omiten errores
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       20 significa que un ajuste se coló en A4
    Sheet.Cells[10, 1].Formula := '=AGGREGATE(12,6,A1:A4)'; // 20     MEDIAN de 10, 20, 30
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Abrir el archivo guardado en Excel y pulsar Ctrl+Alt+F9 da una segunda opinión sobre las mismas celdas. Para un barrido más amplio, ejecute cada número de función de 1 a 19 contra los ocho códigos

Cablear los callbacks cuando usted mismo construye la calculadora

Ambos ajustes dependen de dos callbacks. TXLSCalculator.Create toma un TXLSIsRowHidden y un TXLSIsSubtotalCell después del getter de valores, y ambos toman nil por defecto. Si falta cualquiera de los dos, la columna correspondiente de la tabla nunca se aplica, así que todos los códigos de opciones incluyen filas ocultas, agregaciones anidadas, o ambas. Los objetos de libro XLSX y XLS clásico pasan sus propias implementaciones en sus constructores; el código que crea un TXLSCalculator a mano sobre su propia fuente de datos tiene que suministrar ambos. Los libros reconocen una agregación anidada por una fórmula que empieza por SUBTOTAL(, AGGREGATE( o _xlfn.AGGREGATE(, con o sin el igual inicial, y una fuente personalizada debería usar la misma regla

// TMySheetSource y FormulaAt son tipos suyos; las tres firmas de método
// coinciden con TXLSGetValue, TXLSIsRowHidden y TXLSIsSubtotalCell
// (SheetIndex basado en 1, Row y Col basados en 0 tal como los pasan los libros)
function TMySheetSource.IsSubtotalCell(SheetIndex, Row, Col: Integer): Boolean;
var
  F: string;
begin
  F := FormulaAt(SheetIndex, Row, Col);
  if (F <> '') and (F[1] = '=') then
    Delete(F, 1, 1);
  Result := SameText(Copy(F, 1, 9), 'SUBTOTAL(') or
            SameText(Copy(F, 1, 10), 'AGGREGATE(') or
            SameText(Copy(F, 1, 16), '_xlfn.AGGREGATE(');
end;

// Extern es la tabla de nombres de hoja que su aplicación ya mantiene
Calc := TXLSCalculator.Create(Extern, Source.GetCellValue,
  Source.IsRowHidden, Source.IsSubtotalCell);

La regla del prefijo tiene una consecuencia para los libros generados. Un generador que escribe =ROUND(SUBTOTAL(9,B2:B9),2) produce una celda que la prueba del prefijo no reconoce, así que los códigos 0 a 3 la cuentan y el grupo aterriza dos veces en el total general. Deje la llamada a SUBTOTAL o AGGREGATE lo más externa posible y ponga el redondeo en el formato numérico. Un Recalculate completo evalúa los precedentes antes que los dependientes, así que las agregaciones normalmente leen valores en caché, y el mismo orden se mantiene bajo el recálculo incremental sobre el grafo de dependencias. Cuando un total sigue pareciendo erróneo y la fórmula parece correcta, trazar la evaluación paso a paso muestra si algún precedente se ejecutó bajo demanda dentro del recorrido o si un callback nunca se conectó

El decodificador de opciones, la frontera de lectura y los puntos de enganche de callbacks descritos aquí vienen con el componente de hojas de cálculo HotXLS para Delphi, que lee, escribe y recalcula libros XLS, XLSX y ODS desde código Delphi y C++Builder sin instalación de Excel en la máquina