[Excel] Maximum, Minmum (Advanced) - Extremwerte unter bestimmten Bedingungen

Gepostet am: Oct 08, 2011 10:13:59 AM

In diversen Bereichen der Betriebswirtschaft aber auch in anderen Bereichen müssen immer wieder Maxima bzw. Minima berechnet werden. Das ist grundsätzlich in Excel sehr einfach, daher möchte ich darauf nicht näher eingehen. In diesem Artikel möchte ich hingegen einige spezielle Anwendungsfälle von Minima und Maxima zeigen. Falls spezielle Anforderungen an die Minima und Maxima-Funktionen hat, kann man mich gerne kontaktieren. 

Grundüberlegung

Erst im Zusammenspiel mit Matrixformeln, entwickeln die Funktionen Minimum und Maximum ihre wirkliche Größe. 

Ausgangspunkt ist eine Tabelle mit Gewinnen bzw. Verlusten, wobei die Jahre 4 & 5 nicht einen Gewinn von 0 haben, sondern für die Erfassung von zukünftigen Jahren vorgesehen sind.

Würde man über die Gewinne/Verluste ein Minimum bilden, wäre dies in diesem Fall 0, da im Jahr 4,5 Null (0) in den Zellen steht. Will man diese 0 jedoch ignorieren, wäre das Minimum 2000.

Daher muss man in einem ersten Schritt den Bereich für die Minimum/Maximum-Berechnung um die Zellen, die 0 enthalten bereinigen.

In einem nächsten Schritt bildet man nun über die bereinigten Werte das Minimum oder Maximum

Zusammenfassung:  Will man bestimmte Zahlenbereiche oder einzelne Zahlen ausschließen, kann man folgende Matrixformel verwenden:

{=MIN(WENN((BereichVergleichsOperatorVergleichswert);Bereich;"irgendetwas, das keine Zahl ist"))}

zB:

{=MIN(WENN((Bereich>0);Bereich;">0"))}     Minimum aller Zahlen, die größer als 0 (>0) sind

{=MIN(WENN((Bereich<>0);Bereich;"<>0"))}   Minimum aller Zahlen, die ungleich 0 (<>0) sind

usw.

Was ist aber, wenn es möglich ist, dass wirklich 0 ein gültiger Gewinn/Verlust ist.

In diesem Fall, würde man - bei der Anwendung der oben erwähnten Formeln - einen Minimalen Gewinn von 2000 bekommen, obwohl er im 2. Jahr 0 ist. In diesem Fall, müsste man in einer Zelle erfassen, bis zu welchem Jahr die Werte berücksichtigt werden sollen und welche nur als Platzhalter dienen. 

Im konkreten Fall habe ich das folgendermaßen gelöst: Man vergleicht das Jahr mit den Feld "betrachtete Jahre" - wenn das Jahr <= die betrachteten Jahre ist, berücksichtigt man den Gewinn/Verlust, sonst ignoriert man ihn. Über die bereinigten Werte wird dann das Minimum/Maximum gebildet. 

eine andere Herangehensweise:

Maximum mit Bedingung

Die Matrix-Formel {=MAX(WENN(Zellbereich1=XY;Zellbereich2;0))} führt dazu dass die Zellen bei denen nicht in der Zelle daneben der Text "inkludieren" durch den Wert 0 ersetzt werden. Daraus ergibt sich auch gleich die Einschränkung. Die Formel funktioniert nicht, wenn ein Maximum von negativen Zahlen gebildet werden soll. (siehe dazu nachfolgendes Bild)

Für dieses Szenario müsste man die Formel ein klein wenig ändern. Es muss der Standardwert (im oben gezeigten Beispiel 0) durch einen Wert ersetzt werden, der kleiner ist als das Maximum. Das kann man damit erreichen, indem man 0 durch das Minimum des Bereichs ersetzt.

Maximum ohne aktuelle Zeile

Eine weitere Fragestellung der ich mich widmen möchte, wie man beim die aktuelle Zeile bei der Berechnung des Maximums auslassen kann. Ausgangslage ist folgende Situation:

Die Eingabe derartiger Formeln ist jedoch mühsam und fehleranfällig. Man kann das ganze auch mittels Matrixformel lösen. Praktische Anwendung wäre die Berechnung von Alternativkosten bei der Berechnung der Opportunitätskosten.

Maximum innerhalb gewisser Grenzen

Verweis auf andere Zellen