Acest tutorial va arăta cum să creați diagrame Excel cu formatare condiționată în toate versiunile de Excel: 2007, 2010, 2013, 2016 și 2022.
Formatarea condiționată este practica de atribuire a formatării personalizate la celule Excel, culoare, font etc., pe baza criteriilor (condițiilor) specificate. Funcția ajută la analiza datelor, la găsirea valorilor semnificative statistic și la identificarea modelelor dintr-un anumit set de date.
Dar, din păcate, Excel nu oferă instrumente încorporate pentru aplicarea formatării condiționate diagramelor Excel.
Cu toate acestea, cu o picătură de magie a foilor de calcul, nicio problemă nu este de netrecut. În acest tutorial, veți învăța cum să creați o diagramă dinamică cu formatare condițională în care punctele de date reprezentate sunt formatate automat pe baza valorilor lor reale respective.
De exemplu, aruncați o privire la acest grafic de coloane, descompunând performanțele unui manager de vânzări pe o perioadă de opt luni. Observați cum se ajustează automat culoarea coloanei în funcție de valoarea sa de bază:
Noțiuni de bază
Luați în considerare acest exemplu de set de date care conține date privind performanța vânzărilor:
Înainte de a începe, mutați setul de date, astfel încât să aveți trei rânduri goale deasupra tabelului cu datele brute. Rândurile vor fi utilizate pentru setarea regulilor de formatare condiționată.
Pasul 1: Pregătiți datele graficului.
Pentru a face lucrurile să funcționeze, trebuie să adăugați câteva date grafice suplimentare la ecuație. Pentru confortul dvs., aruncați o privire asupra modului în care ar trebui să arate datele grafice până la sfârșitul fazei de pregătire:
Primul nostru pas este definirea valorilor limită (condiții) care ar trebui să împartă valorile reale în mai multe categorii, făcând posibilă graficarea acestora ca serii de date separate.
De exemplu, din captura de ecran de mai sus, puteți vedea că cifrele vânzărilor pentru o anumită lună trebuie să se încadreze în intervalul 90.000 - 150.000 USD pentru a fi considerate satisfăcătoare.
Pentru a crea regulile, utilizați cele trei rânduri goale de deasupra setului de date:
- Rândul 1 (Etichetă): Utilizați aceste celule pentru etichetarea regulilor de formatare.
- Rândul 2 (valoare minimă): Valorile din acest rând definesc limita inferioară pentru fiecare condiție.
- Rândul 3 (valoare maximă): La fel, aceste valori stabilesc limitele superioare.
Cu această metodă, puteți crea câte reguli de formatare condiționată doriți - asigurați-vă că intervalele de valori nu se suprapun.
Pe măsură ce continuați să avansați în configurarea diagramei, proiectați elemente personalizate de legendă a diagramei care vor oferi context pentru diagramă. Introduceți această formulă în C4 și copiați-l în D4 prin tragerea mânerului de umplere:
| 1 | = C1 & ": de la" & TEXT (C2, "$ #, ##") & "la" & TEXT (C3, "$ #, ##") |
Formula pune laolaltă o etichetă dinamică cu aspect bine bazat pe regulile de formatare condiționate stabilite anterior. Funcția TEXT formatează valorile ca monedă. Dar dacă tipul dvs. de date diferă, aplicați în schimb această formulă:
| 1 | = C1 & ": de la" & TEXT (C2, "#, ##") & "la" & TEXT (C3, "#, ##") |
Sau acesta când lucrați cu procente:
| 1 | = C1 & ": de la" & TEXT (C2, "#%") & "la" & TEXT (C3, "#%") |
În cele din urmă, găsiți valorile graficului. Tastați următoarea formulă în C5, copiați-l în C12 și peste celulele potrivite din coloana D:
| 1 | = IF (ȘI (C $ 2 <$ B5, $ B5 <= C $ 3), $ B5, NA ()) |
Formula compară o valoare reală dată în coloana B împotriva valorilor limită specificate cu ajutorul funcțiilor IF și AND. Dacă valoarea se încadrează în interval, va fi copiată în această coloană. În caz contrar, formula returnează eroarea # N / A pentru a preveni reprezentarea aceleiași valori de mai multe ori.
Pasul 2: configurați o diagramă pe coloane.
După ce ați adunat toate datele grafice, configurați o diagramă simplă pe coloane sau o diagramă cu bare ca alternativă:
- Evidențiați toate datele graficului, cu excepția coloanelor care conțin valorile reale și regulile, ținând apăsat butonul Ctrl cheie (A4: A12 și C4: D12).
- Du-te la Introduce filă.
- Selectați "Introduceți o coloană sau o diagramă cu bare.”
- Alegeți „Coloană grupată / Bară grupată.”
Ca rezultat, veți ajunge cu un grafic cu puncte de date reprezentate pe baza regulilor de formatare condiționată - așa cum a promis:
Pasul 3: Modificați valorile Suprapunere și Lățime decalaj.
Înainte de a închide acest tutorial, remediați problema coloanelor deplasate cauzate de valorile goale invizibile (erorile # N / A) reprezentate pe diagramă.
Faceți clic dreapta pe oricare dintre coloane și alegeți „Formatează seria de date”Din meniul contextual care apare.
În panoul de activități, modificați poziția și lățimea coloanelor:
- Treceți la Opțiuni de serie filă.
- Schimbare "Seria se suprapune" la "100%.”
- Seteaza Lățimea golului la "60%.”
Pasul 4: Reglați schema de culori.
În cele din urmă, adăugați ultimele atingeri. Fără a închide Formatează seria de date panoul de sarcini, modificați schema de culori a graficului:
- Du-te la Fill & Line filă.
- Sub "Completati, ”Alege”Umplere solidă.”
- Apasă pe "Culoare umplere”Pictogramă și alegeți verde din paleta de culori.
Odată ajuns acolo, recolorați celelalte serii de date, schimbați titlul graficului, iar graficul dvs. complet dinamic cu formatare condiționată este gata de plecare!
