Jak filtrować dane z listy rozwijanej w programie Excel?
W programie Excel większość z nas może filtrować dane za pomocą funkcji Filtruj. Ale czy kiedykolwiek próbowałeś filtrować dane z listy rozwijanej? Na przykład, gdy wybieram jeden element z rozwijanej listy, chcę, aby odpowiadające mu wiersze zostały odfiltrowane, jak pokazano na poniższym zrzucie ekranu. W tym artykule omówię, jak filtrować dane za pomocą rozwijanej listy w jednym lub dwóch arkuszach.
Filtruj dane z listy rozwijanej w jednym arkuszu za pomocą formuł pomocniczych
Filtruj dane z listy rozwijanej w dwóch arkuszach z kodem VBA
Filtruj dane z listy rozwijanej w jednym arkuszu za pomocą formuł pomocniczych
Aby filtrować dane z rozwijanej listy, możesz utworzyć kilka pomocniczych kolumn z formułami, wykonaj kolejno następujące kroki:
1. Najpierw wstaw listę rozwijaną. Kliknij komórkę, do której chcesz wstawić listę rozwijaną, a następnie kliknij Dane > Walidacja danych > Walidacja danychzobacz zrzut ekranu:
2. W wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia kartę, wybierz Lista z Dopuszczać listę rozwijaną, a następnie kliknij 
przycisk, aby wybrać listę danych, na podstawie której chcesz utworzyć listę rozwijaną, zobacz zrzut ekranu:
3. A następnie kliknij OK przycisk, lista rozwijana jest wstawiana od razu i wybierz jedną pozycję z listy rozwijanej, a następnie wprowadź tę formułę: = WIERSZE (2 $ A $: A2) (A2 jest pierwszą komórką w kolumnie, która zawiera wartość listy rozwijanej) do komórki D2, a następnie przeciągnij uchwyt wypełniania w dół do komórek, aby zastosować tę formułę, patrz zrzut ekranu:
4. Kontynuuj wprowadzanie tej formuły: = JEŻELI (A2 = $ H $ 2, D2, „”) do komórki E2, a następnie przeciągnij uchwyt wypełniania w dół, aby wypełnić tę formułę, patrz zrzut ekranu:
Note: W powyższym wzorze ：A2 to pierwsza komórka w kolumnie, która zawiera wartość listy rozwijanej ，H2 to komórka, w której została umieszczona lista rozwijana, D2 to pierwsza formuła kolumny pomocniczej.
5. A następnie wpisz tę formułę: = JEŻELI BŁĄD (MAŁE (2 E $: 17 E $, D2); „”) do komórki F2, a następnie przeciągnij uchwyt wypełniania w dół do komórek, aby wypełnić tę formułę, patrz zrzut ekranu:
Note: W powyższym wzorze: E2: E17 to drugie komórki formuły pomocniczej, D2 to pierwsza komórka w pierwszej kolumnie z formułą pomocniczą.
6. Po wstawieniu pomocniczych kolumn z formułami należy umieścić przefiltrowany wynik w innej lokalizacji, zastosuj następującą formułę: =IFERROR(INDEX($A$2:$C$17,$F2,COLUMNS($J$2:J2)),"") do komórki J2, a następnie przeciągnij uchwyt wypełniania z J2 do L2, a pierwszy rekord danych oparty na rozwijanej liście został wyodrębniony, patrz zrzut ekranu:
Note: W powyższym wzorze: A2: C17 to oryginalne dane, które chcesz przefiltrować, F2 to trzecia pomocnicza kolumna z formułą, J2 to komórka, w której chcesz wyświetlić wynik filtru.
7. A następnie przeciągnij uchwyt wypełniania w dół do komórek, aby wyświetlić wszystkie odpowiednie przefiltrowane rekordy, patrz zrzut ekranu:
8. Odtąd, gdy wybierzesz jedną pozycję z rozwijanej listy, wszystkie wiersze oparte na tym wyborze są od razu filtrowane, patrz zrzut ekranu:
Filtruj dane z listy rozwijanej w dwóch arkuszach z kodem VBA
Jeśli Twoja komórka listy rozwijanej w Arkuszu1 i przefiltrowane dane w Arkuszu2, po wybraniu jednego elementu z listy rozwijanej, inny arkusz zostanie odfiltrowany. Jak mogłeś zakończyć tę pracę w Excelu?
Poniższy kod VBA może wyświadczyć ci przysługę, zrób tak:
1. Kliknij prawym przyciskiem myszy kartę arkusza zawierającą komórkę listy rozwijanej, a następnie wybierz Wyświetl kod z menu kontekstowego w otwartym pliku Microsoft Visual Basic dla aplikacji w oknie, skopiuj i wklej następujący kod do pustego modułu:
Kod VBA: Filtruj dane z listy rozwijanej w dwóch arkuszach:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
On Error Resume Next
If Not Intersect(Range("A2"), Target) Is Nothing Then
Application.EnableEvents = False
If Range("A2").Value = "" Then
Worksheets("Sheet2").ShowAllData
Else
Worksheets("Sheet2").Range("A2").AutoFilter 1, Range("A2").Value
End If
Application.EnableEvents = True
End If
End Sub
Note: W powyższym kodzie: A2 to komórka zawierająca listę rozwijaną, a Sheet2 czy arkusz zawiera dane, które chcesz filtrować. Numer 1 w scenariuszu: Autofiltr 1 to numer kolumny, na podstawie której chcesz filtrować. Możesz je zmienić według swoich potrzeb.
2. Odtąd, gdy wybierzesz jeden element z rozwijanej listy w Sheet1, a odpowiednie dane zostaną odfiltrowane w Sheet2, zobacz zrzut ekranu:
Najlepsze narzędzia biurowe
Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas. Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...
Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę
- Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
- Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
- Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
