Anvendelse af betinget formatering i en pivottabel kan være lidt vanskelig.
I betragtning af at pivottabeller er så dynamiske, og dataene i backend ofte kan ændre sig, skal du kende den rigtige måde at bruge betinget formatering i en pivottabel i Excel.
Den forkerte måde at anvende betinget formatering på et pivottabel
Lad os først se på den almindelige måde at anvende betinget formatering på i en pivottabel.
Antag, at du har en pivottabel som vist herunder:
I ovenstående datasæt er datoen i rækkerne, og vi har butiksdata i kolonner.
Her er den almindelige måde at anvende betinget formatering på ethvert datasæt:
- Vælg data (i dette tilfælde anvender vi den betingede formatering til B5: D14).
- Gå til Hjem -> Betinget formatering -> Top-/bundregler -> Over gennemsnittet.
- Angiv formatet (jeg bruger "Grøn udfyldning med grøn tekst").
- Klik på OK.
Dette vil anvende den betingede formatering som vist nedenfor:
Alle datapunkter, der ligger over gennemsnittet af hele datasættet, er blevet fremhævet.
Problemet med denne metode er, at den har anvendt det betingede format på et fast cellecelle (B5: D14). Hvis du tilføjer data i backend og opdaterer denne pivottabel, vil den betingede formatering ikke blive anvendt på den.
For eksempel går jeg tilbage til datasættet og tilføjer data for endnu en dato (11. januar 2015). Dette er, hvad jeg får, når jeg opdaterer pivottabellen.
Som du kan se på billedet ovenfor, bliver dataene for 11. januar 2015 ikke fremhævet (mens det burde, da værdierne for Store 1 og Store 3 er over gennemsnittet).
Grunden, som jeg nævnte ovenfor, er, at den betingede formatering er blevet anvendt på et fast område (B5: D14), og det bliver ikke udvidet til nye data i pivottabellen.
Den rigtige måde at anvende betinget formatering på et pivottabel
Her er to metoder til at sikre, at betinget formatering fungerer, selvom der er nye data i backend.
Metode 1 - Brug af pivottabelformateringsikon
Denne metode bruger ikonet Pivottabelformateringsindstillinger, der vises, så snart du anvender betinget formatering i en pivottabel.
Her er trinene til at gøre dette:
- Vælg de data, som du vil anvende betinget formatering på.
- Gå til Hjem -> Betinget formatering -> Top-/bundregler -> Over gennemsnittet.
- Angiv formatet (jeg bruger "Grøn udfyldning med grøn tekst").
- Klik på Ok.
- Når du følger ovenstående trin, anvender den den betingede formatering på datasættet. Nederst til højre i datasættet vil du se ikonet for formateringsindstillinger:
- Klik på ikonet. Det viser tre muligheder i en rullemenu:
- Udvalgte celler (som ville blive valgt som standard).
- Alle celler, der viser værdier af "sum af indtægter".
- Alle celler, der viser værdier for "sum af indtægter" for "dato" og "butik".
- Vælg den tredje mulighed - Alle celler, der viser værdier af "omsætning" for "Dato" og "Gem".
Når du nu tilføjer data i bagenden og opdaterer pivottabellen, vil de yderligere data automatisk blive dækket af betinget formatering.
Forståelse af de tre muligheder:
- Udvalgte celler: Dette er standardindstillingen, hvor betinget formatering kun anvendes på de valgte celler.
- Alle celler, der viser værdier af "sum af indtægter": I denne indstilling overvejer den alle cellerne, der viser summen af indtægtsværdier (eller hvilke data du har i værdiafsnittet i pivottabellen).
- Problemet med denne mulighed er, at den også vil dække Grand Total -værdierne og anvende betinget formatering på den.
- Alle celler, der viser værdier for "sum af indtægter" for "dato" og "butik": Dette er den bedste løsning i dette tilfælde. Den anvender den betingede formatering til alle værdierne (eksklusive Grand Totals) for kombinationen af dato og butik. Selvom du tilføjer flere data i bagenden, tager denne mulighed sig af det.
Bemærk:
- Ikonet Formateringsindstillinger er synligt lige efter, at du har anvendt betinget formatering på datasættet. Hvis går væk, hvis du gør noget andet (rediger en celle eller skift skrifttype/justering osv.).
- Betinget formatering forsvinder, hvis du ændrer række-/kolonnefelterne. Hvis du f.eks. Fjerner feltet Dato og anvender det igen, vil betinget formatering gå tabt.
Metode 2 - Brug af Conditional Formatting Rules Manager
Bortset fra at bruge ikonet Formateringsindstillinger kan du også bruge dialogboksen Håndtering af betingede formateringsregler til at anvende betinget formatering i en pivottabel.
Denne metode er nyttig, når du allerede har anvendt den betingede formatering, og du vil ændre reglerne.
Sådan gør du det:
- Vælg de data, som du vil anvende betinget formatering på.
- Gå til Hjem -> Betinget formatering -> Top-/bundregler -> Over gennemsnittet.
- Angiv formatet (jeg bruger "Grøn udfyldning med grøn tekst").
- Klik på Ok. Dette vil anvende den betingede formatering til de valgte celler.
- Gå til Hjem -> Betinget formatering -> Administrer regler.
- I den Conditional Formatting Rules Manager skal du vælge den regel, du vil redigere, og klikke på knappen Rediger regel.
- I dialogboksen Rediger regel ser du de samme tre muligheder:
- Udvalgte celler.
- Alle celler, der viser værdier af "sum af indtægter".
- Alle celler, der viser "Sum of Revenue" -værdier for "Date" og "Store".
- Vælg den tredje mulighed, og klik på OK.
Dette vil anvende den betingede formatering på alle cellerne for felterne "Dato" og "Gem". Selvom du ændrer backend -data (tilføj flere lagerdata eller datodata), ville den betingede formatering være funktionel.
Bemærk: Betinget formatering forsvinder, hvis du ændrer række-/kolonnefelterne. Hvis du f.eks. Fjerner feltet Dato og anvender det igen, vil betinget formatering gå tabt.
