Sledujte video - vyhľadávajte a zvýrazňujte údaje pomocou podmieneného formátovania
Ak pracujete s veľkými množinami údajov, môže byť potrebné vytvoriť funkciu vyhľadávania, ktorá vám umožní rýchlo zvýrazniť bunky/riadky pre hľadaný výraz.
Aj keď v programe Excel neexistuje žiadny priamy spôsob, ako to urobiť, môžete vytvárať funkcie vyhľadávania pomocou podmieneného formátovania.
Predpokladajme napríklad, že máte množinu údajov, ako je uvedené nižšie (na obrázku). Obsahuje stĺpce pre názov produktu, obchodného zástupcu a krajinu.
Teraz môžete pomocou podmieneného formátovania vyhľadať kľúčové slovo (zadaním do bunky C2) a zvýrazniť všetky bunky, ktoré dané kľúčové slovo obsahujú.
Niečo, ako je uvedené nižšie (kde do bunky B2 zadám názov položky a stlačím kláves Enter, sa zvýrazní celý riadok):
V tomto tutoriále vám ukážem, ako vytvoriť toto vyhľadávanie a zvýrazniť funkcie v programe Excel.
Neskôr v tomto návode pôjdeme trochu pokročilejšie a uvidíme, ako ho dynamizovať (aby sa zvýrazňoval pri písaní do vyhľadávacieho poľa).
Kliknutím sem stiahnete ukážkový súbor a choďte ďalej.
Vyhľadajte a zvýraznite zodpovedajúce bunky
V tejto časti. Ukážem vám, ako v množine údajov vyhľadávať a zvýrazňovať iba zodpovedajúce bunky.
Niečo, ako je uvedené nižšie:
Tu sú kroky na vyhľadanie a zvýraznenie všetkých buniek, ktoré majú zodpovedajúci text:
- Vyberte množinu údajov, na ktorú chcete použiť podmienené formátovanie (v tomto prípade A4: F19).
- Kliknite na kartu Domov.
- V skupine Štýly kliknite na Podmienené formátovanie.
- V rozbaľovacích možnostiach kliknite na položku Nové pravidlo.
- V dialógovom okne „Nové pravidlo formátovania“ kliknite na možnosť „Pomocou vzorca určiť, ktoré bunky sa majú formátovať“.
- Zadajte nasledujúci vzorec: = A4 = $ B $ 1
- Kliknite na tlačidlo „Formátovať …“.
- Zadajte formátovanie (na zvýraznenie buniek, ktoré sa zhodujú s hľadaným kľúčovým slovom).
- Kliknite na tlačidlo OK.
Teraz zadajte čokoľvek do bunky B1 a stlačte kláves Enter. Zvýrazní zodpovedajúce bunky v množine údajov, ktoré obsahujú kľúčové slovo v jazyku B1.
Ako to funguje?
Podmienené formátovanie sa použije vždy, keď vzorec v ňom uvedený vráti hodnotu TRUE.
Vo vyššie uvedenom príklade skontrolujeme každú bunku pomocou vzorca = A4 = $ B $ 1
Podmienené formátovanie kontroluje každú bunku a overuje, či je obsah bunky rovnaký ako v bunke B1. Ak je to rovnaké, vzorec vráti hodnotu TRUE a bunka sa zvýrazní. Ak to nie je rovnaké, vzorec vráti NEPRAVDU a nič sa nestane.
Kliknutím sem stiahnete ukážkový súbor a choďte ďalej.
Vyhľadajte a zvýraznite riadky pomocou zodpovedajúcich údajov
Ak chcete zvýrazniť celý riadok namiesto iba zodpovedajúcich buniek, môžete to urobiť tak, že trochu upravíte vzorec.
Nasleduje príklad, kde sa zvýrazní celý riadok, ak sa typ produktu zhoduje s typom v bunke B1.
Nasledujú kroky na vyhľadanie a zvýraznenie celého riadka:
- Vyberte množinu údajov, na ktorú chcete použiť podmienené formátovanie (v tomto prípade A4: F19).
- Kliknite na kartu Domov.
- V skupine Štýly kliknite na Podmienené formátovanie.
- V rozbaľovacích možnostiach kliknite na položku Nové pravidlo.
- V dialógovom okne „Nové pravidlo formátovania“ kliknite na možnosť „Pomocou vzorca určiť, ktoré bunky sa majú formátovať“.
- Zadajte nasledujúci vzorec: = $ B4 = $ B $ 1
- Kliknite na tlačidlo „Formátovať …“.
- Zadajte formátovanie (na zvýraznenie buniek, ktoré sa zhodujú s hľadaným kľúčovým slovom).
- Kliknite na tlačidlo OK.
Vyššie uvedené kroky by vyhľadali zadanú položku v množine údajov a ak nájde zodpovedajúcu položku, zvýrazní celý riadok.
Upozorňujeme, že sa tým skontroluje iba stĺpec položky. Ak sem zadáte názov obchodného zástupcu, nebude to fungovať. Ak chcete, aby to fungovalo pre názov obchodného zástupcu, musíte zmeniť vzorec na = $ C4 = $ B $ 1
Poznámka: Dôvod, prečo sa zvýrazňuje celý riadok a nielen zodpovedajúca bunka, je ten, že sme pred odkazom na stĺpec použili znak $ ($ B4). Keď teraz podmienené formátovanie analyzuje bunky v riadku, skontroluje, či sa hodnota v stĺpci B tohto riadka rovná hodnote v bunke B1. Takže aj keď analyzuje A4 alebo B4 alebo C4 a tak ďalej, kontroluje iba hodnotu B4 (pretože stĺpček B sme uzamkli pomocou znaku dolára).
Tu si môžete prečítať viac o absolútnych, relatívnych a zmiešaných odkazoch.
Vyhľadajte a zvýraznite riadky (na základe čiastočnej zhody)
V niektorých prípadoch možno budete chcieť zvýrazniť riadky na základe čiastočnej zhody.
Ak máte napríklad položky ako White Board, Green Board a Grey Board a chcete ich všetky zvýrazniť na základe slova Board, môžete to urobiť pomocou funkcie VYHĽADÁVAŤ.
Niečo, ako je uvedené nižšie:
Tu sú kroky, ako to urobiť:
- Vyberte množinu údajov, na ktorú chcete použiť podmienené formátovanie (v tomto prípade A4: F19).
- Kliknite na kartu Domov.
- V skupine Štýly kliknite na Podmienené formátovanie.
- V rozbaľovacích možnostiach kliknite na položku Nové pravidlo.
- V dialógovom okne „Nové pravidlo formátovania“ kliknite na možnosť „Pomocou vzorca určiť, ktoré bunky sa majú formátovať“.
- Zadajte nasledujúci vzorec: = AND ($ B $ 1 ””, ISNUMBER (VYHĽADÁVANIE ($ B $ 1, $ B4)))
- Kliknite na tlačidlo „Formátovať …“.
- Zadajte formátovanie (na zvýraznenie buniek, ktoré sa zhodujú s hľadaným kľúčovým slovom).
- Kliknite na tlačidlo OK.
Ako to funguje?
- Funkcia SEARCH hľadá hľadaný reťazec/kľúčové slovo vo všetkých bunkách v rade. Ak sa kľúčové slovo vyhľadávania nenájde, vráti chybu. Ak nájde zhodu, vráti číslo.
- Funkcia ISNUMBER prevádza chybu na FALSE a číselné hodnoty na TRUE.
- Funkcia AND kontroluje ďalšiu podmienku - bunka C2 by nemala byť prázdna.
Takže teraz, keď zadáte kľúčové slovo do bunky B1 a stlačíte Enter, zvýrazní všetky riadky, ktoré majú bunky, ktoré dané kľúčové slovo obsahujú.
Bonusový tip: Ak chcete, aby hľadanie rozlišovalo veľké a malé písmená, použite funkciu HLEDAŤ namiesto VYHĽADÁVAŤ.
Kliknutím sem stiahnete ukážkový súbor a choďte ďalej.
Dynamické vyhľadávanie a funkcia zvýraznenia (zvýrazňuje sa počas písania)
Použitím rovnakých trikov podmieneného formátovania, ktoré sú popísané vyššie, to môžete urobiť ešte o krok ďalej a urobiť to dynamickým.
Môžete napríklad vytvoriť vyhľadávací panel, na ktorom sa pri písaní do vyhľadávacieho poľa zvýraznia zodpovedajúce údaje.
Niečo, ako je uvedené nižšie:
To je možné vykonať pomocou ovládacích prvkov ActiveX a môže to byť dobrá funkcia, ktorú je možné použiť pri vytváraní zostáv alebo dashboardov.
Nasleduje video, v ktorom ukážem, ako vytvoriť toto:
Považovali ste tento návod za užitočný? Dajte mi vedieť svoje nápady v sekcii komentárov.
Tiež by sa vám mohli páčiť nasledujúce návody na Excel:
- Dynamický filter programu Excel - extrahuje údaje počas písania.
- Vytvorte rozbaľovací zoznam s návrhom vyhľadávania.
- Vytvorenie tepelnej mapy v programe Excel.
- Zvýraznite riadky na základe hodnoty bunky v programe Excel.
- Zvýraznite aktívny riadok a stĺpec v rozsahu údajov v programe Excel.
- Ako zvýrazniť prázdne bunky v programe Excel.
