Jim beschrieb eine Situation, in der er eine Liste mit Mitarbeiternamen und deren Gehältern hat. Er möchte herausfinden, wer die fünf bestbezahlten Mitarbeiter sind. Er verwendet die Funktion LARGE, um die fünf größten Gehälter zu identifizieren, und versucht dann, mit VLOOKUP die Namen dieser Gehälter zurückzugeben. Dies funktioniert einwandfrei, es sei denn, die fünf höchsten Gehälter sind doppelt vorhanden (die Leute erhalten das gleiche Gehalt). Wenn dies der Fall ist, gibt VLOOKUP nur den Namen des ersten Mitarbeiters bei diesem Gehalt zurück.

Um alle Eigennamen zurückzugeben, können Sie einige Dinge tun.

Eine Methode wäre, eine Formel insgesamt zu umgehen. Stattdessen können Sie die AutoFilter-Funktion in Excel verwenden:

  1. Wählen Sie eine beliebige Zelle in Ihrer Datentabelle aus.

  2. Wählen Sie Daten | Filter | Automatischer Filter. Excel fügt rechts neben jeder Spaltenüberschrift in der Tabelle Dropdown-Pfeile hinzu.

  3. Verwenden Sie die Dropdown-Liste oben in der Gehaltsspalte, um Top 10 auszuwählen. Excel zeigt das Dialogfeld Top 10 AutoFilter an. (Siehe Abbildung 1.)

  4. Stellen Sie den mittleren Regler von 10 auf 5 ein.

  5. Klicken Sie auf OK. Excel zeigt die fünf besten Gehälter in der Liste an.

Wenn Sie diese Schritte ausführen, werden möglicherweise mehr als fünf Datensätze angezeigt, insbesondere wenn die Gehälter der Mitarbeiter gebunden sind. Der Filter identifiziert die fünf höchsten Gehälter und zeigt dann alle Datensätze mit den entsprechenden Gehältern an.

Wenn Sie den AutoFilter nicht verwenden möchten, können Sie auch sicherstellen, dass jeder Datensatz in Ihrer Mitarbeiterliste etwas Einzigartiges enthält. Wenn sich beispielsweise die Mitarbeiternamen in Spalte B und die Gehälter in Spalte C befinden, können Sie die folgende Formel in Spalte A verwenden, um jeden Datensatz eindeutig zu machen:

=C2+ROW()/100000000

Dies addiert die Zeilennummer geteilt durch 100.000.000 und ergibt einen eindeutigen Wert. Wenn Sie (zum Beispiel) identische Gehälter von 98.765,43 in den Zeilen 2 und 49 in Spalte A haben, sind dies:

98765.43000002 98765.43000049

Die große Zahl (100.000.000) ist so, dass Sie, wenn Sie eine identische Zahl in Zeile 65536 hätten, Folgendes erhalten würden:

98765.43065536

Und selbst in diesem Fall wäre der auf 2 Dezimalstellen gerundete Wert immer noch die reelle Zahl. Wenn LARGE und VLOOKUP mit den „nicht eindeutigen“ Werten in Spalte A ausgeführt werden, geben Sie die größten Gehälter (und die zugehörigen Personen) zurück, basierend auf der Position der Person in der Liste.

Ein dritter Ansatz besteht darin, die Funktionen RANK und COUNTIF zu verwenden, um für jeden Wert in der Liste der Gehälter ein eindeutiges „Ranking“ zurückzugeben. Wenn die Gehälter im Bereich B1: B50 liegen, geben Sie Folgendes in Zelle C1 ein und kopieren Sie es in den Bereich:

=RANK(B1,$B$1:$B$50)+COUNTIF($B$1:B1,B1)-1

Sie können jetzt INDEX für die Ranglistenwerte verwenden, um den Namen zurückzugeben, der jedem Gehalt zugeordnet ist.

Schließlich besteht ein vierter Ansatz darin, ein Makro zu erstellen, das die gewünschten Informationen zurückgeben kann. Es gibt viele Möglichkeiten, wie ein Makro implementiert werden kann. Folgendes ist nur eines davon:

Function VLIndex(vValue, rngAll As Range, _   iCol As Integer, lIndex As Long)

Dim x As Long     Dim lCount As Long     Dim vArray() As Variant     Dim rng As Range     On Error GoTo errhandler

Set rng = Intersect(rngAll, rngAll.Columns(1))

ReDim vArray(1 To rng.Rows.Count)

lCount = 0     For x = 1 To rng.Rows.Count         If rng.Cells(x).Value = vValue Then             lCount = lCount + 1             vArray(lCount) = rng.Cells(x).Offset(0, iCol).Value         End If     Next x

ReDim Preserve vArray(1 To lCount)

If lCount = 0 Then         VLIndex = CVErr(xlErrNA)

ElseIf lIndex > lCount Then         VLIndex = CVErr(xlErrNum)

Else         VLIndex = vArray(lIndex)

End If errhandler:

If Err.Number <> 0 Then VLIndex = CVErr(xlErrValue)

End Function

Die an diese benutzerdefinierte Funktion übergebenen Parameter sind der Wert, der Bereich der zu suchenden Zellen, der „Versatz“ von diesem Bereich für die Suche (die Anzahl der Spalten rechts ist positiv, links ist negativ) und der Nummer des Duplikats (1 ist der erste Wert, 2 der zweite usw.).

Nehmen wir zum Beispiel an, A1: B1 enthält Spaltenüberschriften, A2: A100 enthält die Gehälter und B2: B100 enthält die Mitarbeiternamen.

In Zelle E2 können Sie Folgendes eingeben, um das größte Gehalt in der Tabelle zu ermitteln:

=LARGE($A$2:$A$100,ROW()-1)

In Zelle F2 können Sie die folgende Formel eingeben, um festzustellen, ob die Zeile Duplikate enthält, und den aktuellen „Wert“ dieses Duplikats verfolgen:

=IF(E2=E1,1+F1,1)

In Zelle G2 können Sie die folgende Formel verwenden, die die benutzerdefinierte Funktion aufruft:

=VLIndex(E2,$A$2:$A$100,1,F2)

Kopieren Sie die Zellen E2: G2 nach E3: G6, und Sie haben (in Spalte G) die Namen der Mitarbeiter mit den fünf größten Gehältern.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (3077) gilt für Microsoft Excel 97, 2000, 2002 und 2003.