Escreva a maior soma de grupos de intervalos em uma única célula

Question

Considere o seguinte exemplo inventado, de um intervalo de células que representa as contagens mensais de algo ao longo de vários anos e uma coluna de resumo inicial, por exemplo:

.|A    |B      |C    |D    |E    |F    |G    |H    |I    |J    |K    |...
-+-----+-------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----
1|     |Most/3M|Jan19|Feb19|Mar19|Apr19|May19|Jun19|Jul19|Aug19|Sep19|...
2|Foo  |     14|    1|    3|    7|    3|    4|    2|    1|    3|    1|...
3|Bar  |     13|    3|    4|    1|    2|    2|    2|    9|    1|    1|...
4|Baz  |     18|    2|    3|    8|    7|    3|    2|    3|    7|    1|...

Essa coluna de resumo B:Brepresenta a contagem mais alta para qualquer período de três meses, que é calculadaingenuamenteusando:

=MAX(SUM($C2:$E2),SUM($D2:$F2),SUM($E2:$G2),
     SUM($F2:$H2),SUM($G2:$I2),SUM($H2:$J2),
     SUM($I2:$K2))

... e depois preenchido, mas é muito irritante manter isso - à medida que os valores são adicionados à parte mais à direita, por exemplo, L:L, M:M, a fórmula deve ser atualizada com SUM($J2:$L2)e SUM($K2:$M2), etc.

Uma alternativa é:

={LARGE(($C2:$I2 + $D2:$J2 + $E2:$K2), 1)}

...o que não ébastantetão ruim, pois mudar :$I2para :$J2, :$J2para :$K2e :$K2para :$L2é razoável para uma janela de três meses, e isso pode ser derivado do uso horrível de INDIRECT, conforme:

={LARGE(INDIRECT("R[0]C3:R[0]C"&COUNTA(OFFSET($C2,0,0,1,2000)),FALSE)+
        INDIRECT("R[0]C4:R[0]C"&COUNTA(OFFSET($C2,0,0,1,2000))+1,FALSE)+
        INDIRECT("R[0]C5:R[0]C"&COUNTA(OFFSET($C2,0,0,1,2000))+2,FALSE), 1)}

No entanto, isto torna-se doloroso à medida que o tamanho da janela aumenta (ou seja, tendo que adicionar mais e mais +cláusulas) e/ou se o tamanho da janela for configurável, por exemplo, com base no valor de B1(que na verdade é apenas value 3, com um número personalizado formato de "Most/"0"M")

Eu pensei em possivelmente um dos seguintes:

={MAX(SUM(OFFSET($C2:$I2,0,0,1,3)))}
={LARGE(SUM(OFFSET($C2:$I2,0,0,1,3)), 1)}

... onde eu só teria que atualizar o intervalo e o tamanho do grupo dado a OFFSET, com a intenção de eventualmente derivar isso dinamicamente, por exemplo, usando outro horrível INDIRECT, como:

=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&
                           (COUNTA(OFFSET($C2,0,0,1,2000))+COLUMN($C:$C)-B$1),
                           FALSE),
                  0,0,1,B$1)),
       1)

Ao usarAvaliar Fórmulapara passar pela monstruosidade acima emnormalforma, acho que de fato eventualmente se resume a:

=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&(9+3-B$1),FALSE),0,0,1,3)),1)
=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&(12-3),FALSE),0,0,1,3)),1)
=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C9",FALSE),0,0,1,3)),1)
=LARGE(SUM(OFFSET($C$2:$I$2,0,0,1,3)),1)
=LARGE(SUM($C$2:$E$2),1)
=LARGE(11,1)
=11

... então parece que o intervalo fornecido OFFSETnão é expandido como uma matriz e a célula superior esquerda desse intervalo é usada diretamente por OFFSET.

Emvariedadeforma (ou seja CTRLENTER), em vez disso, eventualmente se resume a:

=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&(9+{3}-B$1),FALSE),0,0,1,B$1)),1)
=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&({12}-3),FALSE),0,0,1,B$1)),1)
=LARGE(SUM(OFFSET(INDIRECT({"R[0]C3:R[0]C9"},FALSE),0,0,1,B$1)),1)
=LARGE(SUM(OFFSET({#VALUE!},0,0,1,B$1)),1)
=LARGE(SUM(OFFSET({#VALUE!},0,0,1,3)),1)
=LARGE(SUM({#VALUE!}),1)
=LARGE(1,1)
=1

... então parece que a expansão do array acontece muito cedo (ou seja, no resultado de COLUMNS($C:$C)), o que mais tarde interrompe a INDIRECTchamada.

(Ambos LARGEresultam MAXnos mesmos valores - eu estava usando LARGEporque sua documentação sugere que ele espera uma matriz, enquanto MAXespera uma lista de parâmetros ilimitada de valores únicos.)

Dadas as preferências de não usar VBA ou alterar o layout da planilha, existem:

1. algum truque paraforçauma expansão de array no lugar certo, de modo que SUM(OFFSET)emita um array que pode ser usado por LARGE?
2. alguma abordagem alternativa à minha, que exige alteração de zero a mínimo das fórmulas, à medida que os dados são adicionados à direita da planilha?

(PS. Estou usando o Excel 2019, não o O365/Insiders, então ainda não tenho os arrays/ SEQUENCEcoisas dinâmicas, o que parece que pode se tornar relevante. Respostas que dependem disso podem ser úteis para a posteridade, mas venceram. não resolverei minha necessidade imediata...)

Answer 1

Eu gostaria de sugerir o seguinte método para obter a SOMA de quaisquer 3 trimestres mais altos do ano, compreendendo dados auxiliares e o valor da SUM Grande dos 3 trimestres.

---

Como funciona:

• Coloque as datas dos meses relacionados Row 1e aplique o formato de célula mmm-yy.
• Insira o nome do Quarter em Rnage T9:T12.

• Fórmula na célula U9, preencha-a.

  =IF(ROW(A1)>=5,"",(SUMPRODUCT((ROUNDUP(MONTH(T$1:AE$1)/3,0)=ROW(A1))*(T$2:AE$2))))
  

Observação

• ROUNDUP(MONTH(T$1:AE$1)/3,0)=Row(A1)retorna 1e 0s, 1é quando o mês pertence a1º trimestree 0quando não e assim por diante para outros trimestres como 2, 3& 4.
• SUMPRODUCT, processa todos esses intervalos de dados sem pestanejar.
• =ROW(A1))retorna 1, representa Q1e assim por diante 2, 3 & 4.
• =IF(ROW(A1)>=5,""retorna Blankquando a fórmula não consegue obter o valor do Trimestre após 4.

Depois de obter os dados de cada trimestre, agora insira a fórmula na célula U14para SOMAR os 3 maiores valores.

{=SUM(LARGE(U$9:U$12,{1,2,3}))}

Observação

• É melhor usar a fórmula em formato de array (CSE), então é necessário terminar comCtrl+Shift+Enter, caso contrário, também funciona como uma fórmula não array.

Ajuste as referências de células na fórmula conforme necessário.