Si SUBTOTAL(109, ...) y SUBTOTAL(9, ...) devuelven el mismo número en un libro que contiene filas ocultas, uno de los dos está equivocado. Es una de las trampas más fáciles de pisar al construir un motor de cálculo de hojas de cálculo, y HotXLS, el componente nativo de hoja de cálculo Excel para Delphi y C++Builder, la evita dejando que su motor de cálculo le pregunte a la hoja si una fila dada está oculta
El síntoma rara vez llega como una pregunta sobre códigos de fórmula. Llega como una discrepancia: un job por lotes en el servidor calcula un total, un usuario abre el mismo fichero en Excel con un filtro aplicado, y los dos números difieren en lo que sea que sumaran las filas filtradas. Nadie sospecha de la función de agregación, porque la cadena de fórmula en la celda es idéntica en ambos sitios. La diferencia está enteramente en lo que se dejó ver al evaluador
¿Por qué incluiría SUBTOTAL 109 filas ocultas?
Porque en la mayoría de los diseños de motor la capa que evalúa una fórmula nunca llega a conocer la visibilidad de fila. El diseño obvio accede a los valores de celda mediante un único callback de valores, como el callback TXLSGetValue de lxCalc.pas, que responde con un valor para una tripleta (hoja, fila, columna) y nada más. La visibilidad es un atributo de presentación almacenado en el registro de fila, y si nada más viaja por la cadena de llamadas, el motor acaba con una única ruta de agregación hacia la que resuelven ambas mitades de la tabla de números de función de SUBTOTAL. Ese no es un fallo de la categoría de error de redondeo: la política de filas ocultas es toda la razón por la que existe la segunda mitad de la tabla. ECMA-376 Parte 1, publicado como ISO/IEC 29500-1, define SUBTOTAL en sus definiciones de función de fórmula (§18.17.7) con un primer argumento que selecciona tanto la agregación interna como la política de filas ocultas. Los códigos 1 a 11 mapean a AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR y VARP incluyendo valores en filas ocultas manualmente. Los códigos 101 a 111 seleccionan las mismas once agregaciones y las excluyen. Un usuario que escribe 109 en lugar de 9 está haciendo una afirmación deliberada sobre los datos ocultos, y un motor que colapsa la distinción anula silenciosamente esa afirmación
A qué corresponden los números de función dentro del motor
HotXLS resuelve el primer argumento de SUBTOTAL en CalcSubtotalFunc, que normaliza los códigos 101 a 111 hacia los mismos identificadores de función interna que los códigos 1 a 11 y después despacha sobre la agregación en sí. La mayor parte de la familia fluye a través del acumulador incremental ExcelSum, el que gestiona SUM, COUNT, COUNTA, MIN, MAX y AVERAGE. Cinco de ellas no pueden: STDEV, VAR, STDEVP, VARP y PRODUCT necesitan una pasada de forma cerrada sobre los datos, así que CalcSubtotalFunc dirige los códigos internos 12, 46, 193, 194 y 183 a un reductor aparte, SubtotalReduceVariance. Esa división es lo primero que merece la pena mapear antes de tocar nada, porque dos rutas de agregación independientes significan dos bucles de recorrido de celdas independientes, y una comprobación de filas ocultas añadida solo a una de ellas produce el peor resultado posible: SUBTOTAL(109, ...) respeta el filtro mientras que SUBTOTAL(107, ...) sobre el mismo rango no lo hace. HotXLS tiene seis una vez incluido AGGREGATE, repartidos entre la evaluación de rangos, la recolección plana de rangos, y tres reductores separados
¿Por qué un campo provisional en lugar de seis firmas nuevas?
Porque enhebrar un nuevo parámetro a través de seis funciones de recorrido de celdas, más todo lo que las llama, es un cambio amplio en una ruta de código caliente por el bien de un solo booleano. HotXLS tiene un precedente para la alternativa: un campo transitorio en la calculadora, en el mismo espíritu que el campo provisional que GetRangeInfo usa para registrar cuándo una referencia 3D resolvió hacia un libro externo. La puerta de filas ocultas es un segundo: un tipo de callback, TXLSIsRowHidden, declarado como una función de (SheetIndex, row) que devuelve Boolean, almacenado en FIsRowHidden, más un flag transitorio FIgnoreHiddenRows. El flag se arma al entrar en CalcSubtotalFunc cuando el código de función cae entre 101 y 111, y al entrar en CalcAggregateFunc para los códigos de opción de AGGREGATE que seleccionan la exclusión de filas ocultas. Cada bucle de recorrido de celdas entonces lo inspecciona y se salta una fila cuando está activado, al costo de una sola línea cada uno
// La forma repetida en los seis bucles de recorrido de celdas
for sh := s1 to s2 do
for rr := r1 to r2 do
begin
if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
Continue;
for cc := c1 to c2 do
begin
// ... acumule Cells[rr, cc] en el acumulador ...
end;
end;Dos detalles en el código de armado sostienen la corrección de todo el esquema. El flag se guarda y se restaura en lugar de simplemente activarse y desactivarse, porque un argumento de SUBTOTAL puede contener una expresión que ejecuta su propia evaluación mientras la agregación exterior sigue en la pila, y ese trabajo anidado no debe heredar ni destruir la puerta exterior. Y la restauración vive en un bloque finally, porque CalcSubtotalFunc tiene varias salidas tempranas para códigos de error; un flag que quedara armado tras un retorno de error corrompería silenciosamente la siguiente fórmula no relacionada en el orden de recálculo
prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
FIgnoreHiddenRows := True;
try
// agregue sobre Item.Child[2] .. Item.Child[ChildCount]
// toda ruta de Exit que sigue está cubierta por el finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;La comprobación Assigned es lo que mantiene compatible la API de la calculadora. El constructor de TXLSCalculator toma un tercer parámetro que por defecto es nil, así que el código que construye una calculadora con la llamada de dos argumentos compila sin cambios y obtiene el comportamiento de incluir ocultas
¿De dónde sale realmente el bit de fila oculta?
De la hoja de cálculo, a través de dos fuentes distintas, porque HotXLS lleva dos motores de libro. El lado BIFF heredado responde desde TXLSRowInfoList.GetHidden, al que se llega a través de TXLSWorkbook.GetRowHidden. El lado OOXML responde desde TXLSXWorksheet.GetRowHidden, al que se llega a través de TXLSXWorkbook.GetCalcRowHidden. Ambos están conectados a la calculadora en el momento de la construcción, junto con el callback de valor de celda al que reflejan. Las convenciones de fila son donde este tipo de puente normalmente sale mal, así que merece la pena exponerlas explícitamente. La calculadora entrega al callback una fila indexada desde 0, coincidiendo con las coordenadas que ya usa TXLSGetValue. La hoja XLSX indexa su mapa de filas ocultas por número de fila indexado desde 1, exactamente como Excel numera las filas, que es también lo que expone la propiedad pública RowHidden[ARow]. El puente XLSX por tanto suma uno antes de la búsqueda, y el puente BIFF no lo hace, porque TXLSRowInfoList ya está indexado desde 0. Ambos puentes tratan un índice de hoja o fila fuera del rango válido como visible, así que una consulta fuera de límites degrada hacia la respuesta de incluir ocultas en lugar de perder datos
Qué cambia para los libros filtrados
Este es el caso donde más importa la política de filas ocultas. Aplicar un AutoFilter en HotXLS mediante ApplyAutoFilter evalúa los criterios de columna y oculta cada fila de datos que no coincide, que es precisamente lo que hace Excel cuando un usuario hace clic en un desplegable de filtro. Esas filas son invisibles para el usuario, y como el motor de cálculo consulta el mismo estado de fila, un SUBTOTAL(109, ...) del lado del servidor reporta el total filtrado en lugar del sin filtrar
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
VisibleRows: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
Sheet := Book.Sheets[0];
Sheet.SetAutoFilter('A1:E500');
Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
VisibleRows := Sheet.ApplyAutoFilter; // oculta las filas que no coinciden
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// El valor de la celda coincide con lo que Excel muestra para el mismo filtro,
// y VisibleRows le dice cuántas filas alimentaron el total
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;El ocultamiento manual funciona de la misma manera, ya que RowHidden[ARow] := True es el mismo estado que escribe el filtro. Esa equivalencia es deliberada en Excel y también se cumple en HotXLS. Una consecuencia merece una nota en cualquier documentación que acompañe a tus libros generados: un total calculado con el código 109 es un número dependiente de la vista, así que un destinatario que borre el filtro lo cambia. Cuando un informe debe declarar una cifra fija sin importar lo que el lector haga con la vista, el código 9 es la elección correcta y siempre lo fue. Los filtros, la validación y las tablas se tratan juntos en el artículo sobre validación de datos, AutoFilter y tablas. Como ocultar filas no toca ninguna fórmula, tampoco ensucia por sí solo el grafo de dependencias, lo cual conviene saber si dependes del recálculo incremental sobre el subgrafo sucio para mantener responsivos los libros grandes
Códigos de opción de AGGREGATE
AGGREGATE es SUBTOTAL con un segundo argumento de política, y HotXLS lo maneja en CalcAggregateFunc. El argumento de opción codifica interruptores independientes: si se omiten las llamadas anidadas a SUBTOTAL y AGGREGATE dentro del rango, si se omiten los valores en filas ocultas, y si los valores de error se suprimen en lugar de propagarse. HotXLS arma la puerta compartida de filas ocultas para los códigos de opción 2, 3, 6 y 7, y suprime los valores de error para los códigos de opción 4 a 7. El argumento de número de función entonces selecciona la agregación exactamente igual que SUBTOTAL, incluyendo el enrutamiento de varianza, desviación estándar y producto a través de sus propios reductores. Los libros reales casi siempre colocan las fórmulas SUBTOTAL fuera de los rangos que otras fórmulas SUBTOTAL agregan; si tu generador construye rangos de agregación solapados, mantén los totales internos fuera del rango externo en lugar de confiar en que los códigos de opción los dedupliquen
Una puerta de aridad en el mismo despachador
El mismo despachador lleva una comprobación de conteo de argumentos, y la razón de diseño es la misma que hay detrás del campo provisional: poner la comprobación donde pueda escribirse una sola vez. Con unos 280 cuerpos de función integrada, dejar que cada uno verifique su propio número de argumentos contra Item.ChildCount no deja ningún límite consistente para el caso de demasiados argumentos, y la trampa es fácil de perder: una llamada como =SIN(1,2) llega a un cuerpo de función que examina su primer argumento, ignora el sobrante, y devuelve un número plausible donde Excel devuelve #VALUE!. HotXLS almacena la aridad declarada de cada función integrada en su registro de funciones, expuesta como THashFunc.ArgsCnt con -1 marcando una función variádica como SUM, IF o CONCAT, la reenvía a través de la propiedad TXLSFormula.FuncArgsCntByPtg, y la comprueba en una única puerta al principio de GetValueItemFunc, el despachador principal
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] es el nodo de la función
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) produce #VALUE!
Exit;
end;
end;La puerta rechaza demasiados argumentos y deliberadamente no dice nada sobre demasiado pocos. Omitir un argumento opcional final es legal en Excel para VLOOKUP, SUBSTITUTE, y una larga lista de otras, así que una comprobación simétrica rompería fórmulas correctas para atrapar las incorrectas. Los identificadores desconocidos se reportan como variádicos y se saltan la puerta por completo, que es lo que mantiene a las funciones definidas por el usuario fuera de su camino; si registras tus propias funciones, el comportamiento descrito en la guía del motor de fórmulas y las funciones personalizadas no se ve afectado. El caso de demasiado pocos se queda en cada cuerpo de función, porque cada uno de esos cuerpos tiene su propia semántica de código de error
El motor de cálculo descrito aquí, ambas fachadas de libro, y las APIs de AutoFilter y visibilidad de fila que lo alimentan forman parte del componente de hoja de cálculo Delphi HotXLS, que se distribuye con el código fuente completo para Delphi y C++Builder y no requiere que Excel esté instalado en la máquina que lo ejecuta