Das Minimum einer Reihe mit Bedingung
Gegeben sei eine Spalte mit Zahlen und eine weitere Reihe mit Kennzeichen, hier sind es Buchstaben:
Die Daten
Es soll das Minimum aller Zahlen in Spalte A berechnet werden, wo in der gleichen Zeile, Spalte B kein X steht. Die Farblichen Hinterlegungen dienen hier nur der Verdeutlichung. Die insgesamt niedrigste Zahl steht zwar in A9, aber die soll wegen des „X” in B9 nicht mit gewertet werden. Darum ist 216 in A6 der korrekte Wert. In dieser Datei finden Sie die hier dargestellten Lösungen auch in einem Tabellenblatt wieder.
Lösung 1 – Array-Formel
Die wohl einfachste Formel sieht so aus:
=MIN(WENN(($B$1:$B$11<>"X")*($A$1:$A$11>0); $A$1:$A$11))
Wichtig ist aber, dass Sie die Formel nicht normal mit Eingabe sondern unbedingt mit StrgShiftEingabe abschließen. Dadurch werden automatisch geschweifte Klammern { } um die eigentliche Formel gesetzt und diese wird so zu einer Array-Formel.
Einschränkung: Bei größeren Datenmengen kann eine Array-Formel enorm ausbremsen und das Arbeiten mit dem Blatt extrem langsam machen. Da sollten Sie dann über eine der folgenden Möglichkeiten nachdenken.
Lösung 2: Datenbank-Funktionalität
Excel hat ja auch einige Datenbank-Funktionen. Zugegeben, Excel ist weit von einer „echten” Datenbank entfernt, aber mitunter sind die DB-Funktionen ganz nutzbringend. Dazu ist es erst einmal wichtig, dass Sie einen Bereich für das Kriterium (oder auch Kriterien) schaffen. Genau so wie beim Erweiterten Filter muss die oberste Zeile des Bereichs die gleiche, die identische Überschrift haben wie der zu vergleichende Bereich der Daten. Das ist in der Mustertabelle in C1 realisiert. Direkt darunter wird die Bedingung eingetragen, hier: ="<>X". In die Ziel-Zelle, wo das Ergebnis der Berechnung erscheinen soll, geben Sie nun folgende Formel ein:
=DBMIN(A1:C11; "Zahl"; C1:C2)
Das Ganze ist auf den ersten Blick vielleicht etwas abstrakt. Beim ersten Argument wird der Datenbank-Bereich eingegeben, dann die Überschrift der auszuwertenden Spalte und letztendlich der Bereich mit den Kriterien. Das wird vielleicht etwas transparenter für Sie, wenn Sie sich die Lösung 2a auf dem Blatt Tabelle2 ansehen. Unabhängig davon, dass der Datenbank-Bereich auch als Liste formatiert worden ist haben wir diesem Bereich den Bereichsnamen Datenbank vergeben. Und D1:D2 hat den Bereichsnamen Kriterien. Die Formel ist nun (aus unserer Sicht) plötzlich viel deutlicher:
=DBMIN(Datenbank; 1; Kriterien)
Statt der 1 (für die erste Spalte) als zweitem Kriterium können Sie natürlich auch wieder die Überschrift (in Anführungszeichen) einsetzen. Und um Sie nicht zu sehr zu verwirren, haben wir auf die Funktionalität der Liste/Intelligenten Tabelle verzichtet und nur die vergebenen Bereichsnamen verwendet.
Lösung 3: AGGREGAT()-Funktion
Erstmals in Excel 2010 konnten Sie die AGGREGAT()-Funktion einsetzen. Eine sehr schöne und auch mächtige neue Funktion. Wenn Sie die Formel per Hand eingeben, werden Ihnen automatisch in einer Art Kontext-Hilfe alle Möglichkeiten eingeblendet, welche Sie dann an der Stelle auch auswählen können. Unsere Formel sieht so aus:
=AGGREGAT(5; 5; $A$1:$A$11)
Es irritiert Sie, dass an keiner Stelle abgefragt wird, ob in Spalte B ein „X” steht? Was wir Ihnen bis hierher verschwiegen haben: Der Datenbereich muss gefiltert werden. Und durch das zweite Funktions-Argument legen Sie fest, dass gefilterte Zeilen bei der Auswertung nicht mit berücksichtigt werden. Also: Sie müssen den Filter in Spalte B aufrufen und das Häkchen beim X entfernen. Dann stimmt auch die Berechnung.
In Tabelle2 ist in G17 eine modifizierte Form der Formel verwendet worden, das dritte Argument ist der Listen-Bereich. So wird sich der auszuwertende Bereich immer anpassen, wenn sich die Größe der Intelligenten Tabelle verändert.
Außer Konkurrenz
Es ist auch durchaus möglich, eine Auswertung mit einer PivotTable zu erstellen. Im Arbeitsblatt Tabelle2 ist beginnend in E19 eine solche Auswertung eingefügt.
Eine Lösung per VBA ist denkbar soll hier aber nicht betrachtet werden.
[NachObenLetzte Verweis=„Wege nach Rom: Bedingtes MinMax”]