Sledujte video - vyhledávejte a zvýrazňujte data pomocí podmíněného formátování
Pokud pracujete s velkými datovými sadami, může být potřeba vytvořit vyhledávací funkci, která vám umožní rychle zvýraznit buňky/řádky pro hledaný výraz.
I když v aplikaci Excel neexistuje žádný přímý způsob, jak to provést, můžete vytvořit funkci vyhledávání pomocí podmíněného formátování.
Předpokládejme například, že máte datovou sadu, jak je uvedeno níže (na obrázku). Obsahuje sloupce pro název produktu, obchodního zástupce a zemi.
Nyní můžete použít podmíněné formátování k vyhledání klíčového slova (zadáním do buňky C2) a zvýraznění všech buněk, které dané klíčové slovo mají.
Něco, jak je uvedeno níže (kde do buňky B2 zadám název položky a stisknu Enter, se zvýrazní celý řádek):
V tomto tutoriálu vám ukážu, jak vytvořit toto vyhledávání a zvýraznit funkce v Excelu.
Později v tutoriálu půjdeme trochu pokročileji a uvidíme, jak to udělat dynamickým (aby se zvýraznilo při psaní do vyhledávacího pole).
Kliknutím sem stáhnete ukázkový soubor a následovat.
Vyhledejte a zvýrazněte odpovídající buňky
V této části. Ukážu vám, jak v datové sadě vyhledávat a zvýrazňovat pouze odpovídající buňky.
Něco, jak je uvedeno níže:
Zde jsou kroky k vyhledání a zvýraznění všech buněk, které mají odpovídající text:
- Vyberte datovou sadu, na kterou chcete použít podmíněné formátování (A4: F19 v tomto příkladu).
- Klikněte na kartu Domů.
- Ve skupině Styly klikněte na Podmíněné formátování.
- V rozevíracích možnostech klikněte na Nové pravidlo.
- V dialogovém okně „Nové pravidlo formátování“ klikněte na možnost „Pomocí vzorce určit, které buňky chcete formátovat“.
- Zadejte následující vzorec: = A4 = $ B $ 1
- Klikněte na tlačítko „Formátovat…“.
- Zadejte formátování (zvýrazněte buňky, které odpovídají hledanému klíčovému slovu).
- Klikněte na OK.
Nyní zadejte cokoli do buňky B1 a stiskněte Enter. Zvýrazní odpovídající buňky v datové sadě, které obsahují klíčové slovo v B1.
Jak to funguje?
Podmíněné formátování se použije vždy, když vzorec v něm uvedený vrátí hodnotu TRUE.
Ve výše uvedeném příkladu zkontrolujeme každou buňku pomocí vzorce = A4 = $ B $ 1
Podmíněné formátování kontroluje každou buňku a ověřuje, že obsah v buňce je stejný jako v buňce B1. Pokud je to stejné, vzorec vrátí PRAVDA a buňka se zvýrazní. Pokud to není stejné, vzorec vrátí FALSE a nic se nestane.
Kliknutím sem stáhnete ukázkový soubor a následovat.
Hledejte a zvýrazněte řádky pomocí odpovídajících dat
Pokud chcete zvýraznit celý řádek namísto pouze odpovídajících buněk, můžete to udělat tak, že trochu upravíte vzorec.
Níže je uveden příklad, kde se zvýrazní celý řádek, pokud se typ produktu shoduje s typem v buňce B1.
Zde jsou kroky k vyhledání a zvýraznění celého řádku:
- Vyberte datovou sadu, na kterou chcete použít podmíněné formátování (A4: F19 v tomto příkladu).
- Klikněte na kartu Domů.
- Ve skupině Styly klikněte na Podmíněné formátování.
- V rozevíracích možnostech klikněte na Nové pravidlo.
- V dialogovém okně „Nové pravidlo formátování“ klikněte na možnost „Pomocí vzorce určit, které buňky chcete formátovat“.
- Zadejte následující vzorec: = $ B4 = $ B $ 1
- Klikněte na tlačítko „Formátovat…“.
- Zadejte formátování (zvýrazněte buňky, které odpovídají hledanému klíčovému slovu).
- Klikněte na OK.
Výše uvedené kroky by vyhledaly zadanou položku v datové sadě a pokud najde odpovídající položku, zvýrazní celý řádek.
Všimněte si, že se tím zkontroluje pouze sloupec položky. Pokud zde zadáte název obchodního zástupce, nebude to fungovat. Pokud chcete, aby fungoval pro název obchodního zástupce, musíte změnit vzorec na = $ C4 = $ B $ 1
Poznámka: Důvodem, proč je zvýrazněn celý řádek a ne pouze odpovídající buňka, je to, že jsme před odkaz na sloupec použili znak $ ($ B4). Když nyní podmíněné formátování analyzuje buňky v řádku, zkontroluje, zda se hodnota ve sloupci B tohoto řádku rovná hodnotě v buňce B1. Takže i když analyzuje A4 nebo B4 nebo C4 atd., Kontroluje pouze hodnotu B4 (protože jsme uzamkli sloupec B pomocí znaku dolaru).
Více o absolutních, relativních a smíšených referencích si můžete přečíst zde.
Vyhledávání a zvýraznění řádků (na základě částečné shody)
V některých případech můžete chtít zvýraznit řádky na základě částečné shody.
Pokud například máte položky jako White Board, Green Board a Grey Board a chcete je všechny zvýraznit na základě slova Board, můžete to provést pomocí funkce HLEDAT.
Něco, jak je uvedeno níže:
Zde jsou kroky, jak toho dosáhnout:
- Vyberte datovou sadu, na kterou chcete použít podmíněné formátování (A4: F19 v tomto příkladu).
- Klikněte na kartu Domů.
- Ve skupině Styly klikněte na Podmíněné formátování.
- V rozevíracích možnostech klikněte na Nové pravidlo.
- V dialogovém okně „Nové pravidlo formátování“ klikněte na možnost „Pomocí vzorce určit, které buňky chcete formátovat“.
- Zadejte následující vzorec: = AND ($ B $ 1 ””, ISNUMBER (HLEDAT ($ B $ 1, $ B4)))
- Klikněte na tlačítko „Formátovat…“.
- Zadejte formátování (zvýrazněte buňky, které odpovídají hledanému klíčovému slovu).
- Klikněte na OK.
Jak to funguje?
- Funkce SEARCH hledá hledaný řetězec/klíčové slovo ve všech buňkách v řadě. Pokud klíčové slovo pro hledání není nalezeno, vrátí chybu, a pokud nalezne shodu, vrátí číslo.
- Funkce ISNUMBER převádí chybu na FALSE a číselné hodnoty na TRUE.
- Funkce AND kontroluje další podmínku - buňka C2 by neměla být prázdná.
Takže nyní, kdykoli zadáte klíčové slovo do buňky B1 a stisknete Enter, zvýrazní všechny řádky, které mají buňky obsahující toto klíčové slovo.
Bonusový tip: Pokud chcete, aby byla velká a malá písmena ve vyhledávání, použijte funkci HLEDAT místo HLEDAT.
Kliknutím sem stáhnete ukázkový soubor a následovat.
Dynamické vyhledávání a funkce zvýraznění (zvýraznění při psaní)
Pomocí stejných triků podmíněného formátování, které jsou popsány výše, můžete také posunout o krok dále a učinit ho dynamickým.
Můžete například vytvořit vyhledávací panel, kde se při psaní do vyhledávacího pole zvýrazní odpovídající data.
Něco, jak je uvedeno níže:
To lze provést pomocí ovládacích prvků ActiveX a může to být dobrá funkce při vytváření sestav nebo řídicích panelů.
Níže je video, kde ukazuji, jak vytvořit toto:
Považovali jste tento návod za užitečný? Dejte mi vědět své myšlenky v sekci komentáře.
Také by se vám mohly líbit následující výukové programy pro Excel:
- Dynamický filtr aplikace Excel - extrahuje data při psaní.
- Vytvořte rozevírací seznam s návrhem vyhledávání.
- Vytvoření tepelné mapy v aplikaci Excel.
- Zvýrazněte řádky na základě hodnoty buňky v aplikaci Excel.
- Zvýrazněte aktivní řádek a sloupec v rozsahu dat v aplikaci Excel.
- Jak zvýraznit prázdné buňky v Excelu.
