Vaadake videot - otsige teine, kolmas või üheksas sobiv väärtus
Andmete otsimisel Excelist on kaks hämmastavat funktsiooni, mida ma sageli kasutan - VLOOKUP ja INDEX (enamasti koos funktsiooniga MATCH).
Need valemid on aga loodud leidmaks ainult otsinguväärtuse esimest eksemplari.
Aga mis siis, kui soovite otsida teist, kolmandat, neljandat või n-ndat väärtust.
Noh, see on teostatav väikese lisatööga.
Selles õpetuses näitan teile erinevaid viise (näidetega), kuidas Excelis teist või n -d väärtust otsida.
Otsige Excelist teist, kolmandat või n -ndat väärtust
Selles õpetuses käsitlen kahte võimalust Exceli teise või n-nda väärtuse otsimiseks:
- Abistajaveeru kasutamine.
- Massiivivalemite kasutamine.
Alustame ja sukeldume otse sisse.
Helperi veeru kasutamine
Oletame, et olete organisatsiooni koolituskoordinaator ja teil on allpool näidatud andmestik. Soovite loetleda kõik koolitused töötaja nime ees.
Ülaltoodud andmekogumis on töötajaid koolitatud erinevate Microsoft Office'i tööriistade (Excel, PowerPoint ja Word) kohta.
Nüüd saate kasutada funktsiooni VLOOKUP või kombinatsiooni INDEX/MATCH, et leida töötaja läbitud koolitus. Siiski tagastab see ainult esimese sobiva eksemplari.
Näiteks Johni puhul on ta läbinud kõik kolm koolitust, kuid kui ma otsin tema nime üles VLOOKUP või INDEX/MATCH abil, kuvatakse see alati 'Excel', mis on tema nime jaoks esimene koolitus nimekirjas .
Selle tegemiseks saame kasutada abiveergu ja luua selles ainulaadseid otsinguväärtusi.
Siin on sammud.
- Sisestage veerg koolituse loetelu veeru ette.
- Lahtrisse B2 sisestage järgmine valem:
= A2 ja COUNTIF ($ A $ 2: $ A2, A2)
- Sisestage lahtrisse F2 järgmine valem ja kopeerige ja kleepige kõik ülejäänud lahtrid.
= IFNA (VLOOKUP ($ E2 & COLUMNS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
Ülaltoodud valem tagastab iga töötaja koolituse loendis kuvatud järjekorras. Kui töötaja jaoks pole koolitust loetletud, tagastab see tühiku.
Kuidas see valem töötab?
Abivahendi veerus olev COUNTIF valem muudab iga töötaja nime ainulaadseks, lisades sellele numbri. Näiteks Johannese esimesest astmest saab John1, teisest John2 ja nii edasi.
VLOOKUP valem kasutab nüüd neid unikaalseid töötajate nimesid sobiva koolituse leidmiseks.
Pange tähele, et $ E2 & COLUMNS ($ F $ 1: F1) on valemi otsinguväärtus. See lisaks töötaja nimele veerunumbri alusel numbri. Näiteks kui seda valemit kasutatakse lahtris F2, muutub otsinguväärtuseks „John1“. Lahtris G2 saab sellest “John2” ja nii edasi.
Massiivivalemi kasutamine
Kui te ei soovi algset andmekogumit abiveergude lisamisega muuta, saate teise, kolmanda või n -nda väärtuse otsimiseks kasutada ka massiivivalemit.
Oletame, et teil on sama andmestik nagu allpool näidatud:
Siin on valem, mis tagastab õige otsinguväärtuse:
= IFERROR (INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, "")), VEERGAD ($ E $ 1 : E1))), "")
Kopeerige see valem ja kleepige see lahtrisse E2.
Pange tähele, et see on massiivivalem ja peate kasutama klahve Control + Shift + Enter (hoidke all klahve Control ja Shift ja vajutage sisestusklahvi), selle asemel, et vajutada ainult sisestusklahvi.
Näidisfaili allalaadimiseks klõpsake siin.
Kuidas see valem töötab?
Jagame selle valemi osadeks ja vaatame, kuidas see toimib.
$ A $ 2: $ A $ 14 = $ D2
Valemi ülaltoodud osas võrreldakse iga lahtrit A2: A14 väärtusega D2. Selles andmestikus kontrollib see, kas lahter sisaldab nime “John” või mitte.
See tagastab massiivi TRUE of FALSE. Kui lahtri nimi on „John”, oleks see tõene, vastasel juhul oleks see vale.
Allpool on toodud näites toodud massiiv:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}
Pange tähele, et sellel on 1., 7. ja 111. positsioonil TRUE, kuna seal on andmekogus nimi John.
IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)
Ülaltoodud IF valem kasutab massiivi TRUE ja FALSE ning asendab TRUE selle esinemise asukohaga loendis (antud ROW ($ A $ 2: $ A $ 14) -1) ja FALSE tähega „” (tühjad). Selle IF -valemiga saadud massiiv on järgmine:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Pange tähele, et 1, 7 ja 11 on Johannese esinemise koht loendis.
VÄIKE (KUI ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), VEERGAD ($ E $ 1: E1))
Funktsioon SMALL valib sellest massiivist nüüd esimese, teise, kolmanda väikseima numbri. Pange tähele, et see kasutab veeru numbri genereerimiseks funktsiooni COLUMNS. Lahtris E2 tagastab funktsioon COLUMNS 1 ja funktsioon SMALL 1. 1. Lahtris F2 tagastab funktsioon COLUMNS 2 ja funktsioon SMALL 7.
INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), VEERGUD ($ E $ 1: E1) ))
Funktsioon INDEX tagastab nüüd veeru B loendi väärtuse funktsiooni SMALL tagastatud positsiooni alusel. Seega tagastab see lahtris E2 'Exceli', mis on B2: B14 esimene üksus. Lahtris F2 tagastab see PowerPointi, mis on loendi 7. element.
Kuna on juhtumeid, kus mõne töötaja jaoks on ainult üks või kaks koolitust, tagastab funktsioon INDEX vea. Funktsiooni IFERROR kasutatakse vea asemel tühiku tagastamiseks.
Pange tähele, et selles näites olen kasutanud vahemiku viiteid. Praktilistes näidetes on aga kasulik teisendada andmed Exceli tabeliks. Exceli tabeliks teisendades saate kasutada struktureeritud viiteid, mis hõlbustab valemite loomist. Samuti saab Exceli tabelis automaatselt arvesse võtta kõiki uusi loendisse lisatud treeningobjekte (seega ei pea te iga kord valemeid kohandama).
Mida teha, kui peate otsima teise, kolmanda või n-nda väärtuse? Olen kindel, et selleks on rohkem võimalusi. Kui kasutate midagi lihtsamat kui siin loetletud, jagage meiega kommentaaride jaotises.
Näidisfaili allalaadimiseks klõpsake siin.
