Finden Sie das letzte Vorkommen eines Nachschlagewerts in einer Liste in Excel

In diesem Tutorial erfahren Sie, wie Sie mithilfe von Excel-Formeln das letzte Vorkommen eines Elements in einer Liste finden.

Kürzlich habe ich daran gearbeitet, die Tagesordnung für ein Meeting festzulegen.

Ich hatte eine Excel-Liste, in der ich eine Liste der Personen und der Daten hatte, an denen sie als „Meeting Chair“ fungierten.

Da es Wiederholungen in der Liste gab (was bedeutet, dass eine Person mehrmals Besprechungsvorsitzender war), musste ich auch wissen, wann eine Person das letzte Mal als „Besprechungsvorsitzender“ fungierte.

Dies lag daran, dass ich sicherstellen musste, dass jemand, der kürzlich den Vorsitz führte, nicht erneut zugewiesen wurde.

Also beschloss ich, etwas Excel-Funktionsmagie zu verwenden, um dies zu erledigen.

Unten ist das Endergebnis, bei dem ich einen Namen aus der Dropdown-Liste auswählen kann und das Datum des letzten Vorkommens dieses Namens in der Liste angibt.

Wenn Sie Excel-Funktionen gut verstehen, wissen Sie, dass es keine Excel-Funktion gibt, die dies kann.

Aber Sie befinden sich im Bereich Formel-Hack, und hier lassen wir die Magie passieren.

In diesem Tutorial zeige ich Ihnen drei Möglichkeiten, dies zu tun.

Finden Sie das letzte Vorkommen - Verwenden der MAX-Funktion

Diese Technik wird einem Artikel von Excel-MVP Charley Kyd zugeschrieben.

Hier ist die Excel-Formel, die den letzten Wert aus der Liste zurückgibt:

=INDEX($B$2:$B$14,SUMPRODUCT(MAX(ROW($A$2:$A$14)*($D$3=$A$2:$A$14))-1))

So funktioniert diese Formel:

• Die MAX-Funktion wird verwendet, um die Zeilennummer des letzten übereinstimmenden Namens zu finden. Wenn der Name beispielsweise Glen lautet, würde er 11 zurückgeben, da er sich in der Zeile 11 befindet. Da unsere Liste ab der zweiten Reihe beginnt, wurde 1 abgezogen. Die Position des letzten Vorkommens von Glen ist also 10 auf unserer Liste.
• SUMPRODUCT wird verwendet, um sicherzustellen, dass Sie nicht Strg + Umschalt + Eingabetaste verwenden müssen, da SUMPRODUCT Array-Formeln verarbeiten kann.
• Die INDEX-Funktion wird jetzt verwendet, um das Datum für den letzten übereinstimmenden Namen zu finden.

Finden Sie das letzte Vorkommen - Verwenden der LOOKUP-Funktion

Hier ist eine andere Formel, um die gleiche Arbeit zu erledigen:

=VERWEIS(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)

So funktioniert diese Formel:

• Der Nachschlagewert ist 2 (Sie werden sehen, warum … lesen Sie weiter)
• Der Nachschlagebereich ist 1/($A$2:$A$14=$D$3) – Dies gibt 1 zurück, wenn der übereinstimmende Name gefunden wird, und einen Fehler, wenn dies nicht der Fall ist. So erhalten Sie am Ende ein Array. Wenn der Nachschlagewert beispielsweise Glen ist, wäre das Array {#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}.
• Das dritte Argument ([result_vector]) ist der Bereich, aus dem es das Ergebnis liefert, das in diesem Fall Datumsangaben sind.

Der Grund, warum diese Formel funktioniert, ist, dass die LOOKUP-Funktion die Approximation-Match-Technik verwendet. Dies bedeutet, dass, wenn es den genau passenden Wert findet, es diesen zurückgeben würde, aber wenn es nicht kann, wird das gesamte Array bis zum Ende durchsucht und der nächstgrößere Wert zurückgegeben, der niedriger als der Nachschlagewert ist.

In diesem Fall ist der Lookup-Wert 2, und in unserem Array erhalten wir nur 1 oder Fehler. Es scannt also das gesamte Array und gibt die Position der letzten 1 zurück - das ist der letzte übereinstimmende Wert des Namens.

Finden Sie das letzte Vorkommen - Verwenden der benutzerdefinierten Funktion (VBA)

Lassen Sie mich Ihnen auch einen anderen Weg zeigen.

Wir können eine benutzerdefinierte Funktion (auch als benutzerdefinierte Funktion bezeichnet) mit VBA erstellen.

Der Vorteil der Erstellung einer benutzerdefinierten Funktion besteht darin, dass sie einfach zu verwenden ist. Sie müssen sich nicht jedes Mal darum kümmern, eine komplexe Formel zu erstellen, da die meiste Arbeit im VBA-Backend stattfindet.

Ich habe eine einfache Formel erstellt (die der SVERWEIS-Formel sehr ähnlich ist).

Um eine benutzerdefinierte Funktion zu erstellen, benötigen Sie den VBA-Code im VB-Editor. Ich werde Ihnen den Code und die Schritte zum Einfügen in den VB-Editor in Kürze geben, aber lassen Sie mich Ihnen zuerst zeigen, wie es funktioniert:

Dies ist die Formel, die Ihnen das Ergebnis liefert:

=LastItemLookup($D$3,$A$2:$B$14,2)

Die Formel benötigt drei Argumente:

• Nachschlagewert (dies wäre der Name in Zelle D3)
• Nachschlagebereich (dies wäre der Bereich mit den Namen und Daten - A2: B14)
• Spaltennummer (dies ist die Spalte, aus der wir das Ergebnis wollen)

Nachdem Sie die Formel erstellt und den Code in den VB-Editor eingefügt haben, können Sie sie wie alle anderen regulären Excel-Arbeitsblattfunktionen verwenden.

Hier der Code für die Formel:

'Dies ist ein Code für eine Funktion, die das letzte Vorkommen eines Nachschlagewerts findet und den entsprechenden Wert aus der angegebenen Spalte zurückgibt 'Code erstellt von Sumit Bansal (https://trumpexcel.com) Funktion LastItemLookup(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long For i = LookupRange.Columns(1).Cells.Count To 1 Step -1 Wenn Lookupvalue = LookupRange.Cells(i, 1) Then LastItemLookup = LookupRange.Cells(i, ColumnNumber) Exit Function End If Next i End Function

Hier sind die Schritte, um diesen Code im VB-Editor zu platzieren:

1. Wechseln Sie zur Registerkarte Entwickler.
2. Klicken Sie auf die Visual Basic-Option. Dies öffnet den VB-Editor im Backend.
3. Klicken Sie im Bereich Projekt-Explorer im VB-Editor mit der rechten Maustaste auf ein beliebiges Objekt für die Arbeitsmappe, in die Sie den Code einfügen möchten. Wenn Sie den Projekt-Explorer nicht sehen, gehen Sie zur Registerkarte Ansicht und klicken Sie auf Projekt-Explorer.
4. Gehen Sie zu Einfügen und klicken Sie auf Modul. Dadurch wird ein Modulobjekt für Ihre Arbeitsmappe eingefügt.
5. Kopieren Sie den Code und fügen Sie ihn in das Modulfenster ein.

Jetzt wäre die Formel in allen Arbeitsblättern der Arbeitsmappe verfügbar.

Beachten Sie, dass Sie die Arbeitsmappe im .XLSM-Format speichern müssen, da sie ein Makro enthält. Wenn diese Formel auch in allen von Ihnen verwendeten Arbeitsmappen verfügbar sein soll, können Sie sie entweder in der persönlichen Makroarbeitsmappe speichern oder daraus ein Add-In erstellen.