Opções do AGGREGATE e gates aninhados no HotXLS para Delphi

Quatro células bastam para que =AGGREGATE(9,7,A1:A4) devolva 20 num avaliador próprio enquanto o Excel mostra 40. A1 contém 10, A2 contém 20 numa linha oculta, A3 contém =1/0 e A4 contém =SUBTOTAL(9,A1:A2), um subtotal de grupo que vale 30. A opção 7 significa ignorar linhas ocultas, ignorar erros e contar subtotais aninhados como valores, então o Excel soma 10 e 30. O 20 aparece quando a configuração de ignoração de linhas ocultas da chamada externa continua ligada enquanto A4 calcula o próprio valor, o que reduz A4 a 10. Chegar a 40 exige duas coisas: decodificar os três bits de opção nas posições certas e garantir que uma fórmula referenciada nunca veja as opções da fórmula que a referenciou

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

O argumento de opções é uma matriz de três bits, e os bits são independentes entre si. O bit 0 (valor 1) ignora linhas ocultas, o bit 1 (valor 2) ignora valores de erro e o bit 2 (valor 4) deixa de ignorar células cuja própria fórmula é um SUBTOTAL ou AGGREGATE, porque ignorá-las é o padrão dos códigos baixos. Dois pontos dessa leitura são fáceis de inverter. O bit de linhas ocultas é o bit baixo, então AGGREGATE(9,1,...) é o total filtrado e AGGREGATE(9,2,...) é o tolerante a erros. E a política de aninhados funciona ao contrário das outras duas: só os códigos 4 a 7 tratam uma agregação aninhada como um número comum. A ECMA-376 Part 1 §18.17.7 define o SUBTOTAL com a mesma divisão de incluir ou excluir linhas ocultas entre os códigos 1–11 e 101–111; o AGGREGATE, armazenado em OOXML sob o prefixo _xlfn., incorpora essa divisão ao argumento de opções, e a tabela que a Microsoft publica para a função é o contrato que um motor de cálculo precisa cumprir

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

Todo código fora de 0 a 7 é rejeitado com #VALUE!, exatamente como o Excel faz

Por que os códigos de um só bit são onde as decodificações erram

Uma decodificação escrita a partir de um resumo da tabela, em vez da própria tabela, tende a ficar assim: ignorar erros para os códigos 4 e acima, ignorar linhas ocultas para 2, 3, 6 e 7, e deixar a política de aninhados de fora por completo. Essa versão pode sobreviver muito tempo em produção. A combinação que a maioria realmente usa, linhas ocultas mais erros, cai nos códigos 3 e 7 nas duas leituras, então a troca só aparece num código de um bit só. AGGREGATE(9,1,A1:A4) devolve então a soma sem filtro, e AGGREGATE(9,2,...) ignora linhas ocultas enquanto ainda propaga #DIV/0!. Planilhas reais raramente usam os códigos 1 e 2 sozinhos, e é exatamente por isso que uma revisão contra a tabela publicada pega mais problemas do que esperar por um arquivo de cliente

O HotXLS decodifica as opções como três testes de pertencimento a conjunto, um por coluna da tabela, e atribui os três resultados quando a chamada AGGREGATE começa, restaurando o que estava em vigor antes quando ela termina. A atribuição é incondicional de propósito. Uma decodificação escrita como "se a opção pedir, liga a flag" deixa um AGGREGATE com código 4 dentro de um SUBTOTAL(109, ...) herdar a configuração de linhas ocultas externa em vez de limpá-la. O esboço abaixo mostra a mesma regra para quem escreve ou revisa o próprio avaliador

// Decodificação ilustrativa para o seu avaliador: TAggPolicy e
// DecodeAggregateOptions são nomes que você define, não são API do HotXLS
type
  TAggPolicy = record
    IgnoreHidden, IgnoreErrors, SkipNested: Boolean;
  end;

function DecodeAggregateOptions(OptCode: Integer; out P: TAggPolicy): Boolean;
begin
  Result := (OptCode >= 0) and (OptCode <= 7);   // qualquer outro valor é #VALUE!
  if not Result then
    Exit;
  P.IgnoreHidden := OptCode in [1, 3, 5, 7];       // bit 0
  P.IgnoreErrors := OptCode in [2, 3, 6, 7];       // bit 1
  P.SkipNested   := OptCode in [0, 1, 2, 3];       // bit 2 limpo
end;

O lado SUBTOTAL das mesmas regras de linhas ocultas, incluindo como linhas filtradas e linhas ocultas manualmente se comparam, está coberto em o artigo sobre linhas ocultas de SUBTOTAL e AGGREGATE

Por que um AGGREGATE externo alcança as fórmulas que ele referencia?

Porque o jeito natural de passar "ignorar linhas ocultas" para seis ou sete loops de percurso de células é um campo no objeto calculadora, e uma calculadora avalia toda fórmula numa passada de recálculo. Esse design se sustenta enquanto tudo que roda com o campo armado pertence à agregação que o armou. Ele deixa de se sustentar num momento específico: o fetch da célula. Quando o walker pede o valor de uma célula e essa célula tem uma fórmula sem resultado em cache, a pasta compila e avalia essa fórmula na hora, na mesma calculadora, com as configurações externas ainda no lugar

Percorra o exemplo de abertura num motor assim. O AGGREGATE com código 7 liga o salto de linhas ocultas e começa no A1. A1 dá 10, A2 está oculta e é pulada, A3 é erro e é pulada. Aí o walker chega no A4, que ainda não foi calculado. O SUBTOTAL(9,A1:A2) de A4 roda, e uma implementação de SUBTOTAL que só liga o salto de linhas ocultas para os códigos 101 a 111 (um jeito perfeitamente razoável de escrever) nunca o desliga para o código 9. Ela encontra a configuração já ligada, pula A2 e devolve 10. O total externo vira 10 + 10 = 20. Nada na fórmula de A4 menciona linhas ocultas

A configuração de agregados aninhados vaza nos dois sentidos. Com os códigos 0 a 3 ela está ligada, e um walker de faixa genérico que a respeitasse faria um precedente como =SUM(B1:B3) descartar B2 sempre que B2 contiver um SUBTOTAL. No outro sentido, um SUBTOTAL que desliga o salto de aninhados ao terminar, em vez de restaurar o estado anterior, desativa a política do AGGREGATE externo pelo resto do percurso. Todo avaliador que usa um campo transitório para carregar contexto numa caminhada recursiva tem essa forma; o campo está correto para o frame que o armou e errado para todo frame que o herda

Cortando a política na fronteira do fetch

O HotXLS põe uma fronteira em volta de cada ponto em que o AGGREGATE lê um valor que ele mesmo não calculou. Antes do fetch, as duas configurações são salvas e limpas; depois, são restauradas 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 do fetch. A fórmula referenciada roda sem política nenhuma, o que bate com o Excel. As três famílias de walker passam por essa fronteira: a família do SUM (SUM, AVERAGE, COUNT, COUNTA, MAX, MIN), as reduções de fórmula fechada (PRODUCT, STDEV, VAR) e as formas que recebem um argumento k (MEDIAN, LARGE, SMALL, os percentis e os quartis)

// Walker ilustrativo para o seu avaliador. FPolicy, FetchCell,
// IsRowHidden e IsSubtotalCell são membros que você mesmo define
function TMyEvaluator.FetchForAggregate(Sheet, Row, Col: Integer;
  var V: Variant): Integer;
var
  Saved: TAggPolicy;
begin
  Saved := FPolicy;
  FPolicy := Default(TAggPolicy);     // a fórmula referenciada tem política própria
  try
    Result := FetchCell(Sheet, Row, Col, V);
  finally
    FPolicy := Saved;
  end;
end;

// dentro do percurso de faixa: a política externa é aplicada em volta do fetch
for R := R1 to R2 do
begin
  if FPolicy.IgnoreHidden and IsRowHidden(Sheet, R) then
    Continue;
  for C := C1 to C2 do
  begin
    if FPolicy.SkipNested and IsSubtotalCell(Sheet, R, C) then
      Continue;
    Code := FetchForAggregate(Sheet, R, C, V);
    // ... aplica a política de erro a Code / V e acumula ...
  end;
end;

Argumentos que não são faixas simples precisam de mais que flags limpas. Um argumento como A1:A4/(B1:B4-20) é uma matriz calculada, e o formato dos elementos precisa sobreviver para que a opção 6 descarte só os elementos que dividiram por zero. O HotXLS transforma cada faixa referenciada numa matriz bidimensional pela mesma fronteira, guarda uma célula que falhou como elemento de erro em vez de abortar, e aplica os operadores aritméticos e de comparação elemento a elemento. Uma faixa que cruza várias planilhas, ou uma com os cantos invertidos, devolve #VALUE!. Uma faixa grande demais para TXLSCalculator.FormulaArrayMemoryLimit (64 MB por padrão) devolve um resultado de limite de recursos, e esse resultado fica deliberadamente fora do conjunto de erros que as opções 2, 3, 6 e 7 podem ignorar. Engolir o sinal de falta de memória do próprio motor porque a fórmula pediu para ignorar #N/A produziria um número errado com plena confiança, então a fórmula falha

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

A primeira que ele encontra, sem alteração. Colapsar todo erro em #VALUE! é o atalho fácil, e faz AGGREGATE(9,4,A1:A3) sobre uma célula #DIV/0! divergir do Excel, que deixa o #DIV/0! passar. O HotXLS mapeia tanto as variantes genuínas de erro quanto as sete strings de erro (#NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A) de volta aos próprios códigos, e cada walker devolve o primeiro código que registrar. Uma célula cuja fórmula nunca foi calculada entrega o erro como código de retorno do fetch, e não como valor em cache; ele propaga exatamente como um em cache

Duas funções de contagem seguem o SUBTOTAL em vez do SUM. Para a função 2, COUNT, uma célula de erro nunca é contada nem propagada, seja qual for o código de opções, porque o COUNT só conta números. Para a função 3, 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. Essa assimetria é como o Excel trata COUNT e COUNTA fora do AGGREGATE também, e uma regra genérica "se é erro, propaga" erra as duas

Conferindo os oito códigos numa única planilha

As quatro células da abertura fazem uma conferência compacta de todos os códigos. Avalie a forma SUM e a forma MEDIAN sobre A1:A4 para cada código de 0 a 7 e compare com valores calculados à 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, 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 os 30 de A4 agora contam. O código 7 dá 40 e 20, e é o que um motor sem a fronteira de fetch reporta como 20. A conferência só tem sentido se A4 estiver sem cache quando o AGGREGATE roda, então avalie cada caso uma vez com precedentes já calculados e uma vez sem

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 de grupo = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  ocultas ignoradas, erro propaga
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       ocultas, erros e aninhados ignorados
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       só erros ignorados
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       20 significa que uma configuração vazou para o A4
    Sheet.Cells[10, 1].Formula := '=AGGREGATE(12,6,A1:A4)'; // 20     MEDIAN de 10, 20, 30
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Abrir o arquivo salvo no Excel e pressionar Ctrl+Alt+F9 dá uma segunda opinião sobre as mesmas células. Para uma varredura mais ampla, rode todos os números de função de 1 a 19 contra os oito códigos

Ligando os callbacks quando você mesmo constrói uma calculadora

As duas configurações dependem de dois callbacks. TXLSCalculator.Create recebe um TXLSIsRowHidden e um TXLSIsSubtotalCell depois do getter de valor, e ambos têm nil como padrão. Faltando qualquer um dos dois, a coluna correspondente da tabela nunca se aplica, e todo código de opções inclui linhas ocultas, agregações aninhadas ou ambos. Os objetos de pasta XLSX e XLS clássico passam as próprias implementações nos construtores; código que cria um TXLSCalculator à mão sobre a própria fonte de dados precisa fornecer os dois. As pastas reconhecem uma agregação aninhada por uma fórmula que começa com SUBTOTAL(, AGGREGATE( ou _xlfn.AGGREGATE(, com ou sem o igual inicial, e uma fonte customizada deve usar a mesma regra

// TMySheetSource e FormulaAt são tipos seus; as três assinaturas de método
// correspondem a TXLSGetValue, TXLSIsRowHidden e TXLSIsSubtotalCell
// (SheetIndex é base 1, Row e Col são base 0 como as pastas passam)
function TMySheetSource.IsSubtotalCell(SheetIndex, Row, Col: Integer): Boolean;
var
  F: string;
begin
  F := FormulaAt(SheetIndex, Row, Col);
  if (F <> '') and (F[1] = '=') then
    Delete(F, 1, 1);
  Result := SameText(Copy(F, 1, 9), 'SUBTOTAL(') or
            SameText(Copy(F, 1, 10), 'AGGREGATE(') or
            SameText(Copy(F, 1, 16), '_xlfn.AGGREGATE(');
end;

// Extern é a tabela de nomes de planilha que seu host já mantém
Calc := TXLSCalculator.Create(Extern, Source.GetCellValue,
  Source.IsRowHidden, Source.IsSubtotalCell);

A regra do prefixo tem uma consequência para pastas geradas. Um gerador que escreve =ROUND(SUBTOTAL(9,B2:B9),2) produz uma célula que o teste de prefixo não reconhece, então os códigos 0 a 3 a contam e o grupo entra duas vezes no total geral. Mantenha a chamada SUBTOTAL ou AGGREGATE o mais externa possível e deixe o arredondamento no formato numérico. Um Recalculate completo avalia precedentes antes de dependentes, então agregações normalmente leem valores em cache, e a mesma ordenação vale sob recálculo incremental sobre o grafo de dependências. Quando um total continua parecendo errado e o texto da fórmula parece certo, traçar a avaliação passo a passo mostra se um precedente rodou sob demanda dentro do percurso ou se um callback nunca foi anexado

O decodificador de opções, a fronteira de fetch e os hooks de callback descritos aqui acompanham o HotXLS Delphi spreadsheet component, que lê, escreve e recalcula pastas de trabalho XLS, XLSX e ODS a partir de código Delphi e C++Builder sem instalação do Excel na máquina