Opciones de AGGREGATE y gates anidados en HotXLS para Delphi

Cuatro celdas bastan para que =AGGREGATE(9,7,A1:A4) devuelva 20 en un evaluador propio 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. El código 7 significa saltar las filas ocultas, saltar los errores y contar los subtotales anidados como valores, así que Excel suma 10 y 30. El 20 aparece cuando el ajuste de "saltar filas ocultas" de la llamada externa sigue activo mientras A4 calcula su propio valor, lo que reduce A4 a 10. Llegar a 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 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

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 bit son donde las decodificaciones se equivocan

Una decodificación escrita a partir de una paráfrasis de la tabla en lugar de la tabla misma suele verse así: ignorar los errores para los códigos 4 en adelante, saltar las filas ocultas para 2, 3, 6 y 7, y dejar la política de anidados completamente fuera. Esa versión puede sobrevivir mucho tiempo en producción. La combinación que más usa la gente, filas ocultas más errores, cae en los códigos 3 y 7 bajo las dos lecturas, así que el intercambio solo se nota en un código de un bit. AGGREGATE(9,1,A1:A4) devuelve entonces la suma sin filtrar, y AGGREGATE(9,2,...) salta las filas ocultas mientras sigue propagando #DIV/0!. Los libros reales rara vez usan los códigos 1 y 2 solos, que es justamente por lo que una revisión contra la tabla publicada detecta más que esperar a que llegue el 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 la llamada a AGGREGATE arranca, restaurando al terminar lo que estuviera en efecto antes. La asignación es incondicional a propósito. Una decodificación escrita como "si la opción lo pide, active el flag" permite que un AGGREGATE con código 4 situado dentro de un SUBTOTAL(109, ...) herede el ajuste externo de filas ocultas en lugar de limpiarlo. El boceto de abajo muestra la misma regla para quien escriba o revise su propio evaluador

// Illustrative decode for your own evaluator: TAggPolicy and
// 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 otro valor 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 en cero
end;

El lado de SUBTOTAL de las mismas reglas de filas ocultas, incluida la comparación entre filas filtradas y filas ocultas manualmente, está cubierto en el artículo sobre filas ocultas en SUBTOTAL y AGGREGATE

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

Porque la forma natural de pasar "saltar filas ocultas" hacia seis o siete bucles de recorrido de celdas es un campo del objeto calculadora, y una calculadora evalúa todas las fórmulas de una pasada de recálculo. Ese diseño se sostiene mientras todo lo que corre con el campo puesto pertenezca a la agregación que lo puso. Deja de sostenerse en un momento concreto: el fetch de la celda. Cuando el walker pide el valor de una celda y esa celda contiene una fórmula sin resultado cacheado todavía, el workbook compila y evalúa esa fórmula en el momento, sobre la misma calculadora, con los ajustes externos todavía puestos

Recorra el ejemplo inicial por un motor así. El AGGREGATE con código 7 activa el salto de filas ocultas y arranca en A1. A1 da 10, A2 está oculta y se salta, A3 es un error y se salta. Después el walker llega a A4, que no se calculó. El SUBTOTAL(9,A1:A2) de A4 corre, y una implementación de SUBTOTAL que solo activa el salto de filas ocultas para los códigos 101 a 111 (una forma perfectamente razonable de escribirlo) nunca lo desactiva para el código 9. Encuentra el ajuste ya activo, salta A2 y devuelve 10. El total externo queda 10 + 10 = 20. Nada en la fórmula de A4 menciona filas ocultas

El ajuste de agregados anidados se filtra en las dos direcciones. Con los códigos 0 a 3 está activo, y un walker genérico de rangos que lo respetara haría que un precedente como =SUM(B1:B3) descarte B2 cada vez que B2 contenga un SUBTOTAL. En la otra dirección, un SUBTOTAL que desactiva el salto de anidados al terminar, en lugar de restaurar el estado anterior, desactiva la política del AGGREGATE externo por 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 puso y equivocado para cada marco que lo hereda

Cortar la política en la frontera del fetch

HotXLS pone una frontera alrededor de cada punto donde AGGREGATE lee un valor que no calculó él mismo. Antes del fetch, los dos ajustes se guardan y se limpian; después, se restauran 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. La fórmula referenciada corre sin política alguna, que es lo que hace Excel. Las tres familias de walkers pasan por esa frontera: la familia de SUM (SUM, AVERAGE, COUNT, COUNTA, MAX, MIN), las reducciones de forma cerrada (PRODUCT, STDEV, VAR) y las formas que llevan un argumento k (MEDIAN, LARGE, SMALL, los percentiles y los cuartiles)

// Illustrative walker for your own evaluator. FPolicy, FetchCell,
// IsRowHidden e 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 es dueña de 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 del fetch
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);
    // ... aplique la política de errores a Code / V y acumule ...
  end;
end;

Los argumentos que no son rangos simples necesitan más que flags limpiados. Un argumento como A1:A4/(B1:B4-20) es un array calculado, y su forma de 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 como elemento de error una celda que falló en lugar de abortar, y aplica los operadores aritméticos y de comparación elemento por elemento. Un rango que abarca varias hojas, o uno con las esquinas 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 saltar. Tragarse 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 encuentra, sin cambios. Colapsar cada error 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 los siete strings de error (#NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A) de vuelta a sus propios códigos, y cada walker devuelve el primer código que registró. Una celda cuya fórmula nunca se calculó entrega su error como código de retorno del fetch en lugar de como valor cacheado; se propaga exactamente igual que una cacheada

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 salte los errores. Esa asimetría es cómo trata Excel a COUNT y COUNTA también fuera de AGGREGATE, y una regla genérica de "si hay error, propagar" se equivoca con las dos

Verificar las ocho opciones contra una hoja de cálculo

Las cuatro celdas iniciales forman una verificació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 contra valores calculados a mano. Los códigos 0, 1, 4 y 5 tienen que propagar el #DIV/0! de A3, porque ninguno salta 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, y es el caso que un motor sin la frontera de fetch reporta como 20. La verificación solo sirve si A4 está sin cachear 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 del grupo = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  ocultas salteadas, el error se propaga
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       ocultas, errores y anidadas salteadas
    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       20 significa que un ajuste se filtró a A4
    Sheet.Cells[10, 1].Formula := '=AGGREGATE(12,6,A1:A4)'; // 20     MEDIANA 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, corra cada número de función del 1 al 19 contra los ocho códigos

Cablear los callbacks cuando usted arma la calculadora por su cuenta

Los dos ajustes dependen de dos callbacks. TXLSCalculator.Create recibe un TXLSIsRowHidden y un TXLSIsSubtotalCell después del getter de valores, y ambos quedan en nil por defecto. Si falta cualquiera de los dos, la columna correspondiente de la tabla nunca se aplica, así que cada código de opciones incluye filas ocultas, agregaciones anidadas, o ambas. Los objetos workbook de XLSX y del 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 proveer ambos. Los workbooks reconocen una agregación anidada por una fórmula que empieza con SUBTOTAL(, AGGREGATE( o _xlfn.AGGREGATE(, con o sin el signo igual adelante, y una fuente personalizada debería usar la misma regla

// TMySheetSource and FormulaAt are your own types; the three method
// coinciden con TXLSGetValue, TXLSIsRowHidden y TXLSIsSubtotalCell
// (SheetIndex 1-based, Row y Col zero-based tal como los workbooks las pasan)
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 host 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 afuera 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 cacheados, y el mismo orden se mantiene bajo el recálculo incremental sobre el grafo de dependencias. Cuando un total sigue viéndose mal y el texto de la fórmula se ve bien, trazar la evaluación paso a paso muestra si un precedente corrió bajo demanda dentro del recorrido o si nunca se enganchó un callback

El decodificador de opciones, la frontera de fetch y los hooks de callbacks descritos acá vienen con el componente de hojas de cálculo HotXLS Delphi, que lee, escribe y recalcula libros XLS, XLSX y ODS desde código Delphi y C++Builder sin necesidad de tener Excel instalado