Keresőmező létrehozása az Excelben – lépésről lépésre
Keresőmező létrehozása az Excelben javítja a táblázatok funkcionalitását azáltal, hogy megkönnyíti az egyes adatok szűrését és gyors elérését. Ez az útmutató a keresőmező megvalósításának számos módszerét ismerteti, amelyek az Excel különböző verzióira vonatkoznak. Akár kezdő, akár haladó felhasználó, ezek a lépések segítenek dinamikus keresőmező beállításában olyan funkciók használatával, mint a SZŰRŐ funkció, a Feltételes formázás és a különböző képletek.
- Könnyen létrehozhat keresőmezőt a SZŰRŐ funkció
(elérhető Excel 2019-ben és újabb verziókban, Excel for Microsoft 365)
- Hozzon létre egy keresőmezőt a segítségével Feltételes formázás
(minden Excel verzióban elérhető)
- Hozzon létre egy keresőmezőt a képlet kombinációk
(minden Excel verzióban elérhető)
Könnyen létrehozhat keresőmezőt a FILTER funkcióval
- Ez a funkció automatikusan frissíti a kimenetet az adatok változásával.
- A SZŰRŐ függvény tetszőleges számú eredményt adhat vissza, egyetlen sortól több ezerig, attól függően, hogy az adatkészletben hány bejegyzés felel meg a beállított feltételeknek.
Itt megmutatom, hogyan használhatja a FILTER funkciót keresőmező létrehozásához az Excelben.
1. lépés: Helyezzen be egy szövegmezőt, és konfigurálja a tulajdonságokat
- Menj a Fejlesztő fülre kattintva betétlap > Text Box (ActiveX Control).
típus: Ha a Fejlesztő lap nem jelenik meg a szalagon, engedélyezheti az oktatóanyag utasításait követve: Hogyan lehet megjeleníteni / megjeleníteni a fejlesztői lapot az Excel szalagban?
- A kurzor keresztté változik, majd el kell húznia a kurzort, hogy megrajzolja a szövegdobozt arra a helyre a munkalapon, ahová a szövegdobozt el szeretné helyezni. A szövegdoboz megrajzolása után engedje fel az egeret.
- Kattintson a jobb gombbal a szövegmezőre, és válassza ki Ingatlanok a helyi menüből.
- A Ingatlanok panelen, kapcsolja össze a szövegmezőt egy cellával a cellahivatkozás beírásával LinkedCell terület. Például írja be, hogy "J2" biztosítja, hogy a szövegmezőbe beírt adatok automatikusan frissüljenek a J2 cellában, és fordítva.
- Kattintson az Tervezési mód alatt a Fejlesztő fület a Tervezési módból való kilépéshez.
A szövegmező most lehetővé teszi a szöveg bevitelét.
2. lépés: Alkalmazza a FILTER funkciót
- A SZŰRŐ funkció használata előtt másolja át az eredeti fejlécet egy új területre. Itt helyezem el a fejlécet a keresőmező alá.
típus: Ez a megközelítés lehetővé teszi a felhasználók számára, hogy egyértelműen lássák az eredményeket az eredeti adatokkal azonos oszlopfejlécek alatt.
- Válassza ki az első fejléc alatti cellát (pl I5 ebben a példában), írja be a következő képletet, és nyomja meg a gombot belép kulcs az eredmény eléréséhez.
=FILTER(Sheet2!$A$5:$G$281,Sheet2!$B$5:$B$281=J2,"No data found")
Amint a fenti képernyőképen látható, mivel a szövegmezőnek most nincs bevitele, a képlet megjeleníti az eredményt "Nem található adat" ban ben I5.
- Ebben a képletben:
- 2. lap!$5:$281 G$: $A$5:$G$281 az az adattartomány, amelyet a 2. munkalapon szűrni szeretne.
- 2. lap!$B$5:$B$281=J2: Ez a rész határozza meg a tartomány szűrésére használt feltételeket. Ellenőrzi a B oszlop minden celláját a 5. munkalap 281. sorától a 2. sorig, hogy lássa, megegyezik-e a J2 cellában lévő értékkel. A J2 a keresőmezőhöz kapcsolódó cella.
- Nem található adat: Ha a SZŰRŐ függvény nem talál olyan sorokat, ahol a B oszlopban lévő érték megegyezik a J2 cellában lévő értékkel, akkor a "Nem található adat" üzenetet adja vissza.
- Ez a módszer kis-és nagybetűk, vagyis egyezni fog a szöveggel, függetlenül attól, hogy nagy- vagy kisbetűket ír-e be.
Eredmény: Tesztelje a keresőmezőt
Most teszteljük a keresőmezőt. Ebben a példában, amikor beírom egy ügyfél nevét a keresőmezőbe, a megfelelő találatok kiszűrve és azonnal megjelennek.
Hozzon létre egy keresőmezőt a feltételes formázással
A feltételes formázás használható a keresési kifejezésnek megfelelő adatok kiemelésére, így közvetetten keresőmező-effektust hozhat létre. Ez a módszer nem szűri ki az adatokat, hanem vizuálisan elvezeti a megfelelő cellákhoz. Ez a rész bemutatja, hogyan hozhat létre keresőmezőt az Excel feltételes formázásával.
1. lépés: Helyezzen be egy szövegmezőt, és konfigurálja a tulajdonságokat
- Menj a Fejlesztő fülre kattintva betétlap > Text Box (ActiveX Control).
típus: Ha a Fejlesztő lap nem jelenik meg a szalagon, engedélyezheti az oktatóanyag utasításait követve: Hogyan lehet megjeleníteni / megjeleníteni a fejlesztői lapot az Excel szalagban?
- A kurzor keresztté változik, majd el kell húznia a kurzort, hogy megrajzolja a szövegdobozt arra a helyre a munkalapon, ahová a szövegdobozt el szeretné helyezni. A szövegdoboz megrajzolása után engedje fel az egeret.
- Kattintson a jobb gombbal a szövegmezőre, és válassza ki Ingatlanok a helyi menüből.
- A Ingatlanok panelen, kapcsolja össze a szövegmezőt egy cellával a cellahivatkozás beírásával LinkedCell terület. Például írja be, hogy "J3" biztosítja, hogy a szövegmezőbe beírt adatok automatikusan frissüljenek a J3 cellában, és fordítva.
- Kattintson az Tervezési mód alatt a Fejlesztő fület a Tervezési módból való kilépéshez.
A szövegmező most lehetővé teszi a szöveg bevitelét.
2. lépés: Alkalmazza a feltételes formázást az adatok kereséséhez
- Válassza ki a keresendő teljes adattartományt. Itt az A3:G279 tartományt választom.
- Alatt Kezdőlap fülre kattintva Feltételes formázás > Új szabály.
- A Új formázási szabály párbeszédablak:
- választ Képlet segítségével határozza meg, hogy mely cellákat formázza a Válasszon egy szabálytípust lehetőségeket.
- Írja be a következő képletet a Formázza az értékeket, ahol ez a képlet igaz doboz.
=$B3=$J$3
Itt, B3 USD az oszlop első celláját jelöli, amelyet a kiválasztott tartományban a keresési feltételekkel kíván egyeztetni, és 3 $ J$ a keresőmezőhöz kapcsolódó cella. - Kattintson az kialakított gombot a keresési eredmények kitöltési színének megadásához.
- Kattintson az OK gomb. Lásd a képernyőképet:
Eredmény
Most teszteljük a keresőmezőt. Ebben a példában, amikor beírom egy ügyfél nevét a keresőmezőbe, a megfelelő sorok, amelyek ezt az ügyfelet tartalmazzák a B oszlopban, azonnal kiemelve lesznek a megadott kitöltési színnel.
Hozzon létre egy keresőmezőt képletkombinációkkal
Ha nem az Excel legújabb verzióját használja, és nem csak a sorokat szeretné kiemelni, az ebben a részben leírt módszer hasznos lehet. Az Excel-képletek kombinációjával létrehozhat funkcionális keresőmezőt az Excel bármely verziójában. Kérjük, kövesse az alábbi lépéseket.
1. lépés: Hozzon létre egy listát az egyedi értékekről a keresőoszlopból
- Ebben az esetben kijelölöm és kimásolom a tartományt B4: B281 egy új munkalapra.
- A tartomány új munkalapba történő beillesztése után tartsa kijelölve a beillesztett adatokat, lépjen a dátum Lapot és válassza ki Távolítsa el a másolatokat.
- A megnyitón Távolítsa el a másolatokat párbeszédpanelen kattintson a OK gombot.
- A Microsoft Excel prompt mezőben jelenik meg, hogy megmutassa, hány másolatot távolított el. Kattintson OK.
- Az ismétlődések eltávolítása után jelölje ki az összes egyedi értéket a listában, kivéve a fejlécet, és adjon nevet ehhez a tartományhoz a Név doboz. Itt a tartományt így neveztem el Vevő.
2. lépés: Szúrjon be egy kombinált mezőt, és konfigurálja a tulajdonságokat
- Menjen vissza a keresni kívánt adatkészletet tartalmazó munkalaphoz. Menj a Fejlesztő fülre kattintva betétlap > Combo Box (ActiveX vezérlő).
típus: Ha a Fejlesztő lap nem jelenik meg a szalagon, engedélyezheti az oktatóanyag utasításait követve: Hogyan lehet megjeleníteni / megjeleníteni a fejlesztői lapot az Excel szalagban?
- A kurzor keresztté változik, majd a kurzor húzásával meg kell rajzolnia a kombinált mezőt a munkalap azon helyére, ahová a keresőmezőt el szeretné helyezni. A kombinált doboz megrajzolása után engedje el az egeret.
- Kattintson jobb gombbal a kombinált mezőre, és válassza ki Ingatlanok a helyi menüből.
- A Ingatlanok ablaktábla:
- Kapcsolja össze a kombinált mezőt egy cellával a cella hivatkozásának megadásával LinkedCell terület. neki írok "M2".
Tipp: A mező megadása biztosítja, hogy a kombinált mezőbe beírt adatok automatikusan frissüljenek az M2 cellában, és fordítva.
- A ListFillRange mezőbe írja be a tartomány neve az 1. lépésben megadott egyedi listához.
- Változtasd meg a MatchEntry mezőt 2 – fmMatchEntryNone.
- Csukja be a Ingatlanok ablaktábla.
- Kapcsolja össze a kombinált mezőt egy cellával a cella hivatkozásának megadásával LinkedCell terület. neki írok "M2".
- Kattintson az Tervezési mód alatt a Fejlesztő fület a Tervezési módból való kilépéshez.
Mostantól bármelyik elemet kiválaszthatja a kombinált mezőből, vagy beírhatja a keresendő szöveget.
3. lépés: Alkalmazzon képleteket
- Hozzon létre három segédoszlopot az eredeti adattartomány mellett. Lásd a képernyőképet:
- A cellában (H5) az első segítő oszlop fejlécébe írja be a következő képletet, és nyomja meg a gombot belép.
=ROWS($B$5:B5)
Itt B5 az a cella, amely a keresendő oszlop első ügyfélnevét tartalmazza. - Kattintson duplán a képletcella jobb alsó sarkára, a következő cella automatikusan kitölti ugyanazt a képletet.
- A cellában (I5) a második segítő oszlop fejlécébe írja be a következő képletet, és nyomja meg a gombot belép. Ezután kattintson duplán a képletcella jobb alsó sarkára, hogy automatikusan kitöltse az alábbi cellákat ugyanazzal a képlettel.
=IF(ISNUMBER(SEARCH($M$2,B5)),H5,"")
Itt M2 a cella a kombinált dobozhoz kapcsolódik. - A cellában (J5) a harmadik segítő oszlop fejlécébe írja be a következő képletet, és nyomja meg a gombot belép. Ezután kattintson duplán a képletcella jobb alsó sarkára, hogy automatikusan kitöltse az alábbi cellákat ugyanazzal a képlettel.
=IFERROR(SMALL($I$5:$I$281,H5),"")
- Másolja az eredeti fejlécsort egy új területre. Itt helyezem el a fejlécet a keresőmező alá.
- Válassza ki az első fejléc alatti cellát (pl L5 ebben a példában), írja be a következő képletet, és nyomja meg az Enter billentyűt.
=IFERROR(INDEX($A$5:$G$281,$J5,COLUMNS($L$4:L4)),"")
Itt A5: G281 az eredménycellában megjeleníteni kívánt teljes adattartomány. - Jelölje ki ezt a képletcellát, húzza a Töltse meg a fogantyút jobbra, majd lefelé, hogy a képletet a megfelelő oszlopokra és sorokra alkalmazza.
Megjegyzések:
- Mivel a keresőmezőben nincs bevitel, a képlet eredményei a nyers adatokat jelenítik meg.
- Ez a módszer nem tesz különbséget a kis- és nagybetűk között, ami azt jelenti, hogy egyezni fog a szöveggel, függetlenül attól, hogy nagy- vagy kisbetűket ír-e be.
Eredmény
Most teszteljük a keresőmezőt. Ebben a példában, amikor beírom vagy kiválasztom egy ügyfél nevét a kombinált mezőből, a megfelelő sorok, amelyek az adott ügyfél nevét a B oszlopban tartalmazzák, kiszűrik és azonnal megjelennek az eredménytartományban.
Keresőmező létrehozása az Excelben jelentősen javíthatja az adatokkal való interakciót, így a táblázatok dinamikusabbak és felhasználóbarátabbak. Függetlenül attól, hogy a SZŰRŐ funkció egyszerűségét, a feltételes formázás vizuális segítségét vagy a képletkombinációk sokoldalúságát választja, mindegyik módszer értékes eszközöket biztosít az adatkezelési képességek javításához. Kísérletezzen ezekkel a technikákkal, hogy megtalálja, melyik a legmegfelelőbb az Ön speciális igényeinek és adatforgatókönyveinek. Azok számára, akik szeretnének mélyebbre ásni az Excel képességeit, weboldalunk rengeteg oktatóanyagot kínál. Itt találhat további Excel tippeket és trükköket.
Kapcsolódó cikkek
A végső útmutató az Excel kereshető legördülő listájához
Ez az útmutató négy módszert mutat be egy kereshető legördülő lista beállításához az Excelben.
Keresés és kiemelés a keresési eredmények között az Excelben
Ez a cikk két különböző módszert mutat be az Excelben való keresés és a találatok kiemelésének egyidejű segítésére.
Keresse meg az egyező értéket az Excelben felfelé keresve
Általában egy Excel oszlopban találunk egyező értékeket felülről lefelé. Mit szólnál, ha felfelé keresve találnál egyező értéket? Ez a cikk bemutatja a módszereket ennek elérésére.
Keresési érték az összes megnyitott Excel-munkafüzetben
Ez a cikk bemutatja az értékek vagy szövegek keresési módszereit az aktuális munkafüzetben, valamint az összes megnyitott munkafüzetben.
A legjobb irodai hatékonyságnövelő eszközök
Töltsd fel Excel-készségeidet a Kutools for Excel segítségével, és tapasztald meg a még soha nem látott hatékonyságot. A Kutools for Excel több mint 300 speciális funkciót kínál a termelékenység fokozásához és az időmegtakarításhoz. Kattintson ide, hogy megszerezze a leginkább szükséges funkciót...
Az Office lap füles felületet hoz az Office-ba, és sokkal könnyebbé teszi a munkáját
- Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
- Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
- 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!