Find den sidste forekomst af en opslagsværdi en liste i Excel

I denne vejledning lærer du, hvordan du finder den sidste forekomst af et element på en liste ved hjælp af Excel -formler.

For nylig arbejdede jeg på at sætte dagsordenen for et møde.

Jeg havde en liste i Excel, hvor jeg havde en liste over mennesker og de datoer, hvor de fungerede som 'mødeleder'.

Da der var gentagelse på listen (hvilket betyder, at en person har været mødeleder flere gange), havde jeg også brug for at vide, hvornår sidste gang en person fungerede som 'mødestol'.

Dette var fordi jeg var nødt til at sikre, at en, der for nylig var formand, ikke blev tildelt igen.

Så jeg besluttede at bruge noget Excel -funktionsmagi til at få dette gjort.

Nedenfor er det endelige resultat, hvor jeg kan vælge et navn fra rullemenuen, og det giver mig datoen for det sidste forekomst af dette navn på listen.

Hvis du har en god forståelse af Excel -funktioner, ved du, at der ikke er nogen Excel -funktion, der kan gøre dette.

Men du er i Formula Hack -sektionen, og her får vi magien til at ske.

I denne vejledning viser jeg dig tre måder at gøre dette på.

Find den sidste forekomst - ved hjælp af MAX -funktionen

Kreditten til denne teknik går til en artikel af Excel MVP Charley Kyd.

Her er Excel -formlen, der returnerer den sidste værdi fra listen:

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

Sådan fungerer denne formel:

• MAX -funktionen bruges til at finde rækkenummeret for det sidste matchende navn. For eksempel, hvis navnet er Glen, ville det returnere 11, som det er i 11 -rækken. Siden vores liste starter fra anden række og frem, er 1 blevet trukket fra. Så placeringen af ​​den sidste forekomst af Glen er 10 på vores liste.
• SUMPRODUCT bruges til at sikre, at du ikke behøver at bruge Control + Shift + Enter, da SUMPRODUCT kan håndtere matrixformler.
• INDEX -funktionen bruges nu til at finde datoen for det sidste matchende navn.

Find den sidste forekomst - Brug LOOKUP -funktion

Her er en anden formel til at gøre det samme job:

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

Sådan fungerer denne formel:

• Opslagningsværdien er 2 (du vil se hvorfor … fortsæt med at læse)
• Opslagsområdet er 1/($ A $ 2: $ A $ 14 = $ D $ 3) - Dette returnerer 1, når det finder det matchende navn og en fejl, når det ikke gør det. Så du ender med at få et array. For eksempel er opslagsværdien Glen, arrayet ville være {#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!}.
• Det tredje argument ([result_vector]) er det område, hvorfra det giver resultatet, som er datoer i dette tilfælde.

Grunden til, at denne formel fungerer, er, at LOOKUP -funktionen anvender den omtrentlige matchteknik. Dette betyder, at hvis det kan finde den nøjagtige matchende værdi, ville det returnere det, men hvis det ikke kan, scanner det hele arrayet til slutningen og returnerer den næststørste værdi, som er lavere end opslagsværdien.

I dette tilfælde er opslagsværdien 2, og i vores array får vi kun 1’er eller fejl. Så det scanner hele arrayet og returnerer placeringen af ​​den sidste 1 - som er den sidste matchende værdi af navnet.

Find den sidste forekomst - Brug af brugerdefineret funktion (VBA)

Lad mig også vise dig en anden måde at gøre dette på.

Vi kan oprette en brugerdefineret funktion (også kaldet brugerdefineret funktion) ved hjælp af VBA.

Fordelen ved at oprette en brugerdefineret funktion er, at den er let at bruge. Du behøver ikke bekymre dig om at oprette en kompleks formel hver gang, da det meste af arbejdet sker i VBA -backend.

Jeg har oprettet en simpel formel (som minder meget om VLOOKUP -formel).

For at oprette en brugerdefineret funktion skal du have VBA -koden i VB Editor. Jeg giver dig koden og trinene til at placere den i VB Editor om et stykke tid, men lad mig først vise dig, hvordan den fungerer:

Dette er formlen, der giver dig resultatet:

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

Formlen tager tre argumenter:

• Opslagsværdi (dette ville være navnet i celle D3)
• Opslagsområde (dette ville være det område, der har navne og datoer - A2: B14)
• Kolonnenummer (dette er den kolonne, hvorfra vi ønsker resultatet)

Når du har oprettet formlen og lagt koden i VB Editor, kan du bruge den ligesom alle andre almindelige Excel -regnearksfunktioner.

Her er koden til formlen:

'Dette er en kode for en funktion, der finder den sidste forekomst af en opslagsværdi og returnerer den tilsvarende værdi fra den angivne kolonne' Kode oprettet af 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 If Lookupvalue = LookupRange.Cells (i, 1) Then LastItemLookup = LookupRange.Cells (i, ColumnNumber) Afslut funktion Afslut hvis næste i Afslut funktion

Her er trinene til at placere denne kode i VB Editor:

1. Gå til fanen Udvikler.
2. Klik på indstillingen Visual Basic. Dette åbner VB -editoren i backend.
3. Højreklik på et objekt til projektmappen, hvor du vil indsætte koden, i ruden Project Explorer i VB Editor. Hvis du ikke kan se Project Explorer, skal du gå til fanen Vis og klikke på Project Explorer.
4. Gå til Indsæt og klik på Modul. Dette vil indsætte et modulobjekt til din projektmappe.
5. Kopier og indsæt koden i modulvinduet.

Nu ville formlen være tilgængelig i hele regnearket i projektmappen.

Bemærk, at du skal gemme projektmappen i .XLSM -format, da den har en makro i den. Hvis du vil have denne formel til at være tilgængelig i alle de projektmapper, du bruger, kan du enten gemme den i den personlige makro-projektmappe eller oprette et tilføjelsesprogram ud fra den.