Summenprodukt für bedingte Summe - Bedingung kann fehle

Moderator: ModerationP

Summenprodukt für bedingte Summe - Bedingung kann fehle

Beitragvon wkosensky » 30. Nov 2020, 14:27

Hallo,

ich habe ständig Summenprodukte, die unter Beachtung einiger Bedingungen grundsätzlich immer die gleiche Summe bilden. Nur die Bedingungen wechseln und einzelne Bedingungen können auch mal nicht vorhanden sein.
Zugrunde liegt eine intelligente Tabelle "Tab" mit Attributen und Werten. Die Attribute werden als Bedingung in der Formel mit INDIREKT angesprochen und die Werte bei Übereinstimmung der Bedingungen summiert (siehe Zeile 17).
Nun suche ich eine Formel, die eben auch das Nichtvorhandensein von Bedingungen zulässt (siehe Zeile 18 --> da fehlt C18). In Zeile 18 funktioniert die gleiche Formel wie in Zeile 17 nicht, weil das Kriterium in C18 nicht vorhanden ist - ich muss die komplette Bedingung entfernen (siehe Zeile 19). Schwer zu erklären, ich habe eine Datei angehängt.

Könnt ihr mir bitte einen Tipp geben, wie ich die Formel variabel schreiben kann, so dass die Bedingungen beachtet werden, wenn sie vorhanden sind. Wenn eine Bedingung nicht vorhanden ist, sollte diese eben auch nicht beachtet werden, d. h. der entsprechende Term in der Summenprodukt-Formel sollte dann wohl 1 ergeben.

Vielen Dank!!

VG
W.
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
wkosensky
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 8
Registriert: 28. Sep 2020, 11:40

Re: Summenprodukt für bedingte Summe - Bedingung kann fehle

Beitragvon slowboarder » 30. Nov 2020, 14:50

Hi

erweitere die einzelnen Bedingungen nach diesem Prinzip:

aus (INDIREKT($A$16)=$A17)
wird ((INDIREKT($A$16)=$A17)+($A17=""))
dies gilt, wenn in der Tabelle jede Zeile gefüllt ist.
das + simuliert in solchen Matrixformeln die ODER-Verknüpfung, so wie das * die UND-Verknüpfung simuliert.
allerdings sollte es in dieser einfachen Variante ausgeschlossen sein, dass beide Bedingungen gleichzeitig zutreffen, da diese sonst doppelt in die Berechnung eingehen.

sollte es vorkommen können, dass auch in der Tabellenspalte eine Zelle leer sein kann; dann
(((INDIREKT($A$16)=$A17)+($A17=""))>0)

allerdings könntst du auch SummeWenns zum auswerten verwenden:
=SUMMEWENNS(Tab[Wert_1];INDIREKT($A$16);$A17;INDIREKT($B$16);$B17;INDIREKT($C$16);$C17)
wenn du als Bedingung in den Spalten, die nicht berücksichtigt werden sollen den Stern * einträgst, anstatt die Zelle leer zu lassen
Gruß Daniel
Zuletzt geändert von slowboarder am 30. Nov 2020, 14:56, insgesamt 1-mal geändert.
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 27563
Registriert: 18. Apr 2009, 13:33

Re: Summenprodukt für bedingte Summe - Bedingung kann fehle

Beitragvon HKindler » 30. Nov 2020, 14:50

Hi,

=SUMMENPRODUKT((INDIREKT($A$16)=$A18)*(INDIREKT($B$16)=$B18)*((INDIREKT($C$16)=$C18)+($C18=""))*Tab[Wert_1])

Auf die Art muss C18 entweder leer sein oder der Eintrag in der Spalte muss C18 entsprechen. Falls noch mehr Spalten leer sein können, dann musst du dieses Prinzip *((Bedingung)+(Feld=""))* auch für jene anwenden.

Übrigens würde ich INDIREKT meiden. In dieser einfachen Tabelle könntest du z.B. einfach alle Spalten verwenden und die uninteressanten leer lassen. Wenn es deutlich mehr Spalten sind, wird es natürlich schwieriger...
Gruß,
Helmut

----------------------------
Windows 10 Enterprise (64 Bit) / Office 365 ProPlus (32 Bit)
Benutzeravatar
HKindler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 5351
Registriert: 04. Jul 2013, 09:02

Re: Summenprodukt für bedingte Summe - Bedingung kann fehle

Beitragvon shift-del » 30. Nov 2020, 16:59

Moin

Jack's drittes Postulat: Lösungen mit SUMMENPRODUKT() oder INDIREKT() sind Mist.

Erstens verwende SUMMEWENNS():
Code: Alles auswählen
=SUMMEWENNS(Tab[Wert_1];Tab[Spalte_A];A17;Tab[Spalte_B];B17;Tab[Spalte_C];C17)

Zweitens für ein nicht vorhandenes Kriterium trägst du in die Zelle ein * ein.
Wir sehen uns!
... shift-del
Eine Mustertabelle hilft beim Helfen.
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
Benutzeravatar
shift-del
Moderator
 
Beiträge: 21384
Registriert: 25. Jan 2009, 22:54
Wohnort: Ehemaliges Fabrikgelände

Re: Summenprodukt für bedingte Summe - Bedingung kann fehle

Beitragvon wkosensky » 01. Dez 2020, 08:07

shift-del hat geschrieben:
Jack's drittes Postulat: Lösungen mit SUMMENPRODUKT() oder INDIREKT() sind Mist.

Danke für den Denkanstoß - ich habe tatsächlich ein Performance-Problem durch die volatile Funktion Indirekt(), weil die zu verarbeitende Tabelle sehr groß ist.

shift-del hat geschrieben:Erstens verwende SUMMEWENNS():
Code: Alles auswählen
=SUMMEWENNS(Tab[Wert_1];Tab[Spalte_A];A17;Tab[Spalte_B];B17;Tab[Spalte_C];C17)

Zweitens für ein nicht vorhandenes Kriterium trägst du in die Zelle ein * ein.

Danke für den Tipp, so etwas halbwegs "elegantes" hab ich gesucht. Leider funktioniert es aber noch nicht - es kommt "0" raus. Es liegt wohl an den *.

Hättest Du vielleicht eine Lösung dafür? Ich gebe einen aus!

VG
W.
wkosensky
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 8
Registriert: 28. Sep 2020, 11:40

Re: Summenprodukt für bedingte Summe - Bedingung kann fehle

Beitragvon shift-del » 01. Dez 2020, 09:10

In deiner Beispielmappe hat es funktioniert. Aber vermutlich sieht deine Original-Mappe gaaaaanz anders aus.
Wir sehen uns!
... shift-del
Eine Mustertabelle hilft beim Helfen.
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
Benutzeravatar
shift-del
Moderator
 
Beiträge: 21384
Registriert: 25. Jan 2009, 22:54
Wohnort: Ehemaliges Fabrikgelände

Re: Summenprodukt für bedingte Summe - Bedingung kann fehle

Beitragvon wkosensky » 01. Dez 2020, 10:34

shift-del hat geschrieben:In deiner Beispielmappe hat es funktioniert. Aber vermutlich sieht deine Original-Mappe gaaaaanz anders aus.


Ok, stimmt, in meinem Beispiel funktioniert es. Problem ist wohl, dass in der Originalmappe die Attribute auch mal Zahlen sein können und nicht nur Text. Bei Zahlen funktioniert wohl der * als Platzhalter nicht.
Gibt es dafür einen eleganten Workaround?
wkosensky
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 8
Registriert: 28. Sep 2020, 11:40

Re: Summenprodukt für bedingte Summe - Bedingung kann fehle

Beitragvon shift-del » 01. Dez 2020, 10:45

Probiere mal bei den Zahlen den Eintrag >=0.
Wir sehen uns!
... shift-del
Eine Mustertabelle hilft beim Helfen.
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
Benutzeravatar
shift-del
Moderator
 
Beiträge: 21384
Registriert: 25. Jan 2009, 22:54
Wohnort: Ehemaliges Fabrikgelände

Re: Summenprodukt für bedingte Summe - Bedingung kann fehle

Beitragvon slowboarder » 01. Dez 2020, 10:58

Hi

die verschiedenen Joker und Vergleiche als Kriterium zählen folgendes:
"*" und "?" zählt nur Texte
">0" , "<0" zählt nur Zahlen
"<>" zählt alles, was einen Inhalt hat, Texte und Zahlen, aber keine Leerzellen.
"=" zählt Leerzellen

Gruß Daniel
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 27563
Registriert: 18. Apr 2009, 13:33

Re: Summenprodukt für bedingte Summe - Bedingung kann fehle

Beitragvon wkosensky » 01. Dez 2020, 11:05

shift-del hat geschrieben:Probiere mal bei den Zahlen den Eintrag >=0.


OK, das funktioniert jetzt. Nett, elegant genug! :-) Danke Dir dafür - das hilft mir sehr! Schicke mir doch gern per PN mal eine Möglichkeit, mich erkenntlich zu zeigen (Paypal, etc.)!!

Auch den anderen vielen Dank, aber die Lösungen waren zu kompliziert. Bei vielen Kriterien wäre das zu unübersichtlich.
wkosensky
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 8
Registriert: 28. Sep 2020, 11:40

Re: Summenprodukt für bedingte Summe - Bedingung kann fehle

Beitragvon wkosensky » 01. Dez 2020, 15:50

Gibt es eine Möglichkeit, dass ich nicht fest in die Summenwenns()-Funktion schreibe, welche Wert-Spalte ich summieren möchte, ohne indirekt() zu benutzen? D. h. ich möchte in einem anderen Feld festlegen, welche Wert-Spalte summiert werden soll.
wkosensky
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 8
Registriert: 28. Sep 2020, 11:40

Re: Summenprodukt für bedingte Summe - Bedingung kann fehle

Beitragvon shift-del » 01. Dez 2020, 16:07

Es ist mir nicht klar warum du so unsystematisch Werte summieren willst.

Besser wäre es wenn es ein System gäbe dann ließe sich das bestimmt mit einer Pivot darstellen.
Wir sehen uns!
... shift-del
Eine Mustertabelle hilft beim Helfen.
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
Benutzeravatar
shift-del
Moderator
 
Beiträge: 21384
Registriert: 25. Jan 2009, 22:54
Wohnort: Ehemaliges Fabrikgelände

Re: Summenprodukt für bedingte Summe - Bedingung kann fehle

Beitragvon slowboarder » 01. Dez 2020, 16:18

Hi

Auswählbare Spalten ohne Indirekt geht auch etwas aufwendiger mit Index

anstelle von Indirekt(Spaltenname) schreibst du Index(GanzeTabelle;0;Vergleich(SpaltenÜberschrift;ÜberschriftenZeile;0))

Gruß Daniel
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 27563
Registriert: 18. Apr 2009, 13:33

Re: Summenprodukt für bedingte Summe - Bedingung kann fehle

Beitragvon wkosensky » 01. Dez 2020, 20:14

shift-del hat geschrieben:Es ist mir nicht klar warum du so unsystematisch Werte summieren willst.

Besser wäre es wenn es ein System gäbe dann ließe sich das bestimmt mit einer Pivot darstellen.


Das System gibt es schon: um beim Bsp. zu bleiben --> Anzahl aller verkauften Mercedes-Teile, Anzahl aller verkauften Mercedes-Lichter, Anzahl aller verkauften Lichter (egal ob Mercedes oder VW), Anzahl aller verkauften hochwertigen Teile etc... Nur das ich in echt viel mehr Attribute habe, die mir mein gesamtes Volumen (die Wert-Spalten) immer genauer erklären.
Meine Letzte Frage nach der variablen Wert-Spalte zielte darauf ab, dass ich eben das eine Mal die Anzahlen addieren möchte, und dann auch mal die Verkaufspreise - mit den gleichen Kriteren wie bei Anzahlen.

Mit einer Pivottabelle kann ich doch immer nur eine Kombination von Attributsausprägungen wählen und damit verschiedene Werte-Spalten addieren. Ich möchte aber von unterschiedlichsten Attributskombinationen die addierten Wert-Spalten übersichtlich darstellen.
wkosensky
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 8
Registriert: 28. Sep 2020, 11:40


Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 11 Gäste