Si SUBTOTAL(109, ...) y SUBTOTAL(9, ...) devuelven el mismo número en un libro que contiene filas ocultas, uno de los dos está equivocado. HotXLS, el componente nativo de hoja de cálculo Excel para Delphi y C++Builder, se comportaba exactamente así hasta la versión 2.197.0, porque su motor de cálculo no tenía forma de preguntarle a una hoja si una fila dada estaba oculta
El síntoma rara vez llega como un informe de bug 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 el evaluador tenía permitido ver
¿Por qué SUBTOTAL 109 incluye 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. HotXLS era un caso de manual: el motor de cálculo en lxCalc.pas accedía a los valores de celda mediante un único callback TXLSGetValue 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 ninguna parte de ese registro viajaba por la cadena de llamadas. El motor por tanto tenía una única ruta de agregación, y ambas mitades de la tabla de números de función de SUBTOTAL resolvían hacia ella. Ese no es un defecto de la categoría de error de redondeo: 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 los 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 corrección aplicada 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. Contar los bucles en HotXLS arrojó 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 ya tenía 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 versión 2.197.0 añadió un segundo. El motor ganó un tipo de callback, TXLSIsRowHidden, declarado como una función de (SheetIndex, row) que devuelve Boolean, almacenada 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, añadiendo una sola línea cada uno
// The shape repeated in all six cell-walk loops
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
// ... fold Cells[rr, cc] into the accumulator ...
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
// aggregate over Item.Child[2] .. Item.Child[ChildCount]
// every Exit path below is covered by the finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;La comprobación Assigned es lo que mantiene el cambio compatible. HotXLS amplió el constructor de la calculadora con un tercer parámetro que por defecto es nil, así que cualquier código que construya un TXLSCalculator con la antigua llamada de dos argumentos sigue compilando y sigue obteniendo el comportamiento heredado de incluir ocultas. Nada de la forma de la API existente cambió
¿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 vieja respuesta de incluir ocultas en lugar de perder datos
Qué cambia para los libros filtrados
Este es el caso que genera los tickets de soporte. 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. Antes de v2.197.0 esas filas ocultas eran invisibles para el usuario y totalmente visibles para el motor de cálculo, así que un SUBTOTAL(109, ...) del lado del servidor reportaba el total sin filtrar. Ahora la misma llamada reporta el filtrado
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; // hides the non-matching rows
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// The cell value now agrees with what Excel shows for the same filter,
// and VisibleRows tells you how many rows fed into it
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 ahora 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 y un límite que sigue abierto
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. Queda una brecha documentada, y es mejor exponerla aquí que descubrirla en producción: la semántica de ignorar-SUBTOTAL-anidado asociada a los códigos de opción bajos no está implementada en HotXLS. Detectar un SUBTOTAL anidado dentro de un rango referenciado exige marcar el estado de recursión del evaluador para que una agregación interna pueda anunciarse a la exterior, lo cual es un cambio mayor que la puerta de filas ocultas. En la práctica la exposición es pequeña, porque los libros reales casi siempre colocan las fórmulas SUBTOTAL fuera de los rangos que otras fórmulas SUBTOTAL agregan. Si tu generador sí construye rangos de agregación solapados, no confíes en los códigos de opción bajos para deduplicarlos
La comprobación de aridad que se publicó junto con esto
La versión 2.197.0 también cerró una brecha de validación en el mismo despachador, y la razón de diseño es la misma que motivó el campo provisional: poner la comprobación donde pueda escribirse una sola vez. Aproximadamente 280 cuerpos de función integrada verificaban cada uno su propio número de argumentos contra Item.ChildCount, lo cual no dejaba ningún límite consistente para el caso de demasiados argumentos. Una llamada como =SIN(1,2) llegaba a un cuerpo de función que examinaba su primer argumento, ignoraba el sobrante, y devolvía un número plausible donde Excel devuelve #VALUE!. HotXLS ya almacenaba 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 versión 2.197.0 reenvió eso a través de una nueva propiedad TXLSFormula.FuncArgsCntByPtg y añadió una puerta en lo alto de GetValueItemFunc, el despachador principal
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] is the function node
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) now yields #VALUE!
Exit;
end;
end;La comprobación 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 habría roto 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. Centralizar el caso de demasiado pocos es un trabajo aparte, porque cada uno de esos 280 cuerpos tiene su propia semántica de código de error y hay que revisarlos uno a uno en lugar de asumirlos
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