Utilizarea formulelor pentru formatare condiționată în Excel

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.

Excel a avut un număr de opțiuni încorporate pentru condițiile utilizate în mod obișnuit, cum ar fi găsirea de numere mai mari sau mai mici decât o anumită valoare sau găsirea de numere care sunt superioare sau inferioare valorii medii.

Î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.

01 din 04

Aplicarea regulilor multiple în Excel

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.

Găsirea datelor care depășesc 25% și 50% crește

În următorul exemplu, vor fi aplicate două reguli personalizate de formatare condiționată pentru intervalul de celule B2 la B5.

• Prima regulă verifică dacă datele din celule A2 la A5 este mai mare decât valoarea corespunzătoare din B2 la B5 cu mai mult de 25%.
• A doua regulă verifică dacă aceleași date în A2: A5 depășește valoarea corespunzătoare în 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 a celulelor din interval B1: B4 se va schimba.

• Pentru datele în care diferența este mai mare de 25%, culoarea de fundal a celulei va deveni verde.
• Dacă diferența este mai mare de 50%, culoarea de fundal a celulei va deveni roșie.

Regulile utilizate pentru realizarea acestei sarcini vor fi introduse folosind formatul condițional Noua normă de formatare căsuță de dialog. Începeți prin introducerea datelor de probă în celulele A1 până la C5 așa cum se vede în imaginea de mai sus

În al treilea pas din tutorial va adăuga formule în celule C2: C4 care arată diferența procentuală exactă între valorile din celule A2: A5 și B2: B5 pentru a verifica exactitatea regulilor condiționate de formatare.

02 din 04

Setarea regulilor de formatare condiționate

Așa cum am menționat, regulile condiționate de formatare care verifică cele două condiții vor fi introduse folosind formatul condițional Noua normă de formatare căsuță de dialog. Să începem prin setarea formatării noastre personalizate condiționate:

Formarea condiționată pentru a găsi o creștere de peste 25%

= (A2-B2) / A2> 25%

1. A sublinia celule B2 la B5 în foaia de lucru.
2. Faceți clic pe Pagina principală din panglică.
3. Faceți clic pe Formatarea condițională pictograma în panglică pentru a deschide meniul derulant.
4. Alege Noua regulă pentru a deschide Noua normă de formatare căsuță de dialog.
5. De la Stil: , selectați Clasic opțiune.
6. În al doilea drop-down, faceți clic pe ultima opțiuneUtilizați o formulă pentru a determina ce celule să fie formatate.
7. Tastați formulă furnizate mai sus, în spațiul furnizat.
8. Faceți clic pe Formatați cu: drop-down apoi selectați Lumină roșie umple cu text roșu închis opțiune.
9. Clic O.K pentru a închide casetele de dialog și a reveni la foaia de lucru.
10. În acest moment, culoarea de fundal a celule B3 și B5 ar trebui să fie verde.

Formarea condiționată pentru a găsi o creștere mai mare de 50%

= (A2-B2) / A2> 50%

A sublinia celule B2 la B5 în foaia de lucru.

1. Faceți clic pe Pagina principală din panglică.
2. Faceți clic pe Formatarea condițională pictograma în panglică pentru a deschide meniul derulant.
3. Alege Noua regulă pentru a deschide Noua normă de formatare căsuță de dialog.
4. De la Stil: , selectați Clasic opțiune.
5. În al doilea drop-down, faceți clic pe ultima opțiuneUtilizați o formulă pentru a determina ce celule să fie formatate.
6. Tastați formulă furnizate mai sus, în spațiul furnizat.
7. Faceți clic pe Formatați cu: drop-down apoi selectați Lumină verde umplere cu text verde închis opțiune.
8. Clic O.K pentru a închide casetele de dialog și a reveni la foaia de lucru.

Culoarea de fundal a celulă B3 ar trebui să fie în continuare verde indicând faptul că diferența procentuală dintre numerele din celule A3 și B3 este mai mare de 25%, dar mai mică sau egală cu 50%. Culoarea de fundal a celulă B5 ar trebui să se schimbe pe roșu indicând faptul că diferența procentuală dintre numerele din celule A5 și B5 este mai mare de 50%.

03 din 04

Verificarea regulilor de formatare condiționată

Pentru a verifica dacă regulile condiționate de formatare introduse sunt corecte, putem introduce formule în celule C2: C5 care va calcula diferența procentuală exactă dintre numerele din intervale A2: A5 și B2: B5.

= (A2-B2) / A2

1. Click pe celula C2 pentru a deveni celula activă.
2. Tastați formulele de mai sus și apăsați pe introduce cheie de pe tastatură.
3. Răspunsul de 10% ar trebui să apară în celula C2, indicând faptul că numărul în celula A2 este cu 10% mai mare decât numărul din celula B2.
4. Este posibil să fie necesară modificarea formatării celula C2 pentru a afișa răspunsul ca procentaj.
5. Folosește umpleți mânerul pentru a copia formula de la celula C2 la celulele C3 la C5.
6. Răspunsurile pentru celulele C3 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 celule A3 și B3 este mai mare de 25% și diferența dintre celule A5 și B5 este mai mare de 50%.

Celula B4 nu a schimbat culoarea, deoarece diferența dintre celule A4 și B4 este egală cu 25%, iar regulamentul nostru de formatare condiționată specifică faptul că un procent mai mare de 25% este necesar pentru ca culoarea de fundal să se modifice în verde.

04 din 04

Ordonanța de precizie pentru formatul condiționat

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 în același timp un fundal roșu și verde, 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ă utilizată în acest tutorial 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 Sus si jos butoanele săgeată din caseta de dialog 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ă condiționată de formatare din exemplul nostru a format intervalul de celule B2: B5 cu o frontieră verde în locul unei culori de fundal verde, cele două reguli condiționale de formatare nu ar fi în conflict, deoarece ambele formate pot fi aplicate fără a interfera cu cealaltă.

Ca rezultat, celulă B5 ar avea atât o graniță verde, cât și o culoare de fundal roșu, deoarece diferența dintre numerele din celule 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ă a fost inițial aplicată o culoare de fundal galben celule B2 la B5 în exemplu, odată ce au fost adăugate regulile condiționate de formatare celule B2 și B4 ar rămâne galben. Deoarece regulile de formatare condiționată introduse se aplică celulelor B3 și B5, culorile lor de fundal s-ar schimba de la galben la verde și respectiv roșu.