Å bruke betinget formatering i et pivottabell kan være litt vanskelig.
Gitt at pivottabeller er så dynamiske og dataene i backend ofte kan endres, må du vite den riktige måten å bruke betinget formatering i en pivottabell i Excel.
Feil måte å bruke betinget formatering på et pivottabell
La oss først se på den vanlige måten å bruke betinget formatering på i en pivottabell.
Anta at du har et pivottabell som vist nedenfor:
I datasettet ovenfor er datoen i radene, og vi har lagringssalgsdata i kolonner.
Her er den vanlige måten å bruke betinget formatering på et hvilket som helst datasett:
- Velg dataene (i dette tilfellet bruker vi den betingede formateringen til B5: D14).
- Gå til Hjem -> Betinget formatering -> Topp-/bunnregler -> Over gjennomsnittet.
- Spesifiser formatet (jeg bruker "Grønn fyll med grønn tekst").
- Klikk OK.
Dette vil gjelde den betingede formateringen som vist nedenfor:
Alle datapunkter som er over gjennomsnittet av hele datasettet er uthevet.
Problemet med denne metoden er at den har brukt det betingede formatet på et fast celleområde (B5: D14). Hvis du legger til data i backend og oppdaterer denne pivottabellen, vil ikke den betingede formateringen bli brukt på den.
For eksempel går jeg tilbake til datasettet og legger til data for en annen dato (11. januar 2015). Dette er hva jeg får når jeg oppdaterer pivottabellen.
Som du kan se på bildet ovenfor, blir ikke dataene for 11. januar 2015 fremhevet (mens de burde det, ettersom verdiene for Store 1 og Store 3 er over gjennomsnittet).
Grunnen, som jeg nevnte ovenfor, er at den betingede formateringen er brukt på et fast område (B5: D14), og den blir ikke utvidet til nye data i pivottabellen.
Den riktige måten å bruke betinget formatering på et pivottabell
Her er to metoder for å sikre at betinget formatering fungerer selv om det er nye data i backend.
Metode 1 - Bruke pivottabellformateringsikon
Denne metoden bruker ikonet for alternativer for pivottabellformatering som vises så snart du bruker betinget formatering i en pivottabell.
Her er trinnene for å gjøre dette:
- Velg dataene du vil bruke betinget formatering på.
- Gå til Hjem -> Betinget formatering -> Topp-/bunnregler -> Over gjennomsnittet.
- Spesifiser formatet (jeg bruker "Grønn fyll med grønn tekst").
- Klikk Ok.
- Når du følger trinnene ovenfor, gjelder den betingede formateringen på datasettet. Nederst til høyre i datasettet ser du ikonet for formateringsalternativer:
- Klikk på ikonet. Det vil vise tre alternativer i en rullegardinmeny:
- Utvalgte celler (som ville blitt valgt som standard).
- Alle celler som viser verdier for "sum of Revenue".
- Alle cellene som viser verdiene "Sum of Revenue" for "Date" og "Store".
- Velg det tredje alternativet - Alle celler som viser "Sum of Revenue" -verdier for "Date" og "Store".
Når du legger til data i bakenden og oppdaterer pivottabellen, vil tilleggsdataene automatisk bli dekket av betinget formatering.
Forstå de tre alternativene:
- Utvalgte celler: Dette er standardalternativet der betinget formatering bare brukes på de valgte cellene.
- Alle celler som viser verdier av summen av inntekter: I dette alternativet tar det i betraktning alle cellene som viser summen av inntektsverdier (eller hvilke data du har i verdidelen i pivottabellen).
- Problemet med dette alternativet er at det også vil dekke Grand Total -verdiene og bruke betinget formatering på det.
- Alle celler som viser verdiene «Sum of Revenue» for “Date” og “Store”: Dette er det beste alternativet i dette tilfellet. Den bruker den betingede formateringen på alle verdiene (unntatt totalsummer) for kombinasjonen av dato og butikk. Selv om du legger til flere data i bakenden, vil dette alternativet ta seg av det.
Merk:
- Ikonet Formateringsalternativer er synlig rett etter at du har brukt betinget formatering på datasettet. Hvis forsvinner hvis du gjør noe annet (rediger en celle eller endre skrift/justering, etc.).
- Betinget formatering forsvinner hvis du endrer rad/kolonne -feltene. For eksempel, hvis du fjerner Dato -feltet og bruker det igjen, vil betinget formatering gå tapt.
Metode 2 - Bruke Conditional Formatting Rules Manager
Bortsett fra å bruke ikonet Formateringsalternativer, kan du også bruke dialogboksen Håndterer betingede formateringsregler for å bruke betinget formatering i en pivottabell.
Denne metoden er nyttig når du allerede har brukt den betingede formateringen og du vil endre reglene.
Slik gjør du det:
- Velg dataene du vil bruke betinget formatering på.
- Gå til Hjem -> Betinget formatering -> Topp-/bunnregler -> Over gjennomsnittet.
- Spesifiser formatet (jeg bruker "Grønn fyll med grønn tekst").
- Klikk Ok. Dette vil gjelde den betingede formateringen på de valgte cellene.
- Gå til Hjem -> Betinget formatering -> Administrer regler.
- I Regelen for betinget formateringsregler velger du regelen du vil redigere, og klikker på knappen Rediger regel.
- I dialogboksen Rediger regel ser du de samme tre alternativene:
- Utvalgte celler.
- Alle celler som viser verdier for "sum of Revenue".
- Alle celler som viser verdier for "Sum of Revenue" for "Date" og "Store".
- Velg det tredje alternativet og klikk OK.
Dette vil gjelde den betingede formateringen på alle cellene for feltene "Dato" og "Lagre". Selv om du endrer backend -dataene (legger til flere lagringsdata eller datodata), vil den betingede formateringen være funksjonell.
Merk: Betinget formatering forsvinner hvis du endrer rad/kolonne -feltene. For eksempel, hvis du fjerner Dato -feltet og bruker det igjen, vil betinget formatering gå tapt.
