Adăugarea formatării condiționate în Excel vă permite să aplicați diferite opțiuni de formatare unei celule sau unei game de celule care îndeplinesc condițiile specifice pe care le setați. Setarea unor astfel de condiții poate ajuta la organizarea foii de calcul și poate facilita scanarea. Opțiunile de formatare pe care le puteți utiliza includ modificări ale fontului și ale culorii de fundal, stiluri de font, chenarele celulelor și adăugarea formatării numerelor la date.
Excel are opțiuni încorporate pentru condițiile utilizate în mod obișnuit, cum ar fi găsirea numerelor care sunt mai mari sau mai mici decât o anumită valoare sau găsirea numerelor care sunt peste sau sub valoarea medie. Pe lângă aceste opțiuni prestabilite, puteți crea și reguli personalizate de formatare condiționată folosind formule Excel.
Aceste instrucțiuni se aplică pentru Excel 2019, 2016, 2013, 2010 și Excel pentru Microsoft 365.
Aplicarea mai multor condiții în Excel
Puteți aplica mai mult de o regulă acelorași date pentru a testa diferite condiții. De exemplu, datele bugetului pot avea condiții setate care să aplice modificări de format atunci când sunt atinse anumite niveluri de cheltuieli, cum ar fi 50%, 75% și 100% din bugetul total.
În astfel de circumstanțe, Excel determină mai întâi dacă diferitele reguli sunt în conflict și, dacă da, programul urmează o ordine de prioritate stabilită pentru a determina ce regulă de formatare condiționată se aplică datelor.
Găsirea datelor care depășesc 25% și creșteri de 50%
În exemplul următor, două reguli de formatare condiționată personalizate vor fi aplicate în intervalul de celule B2 până la B5.
- Prima regulă verifică dacă datele din celule A2:A5 sunt mai mari decât valoarea corespunzătoare din B2:B5 de mai mult de 25%.
- A doua regulă verifică dacă aceleași date din A2:A5 depășesc valoarea corespunzătoare din B2:B5 cu mai mult de 50%.
După cum se poate vedea în imaginea de mai sus, dacă oricare dintre condițiile de mai sus este adevărată, culoarea de fundal a celulei sau a celulelor din intervalul B1:B4 se va modifica.
- Pentru datele în care diferența este mai mare de 25%, culoarea de fundal a celulei se va schimba în verde.
- Dacă diferența este mai mare de 50%, culoarea de fundal a celulei se va schimba în roșu.
Regulile utilizate pentru îndeplinirea acestei sarcini vor fi introduse utilizând caseta de dialog Regulă nouă de formatare. Începeți prin a introduce datele eșantionului în celule A1 la C5, așa cum se vede în imaginea de mai sus.
În ultima parte a tutorialului vom adăuga formule în celulele C2:C4 care arată diferența procentuală exactă dintre valorile din celulele A2:A5 și B2:B5; acest lucru ne va permite să verificăm acuratețea regulilor de formatare condiționată.
Setarea regulilor de formatare condiționată
În primul rând, vom aplica formatarea condiționată pentru a găsi o creștere semnificativă de 25 la sută sau mai mult.
Funcția va arăta astfel:
=(A2-B2)/A2>25%
- Evidențiați celule B2 la B5 în foaia de lucru.
- Faceți clic pe fila Acasă din panglică.
- Faceți clic pe pictograma Formatizare condiționată din ribbon pentru a deschide meniul drop-down.
-
Alege Regulă nouă pentru a deschide caseta de dialog Regulă nouă de formatare.
- Sub Selectați un tip de regulă, faceți clic pe ultima opțiune: Utilizați o formulă pentru a determina ce celule să formatați.
- Tastați formula menționată mai sus în spațiul de dedesubt Format valorile în care această formulă este adevărată:
- Faceți clic pe butonul Format pentru a deschide caseta de dialog. Faceți clic pe Fill fila și alegeți o culoare.
- Faceți clic pe OK pentru a închide casetele de dialog și a reveni la foaia de lucru.
- Culoarea de fundal a celule B3 și B5 ar trebui să se schimbe în culoarea pe care ați selectat-o.
Acum, vom aplica formatarea condiționată pentru a găsi o creștere de 50 la sută sau mai mare. Formula va arăta astfel:
- Repetați primii cinci pași de mai sus.
-
Tastați formula furnizată mai sus în spațiul de mai jos Format valorile în care această formulă este adevărată:
- Faceți clic pe butonul Format pentru a deschide caseta de dialog. Faceți clic pe fila Umplere și alegeți o culoare diferită de cea pe care ați făcut-o în setul anterior de pași.
- Faceți clic pe OK pentru a închide casetele de dialog și a reveni la foaia de lucru.
Culoarea de fundal a celula B3 ar trebui să rămână aceeași, indicând faptul că diferența procentuală dintre numerele din celule A3 șiB3 este mai mare de 25 la sută, dar mai mic sau egal cu 50 la sută. Culoarea de fundal a celula B5 ar trebui să se schimbe la noua culoare pe care ați selectat-o, indicând faptul că diferența procentuală dintre numerele din celule A5 și B5 este mai mare de 50 la sută.
Verificarea regulilor de formatare condiționată
Pentru a verifica dacă regulile de formatare condiționată introduse sunt corecte, putem introduce formule în celulele C2:C5 care vor calcula diferența procentuală exactă dintre numerele din intervaleleA2:A5 și B2:B5.
Formula din celula C2 arată astfel:
=(A2-B2)/A2
- Faceți clic pe celula C2 pentru a deveni celula activă.
- Tastați formula de mai sus și apăsați tasta Enter de pe tastatură.
- Răspunsul 10% ar trebui să apară în celula C2, indicând faptul că numărul din celula A2 este cu 10% mai mare decât numărul din celulă B2.
- Poate fi necesar să schimbați formatarea în celula C2 pentru a afișa răspunsul ca procent.
- Folosiți mânerul de completare pentru a copia formula din celula C2 în celule C3 în C5.
- Răspunsurile pentru celule C3 la C5 ar trebui să fie 30%, 25% și 60%.
Răspunsurile din aceste celule arată că regulile de formatare condiționată sunt corecte, deoarece diferența dintre celule A3 și B3 este mai mare de 25 la sută, iar diferența dintre celule A5 și B5 este mai mare de 50 la sută.
Celula B4 nu a schimbat culoarea deoarece diferența dintre celule A4 și B4 este egală 25 la sută, iar regula noastră de formatare condiționată a specificat că este necesar un procent mai mare de 25 la sută pentru ca culoarea de fundal să se schimbe.
Ordinea de prioritate pentru formatarea condiționată
Când aplicați mai multe reguli aceluiași interval de date, Excel stabilește mai întâi dacă regulile sunt în conflict. Regulile aflate în conflict sunt acelea în care opțiunile de formatare nu pot fi aplicate ambele la aceleași date.
În exemplul nostru, regulile sunt în conflict, deoarece ambele folosesc aceeași opțiune de formatare - schimbând culoarea celulei de fundal.
În situația în care a doua regulă este adevărată (diferența de valoare este mai mare de 50 la sută între două celule), atunci prima regulă (diferența de valoare fiind mai mare de 25 la sută) este, de asemenea, adevărată.
Deoarece o celulă nu poate avea ambele fundaluri de două culori diferite în același timp, Excel trebuie să știe ce regulă de formatare condiționată ar trebui să se aplice.
Ordinea de prioritate a Excel afirmă că regula care este mai sus în listă din caseta de dialog Manager reguli de formatare condiționată este aplicată mai întâi.
După cum se arată în imaginea de mai sus, a doua regulă folosită în acest tutorial este mai sus în listă și, prin urmare, are prioritate față de prima regulă. Drept urmare, culoarea de fundal a celula B5 este verde.
În mod implicit, regulile noi merg în partea de sus a listei; pentru a modifica ordinea, utilizați butoanele săgeți Sus și Jos din caseta de dialog.
Aplicarea regulilor neconflictuale
Dacă două sau mai multe reguli de formatare condiționată nu intră în conflict, ambele sunt aplicate atunci când condiția pe care o testează fiecare regulă devine adevărată.
Dacă prima regulă de formatare condiționată din exemplul nostru ar formata intervalul de celule B2:B5 cu un chenar portocaliu în loc de o culoare de fundal portocalie, cele două reguli de formatare condiționată nu ar conflict, deoarece ambele formate pot fi aplicate fără a interfera cu celăl alt.
Formatarea condiționată vs. Formatarea obișnuită
În cazul unor conflicte între regulile de formatare condiționată și opțiunile de formatare aplicate manual, regula de formatare condiționată are întotdeauna prioritate și va fi aplicată în locul oricăror opțiuni de formatare adăugate manual.
