Si SUBTOTAL(109, ...) y SUBTOTAL(9, ...) devuelven el mismo número en un libro que contiene filas ocultas, uno de los dos está mal. Es una de las trampas más fáciles de caer al construir un motor de cálculo de hojas de cálculo, y HotXLS, el componente nativo de hojas de cálculo Excel para Delphi y C++Builder, la evita dejando que su motor de cálculo le pregunte a la hoja de trabajo si una fila dada está oculta
El síntoma casi nunca llega como una pregunta sobre códigos de fórmula. Llega como un desajuste: un trabajo por lotes en el servidor calcula un total, un usuario abre el mismo archivo 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 lugares. La diferencia está completamente en lo que se le permitió ver al evaluador
¿Por qué SUBTOTAL 109 incluiría filas ocultas?
Porque en la mayoría de los diseños de motor, la capa que evalúa una fórmula nunca se entera de la visibilidad de las filas. El diseño obvio accede a los valores de celda mediante un único callback de valores, como el callback TXLSGetValue en 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 termina con una sola ruta de agregación hacia la que resuelven ambas mitades de la tabla de números de función de SUBTOTAL. Eso no es una clase 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 funciones 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 declaración deliberada sobre los datos ocultos, y un motor que colapsa la distinción anula esa declaración en silencio
A qué se mapean 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 luego despacha sobre la propia agregación. La mayor parte de la familia fluye a través del acumulador incremental ExcelSum, el que maneja SUM, COUNT, COUNTA, MIN, MAX, y AVERAGE. Cinco de ellas no pueden: STDEV, VAR, STDEVP, VARP, y PRODUCT necesitan un paso de forma cerrada sobre los datos, así que CalcSubtotalFunc enruta los códigos internos 12, 46, 193, 194, y 183 hacia un reductor separado, SubtotalReduceVariance. Esa división es lo primero que vale la pena mapear antes de tocar nada, porque dos rutas de agregación independientes significan dos bucles de recorrido de celdas independientes, y una verificación de filas ocultas agregada a solo uno de ellos produce el peor resultado posible: SUBTOTAL(109, ...) respeta el filtro mientras que SUBTOTAL(107, ...) sobre el mismo rango no lo hace. HotXLS tiene seis de ellos una vez que se incluye AGGREGATE, repartidos entre la evaluación de rangos, la recolección de rangos simple, y tres reductores separados
Por qué un campo temporal en lugar de seis firmas nuevas
Porque enhebrar un parámetro nuevo a través de seis funciones de recorrido de celdas, más todo lo que las llama, es un cambio amplio a una ruta de código caliente por el bien de un solo booleano. HotXLS ya tenía un precedente para la alternativa: un campo transitorio en el calculador, en el mismo espíritu que el campo temporal que GetRangeInfo usa para registrar cuándo una referencia 3D resolvió hacia un libro externo. La compuerta de filas ocultas es un segundo: un tipo de callback, TXLSIsRowHidden, declarado como una función de (SheetIndex, row) que devuelve Boolean, almacenada en FIsRowHidden, más un indicador transitorio FIgnoreHiddenRows. El indicador 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 omite una fila cuando está activado, agregando 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
// ... plegar 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 indicador 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 externa todavía está en la pila, y ese trabajo anidado no debe heredar ni destruir la puerta externa. Y la restauración vive en un bloque finally, porque CalcSubtotalFunc tiene varias salidas anticipadas para códigos de error; un indicador que quedara armado después de un retorno de error corrompería en silencio 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
// agregar sobre Item.Child[2] .. Item.Child[ChildCount]
// cada ruta de Exit de abajo está cubierta por el finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;La prueba 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 arma una calculadora con la llamada de dos argumentos compila sin cambios y obtiene el comportamiento de incluir las ocultas
De dónde viene realmente el bit de fila oculta
De la hoja de trabajo, a través de dos fuentes distintas, porque HotXLS lleva dos motores de libro. El lado BIFF legado responde desde TXLSRowInfoList.GetHidden, alcanzado a través de TXLSWorkbook.GetRowHidden. El lado OOXML responde desde TXLSXWorksheet.GetRowHidden, alcanzado a través de TXLSXWorkbook.GetCalcRowHidden. Ambos se cablean en la calculadora al momento de construirla, junto al callback de valores de celda que espejan. Las convenciones de filas son donde este tipo de puente normalmente se equivoca, así que valen enunciarse explícitamente. La calculadora le pasa al callback una fila base 0, coincidiendo con las coordenadas que TXLSGetValue ya usa. La hoja XLSX indexa su mapa de filas ocultas por número de fila base 1, exactamente como Excel numera filas, que también es lo que expone la propiedad pública RowHidden[ARow]. El puente XLSX por lo tanto suma uno antes de la búsqueda, y el puente BIFF no, porque TXLSRowInfoList ya es base 0. Ambos puentes tratan un índice de hoja o una fila fuera del rango válido como visible, así que una consulta fuera de rango degrada a la respuesta de incluir las ocultas en lugar de perder datos
Qué cambia para los libros filtrados
Este es el caso donde la política de filas ocultas importa más. Aplicar un AutoFilter en HotXLS a través de 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 dropdown de filtro. Esas filas son invisibles para el usuario, y como el motor de cálculo consulta el mismo estado de filas, un SUBTOTAL(109, ...) del lado 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 indica cuántas filas alimentaron el cálculo
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 mantiene en HotXLS. Una consecuencia merece una nota en la documentación que acompañe a sus 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 reporte 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 cubren juntos en el artículo sobre validación de datos, AutoFilter y tablas. Como ocultar filas no toca ninguna fórmula, tampoco ensucia el grafo de dependencias por sí solo, lo cual vale la pena saber si usted depende del recálculo incremental sobre el subgrafo sucio para mantener responsivos a 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 compuerta 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 como lo hace SUBTOTAL, incluido 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 sobre los que otras fórmulas SUBTOTAL agregan; si su generador construye rangos de agregación superpuestos, mantenga los totales internos fuera del rango externo en lugar de confiar en los códigos de opción para deduplicarlos
Una compuerta de aridad en el mismo despachador
El mismo despachador lleva una verificación de cantidad de argumentos, y la razón de diseño es la misma que está detrás del campo temporal: poner la verificación donde pueda escribirse una sola vez. Con unos 280 cuerpos de función incorporados, dejar que cada uno verifique su propia cantidad 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 excedente, y devuelve un número verosímil donde Excel devuelve #VALUE!. HotXLS almacena la aridad declarada de cada función incorporada 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 verifica en una sola compuerta al inicio 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 compuerta rechaza demasiados argumentos y deliberadamente no dice nada sobre muy pocos. Omitir un argumento opcional final es legal en Excel para VLOOKUP, SUBSTITUTE, y una larga lista de otras, así que una verificación simétrica rompería fórmulas correctas para atrapar las incorrectas. Los identificadores desconocidos se reportan como variádicos y se saltan la compuerta por completo, que es lo que mantiene a las funciones definidas por el usuario fuera de su camino; si usted registra sus 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 muy pocos argumentos 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 API de AutoFilter y visibilidad de filas que lo alimentan son parte del componente de hoja de cálculo HotXLS para Delphi, que se incluye con el código fuente completo para Delphi y C++Builder y no requiere instalación de Excel en la máquina que lo ejecuta