Gledajte video zapis - potražite drugu, treću ili ntu odgovarajuću vrijednost
Što se tiče pretraživanja podataka u Excelu, dvije su nevjerojatne funkcije koje često koristim - VLOOKUP i INDEX (uglavnom zajedno s funkcijom MATCH).
Međutim, ove formule su osmišljene tako da pronađu samo prvu instancu vrijednosti traženja.
Ali što ako želite potražiti drugu, treću, četvrtu ili N-tu vrijednost.
Pa, izvedivo je uz malo dodatnog rada.
U ovom vodiču pokazat ću vam različite načine (s primjerima) kako potražiti drugu ili Ntu vrijednost u Excelu.
Potražite drugu, treću ili Ntu vrijednost u Excelu
U ovom ću vodiču pokriti dva načina traženja druge ili N-te vrijednosti u Excelu:
- Korištenje pomoćnog stupca.
- Korištenje formula niza.
Počnimo i odmah zaronimo.
Korištenje kolone pomoćnika
Pretpostavimo da ste koordinator obuke u organizaciji i imate skup podataka kao što je prikazano u nastavku. Želite navesti svu obuku ispred imena zaposlenika.
U gornjem skupu podataka zaposlenici su prošli obuku o različitim alatima sustava Microsoft Office (Excel, PowerPoint i Word).
Sada možete upotrijebiti funkciju VLOOKUP ili kombinaciju INDEKS/MATCH kako biste pronašli obuku koju je zaposlenik završio. Međutim, vratit će samo prvu odgovarajuću instancu.
Na primjer, u slučaju Ivana, prošao je sva tri treninga, ali kad pogledam njegovo ime pomoću VLOOKUP ili INDEX/MATCH, uvijek će se vratiti 'Excel', što je prvi trening za njegovo ime na popisu .
Da bismo to učinili, možemo upotrijebiti pomoćni stupac i u njemu stvoriti jedinstvene vrijednosti pretraživanja.
Evo koraka:
- Umetnite stupac prije stupca u kojem je naveden trening.
- U ćeliju B2 unesite sljedeću formulu:
= A2 & COUNTIF ($ A $ 2: $ A2, A2)
- U ćeliju F2 unesite sljedeću formulu i kopirajte i zalijepite za sve ostale ćelije:
= IFNA (VLOOKUP ($ E2 & COLUMNS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
Gornja formula vratila bi obuku za svakog zaposlenika redoslijedom koji se prikazuje na popisu. U slučaju da nema navedene obuke za zaposlenika, vraća se prazno.
Kako funkcionira ova formula?
Formula COUNTIF u stupcu pomoćnika čini ime svakog zaposlenika jedinstvenim dodajući mu broj. Na primjer, prva instanca Ivana postaje John1, druga instanca postaje John2 itd.
Formula VLOOKUP sada koristi ove jedinstvene nazive zaposlenika za pronalaženje odgovarajuće obuke.
Imajte na umu da je $ E2 & COLUMNS ($ F $ 1: F1) vrijednost pretraživanja u formuli. Time bi se imenu zaposlenika dodao broj na temelju broja stupca. Na primjer, kada se ova formula koristi u ćeliji F2, vrijednost pretraživanja postaje "John1". U ćeliji G2 postaje "John2" i tako dalje.
Korištenje formule niza
Ako ne želite promijeniti izvorni skup podataka dodavanjem pomoćnih stupaca, možete koristiti i formulu niza da biste potražili drugu, treću ili ntu vrijednost.
Pretpostavimo da imate isti skup podataka kao što je prikazano u nastavku:
Evo formule koja će vratiti ispravnu vrijednost pretraživanja:
= IFERROR (INDEKS ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1), ""), COLUMNS ($ E $ 1 : E1))), "")
Kopirajte ovu formulu i zalijepite je u ćeliju E2.
Imajte na umu da je ovo formula niza i morate koristiti Control + Shift + Enter (držite tipke Control i Shift i pritisnite tipku Enter), umjesto da pritisnete samo tipku Enter.
Kliknite ovdje za preuzimanje datoteke primjera.
Kako funkcionira ova formula?
Razdvojimo ovu formulu na dijelove i vidimo kako djeluje.
$ A $ 2: $ A $ 14 = $ D2
Gornji dio formule uspoređuje svaku ćeliju u A2: A14 s vrijednošću u D2. U ovom skupu podataka provjerava sadrži li ćelija naziv "John" ili ne.
Vraća niz TRUE od FALSE. Ako ćelija ima naziv 'John', to bi bilo točno, inače bi bilo netočno.
Ispod je niz koji biste dobili u ovom primjeru:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}
Imajte na umu da ima TRUE na 1., 7. i 111. mjestu jer se tamo u skupu podataka pojavljuje ime John.
AKO ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)
Gornja IF formula koristi niz TRUE i FALSE, a TRUE zamjenjuje pozicijom svog pojavljivanja na popisu (dano ROW ($ A $ 2: $ A $ 14) -1) i FALSE sa “” (prazna polja). Slijedi rezultirajući niz koji dobivate s ovom IF formulom:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Imajte na umu da su 1, 7 i 11 položaj Ivana na popisu.
MALI (AKO ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), COLUMNS ($ E $ 1: E1))
Funkcija SMALL sada odabire prvi najmanji, drugi najmanji, treći najmanji broj iz ovog niza. Imajte na umu da koristi funkciju COLUMNS za generiranje broja stupca. U ćeliji E2 funkcija COLUMNS vraća 1, a funkcija SMALL vraća 1. U ćeliji F2 funkcija COLUMNS vraća 2, a funkcija SMALL 7.
INDEKS ($ B $ 2: $ B $ 14, MALI (AKO ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), KOLONE ($ E $ 1: E1) ))
INDEX funkcija sada vraća vrijednost s popisa u stupcu B na temelju pozicije koju vraća funkcija SMALL. Stoga u ćeliji E2 vraća ‘Excel’, koja je prva stavka u B2: B14. U ćeliji F2 vraća PowerPoint, koji je sedma stavka na popisu.
Budući da postoje slučajevi u kojima postoji samo jedna ili dvije obuke za neke zaposlenike, funkcija INDEX vratila bi grešku. Funkcija IFERROR koristi se za vraćanje praznog mjesta umjesto pogreške.
Imajte na umu da sam u ovim primjerima koristio reference raspona. Međutim, u praktičnim primjerima korisno je pretvoriti podatke u Excel tablicu. Pretvaranjem u Excel tablicu možete koristiti strukturirane reference, što olakšava stvaranje formula. Također, Excel tablica može automatski uzeti u obzir sve nove stavke obuke koje se dodaju na popis (tako da ne morate svaki put prilagođavati formule).
Što radite kada morate potražiti drugu, treću ili Ntu vrijednost? Siguran sam da postoji više načina za to. Ako koristite nešto lakše od ovdje navedenog, podijelite sa svima nama u odjeljku komentara.
Kliknite ovdje za preuzimanje datoteke primjera.
