@ > Home > Inhalt > Auswahl des Datensatzes mit mehreren nachrangigen Extrema Excel 97+

Aufgabe: 

Von den Sätzen mit frühestem Datum soll einer mit größter Anzahl wiedergegeben werden, für den Summe minimal ist. Gibt es auch hier noch mehrere Möglichkeiten, soll davon der Satz mit den meisten Fehlern genommen werden.

        A     B      C      D     
  1 Datum   Anzahl  Summe Fehler  
  2 01.04.08  1     78      0     
  3 01.04.08  2     45      0     
  4 01.04.08  1     67      0     
  5 01.04.08  1     68      1     
  6 01.04.08  2     89      2     
  7 01.04.08  1     91      1     
  8 02.05.08  2     56      1     
  9 01.04.08  2     45      1     
 10 01.04.08  1     91      0     
 11 01.04.08  1     67      2     
 12                               
 13 Satz:     8  (in Zeile 9)

Lösung (geht nur für Zahlen!):

B13: =VERGLEICH(MIN(A2:A11-B2:B11%+C2:C11%%-D2:D11%%%);A2:A11-B2:B11%+C2:C11%%-D2:D11%%%;)

Die Formel ist als Matrixformel mit Strg-Umschalt-Eingabe einzugeben, sonst funktioniert sie nicht! Man beachte das letzte ; für unsortierte Daten.

Erklärung:

Die Hervorhebungs-Balken veranschaulichen, wie lange die gewünschten Extrema noch mehrdeutig sind. Erst in D:D werden sie eindeutig.

Das MIN bestimmt das früheste Datum (hier vorrangiges Kriterium; MAX ergäbe dann das späteste Datum). Gibt es davon mehrere und man möchte für einen Unterbegriff (hier "Anzahl" mit -B2:B11%) den größten Vertreter für das gegebene Datum, subtrahiert man die Spalte, versehen mit einem oder mehreren % (hier zählt übrigens als letztes Kriterium "Fehler" mit -D2:D11%%% auch dazu). Soll es hingegen der kleinste Wert sein (wie hier bei "Summe" mit +C2:C11%%), wird die Spalte addiert. Je mehr %-Zeichen, desto nachrangiger das Kriterium. Je nach Größe der Zahl in den Kriterien muss die Anzahl der % entsprechend zunehmen:

<=9 ergibt /10
<=99 ergibt %
<=999 ergibt %/10 oder /1000
<=9999 ergibt %% usw., jeweils zu den Zeichen höherer Ebenen hinzuzuzählen.

- Achtung: Die Anzahl 15 der effektiven Stellen einer Zahl in Excel begrenzt den Einsatz von Kriterien entsprechend. Hier sind es 11 Stellen.
- Achtung: Beginnt der Term mit MAX statt MIN, kehren sich alle Vorzeichen in ihrer Bedeutung um!
- Die Reihenfolge der Spalten ist natürlich beliebig, d.h. D könnte auch gegenüber C vorrangig sein, ausgedrückt durch weniger %-Zeichen.
- Die Lösung funktioniert auch für eine transponierte Aufgabenstellung (Zeilen statt Spalten und umgekehrt).
- Das Beispiel mit 4 Ebenen kann man zu 1 bis 15 Ebenen abändern, je nach Zahlenlängen.
- Die Vektoren lassen sich auch durch einfache Bezüge ersetzen, so dass nicht nach dem Extrem, sondern einem bestimmten Wert gesucht wird.