Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1912to1916
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
Inhaltsverzeichnis

Korrelation und Summewenn mit Bedingung

Korrelation und Summewenn mit Bedingung
22.12.2022 15:11:10
JayJay
Hallo zusammen,
ich habe zwei Anliegen in einer Tabelle:
https://www.herber.de/bbs/user/156897.xlsx
1) Ich möchte die Korrelation zweier Variablen herstellen (an sich kein Problem), diese jedoch an eine weitere Bedingung (das Jahr) knüpfen (das ist mein Problem).
2) Ich möchte eine Summewenn-Funktion an zwei weitere Bedingungen/ Variablen knüpfen, die ich farblich markiert habe: Ich möchte die Variable (A, B, C) auswählen können und gleichzeitig der Funktion mitteilen, wie viele Parameter sie (ausgehend vom aktuellen Datum) in die Summewenn-Funktion einbinden soll. Also im Beispiel möchte ich aus der Tabelle nur die letzten 3 Zahlen für Variable B aufsummieren, ausgehend vom heutigen Datum.
Habt ihr eine Idee? :/
LG
JayJay

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: z.B. mit einer klassischen Matrixformel ...
22.12.2022 15:22:19
neopa
Hallo JayJay,
... so: {=KORREL(WENN(Tabelle1[Jahr]=H8;Tabelle1[Variable A]);Tabelle1[Variable B])}
Gruß Werner
.. , - ...
AW: Korrelation und Summewenn mit Bedingung
22.12.2022 15:25:09
lupo1
A) =KORREL(WENN(Tabelle1[Jahr]=M3;Tabelle1[Variable A]);WENN(Tabelle1[Jahr]=M3;Tabelle1[Variable B])) ggflls als {} eingeben!
B) =SUMMENPRODUKT(INDEX(Tabelle2[Variable B];KGRÖSSTE(WENN(Tabelle2[Variable A]=C4;ZEILE(Tabelle2[Variable B]));ZEILE(INDIREKT("1:"&C3)))-ZEILE(Tabelle2[[#Kopfzeilen];[Variable B]])))
AW: Korrelation und Summewenn mit Bedingung
22.12.2022 15:33:07
HeritzP
Hallo,

=SUMMEWENNS(D$6:D$25;C$6:C$25;C4;B$6:B$25;">="&INDEX(B$6:B$25;AGGREGAT(14;6;ZEILE(A$1:A$25) /(C$6:C$25=C4);C3))) 

Anzeige
AW: Korrelation und Summewenn mit Bedingung
22.12.2022 15:44:58
JayJay
Hallo an alle,
das funktioniert wunderbar - ich danke euch!
@HeritzP: wieso muss ich bei deiner Formel noch Spalte A mit einbeziehen, obwohl diese keine Werte hat?
LG
Du musst Dich verguckt haben
22.12.2022 15:48:39
lupo1
Da wird nirgendwo sich auf A1:A25 bezogen ;)
AW: Du musst Dich verguckt haben
22.12.2022 16:02:13
JayJay
Hi, wen meinst du denn genau? :)
Ich habe aber bei allen Formeln das Problem, dass das aktuelle Datum gar nicht einbezogen wird? Es wird quasi immer ausgehend vom Ende der Tabelle die Anzahl x summiert. Wenn ich aber bspw. den 13.12.22 eingebe als Datum, dürften ja nur ab dem 13.12.22 ausgehend die letzten x Werte summiert werden. Es werden aber in beiden Beispielen dennoch die letzten x Werte ausgehend vom Tabellenende genommen, Oder habe ich einen Denkfehler!?
LG
Anzeige
Da sind wir wieder bei diesem "ZEILE", was Du ...
22.12.2022 16:26:42
lupo1
....nicht verstehst, hier sogar noch verschlimmert um INDIREKT.
Sorry, Du hast recht
22.12.2022 16:35:49
lupo1
=SUMMENPRODUKT(INDEX(Tabelle2[Variable B];KGRÖSSTE(WENN(
C$6:INDEX(Tabelle2[Variable A];VERGLEICH(B$2;Tabelle2[Datum]))=C$4;ZEILE(
D$6:INDEX(Tabelle2[Variable B];VERGLEICH(B$2;Tabelle2[Datum]))));ZEILE(INDIREKT("1:"&C$3)))-
ZEILE(Tabelle2[[#Kopfzeilen];[Variable B]])))

Ich hasse diese intelligente Schreibweise abgrundtief!
AW: Sorry, Du hast recht
22.12.2022 16:46:06
JayJay
So passt es :)
Ich find die Schreibweise eigentlich ganz geil - vor allem, wenn man auf anderen Tabellenblättern unterwegs ist und die Datentabelle ein vielfaches an eindeutig benannten Spalten hat :)
Anzeige
AW: welche XL-Version hast Du wirklich? ...
22.12.2022 17:04:17
neopa
Hallo JayJay,
... denn in der von Dir angegebenen XL2016er Version käme mit der von lupo1 angegebenen Formel für Deine Beispieldatei standardmäßig nur #WERT! als Ergebnis heraus und als Matrixformel abgeschlossen eine 4.
Die Formel von Peter und meine (ohne INDEX) folgende ergibt das vorgegebene Ergebnis 10.
=SUMMEWENNS(Tabelle2[Variable B];Tabelle2[Variable A];C4;Tabelle2[Datum];"&gt="&AGGREGAT(14;6;Tabelle2[Datum]/(Tabelle2[Variable A]=C4);C3))
Allerdings dies nur, wenn z.B. in B17 nicht auch das gleiche Datum stehen könnte wie in B18. Für diesen Fall bedürfte es einer Formelerweiterung .
Gruß Werner
.. , - ...
Anzeige
AW: welche XL-Version hast Du wirklich? ...
22.12.2022 17:15:15
JayJay
Hallo Werner,
jetzt bin ich tatsächlich etwas verwirrt.
Ich habe nochmal deine Formel probiert, da kommt bei mir 10 raus.
Wenn ich jetzt allerdings das Datum oben auf 13.12.2022 ändere, bleibt es bei 10. Hab ich irgendwas falsch verstanden?
Tatsächlich hab ich auf meinem Laptop gerade Office365 - auf meinem anderen allerdings noch Excel 2016.
AW: was meinst Du mit "oben"? Ich meinte B17 owT
22.12.2022 17:25:16
neopa
Gruß Werner
.. , - ...
Für 365 wird es kürzer
22.12.2022 17:28:02
lupo1
=SUMME(INDEX(SORTIEREN(FILTER(B6:D25;(C6:C25=C4)*(B6:B25&lt=B2));;-1);SEQUENZ(C3);3))
AW: Für 365 wird es kürzer
22.12.2022 17:37:15
JayJay
Das ist quasi die einfachste Lösung, die ich gesucht habe :)
Allerdings funktioniert es scheinbar nicht mit den direkten Tabellenbezügen aus der intelligenten Tabelle :/
Anzeige
AW: für Deine angegebene 2016er XL-Version ...
22.12.2022 17:45:37
neopa
Hall nochmal,
... und den Fall, dass für eine Kategorie mehrmals das gleiche Datum (ohne zusätzliche Zeitangabe) mit verschiedenen Werten der Variable B erfasst wurde, dann folgende erweiterte Formel:
=SUMMENPRODUKT(Tabelle2[Variable B]*(Tabelle2[Variable A]=C4)*(Tabelle2[Datum]+ZEILE(Tabelle2)%%&gt=AGGREGAT(14;6;(Tabelle2[Datum]+ZEILE(Tabelle2)%%)/(Tabelle2[Variable A]=C4);C3)))
Damit wird bei Deiner vorliegenden Sortierung der unterste Datumswert mit zugehörigen Datenwerten in die Summierung einbezogen. Ansonsten ergebe sich in Deiner Beispieldatei mit B17=B18 nicht 10 sondern 21 als Ergebnis.
Gruß Werner
.. , - ...
Anzeige

302 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige