SVERWEIS() nach links mit Platzierung
Gegeben ist eine Liste mit 10 Mitarbeitern, welche die meisten Überstunden im Monat gemacht haben. In einem getrennten Bereich sollen nun die drei Mitarbeiter ausgegeben werden, welche die meisten Überstunden gemacht haben. Die Reihenfolge der Ursprungs-Liste soll nicht verändert werden. Das Ergebnis für die Top drei sollte dann so aussehen:
So soll das Ergebnis aussehen
Dabei sind grundsätzlich zwei Schritte zu vollziehen: Zuerst muss die „Platzierung” errechnet werden, der Wert kommt in Spalte E. Und anschließend wird in Spalte D passend zu dem gefundenen Wert der Name des Mitarbeiters bzw. der Mitarbeiterin hinein geschrieben.
Da nur die Top drei gesucht werden sollen, entfällt die Funktion RANG(). Damit wird ja aus der Gesamtheit der Mitarbeiter die Rangfolge festgelegt. Es müssten dann alle mit einem Rang bewertet werden und dann käme ein Filter zum Einsatz. Was bei dieser Form der Darstellung natürlich dazu führen würde, dass in Spalte A:B auch gefiltert wird, was dann wiederum nicht mit der gewünschten Darstellung des Ergebnisses übereinstimmt. Darum findet die Funktion KGRÖSSTE() hier Anwendung. In Spalte E werden mit dieser Funktion zuerst die drei höchsten Werte der Überstunden in Spalte B berechnet. Die Formel dazu können Sie in dieser Datei nachschauen.
Jetzt muss eigentlich nur noch in Spalte D der passende Name eingetragen werden. Die erste Idee wird vielfach sein: SVERWEIS() und dann nach links suchen. Aber bekanntermaßen geht das nicht. Aber die Funktion VERWEIS() kann das im gewissen Rahmen, denn dort werden das Suchkriterium und der Ergebnisvektor durchaus getrennt gesehen. Prinzipiell geht das so: Suche den Wert x in Spalte B und gebe als Ergebnis den Wert der Fund-Zeile, Spalte A zurück. Das funktioniert auch unter gewissen, definierten Bedingungen, aber Sie werden dennoch hier ein fehlerhaftes Ergebnis bekommen. Und das liegt daran, dass der Such-Bereich in aufsteigender Reihenfolge sortiert sein muss. Ohne Ausnahme. Und das ist hier offensichtlich nicht gegeben.
Also ist es erforderlich, einen anderen Weg zu finden. Und der erschließt sich, wenn Sie die viel zu stark unterschätzte Kombination von VERGLEICH() und INDEX() einsetzen. Noch einmal zur Verdeutlichung: Die drei Werte, welche in Spalte E berechnet worden sind, existieren auf jeden Fall exakt so in Spalte B. Ich kann also in Spalte B den Wert aus D2 suchen und werde auf jeden Fall fündig. Und dazu nutze ich dann die Excel-Funktion VERGLEICH():
=VERGLEICH(E2; $B$2:$B$11; 0)
„Übersetzt” heißt das: Suche den Wert aus E2 ; im Bereich $B$2:$B$11 ; und beachte eine exakte Übereinstimmung. Diese Funktion gibt einen numerischen Wert zurück, nämlich die Position der Fundstelle innerhalb des angegebenen Bereichs. In diesem Fall ist es die 4, weil der Suchwert 21:04 an vierter Position im benannten Bereich (die Hilfe spricht von „Vektor”) gefunden wird. Beachten Sie, dass die gefundene Zeile nicht identisch ist mit der Zeilennummer der Tabelle, denn der Bereich beginnt hie in Zeile 2.
Als Ergebnis soll nun ja in Spalte A die gleiche (relative) Position gefunden werden, also im Bereich A2:A11 die vierte Zeile. Das wiederum geht mit der Excel-Funktion INDEX(). Auch das können Sie ganz einfach probieren, indem Sie an beliebiger Position des Tabellenblattes die Formel =INDEX(A2:A11; 4) eingeben. Das Ergebnis entspricht den Erwartungen. Bleibt nur noch das vereinen der beiden einzelnen Funktionen zu einer einzigen Formel:
=INDEX($A$2:$A$11; VERGLEICH(E2; $B$2:$B$11; 0))
Die Dollarzeichen in den Such- und Ergebnisvektoren sorgen dafür, dass Sie die Formel nach unten kopieren können. Das Ergebnis wird immer richtig sein, da (im Beispiel) E2 nicht durch ein „$” absolut gesetzt wurde, die Adresse ist relativ und wird entsprechend automatisch angepasst.
Hinweis: An der (1) in der Überschrift haben Sie gewiss erkannt, dass es nicht bei diesem einen Beitrag zu dieser Thematik bleiben wird. Das ist richtig. Das eine oder andere Beispiel wird in nicht allzu langer Zeit folgen. Versprochen.