U ovom ćete vodiču naučiti kako pronaći Excel posljednje pojavljivanje stavke na popisu pomoću Excel formula.
Nedavno sam radio na postavljanju dnevnog reda sastanka.
Imao sam popis u Excelu na kojem sam imao popis ljudi i datume na koje su oni djelovali kao 'stolica za sastanke'.
Budući da se na popisu ponavljalo (što znači da se neka osoba više puta sastajala s predsjednikom sastanka), također sam morao znati kada je posljednji put osoba djelovala kao "predsjednica sastanka".
To je bilo zato što sam morao osigurati da netko tko je nedavno predsjedavao nije ponovno dodijeljen.
Stoga sam odlučio upotrijebiti neku čaroliju Excel funkcije da to uspijem.
Ispod je konačni rezultat gdje mogu odabrati ime s padajućeg izbornika i on mi daje datum posljednjeg pojavljivanja tog imena na popisu.
Ako dobro razumijete Excel funkcije, znali biste da ne postoji jedna Excel funkcija koja to može učiniti.
Ali vi ste u odjeljku Formula Hack, a mi ovdje činimo da se čarolija dogodi.
U ovom vodiču pokazat ću vam tri načina kako to učiniti.
Pronađite posljednju pojavu - pomoću funkcije MAX
Zasluga za ovu tehniku pripada članku MVP -a Excela Charleyja Kyda.
Evo Excel formule koja će vratiti zadnju vrijednost s popisa:
= INDEKS ($ B $ 2: $ B $ 14, SUMPRODUCT (MAX (ROW ($ A $ 2: $ A $ 14)**($ D $ 3 = $ A $ 2: $ A $ 14))-1))
Evo kako ova formula funkcionira:
- Funkcija MAX koristi se za pronalaženje broja retka posljednjeg odgovarajućeg imena. Na primjer, ako je ime Glen, vratilo bi se 11, jer je u 11. retku. Budući da naš popis počinje od drugog reda nadalje, oduzeo se 1. Dakle, pozicija posljednjeg pojavljivanja Glena je 10 na našem popisu.
- SUMPRODUCT se koristi kako bi se osiguralo da ne morate koristiti Control + Shift + Enter jer SUMPRODUCT može rukovati formulama niza.
- INDEX funkcija sada se koristi za pronalaženje datuma za posljednji odgovarajući naziv.
Pronađite posljednju pojavu - pomoću funkcije LOOKUP
Evo još jedne formule za obavljanje istog posla:
= POGLEDAJ (2,1/($ A $ 2: $ A $ 14 = $ D $ 3), $ B $ 2: $ B $ 14)
Evo kako ova formula funkcionira:
- Vrijednost pretraživanja je 2 (vidjet ćete zašto … nastavite čitati)
- Raspon pretraživanja je 1/($ A $ 2: $ A $ 14 = $ D $ 3) - Ovo vraća 1 kada pronađe odgovarajući naziv, a pogrešku ako ne. Tako ćete na kraju dobiti niz. Na primjer, vrijednost pretraživanja je Glen, niz bi bio {#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!}.
- Treći argument ([result_vector]) je raspon iz kojeg daje rezultat, a to su u ovom slučaju datumi.
Razlog zašto ova formula funkcionira je taj što funkcija LOOKUP koristi tehniku približnog podudaranja. To znači da će, ako može pronaći točnu podudarnu vrijednost, to vratiti, ali ako ne može, skenirat će cijeli niz do kraja i vratiti sljedeću najveću vrijednost koja je niža od tražene vrijednosti.
U ovom slučaju, vrijednost pretraživanja je 2, a u našem nizu dobit ćemo samo 1 ili pogreške. Tako skenira cijeli niz i vraća poziciju posljednje 1 - što je posljednja odgovarajuća vrijednost imena.
Pronađite posljednju pojavu - pomoću prilagođene funkcije (VBA)
Dopustite mi da vam pokažem i drugi način kako to učiniti.
Pomoću VBA možemo stvoriti prilagođenu funkciju (koja se naziva i korisnički definirana funkcija).
Prednost stvaranja prilagođene funkcije je u tome što je jednostavna za korištenje. Ne morate brinuti o stvaranju složene formule svaki put, jer se većina posla događa u VBA pozadini.
Napravio sam jednostavnu formulu (koja je jako slična formuli VLOOKUP).
Da biste stvorili prilagođenu funkciju, morate imati VBA kôd u VB Editoru. Dat ću vam kôd i korake za njegovo postavljanje u VB Editor za neko vrijeme, ali prvo ću vam pokazati kako to radi:
Ovo je formula koja će vam dati rezultat:
= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)
Formula ima tri argumenta:
- Lookup Value (to bi bio naziv u ćeliji D3)
- Raspon pretraživanja (to bi bio raspon koji ima nazive i datume - A2: B14)
- Broj stupca (ovo je stupac iz kojeg želimo rezultat)
Nakon što ste stvorili formulu i stavili kôd u VB Editor, možete je koristiti kao i sve druge uobičajene funkcije radnog lista programa Excel.
Evo koda za formulu:
'Ovo je kôd za funkciju koja pronađe posljednje pojavljivanje vrijednosti pretraživanja i vrati odgovarajuću vrijednost iz navedenog stupca' Kôd koji je stvorio Sumit Bansal (https://trumpexcel.com) Funkcija LastItemLookup (Lookupvalue As String, LookupRange As Raspon, broj stupca kao cijeli broj) Dim i koliko dugo za i = LookupRange.Columns (1) .Cells.Count To 1 Step -1 If Lookupvalue = LookupRange.Cells (i, 1) Zatim LastItemLookup = LookupRange.Cells (i, ColumnNumber) Izađi iz funkcije Kraj ako je sljedeće i prekine funkciju
Evo koraka za postavljanje ovog koda u VB Editor:
- Idite na karticu Developer.
- Kliknite na opciju Visual Basic. Ovo će otvoriti VB editor u pozadini.
- U oknu Project Explorer u VB Editoru desnom tipkom miša kliknite bilo koji objekt radne knjige u koji želite umetnuti kôd. Ako ne vidite Project Explorer, idite na karticu View i kliknite na Project Explorer.
- Idite na Umetni i kliknite na Modul. Ovo će umetnuti objekt modula u vašu radnu knjigu.
- Kopirajte i zalijepite kôd u prozor modula.
Sada bi formula bila dostupna na svim radnim listovima radne knjige.
Imajte na umu da radnu knjigu morate spremiti u .XLSM formatu jer u njoj ima makronaredbu. Također, ako želite da ova formula bude dostupna u svim radnim knjigama koje koristite, možete je spremiti u osobnu radnu knjigu makronaredbi ili iz nje izraditi dodatak.
