Podmíněné formátování při porovnávání seznamů v Excelu

Můžete být požádáni, abyste porovnali dva seznamy a vybrali hodnoty, které jsou v jednom seznamu, ale ne ve druhém, nebo možná budete muset porovnat dva seznamy a vybrat pouze hodnoty, které existují v obou seznamech. Podmíněné formátování je ideální způsob, jak splnit oba úkoly.

Zvýrazněte hodnoty, které existují v List1, ale ne v List2

Obrázek ilustruje cvičení podmíněného formátování, které porovnává zákazníky z roku 2013 a 2014 a zvýrazní ty zákazníky v roce 2014, kteří jsou novými zákazníky (v roce 2013 nebyli zákazníky).

Chcete-li vytvořit toto základní pravidlo formátování, postupujte takto:

Vyberte datové buňky ve vašem cílovém rozsahu (v tomto příkladu buňky E4:E28), klikněte na kartu Domů na pásu karet aplikace Excel a poté vyberte Podmíněné formátování → Nové pravidlo.

Otevře se dialogové okno Nové pravidlo formátování.

V seznamu v horní části dialogového okna klikněte na možnost Použít vzorec k určení buněk k formátování.

Tento výběr vyhodnotí hodnoty na základě vámi zadaného vzorce. Pokud se určitá hodnota vyhodnotí jako TRUE, podmíněné formátování se použije na tuto buňku.

Do pole pro zadání vzorce zadejte vzorec zobrazený v tomto kroku.

Všimněte si, že pomocí funkce COUNTIF vyhodnotíte, zda se hodnota v cílové buňce (E4) nachází ve vašem porovnávacím rozsahu ($B$4:$B$21). Pokud hodnota není nalezena, funkce COUNTIF vrátí 0, čímž se spustí podmíněné formátování. Stejně jako u standardních vzorců musíte zajistit, že používáte absolutní odkazy, aby byla každá hodnota ve vašem rozsahu porovnána s příslušnou porovnávací buňkou.

=COUNTIF($B$4:$B$21,E4)=0

Všimněte si, že ve vzorci vyloučíte absolutní referenční symboly dolaru ($) pro cílovou buňku (E4). Pokud místo psaní odkazu na buňku klepnete na buňku E4, Excel automaticky nastaví odkaz na buňku jako absolutní. Je důležité, abyste do cílové buňky nezahrnuli absolutní referenční symboly dolaru, protože k použití tohoto pravidla formátování na základě vlastní hodnoty každé buňky potřebujete Excel.

Klepněte na tlačítko Formát.

Otevře se dialogové okno Formát buněk, kde máte úplnou sadu možností pro formátování písma, ohraničení a výplně pro cílovou buňku. Po dokončení výběru možností formátování potvrďte změny kliknutím na tlačítko OK a vraťte se do dialogového okna Nové pravidlo formátování.

V dialogovém okně Nové pravidlo formátování potvrďte pravidlo formátování kliknutím na tlačítko OK.

Pokud potřebujete upravit pravidlo podmíněného formátování, jednoduše umístěte kurzor do libovolné datové buňky ve formátovaném rozsahu a poté přejděte na kartu Domů a vyberte Podmíněné formátování → Spravovat pravidla. Otevře se dialogové okno Správce pravidel podmíněného formátování. Klikněte na pravidlo, které chcete upravit, a poté klikněte na tlačítko Upravit pravidlo.

Zvýrazněte hodnoty, které existují v List1 a List2

Obrázek ilustruje cvičení podmíněného formátování, které porovnává zákazníky z roku 2013 a 2014 a zvýrazní zákazníky v roce 2014, kteří jsou v obou seznamech.

Chcete-li vytvořit toto základní pravidlo formátování, postupujte takto:

Vyberte datové buňky ve vašem cílovém rozsahu (v tomto příkladu buňky E4:E28), klepněte na kartu Domů na pásu karet aplikace Excel a vyberte Podmíněné formátování → Nové pravidlo.

Otevře se dialogové okno Nové pravidlo formátování.

V seznamu v horní části dialogového okna klikněte na možnost Použít vzorec k určení buněk k formátování.

Tento výběr vyhodnotí hodnoty na základě vámi zadaného vzorce. Pokud se určitá hodnota vyhodnotí jako TRUE, podmíněné formátování se použije na tuto buňku.

Do pole pro zadání vzorce zadejte vzorec zobrazený v tomto kroku.

Všimněte si, že pomocí funkce COUNTIF vyhodnotíte, zda se hodnota v cílové buňce (E4) nachází ve vašem porovnávacím rozsahu ($B$4:$B$21). Pokud je hodnota nalezena, funkce COUNTIF vrátí číslo větší než 0, čímž spustí podmíněné formátování. Stejně jako u standardních vzorců musíte zajistit, že používáte absolutní odkazy, aby byla každá hodnota ve vašem rozsahu porovnána s příslušnou porovnávací buňkou.

=COUNTIF($B$4:$B$21,E4)>0

Všimněte si, že ve vzorci vyloučíte absolutní referenční symboly dolaru ($) pro cílovou buňku (E4). Pokud místo psaní odkazu na buňku klepnete na buňku E4, Excel automaticky nastaví odkaz na buňku jako absolutní. Je důležité, abyste do cílové buňky nezahrnuli absolutní referenční symboly dolaru, protože k použití tohoto pravidla formátování na základě vlastní hodnoty každé buňky potřebujete Excel.

Klepněte na tlačítko Formát.

Otevře se dialogové okno Formát buněk, kde máte úplnou sadu možností pro formátování písma, ohraničení a výplně pro cílovou buňku. Po dokončení výběru možností formátování potvrďte změny kliknutím na tlačítko OK a vraťte se do dialogového okna Nové pravidlo formátování.

Zpět v dialogovém okně Nové pravidlo formátování potvrďte pravidlo formátování kliknutím na tlačítko OK.

Pokud potřebujete upravit pravidlo podmíněného formátování, jednoduše umístěte kurzor do libovolné datové buňky ve formátovaném rozsahu a poté přejděte na kartu Domů a vyberte Podmíněné formátování → Spravovat pravidla. Otevře se dialogové okno Správce pravidel podmíněného formátování. Klikněte na pravidlo, které chcete upravit, a poté klikněte na tlačítko Upravit pravidlo.