Formatowanie warunkowe w programie Excel: ostateczny przewodnik z przykładami

Formatowanie warunkowe to jedna z najprostszych, ale zaawansowanych funkcji arkuszy kalkulacyjnych programu Excel.

Jak sama nazwa wskazuje, możesz użyć formatowania warunkowego w programie Excel, gdy chcesz wyróżnić komórki spełniające określony warunek.

Daje możliwość szybkiego dodania warstwy analizy wizualnej do zestawu danych. Możesz tworzyć mapy ciepła, wyświetlać rosnące / malejące ikony, bąbelki Harveya i wiele więcej za pomocą formatowania warunkowego w programie Excel.

Korzystanie z formatowania warunkowego w programie Excel (przykłady)

W tym samouczku pokażę Ci siedem niesamowitych przykładów użycia formatowania warunkowego w Excelu:

• Szybko identyfikuj duplikaty za pomocą formatowania warunkowego w programie Excel.
• Wyróżnij komórki o wartości większej/mniejszej niż liczba w zestawie danych.
• Podświetlanie 10 górnych/dolnych (lub 10%) wartości w zestawie danych.
• Podświetlanie błędów / pustych miejsc za pomocą formatowania warunkowego w programie Excel.
• Tworzenie map ciepła przy użyciu formatowania warunkowego w programie Excel.
• Podświetl każdy n-ty wiersz/kolumnę za pomocą formatowania warunkowego.
• Wyszukaj i wyróżnij za pomocą formatowania warunkowego w programie Excel.

1. Szybko zidentyfikuj duplikaty

Formatowanie warunkowe w programie Excel może służyć do identyfikowania duplikatów w zestawie danych.

Oto jak możesz to zrobić:

• Wybierz zestaw danych, w którym chcesz wyróżnić duplikaty.
• Przejdź do Strona główna -> Formatowanie warunkowe -> Podświetlanie reguł komórek -> Zduplikowane wartości.
• W oknie dialogowym Zduplikowane wartości upewnij się, że w lewym menu rozwijanym wybrano opcję Duplikuj. Możesz określić format, który ma zostać zastosowany, korzystając z prawego menu rozwijanego. Istnieje kilka istniejących formatów, których można użyć, lub określić własny format za pomocą opcji Format niestandardowy.
• Kliknij OK.

Spowoduje to natychmiastowe podświetlenie wszystkich komórek, które mają duplikat w wybranym zestawie danych. Twój zestaw danych może znajdować się w jednej kolumnie, wielu kolumnach lub w nieciągłym zakresie komórek.

Zobacz też: Kompletny przewodnik po znajdowaniu i usuwaniu duplikatów w programie Excel.

2. Zaznacz komórki o wartości większej/mniejszej niż liczba

Możesz użyć formatowania warunkowego w programie Excel, aby szybko wyróżnić komórki zawierające wartości większe/mniejsze niż określona wartość. Na przykład podświetlenie wszystkich komórek o wartości sprzedaży mniejszej niż 100 milionów lub podświetlenie komórek ze znakami mniejszymi niż próg przejścia.

Oto kroki, aby to zrobić:

• Wybierz cały zbiór danych.
• Przejdź do Strona główna -> Formatowanie warunkowe -> Podświetlanie reguł komórki -> Większe niż… / Mniejsze niż…
• W zależności od wybranej opcji (większe lub mniejsze niż) otworzy się okno dialogowe. Załóżmy, że wybierasz opcję „Większe niż”. W oknie dialogowym wprowadź numer w polu po lewej stronie. Celem jest wyróżnienie komórek, które mają liczbę większą niż ta określona liczba.

• Określ format, który ma być zastosowany do komórek spełniających warunek, korzystając z listy rozwijanej po prawej stronie. Istnieje kilka istniejących formatów, których można użyć, lub określić własny format za pomocą opcji Format niestandardowy.
• Kliknij OK.

Spowoduje to natychmiastowe podświetlenie wszystkich komórek o wartościach większych niż 5 w zestawie danych.Uwaga: Jeśli chcesz wyróżnić wartości większe niż równe 5, powinieneś ponownie zastosować formatowanie warunkowe z kryterium „Równe”.

Ten sam proces można wykonać, aby podświetlić komórki o wartości mniejszej niż określone wartości.

3. Podświetlenie Top/Bottom 10 (lub 10%)

Formatowanie warunkowe w programie Excel może szybko zidentyfikować 10 najważniejszych elementów lub pierwsze 10% z zestawu danych. Może to być przydatne w sytuacjach, w których chcesz szybko zobaczyć najlepszych kandydatów według wyników lub najwyższych wartości transakcji w danych sprzedaży.

Podobnie możesz również szybko zidentyfikować 10 najniższych pozycji lub najniższe 10% w zestawie danych.

Oto kroki, aby to zrobić:

• Wybierz cały zbiór danych.
• Przejdź do Strona główna -> Formatowanie warunkowe -> Reguły górne/dolne -> 10 najlepszych pozycji (lub %) / 10 najniższych pozycji (lub %).
• W zależności od tego, co wybierzesz, otworzy się okno dialogowe. Załóżmy, że wybrałeś 10 najlepszych pozycji, a następnie otworzy się okno dialogowe, jak pokazano poniżej:
• Określ format, który ma być zastosowany do komórek spełniających warunek, korzystając z listy rozwijanej po prawej stronie. Istnieje kilka istniejących formatów, których można użyć, lub określić własny format za pomocą opcji Format niestandardowy.
• Kliknij OK.

Spowoduje to natychmiastowe wyróżnienie 10 najważniejszych elementów w wybranym zbiorze danych. Zauważ, że działa to tylko w przypadku komórek, które mają w sobie wartość liczbową.

Ponadto, jeśli masz mniej niż 10 komórek w zestawie danych i wybierzesz opcje podświetlenia Top 10 elementów/Bottom 10 Items, wszystkie komórki zostaną podświetlone.

Oto kilka przykładów działania formatowania warunkowego:

4. Podświetlanie błędów/pustych miejsc

Jeśli pracujesz z dużą ilością danych liczbowych i obliczeń w programie Excel, wiesz, jak ważne jest identyfikowanie i traktowanie komórek, które zawierają błędy lub są puste. Jeśli komórki te zostaną użyte w dalszych obliczeniach, może to prowadzić do błędnych wyników.

Formatowanie warunkowe w programie Excel może pomóc w szybkim identyfikowaniu i wyróżnianiu komórek zawierających błędy lub pustych.

Załóżmy, że mamy zbiór danych, jak pokazano poniżej:

Ten zestaw danych zawiera pustą komórkę (A4) i błędy (A5 i A6).

Oto kroki, aby podświetlić komórki, które są puste lub zawierają błędy:

• Wybierz zestaw danych, w którym chcesz wyróżnić puste komórki i komórki z błędami.
• Przejdź do Strona główna -> Formatowanie warunkowe -> Nowa reguła.
• W oknie dialogowym Nowa reguła formatowania wybierz Użyj formuły, aby określić, które komórki mają zostać sformatowane.
• Wprowadź następującą formułę w polu w sekcji „Edytuj opis reguły”:
  =LUB(CZY.PUSTE(A1);BŁĄD(A1))
  • Powyższa formuła sprawdza wszystkie komórki pod kątem dwóch warunków - czy jest pusta, czy nie i czy zawiera błąd, czy nie. Jeśli którykolwiek z warunków ma wartość TRUE, zwraca TRUE.
• Ustaw format, który chcesz zastosować do komórek, które są puste lub zawierają błędy. Aby to zrobić, kliknij przycisk Format. Otworzy się okno dialogowe „Formatuj komórki”, w którym możesz określić format.
• Kliknij OK.

Spowoduje to natychmiastowe podświetlenie wszystkich komórek, które są puste lub zawierają błędy.

Notatka: Nie musisz używać całego zakresu A1:A7 w formule w formatowaniu warunkowym. Powyższa formuła wykorzystuje tylko A1. Po zastosowaniu tej formuły do ​​całego zakresu program Excel sprawdza jedną komórkę na raz i dostosowuje odwołanie. Na przykład, gdy sprawdza A1, używa formuły =OR(ISBLANK(A1),ISERROR(A1)). Kiedy sprawdza komórkę A2, używa formuły =OR(ISBLANK(A2),ISERROR(A2)). Automatycznie dostosowuje odniesienie (ponieważ są to odniesienia względne) w zależności od analizowanej komórki. Nie musisz więc pisać osobnej formuły dla każdej komórki. Excel jest na tyle sprytny, że sam zmienia odwołanie do komórki 🙂

Zobacz też: Używanie IFERROR i ISERROR do obsługi błędów w programie Excel.

5. Tworzenie map ciepła

Mapa cieplna to wizualna reprezentacja danych, w której kolor reprezentuje wartość w komórce. Na przykład możesz utworzyć mapę cieplną, w której komórka z najwyższą wartością ma kolor zielony, a wraz ze spadkiem wartości następuje przesunięcie w kierunku koloru czerwonego.

Coś, jak pokazano poniżej:

Powyższy zestaw danych ma wartości od 1 do 100. Komórki są wyróżniane na podstawie zawartej w nim wartości. 100 otrzymuje kolor zielony, 1 otrzymuje kolor czerwony.

Oto kroki tworzenia map ciepła przy użyciu formatowania warunkowego w programie Excel.

• Wybierz zestaw danych.
• Przejdź do Home -> Formatowanie warunkowe -> Skale kolorów i wybierz jeden ze schematów kolorów.

Gdy tylko klikniesz ikonę mapy cieplnej, zastosuje formatowanie do zbioru danych. Do wyboru jest wiele gradientów kolorów. Jeśli nie jesteś zadowolony z istniejących opcji kolorów, możesz wybrać więcej reguł i określić żądany kolor.

Uwaga: W podobny sposób można również zastosować paski danych i zestawy ikon.

6. Podświetl każdy inny wiersz/kolumnę

Możesz podświetlić alternatywne wiersze, aby zwiększyć czytelność danych.

Są to tak zwane linie zebry i mogą być szczególnie przydatne podczas drukowania danych.

Teraz są dwa sposoby tworzenia tych linii zebry. Najszybszym sposobem jest przekonwertowanie danych tabelarycznych na tabelę Excel. Automatycznie zastosował kolor do alternatywnych wierszy. Więcej na ten temat przeczytasz tutaj.

Innym sposobem jest użycie formatowania warunkowego.

Załóżmy, że masz zbiór danych, jak pokazano poniżej:

Oto kroki, aby wyróżnić alternatywne wiersze przy użyciu formatowania warunkowego w programie Excel.

• Wybierz zbiór danych. W powyższym przykładzie wybierz A2:C13 (z wyłączeniem nagłówka). Jeśli chcesz dołączyć również nagłówek, wybierz cały zestaw danych.
• Otwórz okno dialogowe Formatowanie warunkowe (Strona główna-> Formatowanie warunkowe-> Nowa reguła) [Skrót klawiaturowy - Alt + O + D].
• W oknie dialogowym wybierz „Użyj formuły, aby określić, które komórki do sformatowania” okno dialogowe.
• Wprowadź następującą formułę w polu w sekcji „Edytuj opis reguły”:
  =ODD(WIERSZ())

• Powyższa formuła sprawdza wszystkie komórki i jeśli numer ROW komórki jest nieparzysty, zwraca TRUE. Określony format warunkowy zostanie zastosowany do wszystkich komórek, które zwracają TRUE.
• Ustaw format, który chcesz zastosować do komórek, które są puste lub zawierają błędy. Aby to zrobić, kliknij przycisk Format. Otworzy się okno dialogowe „Formatuj komórki”, w którym możesz określić format.
• Kliknij OK.

Otóż ​​to! Alternatywne wiersze w zestawie danych zostaną podświetlone.

W wielu przypadkach możesz użyć tej samej techniki. Wystarczy użyć odpowiedniej formuły w formatowaniu warunkowym. Oto kilka przykładów:

• Wyróżnij alternatywne wiersze parzyste: =ISEVEN(ROW())
• Zaznacz alternatywne wiersze dodawania: =ISODD(ROW())
• Podświetlaj co 3 wiersz: =MOD(ROW(),3)=0

7. Wyszukaj i zaznacz dane za pomocą formatowania warunkowego

Ten jest nieco zaawansowanym użyciem formatowania warunkowego. To sprawi, że będziesz wyglądać jak gwiazda rocka Excela.

Załóżmy, że masz zestaw danych, jak pokazano poniżej, z nazwą produktu, przedstawicielem handlowym i geografią. Pomysł polega na wpisaniu ciągu w komórce C2, a jeśli pasuje do danych w dowolnej komórce (komórkach), powinno to zostać podświetlone. Coś, jak pokazano poniżej:

Oto kroki, aby utworzyć tę funkcję wyszukiwania i wyróżniania:

• Wybierz zbiór danych.
• Przejdź do Strona główna -> Formatowanie warunkowe -> Nowa reguła (Skrót klawiaturowy - Alt + O + D).
• W oknie dialogowym Nowa reguła formatowania wybierz opcję „Użyj formuły, aby określić, które komórki należy sformatować”.
• Wprowadź następującą formułę w polu w sekcji „Edytuj opis reguły”:
  =ORAZ($C$2””,$C$2=B5)
• Ustaw format, który chcesz zastosować do komórek, które są puste lub zawierają błędy. Aby to zrobić, kliknij przycisk Format. Otworzy się okno dialogowe „Formatuj komórki”, w którym możesz określić format.
• Kliknij OK.

Otóż ​​to! Teraz, gdy wpiszesz cokolwiek w komórce C2 i naciśniesz Enter, podświetli wszystkie pasujące komórki.

Jak to działa?

Formuła używana w formatowaniu warunkowym ocenia wszystkie komórki w zestawie danych. Załóżmy, że w komórce C2 wchodzisz do Japonii. Teraz Excel oceni formułę dla każdej komórki.

Formuła zwróci TRUE dla komórki, gdy spełnione są dwa warunki:

• Komórka C2 nie jest pusta.
• Zawartość komórki C2 dokładnie odpowiada zawartości komórki w zestawie danych.

W związku z tym wszystkie komórki zawierające tekst Japonia zostają podświetlone.

Pobierz przykładowy plik

Możesz użyć tej samej logiki do tworzenia odmian, takich jak:

• Zaznacz cały wiersz zamiast komórki.
• Wyróżnij, nawet jeśli występuje częściowe dopasowanie.
• Podświetlaj komórki/wiersze podczas pisania (dynamicznie) [Pokochasz tę sztuczkę :)].

Jak usunąć formatowanie warunkowe w programie Excel?

Po zastosowaniu formatowanie warunkowe pozostaje na miejscu, chyba że usuniesz je ręcznie. Najlepszym rozwiązaniem jest stosowanie formatowania warunkowego tylko do tych komórek, w których jest to potrzebne.

Ponieważ jest niestabilny, może prowadzić do powolnego skoroszytu programu Excel.

Aby usunąć formatowanie warunkowe:

• Zaznacz komórki, z których chcesz usunąć formatowanie warunkowe.
• Przejdź do Strona główna -> Formatowanie warunkowe -> Wyczyść reguły -> Wyczyść reguły z wybranych komórek.
  • Jeśli chcesz usunąć formatowanie warunkowe z całego arkusza, wybierz opcję Wyczyść reguły z całego arkusza.

Ważne rzeczy, które należy wiedzieć o formatowaniu warunkowym w programie Excel

• Formatowanie warunkowe jest nietrwałe. Może to prowadzić do powolnego skoroszytu. Używaj go tylko w razie potrzeby.
• Podczas kopiowania wklejanych komórek zawierających formatowanie warunkowe kopiowane jest również formatowanie warunkowe.
• Jeśli zastosujesz wiele reguł do tego samego zestawu komórek, wszystkie reguły pozostaną aktywne. W przypadku jakiegokolwiek nakładania się, preferowana jest reguła zastosowana jako ostatnia. Możesz jednak zmienić kolejność, zmieniając kolejność w oknie dialogowym Zarządzaj regułami.