Stosowanie formatowania warunkowego w tabeli przestawnej może być nieco trudne.
Biorąc pod uwagę, że tabele przestawne są tak dynamiczne, a dane w zapleczu mogą się często zmieniać, musisz znać właściwy sposób używania formatowania warunkowego w tabeli przestawnej w programie Excel.
Niewłaściwy sposób zastosowania formatowania warunkowego do tabeli przestawnej
Przyjrzyjmy się najpierw zwykłemu sposobowi stosowania formatowania warunkowego w tabeli przestawnej.
Załóżmy, że masz tabelę przestawną, jak pokazano poniżej:
W powyższym zestawie danych data jest w wierszach, a dane sprzedaży sklepu mamy w kolumnach.
Oto zwykły sposób stosowania formatowania warunkowego do dowolnego zestawu danych:
- Wybierz dane (w tym przypadku stosujemy formatowanie warunkowe do B5:D14).
- Przejdź do Strona główna -> Formatowanie warunkowe -> Reguły góra/dół -> Powyżej średniej.
- Określ format (używam „Zielonego wypełnienia z zielonym tekstem”).
- Kliknij OK.
Spowoduje to zastosowanie formatowania warunkowego, jak pokazano poniżej:
Wszystkie punkty danych, które są powyżej średniej całego zestawu danych, zostały wyróżnione.
Problem z tą metodą polega na tym, że zastosowano format warunkowy do ustalonego zakresu komórek (B5:D14). Jeśli dodasz dane w zapleczu i odświeżysz tę tabelę przestawną, formatowanie warunkowe nie zostanie do niej zastosowane.
Na przykład wracam do zbioru danych i dodaję dane dla jeszcze jednej daty (11 stycznia 2015 r.). To właśnie otrzymuję, gdy odświeżam tabelę przestawną.
Jak widać na powyższym zdjęciu, dane z 11 stycznia 2015 r. nie są podświetlone (powinno, ponieważ wartości dla Sklepu 1 i Sklepu 3 są powyżej średniej).
Powodem, jak wspomniałem powyżej, jest to, że formatowanie warunkowe zostało zastosowane w ustalonym zakresie (B5:D14) i nie jest rozszerzane na nowe dane w tabeli przestawnej.
Właściwy sposób zastosowania formatowania warunkowego do tabeli przestawnej
Oto dwie metody, aby upewnić się, że formatowanie warunkowe działa, nawet jeśli w zapleczu znajdują się nowe dane.
Metoda 1 - Korzystanie z ikony formatowania tabeli przestawnej
Ta metoda wykorzystuje ikonę Opcje formatowania tabeli przestawnej, która pojawia się zaraz po zastosowaniu formatowania warunkowego w tabeli przestawnej.
Oto kroki, aby to zrobić:
- Wybierz dane, do których chcesz zastosować formatowanie warunkowe.
- Przejdź do Strona główna -> Formatowanie warunkowe -> Reguły góra/dół -> Powyżej średniej.
- Określ format (używam „Zielonego wypełnienia z zielonym tekstem”).
- Kliknij OK.
- Gdy wykonasz powyższe kroki, zastosuje formatowanie warunkowe do zestawu danych. W prawym dolnym rogu zestawu danych zobaczysz ikonę Opcje formatowania:
- Kliknij ikonę. Pokaże trzy opcje na liście rozwijanej:
- Wybrane komórki (które byłyby zaznaczone domyślnie).
- Wszystkie komórki pokazujące wartości „Suma przychodów”.
- Wszystkie komórki pokazujące wartości „Suma przychodów” dla „Data” i „Sklep”.
- Wybierz trzecią opcję - Wszystkie komórki pokazujące wartości „Suma przychodów” dla „Data” i „Sklep”.
Teraz, gdy dodasz jakiekolwiek dane w zapleczu i odświeżysz tabelę przestawną, dodatkowe dane zostaną automatycznie objęte formatowaniem warunkowym.
Zrozumienie trzech opcji:
- Wybrane komórki: Jest to domyślna opcja, w której formatowanie warunkowe jest stosowane tylko w wybranych komórkach.
- Wszystkie komórki pokazujące wartości „Suma przychodów”: W tej opcji uwzględnia wszystkie komórki, które pokazują wartości sumy przychodów (lub dowolne dane, które masz w sekcji wartości tabeli przestawnej).
- Problem z tą opcją polega na tym, że obejmie ona również wartości sumy całkowitej i zastosuje do niej formatowanie warunkowe.
- Wszystkie komórki pokazujące wartości „Suma przychodów” dla „Data” i „Sklep”: To najlepsza opcja w tym przypadku. Stosuje formatowanie warunkowe do wszystkich wartości (z wyjątkiem sum całkowitych) dla kombinacji Data i Sklep. Nawet jeśli dodasz więcej danych w zapleczu, ta opcja zajmie się tym.
Notatka:
- Ikona Opcje formatowania jest widoczna zaraz po zastosowaniu formatowania warunkowego w zestawie danych. Jeśli zniknie, jeśli zrobisz coś innego (edytujesz komórkę lub zmieniasz czcionkę/wyrównanie itp.).
- Formatowanie warunkowe znika po zmianie pól wiersza/kolumny. Na przykład, jeśli usuniesz pole Data i zastosujesz je ponownie, formatowanie warunkowe zostanie utracone.
Metoda 2 - Korzystanie z Menedżera reguł formatowania warunkowego
Oprócz korzystania z ikony Opcje formatowania możesz również użyć okna dialogowego Menedżer reguł formatowania warunkowego, aby zastosować formatowanie warunkowe w tabeli przestawnej.
Ta metoda jest przydatna, gdy już zastosowałeś formatowanie warunkowe i chcesz zmienić reguły.
Oto jak to zrobić:
- Wybierz dane, do których chcesz zastosować formatowanie warunkowe.
- Przejdź do Strona główna -> Formatowanie warunkowe -> Reguły góra/dół -> Powyżej średniej.
- Określ format (używam „Zielonego wypełnienia z zielonym tekstem”).
- Kliknij OK. Spowoduje to zastosowanie formatowania warunkowego do wybranych komórek.
- Przejdź do Strona główna -> Formatowanie warunkowe -> Zarządzaj regułami.
- W Menedżerze reguł formatowania warunkowego wybierz regułę, którą chcesz edytować i kliknij przycisk Edytuj regułę.
- W oknie dialogowym Edytuj regułę zobaczysz te same trzy opcje:
- Wybrane komórki.
- Wszystkie komórki pokazujące wartości „Suma przychodów”.
- Wszystkie komórki pokazujące wartości „Suma przychodów” dla „Data” i „Sklep”.
- Wybierz trzecią opcję i kliknij OK.
Spowoduje to zastosowanie formatowania warunkowego do wszystkich komórek w polach „Data” i „Sklep”. Nawet jeśli zmienisz dane zaplecza (dodasz więcej danych sklepu lub danych dat), formatowanie warunkowe będzie działać.
Notatka: Formatowanie warunkowe znika po zmianie pól wiersza/kolumny. Na przykład, jeśli usuniesz pole Data i zastosujesz je ponownie, formatowanie warunkowe zostanie utracone.
