Oglejte si video - poiščite drugo, tretjo ali n -to vrednost, ki se ujema
Kar zadeva iskanje podatkov v Excelu, pogosto uporabljam dve neverjetni funkciji - VLOOKUP in INDEX (večinoma v povezavi s funkcijo MATCH).
Vendar so te formule zasnovane tako, da najdejo le prvi primerek iskalne vrednosti.
Kaj pa, če želite poiskati drugo, tretjo, četrto ali N-to vrednost.
No, z malo dodatnega dela je to izvedljivo.
V tej vadnici vam bom pokazal različne načine (s primeri), kako poiskati drugo ali N -to vrednost v Excelu.
Poiščite drugo, tretjo ali Nto vrednost v Excelu
V tej vadnici bom obravnaval dva načina iskanja druge ali N-te vrednosti v Excelu:
- Uporaba pomožnega stolpca.
- Uporaba matričnih formul.
Začnimo in se takoj potopimo.
Uporaba stolpca za pomoč
Recimo, da ste koordinator usposabljanja v organizaciji in imate nabor podatkov, kot je prikazano spodaj. Pred imenom zaposlenega želite navesti vse usposabljanje.
V zgornjem naboru podatkov so bili zaposleni usposobljeni za različna orodja Microsoft Office (Excel, PowerPoint in Word).
Zdaj lahko s funkcijo VLOOKUP ali kombinacijo INDEX/MATCH poiščete usposabljanje, ki ga je zaposleni opravil. Vendar bo vrnil le prvi ujemajoči se primerek.
Na primer, v primeru Johna je opravil vsa tri usposabljanja, toda ko pogledam njegovo ime z VLOOKUP ali INDEX/MATCH, se bo vedno vrnilo "Excel", ki je prvo usposabljanje za njegovo ime na seznamu .
Če želimo to narediti, lahko uporabimo pomožni stolpec in v njem ustvarimo edinstvene iskalne vrednosti.
Tu so koraki:
- Pred stolpec, ki navaja vadbo, vstavite stolpec.
- V celico B2 vnesite naslednjo formulo:
= A2 & COUNTIF ($ A $ 2: $ A2, A2)
- V celico F2 vnesite naslednjo formulo in kopirajte in prilepite vse ostale celice:
= IFNA (VLOOKUP ($ E2 & COLUMNS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
Zgornja formula bi vrnila usposabljanje za vsakega zaposlenega po vrstnem redu, ki je prikazan na seznamu. Če za zaposlenega ni usposabljanja, se vrne prazno.
Kako deluje ta formula?
Formula COUNTIF v pomožnem stolpcu naredi ime vsakega zaposlenega edinstveno, tako da mu doda številko. Na primer, prvi primerek Janeza postane Janez1, drugi primer Janez2 in tako naprej.
Formula VLOOKUP zdaj uporablja ta edinstvena imena zaposlenih za iskanje ustreznega usposabljanja.
Upoštevajte, da sta $ E2 & COLUMNS ($ F $ 1: F1) iskalna vrednost v formuli. S tem bi imenu zaposlenega dodali številko glede na številko stolpca. Ko se na primer ta formula uporabi v celici F2, vrednost iskanja postane »John1«. V celici G2 postane "John2" itd.
Uporaba formule matrike
Če ne želite spremeniti prvotnega nabora podatkov z dodajanjem pomožnih stolpcev, lahko uporabite tudi formulo matrike, da poiščete drugo, tretjo ali nto vrednost.
Recimo, da imate isti nabor podatkov, kot je prikazano spodaj:
Tu je formula, ki bo vrnila pravilno iskalno vrednost:
= IFERROR (INDEKS ($ B $ 2: $ B $ 14, SMALL (ČE ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1), ""), STOLPCI ($ E $ 1 : E1))), "")
Kopirajte to formulo in jo prilepite v celico E2.
Upoštevajte, da je to matrična formula in morate uporabiti Control + Shift + Enter (držite tipki Control in Shift ter pritisnite tipko Enter), namesto da pritisnete samo tipko Enter.
Kliknite tukaj, če želite prenesti primer datoteke.
Kako deluje ta formula?
Razdelimo to formulo na dele in poglejmo, kako deluje.
$ A $ 2: $ A $ 14 = $ D2
Zgornji del formule primerja vsako celico v A2: A14 z vrednostjo v D2. V tem nizu podatkov preveri, ali celica vsebuje ime »John« ali ne.
Vrne polje TRUE od FALSE. Če ima celica ime "John", bi bilo res, sicer bi bilo napačno.
Spodaj je matrika, ki bi jo dobili v tem primeru:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}
Upoštevajte, da ima TRUE na 1., 7. in 111. mestu, saj je tam v naboru podatkov ime John.
ČE ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, "")
Zgornja formula IF uporablja matriko TRUE in FALSE ter TRUE nadomesti s položajem svojega pojavljanja na seznamu (podano z ROW ($ A $ 2: $ A $ 14) -1) in FALSE z “” (prazna polja). Naslednji niz je rezultat, ki ga dobite s to formulo IF:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Upoštevajte, da sta na seznamu Janezov položaj 1, 7 in 11.
MALO (ČE ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), STOLPCI ($ E $ 1: E1))
Funkcija SMALL zdaj izbere prvo najmanjše, drugo najmanjše, tretje najmanjše število iz tega niza. Upoštevajte, da za ustvarjanje številke stolpca uporablja funkcijo COLUMNS. V celici E2 funkcija COLUMNS vrne 1, funkcija SMALL pa vrne 1. V celici F2 funkcija COLUMNS vrne 2, funkcija SMALL pa 7.
INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), STOLPCI ($ E $ 1: E1) ))
Funkcija INDEX zdaj vrne vrednost s seznama v stolpcu B glede na položaj, ki ga vrne funkcija SMALL. Zato v celici E2 vrne "Excel", ki je prvi element v B2: B14. V celici F2 vrne PowerPoint, ki je sedmi element na seznamu.
Ker obstajajo primeri, ko sta za nekatere zaposlene samo eno ali dve usposabljanji, bi funkcija INDEX vrnila napako. Funkcija IFERROR se uporablja za vrnitev praznine namesto napake.
Upoštevajte, da sem v teh primerih uporabil sklice na obseg. V praktičnih primerih pa je koristno pretvoriti podatke v Excelovo tabelo. S pretvorbo v Excelovo tabelo lahko uporabite strukturirane reference, kar olajša ustvarjanje formul. Poleg tega lahko Excelova tabela samodejno upošteva vse nove elemente usposabljanja, ki so dodani na seznam (zato vam ni treba vsakič prilagajati formul).
Kaj storite, ko morate poiskati drugo, tretjo ali N-to vrednost? Prepričan sem, da obstaja več načinov za to. Če uporabljate kaj lažjega od tistega, ki je naveden tukaj, delite z nami v razdelku za komentarje.
Kliknite tukaj, če želite prenesti primer datoteke.
