Hvordan finne den siste forekomsten av et element i en liste ved hjelp av en Excel-formel

Sammendrag: i denne opplæringen lærer du hvordan du bruker en Excel-formel for å finne den siste forekomsten av et element i en liste.

Nylig jobbet jeg med en møteagenda.

Jeg har en liste i Excel med en liste over personer og datoene de var "møteledere".

Jeg trenger også å vite når en person sist var "møteleder" på grunn av duplisering i listen (som betyr at en person har vært møteleder flere ganger).

Dette er fordi jeg må sørge for at den siste styrelederen ikke blir omplassert.

Så jeg bestemte meg for å bruke litt Excel-funksjonsmagi for å gjøre jobben.

Nedenfor er det endelige resultatet, jeg kan velge et navn fra rullegardinmenyen og det gir meg datoen for siste forekomst av det navnet i listen.

Hvis du har god forståelse for Excel-funksjoner, vet du at det ikke finnes noen Excel-funksjon som kan gjøre dette.Her får vi mirakler til å skje.

I denne opplæringen skal jeg vise deg tre måter å gjøre dette på.

Finn siste forekomst - ved å bruke MAX-funksjonen

Her er Excel-formelen som vil returnere den siste verdien i listen:

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

Slik fungerer denne formelen:

• MAX-funksjonen brukes til å finne linjenummeret til det siste samsvarende navnet.For eksempel, hvis navnet er Glen, vil det returnere 11 fordi det er på linje 11.Siden listen vår starter på den andre raden, trekkes 1 fra.Derfor er den siste forekomsten av Glen 10 i listen vår.
• SUMPRODUCT brukes for å sikre at du ikke trenger å bruke Control + Shift + Enter fordi SUMPRODUCT kan håndtere matriseformler.
• INDEKS-funksjonen brukes nå til å finne datoen for det siste samsvarende navnet.

Finn den siste forekomsten - ved å bruke OPSLAKK-funksjonen

Her er en annen formel som gjør samme jobben:

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

Slik fungerer denne formelen:

• Oppslagsverdien er 2 (du vil se hvorfor .. fortsett å lese)
• Oppslagsområdet er 1/($A$2:$A$14=$D$3) - det returnerer 1 når det finner et samsvarende navn, og en feil ellers.Så du ender opp med en matrise.例如，查找值為 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!}。
• Den tredje parameteren ([result_vektor]) er området der den gir resultatet, i dette tilfellet datoen.

Grunnen til at denne formelen fungerer er at OPSLAKK-funksjonen bruker en omtrentlig samsvarsteknikk.Dette betyr at hvis den kan finne en eksakt match, vil den returnere, men hvis den ikke kan, vil den skanne hele matrisen til slutten og returnere den nest største verdien som er mindre enn oppslagsverdien.

I dette tilfellet er oppslagsverdien 2, og i matrisen vår får vi bare 1 eller en feil.Så den skanner hele matrisen og returnerer posisjonen til den siste 1 - den siste samsvarende verdien for navnet.

Finn siste forekomst – ved hjelp av egendefinert funksjon (VBA)

La meg også vise deg en annen måte.

Vi kan lage tilpassede funksjoner (også kjent som brukerdefinerte funksjoner) ved hjelp av VBA.

Fordelen med å lage tilpassede funksjoner er brukervennlighet.Du trenger ikke å bekymre deg for å lage komplekse formler hver gang fordi det meste av arbeidet skjer i VBA-backend.

Jeg opprettet en enkel formel (mye som VLOOKUP-formelen).

For å lage egendefinerte funksjoner trenger du VBA-kode i VB-editoren.Jeg vil gi deg koden og trinnene for å få den inn i VB-editoren senere, men la meg først vise deg hvordan det fungerer:

Her er formelen som vil gi deg resultatet:

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

Formelen tar tre parametere:

• Finn verdien (dette vil være navnet i celle D3)
• Finn området (dette vil være området med navn og dato – A2:B14)
• kolonnenummer (dette er kolonnen vi ønsker resultatet for)

Etter at du har opprettet formelen og lagt koden inn i VB-editoren, kan du bruke den som alle andre vanlige Excel-regnearkfunksjoner.

Her er koden for formelen:

Funksjon 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) Exit Function End If Next i End Function

Her er trinnene for å legge denne koden inn i VB-editoren:

1. Gå til utviklerfanen.
2. Klikk på Visual Basic-alternativer.Dette vil åpne VB-editoren i backend.
3. Høyreklikk på et objekt i arbeidsboken der du vil sette inn kode i Project Explorer-ruten i VB-editoren.Hvis du ikke ser Project Explorer, gå til View-fanen og klikk Project Explorer.
4. Gå til Sett inn og klikk på Moduler.Dette vil sette inn et modulobjekt for arbeidsboken din.
5. Kopier og lim inn koden i modulvinduet.

Nå vil formelen være tilgjengelig i alle arkene i arbeidsboken.

Merk at du må lagre arbeidsboken i .XLSM-format da den inneholder makroer.Dessuten, hvis du vil at denne formelen skal være tilgjengelig i alle arbeidsbøker du bruker, kan du lagre den i din personlige makroarbeidsbok eller lage et tillegg fra den.