Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Summenfunktionen

Beitrag: Maximum aus mehreren Einzelwerten mit Zusatzbedingung*

Aufgabe
Aus mehreren Einzelumsätzen soll ermittelt werden, wer für einen bestimmten Artikel den höchsten Umsatz erzielt hat. Dabei sollen Hilfsspalten vermieden werden.
Im Beispiel für den Artikel "c" ist das WF: € 255,- + € 600,- = € 855,-

       A               B               C               D               E               F       
1 Name  Artikel  Umsatz    Artikel  c  
2 Jens  a  300,00    Top-Seller:  WF  
3 WF  c  255,00       
4 WF  c  600,00       
5 WF  a  144,00       
6 Boris  c  266,00       
7 Boris  c  320,00       
8 Jens  c  544,00       
9 Jens  a  266,00       

Lösung
In F1 wird der gewünschte Artikel eingegeben.
In F2 gibt´s dann die Lösung mit:

{=INDEX(A2:A9;VERGLEICH(MAX(MMULT((MTRANS(A2:A9)=A2:A9)*(MTRANS(B2:B9=F1))*MTRANS(C2:C9);ZEILE(2:9)^0));MMULT((MTRANS(A2:A9)=A2:A9)*(MTRANS(B2:B9=F1))*MTRANS(C2:C9);ZEILE(2:9)^0);))}

Dass die neueren Excelversionen (seit xl2007) auch ihre Vorzüge haben, zeigt die vereinfachte Lösung:

{=INDEX(A2:A9;VERGLEICH(MAX(SUMMEWENNS(C2:C9;B2:B9;F1;A2:A9;A2:A9));SUMMEWENNS(C2:C9;B2:B9;F1;A2:A9;A2:A9);0))}

Wie gesagt: Erst ab xl2007, da die Funktion SUMMEWENNS zum Einsatz kommt.
Logischerweise ist diese Lösung nicht abwärtskompatibel.

Ergänzung vom 01.11.2012:

Wenn man nur wissen möchte, wer der Top-Seller ist (ohne die Zusatzbedingung für einen bestimmten Artikel in Spalte B), kann man die deutlich einfachere 2007er Formel ohne "S-Erweiterung" bei SUMMEWENN auch in allen Excelversionen verwenden.

Die Summe aller Umsätze des Top-Sellers erhält man dann mit:
{=MAX(SUMMEWENN(A2:A9;A2:A9;C2:C9))}
Ergebnis: 1.110

und den dazugehörigen Namen mit:
{=INDEX(A2:A9;VERGLEICH(MAX(SUMMEWENN(A2:A9;A2:A9;C2:C9));SUMMEWENN(A2:A9;A2:A9;C2:C9);0))}
Ergebnis: Jens

Erläuterung
ZEILE(2:9)^0 steht für die Anzahl der Umsätze (hier 8). Dadurch wird das passende Einserarray für MMULT erzeugt, denn jede Zahl hoch 0 ergibt 1.
Somit erhält man das Array:
{1;1;1;1;1;1;1;1}

Bei anderen Datensätzen entsprechend anpassen/erhöhen auf z.B. ZEILE(29:317)^0.