Žiūrėti vaizdo įrašą - suraskite antrąją, trečiąją ar devintąją atitinkančias vertes
Kalbant apie duomenų paiešką „Excel“, yra dvi nuostabios funkcijos, kurias dažnai naudoju - VLOOKUP ir INDEX (dažniausiai kartu su funkcija MATCH).
Tačiau šios formulės yra skirtos rasti tik pirmąjį paieškos vertės egzempliorių.
Bet ką daryti, jei norite ieškoti antros, trečios, ketvirtos ar N vertės.
Na, tai galima padaryti šiek tiek papildomo darbo.
Šioje pamokoje aš jums parodysiu įvairius būdus (su pavyzdžiais), kaip „Excel“ ieškoti antrosios arba n -tosios vertės.
„Excel“ ieškokite antrosios, trečiosios ar n -tosios vertės
Šioje pamokoje aptarsiu du būdus, kaip „Excel“ ieškoti antrosios arba N vertės:
- Naudojant pagalbinę kolonėlę.
- Naudojant masyvo formules.
Pradėkime ir pasinerkime tiesiai.
Pagalbos stulpelio naudojimas
Tarkime, kad esate organizacijos mokymo koordinatorius ir turite duomenų rinkinį, kaip parodyta žemiau. Visus mokymus norite išvardyti prieš darbuotojo vardą.
Aukščiau pateiktame duomenų rinkinyje darbuotojai buvo mokomi apie įvairius „Microsoft Office“ įrankius („Excel“, „PowerPoint“ ir „Word“).
Dabar galite naudoti funkciją VLOOKUP arba kombinaciją INDEX/MATCH, kad surastumėte darbuotojo baigtą mokymą. Tačiau jis grąžins tik pirmąjį atitinkantį egzempliorių.
Pavyzdžiui, Džono atveju jis išklausė visus tris mokymus, bet kai aš ieškosiu jo vardo naudodami VLOOKUP arba INDEX/MATCH, jis visada pateiks „Excel“, kuris yra pirmasis jo vardo mokymas sąraše .
Norėdami tai padaryti, galime naudoti pagalbinį stulpelį ir jame sukurti unikalias paieškos vertes.
Štai šie veiksmai:
- Prieš stulpelį, kuriame nurodytas mokymas, įterpkite stulpelį.
- B2 langelyje įveskite šią formulę:
= A2 ir COUNTIF ($ A $ 2: $ A2, A2)
- Ląstelėje F2 įveskite šią formulę ir nukopijuokite ir įklijuokite visas kitas ląsteles:
= IFNA (VLOOKUP ($ E2 & COLUMNS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
Aukščiau pateikta formulė grąžintų kiekvieno darbuotojo mokymus tokia tvarka, kokia ji yra sąraše. Jei darbuotojo mokymai nėra išvardyti, grąžinama tuščia vieta.
Kaip veikia ši formulė?
Pagalbos stulpelyje esanti COUNTIF formulė daro kiekvieno darbuotojo vardą unikalų, pridedant prie jo skaičių. Pavyzdžiui, pirmasis Jono egzempliorius tampa Jonas1, antrasis - Jonas2 ir pan.
VLOOKUP formulė dabar naudoja šiuos unikalius darbuotojų vardus, kad surastų atitinkamus mokymus.
Atminkite, kad $ E2 & COLUMNS ($ F $ 1: F1) yra formulės paieškos vertė. Tai pridėtų skaičių prie darbuotojo vardo pagal stulpelio numerį. Pavyzdžiui, kai ši formulė naudojama langelyje F2, paieškos vertė tampa „Jonas1“. G2 langelyje jis tampa „John2“ ir pan.
Naudojant masyvo formulę
Jei nenorite pakeisti pradinio duomenų rinkinio pridėdami pagalbinius stulpelius, taip pat galite naudoti masyvo formulę, kad surastumėte antrąją, trečiąją ar n -ąją reikšmes.
Tarkime, kad turite tą patį duomenų rinkinį, kaip parodyta žemiau:
Čia yra formulė, kuri grąžins teisingą paieškos vertę:
= IFERROR (INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, "")), SKILTYS ($ E $ 1 : E1))), "")
Nukopijuokite šią formulę ir įklijuokite ją į langelį E2.
Atminkite, kad tai yra masyvo formulė ir jums reikia naudoti „Control“ + „Shift“ + „Enter“ (laikykite nuspaudę klavišus „Control“ ir „Shift“ ir paspauskite klavišą „Enter“), o ne paspauskite tik klavišą „Enter“.
Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą.
Kaip veikia ši formulė?
Suskaidykime šią formulę į dalis ir pažiūrėkime, kaip ji veikia.
$ A $ 2: $ A $ 14 = $ D2
Aukščiau pateikta formulės dalis lygina kiekvieną A2: A14 langelį su D2 reikšme. Šiame duomenų rinkinyje ji tikrina, ar langelyje yra vardas „Jonas“, ar ne.
Pateikiamas masyvas TRUE of FALSE. Jei langelis turi pavadinimą „Jonas“, tai būtų tiesa, kitaip jis būtų klaidingas.
Žemiau yra masyvas, kurį gausite šiame pavyzdyje:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}
Atminkite, kad 1, 7 ir 111 pozicijose jis turi TRUE, nes duomenų rinkinyje yra vardas Jonas.
IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, "")
Aukščiau pateikta IF formulė naudoja masyvą TRUE ir FALSE ir pakeičia TRUE į jo atsiradimo vietą sąraše (nurodoma ROW ($ A $ 2: $ A $ 14) -1), o FALSE-su „“ (tuščios vietos). Toliau pateikiamas gautas masyvas naudojant šią IF formulę:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Atkreipkite dėmesį, kad 1, 7 ir 11 yra Jono atsiradimo sąraše vieta.
MAŽAS (JEI ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), SKILTYS ($ E $ 1: E1))
Funkcija SMALL iš šio masyvo parenka pirmą, antrą, trečią, mažiausią skaičių. Atminkite, kad stulpelio numeriui generuoti naudojama funkcija SKILTYS. E2 langelyje funkcija COLUMNS grąžina 1, o funkcija SMALL - 1. F2 langelyje funkcija COLUMNS grąžina 2, o funkcija SMALL grąžina 7.
INDEKSAS ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)), SKILTYS ($ E $ 1: E1) ))
Funkcija INDEX dabar grąžina vertę iš B stulpelio sąrašo, atsižvelgdama į funkciją SMALL. Taigi E2 langelyje jis grąžina „Excel“, kuris yra pirmasis elementas B2: B14. F2 langelyje pateikiama „PowerPoint“, kuri yra septintasis sąrašo elementas.
Kadangi yra atvejų, kai kai kuriems darbuotojams yra tik vienas ar du mokymai, funkcija INDEX grąžina klaidą. Funkcija IFERROR naudojama grąžinti tuščią vietą klaidos vietoje.
Atminkite, kad šiuose pavyzdžiuose naudojau diapazono nuorodas. Tačiau praktiniuose pavyzdžiuose naudinga paversti jo duomenis į „Excel“ lentelę. Konvertuodami į „Excel“ lentelę, galite naudoti struktūrines nuorodas, kurios palengvina formulių kūrimą. Be to, „Excel“ lentelėje gali būti automatiškai atsižvelgiama į visus naujus mokymo elementus, įtrauktus į sąrašą (todėl jums nereikia kiekvieną kartą koreguoti formulių).
Ką daryti, kai reikia ieškoti antros, trečios ar N vertės? Esu tikras, kad yra daugiau būdų tai padaryti. Jei naudojate ką nors lengvesnio nei čia išvardytas, pasidalykite su mumis komentarų skiltyje.
Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą.
