jak zastosować formatowanie warunkowe w tabeli przestawnej w programie Excel

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 korzystania z formatowania warunkowego w tabeli przestawnej w programie Excel.

niewłaściwy sposób stosowania formatowania warunkowego do tabeli przestawnej

przyjrzyjmy się najpierw regularnemu 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 przechowujemy w kolumnach.

oto regularny sposób stosowania formatowania warunkowego do dowolnego zbioru danych:

• Wybierz dane (w tym przypadku stosujemy formatowanie warunkowe do B5:D14).
• przejdź do strony głównej –> Formatowanie warunkowe –> reguły górne/dolne –> Powyżej średniej.
• określ format (używam “Green Fill with Dard Green Text”).
• kliknij OK.

zastosuje to formatowanie warunkowe, 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 zastosowała ona format warunkowy do stałego zakresu komórek (B5:D14). Jeśli dodasz dane do zaplecza 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). To jest to, co dostaję, gdy odświeżam tabelę przestawną.

jak widać na powyższym obrazku, dane z 11 stycznia 2015 r.nie są podświetlane (podczas gdy powinny jako wartości dla magazynu 1 i magazynu 3 są Powyżej średniej).

powodem, jak wspomniałem powyżej, jest to, że formatowanie warunkowe zostało zastosowane na stałym zakresie (B5:D14) i nie zostanie rozszerzone 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 wtedy, gdy w zapleczu są nowe dane.

Metoda 1-Używanie ikony formatowania tabeli przestawnej

ta metoda wykorzystuje ikonę opcji formatowania tabeli przestawnej, która pojawia się po zastosowaniu formatowania warunkowego w tabeli przestawnej.

oto kroki, aby to zrobić:

• Wybierz dane, na których chcesz zastosować formatowanie warunkowe.
• przejdź do strony głównej –> Formatowanie warunkowe –> reguły górne/dolne –> Powyżej średniej.
• określ format (używam “Green Fill with Dard Green Text”).
• Kliknij Ok.
  • wykonując powyższe kroki, stosuje się formatowanie warunkowe na zestawie danych. W prawym dolnym rogu zestawu danych zobaczysz ikonę opcji formatowania:

• kliknij na ikonę. Pokaże trzy opcje w rozwijanej:
  • wybrane komórki (które zostaną wybrane domyślnie).
  • wszystkie komórki pokazujące wartości “suma przychodów”.
  • wszystkie komórki pokazujące wartości” suma przychodów ” dla ” daty “i ” sklepu”.
• Wybierz trzecią opcję-Wszystkie komórki pokazujące wartości” suma przychodów “dla” daty “i”sklepu”.

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 stosuje się tylko na wybranych komórkach.
• wszystkie komórki pokazujące wartości “suma przychodów”: W tej opcji uwzględnia wszystkie komórki, które pokazują sumę wartości przychodów (lub dowolne dane w sekcji wartości tabeli przestawnej).
  • problem z tą opcją polega na tym, że obejmie ona również wielkie wartości całkowite i zastosuje do niej formatowanie warunkowe.
• wszystkie komórki pokazujące wartości” suma przychodów ” dla ” daty “i ” sklepu”: jest to najlepsza opcja w tym przypadku. Stosuje formatowanie warunkowe do wszystkich wartości (z wyjątkiem wielkich sumy) dla kombinacji date I Store. Nawet jeśli dodasz więcej danych w zapleczu, ta opcja zajmie się tym.

Uwaga:

• ikona opcje formatowania jest widoczna zaraz po zastosowaniu formatowania warunkowego na zbiorze danych. Jeśli zniknie, jeśli zrobisz coś innego(edytuj komórkę lub zmień czcionkę / wyrównanie itp.).
• Formatowanie warunkowe zniknie, jeśli zmienisz pola wierszy/kolumn. Na przykład, jeśli usuniesz pole daty i zastosujesz je ponownie, formatowanie warunkowe zostanie utracone.

Metoda 2 – Używanie Menedżera reguł formatowania warunkowego

oprócz używania ikony opcji formatowania, można 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, na których chcesz zastosować formatowanie warunkowe.
• przejdź do strony głównej –> Formatowanie warunkowe –> reguły górne/dolne –> Powyżej średniej.
• określ format (używam “Green Fill with Dard Green Text”).
• kliknij Ok. Spowoduje to zastosowanie formatowania warunkowego do wybranych komórek.
• przejdź do strony głównej –> 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 ” daty “i ” sklepu”.
• Wybierz trzecią opcję i kliknij OK.

spowoduje to zastosowanie formatowania warunkowego do wszystkich komórek pól “Data” i “sklep”. Nawet jeśli zmienisz dane zaplecza (dodaj więcej danych magazynu lub Danych daty), formatowanie warunkowe będzie funkcjonalne.

Uwaga: formatowanie warunkowe zniknie po zmianie pól wiersza / kolumny. Na przykład, jeśli usuniesz pole daty i zastosujesz je ponownie, formatowanie warunkowe zostanie utracone.

Możesz również polubić następujące samouczki Excela:

• Wyróżnij co drugi wiersz w programie Excel za pomocą formatowania warunkowego.
• Tworzenie tabeli przestawnej w Excelu-samouczek krok po kroku.
• Przygotowanie Danych Źródłowych Do Tabeli Przestawnej.
• jak grupować liczby w tabeli przestawnej w programie Excel.
• jak grupować daty w tabelach przestawnych w Excelu.
• Jak odświeżyć tabelę przestawną w Excelu.
• Usuń tabelę przestawną w programie Excel.
• używanie krajalnic w tabeli przestawnej Excela.
• Jak dodać i użyć pola obliczeniowego tabeli przestawnej programu Excel.
• Pivot Cache w Excelu – Co To jest i jak najlepiej z niego korzystać?
• jak zastąpić puste komórki zerami w tabelach przestawnych programu Excel.