Schreiben Sie die größte Summe von Bereichsgruppen in eine einzelne Zelle

Question

Betrachten Sie das folgende konstruierte Beispiel eines Zellbereichs, der die monatlichen Zahlen von etwas über mehrere Jahre hinweg darstellt, und einer führenden Zusammenfassungsspalte, z. B.:

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

Diese Zusammenfassungsspalte B:Bstellt die höchste Zahl für einen Dreimonatszeitraum dar, berechnetnaivmit:

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

... und dann nach unten ausgefüllt, was jedoch ziemlich mühsam zu pflegen ist – da Werte ganz rechts hinzugefügt werden, z. B. , L:L, muss die Formel mit und usw. M:Maktualisiert werden.SUM($J2:$L2)SUM($K2:$M2)

Eine Alternative ist:

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

...was nicht istganzso schlimm, da die Änderung :$I2von in :$J2, :$J2zu :$K2und :$K2zu :$L2für ein Zeitfenster von drei Monaten sinnvoll ist und dies selbst durch schrecklichen Gebrauch von abgeleitet werden könnte INDIRECT, wie beispielsweise:

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

Dies wird jedoch mühsam, wenn die Fenstergröße zunimmt (d. h. wenn immer mehr +Klauseln hinzugefügt werden müssen) und/oder wenn die Fenstergröße konfigurierbar sein soll, z. B. basierend auf dem Wert von (der eigentlich nur ein Wert mit einem benutzerdefinierten Zahlenformat von B1ist ).3"Most/"0"M"

Ich habe möglicherweise an eines der folgenden gedacht:

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

...wobei ich nur den Bereich und die Gruppengröße aktualisieren müsste, die für angegeben wurden OFFSET, mit der Absicht, dies schließlich dynamisch abzuleiten, z. B. unter Verwendung eines anderen schrecklichen INDIRECT, wie etwa:

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

Beim BenutzenFormel auswertendurch die oben genannte Monstrosität zu gehen innormalForm, stelle ich fest, dass es sich letztendlich tatsächlich wie folgt auflöst:

=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

... es scheint also, dass der angegebene Bereich OFFSETnicht als Array erweitert wird und stattdessen die obere linke Zelle dieses Bereichs direkt von verwendet wird OFFSET.

InAnordnungForm (dh CTRLENTER), löst es sich stattdessen letztendlich auf zu:

=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

... es scheint also, dass die Array-Erweiterung zu früh erfolgt (d. h. zum Ergebnis von COLUMNS($C:$C)), was den Aufruf später abbricht INDIRECT.

(Sowohl LARGEals auch MAXführen zu denselben Werten – ich habe es verwendet, LARGEweil die Dokumentation nahelegt, dass ein Array erwartet wird, während MAXeine unbegrenzte Parameterliste mit Einzelwerten erwartet wird.)

Angesichts der Präferenzen, VBA nicht zu verwenden oder das Blattlayout nicht zu ändern, gibt es:

1. irgendwelche Tricks, umGewalteine Array-Erweiterung an der richtigen Stelle, sodass SUM(OFFSET)ein Array ausgegeben wird, das von verwendet werden kann LARGE?
2. gibt es alternative Ansätze zu meinem, die keine oder nur minimale Änderungen an den Formeln erfordern, da die Daten auf der rechten Seite des Blattes hinzugefügt werden?

(PS. Ich verwende Excel 2019, nicht O365/Insider, daher habe ich die dynamischen Arrays/ SEQUENCEdas Zeug noch nicht, was aber relevant werden könnte. Antworten, die darauf basieren, sind vielleicht für die Nachwelt nützlich, werden mein unmittelbares Problem aber nicht lösen …)

Answer 1

Ich möchte die folgende Methode vorschlagen, um die SUMME der höchsten Werte aller drei Quartale des Jahres zu erhalten. Sie umfasst Hilfsdaten und die SUMME der Werte aller drei großen Quartale.

---

Wie es funktioniert:

• Geben Sie die Daten der entsprechenden Monate ein Row 1und wenden Sie das Zellenformat an mmm-yy.
• Geben Sie den Namen des Quartals in den Bereich ein T9:T12.

• Formel in Zelle U9, füllen Sie sie aus.

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

Achtung

• ROUNDUP(MONTH(T$1:AE$1)/3,0)=Row(A1)gibt zurück 1und ist 0s, 1wenn der Monat zu gehörtFrage 1und 0wenn dies nicht der Fall ist, und so weiter für andere Quartale wie 2, 3& 4.
• SUMPRODUCT, verarbeitet all diese Datenmengen, ohne mit der Wimper zu zucken.
• =ROW(A1))gibt 1 zurück, stellt dar Q1und so abwärts 2, 3 & 4.
• =IF(ROW(A1)>=5,""gibt zurück Blank, wenn die Formel den Quartalswert nach 4 nicht abrufen kann.

Nachdem Sie die Daten für jedes Quartal erhalten haben, geben Sie nun die Formel in die Zelle ein, U14um die drei größten Werte zu summieren.

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

Achtung

• Besser ist es, die Formel in Array-Form (CSE) zu verwenden, also muss man abschließen mitStrg+Umschalt+Eingabe, ansonsten funktioniert es auch als Nicht-Array-Formel.

Passen Sie die Zellbezüge in der Formel nach Bedarf an.