Wyróżnij wiersze na podstawie wartości komórki w programie Excel (formatowanie warunkowe)

Obejrzyj wideo - podświetlanie wierszy na podstawie wartości komórek w programie Excel

Jeśli zamiast tego wolisz czytać pisemną instrukcję, poniżej znajduje się samouczek.

Formatowanie warunkowe umożliwia formatowanie komórki (lub zakresu komórek) na podstawie zawartej w niej wartości.

Ale czasami zamiast tylko podświetlać komórkę, możesz podświetlić cały wiersz (lub kolumnę) na podstawie wartości w jednej komórce.

Aby dać ci przykład, poniżej mam zestaw danych, w którym podświetliłem wszystkie wiersze, w których imieniem przedstawiciela handlowego jest Bob.

W tym samouczku pokażę, jak wyróżniać wiersze na podstawie wartości komórki za pomocą formatowania warunkowego przy użyciu różnych kryteriów.

Kliknij tutaj aby pobrać przykładowy plik i postępować dalej.

Wyróżnij wiersze na podstawie kryteriów tekstowych

Załóżmy, że masz zestaw danych, jak pokazano poniżej, i chcesz wyróżnić wszystkie rekordy, w których przedstawicielem handlowym jest Robert.

Oto kroki, aby to zrobić:

1. Wybierz cały zestaw danych (w tym przykładzie A2:F17).
2. Kliknij kartę Strona główna.
3. W grupie Style kliknij Formatowanie warunkowe.
4. Kliknij „Nowe zasady”.
5. W oknie dialogowym „Nowa reguła formatowania” kliknij „Użyj formuły, aby określić, które komórki należy sformatować”.
6. W polu formuły wprowadź następującą formułę: =$C2=”Bob”
7. Kliknij przycisk „Format”.
8. W wyświetlonym oknie dialogowym ustaw kolor, w jakim wiersz ma być podświetlony.
9. Kliknij OK.

Spowoduje to podświetlenie wszystkich wierszy, w których nazwa przedstawiciela handlowego brzmi „Bob”.

Kliknij tutaj aby pobrać przykładowy plik i postępować dalej.

Jak to działa?

Formatowanie warunkowe sprawdza każdą komórkę pod kątem określonego przez nas warunku, czyli =$C2=”Bob”

Więc kiedy analizuje każdą komórkę w wierszu A2, sprawdzi, czy komórka C2 ma nazwę Bob, czy nie. Jeśli tak, ta komórka zostanie podświetlona, ​​w przeciwnym razie nie.

Zauważ, że sztuczka polega na użyciu znaku dolara ($) przed alfabetem kolumn ($C1). W ten sposób zablokowaliśmy kolumnę, aby zawsze była C. Więc nawet jeśli komórka A2 jest sprawdzana pod kątem formuły, sprawdza C2, a gdy sprawdzany jest warunek A3, sprawdza C3.

To pozwala nam wyróżnić cały wiersz przez formatowanie warunkowe.

Wyróżnij wiersze na podstawie kryteriów liczbowych

W powyższym przykładzie widzieliśmy, jak sprawdzić nazwę i podświetlić cały wiersz.

Możemy użyć tej samej metody, aby sprawdzić wartości liczbowe i podświetlić wiersze na podstawie warunku.

Załóżmy, że mam te same dane (jak pokazano poniżej) i chcę podświetlić wszystkie wiersze, w których ilość jest większa niż 15.

Oto kroki, aby to zrobić:

1. Wybierz cały zestaw danych (w tym przykładzie A2:F17).
2. Kliknij kartę Strona główna.
3. W grupie Style kliknij Formatowanie warunkowe.
4. Kliknij „Nowe zasady”.
5. W oknie dialogowym „Nowa reguła formatowania” kliknij „Użyj formuły, aby określić, które komórki należy sformatować”.
6. W polu formuły wprowadź następującą formułę: =$D2>=15
7. Kliknij przycisk „Format”. W wyświetlonym oknie dialogowym ustaw kolor, w jakim wiersz ma być podświetlony.
8. Kliknij OK.

Spowoduje to podświetlenie wszystkich wierszy, w których ilość jest większa lub równa 15.

Podobnie możemy również użyć tego, aby mieć również kryteria dotyczące daty.

Na przykład, jeśli chcesz podświetlić wszystkie wiersze, w których data jest po 10 lipca 2021-2022, możesz użyć poniższej formuły daty:

=$A2>DATA(2018,7,10)

Wyróżnij wiersze na podstawie wielu kryteriów (ORAZ/LUB)

Możesz także użyć wielu kryteriów, aby wyróżnić wiersze za pomocą formatowania warunkowego.

Na przykład, jeśli chcesz podświetlić wszystkie wiersze, w których nazwa przedstawiciela handlowego to „Bob”, a ilość jest większa niż 10, możesz to zrobić, wykonując następujące czynności:

1. Wybierz cały zestaw danych (w tym przykładzie A2:F17).
2. Kliknij kartę Strona główna.
3. W grupie Style kliknij Formatowanie warunkowe.
4. Kliknij „Nowe zasady”.
5. W oknie dialogowym „Nowa reguła formatowania” kliknij „Użyj formuły, aby określić, które komórki należy sformatować”.
6. W polu formuły wprowadź następującą formułę: =ORAZ($C2=”Bob”,$D2>10)
7. Kliknij przycisk „Format”. W wyświetlonym oknie dialogowym ustaw kolor, w jakim wiersz ma być podświetlony.
8. Kliknij OK.

W tym przykładzie podświetlane są tylko te wiersze, w których spełnione są oba warunki (odbywa się to za pomocą formuły AND).

Podobnie możesz również użyć warunku OR. Na przykład, jeśli chcesz wyróżnić wiersze, w których przedstawicielem handlowym jest Robert lub ilość jest większa niż 15, możesz użyć poniższej formuły:

=LUB($C2="Robert",$D2>15)

Kliknij tutaj aby pobrać przykładowy plik i postępować dalej.

Wyróżnij wiersze w innym kolorze w zależności od wielu warunków

Czasami możesz chcieć wyróżnić wiersze kolorem w zależności od warunku.

Na przykład możesz chcieć podświetlić wszystkie wiersze, w których ilość jest większa niż 20 na zielono i gdzie ilość jest większa niż 15 (ale mniejsza niż 20) na pomarańczowo.

Aby to zrobić, musisz utworzyć dwie reguły formatowania warunkowego i ustawić priorytet.

Oto kroki, aby to zrobić:

1. Wybierz cały zestaw danych (w tym przykładzie A2:F17).
2. Kliknij kartę Strona główna.
3. W grupie Style kliknij Formatowanie warunkowe.
4. Kliknij „Nowe zasady”.
5. W oknie dialogowym „Nowa reguła formatowania” kliknij „Użyj formuły, aby określić, które komórki należy sformatować”.
6. W polu formuły wprowadź następującą formułę: =$D2>15
7. Kliknij przycisk „Formatuj”. W wyświetlonym oknie dialogowym ustaw kolor na Pomarańczowy.
8. Kliknij OK.
9. W oknie dialogowym „Menedżer reguł formatowania warunkowego” kliknij „Nowa reguła”.
10. W oknie dialogowym „Nowa reguła formatowania” kliknij „Użyj formuły, aby określić, które komórki należy sformatować”.
11. W polu formuły wprowadź następującą formułę: =$D2>20
12. Kliknij przycisk „Formatuj”. W wyświetlonym oknie dialogowym ustaw kolor na Zielony.
13. Kliknij OK.
14. Kliknij Zastosuj (lub OK).

Powyższe kroki sprawią, że wszystkie wiersze z ilością większą niż 20 będą zielone, a te z większą niż 15 (ale mniejszą niż 20 - na pomarańczowo).

Zrozumienie porządku reguł:

W przypadku korzystania z wielu warunków ważne jest, aby upewnić się, że kolejność warunków jest prawidłowa.

W powyższym przykładzie warunek koloru zielonego jest powyżej warunku koloru pomarańczowego.

Gdyby było odwrotnie, wszystkie rzędy byłyby pokolorowane tylko na pomarańczowo.

Czemu?

Ponieważ wiersz, w którym ilość jest większa niż 20 (powiedzmy 23), spełnia oba nasze warunki (=$D2>15 i =$D2>20). A ponieważ stan Orange jest na górze, ma pierwszeństwo.

Możesz zmienić kolejność warunków za pomocą przycisków Przenieś w górę/w dół.

Kliknij tutaj aby pobrać przykładowy plik i postępować dalej.

Zaznacz wiersze, w których dowolna komórka jest pusta

Jeśli chcesz podświetlić wszystkie wiersze, w których dowolna z komórek jest pusta, musisz sprawdzić każdą komórkę za pomocą formatowania warunkowego.

Oto kroki, aby to zrobić:

1. Wybierz cały zestaw danych (w tym przykładzie A2:F17).
2. Kliknij kartę Strona główna.
3. W grupie Style kliknij Formatowanie warunkowe.
4. Kliknij „Nowe zasady”.
5. W oknie dialogowym „Nowa reguła formatowania” kliknij „Użyj formuły, aby określić, które komórki należy sformatować”.
6. W polu formuły wprowadź następującą formułę: =LICZ.JEŻELI($A2:$F2””)>0
7. Kliknij przycisk „Format”. W wyświetlonym oknie dialogowym ustaw kolor na Pomarańczowy.
8. Kliknij OK.

Powyższa formuła zlicza liczbę pustych komórek. Jeśli wynik jest większy niż 0, oznacza to, że w tym wierszu znajdują się puste komórki.

Jeśli którakolwiek z komórek jest pusta, podświetla cały wiersz.

Wyróżnij wiersze na podstawie wyboru rozwijanego

W omówionych dotychczas przykładach wszystkie warunki zostały określone za pomocą okna dialogowego formatowania warunkowego.

W tej części samouczka pokażę Ci, jak sprawić, by była dynamiczna (abyś mógł wpisać warunek w komórce w Excelu i automatycznie podświetli wiersze na jego podstawie).

Poniżej znajduje się przykład, w którym wybieram nazwę z listy rozwijanej, a wszystkie wiersze o tej nazwie zostają podświetlone:

Oto kroki, aby to stworzyć:

1. Utwórz listę rozwijaną w komórce A2. Tutaj użyłem nazwisk przedstawicieli handlowych, aby utworzyć listę rozwijaną. Oto szczegółowy przewodnik dotyczący tworzenia listy rozwijanej w programie Excel.
2. Wybierz cały zestaw danych (w tym przykładzie C2:H17).
3. Kliknij kartę Strona główna.
4. W grupie Style kliknij Formatowanie warunkowe.
5. Kliknij „Nowe zasady”.
6. W oknie dialogowym „Nowa reguła formatowania” kliknij „Użyj formuły, aby określić, które komórki należy sformatować”.
7. W polu formuły wprowadź następującą formułę: =$E2=$A$2
8. Kliknij przycisk „Formatuj”. W wyświetlonym oknie dialogowym ustaw kolor na Pomarańczowy.
9. Kliknij OK.

Teraz po wybraniu dowolnej nazwy z listy rozwijanej automatycznie podświetli wiersze, w których nazwa jest taka sama, jak wybrana z listy rozwijanej.

Chcesz dowiedzieć się więcej o wyszukiwaniu i wyróżnianiu w programie Excel? Sprawdź poniższe filmy.