Denne vejledning viser, hvordan du opretter Excel -diagrammer med betinget formatering i alle versioner af Excel: 2007, 2010, 2013, 2016 og 2022.
Betinget formatering er praksis med at tildele tilpasset formatering til Excel-celler-farve, skrifttype osv. Baseret på de angivne kriterier (betingelser). Funktionen hjælper med at analysere data, finde statistisk signifikante værdier og identificere mønstre inden for et givet datasæt.
Men desværre tilbyder Excel ikke nogen indbyggede værktøjer til anvendelse af betinget formatering på Excel-diagrammer.
Men med en dråbe regnearksmagi er intet problem uoverstigeligt. I denne vejledning lærer du, hvordan du opretter et dynamisk diagram med betinget formatering, hvor de plottede datapunkter automatisk formateres baseret på deres respektive faktiske værdier.
Som et eksempel kan du se på dette kolonnediagram og nedbryde en salgschefs ydeevne over en periode på otte måneder. Bemærk, hvordan kolonnens farve automatisk justeres afhængigt af dens underliggende værdi:
Kom godt i gang
Overvej dette eksempeldatasæt, der indeholder salgsdata:
Før vi begynder, skal du flytte dit datasæt rundt, så du har tre tomme rækker over tabellen med dine rådata. Rækkerne bruges til at angive betingede formateringsregler.
Trin 1: Forbered diagramdata.
For at få tingene til at fungere, skal du tilføje nogle ekstra diagramdata til ligningen. For nemheds skyld kan du tage et kig på, hvordan dine diagramdata skal se ud ved afslutningen af forberedelsesfasen:
Vores første trin er at definere de grænseværdier (betingelser), der skal opdele de faktiske værdier i flere kategorier, hvilket gør det muligt at kortlægge dem som separate dataserier.
Fra ovenstående skærmbillede kan du for eksempel se, at salgstallene for en given måned skal falde inden for intervallet $ 90.000 til $ 150.000 for at blive betragtet som tilfredsstillende.
For at oprette reglerne skal du bruge de tre tomme rækker over datasættet:
- Række 1 (etiket): Brug disse celler til at mærke formateringsreglerne.
- Række 2 (min. Værdi): Værdierne i denne række definerer den nedre grænse for hver betingelse.
- Række 3 (maks. Værdi): På samme måde sætter disse værdier de øvre grænser.
Med denne metode kan du oprette så mange betingede formateringsregler som du vil-bare sørg for, at værdiområderne ikke overlapper hinanden.
Når du fortsætter med at gå fremad i opsætningen af dit diagram, skal du designe tilpassede diagramlegendelementer, der giver kontekst til diagrammet. Indtast denne formel i C4 og kopier det over til D4 ved at trække i fyldhåndtaget:
| 1 | = C1 & ": fra" & TEKST (C2, "$#, ##") & "til" & TEKST (C3, "$#, ##") |
Formlen sammensætter en pænt dynamisk etiket baseret på de tidligere fastsatte betingede formateringsregler. TEKST -funktionen formaterer værdierne som valuta. Men hvis din datatype er forskellig, skal du i stedet anvende denne formel:
| 1 | = C1 & ": fra" & TEKST (C2, "#, ##") & "til" & TEKST (C3, "#, ##") |
Eller denne, når du arbejder med procenter:
| 1 | = C1 & ": fra" & TEKST (C2, "#%") & "til" & TEKST (C3, "#%") |
Find endelig diagramværdierne. Indtast følgende formel i C5, kopier det ned til C12 og over til de matchende celler i kolonne D:
| 1 | = HVIS (OG (C $ 2 <$ B5, $ B5 <= C $ 3), $ B5, NA ()) |
Formlen sammenligner en given faktisk værdi i kolonne B mod de angivne grænseværdier ved hjælp af IF- og AND -funktionerne. Hvis værdien falder inden for området, bliver den kopieret til denne kolonne. Ellers returnerer formlen fejlen #N/A for at forhindre, at den samme værdi plottes flere gange.
Trin 2: Opret et kolonnediagram.
Efter at have samlet alle diagramdata, skal du oprette et simpelt søjlediagram eller et søjlediagram som et alternativ:
- Fremhæv alle diagramdata bortset fra de kolonner, der indeholder de faktiske værdier og reglerne, ved at holde Ctrl nøgle (A4: A12 og C4: D12).
- Gå til Indsæt fane.
- Vælg "Indsæt kolonne eller søjlediagram.”
- Vælg "Klynge i søjle/klynge.”
Som et resultat vil du ende med et diagram med datapunkter afbildet baseret på de betingede formateringsregler-lige som lovet:
Trin 3: Rediger værdierne for overlapning og mellemrumsbredde.
Inden du lukker denne vejledning, skal du løse problemet med de forkert placerede kolonner forårsaget af de usynlige tomme værdier ( #N/A -fejlene), der er afbildet på diagrammet.
Højreklik på en af kolonnerne, og vælg "Formatér dataserier”Fra den kontekstuelle menu, der dukker op.
I opgaveruden skal du ændre kolonnernes placering og bredde:
- Skift til Seriemuligheder fane.
- Lave om "Serie overlapning" til "100%.”
- Indstil Gapbredde til "60%.”
Trin 4: Juster farveskemaet.
Tilføj endelig den sidste hånd. Uden at lukke Formatér dataserier opgaveruden, rediger farveskemaet i diagrammet:
- Gå til Fyld & linje fane.
- Under "Fylde, "Vælg"Fast fyld.”
- Klik på "Fyld farve”Ikon og vælg grøn fra farvepaletten.
Når du er der, skal du farve de andre dataserier om, ændre diagramtitlen, og dit fuldt dynamiske diagram med betinget formatering er klar til at gå!
