I denne opplæringen lærer du hvordan du finner den siste forekomsten av et element i en liste ved hjelp av Excel -formler.
Nylig jobbet jeg med å sette dagsorden for et møte.
Jeg hadde en liste i Excel der jeg hadde en liste over mennesker og datoene de fungerte som "møteleder".
Siden det var gjentakelse på listen (som betyr at en person har vært møteleder flere ganger), trengte jeg også å vite når var siste gangen en person fungerte som "møteleder".
Dette var fordi jeg måtte sørge for at noen som nylig ledet ikke ble tildelt igjen.
Så jeg bestemte meg for å bruke litt Excel Function -magi for å få dette gjort.
Nedenfor er det endelige resultatet der jeg kan velge et navn fra rullegardinmenyen, og det gir meg datoen for det siste forekomsten av det navnet i listen.
Hvis du har god forståelse av Excel -funksjoner, vet du at det ikke er en Excel -funksjon som kan gjøre dette.
Men du er i Formel Hack -delen, og her får vi magien til å skje.
I denne opplæringen viser jeg deg tre måter å gjøre dette på.
Finn den siste forekomsten - ved hjelp av MAX -funksjonen
Kreditt til denne teknikken går til en artikkel av Excel MVP Charley Kyd.
Her er Excel -formelen som returnerer den siste verdien fra listen:
= INDEKS ($ B $ 2: $ B $ 14, SUMPRODUCT (MAX (RAD ($ A $ 2: $ A $ 14)*($ D $ 3 = $ A $ 2: $ A $ 14))-1))
Slik fungerer denne formelen:
- MAX -funksjonen brukes til å finne radnummeret til det siste matchende navnet. For eksempel, hvis navnet er Glen, vil det returnere 11, som det er i 11 -raden. Siden listen vår starter fra andre rad og fremover, er 1 trukket fra. Så posisjonen til den siste forekomsten av Glen er 10 på vår liste.
- SUMPRODUCT brukes til å sikre at du ikke trenger å bruke Control + Shift + Enter, da SUMPRODUCT kan håndtere matriseformler.
- INDEX -funksjonen brukes nå for å finne datoen for det siste matchende navnet.
Finn den siste forekomsten - ved hjelp av LOOKUP -funksjonen
Her er en annen formel for å gjøre den samme jobben:
= OPLETNING (2,1/($ A $ 2: $ A $ 14 = $ D $ 3), $ B $ 2: $ B $ 14)
Slik fungerer denne formelen:
- Oppslagsverdien er 2 (du ser hvorfor … fortsett å lese)
- Oppslagsområdet er 1/($ A $ 2: $ A $ 14 = $ D $ 3) - Dette returnerer 1 når det finner det matchende navnet og en feil når det ikke gjør det. Så du ender opp med å få en matrise. For eksempel, av oppslagsverdien er Glen, vil matrisen 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 argumentet ([result_vector]) er området det gir resultatet fra, som er datoer i dette tilfellet.
Grunnen til at denne formelen fungerer er at LOOKUP -funksjonen bruker omtrentlig match -teknikk. Dette betyr at hvis den kan finne den eksakte matchende verdien, vil den returnere den, men hvis den ikke kan, vil den skanne hele matrisen til slutten og returnere den nest største verdien som er lavere enn oppslagsverdien.
I dette tilfellet er oppslagsverdien 2, og i matrisen vår får vi bare 1’er eller feil. Så den skanner hele matrisen og returnerer posisjonen til den siste 1 - som er den siste matchende verdien av navnet.
Finn den siste forekomsten - ved hjelp av egendefinert funksjon (VBA)
La meg også vise deg en annen måte å gjøre dette på.
Vi kan lage en tilpasset funksjon (også kalt User Defined Function) ved hjelp av VBA.
Fordelen med å lage en egendefinert funksjon er at den er enkel å bruke. Du trenger ikke å bekymre deg for å lage en kompleks formel hver gang, ettersom det meste av arbeidet skjer i VBA -backend.
Jeg har laget en enkel formel (som ligner mye på VLOOKUP -formel).
For å lage en egendefinert funksjon må du ha VBA -koden i VB Editor. Jeg vil gi deg koden og trinnene for å plassere den i VB Editor om en stund, men la meg først vise deg hvordan den fungerer:
Dette er formelen som vil gi deg resultatet:
= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)
Formelen tar tre argumenter:
- Oppslagsverdi (dette ville være navnet i celle D3)
- Oppslagsområde (dette vil være området som har navn og datoer - A2: B14)
- Kolonnenummer (dette er kolonnen vi ønsker resultatet fra)
Når du har opprettet formelen og lagt koden i VB Editor, kan du bruke den akkurat som alle andre vanlige Excel -regnearkfunksjoner.
Her er koden for formelen:
'Dette er en kode for en funksjon som finner den siste forekomsten av en oppslagsverdi og returnerer den tilsvarende verdien fra den angitte kolonnen' Kode opprettet av Sumit Bansal (https://trumpexcel.com) 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) Avslutt funksjon Slutt hvis neste i Avslutt funksjon
Her er trinnene for å plassere denne koden i VB Editor:
- Gå til kategorien Utvikler.
- Klikk på Visual Basic -alternativet. Dette vil åpne VB -redaktøren i backend.
- I Project Explorer-ruten i VB Editor, høyreklikker du på et hvilket som helst objekt for arbeidsboken der du vil sette inn koden. Hvis du ikke ser Project Explorer, gå til kategorien View og klikk på Project Explorer.
- Gå til Sett inn og klikk på Modul. Dette vil sette inn et modulobjekt for arbeidsboken din.
- Kopier og lim inn koden i modulvinduet.
Nå ville formelen være tilgjengelig i hele regnearket i arbeidsboken.
Vær oppmerksom på at du må lagre arbeidsboken i .XLSM -formatet, da den har en makro i den. Hvis du vil at denne formelen skal være tilgjengelig i alle arbeidsbøkene du bruker, kan du enten lagre den i Personal Macro Workbook eller lage et tillegg fra den.
