Hoe u het laatste voorkomen van een item in een lijst kunt vinden met behulp van een Excel-formule

Samenvatting: in deze tutorial leert u hoe u een Excel-formule kunt gebruiken om de laatste instantie van een item in een lijst te vinden.

Onlangs was ik bezig met een vergaderagenda.

Ik heb een lijst in Excel met een lijst van mensen en de datums waarop ze "vergaderingsvoorzitters" waren.

Ik moet ook weten wanneer een persoon voor het laatst "voorzitter van de vergadering" was vanwege duplicatie in de lijst (wat betekent dat een persoon meerdere keren voorzitter van de vergadering is geweest).

Ik moet er namelijk voor zorgen dat de meest recente voorzitter niet wordt herplaatst.

Dus besloot ik om wat Excel-functiemagie te gebruiken om het werk te doen.

Hieronder is het eindresultaat, ik kan een naam selecteren uit de vervolgkeuzelijst en het geeft me de datum van de laatste keer dat die naam in de lijst voorkomt.

Als je een goed begrip hebt van Excel-functies, weet je dat er geen Excel-functie is die dit kan.Hier laten we wonderen gebeuren.

In deze tutorial laat ik je drie manieren zien om dit te doen.

Vind het laatste voorkomen - met behulp van de MAX-functie

Hier is de Excel-formule die de laatste waarde in de lijst retourneert:

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

Zo werkt deze formule:

• De MAX-functie wordt gebruikt om het regelnummer van de laatst overeenkomende naam te vinden.Als de naam bijvoorbeeld Glen is, wordt 11 geretourneerd omdat deze op regel 11 staat.Omdat onze lijst op de tweede rij begint, wordt 1 afgetrokken.Daarom is het laatste exemplaar van Glen 10 in onze lijst.
• SUMPRODUCT wordt gebruikt om ervoor te zorgen dat u Control + Shift + Enter niet hoeft te gebruiken, omdat SUMPRODUCT matrixformules aankan.
• De INDEX-functie wordt nu gebruikt om de datum van de laatst overeenkomende naam te vinden.

Vind het laatste exemplaar - met behulp van de LOOKUP-functie

Hier is nog een formule die hetzelfde werk doet:

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

Zo werkt deze formule:

• Opzoekwaarde is 2 (u zult zien waarom ... blijf lezen)
• Het opzoekbereik is 1/($A$2:$A$14=$D$3) - het retourneert 1 wanneer het een overeenkomende naam vindt, en anders een fout.Dus je eindigt met een 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!}。
• De derde parameter ([result_vector]) is het bereik waarin het het resultaat geeft, in dit geval de datum.

De reden dat deze formule werkt, is dat de functie ZOEKEN een benaderingstechniek gebruikt.Dit betekent dat als het een exacte overeenkomst kan vinden, het zal terugkeren, maar als het niet kan, zal het de hele array scannen tot het einde en de volgende grootste waarde retourneren die kleiner is dan de opzoekwaarde.

In dit geval is de opzoekwaarde 2 en in onze array krijgen we gewoon 1 of een fout.Dus het scant de hele array en retourneert de positie van de laatste 1 - de laatste overeenkomende waarde voor de naam.

Laatste keer zoeken - Aangepaste functie (VBA) gebruiken

Laat me je ook een andere manier laten zien.

We kunnen aangepaste functies maken (ook wel door de gebruiker gedefinieerde functies genoemd) met behulp van VBA.

Het voordeel van het maken van aangepaste functies is het gebruiksgemak.U hoeft zich niet elke keer zorgen te maken over het maken van complexe formules omdat het meeste werk in de VBA-backend gebeurt.

Ik heb een eenvoudige formule gemaakt (net zoals de formule VERT.ZOEKEN).

Om aangepaste functies te maken, hebt u VBA-code nodig in de VB-editor.Ik zal je de code en de stappen geven om het later in de VB-editor te krijgen, maar laat me je eerst laten zien hoe het werkt:

Hier is de formule die u het resultaat zal geven:

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

De formule heeft drie parameters:

• Zoek de waarde (dit wordt de naam in cel D3)
• Zoek het bereik (dit is het bereik met naam en datum - A2:B14)
• kolomnummer (dit is de kolom waarvan we het resultaat willen)

Nadat u de formule hebt gemaakt en de code in de VB-editor hebt geplaatst, kunt u deze gebruiken zoals elke andere normale Excel-werkbladfunctie.

Hier is de code voor de formule:

Functie 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) Functie verlaten End If Next i End Function

Hier zijn de stappen om deze code in de VB-editor te plaatsen:

1. Ga naar het tabblad Ontwikkelaars.
2. Klik op Visual Basic-opties.Dit opent de VB-editor in de backend.
3. Klik in het deelvenster Projectverkenner van de VB-editor met de rechtermuisknop op een object in de werkmap waarin u code wilt invoegen.Als u Projectverkenner niet ziet, gaat u naar het tabblad Beeld en klikt u op Projectverkenner.
4. Ga naar Invoegen en klik op Modules.Hiermee wordt een moduleobject voor uw werkmap ingevoegd.
5. Kopieer en plak de code in het modulevenster.

Nu is de formule beschikbaar in alle bladen van de werkmap.

Houd er rekening mee dat u de werkmap in .XLSM-indeling moet opslaan omdat deze macro's bevat.Als u wilt dat deze formule beschikbaar is in alle werkmappen die u gebruikt, kunt u deze ook opslaan in uw persoonlijke macrowerkmap of er een invoegtoepassing van maken.