Min Max Umsatz in Abhängigkeit berechnen

Bild

Betrifft: Min Max Umsatz in Abhängigkeit berechnen
von: Thomas
Geschrieben am: 18.06.2015 15:16:16

Hallo,
ich habe zur Verdeutlichung des Problems eine Beispieltabelle erstellt:
Userbild
Die Tabelle enthält Umsatzdaten je Produkt (Spalten B, C und D), sowie den Gesamtumsatz (Spalte E) und noch eine weitere Spalte für die Artikel-Art (Spalte F).
Berechnet werden sollen vier Werte:
Maximaler Umsatz der Artikel Art A (Zelle I1),
minimaler Umsatz der Artikel Art A (Zelle I2),
maximaler Umsatz der Artikel Art A (Zelle I4) und
minimaler Umsatz der Artikel Art A (Zelle I5).
Mein Problem: Wenn Umsatzdaten noch nicht vollständig vorliegen, in diesem Fall für Produkt 2 und Produkt 3 am Verkaufstag 4 (Zellen C5 und D5), soll der Gesamtumsatz bei der Ermittlung der obigen Werte nicht berücksichtigt werden.
In Zelle I2 erwarte ich also nicht den Wert 30, weil die Zellen C5 und D5 noch nicht gefüllt sind, sondern den Wert 210, da dies der geringste Wert für A ist, unter der Voraussetzung, dass alle Umsatzdaten in der Zeile gefüllt sind.
Mit mehreren WENN-Verschachtelungen käme ich zu einer Lösung, jedoch gibt es mehr als drei Produkte (oben nur eine Beispieltabelle) und ich kann nur sieben WENN-Funktionen verschachteln. Zudem finde ich mehrere WENN-Verschachtelungen unübersichtlich und auf Dauer nicht nachvollziehbar.
Hat jemand eine Lösung?
Grüße
Thomas
P.S.: Die Formeln lauten aktuell (jeweils als Matrixformel!):
I1: =MAX(WENN(F:F="A";E:E;0))
I2: =MIN(WENN(F:F="A";WENN(E:E<>0;E:E)))
I4: =MAX(WENN(F:F="B";E:E;0))
I5: =MIN(WENN(F:F="B";WENN(E:E<>0;E:E)))

Bild

Betrifft: am einfachsten & schnellsten ist PIVOTauswert. ..
von: der neopa C
Geschrieben am: 18.06.2015 15:26:41
Hallo Thomas,
... dazu sind nur wenige Mausklicks notwendig.
Wenn Du allerdings eine Formellösung anstrebst dann bedarf es hier auch keiner Matrixformeln.
Stelle Dein Bild als Datei ein und wir können es Dir schnell aufzeigen.
Gruß Werner
.. , - ...

Bild

Betrifft: ...und zwar mit QlikView von QlikTec, jederzeit...
von: Luc:-?
Geschrieben am: 18.06.2015 15:39:43
…auf beliebige Diagramm-Darstellung per Klick umschaltbar und mit Xl-ExportFunktionalität.
Gruß, Luc :-?

Besser informiert mit …

Bild

Betrifft: mit Hilfsspalte G z.B.
von: Michael
Geschrieben am: 18.06.2015 17:37:54
Hi zusammen,
=Zählenwenn(B2:D2;"")
Die Überprüfung, ob G=0 setzt Du in Deine vorhandenen Formeln mit ein, z.B. so:
{=MIN(WENN(F:F="A";WENN(G:G=0;WENN(E:E<>0;E:E;" "))))} ergibt 210
Nebenbei: die verschachtelten Wenns nerven, aber UND zickt anscheinend.
Abgesehen davon: falls ein Artikel an dem Tag tatsächlich nicht verkauft wird, muß ne 0 rein.
Schöne Grüße,
Michael

Bild

Betrifft: in Kombination von AGGREGAT() mit MMULT() ...
von: der neopa C
Geschrieben am: 18.06.2015 19:00:19
Hallo Thomas,
... in einer kleinen MATRIXfunktion(alitäts)formel.
Auch wenn es keine Matrixformel ist (somit auch keiner {} bedarf) und somit auch schneller in der Auswertung als diese ist, sollte die Berechnung auch hier nicht über den gesamten Zeilenbereich vorgenommen werden sondern lediglich über den max. notwendigen Zeilenbereich.
Für drei Produkte lautet mein Auswertungsformelvorschlag:


=AGGREGAT(15;6;E2:E99/(F2:F99="A")/(MMULT((B2:D99>0)*1;{1;1;1})=3);1)
Diese Formel könnte natürlich noch verallgemeinert (beliebige Anzahl an Produkten) werden
Gruß Werner
.. , - ...

Bild

Betrifft: TE hat xl2003- wg max nested 7 WENN (owT)
von: lupo1
Geschrieben am: 19.06.2015 08:43:12
.

Bild

Betrifft: nein! TE hat XL2010 angeben, das andere ..
von: der neopa C
Geschrieben am: 19.06.2015 09:56:02
Hallo lupo,
... kann u.a. eine Vermutung oder "Altwissen" sein, weil er ja auch geschrieben hat:
"Zudem finde ich mehrere WENN-Verschachtelungen unübersichtlich und auf Dauer nicht nachvollziehbar". Anderenfalls sollte sich Thomas dazu äußern.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: nein! TE hat XL2010 angeben, das andere ..
von: Thomas
Geschrieben am: 19.06.2015 11:43:33
Hallo,
erst einmal vielen Dank an alle für die Antworten. Bis jetzt kam ich erst dazu diese kurz zu überfliegen.
Eine Möglichkeit das Problem mit Pivot zu lösen (oder gar womöglich dem erweiterten Filter?) kam mir auch in den Sinn, jedoch muss, so mein Wissensstand, die Pivot-Tabelle jedes mal händisch aktualisiert werden um das richtige Ergebnis zu bekommen.
Eine Formellösung wäre ideal. Ich schaue mir gleich mal die Vorschläge mit "ZÄHLENWENN()" und "
AGGREGAT() mit MMULT()" an. Mit letzteren habe ich bis dato noch keine Erfahrung.
Zum letzten Beitrag:
Hallo lupo,
... kann u.a. eine Vermutung oder "Altwissen" sein, weil er ja auch geschrieben hat:
"Zudem finde ich mehrere WENN-Verschachtelungen unübersichtlich und auf Dauer nicht nachvollziehbar". Anderenfalls sollte sich Thomas dazu äußern.
Gruß Werner
.. , - ...

Soll mir das einen Hinweis darauf geben, dass es mittlerweile keine oder eine höhere Begrenzung für die maximale Anzahl an WENN-Verschachtelungen gibt? Jedoch würde ich wenn möglich gerne darauf verzichten zu viele WENNs zu verschachteln, eben aus Gründen der Nachvollziehbarkeit.
Viele Grüße
Thomas

Bild

Betrifft: zu Deiner Zusatzfrage ...
von: der neopa C
Geschrieben am: 19.06.2015 12:32:38
Hallo Thomas,
... Du könntest in Deiner Excel-Version (20109 theoretisch mehr als nur 7 WENN()- Vierschachtelungen vornehmen. Ich hab mich allerdings für die neue Grenze nie interessiert, weil ich WENN()-Verschachtelungstiefen grundsätzlich anderweitig umgehen kann und auch niemanden eine größere Tiefe als max. 3 empfehlen würde.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: zu Deiner Zusatzfrage ...
von: Thomas
Geschrieben am: 19.06.2015 16:41:58
Hallo Werner,
danke für die Info. Das sehe ich ähnlich. Ich habe eben die AGGREGAT() mit MMULT()-Lösung erfolgreich getestet - vielen Dank dafür. Ich betrachte mein Problem damit als erledigt, werde es aber auch noch einmal mit der ZÄHLENWENN()-Variante probieren.
Alles in allem: Danke für die schnelle und kompetente Hilfe.
Grüße
Thomas

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Min Max Umsatz in Abhängigkeit berechnen"