Live-Forum - Die aktuellen Beiträge
Datum
Titel
02.05.2024 22:34:29
Zeilen suchen und löschen aus zwei ListObject
02.05.2024 22:04:57
Immer erste Mappe ansprechen und bestimmten Bereich kopieren
02.05.2024 18:17:15
Button fixieren
02.05.2024 17:27:11
Größten/höchsten Buchstaben einer Spalte anzeigen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1928to1932
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1928to1932
1928to1932
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Bereich dynamische summieren

Bereich dynamische summieren
27.04.2023 13:56:58
Lola

Hi zusammen,

folgendes Problem:
Ich möchte einen Bereich anhand bestimmter Kriterien dynamisch in Abhängigkeit der aktuellen Periode aufsummieren und zwar immer ausgehend vom aktuellen Monat soll die Summe 6 Monate rückwärts gerechnet umfassen (rollierende Summe):
Bsp. Aktuelle Periode ist Februar 2023; Land 1 hat in den letzten 11 Monaten jeden Monat Austritte gehabt. Ausgehend vom Februar sollen die Monate September 22 bis Februar 2023 aufsummiert werden. Das ganz jeweils dynamisch.

Mit Hilfe Summewenns und Index Vergleich (siehe Beispieldatei) bekomme ich die Werte aus der Datengrundlage zumindest in meine Übersicht.
Nach etwas Recherche wäre vermutlich Bereich Verschieben die richtige Lösung, leider bekomme ich es nicht mit der Summewenns und Index Vergleichs Lösung von mir kombiniert.
Daher bitte ich um eure Hilfe.
Eine Beispieldatei ist hochgeladen: https://www.herber.de/bbs/user/158931.xlsx
Bin generell für jede kreative Lösung offen und dankbar :-)

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Bereich dynamische summieren
27.04.2023 14:10:52
SF
Hola,

wenn in D3, E3.... echte Datumswerte stehen würden, formatiert als MMMM JJJJ (also 01.03.2022, 01.04.2022...), ginge das ganz einfach mit Summenprodukt.
=summenprodukt(($A$5:$A$10=$A15)*($B$5:$B$10="eintritte")*($D$3:$O$3>=edatum(C14;-6))*($D$5:$O$10))
Gruß,
steve1da


AW: Bereich dynamische summieren
27.04.2023 14:13:37
SF
Edit: in Zeile 14 müssen natürlich ebenfalls solche Datumswerte stehen.


AW: Bereich dynamische summieren
27.04.2023 14:11:22
Daniel
Hi
Summe + Bereich.Verschieben.
das mit dem Wenns kannst du dir sparen, wenn du dir sparen, wenn du das Bereich.Verschieben nicht nur benutzt, um die Spalten zu setzten, sondern auch die Zeile:
in B15:
=SUMME(BEREICH.VERSCHIEBEN($A$4;VERGLEICH($A15;$A$5:$A$10;0)+1;VERGLEICH(B$14;$3:$3;0)-1;1;-6))
das "+1" in der Mitte ist für die Eintritte, brauchst du die Austritte, nimm +0 (oder lass weg).

Gruß Daniel


Anzeige
AW: Bereich dynamische summieren
27.04.2023 14:38:30
RePeter
in Zelle B15:
=SUMME(INDEX(BEREICH.VERSCHIEBEN($D$4:$O$4;VERGLEICH($A15&$B$13;$A$5:$A$10&$C$5:$C$10;0);0); VERGLEICH(B$14;$D$3:$O$3;0)):INDEX(BEREICH.VERSCHIEBEN($D$4:$O$4;VERGLEICH($A15&$B$13; $A$5:$A$10&$C$5:$C$10;0);0);VERGLEICH(B$14;$D$3:$O$3;0)-5))

dann die Formel in den Zielbereich nach unten und rechts kopieren. Damit sollte auch eine Dynamisierung von B13 möglich sein. Gegebenenfalls noch einen WENNFEHLER-Abfang einbauen (weil es bei einigen ja keine Austritte gibt)
VG


noch'n Gedicht
27.04.2023 15:48:07
Rudi Maintaire
Hallo,
nach Land und Kennzahl ID:

=LET(x;VERGLEICH(D14;3:3;);SUMMENPRODUKT((A5:A10=A15)*(C5:C10=B13)*INDEX(A5:O10;;x-5):INDEX(A5:O10;; x)))

Gruß
Rudi


Anzeige
AW: noch'n Gedicht
27.04.2023 17:46:16
Lola
Hi Rudi,

Danke für Deinen Input ;-)
Ich benutze gerne in Formeln als Matrix das komplette Blatt: !$1:$1048576, und ganze Spalten und Zeilen.
Regelmäßig kommen in meiner Datengrundlage Zeilen dazu und zwecks Flexibilisierung versuche ich fixierte Bereiche zu vermeiden.

Ist es innerhalb der LET Formel generell möglich oder benötigt sie fixierte Bereiche?
Mein Versuch mit Anpassung in deiner Formel schlug leider fehl :-( ich habe dafür die Zieltabelle und die Datengrundlage in 2 verschiedene Reiter gepackt
Zum besseren Verständnis nochmal die Datei: https://www.herber.de/bbs/user/158936.xlsx 
=LET(x;VERGLEICH($E$5;Test!$3:$3;);SUMMENPRODUKT((Test!$A:$A=$B6)*(Test!$C:$C=$C$4) *INDEX(Test!$1:$1048576;;x-5):INDEX(Test!$1:$1048576;; x)))
Danke!


Anzeige
AW: Bereich dynamische summieren
27.04.2023 20:51:12
Yal
Hallola,

deine Tabelle nicht ganz "gerade": in Spalten A:C sind die Überschrift in Zeile 4, in den Monatspalten in Zeile 3. Man kann es von vorne rein beseitigen*, darauf eine "intelligente" Tabelle legen, dann mit Power Query entpivotieren und ein "InLetzten6M" einführen und das gesamt mit einer Pivottabelle auswerten.

Interesse? einfach ja sagen.

VG
Yal

*: wenn nicht in der Quelle, kann diese Unschönheit mit PQ beseitigen. Ist nur umstandlicher, aber wenn es nicht anders geht...


INDEX()
27.04.2023 21:56:17
lupo1
B15[:D18]: =SUMME(INDEX($D$5:$O$10;VERGLEICH($A15&$B$13;$A$5:$A$10&$C$5:$C$10;);VERGLEICH(B$14;$D$3:$O$3;)-{0.1.2.3.4.5}))

teildynamisch (nach Zeile):
B15#[:D15#]:
=NACHZEILE($A15:$A18;LAMBDA(z;SUMME(INDEX($D5:$O10;VERGLEICH(z&$B13;$A5:$A10&$C5:$C10;);VERGLEICH(B14;$D3:$O3;)-{0.1.2.3.4.5}))))

volldynamisch "B15# only" ist mir grad nicht gelungen.


Anzeige
mit Hilfe von maninweb nun volldynamisch in B15#:
29.04.2023 14:22:53
lupo1
B15#: =LET(z;A15:A18;s;B14:D14;zz;ZEILEN(z);ss;SPALTEN(s);MATRIXERSTELLEN(zz;ss;LAMBDA(zz;ss;
SUMME(INDEX(D5:O10;VERGLEICH(INDEX(z;zz)&B13;A5:A10&C5:C10;);VERGLEICH(INDEX(s;ss);D3:O3;)-SEQUENZ(;6;0))))))

Kann man in XLWeb ausprobieren! https://office.live.com/start/Excel.aspx


AW: Bereich dynamische summieren
01.05.2023 16:55:02
Kulo
Hallo Lola,

schau mal, ob Dir das weiterhilft:

https://www.herber.de/bbs/user/158990.xlsx

Nur Laienhaft zusammengebastelt. Zum Glück rechnet Excel die Formeln alleine aus... ;-)

Viele Grüße
Kulo


Anzeige
AW: Bereich dynamische summieren
01.05.2023 17:27:09
Kulo
... oder mit der Idee von Daniel leicht abgewandelt:

=SUMME(BEREICH.VERSCHIEBEN($D$3:$O$3;VERGLEICH($A15&$B$13;$A$5:$A$10&$C$5:$C$10;0)+1;SPALTE()+7;1;-6) )

"VERGLEICH($A15&$B$13;$A$5:$A$10&$C$5:$C$10;0)" gibt die Zeile im Bereich Zeile 5 bis 10 aus, in der in A das Land und in C die Kennzahl den Kriterien in der Übersicht entsprechen.

Vielen Dank an Daniel. Hab wieder einiges gelernt.

Viele Grüße
Kulo


Welche Version hast Du?
02.05.2023 00:38:35
lupo1
Mit ihrem 365 kann Lola nämlich die volldynamische Variante (29.04.2023 14:22:53) verwenden.

Du kannst es auch: In Excel Online. Wenn Du nicht 365 hast.


Anzeige
AW: Welche Version hast Du?
02.05.2023 19:15:35
Kulo
Hallo Lupo,

diese Formel ist natürlich die Premiumvariante.

Aber um solche Formeln zu verstehen, bedarf es bei mir noch jede Menge an Hirnschmalz und Wohlwollen aus dem Excelforum.

Ich habe Excel 365. Gerade bin ich unterwegs. Aber am Wochenende versuche ich mal, die Formel aufzudröseln.

Vielen Dank für den Tipp und eine schöne Woche

Kulo


Normalerweise wäre sie etwas länger ...
02.05.2023 22:33:54
lupo1
... weil man die 4 maßgebenden Bereiche

B14:D14: Zielparameter Spalten
A15:A18: Zielparameter Zeilen
D:O: Quellparameter Spalten
5:10: Quellparameter Zeilen

eigentlich nach vorne zieht und sich dann mit INDEX auf (Ziel) sie oder (Quelle) Teile davon bezieht. Aber es geht hier nicht um eine Allgemein-, sondern um eine Speziallösung.

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige