Označite vrstice na podlagi vrednosti celice v Excelu (pogojno oblikovanje)

Oglejte si video - Označite vrstice na podlagi vrednosti celic v Excelu

Če namesto tega raje berete pisna navodila, je spodaj vadnica.

Pogojno oblikovanje omogoča oblikovanje celice (ali obsega celic) glede na vrednost v njej.

Včasih pa boste namesto, da celico samo označite, morda želeli označiti celotno vrstico (ali stolpec) glede na vrednost v eni celici.

Za primer vam predstavljam nabor podatkov, v katerem sem označil vse vrstice, v katerih je ime prodajnega predstavnika Bob.

V tej vadnici vam bom pokazal, kako označiti vrstice na podlagi vrednosti celice z uporabo pogojnega oblikovanja z različnimi merili.

Klikni tukaj da prenesete datoteko z vzorcem in sledite.

Označite vrstice na podlagi besedilnih meril

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite označiti vse zapise, kjer je ime prodajnega predstavnika Bob.

Tu so naslednji koraki:

1. Izberite celoten nabor podatkov (A2: F17 v tem primeru).
2. Kliknite zavihek Domov.
3. V skupini Slogi kliknite Pogojno oblikovanje.
4. Kliknite »Nova pravila«.
5. V pogovornem oknu »Novo pravilo oblikovanja« kliknite »Uporabi formulo, da določite, katere celice želite formatirati«.
6. V polje formule vnesite naslednjo formulo: = $ C2 = "Bob"
7. Kliknite gumb »Oblikuj«.
8. V pogovornem oknu, ki se odpre, nastavite barvo, v kateri želite, da je vrstica označena.
9. Kliknite V redu.

S tem bodo označene vse vrstice, kjer je ime prodajnega predstavnika "Bob".

Klikni tukaj da prenesete datoteko z vzorcem in sledite.

Kako deluje?

Pogojno oblikovanje v vsaki celici preveri stanje, ki smo ga podali, to je = $ C2 = "Bob"

Ko bo torej analiziral vsako celico v vrstici A2, bo preveril, ali ima celica C2 ime Bob ali ne. Če se to zgodi, se celica označi, drugače pa ne.

Upoštevajte, da je tukaj trik uporaba znaka dolarja ($) pred abecedo stolpca ($ C1). S tem smo stolpec zaklenili tako, da je vedno C. Torej, tudi če celico A2 preverjamo po formuli, bo preverila C2, in ko bo A3 preverila stanje, bo preverila C3.

To nam omogoča, da s pogojnim oblikovanjem označimo celotno vrstico.

Označite vrstice na podlagi meril števila

V zgornjem primeru smo videli, kako preveriti ime in označiti celotno vrstico.

Z isto metodo lahko preverimo tudi številčne vrednosti in označimo vrstice na podlagi pogoja.

Recimo, da imam iste podatke (kot je prikazano spodaj), in želim poudariti vse vrstice, kjer je količina večja od 15.

Tu so naslednji koraki:

1. Izberite celoten nabor podatkov (A2: F17 v tem primeru).
2. Kliknite zavihek Domov.
3. V skupini Slogi kliknite Pogojno oblikovanje.
4. Kliknite »Nova pravila«.
5. V pogovornem oknu »Novo pravilo oblikovanja« kliknite »Uporabi formulo, da določite, katere celice želite formatirati«.
6. V polje formule vnesite naslednjo formulo: = $ D2> = 15
7. Kliknite gumb »Oblikuj«. V pogovornem oknu, ki se odpre, nastavite barvo, v kateri želite, da se vrstica označi.
8. Kliknite V redu.

S tem bodo označene vse vrstice, kjer je količina večja ali enaka 15.

Podobno lahko to uporabite tudi za merila za datum.

Če želite na primer označiti vse vrstice, kjer je datum po 10. juliju 2021–2022, lahko uporabite spodnjo formulo datuma:

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

Označite vrstice na podlagi več meril (IN/ALI)

Za označevanje vrstic s pogojnim oblikovanjem lahko uporabite tudi več meril.

Če želite na primer označiti vse vrstice, kjer je ime prodajnega predstavnika "Bob" in je količina večja od 10, lahko to storite z naslednjimi koraki:

1. Izberite celoten nabor podatkov (A2: F17 v tem primeru).
2. Kliknite zavihek Domov.
3. V skupini Slogi kliknite Pogojno oblikovanje.
4. Kliknite »Nova pravila«.
5. V pogovornem oknu »Novo pravilo oblikovanja« kliknite »Uporabi formulo, da določite, katere celice želite formatirati«.
6. V polje formule vnesite naslednjo formulo: = AND ($ C2 = ”Bob”, $ D2> 10)
7. Kliknite gumb »Oblikuj«. V pogovornem oknu, ki se odpre, nastavite barvo, v kateri želite, da se vrstica označi.
8. Kliknite V redu.

V tem primeru so označene samo tiste vrstice, kjer sta izpolnjena oba pogoja (to se naredi s formulo AND).

Podobno lahko uporabite tudi pogoj OR. Na primer, če želite označiti vrstice, kjer je prodajni predstavnik Bob ali je količina večja od 15, lahko uporabite spodnjo formulo:

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

Klikni tukaj da prenesete datoteko z vzorcem in sledite.

Označite vrstice v različnih barvah glede na več pogojev

Včasih boste morda želeli označiti vrstice v barvi glede na stanje.

Morda boste na primer želeli označiti vse vrstice, kjer je količina večja od 20 v zeleni barvi in ​​kjer je količina večja od 15 (vendar manj kot 20) v oranžni barvi.

Če želite to narediti, morate ustvariti dva pravila pogojnega oblikovanja in nastaviti prednost.

Tu so naslednji koraki:

1. Izberite celoten nabor podatkov (A2: F17 v tem primeru).
2. Kliknite zavihek Domov.
3. V skupini Slogi kliknite Pogojno oblikovanje.
4. Kliknite »Nova pravila«.
5. V pogovornem oknu »Novo pravilo oblikovanja« kliknite »Uporabi formulo, da določite, katere celice želite formatirati«.
6. V polje formule vnesite naslednjo formulo: = $ D2> 15
7. Kliknite gumb »Oblikuj«. V pogovornem oknu, ki se odpre, nastavite barvo na oranžno.
8. Kliknite V redu.
9. V pogovornem oknu »Upravitelj pravil pogojnega oblikovanja« kliknite »Novo pravilo«.
10. V pogovornem oknu »Novo pravilo oblikovanja« kliknite »Uporabi formulo, da določite, katere celice želite formatirati«.
11. V polje formule vnesite naslednjo formulo: = $ D2> 20
12. Kliknite gumb »Oblikuj«. V pogovornem oknu, ki se odpre, nastavite barvo na zeleno.
13. Kliknite V redu.
14. Kliknite Uporabi (ali V redu).

Zgornji koraki bi naredili vse vrstice s količino več kot 20 v zeleni barvi in ​​tiste z več kot 15 (vendar manj kot 20 v oranžni barvi).

Razumevanje vrstnega reda pravil:

Pri uporabi več pogojev je pomembno, da se prepričate, da je vrstni red pogojev pravilen.

V zgornjem primeru je stanje zelene barve nad oranžnim.

Če je obratno, bi bile vse vrstice obarvane samo v oranžni barvi.

Zakaj?

Ker vrstica, kjer je količina večja od 20 (recimo 23), izpolnjuje oba pogoja (= $ D2> 15 in = $ D2> 20). In ker je oranžno stanje na vrhu, ima prednost.

Vrstni red pogojev lahko spremenite z gumbi Premakni gor/dol.

Klikni tukaj da prenesete datoteko z vzorcem in sledite temu.

Označite vrstice, kjer je katera koli celica prazna

Če želite označiti vse vrstice, kjer je katera koli celica v njej prazna, morate za vsako celico preveriti s pogojnim oblikovanjem.

Tu so naslednji koraki:

1. Izberite celoten nabor podatkov (A2: F17 v tem primeru).
2. Kliknite zavihek Domov.
3. V skupini Slogi kliknite Pogojno oblikovanje.
4. Kliknite »Nova pravila«.
5. V pogovornem oknu »Novo pravilo oblikovanja« kliknite »Uporabi formulo, da določite, katere celice želite formatirati«.
6. V polje formule vnesite naslednjo formulo: = COUNTIF ($ A2: $ F2, ””)> 0
7. Kliknite gumb »Oblikuj«. V pogovornem oknu, ki se odpre, nastavite barvo na oranžno.
8. Kliknite V redu.

Zgornja formula šteje število praznih celic. Če je rezultat večji od 0, to pomeni, da so v tej vrstici prazne celice.

Če je katera od celic prazna, označi celotno vrstico.

Označite vrstice na podlagi padajoče izbire

V dosedanjih primerih so bili vsi pogoji podani s pogovornim oknom za pogojno oblikovanje.

V tem delu vadnice vam bom pokazal, kako ga narediti dinamičnega (tako da lahko vnesete pogoj v celico v Excelu in bo samodejno označil vrstice na podlagi tega).

Spodaj je primer, ko iz spustnega menija izberem ime, vse vrstice s tem imenom pa bodo označene:

Tukaj so koraki za ustvarjanje tega:

1. Ustvarite spustni seznam v celici A2. Tu sem uporabil imena prodajnega predstavnika za ustvarjanje spustnega seznama. Tukaj je podroben vodnik o tem, kako ustvariti spustni seznam v Excelu.
2. Izberite celoten nabor podatkov (C2: H17 v tem primeru).
3. Kliknite zavihek Domov.
4. V skupini Slogi kliknite Pogojno oblikovanje.
5. Kliknite »Nova pravila«.
6. V pogovornem oknu »Novo pravilo oblikovanja« kliknite »Uporabi formulo, da določite, katere celice želite formatirati«.
7. V polje formule vnesite naslednjo formulo: = $ E2 = $ A $ 2
8. Kliknite gumb »Oblikuj«. V pogovornem oknu, ki se odpre, nastavite barvo na oranžno.
9. Kliknite V redu.

Ko iz spustnega menija izberete katero koli ime, bo samodejno označilo vrstice, kjer je ime enako, kot ste ga izbrali v spustnem meniju.

Vas zanima več o tem, kako iskati in označevati v Excelu? Oglejte si spodnje videoposnetke.