Legördülő szűrő létrehozása az adatok kinyeréséhez a kiválasztás alapján

Videó megtekintése - Adatok kinyerése az Excel legördülő listájával

Ebben az oktatóanyagban megmutatom, hogyan hozhat létre egy legördülő szűrőt az Excelben, hogy a legördülő menüből kiválasztott adatok alapján kinyerhesse az adatokat.

Amint az alábbi képen látható, létrehoztam egy legördülő listát országnevekkel. Amint bármelyik országot kiválasztom a legördülő menüből, az adott ország adatai jobbra kerülnek.

Ne feledje, hogy amint kiválasztom Indiát a legördülő szűrőből, az India összes rekordja kibontásra kerül.

Az adatok kivonása az Excel legördülő listából

Az alábbi lépésekkel hozhat létre egy legördülő szűrőt, amely kinyerheti a kiválasztott elem adatait:

1. Hozzon létre egyedi elemek listáját.
2. Adjon hozzá egy legördülő szűrőt az egyedi elemek megjelenítéséhez.
3. Segédoszlopok segítségével bontsa ki a kijelölt elem rekordjait.

Merüljünk mélyen, és nézzük meg, mit kell tenni ezekben a lépésekben.

Hozzon létre egyedi elemlistát

Bár előfordulhat, hogy az adathalmaz eleme ismétlődik, egyedi elemnevekre van szükségünk, hogy egy legördülő szűrőt hozhassunk létre.

A fenti példában az első lépés az összes ország egyedi listájának megszerzése.

Az alábbi lépésekkel készíthet egyedi listát:

1. Válassza ki az összes országot, és illessze be a munkalap más részébe.
2. Lépjen az Adatok -> Másolatok eltávolítása elemre.
3. Az Ismétlődések eltávolítása párbeszédpanelen válassza ki azt az oszlopot, amelyben az országok listája található. Ezzel egyedi listát kap, amint az alább látható.

Most ezt az egyedi listát fogjuk használni a legördülő lista létrehozásához.

Lásd még: A végső útmutató az ismétlődések megkereséséhez és eltávolításához az Excelben.

A legördülő szűrő létrehozása

Az alábbi lépésekkel hozhat létre egy legördülő listát egy cellában:

1. Lépjen az Adatok -> Adatellenőrzés menüpontra.
2. Az Adatok érvényesítése párbeszédpanelen válassza a Beállítások fület.
3. A Beállítások lapon válassza a „Lista” lehetőséget a legördülő menüben, majd a „Forrás” mezőben válassza ki az általunk létrehozott országok egyedi listáját.
4. Kattintson az OK gombra.

A cél most az, hogy válasszon ki egy országot a legördülő listából, és ez adja meg az ország rekordjainak listáját.

Ehhez segítő oszlopokat és képleteket kell használnunk.

Hozzon létre segédoszlopokat a kijelölt elem rekordjainak kibontásához

Amint kiválasztja a legördülő listát, szüksége van az Excelre, hogy automatikusan azonosítsa a kiválasztott elemhez tartozó rekordokat.

Ezt három segítő oszlop segítségével lehet megtenni.

A segédoszlopok létrehozásának lépései a következők:

• Segédoszlop #1 - Adja meg az összes rekord sorozatszámát (ebben az esetben 20, ehhez használhatja a ROWS () funkciót).
• 2. segédoszlop - Használja ezt az egyszerű IF függvény funkciót: = IF (D4 = $ H $ 2, E4, ””)
  • Ez a képlet ellenőrzi, hogy az első sorban lévő ország megegyezik -e a legördülő menüben szereplő országgal. Tehát ha az Indiát választom, akkor ellenőrzi, hogy az első sor India -e vagy sem. Ha igaz, akkor visszaadja ezt a sorszámot, ellenkező esetben üresen adja vissza („”). Most, amikor bármelyik országot kiválasztjuk, csak azok a sorszámok jelennek meg (a második segédoszlopban), amelyekben a kiválasztott ország szerepel. (Például, ha India van kiválasztva, akkor az alábbi képen fog kinézni).

Most csak ezen sorok adatait kell kinyernünk, amelyek a számot jelenítik meg (mivel ez a sor tartalmazza az adott országot). Azonban ezeket a rekordokat egymás után üresen akarjuk. Ezt megteheti egy harmadik segítő oszlop használatával

• Harmadik segítő oszlop - Használja a következő IFERROR és SMALL funkciók kombinációját:
  = HIBÁS (KIS ($ F $ 4: $ F $ 23, E4), ””)

Ez adna nekünk valamit, amint az alábbi képen látható:

Most, amikor a szám együtt van, csak ki kell nyernünk az adott szám adatait. Ez könnyen elvégezhető az INDEX függvénnyel (használja ezt a képletet azokban a cellákban, ahol az eredmény kivonatára van szüksége):
= HIBÁK (INDEX ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ J $ 3: J3)), ””)

Ez a képlet 2 részből áll:
INDEX - Ez az adatokat a sorszám alapján vonja ki
IFERROR - Ez a funkció üresen tér vissza, ha nincs adat

Itt egy pillanatkép arról, hogy végül mit kaptál:

Ha szeretné, most elrejtheti az eredeti adatokat. Ezenkívül két különböző munkalapon is megtalálhatja az eredeti adatokat és a kinyert adatokat.

Menj tovább. használja ezt a technikát, és nyűgözze le főnökét és kollégáit (egy kis mutogatás sosem rossz dolog).

Töltse le a példa fájlt

Tetszett az oktatóanyag? Mondja el véleményét a megjegyzések részben.

Az alábbi oktatóanyagok is hasznosak lehetnek:

• Dinamikus Excel szűrő - Adatok kivonása gépelés közben.
• Dinamikus keresés az Excelben feltételes formázással.
• Dinamikus legördülő menü létrehozása keresési javaslatokkal.
• Hogyan lehet kibontani egy alstringet az Excelben képletek segítségével.
• A cellák szűrése vastag betűformázással Excelben.