Obejrzyj wideo - wyszukiwanie i wyróżnianie danych przy użyciu formatowania warunkowego
Jeśli pracujesz z dużymi zestawami danych, może zaistnieć potrzeba utworzenia funkcji wyszukiwania, która pozwoli szybko wyróżnić komórki/wiersze dla wyszukiwanego terminu.
Chociaż nie ma bezpośredniego sposobu, aby to zrobić w programie Excel, możesz utworzyć funkcję wyszukiwania za pomocą formatowania warunkowego.
Załóżmy na przykład, że masz zestaw danych, jak pokazano poniżej (na obrazku). Zawiera kolumny Nazwa produktu, Przedstawiciel handlowy i Kraj.
Teraz możesz użyć formatowania warunkowego, aby wyszukać słowo kluczowe (wprowadzając je w komórce C2) i podświetlić wszystkie komórki zawierające to słowo kluczowe.
Coś jak pokazano poniżej (gdzie wpisuję nazwę elementu w komórce B2 i naciskam Enter, cały wiersz zostaje podświetlony):
W tym samouczku pokażę, jak utworzyć tę funkcję wyszukiwania i wyróżniania w programie Excel.
W dalszej części samouczka przejdziemy nieco dalej i zobaczymy, jak sprawić, by był dynamiczny (aby wyróżniał się podczas pisania w polu wyszukiwania).
Kliknij tutaj, aby pobrać przykładowy plik i podążaj dalej.
Wyszukaj i zaznacz pasujące komórki
W tej sekcji. Pokażę ci, jak wyszukiwać i podświetlać tylko pasujące komórki w zbiorze danych.
Coś, jak pokazano poniżej:
Oto kroki, aby wyszukać i podświetlić wszystkie komórki, które mają pasujący tekst:
- Wybierz zestaw danych, do którego chcesz zastosować formatowanie warunkowe (w tym przykładzie A4:F19).
- Kliknij kartę Strona główna.
- W grupie Style kliknij Formatowanie warunkowe.
- W rozwijanych opcjach kliknij Nowa reguła.
- W oknie dialogowym „Nowa reguła formatowania” kliknij opcję „Użyj formuły, aby określić, które komórki należy sformatować”.
- Wprowadź następującą formułę: =A4=$B$1
- Kliknij przycisk „Formatuj…”.
- Określ formatowanie (aby podświetlić komórki pasujące do wyszukiwanego słowa kluczowego).
- Kliknij OK.
Teraz wpisz cokolwiek w komórce B1 i naciśnij enter. Podświetli pasujące komórki w zbiorze danych, które zawierają słowo kluczowe w B1.
Jak to działa?
Formatowanie warunkowe jest stosowane, gdy określona w nim formuła zwraca wartość TRUE.
W powyższym przykładzie sprawdzamy każdą komórkę za pomocą formuły =A4=$B$1
Formatowanie warunkowe sprawdza każdą komórkę i weryfikuje, czy zawartość komórki jest taka sama jak w komórce B1. Jeśli jest taki sam, formuła zwraca TRUE, a komórka zostaje podświetlona. Jeśli to nie to samo, formuła zwraca FALSE i nic się nie dzieje.
Kliknij tutaj, aby pobrać przykładowy plik i podążaj dalej.
Wyszukaj i podświetl wiersze z pasującymi danymi
Jeśli chcesz podświetlić cały wiersz zamiast tylko pasujących komórek, możesz to zrobić, nieco modyfikując formułę.
Poniżej znajduje się przykład, w którym cały wiersz jest podświetlany, jeśli typ produktu odpowiada temu w komórce B1.
Oto kroki, aby wyszukać i podświetlić cały wiersz:
- Wybierz zestaw danych, do którego chcesz zastosować formatowanie warunkowe (w tym przykładzie A4:F19).
- Kliknij kartę Strona główna.
- W grupie Style kliknij Formatowanie warunkowe.
- W rozwijanych opcjach kliknij Nowa reguła.
- W oknie dialogowym „Nowa reguła formatowania” kliknij opcję „Użyj formuły, aby określić, które komórki należy sformatować”.
- Wprowadź następującą formułę: =$B4=$B$1
- Kliknij przycisk „Formatuj…”.
- Określ formatowanie (aby podświetlić komórki pasujące do wyszukiwanego słowa kluczowego).
- Kliknij OK.
Powyższe kroki wyszukają określony element w zestawie danych, a jeśli znajdzie pasujący element, podświetli cały wiersz.
Zwróć uwagę, że będzie to sprawdzać tylko kolumnę pozycji. Jeśli wprowadzisz tutaj imię i nazwisko przedstawiciela handlowego, to nie zadziała. Jeśli chcesz, aby to działało dla nazwy przedstawiciela handlowego, musisz zmienić formułę na =$C4=$B$1
Uwaga: Powodem, dla którego podświetla cały wiersz, a nie tylko pasującą komórkę, jest to, że użyliśmy znaku $ przed odwołaniem do kolumny ($ B4). Teraz, gdy formatowanie warunkowe analizuje komórki w wierszu, sprawdza, czy wartość w kolumnie B tego wiersza jest równa wartości w komórce B1. Więc nawet gdy analizuje A4, B4 lub C4 i tak dalej, sprawdza tylko wartość B4 (ponieważ zablokowaliśmy kolumnę B za pomocą znaku dolara).
Możesz przeczytać więcej o odniesieniach bezwzględnych, względnych i mieszanych tutaj.
Wyszukaj i podświetl wiersze (na podstawie częściowego dopasowania)
W niektórych przypadkach możesz chcieć wyróżnić wiersze na podstawie częściowego dopasowania.
Na przykład, jeśli masz elementy takie jak Biała tablica, Zielona tablica i Szara tablica i chcesz je wszystkie wyróżnić na podstawie słowa Tablica, możesz to zrobić za pomocą funkcji SZUKAJ.
Coś, jak pokazano poniżej:
Oto kroki, aby to zrobić:
- Wybierz zestaw danych, do którego chcesz zastosować formatowanie warunkowe (w tym przykładzie A4:F19).
- Kliknij kartę Strona główna.
- W grupie Style kliknij Formatowanie warunkowe.
- W rozwijanych opcjach kliknij Nowa reguła.
- W oknie dialogowym „Nowa reguła formatowania” kliknij opcję „Użyj formuły, aby określić, które komórki należy sformatować”.
- Wprowadź następującą formułę: =ORAZ($B$1””,ISNUMBER(SEARCH($B$1,$B4)))
- Kliknij przycisk „Formatuj…”.
- Określ formatowanie (aby podświetlić komórki pasujące do wyszukiwanego słowa kluczowego).
- Kliknij OK.
Jak to działa?
- Funkcja SEARCH szuka szukanego ciągu/słowa kluczowego we wszystkich komórkach w wierszu. Zwraca błąd, jeśli słowo kluczowe wyszukiwania nie zostanie znalezione, i zwraca liczbę, jeśli znajdzie dopasowanie.
- Funkcja CZY.LICZBA konwertuje błąd na FAŁSZ, a wartości liczbowe na PRAWDA.
- Funkcja AND sprawdza dodatkowy warunek - komórka C2 nie powinna być pusta.
Tak więc teraz, gdy wpiszesz słowo kluczowe w komórce B1 i naciśniesz Enter, podświetla wszystkie wiersze zawierające komórki zawierające to słowo kluczowe.
Dodatkowa wskazówka: Jeśli chcesz rozróżniać wielkość liter, użyj funkcji ZNAJDŹ zamiast SZUKAJ.
Kliknij tutaj, aby pobrać przykładowy plik i podążaj dalej.
Funkcja dynamicznego wyszukiwania i podświetlania (podświetlane podczas pisania)
Korzystając z tych samych sztuczek formatowania warunkowego, które omówiono powyżej, możesz również pójść o krok dalej i uczynić go dynamicznym.
Na przykład możesz utworzyć pasek wyszukiwania, w którym pasujące dane zostaną podświetlone podczas pisania w pasku wyszukiwania.
Coś, jak pokazano poniżej:
Można to zrobić za pomocą kontrolek ActiveX i może być dobrą funkcją do wykorzystania podczas tworzenia raportów lub pulpitów nawigacyjnych.
Poniżej znajduje się film, w którym pokazuję, jak to stworzyć:
Czy ten samouczek okazał się przydatny? Daj mi znać swoje przemyślenia w sekcji komentarzy.
Możesz również polubić następujące samouczki programu Excel:
- Dynamiczny filtr programu Excel - wyodrębnia dane podczas pisania.
- Utwórz listę rozwijaną z sugestiami wyszukiwania.
- Tworzenie mapy ciepła w Excelu.
- Wyróżnij wiersze na podstawie wartości komórki w programie Excel.
- Podświetl aktywny wiersz i kolumnę w zakresie danych w programie Excel.
- Jak wyróżnić puste komórki w programie Excel.
