Obejrzyj wideo - wyodrębnij dane za pomocą listy rozwijanej w programie Excel
W tym samouczku pokażę, jak utworzyć filtr rozwijany w programie Excel, aby wyodrębnić dane na podstawie wyboru z listy rozwijanej.
Jak widać na poniższym zdjęciu, stworzyłem listę rozwijaną z nazwami krajów. Gdy tylko wybiorę dowolny kraj z listy rozwijanej, dane dla tego kraju zostaną wyodrębnione po prawej stronie.
Zauważ, że jak tylko wybiorę Indie z listy rozwijanej, zostaną wyodrębnione wszystkie rekordy dla Indii.
Wyodrębnij dane z listy rozwijanej w programie Excel
Oto kroki, aby utworzyć filtr rozwijany, który wyodrębni dane dla wybranego elementu:
- Utwórz unikalną listę przedmiotów.
- Dodaj filtr rozwijany, aby wyświetlić te unikalne elementy.
- Użyj kolumn pomocniczych, aby wyodrębnić rekordy dla wybranego elementu.
Zanurzmy się głęboko i zobaczmy, co należy zrobić w każdym z tych kroków.
Stwórz unikalną listę przedmiotów
Chociaż w Twoim zbiorze danych mogą występować powtórzenia elementu, potrzebujemy unikalnych nazw elementów, abyśmy mogli utworzyć z nich filtr rozwijany.
W powyższym przykładzie pierwszym krokiem jest uzyskanie unikalnej listy wszystkich krajów.
Oto kroki, aby uzyskać unikalną listę:
- Wybierz wszystkie kraje i wklej je w innej części arkusza roboczego.
- Przejdź do Dane -> Usuń duplikaty.
- W oknie dialogowym Usuń duplikaty wybierz kolumnę, w której znajduje się lista krajów. To da ci unikalną listę, jak pokazano poniżej.
Teraz użyjemy tej unikalnej listy do stworzenia listy rozwijanej.
Zobacz też: Kompletny przewodnik po znajdowaniu i usuwaniu duplikatów w programie Excel.
Tworzenie filtra rozwijanego
Oto kroki, aby utworzyć listę rozwijaną w komórce:
- Przejdź do Dane -> Walidacja danych.
- W oknie dialogowym Sprawdzanie danych wybierz kartę Ustawienia.
- W zakładce Ustawienia wybierz „Lista” z rozwijanego menu, a w polu „Źródło” wybierz unikalną listę krajów, które wygenerowaliśmy.
- Kliknij OK.
Celem jest teraz wybranie dowolnego kraju z listy rozwijanej, co powinno dać nam listę rekordów dla tego kraju.
Aby to zrobić, musielibyśmy użyć kolumn pomocniczych i formuł.
Utwórz kolumny pomocnicze, aby wyodrębnić rekordy dla wybranego elementu
Zaraz po dokonaniu wyboru z listy rozwijanej program Excel automatycznie identyfikuje rekordy należące do wybranego elementu.
Można to zrobić za pomocą trzech kolumn pomocniczych.
Oto kroki, aby utworzyć kolumny pomocnicze:
- Kolumna pomocnika #1 - Wprowadź numer seryjny dla wszystkich rekordów (20 w tym przypadku możesz użyć do tego funkcji ROWS()).
- Kolumna pomocnika #2 - Użyj tej prostej funkcji JEŻELI: =JEŻELI(D4=$H$2,E4,””)
- Ta formuła sprawdza, czy kraj w pierwszym wierszu odpowiada krajowi w rozwijanym menu. Więc jeśli wybiorę Indie, to sprawdza, czy w pierwszym wierszu są Indie jako kraj, czy nie. Jeśli to prawda, zwraca ten numer wiersza, w przeciwnym razie zwraca puste („”). Teraz, gdy wybieramy dowolny kraj, wyświetlane są tylko te numery wierszy (w drugiej kolumnie pomocniczej), w których znajduje się wybrany kraj. (Na przykład, jeśli wybrano Indie, będzie to wyglądać jak na poniższym zdjęciu).
Teraz musimy wyodrębnić dane tylko dla tych wierszy, które wyświetlają liczbę (ponieważ jest to wiersz zawierający ten kraj). Jednak chcemy, aby te rekordy były bez spacji jeden po drugim. Można to zrobić za pomocą trzeciej kolumny pomocniczej
- Trzecia kolumna pomocnika - Użyj następującej kombinacji funkcji JEŻELI.BŁĄD i MAŁY:
=JEŻELIBŁĄD(MAŁA($F$4:$F$23;E4)"")
To dałoby nam coś, jak pokazano poniżej na zdjęciu:
Teraz, gdy mamy liczbę razem, musimy tylko wyodrębnić dane w tej liczbie. Można to łatwo zrobić za pomocą funkcji INDEKS (użyj tej formuły w komórkach, w których chcesz wyodrębnić wynik):
=JEŻELIBŁĄD(INDEKS($B$4:$D$23;$G4;KOLUMNY($J$3:J3));””)
Ta formuła składa się z 2 części:
INDEKS - To wyodrębnia dane na podstawie numeru wiersza
JEŻELIBŁĄD - Ta funkcja zwraca puste, gdy nie ma danych
Oto migawka tego, co w końcu otrzymujesz:
Możesz teraz ukryć oryginalne dane, jeśli chcesz. Możesz również mieć oryginalne dane i wyodrębnione dane w dwóch różnych arkuszach roboczych.
Zacząć robić. użyj tej techniki i zaimponuj szefowi i współpracownikom (odrobina popisu nigdy nie jest zła).
Pobierz przykładowy plik
Podobał Ci się samouczek? Daj mi znać swoje przemyślenia w sekcji komentarzy.
Przydatne mogą być również następujące samouczki:
- Dynamiczny filtr programu Excel - wyodrębniaj dane podczas pisania.
- Wyszukiwanie dynamiczne w programie Excel przy użyciu formatowania warunkowego.
- Utwórz dynamiczne menu rozwijane z sugestiami wyszukiwania.
- Jak wyodrębnić podciąg w programie Excel za pomocą formuł.
- Jak filtrować komórki za pomocą pogrubionego formatowania czcionki w programie Excel.
