Przewodnik po formatowaniu warunkowym w programie Excel

Jak

PrzezRyan Dube

Ostatnia aktualizacja dnia15 stycznia 2020 r

Jedną z najbardziej przydatnych funkcji programu Excel jest formatowanie warunkowe. Umożliwia to dostarczanie wizualnych wskaźników danych w arkuszu kalkulacyjnym. Korzystając z formatowania warunkowego w programie Excel, możesz pokazać, czy dane są powyżej czy poniżej limitów, trendów w górę lub w dół, lub wiele więcej.

W tym przewodniku dowiesz się, jak korzystać z formatowania warunkowego w programie Excel i kilka przykładów tego, kiedy najlepiej korzystać z tej funkcji.

Zmiana koloru komórki za pomocą formatowania warunkowego

Jednym z najczęstszych sposobów korzystania z formatowania warunkowego w programie Excel są reguły podświetlania komórek. Jako przykład załóżmy, że nauczyciel używa arkusza kalkulacyjnego do rejestrowania ocen z testu.

Jako prosty arkusz kalkulacyjny nauczyciel mógł po prostu przejrzeć arkusz, aby sprawdzić, którzy uczniowie zdali lub nie zdali testu. Lub nauczyciel kreatywny może zastosować zasadę „wyróżnij komórki”, która wyróżnia pozytywne lub negatywne oceny odpowiednim kolorem - czerwonym lub zielonym.

Aby to zrobić, wybierz Dom menu i wybierz Formatowanie warunkowe w grupie Style. Z tej listy możesz wybrać regułę, którą chcesz zastosować. Obejmuje to wyróżnianie komórek, które są większe, mniejsze niż, pomiędzy lub równe wartości.

W tym przykładzie nauczyciel chce podświetlić komórki na zielono, jeśli są wyższe niż wynik pozytywny, który znajduje się w komórce D2. Podświetl wszystkie komórki w kolumnie B (oprócz nagłówka) i wybierz Lepszy niż z Zaznacz reguły komórek menu.

Możesz wprowadzić wartość statyczną jako limit lub wybrać komórkę zawierającą tę wartość. Możesz zachować standardowe „Jasnoczerwone wypełnienie z ciemnoczerwonym tekstem” w menu rozwijanym, wybrać dowolną inną niestandardową konfigurację kolorów lub wybrać Format niestandardowy założyć własne.

Widać, że ta reguła podświetla wszystkie zaliczające oceny na zielono.

Ale co z ocenami negatywnymi? Aby to osiągnąć, musisz wybrać te same komórki i powtórzyć powyższy proces, ale wybierz zasadę „mniej niż”. Wybierz tę samą komórkę oceny i nadaj jej kolor Jasnoczerwony z Ciemnoczerwonym tekstem.

Gdy skończysz, dwie reguły zastosowane do danych odpowiednio wyróżnią dane oceny w zależności od tego, czy są one poniżej, czy przekraczają limit oceny pozytywnej.

Korzystanie z górnych / dolnych reguł w programie Excel

Inną bardzo przydatną regułą formatowania warunkowego w programie Excel są reguły „góra / dół”. Umożliwia to analizę dowolnej długiej listy danych i uszeregowanie listy pod względem dowolnego z poniższych:

• 10 najlepszych przedmiotów
• Dolne 10 pozycji
• Top 10%
• Dolne 10%
• Powyżej średniej
• Poniżej średniej

Załóżmy na przykład, że masz listę bestsellerów New York Timesa wraz z wynikami recenzentów w arkuszu kalkulacyjnym. Możesz użyć reguł Góra / Dół, aby zobaczyć, które książki znalazły się w rankingu spośród 10 najlepszych lub 10 najgorszych z całej listy.

Aby to zrobić, po prostu wybierz całą listę, a następnie z menu Formatowanie warunkowe wybierz Reguły górne / dolne, a następnie wybierz 10 najważniejszych pozycji.

Nie jesteś ograniczony do 10 najlepszych przedmiotów. W oknie konfiguracji możesz to zmienić na dowolną liczbę, a także zmienić kolor komórek.

Możesz zastosować to samo podejście, co w poprzedniej sekcji, aby wyświetlić zarówno górną 10, jak i dolną 10, dodając drugą zasadę i podświetlając dolną 10 na czerwono, jednocześnie podświetlając górną 10 na zielono.

Gdy skończysz, na pierwszy rzut oka możesz zobaczyć najlepiej ocenione i najniżej ocenione na liście.

Użycie wyróżnienia dla najwyższych lub najniższych pozycji pozwala utrzymać porządek na liście zgodnie z upodobaniami, ale nadal możesz zobaczyć sortowanie (najwyższe lub najniższe) na pierwszy rzut oka. Jest to również niezwykle przydatne, gdy używasz również reguły powyżej średniej lub poniżej średniej.

Korzystanie z formatowania warunkowego paska danych

Inną bardzo przydatną regułą formatowania warunkowego są reguły formatowania paska danych. Pozwala to przekształcić komórki danych w wirtualny wykres słupkowy. Ta reguła wypełni komórkę procentem koloru na podstawie położenia punktu danych powyżej i poniżej ustawionych górnych i dolnych limitów.

Załóżmy na przykład, że dużo podróżujesz do pracy i rejestrujesz paliwo, które zużywasz podczas podróży do określonych stanów. Ta funkcja wypełnienia zastosuje wzór wypełnienia dla każdego punktu danych na podstawie maksymalnych i minimalnych punktów danych jako górnych i dolnych limitów. Możesz przekształcić swoje ogniwa danych paliwowych w wykres słupkowy za pomocą reguły formatowania warunkowego paska danych.

Aby to zrobić, wybierz całą kolumnę danych i wybierz Paski danych z Formatowanie warunkowe menu.

Możesz wybrać jedną z dwóch różnych opcji formatowania paska danych.

• Wypełnienie gradientem: spowoduje wypełnienie komórek zacienionym wzorem gradientu.
• Solid Fill: Wypełni komórki w jednolitym kolorze.

Aby to skonfigurować, po prostu wybierz kolumnę danych, do której chcesz zastosować wypełnienie, i wybierz opcję wypełnienia gradientem lub wypełnienia z menu Paski formatowania warunkowego.

Po zastosowaniu zobaczysz wypełnienie gradientowe lub pełne zastosowane do komórki każdego punktu danych.

Możliwość konwertowania danych arkuszy kalkulacyjnych na osadzony wykres słupkowy ma wiele przydatnych aplikacji.

Korzystanie z formatowania warunkowego w skali kolorów

Alternatywą dla korzystania z opcji wizualnego wykresu słupkowego zapewnianej przez opcje wypełnienia komórki jest formatowanie warunkowe skali kolorów funkcja. Wypełnia komórki gradientem, który reprezentuje, czy ten punkt danych znajduje się na dolnym lub górnym końcu ogólnego zakresu danych.

Aby zastosować to formatowanie, po prostu wybierz zakres komórek, do których chcesz zastosować formatowanie, a następnie wybierz kolor Skale kolorów w Formatowanie warunkowe menu.

Zastosowanie tego formatowania do zakresu komórek zapewnia podobną wizualizację jak opcja paska danych. Ale kolorowanie komórek daje lepszy przegląd tego, gdzie każdy punkt danych mieści się w zakresie.

Wybrana opcja naprawdę zależy od tego, jak wolisz prezentować swoje dane w arkuszu kalkulacyjnym. Taka skala kolorów jest przydatna, jeśli tak naprawdę nie chcesz, aby arkusz kalkulacyjny wyglądał jak wykres słupkowy. Ale nadal chcesz zobaczyć - na pierwszy rzut oka - gdzie w zakresie mieści się każdy punkt danych.

Jak korzystać z zestawów ikon w programie Excel

Jedną z najbardziej kreatywnych funkcji formatowania warunkowego w programie Excel są zestawy danych ikon. Pozwalają one zaimplementować ikonę do wizualizacji danych dotyczących danych na wykresie.

w Zestawy ikon menu w Formatowanie warunków, możesz wybierać z szerokiej gamy zestawów ikon.

Te zestawy ikon będą wyświetlane w każdej komórce danych, aby przedstawić, w jakim zakresie mieści się ogólny zakres danych. Jeśli wybierzesz strzałki, zobaczysz czerwoną strzałkę w dół dla niskich danych. Zielona strzałka w górę dla wysokich danych. Żółta strzałka pozioma dla danych średniego zasięgu.

Te kolory można całkowicie dostosować, jeśli chcesz. Aby dostosować, wystarczy wybrać kolumnę danych i wybrać Zarządzaj regułami w menu Formatowanie warunkowe.

Wszystkie te opcje formatowania warunkowego umożliwiają wizualizację danych w arkuszach kalkulacyjnych. Pomaga to w lepszym zrozumieniu tego, co próbujesz przedstawić za pomocą danych na pierwszy rzut oka.