În acest tutorial voi prezenta formatarea condiționată în Microsoft Excel.
Formatarea Condiționată permite formatarea celulelor dintr-o zonă în funcție de anumite criterii definite de dumneavoastră. Se poate formata culoarea background-ului, a fontului etc.
Pentru a aplica formatarea condiționată unui interval de celule, după ce selectați intervalul respectiv, din fila Home – Conditional Formatting – se selectează o categorie și se completează regula pe care trebuie să o îndeplinească celulele ce urmează să fie formatate.
Aveți la dispoziție următoarele opțiuni:
- Highlight Cells Rules: puteți evidenția celulele care sunt mai mari decât o anumită valoare, între două valori, cele care conțin șiruri de text specifice, conțin o dată sau sunt duplicate.
- Top / Bottom Rules: puteți evidenția celulele care conțin primele sau ultimele 10 valori, celulele care conțin primele sau ultimele 10% valori precum și celulele ale căror valori sunt peste sau sub medie.
- Data Bars: inserează diagrame cu bară direct în celule, proporțional cu valoarea celulei.
- Color Scales: aplică culoarea de fundal, proporțională cu valoarea celulei.
- Icon Sets: afișează pictograme (care depind de valoarea celulei) direct în celule.
- New Rule: puteți să specificați alte reguli de formatare condiționată, inclusiv reguli bazate pe o formulă logică.
- Clear Rules: șterge toate regulile de formatare condiționată din celulele selectate.
- Manage Rules: afișează caseta de dialog Conditional Formatting Rules Manager în care puteți să creați noi reguli de formatare condiționată, puteți să editați reguli sau ștergeți reguli de formatare condiționată.
De exemplu, dacă folosiți datele din tabelul Vânzări și doriți să folosiți formatarea condiționată pentru a evidenția cu verde celulele care conțin luna Iunie din fila Home – Conditional Formatting – selectați categoria Highlight Cells Rules și din cadrul acesteia selectați Text That Contains.
Completați în fereastra Text That Contains ca în imaginea de mai jos.
Dacă, în plus, doriți să mai adăugați o regulă de formatare pentru aceeași coloană, de exemplu să evidențiați cu roșu celulele care conțin luna Aprilie, completați în fereastra Text That Contains ca în imaginea de mai jos.
Pentru un control maxim, puteți să vă creați propriile reguli de formatare condiționată.
Pentru a realiza acest lucru, din fila Home – Conditional Formatting – selectați New Rule.
În fereastra New Formatting Rule, din partea de sus selectați tipul de regulă generală și în funcție de regula selectată observați cum se modifică opțiunile din partea de jos a ferestrei.
Sunt disponibile următoarele opțiuni pentru regulile generale:
- Format all cells based on their values (Formatați toate celulele pe baza valorilor lor): utilizată pentru a crea reguli în care se afișează bare de date, scale de culori sau seturi de pictograme.
- Format only cells that contain (Formatați numai celulele care conțin): utilizată pentru a crea reguli care formatează celulele pe baza comparațiilor matematice, reguli bazate pe text, date, spații goale, non-goane și erori.
- Format only top or bottom ranked values (Formatați numai valorile clasate în partea de sus sau de jos): utilizată pentru a crea reguli care implică identificarea celulelor din top n, top n%, bottom n și bottom n%.
- Format only values that are above or below average (Formatați numai valori care sunt peste sau sub medie): utilizată pentru a crea reguli care identifică celulele care sunt peste medie, sub medie sau într-o abatere standard specificată față de medie.
- Format only unique or duplicate values (Formatați numai valori unice sau duplicate): utilizată pentru a crea reguli care formatează valori unice sau duplicate într-un interval.
- Use a formula to determine which cells to format (Utilizați o formulă pentru a determina ce celule să formatați): utilizată pentru a crea reguli bazate pe formule.
Dacă doriți o formatare condiționată de tip Data Bars în care fiecare celulă afișează o bară orizontală, a cărei lungime este proporțională cu valoarea sa, din fila Home – Conditional Formatting – selectați categoria Data Bars și din cadrul acesteia selectați paleta de culori de umplere dorită.
Un exemplu de utilizare este prezentat mai jos:
Dacă doriți o formatare condiționată de tip Color Scale în care culoarea de fundal variază în funcție de valoarea celulelor, din fila Home – Conditional Formatting – selectați categoria Color Scale și din cadrul acesteia selectați scala de culori dorită.
Un exemplu de utilizare este prezentat mai jos:
Dacă doriți o formatare condiționată de tip Icon Set în care, în funcție de valoarea celulei este afișat un mic grafic în celulă, din fila Home – Conditional Formatting – selectați categoria Icon Sets și din cadrul acesteia selectați categoria dorită.
Un exemplu de utilizare este prezentat mai jos:
Formatarea condiționată – sursă pentru filtre pe culoare
Formatele numerice controlează modul în care numerele sunt afișate în Excel. Principalul avantaj al formatelor numerice este că acestea modifică aspectul unui număr dar nu au niciun efect asupra valorii reale stocate de Excel.
Formatele de numere personalizate au o structură specifică.
Fiecare format numeric poate avea până la patru secțiuni, separate prin ; (punct și virgulă), care definesc formatele pentru numerele pozitive, pentru cele negative, pentru valorile zero și pentru text, în ordinea menționată.
<POZITIVE>;<NEGATIVE>;<ZERO>;<TEXT>Când citiți un format de număr personalizat, important este să observați punctele și virgulele și să analizați mental codul conform celor 4 secțiuni.
În formatul unui număr personalizat nu trebuie incluse toate secțiunile.
Dacă specificați o singură secțiune de cod, Excel va utiliza acel format pentru toate valorile. Dacă specificați un format de număr cu doar două secțiuni, prima secțiune este utilizată pentru numere pozitive și zerouri, iar a doua secțiune este utilizată pentru numere negative. Pentru a omite o secțiune, includeți punct și virgulă în locația corectă, dar nu specificați un cod de format.
Pentru fiecare secțiune (text sau număr) avem o serie de substituenți (caractere de înlocuire) cu următoarea semnificație:
| Cod pentru număr | Descriere |
| General | Număr, în formatul general. |
| 0 (zero) | Substituent pentru cifră. Acest cod introduce 0 în mod automat pentru a păstra dimensiunea codului (ex: codul “0000” afișează, când introducem 1, “0001”). |
| # | Substituent pentru cifră. Acest cod nu afișează zerouri suplimentare. |
| ? | Substituent pentru cifră. Acest cod păstrează un spațiu pentru zerourile suplimentare, dar nu le afișează. |
| . (punct zecimal) | Numere cu zecimale. |
| % | Procent. Microsoft Excel înmulțește valoarea introdusă cu 100 și adaugă semnul %. |
| , (virgula) | Separator pentru mii. |
| E+ E- e+ e- | Afișează un număr în format științific (exponențial). Excel afișează un număr la dreapta lui „E” sau „e” care corespunde numărului de zecimale cu care a fost mutată virgula zecimală. De exemplu, dacă formatul este 0,00E+00 și tastați 12.200.000 în celulă, se afișează numărul 1,22E+07. |
Pentru text avem următoarele caractere de înlocuire:
| Cod pentru text | Descriere |
| $ – + / ( ) : spațiu | Aceste caractere sunt afișate ca atare în număr. Pentru a afișa orice alt caracter, îl scrieți între ghilimele sau scrieți caracterul backslash (\) înaintea acestuia. |
| \caracter | Acest cod afișează caracterul specificat. |
| “text” | Acest cod afișează textul scris între ghilimele. |
| * | Acest cod repetă ultimul caracter până se umple celula. |
| _ (underscore) | Acest cod se foloseste, de obicei, în combinație cu paranteza rotundă închisa “)” (fără ghilimele) pentru a lăsa un spațiu după numărul pozitiv, corespunzător parantezei închise utilizate în formatul monetar, pentru a alinia cifrele. |
| @ | Substituent pentru text. |
Pentru formatul de tip dată:
| Cod pentru data | Descriere |
| m | Luna, cu un singur caracter pentru valorile între 1 și 9 (1-12) |
| mm | Luna, cu două caractere (01-12) |
| mmm | Denumirea lunii ca abreviere (de la ian la dec) |
| mmmm | Denumirea lunii ca nume complet (de la ianuarie la decembrie) |
| d | Ziua, cu o cifră pentru valorile 1-9 |
| dd | Ziua, cu două cifre (01-31) |
| ddd | Numele zilei ca abreviere (de la dum la sâm) |
| dddd | Numele zilei ca nume complet (de la duminică la sâmbătă) |
| yy | Anul, cu două cifre (de exemplu, 99) |
| yyyy | Anul cu 4 cifre (de exemplu, 1999) |
Pentru ora:
| Cod pentru ora | Descriere |
| h | Ora, scrisă ca număr cu o cifră pentru valorile de la 0 la 9 |
| hh | Ora cu două cifre (00-23) |
| m | Minutele, scrise ca număr cu o cifră pentru valorile de la 0 la 9 |
| mm | Minutele cu două cifre (00-59) |
| s | Secundele, scrise ca număr cu o cifră pentru valorile de la 0 la 9 |
| ss | Secundele cu două cifre (00-59) |
| AM/PM, am/pm, A/P, a/p | Afișează ora utilizând formatul de 12 ore. Excel afișează AM, am, A sau a pentru orele de la miezul nopții până la amiază și PM, pm, P sau p pentru orele de la amiază până la miezul nopții. |
Se poate aplica și o culoare sau o condiție:
| Alte coduri | Descriere |
| [BLACK], [BLUE], [CYAN], [GREEN], [MAGENTA], [RED], [WHITE], [YELLOW], [COLOR n] | Acest cod afișează numerele în culoarea specificată. Pentru ultimul cod, n reprezintă o valoare între 1și 56. |
| [Conditie valoare] | Conditie poate fi <, >, =, >=, <=, <> și valoare poate fi orice număr. |
Tabelul de culori:
Mai sus, am prezentat codurile cu ajutorul cărora putem personaliza afișarea conținutului unei celule. Iată și un exemplu pentru formatarea cu condiții.
Dacă ne dorim ca la introducerea unei valori, în loc de acea valoare să fie afișate „steluțe” albastre vom scrie codul:
[BLUE] [>=1000] "";[>=500]"";""Pe care îl interpretăm astfel:
Pentru valori mai mari sau egale cu 1000 afișează *** de culoare albastră, pentru valori aparținând intervalului [500,1000) afișează **, iar pentru cele mai mici decât 500 (sau pentru orice altceva) afișează *.
Format cells nu permite decât 2 secțiuni de condiții și o secțiune pentru cazul în care niciuna dintre cele 2 nu este îndeplinită, separate cu ; (punct și virgulă, indiferent de ce setări regionale avem).
Cum procedăm?
Selectăm zona căreia dorim să-i aplicăm acest format, facem click dreapta și alegem Format Cells și în fila Number alegem categoria Custom. Scriem codul de mai sus ca în imagine:
Putem folosi un format personalizat și pentru instrumentul Conditional Formatting, caz în care putem impune mai multe condiții decât la formatarea celulei cu Format Cells.
Să zicem că dorim ca pentru numerele mai mari decât 5000 să afișăm cinci stele, pentru cele între 4000 și 5000 să avem patru, între 3000 și 4000 trei, între 2000 și 3000 două și pentru cele mai mici de 2000 să avem o stea.
Vom aplica zonei alese, succesiv, aceste reguli de formatare astfel:
- Selectăm zona căreia dorim să-i aplicăm această regulă de formatare.
- Din fila Home acționăm instrumentul Conditional Formatting cu opțiunea New Rule.
Alegem categoria Use a formula to determine which cells to format, scriem condiția (formula) folosind adresa relativă a primei celule din zona selectată și acționăm butonul Format pentru a stabili formatul pe care îl va avea zona respectivă dacă este adevarată condiția.
Vom scrie întâi condiția pentru valori mai mici decât 2000 și continuam cu: >=2000, >=3000, >=4000 și >=5000 așa cum puteți vedea în fereastra Conditional Formatting Rules Manager (se deschide din Fila Home – grupul Styles – meniul Conditional Formatting – Manage Rules)
