Opções do AGGREGATE no Delphi: matriz e vazamento de gate

O HotXLS, o componente nativo de planilha Excel para Delphi e C++Builder, entregou duas correções relacionadas de AGGREGATE em setembro de 2026. A versão 2.382.0 corrigiu o argumento options, de modo que os códigos 1/3/5/7 ignoram linhas ocultas, 2/3/6/7 ignoram erros, e 0 a 3 ignoram células SUBTOTAL e AGGREGATE aninhadas, exatamente como a Microsoft documenta. A versão 2.382.3 então impediu que essas flags de seleção vazassem para a avaliação das próprias células que a função referencia. O primeiro defeito é vergonhoso do jeito que bugs de transcrição de tabela sempre são: as posições dos bits estavam trocadas, então toda fórmula que usava um código de options diferente de zero recebia uma política que o autor dela não pediu. O segundo é mais interessante, porque é uma forma que você vai encontrar em qualquer avaliador que use um campo transitório para passar contexto a uma caminhada recursiva. Uma agregação externa arma uma flag, percorre um intervalo e puxa uma célula cuja fórmula ainda não foi calculada. Essa fórmula roda na mesma calculator, vê a mesma flag armada e agrega silenciosamente as linhas erradas, produzindo um número que está errado por uma quantia que ninguém consegue explicar só pelo texto da fórmula

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

O argumento options 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 pulá-las é o padrão dos códigos baixos. Duas coisas nisso são fáceis de inverter. O bit de linha oculta é o bit baixo, não o do meio, então AGGREGATE(9,1,...) é a forma de total filtrado e AGGREGATE(9,2,...) é a tolerante a erros. E a política de agregado aninhado é invertida em relação às outras duas: só os códigos 4 a 7 tratam como valor comum uma célula cuja própria fórmula é um SUBTOTAL ou AGGREGATE. A ECMA-376 Part 1 §18.17.7 define SUBTOTAL com a mesma divisão de incluir ou excluir linhas ocultas entre os códigos 1-11 e 101-111, e o AGGREGATE, armazenado em arquivos OOXML sob o prefixo _xlfn., generaliza essa divisão no argumento options, então a tabela que a Microsoft publica para a função AGGREGATE é o contrato que um engine tem de cumprir, não uma conveniência

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

Por que o HotXLS tinha as opções do AGGREGATE invertidas?

Porque o TXLSCalculator.CalcAggregateFunc original foi escrito a partir de uma paráfrase da tabela, e não da tabela. Ele calculava ignoreErrors := (optCode >= 4) and (optCode <= 7) e armava o gate de linhas ocultas para os códigos 2, 3, 6 e 7, enquanto a política de agregado aninhado não estava implementada de jeito nenhum. O artigo anterior sobre linhas ocultas em SUBTOTAL e AGGREGATE listava essa lacuna como limite em aberto e descrevia o mapeamento antigo como ele então era entregue; a descrição era correta quanto ao código e errada quanto ao Excel, e ninguém percebeu por muito tempo porque as duas políticas que a maioria das pessoas combina, ocultas mais erros, caem nos códigos 3 e 7 nas duas tabelas. Só um código de um único bit expôs a troca: AGGREGATE(9,1,A1:A4) devolvia a soma não filtrada, e AGGREGATE(9,2,...) pulava linhas ocultas e ainda propagava #DIV/0!. O defeito apareceu numa revisão estática de lxCalc.pas, registrado como HXLS-008 no registro de problemas conhecidos do projeto, não a partir de um arquivo de cliente, o que diz algo sobre a raridade com que os códigos de um único bit aparecem em planilhas de produção. A versão 2.382.0 reescreveu o decode como três testes de pertinência a conjunto e acrescentou um segundo gate para a política de aninhamento, ligado por um novo callback TXLSIsSubtotalCell que o workbook fornece ao lado de 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
  // ... mapeia function_num para a iftab interna, percorre ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Repare que as duas flags são atribuídas incondicionalmente, em vez de só serem setadas quando a opção pede por elas. 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 de linhas ocultas externo em vez de limpá-lo. Atribuir o valor decodificado na entrada e restaurar o valor anterior no bloco finally faz cada chamada a AGGREGATE ser dona da sua política durante a caminhada dela e nada mais. A versão 2.382.0 também tornou honesta a forma de array: quando um argumento é avaliado como um array Variant de uma ou duas dimensões, o CalcAggregateFunc agora percorre cada elemento e aplica a política de erro por elemento, enquanto o código antigo testava apenas um double NaN e, fora isso, entregava o array inteiro ao ExcelSum

Por que um AGGREGATE externo vaza para as fórmulas que ele referencia?

Porque FIgnoreHiddenRows e FIgnoreSubtotalCells são campos da calculator, e a calculator é compartilhada por toda fórmula avaliada durante um recálculo. Os gates foram projetados como campos de rascunho justamente para que seis loops de caminhada por células pudessem consultá-los sem enfiar um parâmetro em toda assinatura, e esse design é sadio desde que tudo que roda enquanto um gate está armado pertença à agregação que o armou. A suposição quebra num ponto específico: FGetValue. Quando um walker pede ao workbook o valor de uma célula e essa célula guarda uma fórmula sem resultado em cache, o workbook compila a fórmula e a avalia na hora, na mesma TXLSCalculator, com os gates externos ainda setados. O fixture de regressão em HotXLS.WorkbookApiTests.pas mostra a falha com quatro células. A1 guarda 10, A2 guarda 20 numa linha oculta, A3 guarda =1/0, e A4 guarda =SUBTOTAL(9,A1:A2), cujo valor correto é 30. Agora avalie =AGGREGATE(9,7,A1:A4): ignorar linhas ocultas, ignorar erros, contar o subtotal aninhado como valor. O Excel devolve 10 + 30 = 40. Com A4 sem cache, o engine anterior à v2.382.3 armava o gate de linhas ocultas, caminhava até A4, disparava a avaliação dela, e o CalcSubtotalFunc para o código 9 herdava o gate armado, porque ele só seta a flag para os códigos 101 a 111 e nunca a limpa. A4 era avaliada como 10 em vez de 30, e o total externo voltava como 20. Nada em nenhuma das duas fórmulas menciona linhas ocultas no caminho que produziu o número errado

O gate de agregado aninhado vazava do mesmo jeito na outra direção. Com os códigos 0 a 3, FIgnoreSubtotalCells está armado, e o walker genérico de intervalo em GetValueItemRange o honra, então um precedente cuja fórmula é =SUM(B1:B3) descartaria B2 em silêncio se B2 por acaso contivesse um SUBTOTAL. Pior, o CalcSubtotalFunc reseta FIgnoreSubtotalCells para False na saída em vez de restaurar o valor anterior, então um precedente SUBTOTAL sem cache alcançado no meio da caminhada desarmava o gate externo para toda célula depois dele. O registro de problemas conhecidos do projeto arquiva isso sob HXLS-008 como vazamento de estado de seleção aninhado, e é esse o nome certo para a classe de bug: uma flag global transitória que está correta para o frame que a setou e errada para todo frame que a herda

Como AggregateGetCellValue e AggregateGetItemValue isolam a caminhada

A correção na v2.382.3 coloca uma fronteira em torno de todo ponto em que o AGGREGATE lê um valor que ele mesmo não calculou. O TXLSCalculator.AggregateGetCellValue envolve a chamada crua a FGetValue: ele salva as duas flags, as limpa, faz a busca e as restaura num bloco finally. A agregação externa ainda aplica a própria política à célula que acabou de buscar, porque os testes de linha oculta e de célula aninhada acontecem no walker em volta da busca, mas a fórmula precedente em si roda 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 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 precisa fazer mais do que limpar flags, porque um argumento como A1:A4/(B1:B4-20) é um array computado cuja forma de elementos tem de sobreviver. O wrapper materializa um intervalo comum num array Variant bidimensional via AggregateGetCellValue, mapeando uma célula que devolveu código de erro para VarAsError para que a política de erro ainda possa ser aplicada por elemento, e ele recursiona pelos nós de operador binário e unário (SA_ADD, SA_DIV, SA_UNARMINUS e os demais) com ApplyArrayBinaryOp e ApplyArrayUnaryOp; qualquer outra coisa cai no GetValueItem normal. Duas guardas ficam na frente da materialização: um intervalo maior que EffectiveFormulaArrayMemoryLimit devolve lxErrorResourceLimit, e um intervalo multi-planilha ou invertido devolve #VALUE!. Um código de limite de recurso deliberadamente não é tratado como erro de célula ignorável nem sob as opções 2/3/6/7, já que um engine que engolisse o próprio sinal de out-of-memory porque o usuário pediu para pular #N/A estaria mentindo. Os três walkers de AGGREGATE, AggregateCollectRange para a família SUM, AggregateReduceVariance para STDEV, VAR e PRODUCT, e AggregateReduceWithK para MEDIAN e as formas de quantil, foram trocados de FGetValue e GetValueItem para os dois wrappers, e cada um ganhou o teste de célula aninhada via FIsSubtotalCell

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

O original, desde a v2.382.3. A versão 2.382.0 detectava células de erro corretamente, mas colapsava todas elas em lxErrorValue, então AGGREGATE(9,4,A1:A3) sobre uma célula #DIV/0! devolvia #VALUE!, enquanto o Excel propaga inalterado o primeiro erro que encontra. O helper substituto AggregateErrorCode mapeia um Variant para o código lxError* correspondente, seja o Variant um varError de verdade ou uma das sete strings de erro, e AggregateValueIsError agora é só um teste de resultado diferente de zero. Cada walker registra 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 portanto chega como código de retorno do FGetValue em vez de um Variant em cache, propaga do mesmo jeito que uma em cache. Duas funções de contagem recebem tratamento especial dentro do AggregateCollectRange, e o tratamento acompanha o SUBTOTAL, não o SUM. Para a função interna 0, COUNT, uma célula de erro nunca é contada nem propagada, independentemente do código de options, porque 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 options ignore erros, caso em que ela é pulada. Essa assimetria é como o Excel trata COUNT e COUNTA fora do AGGREGATE também, e é o tipo de detalhe que uma regra genérica de "se erro então propague" erra em silêncio

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

O fixture descrito acima é exercitado como matriz completa em AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: para cada código de options de 0 a 7 ele avalia tanto a forma SUM quanto a forma MEDIAN sobre A1:A4 e confere o resultado contra uma expectativa derivada à mão. Os códigos 0, 1, 4 e 5 precisam propagar o #DIV/0! de 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 pulado. O código 3 dá 10 e 10. O código 6 dá 60 e 20, porque o 30 em A4 agora conta. O código 7 dá 40 e 20, que é o caso que devolvia 20 antes da correção do vazamento. A rodada de aceitação mais ampla registrada no registro de problemas conhecidos cobre todos os dezenove números de função contra todos os oito códigos, com todo precedente em cache e 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 puladas, erro propagado
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       ocultas + erro + aninhados pulados
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       só erros pulados
    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 a fronteira ainda está

Três limites vale conhecer antes de construir em cima disso. Primeiro, o predicado de agregado aninhado é textual. O TXLSXWorkbook.GetCalcIsSubtotalCell e seu gêmeo do engine clássico respondem True quando a fórmula de uma célula começa com SUBTOTAL(, AGGREGATE( ou _xlfn.AGGREGATE(, com ou sem o sinal de igual na frente, então uma fórmula como =IF(C1,SUBTOTAL(9,B1:B9),0) ou =SUBTOTAL(9,B1:B9)*2 não é reconhecida como aninhada e vai ser contada duas vezes pelos códigos 0 a 3, onde o Excel a pularia; um gerador que emita subtotais calculados deve manter a chamada de agregação no início da fórmula. Segundo, o isolamento mora nos três walkers de AGGREGATE. O CalcSubtotalFunc ainda caminha por GetValueItemRange, CollectRangeValues e SubtotalReduceVariance, que chamam FGetValue direto, então um SUBTOTAL(109, ...) cujo intervalo contém uma fórmula precedente sem cache ainda pode passar seu gate de linhas ocultas para esse precedente. Um Recalculate completo avalia precedentes antes dos dependentes, então o caminho em cache é tomado e o gate nunca é herdado; a exposição se limita à avaliação ad hoc via Calculate e a planilhas carregadas sem valores em cache, e se você depende de recálculo incremental sobre o grafo de dependências para manter modelos grandes responsivos, a mesma garantia de ordenação é o que mantém esse vazamento adormecido. Terceiro, os dois gates são condicionados a Assigned(FIsRowHidden) e Assigned(FIsSubtotalCell). As duas fachadas de workbook ligam os callbacks nos seus construtores, mas código que monta um TXLSCalculator à mão apenas com os dois argumentos originais obtém o comportamento legado de incluir tudo para todo código de options, em silêncio. Quando um total parece errado e o texto da fórmula parece certo, rastrear a avaliação passo a passo é o jeito mais rápido de ver se um precedente foi avaliado sob um gate herdado ou se um callback simplesmente nunca foi anexado

O engine de cálculo descrito aqui, o decoder de opções, os wrappers de busca isolados e a matriz de regressão que os fixa vêm todos como código-fonte com o componente de planilha HotXLS para Delphi, que lê, escreve e recalcula planilhas XLS, XLSX e ODS em Delphi e C++Builder sem precisar de uma instalação do Excel