Zvýrazněte řádky na základě hodnoty buňky v aplikaci Excel (podmíněné formátování)

Sledujte video - zvýrazněte řádky na základě hodnot buněk v aplikaci Excel

Pokud dáváte přednost čtení písemných pokynů, níže je návod.

Podmíněné formátování vám umožňuje formátovat buňku (nebo rozsah buněk) na základě hodnoty v ní.

Někdy však místo zvýraznění buňky můžete chtít zvýraznit celý řádek (nebo sloupec) na základě hodnoty v jedné buňce.

Abych vám dal příklad, níže mám datovou sadu, kde jsem zvýraznil všechny řádky, kde je jméno obchodního zástupce Bob.

V tomto tutoriálu vám ukážu, jak zvýraznit řádky na základě hodnoty buňky pomocí podmíněného formátování pomocí různých kritérií.

Klikněte zde stáhněte si ukázkový soubor a postupujte podle něj.

Zvýrazněte řádky na základě textových kritérií

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete zvýraznit všechny záznamy, kde je název obchodního zástupce Bob.

Zde jsou kroky, jak toho dosáhnout:

1. Vyberte celou datovou sadu (v tomto příkladu A2: F17).
2. Klikněte na kartu Domů.
3. Ve skupině Styly klikněte na Podmíněné formátování.
4. Klikněte na „Nová pravidla“.
5. V dialogovém okně „Nové pravidlo formátování“ klikněte na „Pomocí vzorce určit, které buňky chcete formátovat“.
6. Do pole vzorce zadejte následující vzorec: = $ C2 = ”Bob”
7. Klikněte na tlačítko „Formátovat“.
8. V dialogovém okně, které se otevře, nastavte barvu, ve které chcete zvýraznit řádek.
9. Klikněte na OK.

Tím se zvýrazní všechny řádky, kde je název obchodního zástupce „Bob“.

Klikněte zde stáhněte si ukázkový soubor a postupujte podle něj.

Jak to funguje?

Podmíněné formátování kontroluje v každé buňce podmínku, kterou jsme zadali, což je = $ C2 = ”Bob”

Když tedy analyzuje každou buňku v řádku A2, zkontroluje, zda buňka C2 má jméno Bob nebo ne. Pokud ano, tato buňka se zvýrazní, jinak ne.

Všimněte si, že zde jde o to použít znak dolaru ($) před abecedu sloupců ($ C1). Tímto způsobem jsme uzamkli sloupec tak, aby byl vždy C. Takže i když je buňka A2 kontrolována pro vzorec, bude kontrolovat C2 a když je A3 kontrolována pro podmínku, bude kontrolovat C3.

To nám umožňuje zvýraznit celý řádek podmíněným formátováním.

Zvýrazněte řádky na základě číselných kritérií

Ve výše uvedeném příkladu jsme viděli, jak zkontrolovat název a zvýraznit celý řádek.

Stejnou metodou můžeme také vyhledat číselné hodnoty a zvýraznit řádky na základě podmínky.

Předpokládejme, že mám stejná data (jak je uvedeno níže) a chci zvýraznit všechny řádky, kde je množství větší než 15.

Zde jsou kroky, jak toho dosáhnout:

1. Vyberte celou datovou sadu (v tomto příkladu A2: F17).
2. Klikněte na kartu Domů.
3. Ve skupině Styly klikněte na Podmíněné formátování.
4. Klikněte na „Nová pravidla“.
5. V dialogovém okně „Nové pravidlo formátování“ klikněte na „Pomocí vzorce určit, které buňky chcete formátovat“.
6. Do pole vzorce zadejte následující vzorec: = $ D2> = 15
7. Klikněte na tlačítko „Formátovat“. V dialogovém okně, které se otevře, nastavte barvu, ve které chcete zvýraznit řádek.
8. Klikněte na OK.

Tím se zvýrazní všechny řádky, kde je množství větší nebo rovné 15.

Podobně to můžeme také použít k získání kritérií pro datum.

Pokud například chcete zvýraznit všechny řádky, kde je datum po 10. červenci 2021-2022, můžete použít níže uvedený vzorec data:

= $ A2> DATUM (2018,7,10)

Zvýrazněte řádky na základě více kritérií (A/NEBO)

Můžete také použít více kritérií pro zvýraznění řádků pomocí podmíněného formátování.

Pokud například chcete zvýraznit všechny řádky, kde je název obchodního zástupce „Bob“ a množství je větší než 10, můžete to provést pomocí následujících kroků:

1. Vyberte celou datovou sadu (v tomto příkladu A2: F17).
2. Klikněte na kartu Domů.
3. Ve skupině Styly klikněte na Podmíněné formátování.
4. Klikněte na „Nová pravidla“.
5. V dialogovém okně „Nové pravidlo formátování“ klikněte na „Pomocí vzorce určit, které buňky chcete formátovat“.
6. Do pole vzorce zadejte následující vzorec: = AND ($ C2 = ”Bob”, $ D2> 10)
7. Klikněte na tlačítko „Formátovat“. V dialogovém okně, které se otevře, nastavte barvu, ve které chcete zvýraznit řádek.
8. Klikněte na OK.

V tomto případě se zvýrazní pouze ty řádky, kde jsou splněny obě podmínky (to se provádí pomocí vzorce AND).

Podobně můžete také použít podmínku NEBO. Pokud například chcete zvýraznit řádky, kde buď obchodní zástupce je Bob, nebo je množství větší než 15, můžete použít níže uvedený vzorec:

= NEBO ($ C2 = "Bob", $ D2> 15)

Klikněte zde stáhněte si ukázkový soubor a postupujte podle něj.

Zvýrazněte řádky v různých barvách na základě více podmínek

Někdy můžete chtít zvýraznit řádky barvou na základě podmínky.

Můžete například chtít zvýraznit všechny řádky, kde je množství více než 20 zeleně a kde je množství více než 15 (ale méně než 20) oranžově.

Chcete -li to provést, musíte vytvořit dvě pravidla podmíněného formátování a nastavit prioritu.

Zde jsou kroky, jak toho dosáhnout:

1. Vyberte celou datovou sadu (v tomto příkladu A2: F17).
2. Klikněte na kartu Domů.
3. Ve skupině Styly klikněte na Podmíněné formátování.
4. Klikněte na „Nová pravidla“.
5. V dialogovém okně „Nové pravidlo formátování“ klikněte na „Pomocí vzorce určit, které buňky chcete formátovat“.
6. Do pole vzorce zadejte následující vzorec: = $ D2> 15
7. Klikněte na tlačítko „Formátovat“. V dialogovém okně, které se otevře, nastavte barvu na oranžovou.
8. Klikněte na OK.
9. V dialogovém okně „Správce pravidel podmíněného formátování“ klikněte na „Nové pravidlo“.
10. V dialogovém okně „Nové pravidlo formátování“ klikněte na „Pomocí vzorce určit, které buňky chcete formátovat“.
11. Do pole vzorce zadejte následující vzorec: = $ D2> 20
12. Klikněte na tlačítko „Formátovat“. V dialogovém okně, které se otevře, nastavte barvu na zelenou.
13. Klikněte na OK.
14. Klikněte na Použít (nebo OK).

Výše uvedené kroky by udělaly všechny řádky s množstvím více než 20 zeleně a řádky s více než 15 (ale méně než rovnými 20 v oranžové barvě).

Pochopení pořadí pravidel:

Při použití více podmínek je důležité se ujistit, že je pořadí podmínek správné.

Ve výše uvedeném příkladu je podmínka zelené barvy nad podmínkou oranžové barvy.

Pokud je to naopak, všechny řádky budou zbarveny pouze oranžově.

Proč?

Protože řádek, kde je množství větší než 20 (řekněme 23), splňuje obě naše podmínky (= $ D2> 15 a = $ D2> 20). A protože stav Orange je na vrcholu, dostává přednost.

Pořadí podmínek můžete změnit pomocí tlačítek Move Up/Down.

Klikněte zde stáhněte si ukázkový soubor a postupujte podle něj.

Zvýrazněte řádky, kde je libovolná buňka prázdná

Chcete -li zvýraznit všechny řádky, kde jsou všechny buňky v něm prázdné, musíte u každé buňky zkontrolovat podmíněné formátování.

Zde jsou kroky, jak toho dosáhnout:

1. Vyberte celou datovou sadu (v tomto příkladu A2: F17).
2. Klikněte na kartu Domů.
3. Ve skupině Styly klikněte na Podmíněné formátování.
4. Klikněte na „Nová pravidla“.
5. V dialogovém okně „Nové pravidlo formátování“ klikněte na „Pomocí vzorce určit, které buňky chcete formátovat“.
6. Do pole vzorce zadejte následující vzorec: = COUNTIF ($ A2: $ F2, ””)> 0
7. Klikněte na tlačítko „Formátovat“. V dialogovém okně, které se otevře, nastavte barvu na oranžovou.
8. Klikněte na OK.

Výše uvedený vzorec počítá počet prázdných buněk. Pokud je výsledek větší než 0, znamená to, že v tomto řádku jsou prázdné buňky.

Pokud jsou některé buňky prázdné, zvýrazní se celý řádek.

Zvýrazněte řádky na základě rozevíracího výběru

V dosud zahrnutých příkladech byly všechny podmínky zadány pomocí dialogového okna podmíněné formátování.

V této části tutoriálu vám ukážu, jak to udělat dynamickým (abyste mohli podmínku zadat do buňky v Excelu a ta na základě ní automaticky zvýrazní řádky).

Níže je uveden příklad, kde z rozevíracího seznamu vyberu název a zvýrazní se všechny řádky s tímto názvem:

Zde jsou kroky k vytvoření:

1. V buňce A2 vytvořte rozevírací seznam. Zde jsem použil názvy prodejních zástupců k vytvoření rozevíracího seznamu. Zde je podrobný průvodce vytvořením rozevíracího seznamu v aplikaci Excel.
2. Vyberte celou datovou sadu (v tomto příkladu C2: H17).
3. Klikněte na kartu Domů.
4. Ve skupině Styly klikněte na Podmíněné formátování.
5. Klikněte na „Nová pravidla“.
6. V dialogovém okně „Nové pravidlo formátování“ klikněte na „Pomocí vzorce určit, které buňky chcete formátovat“.
7. Do pole vzorce zadejte následující vzorec: = $ E2 = $ A $ 2
8. Klikněte na tlačítko „Formátovat“. V dialogovém okně, které se otevře, nastavte barvu na oranžovou.
9. Klikněte na OK.

Když nyní vyberete jakýkoli název z rozevíracího seznamu, automaticky zvýrazní řádky, kde je název stejný, jaký jste vybrali z rozevíracího seznamu.

Máte zájem dozvědět se více o tom, jak vyhledávat a zvýrazňovat v Excelu? Podívejte se na níže uvedená videa.