Adatok keresése és kiemelése Excelben (feltételes formázással)

Videó megtekintése - Adatok keresése és kiemelése feltételes formázással

Ha nagy adathalmazokkal dolgozik, szükség lehet egy olyan keresési funkció létrehozására, amely lehetővé teszi a keresett kifejezés celláinak/sorainak gyors kiemelését.

Bár erre nincs közvetlen módszer az Excelben, a feltételes formázás használatával keresési funkciót hozhat létre.

Tegyük fel például, hogy van egy adatkészlete, amint az alább látható (a képen). A terméknév, értékesítési képviselő és ország oszlopokkal rendelkezik.

Most feltételes formázással kereshet kulcsszót (a C2 cellába való beírással), és kiemelheti az összes olyan kulcsot, amely tartalmazza ezt a kulcsszót.

Valami az alábbiak szerint (ahol beírom a tétel nevét a B2 cellába, és megnyomom az Enter billentyűt, a teljes sor kiemelésre kerül):

Ebben az oktatóanyagban megmutatom, hogyan hozhatja létre ezt a keresési és kiemelési funkciót az Excelben.

Az oktatóanyag későbbi szakaszában kissé haladunk, és megnézzük, hogyan tehetjük dinamikussá (hogy kiemelje, miközben gépel a keresőmezőbe).

Kattintson ide a mintafájl letöltéséhez és kövesse.

Keressen és jelöljön ki megfelelő egységeket

Ebben a részben. Megmutatom, hogyan kell keresni és kiemelni az adatkészletben csak a megfelelő cellákat.

Valami az alábbiak szerint:

Íme a lépések a megfelelő szöveget tartalmazó cellák kereséséhez és kiemeléséhez:

1. Válassza ki azt az adatkészletet, amelyen alkalmazni kívánja a feltételes formázást (ebben a példában A4: F19).
2. Kattintson a Kezdőlap fülre.
3. A Stílusok csoportban kattintson a Feltételes formázás elemre.
4. A legördülő menüben kattintson az Új szabály lehetőségre.
   
5. Az „Új formázási szabály” párbeszédpanelen kattintson a „Képlet használata a formázandó cellák meghatározására” lehetőségre.
6. Írja be a következő képletet: = A4 = $ B $ 1
7. Kattintson a „Formázás…” gombra.
8. Adja meg a formázást (a keresett kulcsszónak megfelelő cellák kiemeléséhez).
9. Kattintson az OK gombra.

Most írjon be bármit a B1 cellába, és nyomja meg az enter billentyűt. Kiemeli az adathalmaz egyező celláit, amelyek a B1 kulcsszót tartalmazzák.

Hogy működik ez?

A feltételes formázás akkor kerül alkalmazásra, amikor a benne megadott képlet IGAZ értéket ad vissza.

A fenti példában minden cellát a képlet segítségével ellenőrzünk = A4 = $ B $ 1

A feltételes formázás minden cellát ellenőriz és ellenőrzi, hogy a cella tartalma megegyezik -e a B1 cellával. Ha ugyanaz, a képlet IGAZ értéket ad vissza, és a cella kiemelésre kerül. Ha nem ugyanaz, a képlet HAMIS értéket ad vissza, és semmi sem történik.

Kattintson ide a mintafájl letöltéséhez és kövesse.

Sorok keresése és kiemelése egyező adatokkal

Ha csak az egyező cellák helyett a teljes sort szeretné kiemelni, akkor ezt megteheti a képlet egy kis finomításával.

Az alábbiakban egy példa látható, ahol a teljes sor kiemelésre kerül, ha a terméktípus megegyezik a B1 cellában szereplővel.

Íme a lépések a teljes sor kereséséhez és kiemeléséhez:

1. Válassza ki azt az adatkészletet, amelyen alkalmazni kívánja a feltételes formázást (ebben a példában A4: F19).
2. Kattintson a Kezdőlap fülre.
3. A Stílusok csoportban kattintson a Feltételes formázás elemre.
4. A legördülő menüben kattintson az Új szabály lehetőségre.
   
5. Az „Új formázási szabály” párbeszédpanelen kattintson a „Képlet használata a formázandó cellák meghatározására” lehetőségre.
6. Írja be a következő képletet: = $ B4 = $ B $ 1
7. Kattintson a „Formázás…” gombra.
8. Adja meg a formázást (a keresett kulcsszónak megfelelő cellák kiemeléséhez).
9. Kattintson az OK gombra.

A fenti lépések a megadott elemet keresik az adatkészletben, és ha megtalálja a megfelelő elemet, akkor a teljes sort kiemeli.

Ne feledje, hogy ez csak a tétel oszlopot fogja ellenőrizni. Ha itt megad egy értékesítési képviselőt, az nem fog működni. Ha azt szeretné, hogy az értékesítési képviselő nevében is működjön, módosítsa a képletet erre = $ C4 = $ B $ 1

Megjegyzés: Az ok, ami miatt kiemeli a teljes sort, és nem csak a megfelelő cellát, az, hogy $ jelet használtunk az oszlophivatkozás előtt ($ B4). Most, amikor a feltételes formázás elemzi a sor celláit, ellenőrzi, hogy az adott sor B oszlopában szereplő érték megegyezik -e a B1 cella értékével. Tehát még akkor is, ha az A4 -et, a B4 -et vagy a C4 -et elemzi, és így tovább, csak a B4 értékét ellenőrzi (mivel a B oszlopot a dollárjel használatával zároltuk).

Az abszolút, relatív és vegyes referenciákról itt olvashat bővebben.

Sorok keresése és kiemelése (részleges egyezés alapján)

Bizonyos esetekben érdemes részleges egyezés alapján kiemelni a sorokat.

Például, ha rendelkezik olyan elemekkel, mint a Fehér tábla, Zöld tábla és Szürke tábla, és mindezt a tábla szó alapján szeretné kiemelni, akkor ezt a KERESÉS funkcióval teheti meg.

Valami az alábbiak szerint:

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

1. Válassza ki azt az adatkészletet, amelyen alkalmazni kívánja a feltételes formázást (ebben a példában A4: F19).
2. Kattintson a Kezdőlap fülre.
3. A Stílusok csoportban kattintson a Feltételes formázás elemre.
4. A legördülő menüben kattintson az Új szabály lehetőségre.
   
5. Az „Új formázási szabály” párbeszédpanelen kattintson a „Képlet használata a formázandó cellák meghatározására” lehetőségre.
6. Írja be a következő képletet: = ÉS ($ B $ 1 ””, ISNUMBER (KERESÉS ($ B $ 1, B4 $)))
7. Kattintson a „Formázás…” gombra.
8. Adja meg a formázást (a keresett kulcsszónak megfelelő cellák kiemeléséhez).
9. Kattintson az OK gombra.

Hogy működik ez?

• A SEARCH függvény a keresési karakterláncot/kulcsszót keresi a sor összes cellájában. Hibát ad vissza, ha a keresési kulcsszó nem található, és számot ad vissza, ha egyezést talál.
• Az ISNUMBER függvény a hibát HAMIS, a számértékeket IGAZ értékre alakítja át.
• ÉS a funkció további feltételt keres - hogy a C2 cella ne legyen üres.

Tehát most, amikor begépel egy kulcsszót a B1 cellába, és megnyomja az Enter billentyűt, az minden olyan sort kiemel, amely tartalmazza a kulcsszót tartalmazó cellákat.

Bónusz tipp: Ha meg szeretné különböztetni a keresőbetűket, használja a KERESÉS funkciót a KERESÉS helyett.

Kattintson ide a mintafájl letöltéséhez és kövesse.

Dinamikus keresés és kiemelés funkció (kiemelés gépelés közben)

Ugyanazokkal a feltételes formázási trükkökkel, amelyeket fent ismertettünk, egy lépéssel tovább léphetünk, és dinamikussá tehetjük.

Létrehozhat például egy keresősávot, ahol a megfelelő adatok kiemelésre kerülnek, amikor gépel a keresősávba.

Valami az alábbiak szerint:

Ezt megteheti az ActiveX -vezérlők használatával, és jó funkcionalitás lehet jelentések vagy irányítópultok létrehozásakor.

Az alábbiakban egy videót mutatok be, hogyan kell ezt létrehozni:

Hasznosnak találta ezt az oktatóanyagot? Mondja el véleményét a megjegyzések részben.

A következő Excel oktatóanyagok is tetszhetnek:

• Dinamikus Excel szűrő - Kivonja az adatokat gépelés közben.
• Hozzon létre egy legördülő listát keresési javaslattal.
• Hőtérkép létrehozása Excelben.
• Sorok kiemelése az Excel cellaértéke alapján.
• Jelölje ki az Aktív sort és oszlopot az Excel adattartományában.
• Az üres cellák kiemelése az Excelben.