Beim Auswerten von Daten sind immer wieder andere Aspekte wichtig. Mal geht es um die höchsten, ein anders Mal die niedrigsten Werte. Die folgende Lösung ist auf wechselnde Szenarien perfekt vorbereitet. Über das Dropdownfeld in F3 werden mit nur je zwei Mausklicks alle Werte hervorgehoben,
- die entweder zu den obersten 5
- oder zu untersten 5 gehören.
Für die Lösung sind ein Dropdown sowie Regeln der bedingten Formatierung einzurichten.
So wird die Lösung aufgebaut
- Die Auswahl, welche Art der Auswertung gebraucht wird, ermöglicht ein Dropdownfeld.
- Der Inhalt der Dropdown-Liste richtet sich nach den gewünschten Auswertungskriterien. Hier im Beispiel sind es zwei, es können aber auch mehr sein.
- Für jede der beiden Auswertungsarten wird dann eine eigene Regel für die bedingte Formatierung angelegt.
- Beide Regeln beruhen auf Formeln, die mit dem ausgewählten Text in der Dropdownliste kombiniert werden.
Die Dropdownliste anlegen
- Über der Dropdownliste in Zelle F2 wird ein erläuternder Text eingetragen
»Hier die Auswertungsart wählen«. - Der Pfeil wird über Einfügen > Symbol aus dem Zeichensatz Wingdings erzeugt.
- Dann wird in Zelle F3 über Daten > Datenüberprüfung die Dropdownliste angelegt. Die Einstellungen sind im folgenden Bild zu sehen.
Formeleingabe erleichtern mit einem Bereichsnamen
- Mit einem Bereichsnamen lassen sich die Formeln für die bedingte Formatierung leichter aufstellen. Daher wird der Bereich C3:C18 markiert.
- Mit Strg+Umschalt+F3 wird das Dialogfeld Namen aus Auswahl erstellen aufgerufen. Nach einem Klick auf OK ist der Bereichsname Bestellungen definiert.
Die Regeln für die 5 höchsten/niedrigsten Werte
Zum Kennzeichnen der fünf höchsten bzw. niedrigsten Bestellwerte werden in den Regeln der bedingten Formatierung die Funktionen KGRÖSSTE und KKLEINSTE verwendet.
- Nach dem Markieren des Bereichs von B4 bis C18 wird über Start > Bedingte Formatierung > Neue Regel das unten gezeigte Dialogfeld geöffnet.
- Unter Regeltyp auswählen wird der letzte Eintrag angeklickt: Formel zur Ermittlung der zu formatierenden Zellen verwenden.
- Unter Werte formatieren, für die diese Formel wahr ist, wird dann die folgende Formel eingegeben:
=UND($F$3=“Die obersten 5″; $C4>=KGRÖSSTE(Bestellungen;5)) - Per Klick auf die Schaltfläche Formatieren werden dann im Register Schrift bei Schriftschnitt > Fett sowie eine kontrastierende Schriftfarbe eingestellt.
- Eine zweite Regel steuert, dass die 5 niedrigsten Werte gekennzeichnet werden.
Die Formel lautet diesmal:
=UND($F$3=“Die untersten 5″; $C4>=KKLEINSTE(Bestellungen;5))
Gut zu wissen:
So funktionieren KGRÖSSTE und KKLEINSTE
- Die Funktion KGRÖSSTE ermittelt den k-größten Wert einer Datengruppe.
- Als erstes Argument benötigt die Funktion den auszuwertenden Datenbereich.
- Das zweite Argument k gibt dann an, die wievielt größten Werte zu ermitteln sind.
- Die Funktion KKLEINSTE arbeitet nach dem gleichen Prinzip.
Fazit
Das Ergebnis der technischen Vorarbeiten ist nun auf einen Blick im unten abgebildeten Regel-Manager der bedingten Formatierung zu sehen.
Über das Dropdownfeld in Zelle F3 lassen sich ab sofort entweder die 5 höchsten oder niedrigsten Werte optisch hervorheben.
Umsetzbar sind mit dieser Lösung auch weitere Auswertungsarten wie beispielsweise
- Alle Werte über oder unter dem Durchschnitt sowie
- die jeweiligen Maximal- oder Minimalwerte.
