Denna handledning visar hur du skapar Excel -diagram med villkorlig formatering i alla versioner av Excel: 2007, 2010, 2013, 2016 och 2022.
Villkorlig formatering är praxis att tilldela anpassad formatering till Excel-celler-färg, teckensnitt etc. baserat på de angivna kriterierna (villkor). Funktionen hjälper till att analysera data, hitta statistiskt signifikanta värden och identifiera mönster inom en given datamängd.
Men tyvärr erbjuder Excel inga inbyggda verktyg för att tillämpa villkorlig formatering på Excel-diagram.
Men med en droppe kalkylarkmagi är inga problem oöverstigliga. I den här självstudien lär du dig hur du skapar ett dynamiskt diagram med villkorlig formatering där de plottade datapunkterna formateras automatiskt baserat på deras respektive faktiska värden.
Som ett exempel, ta en titt på det här kolumndiagrammet och bryt ner prestanda för en försäljningschef under en period av åtta månader. Lägg märke till hur kolumnens färg automatiskt justeras beroende på dess underliggande värde:
Komma igång
Tänk på denna exempeluppsättning som innehåller data om försäljningsresultat:
Innan vi börjar, flytta din datamängd så att du har tre tomma rader ovanför tabellen med dina rådata. Raderna kommer att användas för att ställa in villkorliga formateringsregler.
Steg 1: Förbered diagramdata.
För att få saker att fungera måste du lägga till lite extra diagramdata till ekvationen. För din bekvämlighet, ta en titt på hur dina diagramdata ska se ut i slutet av förberedelseskedet:
Vårt första steg är att definiera gränsvärden (villkor) som ska dela de faktiska värdena i flera kategorier, vilket gör det möjligt att kartlägga dem som separata dataserier.
Från skärmdumpen ovan kan du till exempel se att försäljningssiffrorna för en viss månad måste ligga inom intervallet $ 90 000 till $ 150 000 för att anses tillfredsställande.
För att skapa reglerna, använd de tre tomma raderna ovanför datamängden:
- Rad 1 (etikett): Använd dessa celler för att märka formateringsreglerna.
- Rad 2 (min. Värde): Värdena i den här raden definierar den nedre gränsen för varje villkor.
- Rad 3 (maxvärde): På samma sätt sätter dessa värden de övre gränserna.
Med den här metoden kan du skapa så många villkorliga formateringsregler som du bara vill-se bara till att värdeområdena inte överlappar varandra.
När du fortsätter att gå framåt när du konfigurerar ditt diagram, designa anpassade diagramförklaringsobjekt som ger sammanhang för diagrammet. Ange denna formel i C4 och kopiera det till D4 genom att dra i fyllhandtaget:
| 1 | = C1 & ": från" & TEXT (C2, "$#, ##") & "till" & TEXT (C3, "$#, ##") |
Formeln sätter ihop en snygg dynamisk etikett baserad på tidigare uppsatta villkorliga formateringsregler. TEXT -funktionen formaterar värdena som valuta. Men om din datatyp skiljer sig, använd den här formeln istället:
| 1 | = C1 & ": från" & TEXT (C2, "#, ##") & "till" & TEXT (C3, "#, ##") |
Eller den här när du arbetar med procentsatser:
| 1 | = C1 & ": från" & TEXT (C2, "#%") & "till" & TEXT (C3, "#%") |
Slutligen, hitta diagramvärdena. Skriv in följande formel i C5, kopiera ner det till C12 och över till matchande celler i kolumn D:
| 1 | = OM (OCH (C $ 2 <$ B5, $ B5 <= C $ 3), $ B5, NA ()) |
Formeln jämför ett givet verkligt värde i kolumn B mot de angivna gränsvärdena med hjälp av IF- och AND -funktionerna. Om värdet faller inom intervallet kopieras det till den här kolumnen. Annars returnerar formeln felet #N/A för att förhindra att samma värde plottas flera gånger.
Steg 2: Skapa ett kolumndiagram.
Efter att ha samlat in alla diagramdata kan du skapa ett enkelt kolumndiagram eller ett stapeldiagram som ett alternativ:
- Markera alla diagramdata utom kolumnerna som innehåller de faktiska värdena och reglerna genom att hålla ned Ctrl nyckel (A4: A12 och C4: D12).
- Gå till Föra in flik.
- Välj "Infoga kolumn eller stapeldiagram.”
- Välj "Clustered Column/Clustered Bar.”
Som ett resultat kommer du att sluta med ett diagram med datapunkter ritade baserat på villkorliga formateringsregler-precis som utlovat:
Steg 3: Ändra värdena för överlappning och gapbredd.
Innan du stänger den här självstudien, åtgärda problemet med de felplacerade kolumnerna som orsakas av de osynliga tomma värdena ( #N/A -felen) som ritats på diagrammet.
Högerklicka på någon av kolumnerna och välj "Formatera dataserier”Från kontextmenyn som dyker upp.
Ändra position och bredd för kolumnerna i uppgiftsfönstret:
- Byt till Serialternativ flik.
- Förändra "Serie överlappning”Till”100%.”
- Ställ in Gapbredd till ”60%.”
Steg #4: Justera färgschemat.
Till sist, lägg till den sista handen. Utan att stänga Formatera dataserier aktivitetsfönster, ändra färgschemat i diagrammet:
- Gå till Fyll & rad flik.
- Under "Fylla, "Välj"Fast fyllning.”
- Klicka på "Fyllnadsfärg”Ikon och välj grön från färgpaletten.
Väl där, färga om de andra dataserierna, ändra diagramtiteln, och ditt helt dynamiska diagram med villkorlig formatering är redo att gå!
