Zvýraznite riadky na základe hodnoty bunky v programe Excel (podmienené formátovanie)

Pozrite si video - zvýraznite riadky na základe hodnôt buniek v programe Excel

Ak radšej čítate písomné pokyny, nižšie je návod.

Podmienené formátovanie vám umožňuje formátovať bunku (alebo rozsah buniek) na základe hodnoty v nej.

Niekedy však namiesto zvýraznenia bunky môžete chcieť zvýrazniť celý riadok (alebo stĺpec) na základe hodnoty v jednej bunke.

Aby som vám dal príklad, nižšie mám množinu údajov, kde som zvýraznil všetky riadky, kde je názov obchodného zástupcu Bob.

V tomto tutoriále vám ukážem, ako zvýrazniť riadky na základe hodnoty bunky pomocou podmieneného formátovania pomocou rôznych kritérií.

Kliknite tu stiahnite si vzorový súbor a postupujte podľa neho.

Zvýraznite riadky na základe textových kritérií

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete zvýrazniť všetky záznamy, kde je názov obchodného zástupcu Bob.

Tu sú kroky, ako to urobiť:

1. Vyberte celú množinu údajov (v tomto prípade A2: F17).
2. Kliknite na kartu Domov.
3. V skupine Štýly kliknite na Podmienené formátovanie.
4. Kliknite na položku „Nové pravidlá“.
5. V dialógovom okne „Nové pravidlo formátovania“ kliknite na „Pomocou vzorca určiť, ktoré bunky sa majú formátovať“.
6. Do poľa vzorca zadajte nasledujúci vzorec: = $ C2 = ”Bob”
7. Kliknite na tlačidlo „Formátovať“.
8. V dialógovom okne, ktoré sa otvorí, nastavte farbu, v ktorej chcete zvýrazniť riadok.
9. Kliknite na tlačidlo OK.

Tým sa zvýraznia všetky riadky, v ktorých je názov obchodného zástupcu „Bob“.

Kliknite tu stiahnite si vzorový súbor a postupujte podľa neho.

Ako to funguje?

Podmienené formátovanie kontroluje v každej bunke podmienku, ktorú sme uviedli, čo je = $ C2 = ”Bob”

Pri analýze každej bunky v riadku A2 teda skontroluje, či má bunka C2 meno Bob alebo nie. Ak áno, táto bunka sa zvýrazní, inak nie.

Všimnite si, že tu ide o to, použiť pred dolárovou abecedou ($ C1) znak dolára ($). Tým sme uzamkli stĺpec, aby bol vždy C. Takže aj keď sa v bunke A2 kontroluje vzorec, kontroluje sa C2 a keď sa kontroluje podmienka A3, kontroluje sa C3.

To nám umožňuje zvýrazniť celý riadok podmieneným formátovaním.

Zvýraznite riadky na základe číselných kritérií

Vo vyššie uvedenom príklade sme videli, ako vyhľadať meno a zvýrazniť celý riadok.

Rovnakú metódu môžeme použiť aj na kontrolu číselných hodnôt a zvýraznenie riadkov na základe podmienky.

Predpokladajme, že mám rovnaké údaje (ako je uvedené nižšie) a chcem zvýrazniť všetky riadky, v ktorých je množstvo viac ako 15.

Tu sú kroky, ako to urobiť:

1. Vyberte celú množinu údajov (v tomto prípade A2: F17).
2. Kliknite na kartu Domov.
3. V skupine Štýly kliknite na Podmienené formátovanie.
4. Kliknite na položku „Nové pravidlá“.
5. V dialógovom okne „Nové pravidlo formátovania“ kliknite na „Pomocou vzorca určiť, ktoré bunky sa majú formátovať“.
6. Do poľa vzorca zadajte nasledujúci vzorec: = $ D2> = 15
7. Kliknite na tlačidlo „Formátovať“. V dialógovom okne, ktoré sa otvorí, nastavte farbu, v ktorej má byť riadok zvýraznený.
8. Kliknite na tlačidlo OK.

Tým sa zvýraznia všetky riadky, v ktorých je množstvo väčšie alebo rovné 15.

Podobne to môžeme použiť aj na zadanie kritérií pre dátum.

Ak napríklad chcete zvýrazniť všetky riadky, kde je dátum po 10. júli 2021-2022, môžete použiť nasledujúci vzorec dátumu:

= $ A2> DÁTUM (2018,7,10)

Zvýraznite riadky na základe viacerých kritérií (A/ALEBO)

Na zvýraznenie riadkov pomocou podmieneného formátovania môžete použiť aj viacero kritérií.

Ak napríklad chcete zvýrazniť všetky riadky, kde je názov obchodného zástupcu „Bob“ a množstvo je viac ako 10, môžete to urobiť pomocou nasledujúcich krokov:

1. Vyberte celú množinu údajov (v tomto prípade A2: F17).
2. Kliknite na kartu Domov.
3. V skupine Štýly kliknite na Podmienené formátovanie.
4. Kliknite na položku „Nové pravidlá“.
5. V dialógovom okne „Nové pravidlo formátovania“ kliknite na „Pomocou vzorca určiť, ktoré bunky sa majú formátovať“.
6. Do poľa vzorca zadajte nasledujúci vzorec: = AND ($ C2 = ”Bob”, $ D2> 10)
7. Kliknite na tlačidlo „Formátovať“. V dialógovom okne, ktoré sa otvorí, nastavte farbu, v ktorej má byť riadok zvýraznený.
8. Kliknite na tlačidlo OK.

V tomto prípade sa zvýraznia iba tie riadky, kde sú splnené obe podmienky (to sa robí pomocou vzorca AND).

Podobne môžete použiť aj podmienku ALEBO. Ak napríklad chcete zvýrazniť riadky, v ktorých je obchodný zástupca Bob alebo je množstvo vyššie ako 15, môžete použiť nasledujúci vzorec:

= ALEBO ($ C2 = „Bob“, $ D2> 15)

Kliknite tu stiahnite si vzorový súbor a postupujte podľa neho.

Zvýraznite riadky v rôznych farbách na základe viacerých podmienok

Niekedy možno budete chcieť zvýrazniť riadky farbou podľa stavu.

Môžete napríklad zvýrazniť všetky riadky, kde je množstvo viac ako 20 zelenou farbou a kde je množstvo viac ako 15 (ale menej ako 20) oranžovou farbou.

Na to musíte vytvoriť dve pravidlá podmieneného formátovania a nastaviť prioritu.

Tu sú kroky, ako to urobiť:

1. Vyberte celú množinu údajov (v tomto prípade A2: F17).
2. Kliknite na kartu Domov.
3. V skupine Štýly kliknite na Podmienené formátovanie.
4. Kliknite na položku „Nové pravidlá“.
5. V dialógovom okne „Nové pravidlo formátovania“ kliknite na „Pomocou vzorca určiť, ktoré bunky sa majú formátovať“.
6. Do poľa vzorca zadajte nasledujúci vzorec: = $ D2> 15
7. Kliknite na tlačidlo „Formátovať“. V dialógovom okne, ktoré sa otvorí, nastavte farbu na oranžovú.
8. Kliknite na tlačidlo OK.
9. V dialógovom okne „Správca pravidiel podmieneného formátovania“ kliknite na „Nové pravidlo“.
10. V dialógovom okne „Nové pravidlo formátovania“ kliknite na „Pomocou vzorca určiť, ktoré bunky sa majú formátovať“.
11. Do poľa vzorca zadajte nasledujúci vzorec: = $ D2> 20
12. Kliknite na tlačidlo „Formátovať“. V dialógovom okne, ktoré sa otvorí, nastavte farbu na zelenú.
13. Kliknite na tlačidlo OK.
14. Kliknite na Použiť (alebo OK).

Vyššie uvedené kroky by spôsobili, že všetky riadky s počtom viac ako 20 zelene a riadky s viac ako 15 (ale menej ako 20 ako oranžovo).

Pochopenie poradia pravidiel:

Pri použití viacerých podmienok je dôležité zaistiť správne poradie podmienok.

Vo vyššie uvedenom príklade je podmienka zelenej farby nad podmienkou oranžovej farby.

Ak je to naopak, všetky riadky budú zafarbené iba na oranžovo.

Prečo?

Pretože riadok, kde je množstvo viac ako 20 (povedzme 23), spĺňa obe naše podmienky (= $ D2> 15 a = $ D2> 20). A keďže je stav Orangeu na vrchole, dostáva prednosť.

Poradie podmienok môžete zmeniť pomocou tlačidiel Posunúť nahor/nadol.

Kliknite tu stiahnite si vzorový súbor a postupujte podľa neho.

Zvýraznite riadky, kde je ľubovoľná bunka prázdna

Ak chcete zvýrazniť všetky riadky, v ktorých sú bunky v ňom prázdne, musíte skontrolovať každú bunku pomocou podmieneného formátovania.

Tu sú kroky, ako to urobiť:

1. Vyberte celú množinu údajov (v tomto prípade A2: F17).
2. Kliknite na kartu Domov.
3. V skupine Štýly kliknite na Podmienené formátovanie.
4. Kliknite na položku „Nové pravidlá“.
5. V dialógovom okne „Nové pravidlo formátovania“ kliknite na „Pomocou vzorca určiť, ktoré bunky sa majú formátovať“.
6. Do poľa vzorca zadajte nasledujúci vzorec: = COUNTIF ($ A2: $ F2, ””)> 0
7. Kliknite na tlačidlo „Formátovať“. V dialógovom okne, ktoré sa otvorí, nastavte farbu na oranžovú.
8. Kliknite na tlačidlo OK.

Vyššie uvedený vzorec počíta počet prázdnych buniek. Ak je výsledok viac ako 0, znamená to, že v tomto riadku sú prázdne bunky.

Ak sú niektoré bunky prázdne, zvýrazní sa celý riadok.

Zvýraznite riadky na základe rozbaľovacieho výberu

V doposiaľ uvedených príkladoch boli všetky podmienky zadané v dialógovom okne podmieneného formátovania.

V tejto časti tutoriálu vám ukážem, ako to urobiť dynamickým (aby ste mohli podmienku zadať do bunky v Exceli a tá na základe nej automaticky zvýrazní riadky).

Nasleduje príklad, kde v rozbaľovacej ponuke vyberiem názov a zvýraznia sa všetky riadky s týmto názvom:

Tu sú kroky na jeho vytvorenie:

1. V bunke A2 vytvorte rozbaľovací zoznam. Tu som použil názvy predajných zástupcov na vytvorenie rozbaľovacieho zoznamu. Tu je podrobný návod, ako vytvoriť rozbaľovací zoznam v programe Excel.
2. Vyberte celý súbor údajov (v tomto prípade C2: H17).
3. Kliknite na kartu Domov.
4. V skupine Štýly kliknite na Podmienené formátovanie.
5. Kliknite na položku „Nové pravidlá“.
6. V dialógovom okne „Nové pravidlo formátovania“ kliknite na „Pomocou vzorca určiť, ktoré bunky sa majú formátovať“.
7. Do poľa vzorca zadajte nasledujúci vzorec: = $ E2 = $ A $ 2
8. Kliknite na tlačidlo „Formátovať“. V dialógovom okne, ktoré sa otvorí, nastavte farbu na oranžovú.
9. Kliknite na tlačidlo OK.

Keď teraz v rozbaľovacej ponuke vyberiete ľubovoľný názov, automaticky sa zvýraznia riadky, v ktorých je názov rovnaký ako v rozbaľovacom zozname.

Chcete sa dozvedieť viac o tom, ako vyhľadávať a zvýrazňovať v programe Excel? Pozrite sa na nižšie uvedené videá.