Ta vadnica bo pokazala, kako ustvariti Excelove grafikone s pogojnim oblikovanjem v vseh različicah Excela: 2007, 2010, 2013, 2016 in 2022.
Pogojno oblikovanje je praksa dodeljevanja oblikovanja po meri za celice programa Excel-barvo, pisavo itd. Na podlagi določenih meril (pogojev). Funkcija pomaga pri analizi podatkov, iskanju statistično pomembnih vrednosti in prepoznavanju vzorcev v danem naboru podatkov.
Na žalost Excel ne ponuja vgrajenih orodij za uporabo pogojnega oblikovanja na Excelovih grafikonih.
Vendar s kapljico čarovnije preglednice noben problem ni nepremostljiv. V tej vadnici se boste naučili ustvariti dinamični grafikon s pogojnim oblikovanjem, kjer se narisane podatkovne točke samodejno oblikujejo glede na njihove dejanske vrednosti.
Za primer si oglejte ta stolpčni grafikon, ki razčlenjuje uspešnost vodje prodaje v osmih mesecih. Upoštevajte, kako se barva stolpca samodejno prilagodi glede na njegovo osnovno vrednost:
Začetek
Razmislite o tem vzorčnem naboru podatkov, ki vsebuje podatke o prodajni uspešnosti:
Preden začnemo, premaknite nabor podatkov, tako da imate nad tabelo tri prazne vrstice s surovimi podatki. Vrstice bodo uporabljene za nastavitev pravil pogojnega oblikovanja.
1. korak: Pripravite podatke grafikona.
Če želite, da stvari delujejo, morate enačbi dodati nekaj dodatnih podatkov iz grafikona. Za vaše udobje si oglejte, kako naj bi bili videti vaši grafikoni do konca pripravljalne faze:
Naš prvi korak je opredelitev mejnih vrednosti (pogojev), ki bi morale dejanske vrednosti razdeliti v več kategorij, kar jim omogoča, da jih razvrstimo kot ločene serije podatkov.
Na zgornjem posnetku zaslona lahko na primer vidite, da morajo biti prodajne vrednosti za določen mesec v razponu od 90.000 do 150.000 USD, da bi bile zadovoljive.
Če želite ustvariti pravila, uporabite tri prazne vrstice nad nizom podatkov:
- Vrstica 1 (oznaka): S temi celicami označite pravila oblikovanja.
- Vrstica 2 (minimalna vrednost): Vrednosti v tej vrstici določajo spodnjo mejo za vsak pogoj.
- Vrstica 3 (največja vrednost): Prav tako te vrednosti postavljajo zgornje meje.
S to metodo lahko ustvarite poljubno število pravil pogojnega oblikovanja-samo pazite, da se obsegi vrednosti ne prekrivajo.
Ko nenehno napredujete pri nastavljanju grafikona, oblikujte legende grafičnih elementov po meri, ki bodo zagotovili kontekst za grafikon. Vnesite to formulo C4 in ga prekopirajte v D4 tako, da povlečete ročico za polnjenje:
| 1 | = C1 & ": from" & TEXT (C2, "$#, ##") & "to" & TEXT (C3, "$#, ##") |
Formula združuje lepo videti dinamično oznako, ki temelji na predhodno določenih pravilih pogojnega oblikovanja. Funkcija TEXT oblikuje vrednosti kot valuto. Če pa se vaš podatkovni tip razlikuje, uporabite to formulo:
| 1 | = C1 & ": from" & TEXT (C2, "#, ##") & "to" & TEXT (C3, "#, ##") |
Ali pa tole, ko delate z odstotki:
| 1 | = C1 & ": from" & TEXT (C2, "#%") & "to" & TEXT (C3, "#%") |
Na koncu poiščite vrednosti grafikona. Vnesite naslednjo formulo C5, kopirajte do C12 in naprej do ujemajočih se celic v stolpec D:
| 1 | = IF (AND (C $ 2 <$ B5, $ B5 <= C $ 3), $ B5, NA ()) |
Formula primerja dano dejansko vrednost v stolpec B proti določenim mejnim vrednostim s pomočjo funkcij IF in AND. Če je vrednost v razponu, se kopira v ta stolpec. V nasprotnem primeru formula vrne napako #N/A, da se prepreči večkratno izris iste vrednosti.
2. korak: Nastavite stolpčni grafikon.
Ko zberete vse podatke v grafikonu, nastavite preprost stolpčni grafikon ali pa stolpčni grafikon kot alternativo:
- Označite vse podatke grafikona, razen stolpcev, ki vsebujejo dejanske vrednosti in pravila, tako da držite tipko Ctrl ključ (A4: A12 in C4: D12).
- Pojdite na Vstavi zavihek.
- Izberite »Vstavite stolpec ali palični grafikon.”
- Izberite “Grozdasti stolpec/Grozdasta palica.”
Posledično boste dobili grafikon s podatkovnimi točkami, izrisanimi na podlagi pravil pogojnega oblikovanja-tako kot je bilo obljubljeno:
3. korak: spremenite vrednosti prekrivanja in širine vrzeli.
Preden zaprete to vadnico, odpravite težavo z napačno postavljenimi stolpci zaradi nevidnih praznih vrednosti (napake #N/A), narisanih na grafikonu.
Z desno miškino tipko kliknite kateri koli stolpec in izberite »Oblikujte niz podatkov”Iz kontekstnega menija, ki se prikaže.
V podoknu opravil spremenite položaj in širino stolpcev:
- Preklopite na Možnosti serije zavihek.
- Spremeni "Prekrivanje serije"Do"100%.”
- Nastavite Širina vrzeli do “60%.”
4. korak: prilagodite barvno shemo.
Na koncu dodajte še zadnje poteze. Brez zapiranja Oblikujte niz podatkov v podoknu opravil spremenite barvno shemo grafikona:
- Pojdite na Fill & Line zavihek.
- Spodaj "Izpolnite, "Izberite"Trdno polnilo.”
- Kliknite na “Barva polnila”In izberite zelena iz barvne palete.
Ko ste tam, prebarvajte druge serije podatkov, spremenite naslov grafikona in vaš popolnoma dinamični grafikon s pogojnim oblikovanjem je pripravljen za uporabo!
