Videó megtekintése - Keresse meg a második, a harmadik vagy az egyező értéket
Ami az adatok Excelben történő megkeresését illeti, két csodálatos funkciót használok gyakran - a VLOOKUP és az INDEX (többnyire a MATCH funkcióval együtt).
Ezeket a képleteket azonban úgy tervezték, hogy csak a keresési érték első példányát találják meg.
De mi van akkor, ha a második, harmadik, negyedik vagy az N-es értéket szeretné megkeresni.
Nos, ez egy kis többletmunkával megoldható.
Ebben az oktatóanyagban különböző módszereket mutatok be (példákkal), hogyan lehet megkeresni a második vagy az N értéket az Excelben.
Keresse meg a második, harmadik vagy n. Értéket az Excelben
Ebben az oktatóanyagban kétféleképpen fogok foglalkozni a második vagy az N-es érték megkeresésével Excelben:
- Segítő oszlop használata.
- Tömbképletek használata.
Kezdjük el és merüljünk el.
Segítő oszlop használata
Tegyük fel, hogy Ön egy képzési koordinátor egy szervezetben, és rendelkezik az alább látható adatkészlettel. Az összes képzést a munkavállaló neve előtt szeretné felsorolni.
A fenti adatkészletben az alkalmazottak képzést kaptak a különböző Microsoft Office eszközökről (Excel, PowerPoint és Word).
Most a VLOOKUP funkció vagy az INDEX/MATCH kombináció segítségével megkeresheti az alkalmazott által végzett képzést. Azonban csak az első egyező példányt adja vissza.
Például John esetében mindhárom képzést elvégezte, de amikor a VLOOKUP vagy az INDEX/MATCH segítségével megkeresem a nevét, az mindig az „Excel” -t adja vissza, ami az első képzése a nevének a listában .
Ennek elvégzéséhez használhatunk segítő oszlopot, és egyedi keresési értékeket hozhatunk létre benne.
Íme a lépések:
- Illesszen be egy oszlopot a képzést felsoroló oszlop elé.
- A B2 cellába írja be a következő képletet:
= A2 és COUNTIF ($ A $ 2: $ A2, A2)
- Az F2 cellába írja be a következő képletet, és másolja be a többi cellába:
= IFNA (VLOOKUP ($ E2 & COLUMNS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
A fenti képlet minden alkalmazott esetében visszaadja a képzést a listában szereplő sorrendben. Abban az esetben, ha a munkavállalónak nincs képzése, akkor üres értéket ad vissza.
Hogyan működik ez a képlet?
A segítő oszlopban található COUNTIF képlet minden alkalmazott nevét egyedivé teszi, ha számot ad hozzá. Például János első példánya John1 lesz, a második példány John2 és így tovább.
A VLOOKUP képlet most ezeket az egyedi munkavállalói neveket használja a megfelelő képzés megtalálásához.
Ne feledje, hogy a $ E2 & COLUMNS ($ F $ 1: F1) a képlet keresési értéke. Ez hozzáad egy számot az alkalmazott nevéhez az oszlopszám alapján. Például, ha ezt a képletet használja az F2 cellában, a keresési érték „John1” lesz. A G2 cellában „John2” lesz, és így tovább.
Array képlet használata
Ha nem szeretné megváltoztatni az eredeti adatkészletet segítő oszlopok hozzáadásával, akkor tömbképlet segítségével megkeresheti a második, harmadik vagy n.
Tegyük fel, hogy ugyanaz az adathalmaz van, mint az alábbiakban:
Íme a képlet, amely visszaadja a helyes keresési értéket:
= IFERROR (INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, "")), OSZLOPOK ($ E $ 1 : E1))), "")
Másolja ezt a képletet, és illessze be az E2 cellába.
Ne feledje, hogy ez egy tömbképlet, és a Control + Shift + Enter billentyűkombinációt kell használnia (tartsa lenyomva a Control és Shift billentyűket, és nyomja meg az Enter billentyűt) ahelyett, hogy csak az Enter billentyűt nyomja meg.
Kattintson ide a mintafájl letöltéséhez.
Hogyan működik ez a képlet?
Osszuk szét ezt a képletet részekre, és nézzük meg, hogyan működik.
$ A $ 2: $ A $ 14 = $ D2
A képlet fenti része összehasonlítja az A2: A14 minden celláját a D2 értékkel. Ebben az adatkészletben azt ellenőrzi, hogy egy cella tartalmazza -e a „John” nevet vagy sem.
Egy IGAZ HAMIS tömböt ad vissza. Ha a cella neve „John”, akkor igaz, egyébként hamis.
Az alábbiakban látható a tömb, amelyet ebben a példában kapna:
{IGAZ; HAMIS; HAMIS; HAMIS; HAMIS; HAMIS; IGAZ; HAMIS; HAMIS; HAMIS; IGAZ; HAMIS; HAMIS}
Ne feledje, hogy az IGAZ 1., 7. és 111. pozícióban van, mivel ott van a John név az adathalmazban.
HA ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)
A fenti IF képlet az IGAZ és a HAMIS tömböt használja, és az IGAZ értéket a listában előforduló pozícióval helyettesíti (a ROW ($ A $ 2: $ A $ 14) -1) és a HAMIS értéket a "" (üres) mezővel. Az alábbi képlet a tömböt kapja meg ezzel az IF képlettel:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Ne feledje, hogy az 1, 7 és 11 a János előfordulásának helye a listában.
KIS (HA ($ A $ 2: $ A $ 14 = $ D2, SOR ($ A $ 2: $ A $ 14) -1, ””), OSZLOPOK ($ E $ 1: E1))
A KIS funkció most kiválasztja az első legkisebb, a második legkisebb, a harmadik legkisebb számot ebből a tömbből. Vegye figyelembe, hogy a COLUMNS függvényt használja az oszlopszám létrehozásához. Az E2 cellában a COLUMNS függvény 1 -et, a SMALL függvény pedig 1 -et ad vissza. Az F2 cellában a COLUMNS függvény 2 -t, a SMALL függvény pedig 7 -et ad vissza.
INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), OSZLOPOK ($ E $ 1: E1) ))
Az INDEX függvény most visszaadja az értéket a B oszlopban található listából a SMALL függvény által visszaadott pozíció alapján. Ezért az E2 cellában az „Excel” -t adja vissza, amely a B2: B14 első eleme. Az F2 cellában a PowerPointot adja vissza, amely a lista 7. eleme.
Mivel vannak olyan esetek, amikor néhány alkalmazottnak csak egy vagy két képzése van, az INDEX funkció hibát ad vissza. Az IFERROR függvény segítségével üres értéket adhat vissza a hiba helyére.
Vegye figyelembe, hogy ebben a példában tartományi hivatkozásokat használtam. A gyakorlati példákban azonban előnyös az adatokat Excel táblává alakítani. Ha Excel táblává alakítja, strukturált hivatkozásokat használhat, amelyek megkönnyítik a képletek létrehozását. Ezenkívül az Excel táblázat automatikusan figyelembe veszi a listához hozzáadott új képzési elemeket (így nem kell minden alkalommal módosítani a képleteket).
Mi a teendő, ha meg kell keresnie a második, harmadik vagy az N értéket? Biztos vagyok benne, hogy ennek több módja is van. Ha valami könnyebbet használ, mint az itt felsoroltak, ossza meg velünk mindezt a megjegyzések részben.
Kattintson ide a mintafájl letöltéséhez.
