Att tillämpa villkorlig formatering i ett pivottabell kan vara lite knepigt.
Med tanke på att pivottabellerna är så dynamiska och data i backend ofta kan ändras måste du veta rätt sätt att använda villkorlig formatering i en pivottabell i Excel.
Fel sätt att använda villkorlig formatering på ett pivottabell
Låt oss först titta på det vanliga sättet att använda villkorlig formatering i en pivottabell.
Antag att du har ett pivottabell enligt nedan:
I datauppsättningen ovan finns datumet i raderna och vi har butiksförsäljningsdata i kolumner.
Här är det vanliga sättet att tillämpa villkorlig formatering på valfri datamängd:
- Välj data (i det här fallet använder vi den villkorade formateringen på B5: D14).
- Gå till Hem -> Villkorlig formatering -> Övre/undre regler -> Över genomsnittet.
- Ange formatet (jag använder "Grön fyllning med grön text").
- Klicka på OK.
Detta gäller den villkorade formateringen enligt nedan:
Alla datapunkter som ligger över genomsnittet för hela datamängden har markerats.
Problemet med den här metoden är att den har tillämpat det villkorade formatet på ett fast cellintervall (B5: D14). Om du lägger till data i backend och uppdaterar denna pivottabell tillämpas inte den villkorade formateringen på den.
Till exempel går jag tillbaka till datasetet och lägger till data för ytterligare ett datum (11 januari 2015). Detta är vad jag får när jag uppdaterar pivottabellen.
Som du kan se på bilden ovan markeras inte uppgifterna för den 11 januari 2015 (medan det bör, eftersom värdena för Store 1 och Store 3 är över genomsnittet).
Anledningen, som jag nämnde ovan, är att den villkorade formateringen har tillämpats på ett fast intervall (B5: D14), och det utvidgas inte till nya data i pivottabellen.
Rätt sätt att använda villkorlig formatering på ett pivottabell
Här är två metoder för att säkerställa att villkorlig formatering fungerar även när det finns ny data i backend.
Metod 1 - Använda pivottabellformateringsikon
Den här metoden använder ikonen Formateringsalternativ för pivottabell som visas så snart du använder villkorlig formatering i en pivottabell.
Här är stegen för att göra detta:
- Välj de data som du vill använda villkorlig formatering på.
- Gå till Hem -> Villkorlig formatering -> Övre/undre regler -> Över genomsnittet.
- Ange formatet (jag använder "Grön fyllning med grön text").
- Klicka på Ok.
- När du följer stegen ovan tillämpas den villkorliga formateringen på datamängden. Längst ner till höger i datamängden ser du ikonen Formateringsalternativ:
- Klicka på ikonen. Det kommer att visa tre alternativ i en rullgardinsmeny:
- Valda celler (som skulle väljas som standard).
- Alla celler som visar värden för summan av intäkter.
- Alla celler som visar "Summa av intäkter" -värden för "Datum" och "Lagra".
- Välj det tredje alternativet - Alla celler som visar "Summa av intäkter" -värden för "Datum" och "Lagra".
När du nu lägger till data i backend och uppdaterar pivottabellen täcks tilläggsdata automatiskt av villkorlig formatering.
Förstå de tre alternativen:
- Valda celler: Detta är standardalternativet där villkorlig formatering endast tillämpas på de markerade cellerna.
- Alla celler som visar "Summa av intäkter" -värden: I det här alternativet tar det hänsyn till alla celler som visar summan av intäktsvärden (eller vilken data du har i värdesektionen i pivottabellen).
- Problemet med det här alternativet är att det också kommer att täcka de totala värdena och tillämpa villkorlig formatering på det.
- Alla celler som visar "Summa av intäkter" -värden för "Datum" och "Lagra": Detta är det bästa alternativet i det här fallet. Den tillämpar den villkorade formateringen på alla värden (exklusive totalsummor) för kombinationen av datum och lagring. Även om du lägger till mer data i backend kommer detta alternativ att ta hand om det.
Notera:
- Ikonen Formateringsalternativ syns direkt efter att du har tillämpat villkorlig formatering på datamängden. Om försvinner om du gör något annat (redigera en cell eller ändra teckensnitt/justering, etc.).
- Villkorsstyrd formatering försvinner om du ändrar rad-/kolumnfälten. Om du till exempel tar bort datumfältet och använder det igen, kommer villkorlig formatering att gå förlorad.
Metod 2 - Använda Conditional Formatting Rules Manager
Förutom att använda ikonen Formateringsalternativ kan du också använda dialogrutan Villkorlig formateringsreglerhanterare för att tillämpa villkorlig formatering i en pivottabell.
Denna metod är användbar när du redan har tillämpat den villkorliga formateringen och du vill ändra reglerna.
Så här gör du:
- Välj de data som du vill använda villkorlig formatering på.
- Gå till Hem -> Villkorlig formatering -> Övre/undre regler -> Över genomsnittet.
- Ange formatet (jag använder "Grön fyllning med grön text").
- Klicka på Ok. Detta kommer att tillämpa den villkorade formateringen på de markerade cellerna.
- Gå till Hem -> Villkorlig formatering -> Hantera regler.
- I den villkorliga formateringsregelhanteraren väljer du den regel du vill redigera och klickar på knappen Redigera regel.
- I dialogrutan Redigera regel ser du samma tre alternativ:
- Utvalda celler.
- Alla celler som visar värden för summan av intäkter.
- Alla celler som visar "Summa av intäkter" -värden för "Datum" och "Butik".
- Välj det tredje alternativet och klicka på OK.
Detta kommer att tillämpa den villkorade formateringen på alla celler för "Datum" och "Lagra" fält. Även om du ändrar backenddata (lägg till mer lagringsdata eller datumdata) skulle den villkorade formateringen fungera.
Notera: Villkorsstyrd formatering försvinner om du ändrar rad-/kolumnfälten. Om du till exempel tar bort datumfältet och använder det igen, kommer villkorlig formatering att gå förlorad.
