Selles õpetuses saate teada, kuidas Exceli valemite abil leida loendis üksuse viimane esinemine.
Hiljuti töötasin koosoleku päevakorra koostamise kallal.
Mul oli Excelis nimekiri, kus mul oli inimeste nimekiri ja kuupäevad, mil nad toimisid koosoleku juhatajana.
Kuna loendis oli kordusi (mis tähendab, et inimene on kohtunud esimehega mitu korda), pidin ma teadma ka seda, millal viimati isik „koosoleku juhatajana“ tegutses.
Selle põhjuseks oli asjaolu, et pidin tagama, et kedagi, kes hiljuti juhatas, ei määrata uuesti.
Seega otsustasin selle tegemiseks kasutada mõnda Exceli funktsiooni maagiat.
Allpool on lõpptulemus, kus ma saan rippmenüüst nime valida ja see annab mulle selle nime viimase esinemise kuupäeva loendis.
Kui teil on Exceli funktsioonidest hea arusaam, siis teate, et pole ühtegi Exceli funktsiooni, mis seda teeks.
Kuid olete valemihäkkide jaotises ja siin teeme maagia teoks.
Selles õpetuses näitan teile kolme viisi, kuidas seda teha.
Leidke viimane esinemine - funktsiooni MAX kasutamine
Selle tehnika tunnustus kuulub Exceli MVP Charley Kydi artiklile.
Siin on Exceli valem, mis tagastab loendist viimase väärtuse:
= INDEX ($ B $ 2: $ B $ 14, SUMPRODUCT (MAX (ROW ($ A $ 2: $ A $ 14)*($ D $ 3 = $ A $ 2: $ A $ 14))-1))
See valem töötab järgmiselt.
- Funktsiooni MAX kasutatakse viimase sobiva nime reanumbri leidmiseks. Näiteks kui nimi on Glen, tagastab see 11, nagu on reas 11. Kuna meie loend algab teisest reast, on 1 lahutatud. Seega on Gleni viimase esinemise positsioon meie loendis 10.
- SUMPRODUCT -i kasutatakse tagamaks, et te ei pea kasutama klahve Control + Shift + Enter, kuna SUMPRODUCT saab hakkama massiivivalemitega.
- Funktsiooni INDEX kasutatakse nüüd viimase sobiva nime kuupäeva leidmiseks.
Viimase esinemise leidmine - funktsiooni LOOKUP kasutamine
Siin on veel üks valem sama töö tegemiseks:
= OTSING (2,1/($ A $ 2: $ A $ 14 = $ D $ 3), $ B $ 2: $ B $ 14)
See valem töötab järgmiselt.
- Otsinguväärtus on 2 (näete, miks… jätkake lugemist)
- Otsinguvahemik on 1/($ A $ 2: $ A $ 14 = $ D $ 3) - see tagastab 1, kui leiab sobiva nime ja vea, kui seda ei leia. Nii et lõpuks saate massiivi. Näiteks kui otsingu väärtus on Glen, oleks massiiviks {#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!}.
- Kolmas argument ([tulemus_vektor]) on vahemik, millest see annab tulemuse, mis on antud juhul kuupäevad.
Selle valemi tööpõhjus on see, et funktsioon LOOKUP kasutab ligikaudset vastetehnikat. See tähendab, et kui see leiab täpse sobiva väärtuse, tagastab see selle, kuid kui ei leia, skaneerib see kogu massiivi lõpuni ja tagastab järgmise suurima väärtuse, mis on otsinguväärtusest madalam.
Sel juhul on otsinguväärtus 2 ja meie massiivis saame ainult 1 või vigu. Nii skaneerib see kogu massiivi ja tagastab viimase 1 positsiooni - see on nime viimane sobiv väärtus.
Leidke viimane esinemine - kohandatud funktsiooni (VBA) kasutamine
Lubage mul näidata teile ka teist viisi, kuidas seda teha.
Saame VBA abil luua kohandatud funktsiooni (mida nimetatakse ka kasutaja määratud funktsiooniks).
Kohandatud funktsiooni loomise eeliseks on see, et seda on lihtne kasutada. Te ei pea iga kord muretsema keerulise valemi loomise pärast, kuna suurem osa tööst toimub VBA taustaprogrammis.
Olen loonud lihtsa valemi (mis sarnaneb palju VLOOKUP valemiga).
Kohandatud funktsiooni loomiseks peab teil VB -redaktoris olema VBA -kood. Ma annan teile koodi ja sammud selle mõneks ajaks VB redaktorisse paigutamiseks, kuid lubage mul kõigepealt teile näidata, kuidas see toimib:
See on valem, mis annab teile tulemuse:
= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)
Valem sisaldab kolme argumenti:
- Otsingu väärtus (see oleks nimi lahtris D3)
- Otsinguvahemik (see oleks nimede ja kuupäevadega vahemik - A2: B14)
- Veeru number (see on veerg, mille tulemust soovime)
Kui olete valemi loonud ja koodi VB -redaktorisse pannud, saate seda kasutada nagu kõiki teisi tavalisi Exceli töölehe funktsioone.
Siin on valemi kood:
"See on funktsiooni funktsioon, mis leiab otsinguväärtuse viimase esinemise ja tagastab vastava väärtuse määratud veerust" Kood, mille on loonud Sumit Bansal (https://trumpexcel.com) Funktsioon LastItemLookup (otsinguväärtus stringina, otsingupiirkond Vahemik, veergude arv täisarvuna) Dim i nii kaua, kui i = otsingupiirkond.Veerud (1). Lahtrid. Loendage 1. sammuni -1 If Lookupvalue = LookupRange.Cells (i, 1) Siis LastItemLookup = LookupRange.Cells (i, ColumnNumber) Exit Function End Kui järgmine i End Function
Selle koodi VB redaktorisse paigutamiseks toimige järgmiselt.
- Minge vahekaardile Arendaja.
- Klõpsake suvandil Visual Basic. See avab taustaprogrammis VB redaktori.
- Paremklõpsake VB redaktori Project Exploreri paanil töövihiku mis tahes objekti, kuhu soovite koodi sisestada. Kui te Project Explorerit ei näe, minge vahekaardile Vaade ja klõpsake nuppu Project Explorer.
- Minge sisesta ja klõpsake moodulit. See lisab teie töövihikusse mooduliobjekti.
- Kopeerige ja kleepige kood mooduli aknasse.
Nüüd oleks valem saadaval kogu töövihiku töölehel.
Pange tähele, et peate töövihiku salvestama .XLSM -vormingus, kuna selles on makro. Samuti, kui soovite, et see valem oleks saadaval kõigis töövihikutes, mida kasutate, saate selle kas isiklikku makro töövihikusse salvestada või sellest lisandmooduli luua.
