Formatare condiționată în Excel – în detaliu cu exemple

Dacă aveți nevoie să prezentați vizual informații, atunci este foarte convenabil să faceți acest lucru folosind formatarea condiționată. Utilizatorul care folosește acest serviciu economisește o cantitate imensă de energie și timp. Pentru a obține informațiile necesare, este suficientă o vizualizare rapidă a fișierului.

Cum se face formatarea condiționată în Excel

Puteți găsi „Formatare condiționată” în prima filă a panglicii, accesând secțiunea „Stiluri”. 

Apoi, trebuie să găsiți pictograma săgeată puțin la dreapta cu ochiul, mutați cursorul pe ea. Apoi se vor deschide setările, unde puteți configura în mod flexibil toți parametrii necesari. 

Apoi, trebuie să selectați operatorul potrivit pentru a compara variabila dorită cu un număr. Există patru operatori de comparație – mai mare decât, mai mic decât, egal cu și între. Ele sunt listate în meniul regulilor. 

În continuare, am specificat o serie de numere în intervalul A1:A11.

Să formatăm un set de date adecvat. Apoi deschidem meniul de setări „Formatare condiționată” și setăm criteriile de selecție a datelor necesare. În cazul nostru, de exemplu, vom selecta criteriul „Mai multe”. 

Se va deschide o fereastră cu un set de opțiuni. În partea stângă a ferestrei există un câmp în care trebuie să specificați numărul 15. În partea dreaptă este indicată metoda de evidențiere a informațiilor, cu condiția ca aceasta să îndeplinească criteriul specificat anterior. Rezultatul va fi afișat imediat.

Apoi, finalizați configurația cu butonul OK.

Pentru ce este formatarea condiționată?

Într-o anumită măsură, putem spune că este un instrument, după ce învățăm care lucrul cu Excel nu va mai fi niciodată la fel. Acest lucru se datorează faptului că face viața mult mai ușoară. În loc să setați manual formatarea unei celule care se potrivește de fiecare dată cu o anumită condiție și, de asemenea, să încercați să o verificați singur pentru conformitatea cu acest criteriu, trebuie doar să setați setările o dată, apoi Excel va face totul singur.

De exemplu, puteți face ca toate celulele care conțin un număr mai mare de 100 să devină roșii. Sau, stabiliți câte zile au mai rămas până la următoarea plată și apoi colorați cu verde acele celule în care termenul limită încă nu este suficient de departe. 

Să luăm un exemplu.

Acest tabel arată stocul care este în stoc. Are coloane. ca produs, vânzări medii (măsurate în unități pe săptămână), stoc și câte săptămâni au rămas din acest produs.

În plus, sarcina managerului de achiziții este de a determina acele poziții, a căror completare este necesară. Pentru a face acest lucru, trebuie să vă uitați la a patra coloană din stânga, care înregistrează stocul de mărfuri pe săptămână.

Să presupunem că criteriul pentru determinarea unei cauze de panică este mai puțin de 3 săptămâni de inventar. Acest lucru indică faptul că trebuie să pregătim o comandă. Dacă stocul de mărfuri este mai mic de două săptămâni, atunci aceasta indică necesitatea de a plasa urgent o comandă. Dacă tabelul conține un număr mare de poziții, atunci verificarea manuală a câte săptămâni au mai rămas este destul de problematică. Chiar dacă folosești căutarea. Și acum să vedem cum arată tabelul, care evidențiază mărfurile rare în roșu.

Și într-adevăr, este mult mai ușor de navigat. 

Adevărat, acest exemplu este educațional, care este oarecum simplificat în comparație cu imaginea reală. Iar secundele și minutele salvate cu utilizarea regulată a unor astfel de tabele se transformă în ore. Acum este suficient să te uiți la tabel pentru a înțelege ce bunuri sunt în lipsă și să nu petreci ore întregi analizând fiecare celulă (dacă există mii de astfel de articole de bază).

Dacă există bunuri „galbene”, atunci trebuie să începeți să le cumpărați. Dacă poziția corespunzătoare este roșie, atunci trebuie să o faceți imediat. 

După valoarea unei alte celule

Acum să aruncăm o privire la următorul exemplu practic.

Să presupunem că avem un astfel de tabel și ne confruntăm cu sarcina de a evidenția rândurile care conțin anumite valori. 

Deci, dacă proiectul este încă în curs de execuție (adică nu a fost încă finalizat, prin urmare este marcat cu litera „P”), atunci trebuie să-i facem fundalul roșu. Proiectele finalizate sunt marcate cu verde.

Secvența noastră de acțiuni în această situație va fi următoarea:

1. Selectați un interval de valori. 
2. Faceți clic pe butonul „Formatare condiționată” – „Creare regulă”.
3. Vă rugăm să rețineți că, în cazul nostru, trebuie să aplicați formula sub forma unei reguli. În continuare, folosim funcția IFpentru a evidenția liniile care se potrivesc. 

Apoi, completați rândurile așa cum se arată în această imagine.

Important! Șirurile de caractere trebuie să fie referite în mod absolut. Dacă ne referim la o celulă, atunci în acest caz este amestecată (cu fixarea coloanei).

În mod similar, se creează o regulă pentru acele proiecte de lucru care nu au fost finalizate până în prezent. 

Așa arată criteriile noastre.

Și, în sfârșit, avem o astfel de bază de date ca rezultat.

Condiții multiple

Pentru claritate, să luăm intervalul A1:A11 din tabel pentru a demonstra cum, într-un exemplu practic real, puteți folosi mai multe condiții pentru a determina formatarea celulei.

Condițiile în sine sunt următoarele: dacă numărul din celulă depășește valoarea 6, atunci este evidențiat cu roșu. Dacă este verde, atunci culoarea este verde. Și, în sfârșit, cele mai mari numere, mai mult de 20, vor fi evidențiate cu galben. 

Există mai multe moduri în care se realizează formatarea condiționată în conformitate cu mai multe reguli.

1. Metoda 1. Selectăm intervalul nostru, după care, în meniul de setări de formatare condiționată, selectăm o astfel de regulă de selecție a celulelor ca „Mai multe”. Numărul 6 este scris în stânga, iar formatarea este setată în dreapta. În cazul nostru, umplutura ar trebui să fie roșie. După aceea, ciclul se repetă de două ori, dar alți parametri sunt deja setați - mai mult de 10 și culoarea verde și, respectiv, mai mult de 20 și, respectiv, culoarea galbenă. Veți obține un astfel de rezultat.
   

   

2. Metoda 2. Accesați meniul principal de setări al instrumentului Excel „Formatare condiționată”. Acolo găsim meniul „Creare Rule” și facem clic stânga pe acest articol.
   

   

    După aceea, Apoi, selectați elementul „Utilizați formula …” (evidențiat în figură cu un cadru roșu) și setați prima condiție. După aceea, faceți clic pe OK. Și apoi ciclul se repetă pentru condițiile ulterioare în același mod ca cel descris mai sus, dar se folosește formula.

   

Este important să țineți cont de acest lucru. În exemplul nostru, unele celule corespund mai multor criterii simultan. Excel rezolvă acest conflict după cum urmează: prima se aplică regula de mai sus. 

Și iată o captură de ecran pentru o mai bună înțelegere.

De exemplu, avem numărul 24. Îndeplinește toate cele trei condiții în același timp. În acest caz, va exista o umplere care îndeplinește prima condiție, în ciuda faptului că se încadrează mai mult sub a treia. Prin urmare, dacă este important ca numerele de peste 20 să fie umplute cu galben, a treia condiție trebuie pusă pe primul loc. 

Formatare condiționată a datei

Acum să vedem cum funcționează formatarea condiționată cu date. Avem o gamă atât de cool.

În acest caz, trebuie să setați o regulă de formatare condiționată, cum ar fi „Data”.

După aceea, se deschide o casetă de dialog cu un întreg set de condiții. Vă puteți familiariza cu ele în detaliu în această captură de ecran (sunt listate în partea stângă a ecranului sub forma unei liste).

Trebuie doar să alegem cel potrivit și să facem clic pe butonul „OK”.

Vedem că acele date care se referă la ultima săptămână în momentul în care a fost trasă acest interval au fost evidențiate cu roșu. 

Folosind formule

Setul de reguli standard de formatare condiționată este destul de mare. Dar situațiile sunt diferite, iar lista standard poate să nu fie suficientă. În acest caz, vă puteți crea propria regulă de formatare folosind o formulă. 

Pentru a face acest lucru, faceți clic pe butonul „Creați regulă” din meniul de formatare condiționată, apoi selectați elementul afișat în captură de ecran.

Rânduri (după valoarea celulei)

Să presupunem că trebuie să evidențiem rândul care conține o celulă cu o anumită valoare. În acest caz, trebuie să realizăm aceeași secvență de acțiuni ca mai sus, dar pentru șiruri. Adică, puteți alege în ce interval ar trebui să se aplice formatarea condiționată. 

Cum se creează o regulă

Pentru a crea o regulă, trebuie să utilizați opțiunea corespunzătoare din secțiunea „Formatare condiționată”. Să ne uităm la o anumită secvență de acțiuni. 

Mai întâi trebuie să găsiți elementul „Formatare condiționată” pe panglică din fila „Acasă”. Poate fi recunoscut după celulele caracteristice multicolore de diferite dimensiuni, cu o imagine lângă ea cu un semn egal barat. 

Există un buton „Creați regulă”. De asemenea, puteți face clic pe butonul „Gestionați regulile”, după care se va deschide o casetă de dialog, prin care puteți crea și o regulă făcând clic pe butonul corespunzător din colțul din stânga sus. 

De asemenea, puteți vedea în această fereastră acele reguli care au fost deja aplicate.

Reguli de selecție a celulelor

Bine, vorbim atât de mult despre cum să creăm reguli, dar ce este? Regula de selecție a celulelor se referă la caracteristicile care sunt luate în considerare de program pentru a decide cum să formateze celulele care le corespund. De exemplu, condițiile pot fi mai mari decât un anumit număr, mai mici decât un anumit număr, o formulă și așa mai departe. Există un întreg set de ele. Puteți să vă familiarizați cu ele și să exersați utilizarea lor „în cutia de nisip”. 

Formatarea tuturor celulelor pe baza valorilor lor

O persoană poate seta în mod independent intervalul: o linie întreagă, întregul document sau o singură celulă. Programul se poate concentra doar pe valoarea unei celule. Acest lucru adaugă flexibilitate procesului. 

Formatați numai celule unice sau duplicate

Există un număr mare de reguli conectate direct în program. Vă puteți familiariza cu ele în meniul „Formatare condiționată”. În special, pentru ca formatarea să se aplice exclusiv valorilor duplicate sau numai celor unice, există o opțiune specială - „Valori duplicate”.

Dacă selectați acest element, se deschide o fereastră cu setări, unde puteți alege și opțiunea opusă – selectați doar valori unice. 

Formatați valori în intervalul sub și peste medie

Pentru a formata valori sub sau peste medie, există și o opțiune specială în același meniu. Dar trebuie să selectați un alt submeniu – „Reguli pentru selectarea primei și ultimelor valori”. 

Formatarea numai a primei și ultimelor valori

În același submeniu, este posibil să evidențiați doar prima și ultima valoare cu o culoare specială, font și alte metode de formatare. Conform standardului, există o opțiune de selectare a primelor și ultimelor zece elemente, precum și a 10% din numărul total de celule incluse în acest interval. Dar utilizatorul poate determina independent câte celule trebuie să selecteze.

Formatarea numai a celulelor cu conținut specific

Pentru a formata numai celule cu un anumit conținut, trebuie să selectați regula de formatare Egal cu sau Conține text. Diferența dintre ele este că, în primul caz, șirul trebuie să se potrivească complet cu criteriul, iar în al doilea, doar parțial. 

După cum puteți vedea, formatarea condiționată este o caracteristică multifuncțională a programului Excel care oferă un număr imens de avantaje persoanei care a stăpânit-o. Știm că, la prima vedere, toate acestea pot părea complicate. Dar, de fapt, mâinile sunt atrase de el de îndată ce apare să evidențieze o anumită bucată de text în roșu (sau o formatează în orice alt mod), pe baza conținutului său sau în conformitate cu un alt criteriu. Această funcție este inclusă în setul de bază de cunoștințe Excel, fără ea, chiar și lucrul amator cu foi de calcul și baze de date este imposibil.