Vytvoření rozevíracího filtru k extrahování dat na základě výběru

Sledujte video - extrahujte data pomocí rozevíracího seznamu v aplikaci Excel

V tomto tutoriálu vám ukážu, jak vytvořit rozevírací filtr v aplikaci Excel, abyste mohli extrahovat data na základě výběru z rozevíracího seznamu.

Jak je znázorněno na obrázku níže, vytvořil jsem rozevírací seznam se jmény zemí. Jakmile z rozevíracího seznamu vyberu libovolnou zemi, data pro tuto zemi se extrahují vpravo.

Všimněte si toho, že jakmile vyberu Indii z rozevíracího filtru, budou extrahovány všechny záznamy pro Indii.

Extrahujte data z výběru rozevíracího seznamu v aplikaci Excel

Zde jsou kroky k vytvoření rozevíracího filtru, který bude extrahovat data pro vybranou položku:

1. Vytvořte jedinečný seznam položek.
2. Chcete-li zobrazit tyto jedinečné položky, přidejte rozevírací filtr.
3. Pomocí pomocných sloupců extrahujte záznamy pro vybranou položku.

Pojďme se ponořit do hloubky a zjistit, co je třeba udělat v každém z těchto kroků.

Vytvořte jedinečný seznam položek

I když by ve vaší datové sadě mohla existovat opakování položky, potřebujeme jedinečné názvy položek, abychom pomocí ní mohli vytvořit rozevírací filtr.

Ve výše uvedeném příkladu je prvním krokem získání jedinečného seznamu všech zemí.

Zde jsou kroky k získání jedinečného seznamu:

1. Vyberte všechny země a vložte je do jiné části listu.
2. Přejděte na Data -> Odebrat duplikáty.
3. V dialogovém okně Odebrat duplikáty vyberte sloupec, ve kterém máte seznam zemí. Získáte tak jedinečný seznam, jak je uvedeno níže.

Nyní použijeme tento jedinečný seznam k vytvoření rozevíracího seznamu.

Viz také: The Ultimate Guide to Find and remove Duplicates in Excel.

Vytvoření rozevíracího filtru

Zde jsou kroky k vytvoření rozevíracího seznamu v buňce:

1. Přejděte na Data -> Ověření dat.
2. V dialogovém okně Ověření dat vyberte kartu Nastavení.
3. Na kartě Nastavení vyberte v rozevíracím seznamu možnost „Seznam“ a v poli „Zdroj“ vyberte jedinečný seznam zemí, které jsme vygenerovali.
4. Klikněte na OK.

Cílem nyní je vybrat libovolnou zemi z rozevíracího seznamu, a to by nám mělo poskytnout seznam záznamů pro danou zemi.

K tomu bychom museli použít pomocné sloupce a vzorce.

Vytvořte pomocné sloupce k extrahování záznamů pro vybranou položku

Jakmile provedete výběr z rozevíracího seznamu, potřebujete Excel, aby automaticky identifikoval záznamy, které patří k vybrané položce.

To lze provést pomocí tří pomocných sloupců.

Zde jsou kroky k vytvoření pomocných sloupců:

• Sloupec pomocníka č. 1 - Zadejte sériové číslo všech záznamů (20 v tomto případě můžete použít funkci ROWS ()).
• Sloupec pomocníka č. 2 - Použijte tuto jednoduchou funkci IF funkce: = IF (D4 = $ H $ 2, E4, ””)
  • Tento vzorec kontroluje, zda se země v prvním řádku shoduje se zemí v rozevírací nabídce. Pokud tedy vyberu Indii, zkontroluje, zda má první řádek jako zemi Indii nebo ne. Pokud je to pravda, vrátí číslo řádku, jinak vrátí prázdné („“). Když nyní vybereme libovolnou zemi, zobrazí se pouze ta čísla řádků (ve druhém pomocném sloupci), ve kterých je vybraná země. (Pokud je například vybrána Indie, bude vypadat jako na obrázku níže).

Nyní potřebujeme extrahovat data pouze pro tyto řádky, které zobrazí číslo (protože to je řádek, který obsahuje danou zemi). Chceme však tyto záznamy bez mezer jeden po druhém. To lze provést pomocí třetího pomocného sloupce

• Sloupec třetího pomocníka - Použijte následující kombinaci funkcí IFERROR a SMALL:
  = IFERROR (MALÉ ($ F $ 4: $ F $ 23, E4), ””)

To by nám dalo něco, jak je uvedeno níže na obrázku:

Když teď máme číslo pohromadě, stačí data z tohoto čísla extrahovat. To lze snadno provést pomocí funkce INDEX (použijte tento vzorec v buňkách, kde potřebujete extrahovat výsledek):
= IFERROR (INDEX ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ J $ 3: J3)), ””)

Tento vzorec má 2 části:
INDEX - To extrahuje data na základě čísla řádku
IFERROR - Tato funkce vrátí prázdné hodnoty, pokud nejsou k dispozici žádná data

Zde je snímek toho, co nakonec získáte:

Nyní můžete původní data skrýt, pokud chcete. Původní data a extrahovaná data můžete mít také ve dvou různých listech.

Pokračuj. použijte tuto techniku ​​a udělejte dojem na svého šéfa a kolegy (malá paráda není nikdy na škodu).

Stáhněte si ukázkový soubor

Líbil se vám návod? Dejte mi vědět své myšlenky v sekci komentáře.

Také vám mohou být užitečné následující návody:

• Dynamický filtr aplikace Excel - extrahujte data při psaní.
• Dynamické vyhledávání v Excelu pomocí podmíněného formátování.
• Vytvořte dynamické rozevírací seznamy s návrhy vyhledávání.
• Jak extrahovat podřetězec v aplikaci Excel pomocí vzorců.
• Jak filtrovat buňky pomocí tučného písma v aplikaci Excel.