Formatowanie warunkowe za pomocą formuł w programie Excel
Domyślnie program Excel dostarczył nam już wiele typów formatowania warunkowego, które można wykonać z naszymi danymi, ale jeśli jakikolwiek użytkownik chce formatowania wykonanego według określonych kryteriów lub formuły, możemy to zrobić na karcie głównej sekcji formatowania warunkowego, gdy kliknij nową regułę, znajdziemy opcję formatowania warunkowego za pomocą formuł, w których możemy pisać formuły, które zdefiniują komórki do sformatowania.
Formatowanie warunkowe za pomocą Formuły to nic innego jak zmiana formatu komórek na podstawie warunku lub kryteriów podanych przez użytkownika. Mogłeś użyć formatowania warunkowego, aby wyróżnić najwyższą wartość w zakresie, a także zduplikowane wartości.
Dobrze, że mamy formuły w formatowaniu warunkowym w programie Excel. Wszystkie formuły powinny być logiczne; wynik będzie TRUE lub FALSE. Jeśli test logiczny przejdzie pomyślnie, otrzymamy formatowanie warunkowe, a jeśli test logiczny programu Excel się nie powiedzie, nic nie otrzymamy.
Przegląd
Pozwólcie, że przedstawię wam formatowanie warunkowe w oknie formuły.
W poniższej sekcji artykułu zobaczymy szeroką gamę przykładów stosowania formatowania warunkowego za pomocą formuł.
Jak korzystać z formatowania warunkowego programu Excel z formułami?
# 1 - Podświetl komórki, które mają wartości mniejsze niż 500
Załóżmy, że poniżej znajdują się numery sprzedaży, z którymi pracujesz, a poniżej cena sprzedaży na jednostkę.
- Krok 1: Przejdź do Formatowanie warunkowe i kliknij Nowa reguła.
- Krok 2: Teraz wybierz „ Użyj formuły, aby określić, które komórki sformatować”.
- Krok 3: Teraz w sekcji „ Formatuj wartości, w których formuła jest prawdziwa” zastosuj formułę jako A2 <500, a następnie kliknij Formatuj, aby zastosować formatowanie Excela.
- Krok 4: Wybierz format według własnego uznania.
- Krok 5: Teraz możesz zobaczyć podgląd formatu w polu podglądu.
- Krok 6: Teraz kliknij OK, aby zakończyć formatowanie. Teraz podświetlił wszystkie komórki, które mają liczbę <500.
# 2 - Podświetl jedną komórkę opartą na innej komórce
Możemy wyróżnić jedną komórkę na podstawie innej wartości komórki. Załóżmy, że mamy dane o nazwie produktu i cenie produktu w pierwszych dwóch kolumnach.
Z powyższej tabeli musimy zaznaczyć produkty, jeśli cena sprzedaży jest> 220.
- Krok 1: Wybierz asortyment produktów, a następnie przejdź do formatowania warunkowego i kliknij Nowa reguła.
- Krok 2: We wzorze zastosuj formułę jako B2> 220.
- Krok 3: Kliknij przycisk Format i zastosuj format zgodnie z życzeniem.
- Krok 5: Kliknij OK; mamy gotowe formatowanie.
# 3 - Podświetl wszystkie puste komórki w zakresie
Załóżmy, że poniżej znajdują się dane, które posiadasz.
W powyższych danych musisz zaznaczyć wszystkie komórki, które są puste lub puste. W formatowaniu warunkowym programu Excel musimy użyć formuły ISBLANK.
Zastosuj poniższą formułę w sekcji formuły.
Wybierz formatowanie według własnego uznania.
Kliknij ok, podświetli wszystkie puste komórki w wybranym zakresie.
# 4 - Użyj funkcji AND do podświetlania komórek
Rozważ poniższe dane dla tego wzoru.
Jeśli region jest centralny, a wartość sprzedaży> 500, musimy podświetlić cały wiersz.
- Krok 1: Najpierw wybierz całe dane.
- Krok 2: Tutaj musimy użyć funkcji AND excel, ponieważ musimy tutaj przetestować dwa warunki i oba warunki powinny być PRAWDZIWE. Zastosuj poniższą formułę.
- Krok 3: Kliknij Format.
- Krok 4: Przejdź do FILL i wybierz wymagany kolor i efekty.
- Krok 5: Kliknij, OK. Zobaczymy podświetlone wiersze, jeśli oba warunki są PRAWDZIWE.
# 5- Użyj funkcji OR do podświetlania komórek
Jako przykład rozważ poniższe dane.
W powyższym zakresie danych chcę podkreślić nazwy miast Bangalore i Mysore.
- Krok 1: Najpierw wybierz dane i przejdź do Formatowanie warunkowe, a następnie kliknij Nowa reguła.
- Krok 2: Ponieważ musimy podświetlić jedną z dwóch komórek wartości, zastosuj funkcję OR excel.
- Krok 3: Kliknij format i wybierz wymagany format.
- Krok 4: Po zastosowaniu formatowania kliknij OK, aby zakończyć zadanie. Formuła podświetli wszystkie komórki, które mają wartości Bangalore i Mysore.
# 6 - Użyj funkcji LICZ.JEŻELI, aby wyróżnić komórki
Załóżmy, że pracujesz z bazą danych klientów. Musisz zidentyfikować wszystkich klientów, których nazwy pojawiają się na liście więcej niż pięć razy. Poniżej znajdują się dane.
Wybierz dane i zastosuj formułę LICZ.JEŻELI w sekcji formuły.
Kliknij format i zastosuj wymagane formatowanie.
Teraz formuła podświetli wszystkie nazwy, jeśli liczba przekracza 5.
Amber to jedyna nazwa, która pojawia się na liście ponad pięć razy.
# 7 - Podświetl każdy alternatywny rząd
Możemy również mieć formułę, aby podświetlić każdy alternatywny wiersz danych w programie Excel. Załóżmy, że poniżej znajdują się dane, nad którymi pracujesz.
Wybierz dane i zastosuj poniższą formułę.
= MOD (WIERSZ (); 2)
Zastosuj format zgodnie z życzeniem i kliknij OK; mielibyśmy każdy alternatywny wiersz wyróżniony wzorem.
# 8 - Zaznacz każdą alternatywną kolumnę
Tak jak w podobny sposób wyróżniliśmy każdy alternatywny wiersz, możemy podświetlić każdą alternatywną kolumnę. Rozważ te same dane w przykładzie.
Zastosuj poniższą formułę.
= MOD (KOLUMNA (); 2)
Skopiuj powyższą formułę i zastosuj ją w sekcji formuły formatowania warunkowego.
Kliknij OK; podświetli alternatywny wiersz.
Jeśli chcesz opuścić pierwszą kolumnę i podświetlić drugą kolumnę, musisz użyć poniższej formuły.
Podświetliłby alternatywną kolumnę zaczynając od drugiej kolumny.
Rzeczy do zapamiętania
- Formatowanie warunkowe akceptuje tylko formuły logiczne, których wyniki mają tylko wartość PRAWDA lub FAŁSZ.
- Podgląd formatowania warunkowego to tylko wskazanie, jak wygląda formatowanie.
- Nigdy nie używaj bezwzględnych odniesień, jak we wzorze. Kiedy stosujesz formułę i jeśli wybierzesz komórkę bezpośrednio, stanie się ona odniesieniem bezwzględnym, musisz uczynić ją względnym odniesieniem w programie Excel.
