Evidențiați rândurile bazate pe o valoare a celulei în Excel (formatare condiționată)

Vizionați videoclipuri - Evidențiați rândurile pe baza valorilor celulare în Excel

În cazul în care preferați să citiți instrucțiuni scrise, mai jos este tutorialul.

Formatarea condiționată vă permite să formatați o celulă (sau o gamă de celule) pe baza valorii din ea.

Dar uneori, în loc să evidențiați doar celula, poate doriți să evidențiați întregul rând (sau coloană) pe baza valorii dintr-o celulă.

Pentru a vă oferi un exemplu, mai jos am un set de date în care am evidențiat toate rândurile în care numele reprezentantului de vânzări este Bob.

În acest tutorial, vă voi arăta cum să evidențiați rândurile pe baza unei valori de celulă utilizând formatarea condiționată folosind diferite criterii.

Click aici pentru a descărca fișierul Exemplu și a continua.

Evidențiați rândurile pe baza unui criteriu de text

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să evidențiați toate înregistrările unde numele reprezentantului de vânzări este Bob.

Iată pașii pentru a face acest lucru:

1. Selectați întregul set de date (A2: F17 în acest exemplu).
2. Faceți clic pe fila Acasă.
3. În grupul Stiluri, faceți clic pe Formatare condiționată.
4. Faceți clic pe „Reguli noi”.
5. În caseta de dialog „Regula de formatare nouă”, faceți clic pe „Utilizați o formulă pentru a determina ce celule să formatați”.
6. În câmpul formulă, introduceți următoarea formulă: = $ C2 = „Bob”
7. Faceți clic pe butonul „Formatare”.
8. În caseta de dialog care se deschide, setați culoarea în care doriți să fie evidențiat rândul.
9. Faceți clic pe OK.

Aceasta va evidenția toate rândurile unde numele reprezentantului de vânzări este „Bob”.

Click aici pentru a descărca fișierul Exemplu și a continua.

Cum functioneazã?

Formatarea condițională verifică fiecare celulă pentru starea pe care am specificat-o, care este = $ C2 = „Bob”

Deci, atunci când analizează fiecare celulă din rândul A2, va verifica dacă celula C2 are sau nu numele Bob. Dacă da, celula respectivă este evidențiată, altfel nu.

Rețineți că trucul de aici este să folosiți un semn de dolar ($) înainte de alfabetul coloanei ($ C1). Făcând acest lucru, am blocat coloana pentru a fi întotdeauna C. Deci, chiar și atunci când celula A2 este verificată pentru formulă, va verifica C2, iar când A3 este verificată pentru condiție, va verifica C3.

Acest lucru ne permite să evidențiem întregul rând prin formatarea condiționată.

Evidențiați rândurile pe baza unui criteriu de număr

În exemplul de mai sus, am văzut cum să verificăm un nume și să evidențiem întregul rând.

Putem folosi aceeași metodă și pentru a verifica valorile numerice și a evidenția rândurile pe baza unei condiții.

Să presupunem că am aceleași date (așa cum se arată mai jos) și vreau să evidențiez toate rândurile în care cantitatea este mai mare de 15.

Iată pașii pentru a face acest lucru:

1. Selectați întregul set de date (A2: F17 în acest exemplu).
2. Faceți clic pe fila Acasă.
3. În grupul Stiluri, faceți clic pe Formatare condiționată.
4. Faceți clic pe „Reguli noi”.
5. În caseta de dialog „Regula de formatare nouă”, faceți clic pe „Utilizați o formulă pentru a determina ce celule să formatați”.
6. În câmpul formulă, introduceți următoarea formulă: = $ D2> = 15
7. Faceți clic pe butonul „Formatare”. În caseta de dialog care se deschide, setați culoarea în care doriți să fie evidențiat rândul.
8. Faceți clic pe OK.

Aceasta va evidenția toate rândurile în care cantitatea este mai mare sau egală cu 15.

În mod similar, putem folosi acest lucru și pentru a avea criterii și pentru dată.

De exemplu, dacă doriți să evidențiați toate rândurile în care data este după 10 iulie 2021-2022, puteți utiliza formula datei de mai jos:

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

Evidențiați rândurile pe baza unor criterii multiple (ȘI / SAU)

De asemenea, puteți utiliza mai multe criterii pentru a evidenția rândurile folosind formatarea condiționată.

De exemplu, dacă doriți să evidențiați toate rândurile în care numele reprezentantului de vânzări este „Bob” și cantitatea este mai mare de 10, puteți face acest lucru urmând pașii următori:

1. Selectați întregul set de date (A2: F17 în acest exemplu).
2. Faceți clic pe fila Acasă.
3. În grupul Stiluri, faceți clic pe Formatare condiționată.
4. Faceți clic pe „Reguli noi”.
5. În caseta de dialog „Regula de formatare nouă”, faceți clic pe „Utilizați o formulă pentru a determina ce celule să formatați”.
6. În câmpul formulă, introduceți următoarea formulă: = ȘI ($ C2 = „Bob”, $ D2> 10)
7. Faceți clic pe butonul „Formatare”. În caseta de dialog care se deschide, setați culoarea în care doriți să fie evidențiat rândul.
8. Faceți clic pe OK.

În acest exemplu, numai acele rânduri sunt evidențiate acolo unde sunt îndeplinite ambele condiții (acest lucru se face folosind formula ȘI).

În mod similar, puteți utiliza și condiția SAU. De exemplu, dacă doriți să evidențiați rândurile în care reprezentantul vânzărilor este Bob sau cantitatea este mai mare de 15, puteți utiliza formula de mai jos:

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

Click aici pentru a descărca fișierul Exemplu și a continua.

Evidențiați rândurile în culori diferite pe baza mai multor condiții

Uneori, poate doriți să evidențiați rândurile într-o culoare pe baza condiției.

De exemplu, poate doriți să evidențiați toate rândurile în care cantitatea este mai mare de 20 în verde și în care cantitatea este mai mare de 15 (dar mai puțin de 20) în portocaliu.

Pentru a face acest lucru, trebuie să creați două reguli de formatare condiționată și să setați prioritatea.

Iată pașii pentru a face acest lucru:

1. Selectați întregul set de date (A2: F17 în acest exemplu).
2. Faceți clic pe fila Acasă.
3. În grupul Stiluri, faceți clic pe Formatare condiționată.
4. Faceți clic pe „Reguli noi”.
5. În caseta de dialog „Regula de formatare nouă”, faceți clic pe „Utilizați o formulă pentru a determina ce celule să formatați”.
6. În câmpul formulă, introduceți următoarea formulă: = $ D2> 15
7. Faceți clic pe butonul „Formatare”. În caseta de dialog care se deschide, setați culoarea la portocaliu.
8. Faceți clic pe OK.
9. În caseta de dialog „Conditional Formatting Rules Manager”, faceți clic pe „Noua regulă”.
10. În caseta de dialog „Regula de formatare nouă”, faceți clic pe „Utilizați o formulă pentru a determina ce celule să formatați”.
11. În câmpul formulă, introduceți următoarea formulă: = $ D2> 20
12. Faceți clic pe butonul „Formatare”. În caseta de dialog care se deschide, setați culoarea la Verde.
13. Faceți clic pe OK.
14. Faceți clic pe Aplicare (sau OK).

Pașii de mai sus ar face toate rândurile cu cantitate mai mare de 20 în verde și cele cu mai mult de 15 (dar mai puțin egale cu 20 în portocaliu).

Înțelegerea ordinii regulilor:

Când utilizați condiții multiple, este important să vă asigurați că ordinea condițiilor este corectă.

În exemplul de mai sus, starea de culoare verde este peste starea de culoare portocalie.

Dacă este invers, toate rândurile vor fi colorate doar în portocaliu.

De ce?

Deoarece un rând în care cantitatea este mai mare de 20 (să zicem 23) ne îndeplinește ambele condiții (= $ D2> 15 și = $ D2> 20). Și din moment ce starea Orange este în partea de sus, devine preferată.

Puteți modifica ordinea condițiilor folosind butoanele Mutare sus / jos.

Click aici pentru a descărca fișierul Exemplu și a continua.

Evidențiați rândurile în care orice celulă este necompletată

Dacă doriți să evidențiați toate rândurile în care oricare dintre celulele din acesta este goală, trebuie să verificați fiecare celulă folosind formatarea condiționată.

Iată pașii pentru a face acest lucru:

1. Selectați întregul set de date (A2: F17 în acest exemplu).
2. Faceți clic pe fila Acasă.
3. În grupul Stiluri, faceți clic pe Formatare condiționată.
4. Faceți clic pe „Reguli noi”.
5. În caseta de dialog „Regula de formatare nouă”, faceți clic pe „Utilizați o formulă pentru a determina ce celule să formatați”.
6. În câmpul formulă, introduceți următoarea formulă: = COUNTIF ($ A2: $ F2, ””)> 0
7. Faceți clic pe butonul „Formatare”. În caseta de dialog care se deschide, setați culoarea la portocaliu.
8. Faceți clic pe OK.

Formula de mai sus numără numărul de celule goale. Dacă rezultatul este mai mare de 0, înseamnă că există celule goale în acel rând.

Dacă oricare dintre celule sunt goale, evidențiază întregul rând.

Evidențiați rândurile pe baza selecției drop-down

În exemplele acoperite până acum, toate condițiile au fost specificate cu caseta de dialog formatare condițională.

În această parte a tutorialului, vă voi arăta cum să o faceți dinamică (astfel încât să puteți introduce starea într-o celulă din Excel și va evidenția automat rândurile pe baza ei).

Mai jos este un exemplu, în care selectez un nume din meniul derulant și toate rândurile cu acel nume sunt evidențiate:

Iată pașii pentru a crea acest lucru:

1. Creați o listă derulantă în celula A2. Aici am folosit numele reprezentantului de vânzări pentru a crea lista derulantă. Iată un ghid detaliat despre cum să creați o listă derulantă în Excel.
2. Selectați întregul set de date (C2: H17 în acest exemplu).
3. Faceți clic pe fila Acasă.
4. În grupul Stiluri, faceți clic pe Formatare condiționată.
5. Faceți clic pe „Reguli noi”.
6. În caseta de dialog „Regula de formatare nouă”, faceți clic pe „Utilizați o formulă pentru a determina ce celule să formatați”.
7. În câmpul formulă, introduceți următoarea formulă: = $ E2 = $ A $ 2
8. Faceți clic pe butonul „Formatare”. În caseta de dialog care se deschide, setați culoarea la portocaliu.
9. Faceți clic pe OK.

Acum, când selectați orice nume din meniul derulant, acesta va evidenția automat rândurile în care numele este același pe care l-ați selectat din meniul derulant.

Vreți să aflați mai multe despre cum să căutați și să evidențiați în Excel? Verificați videoclipurile de mai jos.