Häufigster Wert eines Bereichs ausgeben (auch Text)

Mit den Formeln Anzahl oder Zählenwenn können bestimmte Werte gezählt werden. Doch was, wenn unzählige verschiedene Werte in einem Bereich aufgeführt sind und wir nur den häufigsten davon auslesen wollen? Nachfolgend sind unsere Daten, aus welchen wir den häufigsten Wert auslesen möchten (die Farben hatte ich vorgängig mit bedingten Formatierungen erstellt):

datenhaeufigsterwert

Nebenan möchten wir nun ermitteln, welches denn der häufigste Wert ist. Aufgrund der Farben - und da die Liste nur 11 Angaben umfasst - könnten wir auch von Auge ermitteln, dass es "Wert2" ist. Dennoch: Wir möchten dies mit einer Formel ermitteln. Diese lautet:

{=INDEX(A1:A11;VERGLEICH(MAX(ZÄHLENWENN(A1:A11;A1:A11));ZÄHLENWENN(A1:A11;A1:A11);0))}

Die geschweifte Klammer ist nicht selbst einzugeben - es handelt sich um eine Matrixformel! Schliesse die Formel mit CTRL + SHIFT + ENTER ab, um die Matrixformel zu bestätigen.

Das Resultat: Wert2 ist am häufigsten im Bereich "A1:A11". Sobald Du die Werte änderst (bspw. mehr "Wert3" im Bereich aufführst), zeigt Dir die Formel sofort den neuen Wert an.

SVERWEIS nach links

Mit der Formel "SVERWEIS" wird ja bekanntlich die erste Spalte Deiner Matrix nach Deinem Suchkriterium durchsucht. Sobald ein Treffer gefunden wurde, wird der Wert aus derjenigen Spalte laut Deines "Spaltenindex" ausgegeben. Schauen wir uns hierzu ein konkretes Beispiel an:

SVERWEIS1

Suchen wir also nach "Eisbär" und verwenden als Spaltenindex die 2, wird "Arktis" als Ergebnis unserer Formel angegeben. Die Formel "SVERWEIS" verläuft also stets nach rechts. In diesem Beitrag möchten wir jedoch eine Möglichkeit anschauen, wie wir beispielsweise nach "Arktis" suchen können und dann "Eisbär" als Resultat erhalten - also einen "SVERWEIS nach links"!

Folgendes können wir im Vornherein definieren: Unsere Suchspalte ist in diesem Fall B, die Ergebnisspalte ist A. Unser Suchbegriff lautet "Arktis". Wir verwenden nun jedoch nicht die Formel "SVERWEIS" (Einen negativen Spaltenindex wie beispielsweise -1 wird leider nicht toleriert.) sondern die flexiblere Version "=INDEX". Anders als bei der herkömmlichen Formel müssen wir zuerst die Ergebnisspalte definieren - "A:A". Für die Ermittlung der korrekten Zeile verwenden wir "VERGLEICH("Arktis";B:B;0)". Zusammengesetzt und vollständig lautet die Formel demnach:

=INDEX(A:A;VERGLEICH("Arktis";B:B;0))

... und das Resultat: "Eisbär"! Mittels diesen beiden Formeln kannst Du also auch eine Suche nach links durchführen.

Hattest Du schon einmal nach dieser Methode gesucht oder gar Deine Tabelle umgestellt, damit Du nach wie vor den "SVERWEIS" anwenden konntest? Fragen oder Anregungen kannst Du wie immer sehr gerne in die Kommentare schreiben.