Matriz de opções do AGGREGATE e fuga de gates no HotXLS

O HotXLS, o componente nativo de folhas de cálculo Excel para Delphi e C++Builder, lançou duas correções relacionadas do AGGREGATE em setembro de 2026. A versão 2.382.0 corrigiu o argumento de opções para que os códigos 1/3/5/7 ignorem linhas ocultas, 2/3/6/7 ignorem erros, e 0 a 3 ignorem células SUBTOTAL e AGGREGATE aninhadas, exatamente como a Microsoft documenta. A versão 2.382.3 impediu depois que esses flags de seleção se infiltrassem na avaliação das próprias células que a função referencia. O primeiro defeito é embaraçoso da forma como os bugs de transcrição de tabelas são sempre embaraçosos: as posições dos bits estavam trocadas, pelo que todas as fórmulas que usassem um código de opções diferente de zero recebiam uma política que o seu autor não pediu. O segundo é mais interessante, porque é uma forma que vai encontrar em qualquer avaliador que use um campo transitório para passar contexto a uma caminhada recursiva. Uma agregação exterior arma um flag, percorre um intervalo, e puxa uma célula cuja fórmula ainda não foi calculada. Essa fórmula corre no mesmo calculator, vê o mesmo flag armado, e agrega silenciosamente as linhas erradas, produzindo um número que está errado numa quantidade que ninguém consegue explicar só pelo texto da fórmula

O que selecionam afinal as opções 0 a 7 do AGGREGATE?

O argumento de opções do AGGREGATE é uma matriz de três bits, e os três bits são independentes. O bit 0 (valor 1) significa ignorar linhas ocultas, o bit 1 (valor 2) significa ignorar valores de erro, e o bit 2 (valor 4) significa parar de ignorar células SUBTOTAL e AGGREGATE aninhadas, porque saltá-las é o predefinido para os códigos baixos. Há duas coisas nisto fáceis de trocar. O bit das linhas ocultas é o bit baixo, não o do meio, pelo que AGGREGATE(9,1,...) é a forma de total filtrado e AGGREGATE(9,2,...) é a tolerante a erros. E a política para agregações aninhadas está invertida em relação às outras duas: só os códigos 4 a 7 tratam uma célula cuja própria fórmula é um SUBTOTAL ou AGGREGATE como um valor comum. A ECMA-376 Parte 1 §18.17.7 define o SUBTOTAL com a mesma divisão incluir-ou-excluir linhas ocultas entre os códigos 1-11 e 101-111, e o AGGREGATE, guardado nos ficheiros OOXML sob o prefixo _xlfn., generaliza essa divisão no argumento de opções, pelo que a tabela que a Microsoft publica para a função AGGREGATE é o contrato que um motor tem de cumprir, e não uma conveniência

Opção	Linhas ocultas	Valores de erro	SUBTOTAL / AGGREGATE aninhados
0	incluídas	propagados	ignorados
1	ignoradas	propagados	ignorados
2	incluídas	ignorados	ignorados
3	ignoradas	ignorados	ignorados
4	incluídas	propagados	incluídos
5	ignoradas	propagados	incluídos
6	incluídas	ignorados	incluídos
7	ignoradas	ignorados	incluídos

Porque é que o HotXLS tinha as opções do AGGREGATE ao contrário?

Porque o TXLSCalculator.CalcAggregateFunc original foi escrito a partir de uma paráfrase da tabela em vez da tabela. Calculava ignoreErrors := (optCode >= 4) and (optCode <= 7) e armava o gate das linhas ocultas para os códigos 2, 3, 6 e 7, enquanto a política para agregações aninhadas não estava implementada de todo. O artigo anterior sobre linhas ocultas no SUBTOTAL e no AGGREGATE listava essa lacuna como um limite em aberto e descrevia o mapeamento antigo tal como saía então; a descrição era exata quanto ao código e errada quanto ao Excel, e ninguém deu por isso durante muito tempo porque as duas políticas que a maioria das pessoas combina, ocultas mais erros, caem nos códigos 3 e 7 sob ambas as tabelas. Só um código de um único bit expunha a troca: AGGREGATE(9,1,A1:A4) devolvia a soma não filtrada, e AGGREGATE(9,2,...) saltava as linhas ocultas enquanto continuava a propagar #DIV/0!. O defeito apareceu numa revisão estática do lxCalc.pas, registado como HXLS-008 no registo de problemas conhecidos do projeto, e não a partir de um ficheiro de cliente, o que diz algo sobre a raridade com que os códigos de um único bit aparecem em livros reais. A versão 2.382.0 reescreveu a descodificação como três testes de pertença a conjuntos e acrescentou um segundo gate para a política de aninhamento, ligado através de um novo callback TXLSIsSubtotalCell que o livro fornece a par do TXLSIsRowHidden

// TXLSCalculator.CalcAggregateFunc, forma da v2.382.3
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // o Excel rejeita códigos fora de 0..7
  Exit;
end;
ignoreErrors := optCode in [2, 3, 6, 7];
prevIgnoreHidden := FIgnoreHiddenRows;
prevIgnoreSubtotal := FIgnoreSubtotalCells;
FIgnoreHiddenRows := (optCode in [1, 3, 5, 7]) and Assigned(FIsRowHidden);
FIgnoreSubtotalCells := (optCode in [0, 1, 2, 3]) and Assigned(FIsSubtotalCell);
try
  // ... mapear function_num para a iftab interna, percorrer ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Repare que os dois flags são atribuídos sem condições, e não apenas definidos quando a opção os pede. A versão 2.382.0 ainda usava if ... then FIgnoreHiddenRows := True, o que significava que um AGGREGATE com código 4 aninhado dentro de um SUBTOTAL(109, ...) herdava o gate das linhas ocultas exterior em vez de o limpar. Atribuir o valor descodificado à entrada e restaurar o valor anterior no bloco finally faz com que cada chamada ao AGGREGATE seja dona da sua política durante a sua caminhada e nada mais. A versão 2.382.0 também tornou honesta a forma de array: quando um argumento é avaliado para um array Variant unidimensional ou bidimensional, o CalcAggregateFunc percorre agora cada elemento e aplica a política de erros por elemento, onde o código antigo só testava se era um double NaN e, caso contrário, entregava o array inteiro ao ExcelSum

Porque é que um AGGREGATE exterior se infiltra nas fórmulas que referencia?

Porque o FIgnoreHiddenRows e o FIgnoreSubtotalCells são campos do calculator, e o calculator é partilhado por todas as fórmulas avaliadas durante um recálculo. Os gates foram desenhados como campos de rascunho precisamente para que seis ciclos de caminhada de células os pudessem consultar sem enfiar um parâmetro por todas as assinaturas, e esse desenho é sólido desde que tudo o que corre enquanto um gate está armado pertença à agregação que o armou. A suposição quebra num ponto específico: o FGetValue. Quando um caminhante pede ao livro o valor de uma célula e essa célula contém uma fórmula sem resultado em cache, o livro compila a fórmula e avalia-a ali mesmo, no mesmo TXLSCalculator, com os gates exteriores ainda definidos. O fixture de regressão em HotXLS.WorkbookApiTests.pas mostra a falha com quatro células. A1 contém 10, A2 contém 20 numa linha oculta, A3 contém =1/0, e A4 contém =SUBTOTAL(9,A1:A2), cujo valor correto é 30. Avalie agora =AGGREGATE(9,7,A1:A4): ignorar linhas ocultas, ignorar erros, contar o subtotal aninhado como valor. O Excel devolve 10 + 30 = 40. Com o A4 sem cache, o motor anterior à 2.382.3 armava o gate das linhas ocultas, caminhava até A4, acionava a sua avaliação, e o CalcSubtotalFunc para o código 9 herdava o gate armado, porque só define o flag para os códigos 101 a 111 e nunca o limpa. A4 avaliava para 10 em vez de 30, e o total exterior voltava como 20. Nenhuma das duas fórmulas menciona linhas ocultas no caminho que produziu o número errado

O gate das agregações aninhadas infiltrava-se da mesma forma no sentido oposto. Com os códigos 0 a 3, o FIgnoreSubtotalCells está armado, e o caminhante genérico de intervalos em GetValueItemRange honra-o, pelo que um precedente cuja fórmula seja =SUM(B1:B3) largaria silenciosamente o B2 se o B2 contivesse um SUBTOTAL. Pior, o CalcSubtotalFunc repõe o FIgnoreSubtotalCells a False na saída em vez de restaurar o valor anterior, pelo que um precedente SUBTOTAL sem cache alcançado a meio da caminhada desarmava o gate exterior para todas as células a seguir a ele. O registo de problemas conhecidos do projeto arquiva isto sob HXLS-008 como fuga de estado de seleção aninhado, e esse é o nome certo para a classe de bug: um flag transitório global que está correto para o frame que o definiu e errado para todos os frames que o herdam

Como o AggregateGetCellValue e o AggregateGetItemValue isolam a caminhada

A correção da v2.382.3 põe uma fronteira em cada ponto onde o AGGREGATE lê um valor que não calculou ele próprio. O TXLSCalculator.AggregateGetCellValue envolve a chamada crua ao FGetValue: guarda os dois flags, limpa-os, faz o fetch, e restaura-os num bloco finally. A agregação exterior continua a aplicar a sua própria política à célula que acabou de buscar, porque os testes de linhas ocultas e de células aninhadas acontecem no caminhante à volta do fetch, mas a própria fórmula precedente corre sem política nenhuma, que é o que o Excel faz

function TXLSCalculator.AggregateGetCellValue(SheetIndex, Row, Col: Integer;
  var Value: Variant; var OutOfRange: Boolean): Integer;
var
  Hidden, Nested: Boolean;
begin
  Hidden := FIgnoreHiddenRows;
  Nested := FIgnoreSubtotalCells;
  FIgnoreHiddenRows := False;        // uma fórmula precedente é dona da sua própria política
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

O AggregateGetItemValue faz o mesmo para argumentos que não são intervalos, e tem de fazer mais do que limpar flags, porque um argumento como A1:A4/(B1:B4-20) é um array calculado cuja forma dos elementos tem de sobreviver. O wrapper materializa um intervalo simples num array Variant bidimensional através do AggregateGetCellValue, mapeando uma célula que devolveu um código de erro em VarAsError para que a política de erros possa ainda ser aplicada por elemento, e recorre pelos nós de operador binário e unário (SA_ADD, SA_DIV, SA_UNARMINUS e os restantes) com ApplyArrayBinaryOp e ApplyArrayUnaryOp; tudo o resto cai no GetValueItem normal. Duas proteções ficam à frente da materialização: um intervalo maior que o EffectiveFormulaArrayMemoryLimit devolve lxErrorResourceLimit, e um intervalo multi-folha ou invertido devolve #VALUE!. Um código de limite de recursos não é deliberadamente tratado como um erro de célula ignorável mesmo sob as opções 2/3/6/7, já que um motor que engolisse o seu próprio sinal de out-of-memory porque o utilizador pediu para saltar #N/A estaria a mentir. Os três caminhantes do AGGREGATE, o AggregateCollectRange para a família SUM, o AggregateReduceVariance para STDEV, VAR e PRODUCT, e o AggregateReduceWithK para MEDIAN e as formas de quantil, foram passados de FGetValue e GetValueItem para os dois wrappers, e cada um ganhou o teste de células aninhadas através do FIsSubtotalCell

Que erro devolve o AGGREGATE quando não ignora erros?

O original, desde a v2.382.3. A versão 2.382.0 detetava as células de erro corretamente mas colapsava todas elas em lxErrorValue, pelo que AGGREGATE(9,4,A1:A3) sobre uma célula #DIV/0! devolvia #VALUE!, onde o Excel propaga o primeiro erro que encontra inalterado. O helper substituto AggregateErrorCode mapeia um Variant para o código lxError* correspondente, quer o Variant seja um varError genuíno quer uma das sete strings de erro, e o AggregateValueIsError é agora apenas um teste a um resultado diferente de zero. Cada caminhante regista o primeiro código de erro que vê e devolve esse código, o que também significa que uma célula cuja fórmula nunca foi calculada, e cujo erro chega portanto como um código de retorno do FGetValue em vez de como um Variant em cache, se propaga da mesma forma que uma em cache. Duas funções de contagem recebem tratamento especial dentro do AggregateCollectRange, e o tratamento corresponde ao SUBTOTAL e não ao SUM. Para a função interna 0, COUNT, uma célula de erro nunca é contada nem propagada independentemente do código de opções, porque o COUNT só conta números. Para a função interna 169, COUNTA, uma célula de erro é um valor não vazio e conta como 1, a menos que o código de opções ignore erros, caso em que é saltada. Essa assimetria é como o Excel trata o COUNT e o COUNTA fora do AGGREGATE também, e é o tipo de detalhe que uma regra genérica de «se houver erro, propagar» erra em silêncio

O que verifica a matriz de regressão das oito opções

O fixture descrito acima é exercitado como uma matriz completa em AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: para cada código de opções de 0 a 7 avalia tanto a forma SUM como a forma MEDIAN sobre A1:A4 e confere o resultado contra uma expectativa derivada à mão. Os códigos 0, 1, 4 e 5 têm de propagar o #DIV/0! do A3, já que nenhum deles ignora erros. O código 2 dá SUM 30 e MEDIAN 15, a partir de 10 e 20 com o A4 aninhado saltado. O código 3 dá 10 e 10. O código 6 dá 60 e 20, porque os 30 no A4 passam agora a contar. O código 7 dá 40 e 20, que é o caso que devolvia 20 antes da correção da fuga. A corrida de aceitação mais ampla registada no registo de problemas conhecidos cobre os dezanove números de função contra os oito códigos, com cada precedente tanto em cache como sem cache, num total de 304 cenários em Win32 e Win64

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 10;
    Sheet.Cells[2, 1].Value := 20;
    Sheet.Cells[3, 1].Formula := '=1/0';
    Sheet.Cells[4, 1].Formula := '=SUBTOTAL(9,A1:A2)';   // subtotal do grupo = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  ocultas saltadas, erro propagado
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       ocultas + erro + aninhados saltados
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       só os erros saltados
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       era 20 antes da v2.382.3
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Onde está ainda a fronteira

Vale a pena conhecer três limites antes de construir sobre isto. Primeiro, o predicado das agregações aninhadas é textual. O TXLSXWorkbook.GetCalcIsSubtotalCell e o seu gémeo do motor clássico respondem True quando a fórmula de uma célula começa por SUBTOTAL(, AGGREGATE( ou _xlfn.AGGREGATE(, com ou sem o sinal de igual à frente, por isso uma fórmula como =IF(C1,SUBTOTAL(9,B1:B9),0) ou =SUBTOTAL(9,B1:B9)*2 não é reconhecida como aninhada e será contada duas vezes pelos códigos 0 a 3 onde o Excel a saltaria; um gerador que emita subtotais calculados deve manter a chamada de agregação à cabeça da fórmula. Segundo, o isolamento vive nos três caminhantes do AGGREGATE. O CalcSubtotalFunc continua a caminhar através do GetValueItemRange, do CollectRangeValues e do SubtotalReduceVariance, que chamam o FGetValue diretamente, pelo que um SUBTOTAL(109, ...) cujo intervalo contenha uma fórmula precedente sem cache ainda pode passar o seu gate de linhas ocultas a esse precedente. Um Recalculate completo avalia os precedentes antes dos dependentes, pelo que o caminho em cache é tomado e o gate nunca é herdado; a exposição limita-se à avaliação ad hoc através do Calculate e a livros carregados sem valores em cache, e se depender de recálculo incremental sobre o grafo de dependências para manter modelos grandes responsivos, essa mesma garantia de ordenação é o que mantém esta fuga adormecida. Terceiro, ambos os gates estão condicionados a Assigned(FIsRowHidden) e Assigned(FIsSubtotalCell). Ambas as fachadas de livro ligam os callbacks nos seus construtores, mas código que construa um TXLSCalculator à mão só com os dois argumentos originais obtém o comportamento legado de incluir tudo para todos os códigos de opções, em silêncio. Quando um total parecer errado e o texto da fórmula parecer certo, traçar a avaliação passo a passo é a forma mais rápida de ver se um precedente foi avaliado sob um gate herdado ou se um callback simplesmente nunca chegou a ser ligado

O motor de cálculo aqui descrito, o descodificador de opções, os wrappers de fetch isolados, e a matriz de regressão que os fixa, saem todos como código-fonte com o componente de folha de cálculo HotXLS para Delphi, que lê, escreve e recalcula livros XLS, XLSX e ODS em Delphi e C++Builder sem uma instalação do Excel