Denne opplæringen viser hvordan du lager Excel -diagrammer med betinget formatering i alle versjoner av Excel: 2007, 2010, 2013, 2016 og 2022.
Betinget formatering er praksisen med å tilordne tilpasset formatering til Excel-celler-farge, skrift, etc., basert på de angitte kriteriene (betingelsene). Funksjonen hjelper til med å analysere data, finne statistisk signifikante verdier og identifisere mønstre i et gitt datasett.
Men dessverre tilbyr Excel ikke noen innebygde verktøy for å bruke betinget formatering på Excel-diagrammer.
Imidlertid, med en dråpe regnearkmagi, er ikke noe problem uoverstigelig. I denne opplæringen lærer du hvordan du oppretter et dynamisk diagram med betinget formatering der de plottede datapunktene automatisk formateres basert på de respektive faktiske verdiene.
Som et eksempel, ta en titt på dette kolonnediagrammet, og bryt ned ytelsen til en salgssjef over en periode på åtte måneder. Legg merke til hvordan fargen på kolonnen automatisk justeres avhengig av den underliggende verdien:
Starter
Vurder dette eksempeldatasettet som inneholder salgsresultatdata:
Før vi begynner, flytt datasettet rundt slik at du har tre tomme rader over tabellen med rådataene dine. Radene skal brukes til å angi betingede formateringsregler.
Trinn 1: Forbered diagramdata.
For å få ting til å fungere, må du legge til noen ekstra diagramdata i ligningen. For enkelhets skyld, ta en titt på hvordan diagramdataene dine skal se ut ved slutten av forberedelsesfasen:
Vårt første trinn er å definere grenseverdiene (betingelsene) som skal dele de faktiske verdiene i flere kategorier, slik at det er mulig å kartlegge dem som separate dataserier.
Fra skjermbildet ovenfor kan du for eksempel se at salgstallene for en gitt måned må falle innenfor området $ 90 000 til $ 150 000 for å bli betraktet som tilfredsstillende.
For å lage reglene, bruk de tre tomme radene over datasettet:
- Rad 1 (etikett): Bruk disse cellene til å merke formateringsreglene.
- Rad 2 (Min. Verdi): Verdiene i denne raden definerer den nedre grensen for hver tilstand.
- Rad 3 (maks. Verdi): På samme måte setter disse verdiene de øvre grensene.
Med denne metoden kan du opprette så mange betingede formateringsregler som du vil-bare pass på at verdiområdene ikke overlapper hverandre.
Når du fortsetter å gå fremover når du setter opp diagrammet, kan du designe tilpassede diagramlegendelementer som gir kontekst for diagrammet. Skriv inn denne formelen C4 og kopier det over til D4 ved å dra i fyllhåndtaket:
| 1 | = C1 & ": fra" & TEKST (C2, "$#, ##") & "til" & TEKST (C3, "$#, ##") |
Formelen setter sammen en pent utseende dynamisk etikett basert på de tidligere angitte betingede formateringsreglene. TEKST -funksjonen formaterer verdiene som valuta. Men hvis datatypen din er forskjellig, kan du bruke denne formelen i stedet:
| 1 | = C1 & ": fra" & TEKST (C2, "#, ##") & "til" & TEKST (C3, "#, ##") |
Eller denne når du jobber med prosenter:
| 1 | = C1 & ": fra" & TEKST (C2, "#%") & "til" & TEKST (C3, "#%") |
Finn til slutt diagramverdiene. Skriv inn følgende formel C5, kopier den ned til C12 og over til de matchende cellene i kolonne D:
| 1 | = HVIS (OG (C $ 2 <$ B5, $ B5 <= C $ 3), $ B5, NA ()) |
Formelen sammenligner en gitt faktisk verdi i kolonne B mot de angitte grenseverdiene ved hjelp av IF- og AND -funksjonene. Hvis verdien faller innenfor området, blir den kopiert til denne kolonnen. Ellers returnerer formelen #N/A -feilen for å forhindre at samme verdi plottes flere ganger.
Trinn 2: Sett opp et kolonnediagram.
Etter å ha samlet alle diagramdataene, sett opp et enkelt kolonnediagram-eller et stolpediagram som et alternativ:
- Merk alle diagramdataene bortsett fra kolonnene som inneholder de faktiske verdiene og reglene ved å holde nede Ctrl nøkkel (A4: A12 og C4: D12).
- Gå til Sett inn kategorien.
- Plukke ut "Sett inn kolonne eller stolpediagram.”
- Velg "Clustered Column/Clustered Bar.”
Som et resultat vil du ende opp med et diagram med datapunkter plottet basert på de betingede formateringsreglene-akkurat som lovet:
Trinn 3: Endre verdiene for overlapping og gapbredde.
Før du avslutter denne opplæringen, må du løse problemet med de feilplasserte kolonnene forårsaket av de usynlige tomme verdiene ( #N/A -feilene) plottet på diagrammet.
Høyreklikk på en av kolonnene og velg "Formater dataserier”Fra den kontekstuelle menyen som dukker opp.
Endre posisjonen og bredden på kolonnene i oppgaveruten:
- Bytt til Serialternativer kategorien.
- Endring "Serie overlapping" til "100%.”
- Sett Gapbredde til "60%.”
Trinn 4: Juster fargeskjemaet.
Til slutt legger du til den siste finpussen. Uten å lukke Formater dataserier oppgaveruten, endre fargeskjemaet i diagrammet:
- Gå til Fyll & linje kategorien.
- Under "Fylle, "Velg"Solid fylling.”
- Klikk på "Fyll farge"Ikon og velg grønn fra fargepaletten.
Når du er der, farger du de andre dataseriene på nytt, endrer diagramtittelen, og det fullt dynamiske diagrammet med betinget formatering er klart til bruk!
