Escriba la suma más grande de grupos de rangos en una sola celda

Question

Considere el siguiente ejemplo artificial de un rango de celdas que representan los recuentos mensuales de algo durante varios años y una columna de resumen inicial, por ejemplo:

.|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|...

Esa columna de resumen B:Brepresenta el recuento más alto para cualquier período de tres meses, que se calculaingenuamenteusando:

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

... y luego se completa, pero es bastante irritante tener que mantenerlo, ya que los valores se agregan en la parte más a la derecha, por ejemplo, L:L, M:Mla fórmula debe actualizarse con SUM($J2:$L2)y SUM($K2:$M2), etc.

Una alternativa es:

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

...que no lo esbastantetan malo, ya que cambiar :$I2a :$J2, :$J2a :$K2y :$K2a :$L2es razonable para un período de tres meses, y eso en sí mismo podría derivarse con un uso horrible de INDIRECT, según:

={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)}

Sin embargo, esto se vuelve doloroso a medida que aumenta el tamaño de la ventana (es decir, tener que agregar más y más +cláusulas) y/o si se pretende que el tamaño de la ventana sea configurable, por ejemplo, en función del valor de B1(que en realidad es solo valor 3, con un número personalizado formato de "Most/"0"M")

Pensé en posiblemente cualquiera de:

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

... donde solo tendría que actualizar el rango y el tamaño del grupo dado a OFFSET, con la intención de eventualmente derivar esto dinámicamente, por ejemplo, usando otro horrible INDIRECT, como por ejemplo:

=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)

Cuando usasEvaluar fórmulapara atravesar la monstruosidad anterior ennormalformulario, encuentro que efectivamente eventualmente se resuelve en:

=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

... entonces parece que el rango dado a OFFSETno se expande como una matriz y, en su lugar, se usa directamente la celda superior izquierda de ese rango OFFSET.

Enformaciónforma (es decir CTRLENTER), eventualmente se resuelve en:

=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

... por lo que parece que la expansión de la matriz ocurre demasiado pronto (es decir, como resultado de COLUMNS($C:$C)), lo que luego interrumpe la INDIRECTllamada.

(Ambos LARGEy MAXdan como resultado los mismos valores; lo estaba usando LARGEporque su documentación sugiere que espera una matriz, mientras que MAXespera una lista de parámetros ilimitada de valores únicos).

Dadas las preferencias de no usar VBA o cambiar el diseño de la hoja, existen:

1. algún truco parafuerzauna expansión de matriz en el lugar correcto, de modo que SUM(OFFSET)emita una matriz que pueda ser utilizada por LARGE?
2. ¿Algún enfoque alternativo al mío, que requiera una modificación de las fórmulas de cero a mínimo, a medida que los datos se agregan a la derecha de la hoja?

(PD. Estoy usando Excel 2019, no O365/Insiders, por lo que todavía no tengo las matrices dinámicas/ SEQUENCEcosas, lo que parece que podría volverse relevante. Las respuestas que se basan en eso pueden ser útiles para la posteridad, pero no lo serán). No soluciono mi necesidad inmediata...)

Answer 1

Me gustaría sugerir el siguiente método para obtener la SUMA de los 3 trimestres más altos del año, que comprende los datos auxiliares y el valor de la SUMA grande de los 3 trimestres.

---

Cómo funciona:

• Coloque las fechas de los meses relacionados Row 1y aplique el formato de celda mmm-yy.
• Inserte el nombre de Quarter en Rnage T9:T12.

• Fórmula en celda U9, complétela.

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

NÓTESE BIEN

• ROUNDUP(MONTH(T$1:AE$1)/3,0)=Row(A1)regresa 1y 0s, 1es cuando pertenece el mesQ1y 0cuándo no, y así sucesivamente para otros trimestres como 2, 3& 4.
• SUMPRODUCT, procesa todos estos rangos de datos sin pestañear.
• =ROW(A1))devuelve 1, representa Q1y así hacia abajo 2, 3 & 4.
• =IF(ROW(A1)>=5,""regresa Blankcuando la fórmula no puede obtener el valor del trimestre después de 4.

Después de obtener los datos para cada trimestre, ahora ingrese la fórmula en la celda U14para SUMAR los 3 valores más grandes.

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

NÓTESE BIEN

• Es mejor utilizar la fórmula en forma de matriz (CSE), por lo que es necesario terminar conCtrl+Mayús+Entrar; de lo contrario, también funciona como una fórmula no matricial.

Ajuste las referencias de celda en la fórmula según sea necesario.