So finden Sie das letzte Vorkommen eines Elements in einer Liste mithilfe einer Excel-Formel

Zusammenfassung: In diesem Tutorial lernen Sie, wie Sie eine Excel-Formel verwenden, um das letzte Vorkommen eines Elements in einer Liste zu finden.

Kürzlich arbeitete ich an einer Meeting-Agenda.

Ich habe eine Liste in Excel mit einer Liste von Personen und den Daten, an denen sie "Sitzungsvorsitzende" waren.

Ich muss auch wissen, wann eine Person aufgrund von Duplikaten in der Liste zuletzt „Vorsitzender der Besprechung“ war (was bedeutet, dass eine Person mehrmals Vorsitzender der Besprechung war).

Denn ich muss dafür sorgen, dass der letzte Vorsitzende nicht neu besetzt wird.

Also beschloss ich, einige Excel-Funktionsmagie zu verwenden, um die Arbeit zu erledigen.

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

Wenn Sie sich gut mit Excel-Funktionen auskennen, wissen Sie, dass es keine Excel-Funktion gibt, die dies leisten kann.Hier geschehen Wunder.

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

Finden Sie das letzte Vorkommen - mit der MAX-Funktion

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

=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($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 ist, wird 11 zurückgegeben, da er sich in Zeile 11 befindet.Da unsere Liste in der zweiten Zeile beginnt, wird 1 subtrahiert.Daher ist das letzte Vorkommen von Glen 10 in unserer Liste.
• SUMPRODUCT wird verwendet, um sicherzustellen, dass Sie nicht Strg + Shift + Enter verwenden müssen, da SUMPRODUCT Matrixformeln verarbeiten kann.
• Die INDEX-Funktion wird nun verwendet, um das Datum des letzten übereinstimmenden Namens zu finden.

Finden Sie das letzte Vorkommen - mit der LOOKUP-Funktion

Hier ist eine andere Formel, die die gleiche Aufgabe erfüllt:

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

So funktioniert diese Formel:

• Der Suchwert ist 2 (Sie werden sehen, warum ... lesen Sie weiter)
• Der Suchbereich ist 1/($A$2:$A$14=$D$3) – er gibt 1 zurück, wenn er einen übereinstimmenden Namen findet, andernfalls einen Fehler.Sie erhalten also ein Array.例如，查找值為 Glen，數組將為 {#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}。
• Der dritte Parameter ([result_vector]) ist der Bereich, in dem er das Ergebnis liefert, in diesem Fall das Datum.

Der Grund, warum diese Formel funktioniert, liegt darin, dass die LOOKUP-Funktion eine ungefähre Übereinstimmungstechnik verwendet.Das bedeutet, dass, wenn es eine genaue Übereinstimmung findet, es zurückkehrt, aber wenn es das nicht kann, es das gesamte Array bis zum Ende durchsucht und den nächstgrößten Wert zurückgibt, der kleiner als der Suchwert ist.

In diesem Fall ist der Suchwert 2, und in unserem Array erhalten wir nur 1 oder einen Fehler.Es scannt also das gesamte Array und gibt die Position der letzten 1 zurück - den letzten übereinstimmenden Wert für den Namen.

Letztes Vorkommen suchen – mit benutzerdefinierter Funktion (VBA)

Lassen Sie mich Ihnen auch einen anderen Weg zeigen.

Mit VBA können wir benutzerdefinierte Funktionen (auch als benutzerdefinierte Funktionen bezeichnet) erstellen.

Der Vorteil beim Erstellen benutzerdefinierter Funktionen ist die Benutzerfreundlichkeit.Sie müssen sich nicht jedes Mal um das Erstellen komplexer Formeln kümmern, da die meiste Arbeit im VBA-Backend stattfindet.

Ich habe eine einfache Formel erstellt (ähnlich wie die VLOOKUP-Formel).

Um benutzerdefinierte Funktionen zu erstellen, benötigen Sie VBA-Code im VB-Editor.Ich gebe Ihnen den Code und die Schritte, um ihn später in den VB-Editor zu bekommen, aber lassen Sie mich Ihnen zuerst zeigen, wie es funktioniert:

Hier ist die Formel, die Ihnen das Ergebnis liefert:

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

Die Formel benötigt drei Parameter:

• Suchen Sie den Wert (dies ist der Name in Zelle D3)
• Finden Sie den Bereich (dies ist der Bereich mit Name und Datum – A2:B14)
• Spaltennummer (das ist die Spalte, für die wir das Ergebnis wollen)

Nachdem Sie die Formel erstellt und den Code in den VB-Editor eingefügt haben, können Sie sie wie jede andere reguläre Excel-Arbeitsblattfunktion verwenden.

Hier ist der Code für die Formel:

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 If Lookupvalue = LookupRange.Cells(i, 1) Then LastItemLookup = LookupRange.Cells(i, ColumnNumber) Ende der Funktion beenden, wenn als nächstes i Funktion beenden

Hier sind die Schritte, um diesen Code in den VB-Editor einzufügen:

1. Gehen Sie zum Entwickler-Tab.
2. Klicken Sie auf Visual Basic-Optionen.Dadurch wird der VB-Editor im Backend geöffnet.
3. Klicken Sie im Bereich Projekt-Explorer des VB-Editors mit der rechten Maustaste auf ein beliebiges Objekt in der Arbeitsmappe, in das Sie Code einfügen möchten.Wenn der Projekt-Explorer nicht angezeigt wird, gehen Sie zur Registerkarte Ansicht und klicken Sie auf Projekt-Explorer.
4. Gehen Sie zu Einfügen und klicken Sie auf Module.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 ist die Formel in allen Blättern der Arbeitsmappe verfügbar.

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