Dinamikus Excel szűrő keresőmező (adatok kivonása gépelés közben)

Az Excel szűrő az egyik leggyakrabban használt funkció, amikor adatokkal dolgozik. Ebben a blogbejegyzésben megmutatom, hogyan hozhat létre dinamikus Excel szűrő keresési mezőt, hogy az szűrje az adatokat a keresőmezőbe beírtak alapján.

Valami az alábbiak szerint:

Ennek kettős funkciója van - kiválaszthatja az ország nevét a legördülő listából, vagy manuálisan beírhatja az adatokat a keresőmezőbe, és megjelenik az összes megfelelő rekord. Például, amikor beírja az „I” -t, az összes országnevet megadja az I. ábécével.

Videó megtekintése - Dinamikus Excel szűrő keresőmező létrehozása

Dinamikus Excel szűrő keresési mező létrehozása

Ez a dinamikus Excel szűrő 3 lépésben hozható létre:

1. Az elemek egyedi listájának megszerzése (ebben az esetben országok). Ezt használják a legördülő menü létrehozásához.
2. A keresőmező létrehozása. Itt egy kombinált dobozt (ActiveX Control) használtam.
3. Az adatok beállítása. Itt három segítő oszlopot használnék képletekkel az egyező adatok kinyeréséhez.

A nyers adatok így néznek ki:

HASZNOS TIPP: Szinte mindig jó ötlet az adatokat Excel táblává alakítani. Ezt úgy teheti meg, hogy az adatkészlet bármely celláját kijelöli, és a Control + T billentyűparancsot használja.

1. lépés - Egyedi tételek listájának megszerzése

1. Válassza ki az összes országot, és illessze be egy új munkalapba.
2. Válassza ki az országlistát -> Ugrás az adatokhoz -> Másolatok eltávolítása.
3. Az Ismétlődések eltávolítása párbeszédpanelen válassza ki azt az oszlopot, amelyben a lista található, majd kattintson az OK gombra. Ezzel eltávolítja az ismétlődéseket, és egyedi listát kap az alábbiak szerint:
4. Egy további lépés az elnevezett tartomány létrehozása ehhez az egyedi listához. Ezt csináld meg:
   • Lépjen a Képlet fülre -> Név definiálása
   • A Név meghatározása párbeszédpanelen:
     • Név: CountryList
     • Hatály: Munkafüzet
     • A következőre vonatkozik: = UniqueList! $ A $ 2: $ A $ 9 (A listát az A2: A9 UniqueList nevű külön lapján találom. Bárhol hivatkozhat az egyedi listára)

MEGJEGYZÉS: Ha az „Ismétlődések eltávolítása” módszert használja, és kibővíti adatait további rekordok és új országok hozzáadásával, akkor ezt a lépést újra meg kell ismételnie. Alternatív megoldásként egy képletet is készíthet a folyamat dinamikussá tételéhez.

2. lépés - A dinamikus Excel szűrő keresőmező létrehozása

Ahhoz, hogy ez a technika működjön, létre kell hoznunk egy „keresőmezőt”, és össze kell kapcsolnunk egy cellával.

A keresőmező szűrőjének létrehozásához használhatjuk az Excel kombinált mezőjét. Ily módon, amikor bármit beír a kombinált mezőbe, az valós időben is megjelenik egy cellában (amint az alább látható).

Ehhez tegye a következő lépéseket:

1. Lépjen a Fejlesztő fülre -> Vezérlők -> Beszúrás -> ActiveX -vezérlők -> Kombinált doboz (ActiveX -vezérlők).
   • Ha nem látja a Fejlesztői lapot, akkor az alábbi lépésekkel engedélyezheti azt.
2. Kattintson bárhová a munkalapon. Helyezze be a kombinált dobozt.
3. Kattintson a jobb gombbal a kombinált mezőre, és válassza a Tulajdonságok lehetőséget.
4. A Tulajdonságok ablakban hajtsa végre a következő módosításokat:
   • Linkelt cella: K2 (bármelyik cellát kiválaszthatja, ahol azt szeretné, hogy a bemeneti értékek megjelenjenek. Ezt a cellát fogjuk használni az adatok beállításakor).
   • ListFillRange: CountryList (ezt a tartományt hoztuk létre az 1. lépésben. Ez az összes országot megjeleníti a legördülő menüben).
   • MatchEntry: 2-fm
5. A Kombinált doboz kiválasztásával lépjen a Fejlesztő fülre -> Vezérlők -> Kattintson a Tervezési mód lehetőségre (ez kilép a tervezési módból, és most bármit beírhat a kombinált mezőbe. Most, bármit beír, a K2 cella tükröződik. valós időben)

3. lépés - Az adatok beállítása

Végül mindent segítő oszlopokkal kapcsolunk össze. Itt három segítő oszlopot használok az adatok szűrésére.

Segédoszlop 1: Adja meg az összes rekord sorozatszámát (ebben az esetben 20 -at). Ehhez használhatja a ROWS () képletet.

2. segédoszlop: A 2. segédoszlopban ellenőrizzük, hogy a keresőmezőbe beírt szöveg megegyezik -e az ország oszlop celláinak szövegével.

Ezt az IF, ISNUMBER és SEARCH függvények kombinációjával lehet megtenni.

Íme a képlet:

= HA (ISNUMBER (KERESÉS ($ K $ 2, D4)), E4, "")

Ez a képlet megkeresi a tartalmat a keresőmezőben (amely a K2 cellához kapcsolódik) az ország nevével rendelkező cellában.

Ha van egyezés, akkor ez a képlet a sorszámot adja vissza, ellenkező esetben üres. Például, ha a kombinált doboz értéke „US”, akkor az „US” országként szereplő összes rekord sorszáma, a többi pedig üres („”)

Segédoszlop 3: A 3. segédoszlopban a 2. segítő oszlop összes sorszámát össze kell gyűjtenünk. Ehhez IFERROR és SMALL képletek kombinációját használhatjuk. Íme a képlet:

= HIBÁS (KIS ($ F $ 4: $ F $ 23, E4), "")

Ez a képlet egymásra rakja az összes egyező sorszámot. Például, ha a kombinált mező értéke US, akkor az összes „US” betűvel ellátott sorszám egymásra rakódik.

Most, amikor a sorszámokat összeraktuk, csak ki kell nyernünk az adatokat a sorból. Ez könnyen elvégezhető az indexképlet használatával (illessze be ezt a képletet oda, ahová ki kívánja vonni az adatokat. Másolja be a bal felső cellába, ahová az adatokat ki szeretné vonni, majd húzza lefelé és jobbra).

= IFERROR (INDEX ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ I $ 3: I3)), "")

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

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

A kombinált mező egy legördülő menü, valamint egy keresőmező. Elrejtheti az eredeti adatokat és a segédoszlopokat, hogy csak a szűrt rekordok jelenjenek meg. A nyers adatokat és a segédoszlopokat más lapon is megtalálhatja, és létrehozhatja ezt a dinamikus Excel szűrőt egy másik munkalapon.

Legyen kreatív! Próbáljon ki néhány variációt

Kipróbálhatja és személyre szabhatja igényeinek megfelelően. Érdemes több excel szűrőt létrehozni egy helyett. Szűrheti például azokat a rekordokat, ahol az értékesítési képviselő Mike, az ország pedig Japán. Ezt pontosan ugyanazokat a lépéseket követve lehet elvégezni a segédoszlopok képletének némi módosításával.

Egy másik változat lehet a kombinált mezőbe beírt karakterekkel kezdődő adatok szűrése. Például, amikor beírja az „I” -t, érdemes I -vel kezdődő országokat kinyerni (a jelenlegi konstrukcióhoz képest, ahol Szingapúr és Fülöp -szigetek is megadná, mivel az I ábécét tartalmazza).

Mint mindig, a legtöbb cikkemet az olvasóim kérdései/válaszai ihlették. Szeretném kérni a véleményét és tanulni tőletek. Hagyja gondolatait a megjegyzések részben.

Megjegyzés: Ha Office 365 -öt használ, akkor a SZŰRŐ funkció segítségével gyorsan szűrheti az adatokat gépelés közben. Könnyebb, mint az ebben az oktatóanyagban bemutatott módszer.