Excel-TippDie drei besten oder schlechtesten Werte automatisch filtern
In der täglichen Arbeit mit Excel stehen wir oft vor der Herausforderung, aus großen Datenmengen die relevantesten Informationen herauszufiltern. Besonders wenn es darum geht, aus einer Liste die besten oder schlechtesten Werte zu ermitteln, kann das manuelle Suchen zeitaufwendig und fehleranfällig werden. Für diesen Zweck bietet Excel verschiedene Möglichkeiten, diese Aufgabe zu automatisieren.
Beispiel: Flexible Auswertung von Kennzahlen
In der folgenden Abbildung sehen Sie eine Tabelle mit wichtigen Kennzahlen eines Konzerns, der in verschiedenen Ländern geschäftliche Aktivitäten verfolgt.
Aus dieser Liste sollen nun immer die drei Einträge mit dem größten prozentualen Wachstum gegenüber dem Vorjahr automatisch an eine andere Stelle gefiltert werden. Die gefilterte Werte sollen hierbei absteigend sortiert dargestellt werden. Ändern sich die Werte in der Liste, dann sollen sich die gefilterten Werte automatisch anpassen.
Die drei größten Werte aus einer Liste filtern und anzeigen
Diese Aufgabenstellung können Sie mit der folgenden Formel lösen:
=SORTIEREN(FILTER(A2:D11;D2:D11>=KGRÖSSTE(D2:D11;3));4;-1)
Die Formel geht hierbei von den folgenden Annahmen aus:
- A2:D11 – Bereich der gesamten Liste, aber ohne die Überschriftenzeile
- D2:D11 – Bereich in der Liste, in welcher die Suchkriterien stehen
Schauen wir uns die Funktionsweise der Formel von innen nach außen an.
Mit der Funktion FILTER(A2:D11;D2:D11>=KGRÖSSTE(D2:D11;3)) wird zunächst die Liste nach den drei größten Wachstumswerten durchsucht. Das „Geheimnis“ liegt hier im zweiten Argument der Filterfunktion („einschließen“), in dem Sie die Suchkriterien definieren.
Mit KGRÖSSTE(D2:D11;3) ermitteln Sie zunächst den drittgrößten Wachstumswert.
Hinweis: Wollen Sie anstelle der drei größten die fünf größten Werte ermitteln, dann tauschen Sie einfach das zweite Argument in der Funktion KKGRÖSSTE() entsprechend aus KGRÖSSTE(D2:D11;3). Für die 3 setzen Sie die Zahl 5 ein.
Anschließend vergleichen Sie diesen Wert mit jedem Wachstumswert im Bereich D2:D11; das ist das zweite Argument der Filterfunktion: D2:D11>=KGRÖSSTE(D2:D11;3)
Ist der Wert größer oder gleich dem drittgrößten Wachstumswert, dann wird die entsprechende Zeile mit der Funktion FILTER() gefiltert.
Die drei gefilterten Werte werden dann an die Funktion SORTIEREN() übergeben, welche die gefilterten Werte absteigend nach der Wachstumsrate sortiert.
=SORTIEREN(FILTER(A2:D11;D2:D11>=KGRÖSSTE(D2:D11;3));4;-1)
Die drei kleinsten Werte aus einer Liste filtern und anzeigen
Wollen Sie sich die drei schlechtesten Werte anzeigen lassen, dann müssen Sie die Formel nur wie folgt anpassen:
=SORTIEREN(FILTER(A2:D11;D2:D11<=KKLEINSTE(D2:D11;3));4;1)
Hier wird zunächst mit der Funktion KKLEINSTE() der drittkleinste Werte ermittelt. Anschließend werden die Listeneinträge gefiltert, deren Wert kleiner oder gleich dem drittkleinsten Wert ist.
