Achtung: Am Samstag veranstalte ich wieder den Kurs „Saubere Fenster leicht gemacht!“ bei mir zu Hause. Die schmutzigen Fenster werden KOSTENLOS zur Verfügung gestellt. Bei Interesse bitte bei mir melden!
Hallo Herr Martin,
ich würde ihr Angebot gerne wahrnehmen, und eine Praxisfrage zu einer Funktion stellen, falls das ok ist.
Problem: Summewenns verschiedene Testungen; eine Testung davon ist, ob der Wert in einer weiteren Liste zu einer bestimmten Kategorie gehört.
=summewenns($c:$c;$b:$b;“>=80000“;$b:$b;“<90000“;[DEM GERADE GETESTETEN WERT IST IN Tabellenblatt2! IN SPALTE 4 DER WERT „Zwerg“ ZUGEORDNET ])
Also in Worten: Summiere die Umsätze, wenn die Person eine 80000er-PLZ hat und ein Zwerg ist
[Blatt 1 hat 150.000 Zeilen; Blatt 2 hat 100 Zeilen. Das Tabellenblatt1 ist ein automatisiert erstellter Bericht (unveränderlich), Blatt 2 sind meine eigenen Daten, könnte verändert werden; Datenausgabe in drittem Tabellenblatt]
Ich habe verschiedene Formelkombinationen ausprobiert, es scheitert immer, dass die Testung einer bestimmten Zelle bedarf….
Haben Sie eine Idee? Falls nein, oder zu komplex, auch ok.
Freundliche Grüße und Vielen Dank vorab
####
Hallo Herr B.,
schwierig!
Erste Lösung: Sie verwenden eine Hilfsspalte:
Die Formel lautet dann:
=SUMMEWENNS(C2:C12;B2:B12;">=80000";B2:B12;"<80000";C2:C12;"Zwerg")Zweite Lösung: das Problem ist, dass die Funktionen ZÄHLENWENN, SUMMEWENN & co nicht mit den Matrixfunktionen zusammenarbeiten. Aus irgendeinem Grund geht:
=ZÄHLENWENN(EINDEUTIG(C2:C4);1)NICHT!
Schade – man hätte ZÄHLENWENN so schön verwenden können: „bist du in der Liste?“ Oder umgekehrt: SUMMEWENNS und HSTAPELN: baue die zwei Listen zusammen.
Also anders:
XVERWEIS ist matrixfähig. Also berechnet XVERWEIS(A2:A12;G2:G6;TYP!H2:H6) zu allen Zellen „Zwerg“ oder „Mensch“
Haben Sie die Funktion HSTAPELN? Sie baut zwei Bereiche nebeneinander zusammen, beispielsweise
=HSTAPELN(A2:A7;C2:C7)Damit kann man die beiden Bereiche zu einem Bereich zusammenfassen:
=HSTAPELN(A2:C12; XVERWEIS(A2:A12;G2:G12;H2:H12))Und davon kann man die heißen filtern:
= FILTER(HSTAPELN(A2:C12;XVERWEIS(A2:A12;G2:G12;H2:H12));(XVERWEIS(A2:A12;G2:G12;H2:H12)="Zwerg"))Das Multiplikationszeichen dient als UND-Operator:
= FILTER(HSTAPELN(A2:C12;XVERWEIS(A2:A12;G2:G12;H2:H12));(XVERWEIS(A2:A12;G2:G12;H2:H12)="Zwerg")*(B2:B12>=80000)*(B2:B12<90000))Eigentlich genügt es, wenn man nur die zweite und dritte Spalte „stapelt“:
= FILTER(HSTAPELN(C2:C12;XVERWEIS(A2:A12;G2:G12;H2:H12));(XVERWEIS(A2:A12;G2:G12;H2:H12)="Zwerg")(B2:B12>=80000)(B2:B12<90000))Das Ergebnis:
Und die Werte – normalerweise stehen mehrere untereinander – kann man addieren/summieren:
= SUMME(FILTER(HSTAPELN(C2:C12;XVERWEIS(A2:A12;G2:G12;H2:H12));(XVERWEIS(A2:A12;G2:G12;H2:H12)="Zwerg")*(B2:B12>=80000)*(B2:B12<90000)))Ich hänge Ihnen die Datei an.
Nachtrag: Kennen Sie LET? Damit kann man Teile einer Funktion an eine Variable auslagern:
= LET(X;XVERWEIS(A2:A12;G2:G12;H2:H12);SUMME(FILTER(HSTAPELN(C2:C12;X);(X="Zwerg")*(B2:B12>=80000)*(B2:B12<90000))))Fragen? Einfach fragen!
Liebe Grüße und: viel Spaß mit Excel wünscht
Rene Martin
Nachtrag: nur wenige Augenblicke, nachdem ich diesen Beitrag gepostet hatte, kam eine kürzere Lösung – von Ernst:
=SUMME(((B2:B12>80000)*(B2:B12<90000)*(XVERWEIS(A2:A12;G2:G12;H2:H12)="Zwerg"))*C2:C12)Es funktioniert sogar mit SVERWEIS
=SUMME(((B2:B12>80000)*(B2:B12<90000)*(SVERWEIS(A2:A12;G2:H12;2;FALSCH)="Zwerg"))*C2:C12)Danke, Ernst – wirklich sehr clever – ohne LET FILTER oder HSTABELN.
Und schließlich:
=SUMMENPRODUKT((B2:B12>80000)*(B2:B12<90000)*(XVERWEIS(A2:A12;G2:G12;H2:H12)="Zwerg")*C2:C12)=SUMMENPRODUKT((B2:B12>80000)*(B2:B12<90000)*(SVERWEIS(A2:A12;G2:H12;2;FALSCH)="Zwerg")*C2:C12)funktionieren auch.
