Se SUBTOTAL(109, ...) e SUBTOTAL(9, ...) retornam o mesmo número num workbook que contém linhas ocultas, um dos dois está errado. É uma das armadilhas mais fáceis de cair ao construir um engine de cálculo de planilhas, e o HotXLS, o componente nativo de planilhas Excel para Delphi e C++Builder, a evita deixando o engine de cálculo dele perguntar à worksheet se uma dada linha está oculta
O sintoma raramente chega como uma pergunta sobre códigos de fórmula. Ele chega como um mismatch: um job em lote no servidor calcula um total, um usuário abre o mesmo arquivo no Excel com um filtro aplicado, e os dois números diferem pelo valor que as linhas filtradas por acaso somam. Ninguém desconfia da função de agregação, porque a string da fórmula na célula é idêntica nos dois lugares. A diferença está inteiramente no que o evaluator tinha permissão de ver
Por que o SUBTOTAL 109 incluiria linhas ocultas?
Porque na maioria dos designs de engine a camada que avalia uma fórmula nunca fica sabendo da visibilidade de linhas. O design óbvio alcança valores de célula por meio de um único callback de valor, como o callback TXLSGetValue no lxCalc.pas, que responde com um valor para uma tripla (sheet, linha, coluna) e nada mais. Visibilidade é um atributo de apresentação armazenado no record da linha, e se nada mais desce pela cadeia de chamadas, o engine acaba com um caminho de agregação para o qual as duas metades da tabela de function numbers do SUBTOTAL resolvem. Não é uma classe de erro de arredondamento: a policy de linhas ocultas é a razão inteira de a segunda metade da tabela existir. A Parte 1 do ECMA-376, publicada como ISO/IEC 29500-1, define SUBTOTAL nas definições de funções de fórmula dele (§18.17.7) com um primeiro argumento que seleciona tanto a agregação interna quanto a policy de linhas ocultas. Os códigos 1 a 11 mapeiam para AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR e VARP incluindo valores em linhas ocultas manualmente. Os códigos 101 a 111 selecionam as mesmas onze agregações e as excluem. Um usuário que digita 109 em vez de 9 está fazendo uma declaração deliberada sobre dados ocultos, e um engine que colapsa a distinção silenciosamente anula essa declaração
Para que os function numbers mapeiam dentro do engine
O HotXLS resolve o primeiro argumento do SUBTOTAL no CalcSubtotalFunc, que normaliza os códigos 101 a 111 para os mesmos identificadores de função interna dos códigos 1 a 11 e então despacha pela agregação em si. A maior parte da família flui pelo acumulador incremental ExcelSum, aquele que cuida de SUM, COUNT, COUNTA, MIN, MAX e AVERAGE. Cinco não podem: STDEV, VAR, STDEVP, VARP e PRODUCT precisam de uma passada de forma fechada sobre os dados, então o CalcSubtotalFunc roteia os códigos internos 12, 46, 193, 194 e 183 para um reducer separado, o SubtotalReduceVariance. Essa divisão é a primeira coisa que vale mapear antes de mexer em qualquer coisa, porque dois caminhos de agregação independentes significam dois loops de caminhada de células independentes, e uma checagem de linha oculta adicionada a só um deles produz o pior resultado possível: SUBTOTAL(109, ...) respeita o filtro enquanto SUBTOTAL(107, ...) no mesmo range não. O HotXLS tem seis deles uma vez que o AGGREGATE entra, espalhados por avaliação de range, coleta plain de range e três reducers separados
Por que um campo de rascunho em vez de seis assinaturas novas?
Porque enfiar um parâmetro novo por seis funções de caminhada de células, mais tudo que as chama, é uma mudança ampla num caminho de código quente por causa de um boolean. O HotXLS tem um precedente para a alternativa: um campo transitório na calculadora, no mesmo espírito do campo de rascunho que o GetRangeInfo usa para registrar quando uma referência 3D resolveu para um workbook externo. O gate de linhas ocultas é um segundo: um tipo de callback, TXLSIsRowHidden, declarado como função de (SheetIndex, linha) retornando Boolean, guardado em FIsRowHidden, mais um flag transitório FIgnoreHiddenRows. O flag é armado na entrada do CalcSubtotalFunc quando o código de função cai em 101 a 111, e na entrada do CalcAggregateFunc para os códigos de opção do AGGREGATE que selecionam exclusão de linhas ocultas. Todo loop de caminhada de células então o inspeciona e pula uma linha quando ele está definido, ao custo de uma linha de código cada
// A forma repetida em todos os seis loops de caminhada de células
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
// ... acumula Cells[rr, cc] no acumulador ...
end;
end;Dois detalhes no código de armar carregam a correção do esquema inteiro. O flag é salvo e restaurado em vez de simplesmente definido e limpo, porque um argumento do SUBTOTAL pode conter uma expressão que roda a própria avaliação dela enquanto a agregação externa ainda está na pilha, e esse trabalho aninhado não deve herdar nem destruir o gate externo. E o restore mora num bloco finally, porque o CalcSubtotalFunc tem várias saídas antecipadas para códigos de erro; um flag deixado armado depois de um retorno de erro corromperia silenciosamente a próxima fórmula não relacionada na ordem de recálculo
prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
FIgnoreHiddenRows := True;
try
// agrega sobre Item.Child[2] .. Item.Child[ChildCount]
// todo caminho de Exit abaixo é coberto pelo finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;O teste Assigned é o que mantém a API da calculadora compatível. O constructor de TXLSCalculator recebe um terceiro parâmetro com default nil, então código que monta uma calculadora com a chamada de dois argumentos compila sem mudança e recebe o comportamento de incluir ocultas
De onde o bit de linha oculta de fato vem?
Da worksheet, por duas fontes diferentes, porque o HotXLS carrega dois engines de workbook. O lado BIFF legado responde por TXLSRowInfoList.GetHidden, alcançado por TXLSWorkbook.GetRowHidden. O lado OOXML responde por TXLSXWorksheet.GetRowHidden, alcançado por TXLSXWorkbook.GetCalcRowHidden. Ambos são ligados à calculadora na construção, junto com o callback de valor de célula que espelham. As convenções de linha são onde esse tipo de ponte normalmente erra, então valem ser enunciadas explicitamente. A calculadora entrega ao callback uma linha 0-based, batendo com as coordenadas que o TXLSGetValue já usa. A worksheet XLSX indexa o map de row-hidden dela por número de linha 1-based, exatamente como o Excel numera linhas, que também é o que a propriedade pública RowHidden[ARow] expõe. A ponte XLSX portanto soma um antes do lookup, e a ponte BIFF não, porque TXLSRowInfoList já é 0-based. Ambas as pontes tratam índice de sheet ou linha fora do intervalo válido como visível, então uma consulta fora dos limites degrada para a resposta de incluir ocultas em vez de descartar dados
O que muda para workbooks filtrados
Este é o caso em que a policy de linhas ocultas mais importa. Aplicar um AutoFilter no HotXLS por meio de ApplyAutoFilter avalia os critérios de coluna e oculta toda linha de dados que não casa, que é precisamente o que o Excel faz quando um usuário clica num dropdown de filtro. Essas linhas são invisíveis para o usuário, e como o engine de cálculo consulta o mesmo estado de linhas, um SUBTOTAL(109, ...) no lado do servidor reporta o total filtrado em vez do não 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; // oculta as linhas que não casam
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// O valor da célula concorda com o que o Excel mostra para o mesmo filtro,
// e VisibleRows diz quantas linhas alimentaram o cálculo
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;Ocultar manualmente funciona do mesmo jeito, já que RowHidden[ARow] := True é o mesmo estado que o filtro escreve. Essa equivalência é deliberada no Excel e vale no HotXLS também. Uma consequência merece uma nota em qualquer documentação que acompanhe seus workbooks gerados: um total calculado com o código 109 é um número dependente de view, então um destinatário que limpar o filtro o muda. Quando um relatório precisa declarar um número fixo independentemente do que o leitor fizer com a view, o código 9 é a escolha correta e sempre foi. Filtros, validação e tabelas estão cobertos juntos no artigo sobre data validation, AutoFilter e tables. Como ocultar linhas não toca fórmula nenhuma, também não suja o grafo de dependências por conta própria, o que vale saber se você depende do recálculo incremental sobre o subgrafo dirty para manter workbooks grandes responsivos
Códigos de opção do AGGREGATE
AGGREGATE é SUBTOTAL com um segundo argumento de policy, e o HotXLS o trata no CalcAggregateFunc. O argumento de opções codifica switches independentes: se chamadas aninhadas de SUBTOTAL e AGGREGATE dentro do range são puladas, se valores em linhas ocultas são pulados, e se valores de erro são suprimidos em vez de propagados. O HotXLS arma o gate compartilhado de linhas ocultas para os códigos de opção 2, 3, 6 e 7, e suprime valores de erro para os códigos de opção 4 a 7. O argumento de function number então seleciona a agregação exatamente como o SUBTOTAL faz, incluindo o roteamento de variância, desvio padrão e produto pelos reducers próprios deles. Workbooks reais quase sempre põem fórmulas SUBTOTAL fora dos ranges sobre os quais outras fórmulas SUBTOTAL agregam; se o seu gerador constrói ranges de agregação sobrepostos, mantenha os totais internos fora do range externo em vez de depender dos códigos de opção para deduplicá-los
Um guard de aridade no mesmo dispatcher
O mesmo dispatcher carrega uma checagem de contagem de argumentos, e a razão de design é a mesma por trás do campo de rascunho: pôr a checagem onde ela pode ser escrita uma vez. Com cerca de 280 corpos de função embutidos, deixar cada um verificar a própria contagem de argumentos contra Item.ChildCount não dá fronteira consistente para o caso de argumentos demais, e a armadilha é fácil de perder: uma chamada como =SIN(1,2) alcança um corpo de função que examina o primeiro argumento, ignora o excedente e retorna um número plausível onde o Excel retorna #VALUE!. O HotXLS guarda a aridade declarada de cada embutida no registry de funções dele, exposto como THashFunc.ArgsCnt com -1 marcando função variádica como SUM, IF ou CONCAT, repassa pela propriedade TXLSFormula.FuncArgsCntByPtg, e checa num único gate no topo do GetValueItemFunc, o dispatcher principal
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] é o nó de função
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) produz #VALUE!
Exit;
end;
end;O guard rejeita argumentos demais e deliberadamente não diz nada sobre argumentos de menos. Omitir um argumento opcional no fim é legal no Excel para VLOOKUP, SUBSTITUTE e uma lista longa de outros, então uma checagem simétrica quebraria fórmulas corretas para pegar as incorretas. Identificadores desconhecidos reportam como variádicos e pulam o gate inteiro, que é o que mantém user-defined functions fora do caminho dele; se você registra as próprias funções, o comportamento descrito no guia do engine de fórmulas e custom functions não é afetado. O caso de argumentos de menos fica com cada corpo de função, porque cada um desses corpos tem a própria semântica de código de erro
O engine de cálculo descrito aqui, ambas as fachadas de workbook, e as APIs de AutoFilter e visibilidade de linhas que o alimentam fazem parte do componente de planilhas HotXLS para Delphi, que vem com source completo para Delphi e C++Builder e não exige instalação do Excel na máquina que o roda