Formatare condiționată în Excel: Instrucțiuni și utilizare

Utilizați aceste instrucțiuni pentru a crea o varietate de formate

Formatarea condiționată în Microsoft Excel este un instrument util pentru a face tabelele, rapoartele sau celulele mai proeminente, pentru a evidenția o zonă sau mai multe coloane, pentru a analiza date, pentru a clarifica diferențele sau pentru a identifica tendințele. Pentru aceasta sunt folosite bare de date, seturi de simboluri sau cântare colorate. În funcție de condițiile specificate, afișarea celulelor este modificată sau gama de celule este formatată. Vă puteți crea propriile dvs. pentru numeroasele condiții integrate.

Formatarea condiționată are sens și vă ajută

Formatarea condiționată în Excel poate fi aplicată în mai multe moduri, fie că este vorba de un tabel Excel, un anumit interval de celule sau un raport de tabel pivot. Vă sunt disponibile diverse acțiuni și șabloane de formatare. Avantajul decisiv pentru utilizatorii Excel este că valorile speciale sau rezultatele pot fi evidențiate prin codarea culorilor. Dacă se modifică o valoare, formatarea este ajustată automat - în funcție de condiții.

info: Tabelul pivot („Pivot Table”) este un tabel special cu ajutorul căruia se poate calcula, rezuma și analiza o cantitate mare de date. În plus, puteți face rapid comparații și identifica tendințe în datele examinării.

Formatare condiționată cu reguli existente pentru numere, conținut text sau date

Dacă formatați cu un operator de comparație, celulele pot fi găsite mai ușor. Puteți face valorile mai vizibile, de exemplu mai mari sau mai mici decât o valoare definită sau anumite valori care se află între o limită superioară și inferioară. Aceasta înseamnă că datele dorite sau căutate sunt afișate în culori. Formatarea rapidă sau formatarea avansată sunt disponibile pentru editare. Cu formatarea extinsă puteți lucra cu formule, de exemplu pentru a marca automat o coloană Excel în culoare.

Exemple:

• Un funcționar de depozit ar dori să evidențieze toate stocurile sub 15 bucăți cu un fundal colorat.
• Toate orașele cu peste 150.000 de locuitori trebuie marcate într-un tabel.
• O companie producătoare dorește să evidențieze în roșu toate bunurile fabricate în perioada 1 ianuarie 2022-2023 și 31 ianuarie 2022-2023.

Formatare rapidă:

Selectarea uneia sau mai multor celule.

Faceți clic pe „Start” din filă.

Accesați săgeata de lângă butonul „Formatare condiționată” din „Stil”.

Faceți clic pe „Reguli pentru evidențierea celulelor”.

Selectați comanda dorită, de ex. B. mai mare decât, mai puțin decât, între, dată etc.

Introduceți valori și selectați formatul.

Formatare extinsă:

1. Selectați una sau mai multe celule dintr-un tabel sau din raportul PivotTable.

2. Accesați fila „Acasă”, în grupul Stiluri, faceți clic pe săgeata de lângă „Formatare condiționată”.

3. Faceți clic pe „Gestionați regulile”: apare caseta de dialog „Manager pentru reguli de formatare condiționată”.

4. Selectați acțiunea:

   • Faceți clic pe „Regula nouă”: Apare caseta de dialog „Regula de formatare nouă”.
   • Pentru a schimba un format, asigurați-vă că este selectată foaia de lucru sau tabelul corect în caseta de listă „Afișați regula de formatare pentru”.
   • În câmpul „Se aplică la”, accesați „Reduceți caseta de dialog”; apoi selectați noua gamă de celule din foaia de lucru.
   • Faceți clic pe butonul „Extindeți caseta de dialog”.
   • Selectați regula și faceți clic pe „Editați regula”: apare caseta de dialog „Editați regula de formatare”.

5. Pentru a schimba intervalul pentru câmpurile din zona de valori „Aplicați regula la” a unui raport din tabelul pivot, procedați în felul următor:

   • Selecţie: Faceți clic pe „Doar aceste celule”
   • Câmp corespondent: Faceți clic pe „Toate celulele cu aceleași câmpuri”
   • Câmp valoric: Faceți clic pe „Toate celulele”

6. Sub „Selectați tipul de regulă” faceți clic pe „Formatează doar celulele care conțin”

Acum puteți efectua acțiuni în „Editați descrierea regulii” în câmpul de listă „Formatați celulele numai cu”:

Formatează după număr, dată sau oră:

Pentru aceasta, selectați o valoare a celulei și un operator de comparație și introduceți numărul, data sau ora. exemplu: Pentru „Între”, introduceți 300 și 500 sau selectați „Egal cu” și introduceți o dată.

Puteți utiliza o formulă pentru a obține un număr, o dată sau o valoare înapoi.

Formatează după text:

Selectați „Text specific” și „Operator relațional”.

Introduceți text, de ex. B. pe "conține" cuvântul "aur" sau pe "începe cu" un prefix pe care doriți să îl căutați.

Se pot introduce maximum 255 de caractere.

Valorile pot fi returnate folosind formule. Pentru a face acest lucru, formula trebuie să înceapă cu (=). Formulele nevalide nu sunt formatate. Este recomandabil să testați mai întâi formula pentru erori.

Formatează după dată:

• Selectați data și data comparației, de ex. B. Ieri, săptămâna viitoare sau luna aceasta

Apoi: Pentru a specifica un format, faceți clic pe „Formatare”, apare caseta de dialog „Formatare celule”. Selectați formatul numeric dorit, formatul de completare, cadrul sau fontul, faceți clic pe „OK”.

Este posibil să alegeți mai multe formate. Puteți vedea ce formate au fost selectate în câmpul „Previzualizare”.

Formatare condiționată cu baze de date, scheme de culori și seturi de simboluri

Conținutul și valorile din celule pot fi actualizate vizual cu baze de date, scale de culori sau seturi de simboluri și pot fi făcute mai atractive și mai clare. Dacă valorile dintr-un interval de celule trebuie comparate între ele, acest lucru este mai ușor cu această formatare condiționată.

Formatați celulele cu bare de date

Barele de date facilitează compararea celulelor între ele. Valoarea din celulă este indicată de lungimea barei. Cu cât valoarea este mai mare, cu atât bara de date este mai lungă, cu atât valoarea este mai mică, cu atât bara este mai scurtă. Aceasta permite determinarea rapidă a valorilor mari sau mici.

Formatare rapidă:

Marcați zona dorită (celulă, tabel sau foaie întreagă).

În fila Acasă, faceți clic pe butonul Formatare condiționată.

Accesați „bara de date” și selectați o completare.

Formatați celulele cu rame colorate

Scalele de culoare ajută la clasificarea mai ușoară a diferitelor informații de date și la posibilitatea de a compara rapid conținutul lor unul cu celălalt. În funcție de nuanța culorii, sunt afișate valori mari sau mici. Puteți formata celule cu o scară de 2 sau 3 culori.

Scală cu 2 culori

Formatare rapidă:

Selectați celula, tabelul sau întreaga foaie.

În fila Acasă, faceți clic pe Formatare condiționată.

Mergeți la „scala de culori” și faceți clic pe formatul de scară cu 2 culori dorit. Culoarea superioară reprezintă valori mai mari, culoarea inferioară pentru valori mai mici.

Scală cu 3 culori

A treia culoare permite vizualizarea valorilor medii - în plus față de reprezentarea culorilor a valorilor înalte și scăzute ca în scala cu 2 culori.

Formatare rapidă:

Selectați celula, tabelul sau întreaga foaie.

În fila Acasă, faceți clic pe Formatare condiționată.

Mergeți la „scala de culori” și faceți clic pe formatul de scară cu 3 culori dorit. Culoarea superioară reprezintă valori mai mari, culoarea inferioară pentru valori mai mici.

Formatează celulele cu seturi de simboluri

Cu un set de simboluri, datele pot fi vizualizate și clasificate în categorii separate. Un simbol reprezintă o anumită gamă de valori. Formatarea condiționată în Excel oferă simboluri colorate diferite: săgeți, puncte, cruci, semne de exclamare și multe altele.

Formatare rapidă:

Selectați celula, tabelul sau întreaga foaie.

În fila Acasă, faceți clic pe Formatare condiționată.

Mergeți la „Seturi de simboluri” și selectați setul de simboluri dorit

Creați singuri reguli de formatare

Dacă nu găsiți o opțiune adecvată în formatarea condiționată oferită de Excel, puteți crea cu ușurință reguli individuale pentru formatare.

Marcați gama de celule care trebuie formatate condiționat.

Accesați „Formatare condiționată” din fila „Acasă”.

Selectați „Regulă nouă” și confirmați cu „OK”.

Dacă nu găsiți opțiunea de care aveți nevoie pentru propria regulă de formatare, o formulă logică vă poate ajuta. De exemplu, puteți utiliza o formulă pentru a colora automat celulele Excel imediat ce conținutul este disponibil. În schimb, celulele care sunt goale pot fi formatate condiționat.

Exemplu: Aplicarea unei formule vă permite să comparați datele în afara intervalului selectat. Aceasta include chiar și date care se află pe o foaie de lucru diferită în același registru de lucru. Sau puteți compara valorile cu un rezultat returnat de o funcție. Formula trebuie să fie logică și să returneze „adevărat” sau „fals”.

Prin evidențierea celulelor direct pe foaia de lucru, puteți introduce referințe de celule într-o formulă. Acestea sunt referințe absolute de celule. Cu toate acestea, Excel poate ajusta și referințele celulei pentru fiecare celulă din zona selectată. Dacă doriți, selectați referințe de celulă relative.

Instrucțiuni pas cu pas pentru crearea unei reguli

1. În fila „Acasă”, grupul „Stiluri”, accesați săgeata de lângă „Formatare condiționată” și faceți clic pe „Gestionați regulile”. Apare caseta de dialog Manager reguli de formatare condiționată.

2. Sunt posibile următoarele acțiuni:

   • Dacă doriți să adăugați un format condițional, accesați „Reglă nouă”.
   • Schimbați un format condițional:
   • • Verificați dacă vă aflați pe foaia de lucru corectă.
     • Mai întâi, în Se aplică la, faceți clic pe Reduceți caseta de dialog. Apoi selectați noua gamă de celule și accesați „Extindeți caseta de dialog”.
     • Selectați regula dorită, faceți clic pe „Editați regula”. Apare „Editați regula de formatare”.

3. Sub „Selectați tipul de regulă” faceți clic pe „Folosiți formula pentru a determina celulele care trebuie formatate”.

   • Sub „Editați descrierea regulii” introduceți o formulă în „Valorile formatului pentru care această regulă este adevărată”. Formula începe cu (=) și trebuie să returneze „adevărat” (1) sau „fals” (0).
   • Faceți clic pe Format pentru a afișa caseta de dialog Formatare celule.
   • Acum selectați numărul, fontul, formatul de completare etc. care trebuie utilizat dacă valoarea celulei este adevărată și confirmați cu „OK”. Formatele selectate sunt afișate în „Previzualizare”.

De exemplu, formula COUNTIF returnează valori logice. Dacă se folosește această formulă, pot fi determinate valorile duplicate sau conținutul care apare într-o anumită frecvență poate fi evidențiat în culori. În acest fel, elementele unice pot fi, de asemenea, găsite și evidențiate în culori. Alte formule care pot fi utilizate pentru formatarea condiționată sunt

• REST: deci culorile alternate ale liniilor pot fi setate automat
• IDENTIC: Cu această formulă, datele pe care le căutați pot fi marcate într-un tabel Excel care corespunde unei valori specificate
• ISERROR: marchează automat celulele goale din tabelul Excel sau face ca valorile erorilor să dispară din registrul de lucru
• NOT și ISNUMBER: Formatează toate celulele Excel care nu conțin numere
• MAX: cea mai mare valoare a celulei este evidențiată în tabel
• MIN: permite formatarea condiționată a celei mai mici valori din lista Excel, astfel încât minimul să poată fi văzut dintr-o privire
• STÂNGA: în funcție de un șir de caractere la început, celulele Excel sunt formatate automat și au o culoare uniformă a textului
• MARE și STÂNGA: permite colorarea unei celule cu conținut nou
• DREAPTA: Celulele pot fi formatate pe baza ultimului caracter al șirului
• AZI: Cu aceasta, întâlnirile pot fi formatate în culori folosind metoda semaforului sau pot fi evidențiate valorile datei care vor fi în curând actualizate
• LUNĂ sau AN: vă permite să împărțiți lista Excel în luni sau ani; Cu YEAR, valorile de date ale anului curent pot fi, de asemenea, marcate și valorile anuale pot fi evidențiate
• SAU și SĂPTĂMÂNĂ: evidențiază valorile în funcție de ziua săptămânii în culori

Exemplu de creare a unei reguli pentru verificările AND și OR:

Pentru acest exemplu, două valori sunt utilizate pentru formatarea condiționată. Dacă prima regulă nu se aplică, a doua regulă se aplică automat.

Prima regulă: Un cumpărător de locuințe dorește să verifice dacă achiziția de proprietate planificată este în conformitate cu bugetul său. El poate aduce până la 80.000 de euro din fondurile sale proprii ca avans și poate furniza o rată lunară de împrumut de maximum 1.200 de euro. Dacă atât avansul, cât și rata împrumutului îndeplinesc aceste cerințe, celulele corespunzătoare sunt marcate cu verde.

A doua regulă: Dacă plata în avans sau rata nu îndeplinesc cerințele, celulele corespunzătoare sunt marcate cu roșu.

Formula pentru a crea prima regulă folosind culoarea verde este următoarea:

= ȘI (IF ($ B $ 4 <= 80000,1); IF (ABS ($ B $ 5) <= 1200,1))

Formula pentru a crea a doua regulă folosind culoarea roșie este următoarea:

= SAU (IF ($ B $ 4 = 1200,1))

Transferați și ștergeți formatarea condiționată

Formatarea condiționată poate fi copiată și aplicată altor date noi sau noi din foaia de lucru sau ștearsă din nou.

Transferați formatarea condiționată

Pentru a copia formatarea condiționată și a o lipi în alte celule sau intervale de celule, utilizați butonul „Transfer Format”.

Selectați celulele cu formatarea condițională pe care doriți să le copiați și lipiți în alte celule și faceți clic pe ele.

Acum mergeți la „Start” și apoi „Transfer Format”. Cursorul apare ca o pensulă.

Puteți adăuga formatarea condiționată la o celulă sau la o gamă de celule mutând peria peste celulă.

Cu ESC încetezi utilizarea pensulei.

Puteți utiliza în continuare pensula pentru a insera formatarea condiționată făcând dublu clic pe „Format de transfer”.

Alternativ, formatarea condiționată poate fi copiată utilizând comenzile rapide de la tastatură și lipită într-o altă celulă Excel.

Ștergeți formatarea condiționată

Se face distincția între un proces de ștergere pe o foaie de lucru și un proces de ștergere într-o gamă de celule.

Ștergeți pe o foaie de lucru sau în întreaga foaie de lucru

Accesați „Formatare condiționată” din fila „Acasă”.

Apoi faceți clic pe „șterge reguli”

Apoi faceți clic pe „Ștergeți regulile din întreaga foaie”.

Alternativ, puteți șterge formatarea condițională utilizând caseta de dialog „Selectare conținut”.

Ștergeți în intervalul de celule

Evidențiați celulele care conțin formatarea condițională dorită.

Faceți clic pe butonul „Lupă de analiză de lectură” (butonul este situat în partea dreaptă jos lângă datele selectate).

Accesați „Ștergeți formatul”.

Găsiți și ștergeți aceleași formate condiționale în toată foaia de lucru

Faceți clic pe celula cu formatul condițional adecvat.

În fila „Acasă”, faceți clic pe săgeata de lângă „Găsiți și selectați”, apoi faceți clic pe „Selectați conținutul”.

Faceți clic pe „Formate condiționate”.

Sub „Verificarea datelor”, accesați „Același lucru” (aceasta va selecta toate celulele cu aceeași formatare condiționată).

În fila „Start”, accesați „Formatare condiționată”, apoi „Ștergeți regulile” și „Ștergeți regulile în celulele selectate”.

Formatare condiționată în raportul PivotTable

Cu un raport PivotTable, utilizatorii pot rezuma rapid cantități mari de date. Gama de celule, foaia de calcul Excel sau raportul PivotTable - formatarea condiționată funcționează practic la fel peste tot. Numai în raportul PivotTable există câteva caracteristici speciale de care ar trebui să fiți conștienți:

• Unele formate condiționate nu funcționează pe câmpuri din intervalul de valori al unui raport din tabelul pivot.
• Dacă modificați aspectul raportului Tabelului pivot în orice mod, formatarea condițională va persista până când câmpurile din datele subiacente vor fi eliminate.
• Formatarea condiționată pentru câmpurile din gama de valori se poate baza pe ierarhia datelor. Intervalul acestei formatări condiționate este determinat de următorul nivel inferior sau următor superior al ierarhiei în rânduri pentru una sau mai multe coloane sau în coloane pentru unul sau mai multe rânduri.
• Gama de formatare condițională pentru câmpurile din intervalul de valori poate fi definită folosind trei metode:

• • selecţie
  • Câmp corespunzător
  • Câmp valoric

Metoda implicită este selecția.

Definirea zonei prin selecție

Accesați „Aplicați regula de formatare la”.

Faceți clic fie pe caseta de dialog Regula de formatare nouă, fie pe caseta de dialog Editare regulă de formatare.

Această metodă este potrivită pentru o serie contiguă de câmpuri din domeniul valorii, de ex. B. date de sondaj pentru o regiune, precum și pentru o serie necontiguă de câmpuri din intervalul valoric, cum ar fi date de sondaj pentru diferite regiuni la diferite niveluri ale ierarhiei datelor.

Definirea zonei printr-un câmp corespunzător

Această metodă funcționează dacă aveți

• doriți să evitați să selectați multe elemente care nu au legătură
• doriți să formatați condiționat mai multe câmpuri din intervalul de valori pentru un nivel de ierarhizare a datelor
• Doriți să excludeți rezultate parțiale

Definirea zonei printr-un câmp valoric

Această metodă funcționează dacă aveți

• doriți să evitați să selectați multe elemente care nu au legătură
• doriți să formatați condiționat mai multe câmpuri din intervalul de valori pentru toate nivelurile de ierarhie
• Doriți să includeți totaluri și rezultate parțiale.

Concluzie: formatarea condiționată în Excel facilitează urmărirea datelor

Dacă doriți să evidențiați în culori anumite valori ale datelor din tabelul Excel, formatarea condiționată în Excel este ideală. Aveți două opțiuni pentru aplicarea formatării condiționate. Fie utilizați regulile furnizate de Excel pentru a evidenția conținutul textului, valorile numerice sau datele în culori. Sau puteți defini personal o regulă cu diferite condiții în care trebuie marcate valorile relevante. Puteți compara datele cu ușurință și eficiență folosind bare de date, bare de culoare și seturi de simboluri. Dacă opțiunile de proiectare nu mai sunt necesare, le puteți elimina cu câteva clicuri.

Mai multe articole despre formatarea condiționată

• Cum se generează automat săgeți în listele Excel
• Afișați valori de timp negative în tabelele Excel
• Procentează negativ culoarea roșie
• Recunoașteți dintr-o privire procentele negative
• Formatați numerele cu un format personalizat color
• Formatați celule Excel care nu au valoarea specificată
• Stocați automat celulele cu formatele valutare corespunzătoare
• Marcați celulele în culori folosind o funcție macro
• Utilizați formatarea condiționată și evidențiați datele care sunt mai mari sau egale cu a treia celulă ca mărime
• Inversează valorile adevărului