Si SUBTOTAL(109, ...) y SUBTOTAL(9, ...) devuelven el mismo número en un libro que contiene filas ocultas, uno de los dos está mal. HotXLS, el componente de hoja de cálculo Excel nativo para Delphi y C++Builder, se comportó exactamente así hasta la versión 2.197.0, porque su motor de cálculo no tenía forma de preguntarle a una hoja de trabajo si una fila dada estaba oculta
El síntoma casi nunca llega como un reporte de error 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 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 se entera de la visibilidad de las filas. HotXLS era un caso de libro de texto: 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 a través de la cadena de llamadas. El motor, por lo tanto, tenía una sola ruta de agregación, y ambas mitades de la tabla de números de función de SUBTOTAL resolvían hacia ella. Eso no es una clase de defecto 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 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 corrección aplicada 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. Contar los bucles en HotXLS arrojó seis de ellos una vez que se incluyó 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 versión 2.197.0 agregó un segundo. El motor obtuvo 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
// 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 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
// aggregate over Item.Child[2] .. Item.Child[ChildCount]
// every Exit path below is covered by the finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;La prueba Assigned es lo que mantiene el cambio compatible. HotXLS extendió el constructor del calculador con un tercer parámetro cuyo valor 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 filas ocultas. Nada sobre la forma de la API existente cambió
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 heredado responde desde TXLSRowInfoList.GetHidden, alcanzado mediante TXLSWorkbook.GetRowHidden. El lado OOXML responde desde TXLSXWorksheet.GetRowHidden, alcanzado mediante TXLSXWorkbook.GetCalcRowHidden. Ambos se conectan al calculador en el momento de la construcción, junto al callback de valor de celda que reflejan. Las convenciones de fila son donde este tipo de puente normalmente se equivoca, así que vale la pena declararlas explícitamente. El calculador le entrega al callback una fila con base 0, coincidiendo con las coordenadas que TXLSGetValue ya usa. La hoja de trabajo XLSX indexa su mapa de filas ocultas por número de fila con base 1, exactamente como Excel numera las filas, que es también 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 lo hace, porque TXLSRowInfoList ya tiene base 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 descartar 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 completamente 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 mantiene 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 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 dependes del recálculo incremental sobre el subgrafo sucio para mantener responsivos a 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 como lo hace SUBTOTAL, incluido el enrutamiento de varianza, desviación estándar, y producto a través de sus propios reductores. Queda una brecha documentada, y es mejor declararla aquí que descubrirla en producción: la semántica de ignorar SUBTOTAL anidado asociada con los códigos de opción bajos no está implementada en HotXLS. Detectar un SUBTOTAL anidado dentro de un rango referenciado requiere marcar el estado de recursión del evaluador para que una agregación interna pueda anunciarse a la externa, que es un cambio más grande 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 sobre los que agregan otras fórmulas SUBTOTAL. Si tu generador sí construye rangos de agregación superpuestos, no confíes en los códigos de opción bajos para deduplicarlos
La protección de aridad que se incluyó 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 temporal: poner la comprobación donde pueda escribirse una sola vez. Aproximadamente 280 cuerpos de función incorporados verificaban cada uno su propia cantidad de argumentos contra Item.ChildCount, lo que 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 excedente, y devolvía un número verosímil donde Excel devuelve #VALUE!. HotXLS ya almacenaba 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 versión 2.197.0 reenvió eso a través de una nueva propiedad TXLSFormula.FuncArgsCntByPtg y agregó una puerta al inicio 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 protección 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 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 muy pocos argumentos es una tarea aparte, porque cada uno de esos 280 cuerpos tiene su propia semántica de código de error y hay que revisarlos uno a la vez en lugar de asumirlos
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