Formatare condiționată în Excel: ultimul ghid cu exemple

Formatarea condiționată este una dintre cele mai simple, dar puternice caracteristici din foile de calcul Excel.

După cum sugerează și numele, puteți utiliza formatarea condițională în Excel atunci când doriți să evidențiați celulele care îndeplinesc o condiție specificată.

Vă oferă posibilitatea de a adăuga rapid un strat de analiză vizuală peste setul de date. Puteți crea hărți de căldură, afișa pictograme crescătoare / descrescătoare, bule Harvey și multe altele folosind formatarea condiționată în Excel.

Utilizarea formatării condiționate în Excel (exemple)

În acest tutorial, vă voi arăta șapte exemple uimitoare de utilizare a formatării condiționate în Excel:

• Identificați rapid duplicatele folosind formatarea condiționată în Excel.
• Evidențiați celulele cu valoare mai mare / mai mică decât un număr dintr-un set de date.
• Evidențierea valorilor de sus / jos 10 (sau 10%) într-un set de date.
• Evidențierea erorilor / golurilor folosind formatarea condiționată în Excel.
• Crearea hărților de căldură folosind formatarea condiționată în Excel.
• Evidențiați fiecare al doilea rând / coloană folosind formatarea condiționată.
• Căutați și evidențiați folosind formatarea condiționată în Excel.

1. Identificați rapid duplicatele

Formatarea condiționată în Excel poate fi utilizată pentru a identifica duplicatele dintr-un set de date.

Iată cum puteți face acest lucru:

• Selectați setul de date în care doriți să evidențiați duplicatele.
• Accesați Acasă -> Formatare condiționată -> Evidențierea regulilor celulei -> Valori duplicate.
• În caseta de dialog Duplicare valori, asigurați-vă că Duplicate este selectat în meniul derulant din stânga. Puteți specifica formatul care trebuie aplicat utilizând meniul derulant din dreapta. Există câteva formate existente pe care le puteți utiliza sau specificați propriul format folosind opțiunea Format personalizat.
• Faceți clic pe OK.

Aceasta ar evidenția instantaneu toate celulele care au un duplicat în setul de date selectat. Setul dvs. de date poate fi într-o singură coloană, mai multe coloane sau într-o gamă necontiguă de celule.

Vezi si: Ghidul final pentru găsirea și eliminarea duplicatelor în Excel.

2. Evidențiați celulele cu valoare mai mare / mai mică decât un număr

Puteți utiliza formatarea condițională în Excel pentru a evidenția rapid celulele care conțin valori mai mari / mai mici decât o valoare specificată. De exemplu, evidențierea tuturor celulelor cu valoare de vânzare mai mică de 100 de milioane sau evidențierea celulelor cu semne mai mici decât pragul de trecere.

Iată pașii pentru a face acest lucru:

• Selectați întregul set de date.
• Accesați Acasă -> Formatare condiționată -> Evidențierea regulilor celulei -> Mai mare decât … / Mai puțin decât …
• Pe baza opțiunii selectate (mai mare sau mai mic decât), se va deschide o casetă de dialog. Să presupunem că selectați opțiunea „Mai mare decât”. În caseta de dialog, introduceți numărul în câmpul din stânga. Intenția este de a evidenția celulele care au un număr mai mare decât acest număr specificat.

• Specificați formatul care trebuie aplicat celulelor care îndeplinesc condiția utilizând meniul derulant din dreapta. Există câteva formate existente pe care le puteți utiliza sau specificați propriul format folosind opțiunea Format personalizat.
• Faceți clic pe OK.

Acest lucru ar evidenția instantaneu toate celulele cu valori mai mari de 5 dintr-un set de date.Notă: Dacă doriți să evidențiați valori mai mari decât egale cu 5, ar trebui să aplicați din nou formatarea condițională cu criteriile „Egal cu”.

Același proces poate fi urmărit pentru a evidenția celulele cu o valoare mai mică decât valorile specificate.

3. Evidențierea sus / jos 10 (sau 10%)

Formatarea condiționată în Excel poate identifica rapid primele 10 articole sau primele 10% dintr-un set de date. Acest lucru ar putea fi util în situațiile în care doriți să vedeți rapid candidații de top în funcție de scoruri sau cele mai bune valori ale ofertei în datele de vânzări.

În mod similar, puteți identifica rapid cele 10 elemente inferioare sau 10% inferioare într-un set de date.

Iată pașii pentru a face acest lucru:

• Selectați întregul set de date.
• Accesați Acasă -> Formatare condiționată -> Reguli de sus / jos -> Top 10 articole (sau%) / 10 elemente inferioare (sau%).
• Pe baza a ceea ce selectați, va deschide caseta de dialog. Să presupunem că ați selectat Top 10 articole, apoi ar deschide o casetă de dialog așa cum se arată mai jos:
• Specificați formatul care trebuie aplicat celulelor care îndeplinesc condiția utilizând meniul derulant din dreapta. Există câteva formate existente pe care le puteți utiliza sau specificați propriul format folosind opțiunea Format personalizat.
• Faceți clic pe OK.

Aceasta ar evidenția instantaneu primele 10 articole din setul de date selectat. Rețineți că acest lucru funcționează numai pentru celulele care au o valoare numerică.

De asemenea, dacă aveți mai puțin de 10 celule în setul de date și selectați opțiunile pentru a evidenția Top 10 articole / 10 elemente de jos, atunci toate celulele vor fi evidențiate.

Iată câteva exemple despre cum ar funcționa formatarea condițională:

4. Evidențierea erorilor / golurilor

Dacă lucrați cu o mulțime de date numerice și calcule în Excel, ați cunoaște importanța identificării și tratării celulelor care au erori sau sunt goale. Dacă aceste celule sunt utilizate în alte calcule, ar putea duce la rezultate eronate.

Formatarea condiționată în Excel vă poate ajuta să identificați și să evidențiați rapid celulele care au erori sau sunt goale.

Să presupunem că avem un set de date așa cum se arată mai jos:

Acest set de date are o celulă goală (A4) și erori (A5 și A6).

Iată pașii pentru a evidenția celulele goale sau cu erori:

• Selectați setul de date în care doriți să evidențiați celulele goale și celulele cu erori.
• Accesați Pagina principală -> Formatare condiționată -> Regulă nouă.
• În caseta de dialog Nouă regulă de formatare selectați Utilizați o formulă pentru a determina ce celule să formatați.
• Introduceți următoarea formulă în câmpul din secțiunea „Editați descrierea regulii”:
  = SAU (ISBLANK (A1), ISERROR (A1))
  • Formula de mai sus verifică toate celulele pentru două condiții - dacă este goală sau nu și dacă are o eroare sau nu. Dacă oricare dintre condiții este ADEVĂRATĂ, returnează ADEVĂRAT.
• Setați formatul pe care doriți să îl aplicați celulelor goale sau cu erori. Pentru a face acest lucru, faceți clic pe butonul Format. Se va deschide caseta de dialog „Formatare celule”, unde puteți specifica formatul.
• Faceți clic pe Ok.

Acest lucru ar evidenția instantaneu toate celulele care sunt goale sau au erori.

Notă: Nu este nevoie să utilizați întreaga gamă A1: A7 în formula în formatare condiționată. Formula menționată mai sus utilizează doar A1. Când aplicați această formulă întregii game, Excel verifică câte o celulă la un moment dat și ajustează referința. De exemplu, când verifică A1, folosește formula = OR (ISBLANK (A1), ISERROR (A1)). Când verifică celula A2, folosește apoi formula = OR (ISBLANK (A2), ISERROR (A2)). Reglează automat referința (deoarece acestea sunt referințe relative) în funcție de ce celulă este analizată. Deci, nu trebuie să scrieți o formulă separată pentru fiecare celulă. Excel este suficient de inteligent pentru a schimba singur referința celulei 🙂

Vezi si: Utilizarea IFERROR și ISERROR pentru a gestiona erorile în Excel.

5. Crearea hărților de căldură

O hartă de căldură este o reprezentare vizuală a datelor în care culoarea reprezintă valoarea dintr-o celulă. De exemplu, puteți crea o hartă de căldură în care o celulă cu cea mai mare valoare este colorată în verde și există o schimbare către culoarea roșie pe măsură ce valoarea scade.

Ceva așa cum se arată mai jos:

Setul de date de mai sus are valori cuprinse între 1 și 100. Celulele sunt evidențiate pe baza valorii din acesta. 100 primește culoarea verde, 1 primește culoarea roșie.

Iată pașii pentru a crea hărți de căldură folosind formatarea condiționată în Excel.

• Selectați setul de date.
• Accesați Acasă -> Formatare condiționată -> Scale de culori și alegeți una dintre schemele de culori.

De îndată ce faceți clic pe pictograma hartă, aceasta va aplica formatarea setului de date. Există mai multe gradiente de culoare din care puteți alege. Dacă nu sunteți mulțumit de opțiunile de culoare existente, puteți selecta mai multe reguli și puteți specifica culoarea dorită.

Notă: în mod similar, puteți aplica și seturi de barde de date și pictograme.

6. Evidențiați orice alt rând / coloană

Poate doriți să evidențiați rânduri alternative pentru a crește lizibilitatea datelor.

Acestea se numesc linii zebra și ar putea fi deosebit de utile dacă tipăriți datele.

Acum există două moduri de a crea aceste linii zebra. Cel mai rapid mod este de a vă converti datele tabulare într-un tabel Excel. A aplicat automat o culoare pe rânduri alternative. Puteți citi mai multe despre asta aici.

O altă modalitate este folosirea formatării condiționate.

Să presupunem că aveți un set de date așa cum se arată mai jos:

Iată pașii pentru evidențierea rândurilor alternative utilizând formatarea condiționată în Excel.

• Selectați setul de date. În exemplul de mai sus, selectați A2: C13 (care exclude antetul). Dacă doriți să includeți și antetul, selectați întregul set de date.
• Deschideți caseta de dialog Formatare condițională (Acasă-> Formatare condițională-> Regula nouă) [Comandă rapidă de la tastatură - Alt + O + D].
• În caseta de dialog, selectați caseta de dialog „Utilizați o formulă pentru a determina ce celule să formatați”.
• Introduceți următoarea formulă în câmpul din secțiunea „Editați descrierea regulii”:
  = ISODD (ROW ())

• Formula de mai sus verifică toate celulele și dacă numărul ROW al unei celule este impar, atunci revine ADEVĂRAT. Formatul condițional specificat ar fi aplicat tuturor celulelor care returnează TRUE.
• Setați formatul pe care doriți să-l aplicați celulelor goale sau care au erori. Pentru a face acest lucru, faceți clic pe butonul Format. Se va deschide caseta de dialog „Formatare celule”, unde puteți specifica formatul.
• Faceți clic pe OK.

Asta e! Rândurile alternative din setul de date vor fi evidențiate.

Puteți utiliza aceeași tehnică în multe cazuri. Tot ce trebuie să faceți este să utilizați formula relevantă în formatarea condiționată. Aici sunt cateva exemple:

• Evidențiați rânduri pare alternative: = ISEVEN (ROW ())
• Evidențiați alternativ adăugați rânduri: = ISODD (ROW ())
• Evidențiați fiecare al treilea rând: = MOD (ROW (), 3) = 0

7. Căutați și evidențiați datele folosind formatarea condiționată

Aceasta este o utilizare puțin avansată a formatării condiționate. Te-ar face să arăți ca un rockstar Excel.

Să presupunem că aveți un set de date așa cum se arată mai jos, cu numele produselor, reprezentant de vânzări și geografie. Ideea este să tastați un șir în celula C2 și, dacă se potrivește cu datele din orice celulă (celule), atunci ar trebui evidențiat. Ceva așa cum se arată mai jos:

Iată pașii pentru a crea această funcționalitate de căutare și evidențiere:

• Selectați setul de date.
• Accesați Pagina principală -> Formatare condiționată -> Regulă nouă (Comandă rapidă de la tastatură - Alt + O + D).
• În caseta de dialog Nouă regulă de formatare, selectați opțiunea „Utilizați o formulă pentru a determina ce celule să formatați”.
• Introduceți următoarea formulă în câmpul din secțiunea „Editați descrierea regulii”:
  = ȘI ($ C $ 2 ””, $ C $ 2 = B5)
• Setați formatul pe care doriți să-l aplicați celulelor goale sau care au erori. Pentru a face acest lucru, faceți clic pe butonul Format. Se va deschide caseta de dialog „Formatare celule”, unde puteți specifica formatul.
• Faceți clic pe OK.

Asta e! Acum, când introduceți ceva în celula C2 și apăsați Enter, acesta va evidenția toate celulele potrivite.

Cum funcționează asta?

Formula utilizată în formatarea condiționată evaluează toate celulele din setul de date. Să presupunem că intrați în Japonia în celula C2. Acum Excel ar evalua formula pentru fiecare celulă.

Formula va returna TRUE pentru o celulă atunci când sunt îndeplinite două condiții:

• Celula C2 nu este goală.
• Conținutul celulei C2 se potrivește exact cu conținutul celulei din setul de date.

Prin urmare, toate celulele care conțin textul Japonia sunt evidențiate.

Descărcați fișierul de exemplu

Puteți utiliza aceeași logică pentru a crea variații precum:

• Evidențiați întregul rând în locul unei celule.
• Evidențiați chiar și atunci când există o potrivire parțială.
• Evidențiați celulele / rândurile pe măsură ce tastați (dinamic) [O să-ți placă acest truc :)].

Cum se elimină formatarea condiționată în Excel

Odată aplicat, formatarea condiționată rămâne în loc, cu excepția cazului în care o eliminați manual. Ca o bună practică, păstrați formatarea condițională aplicată numai acelor celule în care aveți nevoie.

Deoarece este volatil, poate duce la un registru de lucru Excel lent.

Pentru a elimina formatarea condiționată:

• Selectați celulele din care doriți să eliminați formatarea condiționată.
• Accesați Acasă -> Formatare condiționată -> Ștergeți regulile -> Ștergeți regulile din celulele selectate.
  • Dacă doriți să eliminați formatarea condiționată din întreaga foaie de lucru, selectați Ștergeți regulile din întreaga foaie.

Lucruri importante de știut despre formatarea condiționată în Excel

• Formatare condiționată în volatil. Poate duce la un registru de lucru lent. Folosiți-l numai atunci când este necesar.
• Când copiați celule lipite care conțin formatare condiționată, formatarea condiționată este copiată.
• Dacă aplicați mai multe reguli pe același set de celule, toate regulile rămân active. În cazul oricărei suprapuneri, se aplică ultima regulă aplicată. Cu toate acestea, puteți modifica ordinea modificând comanda din caseta de dialog Gestionare reguli.