Evidențiați datele cheie cu formatarea condiționată Excel

Dacă intrați în orice birou, veți găsi probabil un utilizator Microsoft Excel. Este un instrument util de analiză a afacerilor, dacă este folosit în mod corespunzător. Problema cu multe foi de calcul este că toate celulele arată la fel. Trebuie să vânați pentru informații cheie. În acest tutorial, vă voi arăta cum să evidențiați celulele în Excel folosind mai multe metode. (Descărcați foaia de calcul de exemplu în secțiunea Resurse.)

Văd în mod obișnuit acest scenariu. Primiți o foaie de calcul de la cineva cu sute de rânduri de date care arată la fel. Totul este formatat în același mod plictisitor. Dar sunt datele la fel? Există valori ale celulelor care sunt diferite de restul? Este ceva în afara mediei? Acestea sunt întrebări tipice pe care utilizatorii le au atunci când deschid foile de calcul.

Formatarea celulelor Excel în funcție de condiții

În loc ca cititorul să scaneze fiecare celulă, puteți face ca software-ul să facă o muncă de teren folosind formatarea condiționată. Aceasta permite Excel să aplice un format definit celulelor care îndeplinesc anumite criterii specifice. Acestea sunt reguli pe care le puteți defini.

Obiectivul este de a face ca informațiile importante să iasă în evidență, astfel încât să le puteți găsi mai ușor.

Excel face deja o parte din acest lucru pentru dumneavoastră. De exemplu, atunci când formatați numere, există opțiuni pentru a afișa numerele negative cu roșu. Acesta este un exemplu de format predefinit.

Programul vă permite chiar să folosiți opțiuni de formatare pe celule sau rânduri individuale. Cea mai simplă metodă este ca Excel să aplice formatarea condiționată dacă îndeplinește anumite condiții sau locații. Aceasta folosește metoda „Cell Value Is” (Valoarea celulei este).

Câteva exemple de evidențiere a valorii celulei includ:

• Aplică o umplutură roșie dacă valoarea este mai mică de 50
• Aplică un stil de font italic, bold dacă valoarea este între 70 și 90
• Aplică o culoare de font verde dacă textul conține „Montana.”
• Subliniați valorile care sunt egale cu 15
• Aplicați o umplutură galbenă pentru valorile duplicate
• Adaugați o pictogramă cu o săgeată în sus la valorile din celule care depășesc 10%

Excel vă permite, de asemenea, să utilizați formule pentru formatarea condiționată. Un avantaj al formulelor Excel este că puteți face referire la valori în altă parte în foaia de calcul. În exemplul de mai jos, solicit ca, dacă valoarea din B2 este mai mare decât valoarea din C2, să se aplice o culoare de fundal verde.

Microsoft a îmbunătățit foarte mult această caracteristică de-a lungul anilor și acum predefine exemple populare, astfel încât nu trebuie să vă bazați atât de mult pe formule. Mai jos sunt câteva exemple populare.

Cum se aplică formatarea condiționată Excel

Adaugarea unei noi reguli de formatare este ușoară. Cea mai dificilă parte este să găsiți ceea ce doriți să subliniați pe foaia de calcul. Vă ajută să vă gândiți la nevoile publicului dumneavoastră și la acțiunile pe care le-ar putea întreprinde.

Exemplul 1: Evidențiați celulele de deasupra unui anumit număr

Dacă doriți să urmăriți, acesta este exemplul 1 din foaia de calcul de probă. Voi evidenția celulele selectate dacă populația statului depășește sau este egală cu 2% din populația SUA.

1. Deschideți foaia de calcul de probă state-counts-cf.xlsx și faceți clic pe fila Exemplu 1.
2. Click pe celula F2.
3. Selectați întreaga coloană apăsând Ctrl +Shift + ↓.
4. Din fila Home, faceți clic pe butonul Conditional Formatting.
5. Din meniul derulant, selectați Highlight Cell Rules.
6. Din meniul lateral, selectați Greater Than…

1. Ajustați valoarea din câmpul Format cells that are GREATER THAN:.

Nota: Excel va precompleta acest câmp pe baza valorilor existente în celulele evidențiate.

1. Introduceți valoarea dorită. În cazul nostru, este 2,0%. Excel va începe să evidențieze celulele pentru dumneavoastră.
2. Click pe triunghiul de la tastatură_frecvență_jos pentru a face selecția de culoare.

Nota: Puteți crea o schemă diferită prin selectarea unui format personalizat…

1. Click pe OK.

Ar trebui să vedeți acum rezultatele aplicate.

Exemplul 2: Evidențierea primelor 10 intrări

O solicitare populară este de a găsi cele mai importante intrări, fie că este vorba de un număr stabilit sau de un procent. Acesta este un exemplu al unuia dintre formatele predefinite sau încorporate de Excel. Cu alte cuvinte, nu este necesar să introduceți valori specifice, așa cum ați făcut în exemplul 1. Aceste presetări sunt construite în jurul unor elemente comune pe care oamenii le caută, cum ar fi Top 10 sau performanțe sub medie. Avantajul este că nu trebuie să faceți calculele.

În acest exemplu, vreau să evidențiez primele 10 state în funcție de numărul de locuri în Camera Reprezentanților din SUA în coloana D.

1. Deschideți state-counts-cf.xlsx sample spreadsheet și faceți clic pe fila Example 2.
2. Click pe celula D2.
3. Selectați întreaga coloană apăsând Ctrl +Shift + ↓.
4. Din fila Home, faceți clic pe butonul Conditional Formatting.
5. Din meniul derulant Conditional Formatting, selectați Top/Bottom Rules.
6. Din meniul lateral, selectați Top 10 Items…
7. În caseta de dialog Top 10 Items, ajustați numărătoarea sau schema de culori, dacă este necesar.
8. Clic pe OK.

Exemplul 3: Afișarea barelor de date în loc de umbrirea cu culori

O altă opțiune utilă este utilizarea „barelor de date”. Acesta este un alt tip de vizualizare pe care îl puteți aplica. O diferență esențială față de formatul „Data Bar” este că se afișează pe toate celulele în loc să îndeplinească o anumită condiție.

În mod implicit, Excel va păstra fundalul valorii textului. Consider că acest lucru poate fi deranjant. În acest exemplu, am duplicat coloana % Change, dar i-am spus lui Excel să nu pună valoarea datelor în coloana I. Rezultatul este doar bara de date.

Formul de bară de date nu funcționează bine cu toate tipurile de date. Acesta este un bun exemplu de a încerca diferite formate pentru a vedea ce funcționează cel mai bine pentru datele dumneavoastră. Acest lucru este valabil mai ales dacă aveți valori negative sau un interval restrâns.

1. Deschideți foaia de calcul de exemplu state-counts-cf.xlsx și faceți clic pe fila Example 3 (Exemplu 3).
2. Click pe celula I2.
3. Selectați întreaga coloană apăsând Ctrl +Shift + ↓.
4. Din fila Home, faceți clic pe butonul Conditional Formatting.
5. Din meniul derulant Conditional Formatting, selectați Data Bars.
6. Din meniul lateral, selectați More Rules…

1. În secțiunea Edit the Rule Description:, bifați Show Bar Only.

1. Click pe OK.

Exemplul 4: Evidențierea unui rând

Evidențierea celulelor aberante este grozavă, dar uneori, dacă aveți o foaie de calcul mare, este posibil să nu vedeți celulele colorate pentru că sunt în afara ecranului. În aceste situații, este util să evidențiați întregul rând. În acest fel, indiferent de coloanele pe care le vizualizați, știți ce rânduri sunt importante. Acest lucru funcționează cel mai bine atunci când aveți 1 condițional.

O diferență esențială aici este că nu vom evidenția o coloană, ci întreaga foaie de calcul. Acesta este modul în care obținem efectul de rând.

1. Deschideți foaia de calcul de exemplu state-counts-cf.xlsx și faceți clic pe fila Exemplu 4.
2. Click pe celula I2.
3. Selectați toate rândurile apăsând Ctrl +Shift + ↓ + →.
4. Din fila Home, faceți clic pe butonul Conditional Formatting.
5. Select New Rule…
6. În modulul Edit Formatting Rule, selectați Use a formula to determine which cells to format.
7. În câmpul Format values where this formula is true: introduceți criteriile dumneavoastră.

1. Click pe butonul Format… pentru a aplica culoarea de evidențiere.
2. Click pe OK pentru a accepta culoarea dumneavoastră.

În exemplul de mai sus, cer să evidențiez orice rând în care valoarea din coloana F (Procentul total al populației din SUA) este mai mare de 2%. Culoarea de evidențiere este verde. Folosesc, de asemenea, o referință de celulă mixtă prin plasarea unui semn $ înainte de F2 în formulă.

outlined_flag Sfat: Dacă nu sunteți sigur că aveți formula corectă, puteți oricând să o testați într-o altă coloană. De exemplu, aș putea introduce în celula H2, această formulă =$F2>20%. Celula H2 ar fi evaluată ca fiind fie TRUE, fie FALSE. Ați putea apoi să copiați formula în josul coloanei pentru a vă asigura că ați obținut rezultatele așteptate.

1. Click OK.

Găsirea regulilor dvs. de formatare condiționată

Câteodată ni se dă o foaie de calcul și vedem celule sau rânduri evidențiate. Dar… unde sunt regulile condiționale, sau cineva a formatat manual aceste celule? Din fericire, Excel oferă o modalitate de a vedea regulile de formatare condiționată. Puteți folosi foaia de calcul de exemplu pentru a urmări sau puteți folosi propria foaie de calcul.

1. Deschideți foaia de calcul.
2. Din fila Home, faceți clic pe Conditional Formatting.
3. Selectați Manage Rules… din meniul derulant.

1. În Conditional Formatting Rules Manager, selectați foaia dvs. de lucru.
2. Click OK.

În exemplul de mai sus, puteți vedea regula pe care am aplicat-o pentru a evidenția rândul. De aici, puteți adăuga o regulă, edita regula sau o puteți șterge.

Sfaturi & Trucuri

Acest articol a atins doar câteva dintre modurile în care puteți utiliza formatarea condiționată. Cel mai bun mod de a învăța este să experimentați cu niște date, fie că este vorba de cele proprii sau de foaia de calcul de exemplu. Iată alte câteva sfaturi pentru dumneavoastră:

• Puteți avea mai multe reguli în aceeași coloană. De exemplu, este posibil să doriți ca valorile diferitelor intervale să aibă culori diferite.
• Jocati-vă cu diferite formate pentru a vedea ce tip funcționează cel mai bine pentru datele dumneavoastră.
• Dacă obțineți rezultate neașteptate, verificați dacă se execută mai multe condiții și ordinea. Este posibil să fie nevoie să modificați ordinea.
• Puteți seta intervale pe barele de date. De exemplu, dacă aveți o coloană % Complet, este posibil să doriți să setați un interval de la 0 la 1.
• Puteți elimina regulile de formatare condiționată cu opțiunea Clear Rules.
• Aplicați regulile după ce ați introdus datele. Uneori se creează reguli duplicate atunci când inserați rânduri.

Formatul condițional al Excel poate face foile de calcul mai utile dacă sunt folosite în mod corespunzător. Oamenii nu vor mai pierde timpul căutând modificări importante sau acel „ac în carul cu fân”.”

Resurse suplimentare

• Ghid pentru începători pentru Excel XLOOKUP
• Cum se utilizează Excel VLOOKUP
• Cum se utilizează funcția IF în Excel
• Cum se creează un tabel pivot în Excel

Disclaimer: Imagini de la Amazon Product Advertising API. Este posibil să primesc un comision de afiliere pentru aceste produse dacă cumpărați. Actualizat: 2021-03-25