Se SUBTOTAL(109, ...) e SUBTOTAL(9, ...) retornam o mesmo número em uma planilha que contém linhas ocultas, um dos dois está errado. O HotXLS, o componente de planilha Excel nativo para Delphi e C++Builder, se comportava exatamente assim até a versão 2.197.0, porque seu motor de cálculo não tinha como perguntar a uma worksheet se uma dada linha estava oculta
O sintoma raramente chega como um relatório de bug sobre códigos de fórmula. Ele chega como uma incompatibilidade: 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 que quer que as linhas filtradas somassem. Ninguém suspeita da função de agregação, porque a string de fórmula na célula é idêntica nos dois lugares. A diferença está inteiramente no que o avaliador tinha permissão para ver
Por que o SUBTOTAL 109 inclui linhas ocultas?
Porque na maioria dos designs de motor a camada que avalia uma fórmula nunca fica sabendo sobre a visibilidade de linha. O HotXLS era um caso de livro-texto: o motor de cálculo em lxCalc.pas alcançava valores de célula através de um único callback TXLSGetValue que responde com um valor para uma tripla (planilha, linha, coluna) e nada mais. Visibilidade é um atributo de apresentação armazenado no registro de linha, e nenhuma parte desse registro viajava pela cadeia de chamada. O motor, portanto, tinha um único caminho de agregação, e as duas metades da tabela de números de função do SUBTOTAL resolviam para ele. Isso não é uma classe de defeito de erro de arredondamento: é toda a razão pela qual a segunda metade da tabela existe. A ECMA-376 Parte 1, publicada como ISO/IEC 29500-1, define SUBTOTAL em suas definições de funções de fórmula (§18.17.7) com um primeiro argumento que seleciona tanto a agregação interna quanto a política de linha oculta. 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 afirmação deliberada sobre dados ocultos, e um motor que colapsa a distinção silenciosamente anula essa afirmação
Para o que os números de função mapeiam dentro do motor
O HotXLS resolve o primeiro argumento do SUBTOTAL em CalcSubtotalFunc, que normaliza os códigos 101 a 111 para baixo, aos mesmos identificadores de função interna que os códigos 1 a 11, e então despacha na própria agregação. A maior parte da família flui através do acumulador incremental ExcelSum, o que trata SUM, COUNT, COUNTA, MIN, MAX, e AVERAGE. Cinco deles não podem: STDEV, VAR, STDEVP, VARP, e PRODUCT precisam de uma passagem em forma fechada sobre os dados, então CalcSubtotalFunc roteia os códigos internos 12, 46, 193, 194, e 183 para um redutor separado, SubtotalReduceVariance. Essa divisão é a primeira coisa que vale a pena mapear antes de tocar em qualquer coisa, porque dois caminhos de agregação independentes significam dois loops de varredura de célula independentes, e uma correção aplicada a apenas um deles produz o pior resultado possível: SUBTOTAL(109, ...) respeita o filtro enquanto SUBTOTAL(107, ...) no mesmo intervalo não. Contar os loops no HotXLS revelou seis deles uma vez que o AGGREGATE foi incluído, espalhados entre avaliação de intervalo, coleta de intervalo simples, e três redutores separados
Por que um campo de rascunho em vez de seis novas assinaturas?
Porque encadear um novo parâmetro através de seis funções de varredura de célula, mais tudo que as chama, é uma mudança ampla em um caminho de código quente por causa de um único booleano. O HotXLS já tinha um precedente para a alternativa: um campo transitório na calculadora, no mesmo espírito do campo de rascunho que GetRangeInfo usa para registrar quando uma referência 3D resolveu em um workbook externo. A versão 2.197.0 adicionou um segundo. O motor ganhou um tipo de callback, TXLSIsRowHidden, declarado como uma função de (SheetIndex, row) retornando Boolean, armazenado em FIsRowHidden, mais uma flag transitória FIgnoreHiddenRows. A flag é armada na entrada de CalcSubtotalFunc quando o código de função cai entre 101 e 111, e na entrada de CalcAggregateFunc para os códigos de opção do AGGREGATE que selecionam exclusão de linha oculta. Todo loop de varredura de célula então a inspeciona e pula uma linha quando ela está setada, adicionando uma única linha cada
// 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;Dois detalhes no código de armamento carregam a correção do esquema inteiro. A flag é salva e restaurada em vez de simplesmente setada e limpa, porque um argumento de SUBTOTAL pode conter uma expressão que roda sua própria avaliação enquanto a agregação externa ainda está na pilha, e esse trabalho aninhado não pode herdar nem destruir o portão externo. E a restauração vive em um bloco finally, porque CalcSubtotalFunc tem várias saídas antecipadas para códigos de erro; uma flag deixada armada 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
// aggregate over Item.Child[2] .. Item.Child[ChildCount]
// every Exit path below is covered by the finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;O teste Assigned é o que mantém a mudança compatível. O HotXLS estendeu o construtor da calculadora com um terceiro parâmetro com padrão nil, então qualquer código que constrói um TXLSCalculator com a chamada antiga de dois argumentos ainda compila e ainda recebe o comportamento legado de incluir ocultas. Nada na forma da API existente mudou
De onde vem o bit de linha oculta de fato?
Da worksheet, através de duas fontes diferentes, porque o HotXLS carrega dois motores de workbook. O lado BIFF legado responde a partir de TXLSRowInfoList.GetHidden, alcançado através de TXLSWorkbook.GetRowHidden. O lado OOXML responde a partir de TXLSXWorksheet.GetRowHidden, alcançado através de TXLSXWorkbook.GetCalcRowHidden. Ambos são conectados à calculadora no momento da construção, junto com o callback de valor de célula que eles espelham. As convenções de linha são onde esse tipo de ponte normalmente dá errado, então vale a pena declará-las explicitamente. A calculadora entrega ao callback uma linha 0-based, correspondendo às coordenadas que TXLSGetValue já usa. A worksheet XLSX indexa seu mapa de linha-oculta por número de linha 1-based, exatamente como o Excel numera linhas, o que também é o que a propriedade pública RowHidden[ARow] expõe. A ponte XLSX, portanto, adiciona um antes da busca, e a ponte BIFF não, porque TXLSRowInfoList já é 0-based. Ambas as pontes tratam uma planilha ou linha fora do intervalo válido como visível, então uma consulta fora dos limites degrada para a resposta antiga de incluir ocultas em vez de descartar dados
O que muda para workbooks filtrados
Este é o caso que gera os tickets de suporte. Aplicar um AutoFilter no HotXLS através de ApplyAutoFilter avalia o critério da coluna e oculta toda linha de dado que não corresponde, o que é precisamente o que o Excel faz quando um usuário clica em um dropdown de filtro. Antes da v2.197.0 essas linhas ocultas eram invisíveis para o usuário e totalmente visíveis para o motor de cálculo, então um SUBTOTAL(109, ...) no lado do servidor reportava o total não filtrado. Agora a mesma chamada reporta 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; // 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;Ocultar manualmente funciona da mesma forma, já que RowHidden[ARow] := True é o mesmo estado que o filtro grava. Essa equivalência é deliberada no Excel e agora se mantém 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 visualização, então um destinatário que limpa o filtro o muda. Quando um relatório precisa declarar uma cifra fixa independentemente do que o leitor faz com a visualização, o código 9 é a escolha correta e sempre foi. Filtros, validação e tabelas são cobertos juntos no artigo sobre validação de dados, AutoFilter e tabelas. Como ocultar linhas não toca em nenhuma fórmula, isso também não suja o grafo de dependência por si só, o que vale a pena saber se você depende do recálculo incremental sobre o subgrafo sujo para manter workbooks grandes responsivos
Códigos de opção do AGGREGATE e um limite que ainda está em aberto
AGGREGATE é SUBTOTAL com um segundo argumento de política, e o HotXLS o trata em CalcAggregateFunc. O argumento de opção codifica switches independentes: se chamadas SUBTOTAL e AGGREGATE aninhadas dentro do intervalo 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 portão compartilhado de linha oculta 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 número de função então seleciona a agregação exatamente como o SUBTOTAL faz, incluindo o roteamento de variância, desvio padrão, e produto através de seus próprios redutores. Uma lacuna documentada permanece, e é melhor declará-la aqui do que descobri-la em produção: a semântica de ignorar-SUBTOTAL-aninhado associada aos códigos de opção baixos não está implementada no HotXLS. Detectar um SUBTOTAL aninhado dentro de um intervalo referenciado exige marcar o estado de recursão do avaliador para que uma agregação interna possa se anunciar à externa, o que é uma mudança maior que o portão de linha oculta. Na prática a exposição é pequena, porque workbooks reais quase sempre colocam fórmulas SUBTOTAL fora dos intervalos que outras fórmulas SUBTOTAL agregam. Se seu gerador constrói intervalos de agregação sobrepostos, não confie nos códigos de opção baixos para deduplicá-los
A proteção de aridade que veio junto
A versão 2.197.0 também fechou uma lacuna de validação no mesmo dispatcher, e a razão de design é a mesma que motivou o campo de rascunho: colocar a checagem onde ela pode ser escrita uma única vez. Aproximadamente 280 corpos de função embutida cada um verificava sua própria contagem de argumentos contra Item.ChildCount, o que não deixava nenhum limite consistente para o caso de argumentos demais. Uma chamada como =SIN(1,2) alcançava um corpo de função que examinava seu primeiro argumento, ignorava o excedente, e retornava um número plausível onde o Excel retorna #VALUE!. O HotXLS já armazenava a aridade declarada de todo built-in em seu registro de funções, exposto como THashFunc.ArgsCnt com -1 marcando uma função variádica como SUM, IF, ou CONCAT. A versão 2.197.0 encaminhou isso através de uma nova propriedade TXLSFormula.FuncArgsCntByPtg e adicionou um portão no topo de GetValueItemFunc, o dispatcher 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;A proteção rejeita argumentos demais e deliberadamente não diz nada sobre argumentos de menos. Omitir um argumento opcional final é legal no Excel para VLOOKUP, SUBSTITUTE, e uma longa lista de outros, então uma checagem simétrica teria quebrado fórmulas corretas para pegar as incorretas. Identificadores desconhecidos são reportados como variádicos e pulam o portão inteiramente, o que é o que mantém funções definidas pelo usuário fora do seu caminho; se você registra suas próprias funções, o comportamento descrito no guia sobre o motor de fórmulas e funções customizadas não é afetado. Centralizar o caso de argumentos-de-menos é um trabalho separado, porque cada um desses 280 corpos tem sua própria semântica de código de erro e eles precisam ser revisados um de cada vez em vez de assumidos
O motor de cálculo descrito aqui, ambas as fachadas de workbook, e as APIs de AutoFilter e visibilidade de linha que o alimentam fazem parte do componente de planilha HotXLS para Delphi, que vem com código-fonte completo para Delphi e C++Builder e não exige instalação do Excel na máquina que o executa