Adăugarea formatării condiționate în Excel vă permite să aplicați diferite opțiuni de formatare pentru o celulă sau o gamă de celule care îndeplinesc condițiile specifice pe care le setați.
Opțiunile de formatare se aplică numai când celulele selectate îndeplinesc aceste condiții setate.
Opțiunile de formatare care pot fi aplicate includ modificări ale fontului și culorilor de fundal, stiluri de fonturi, limite de celule și adăugarea formatării numerelor la date.
Din Excel 2007, Excel a avut o serie de opțiuni încorporate pentru condiții obișnuite, cum ar fi găsirea de numere mai mari sau mai mici decât o anumită valoare sau găsirea de numere care sunt peste sau sub valoarea medie .
În plus față de aceste opțiuni prestabilite, este posibilă și crearea unor reguli personalizate de formatare condiționată utilizând formulele Excel pentru a testa condițiile specificate de utilizator.
Aplicarea regulilor multiple
Mai multe reguli pot fi aplicate acelorași date pentru a testa diferite condiții. De exemplu, datele despre buget pot avea seturi de condiții care aplică modificările de formatare atunci când sunt cheltuite anumite niveluri - cum ar fi 50%, 75% și 100% - din bugetul total.
În astfel de situații, Excel determină mai întâi dacă diferitele reguli se află în conflict și, dacă da, programul urmează o ordine de prioritate pentru a determina ce regulă condiționată de formatare este aplicată datelor.
Exemplu: Găsirea datelor care depășesc 25% și 50% crește cu formatul condiționat
În exemplul următor, două domenii personalizate de formatare condiționată vor fi aplicate domeniului B2 - B5.
- prima regulă verifică dacă datele din celulele A2 până la A5 sunt mai mari decât valoarea corespunzătoare din B2 până la B5 cu 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%.
Așa cum se poate vedea în imaginea de mai sus, dacă una dintre condițiile de mai sus este adevărată, culoarea de fond a celulei sau celulelor din intervalul B1: B4 se va schimba.
- pentru datele în care diferența este mai mare de 25%, culoarea de fundal a celulei se va schimba în albastru.
- 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,
= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%va fi introdus utilizând caseta de dialog Formatare condiționată nouă .
Introducerea datelor Tutorial
- Introduceți datele în celulele A1 - C5 așa cum se vede în imaginea de mai sus
Notă: Pasul 3 al tutorialului va adăuga formulare către celulele C2: C4 care arată diferența procentuală exactă dintre valorile din celulele A2: A5 și B2: B5 pentru a verifica exactitatea regulilor condiționate de formatare.
Stabilirea regulilor de formatare uniformă
Așa cum am menționat, regulile de formatare condiționată care verifică cele două condiții vor fi introduse utilizând caseta de dialog Formatare condițională nouă.
Setarea formatării condiționate pentru a găsi o creștere de peste 25%
- Evidențiați celulele B2 la B5 din foaia de lucru.
- Faceți clic pe fila Acasă a panglicii.
- Faceți clic pe pictograma Formatare condiționată din panglică pentru a deschide meniul derulant.
- Alegeți o nouă regulă pentru a deschide caseta de dialog New Format Rule, așa cum se vede în imaginea de mai sus.
- În jumătatea superioară a casetei de dialog, faceți clic pe ultima opțiune: Utilizați o formulă pentru a determina ce celule să fie formatate.
- În jumătatea de jos a casetei de dialog, faceți clic pe valorile Format unde această formulă este adevărată: linie.
- Introduceți formula : = (A2-B2) / A2> 25% în spațiul furnizat
- Faceți clic pe butonul Format pentru a deschide caseta de dialog Format celule.
- În această casetă de dialog, faceți clic pe fila Fill și alegeți o culoare albastră de umplere.
- Faceți dublu clic pe OK pentru a închide casetele de dialog și pentru a reveni la foaia de lucru.
- În acest moment, culoarea de fundal a celulelor B3 și B5 ar trebui să fie albastră.
Setarea formatării condiționate pentru a găsi o creștere de peste 50%
- Dacă celulele B2 până la B5 sunt încă selectate, repetați pașii de la 1 la 6 de mai sus.
- Introduceți formula: = (A2-B2) / A2> 50% în spațiul furnizat.
- Faceți clic pe butonul Format pentru a deschide caseta de dialog Format celule.
- Faceți clic pe fila Fill și alegeți o culoare de umplere roșie.
- Faceți dublu clic pe OK pentru a închide casetele de dialog și pentru a reveni la foaia de lucru .
- Culoarea de fundal a celulei B3 ar trebui să fie în continuare albastru, indicând faptul că diferența procentuală dintre numerele din celulele A3 și B3 este mai mare de 25%, dar mai mică sau egală cu 50%.
- Culoarea de fond a celulei B5 ar trebui să se schimbe pe roșu, indicând faptul că diferența procentuală dintre numerele din celulele A5 și B5 este mai mare de 50%.
Verificarea regulilor de formatare condiționată
Calculul diferenței%
Pentru a verifica dacă regulile de formatare condiționată introduse sunt corecte, putem introduce formule în celule C2: C5 care vor calcula diferența procentuală exactă dintre numerele din intervalele A2: A5 și B2: B5.
- Faceți clic pe celula C2 pentru a deveni celula activă.
- Introduceți formula = (A2-B2) / A2 și apăsați tasta Enter de pe tastatură.
- Răspunsul de 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 celula B2.
- Poate fi necesar să modificați formatul pe celula C2 pentru a afișa răspunsul ca procent.
- Utilizați mânerul de umplere pentru a copia formula din celula C2 în celule C3-C5.
- Răspunsurile pentru celulele C3 până la C5 ar trebui să fie: 30%, 25% și 60%.
- Răspunsurile din aceste celule arată că regulile condiționate de formatare create sunt corecte, deoarece diferența dintre celulele A3 și B3 este mai mare de 25%, iar diferența dintre celulele A5 și B5 este mai mare de 50%.
- Celula B4 nu a schimbat culoarea deoarece diferența dintre celulele A4 și B4 este egală cu 25%, iar regulamentul nostru de formatare condiționată specifică faptul că un procent mai mare de 25% a fost necesar pentru ca culoarea de fundal să se modifice în albastru.
Ordonanța de Precedență pentru Regulile de Formatare Condiționată
Aplicarea regulilor de formatare condiționată în conflict
Atunci când se aplică mai multe reguli pentru același interval de date, Excel determină mai întâi dacă conflictele sunt în vigoare.
Regulile conflictuale sunt cele în care opțiunile de formatare alese pentru fiecare regulă nu pot fi aplicate ambelor aceleași date .
În exemplul utilizat în acest tutorial, conflictul de reguli, deoarece ambele reguli utilizează aceeași opțiune de formatare - aceea de a schimba culoarea celulei de fundal.
În situația în care a doua regulă este adevărată (diferența de valoare este mai mare de 50% între două celule) atunci prima regulă (diferența de valoare fiind mai mare de 25%) este, de asemenea, adevărată.
Scrisoarea de Precedență a lui Excel
Din moment ce o celulă nu poate avea atât un fundal roșu, cât și un albastru în același timp, Excel trebuie să știe ce regulă condiționată de formatare ar trebui să se aplice.
Ce regulă se aplică este determinată de ordinea de prioritate a Excel, care precizează că există o regulă care este superioară în listă în caseta de dialog Condiționat pentru regulile de formatare a condițiilor.
După cum se arată în imaginea de mai sus, a doua regulă folosită în acest tutorial (= (A2-B2) / A2> 50%) este mai mare în listă și, prin urmare, are prioritate față de prima regulă.
Ca urmare, culoarea de fundal a celulei B5 este schimbată în roșu.
În mod implicit, noi reguli sunt adăugate în partea de sus a listei și, prin urmare, au o prioritate mai mare.
Pentru a modifica ordinea de prioritate, utilizați butoanele săgeți sus și jos din caseta de dialog așa cum este identificată în imaginea de mai sus.
Aplicarea regulilor nonconflict
Dacă două sau mai multe reguli de formatare condiționată nu intră în conflict, ambele sunt aplicate atunci când condiția pe care fiecare regulă o testează devine adevărată.
Dacă prima regulă de formatare condiționată din exemplul nostru (= A2-B2) / A2> 25%) a formatat intervalul celulelor B2: B5 cu o margine albastră în locul unei culori de fundal albastru, cele două reguli condiționale de formatare nu s-ar contrazice ambele formate pot fi aplicate fără a interfera cu cealaltă.
Ca rezultat, celula B5 ar avea atât o margine albastră, cât și o culoare de fundal roșu, deoarece diferența dintre numerele din celulele A5 și B5 este mai mare decât 25 și 50%.
Formatare condiționată vs. Formatare obișnuită
În cazul conflictelor dintre 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ărei opțiuni de formatare adăugate manual.
Dacă o culoare galbenă de fundal a fost aplicată inițial celulelor B2 - B5 din exemplu, odată cu adăugarea regulilor condiționate de formatare, numai celulele B2 și B4 ar rămâne galbene.
Deoarece regulile de formatare condiționată introduse se aplică celulelor B3 și B5, culorile lor de fundal s-ar schimba de la galben la albastru și, respectiv, roșu.