Vytvorenie rozbaľovacieho filtra na extrahovanie údajov na základe výberu

Sledujte video - extrahujte údaje pomocou rozbaľovacieho zoznamu v programe Excel

V tomto tutoriále vám ukážem, ako vytvoriť rozbaľovací filter v programe Excel, aby ste z rozbaľovacieho zoznamu mohli extrahovať údaje na základe výberu.

Ako je znázornené na obrázku nižšie, vytvoril som rozbaľovací zoznam s názvami krajín. Hneď ako z rozbaľovacej ponuky vyberiem ľubovoľnú krajinu, údaje pre túto krajinu sa extrahujú vpravo.

Všimnite si toho, že hneď ako z rozbaľovacieho filtra vyberiem Indiu, budú extrahované všetky záznamy pre Indiu.

Extrahujte údaje z výberu rozbaľovacieho zoznamu v programe Excel

Tu je postup, ako vytvoriť rozbaľovací filter, ktorý bude extrahovať údaje pre vybratú položku:

1. Vytvorte jedinečný zoznam položiek.
2. Ak chcete zobraziť tieto jedinečné položky, pridajte rozbaľovací filter.
3. Pomocou stĺpcov pomocníka extrahujte záznamy pre vybratú položku.

Poďme sa ponoriť do hĺbky a uvidíme, čo je potrebné urobiť v každom z týchto krokov.

Vytvorte jedinečný zoznam položiek

Aj keď sa vo vašej množine údajov môžu opakovať položky, potrebujeme jedinečné názvy položiek, aby sme pomocou nich mohli vytvoriť rozbaľovací filter.

Vo vyššie uvedenom príklade je prvým krokom získanie jedinečného zoznamu všetkých krajín.

Nasleduje postup, ako získať jedinečný zoznam:

1. Vyberte všetky krajiny a prilepte ho do inej časti pracovného hárka.
2. Prejdite na položku Údaje -> Odstrániť duplikáty.
3. V dialógovom okne Odstrániť duplikáty vyberte stĺpec, v ktorom máte zoznam krajín. Získate tak jedinečný zoznam, ako je uvedené nižšie.

Teraz použijeme tento jedinečný zoznam na vytvorenie rozbaľovacieho zoznamu.

Pozri tiež: Ultimate Guide to Find and remove Duplicates in Excel.

Vytvorenie rozbaľovacieho filtra

Tu je postup, ako vytvoriť rozbaľovací zoznam v bunke:

1. Prejdite na položku Údaje -> Overenie údajov.
2. V dialógovom okne Overenie údajov vyberte kartu Nastavenia.
3. Na karte Nastavenia vyberte v rozbaľovacom zozname položku „Zoznam“ a v poli „Zdroj“ vyberte jedinečný zoznam krajín, ktoré sme vygenerovali.
4. Kliknite na tlačidlo OK.

Cieľom je teraz vybrať z rozbaľovacieho zoznamu ľubovoľnú krajinu a to by nám malo poskytnúť zoznam záznamov pre danú krajinu.

Na to by sme museli použiť pomocné stĺpce a vzorce.

Vytvorte pomocné stĺpce na extrahovanie záznamov pre vybranú položku

Hneď ako vyberiete výber z rozbaľovacej ponuky, potrebujete, aby Excel automaticky identifikoval záznamy, ktoré patria k vybranej položke.

To sa dá dosiahnuť pomocou troch pomocných stĺpcov.

Tu sú kroky na vytvorenie pomocných stĺpcov:

• Stĺpec pomocníka č. 1 - Zadajte sériové číslo všetkých záznamov (v tomto prípade 20 môžete použiť funkciu ROWS ()).
• Stĺpec pomocníka č. 2 - Použite túto jednoduchú funkciu funkcie IF: = IF (D4 = $ H $ 2, E4, ””)
  • Tento vzorec kontroluje, či sa krajina v prvom riadku zhoduje s krajinou v rozbaľovacej ponuke. Ak teda vyberiem Indiu, skontroluje, či je v prvom riadku ako krajina India alebo nie. Ak je to pravda, vráti číslo riadku, v opačnom prípade vráti prázdne miesto („“). Keď teraz vyberieme ľubovoľnú krajinu, zobrazia sa iba tie čísla riadkov (v druhom stĺpci pomocníka), v ktorých je vybratá krajina. (Ak je napríklad vybraná India, bude to vyzerať ako na obrázku nižšie).

Teraz potrebujeme extrahovať údaje iba pre tieto riadky, ktoré zobrazujú číslo (pretože je to riadok, ktorý obsahuje danú krajinu). Chceme však tie záznamy bez medzier jeden po druhom. To je možné vykonať pomocou tretieho pomocného stĺpca

• Tretí stĺpec pomocníka - Použite nasledujúcu kombináciu funkcií IFERROR a SMALL:
  = IFERROR (MALÉ ($ F $ 4: $ F $ 23, E4), ””)

To by nám dalo niečo, ako je uvedené nižšie na obrázku:

Teraz, keď máme číslo spolu, stačí extrahovať údaje v tomto čísle. To sa dá ľahko vykonať pomocou funkcie INDEX (tento vzorec použite v bunkách, kde chcete extrahovať výsledok):
= IFERROR (INDEX ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ J $ 3: J3)), ””)

Tento vzorec má 2 časti:
INDEX - Týmto sa extrahujú údaje na základe čísla riadka
IFERROR - Táto funkcia vráti prázdne pole, ak nie sú k dispozícii žiadne údaje

Tu je prehľad toho, čo nakoniec získate:

Teraz môžete pôvodné údaje skryť, ak chcete. Pôvodné údaje a extrahované údaje môžete mať aj v dvoch rôznych pracovných listoch.

Do toho. použite túto techniku ​​a urobte na svojho šéfa a kolegov dojem (malá paráda nie je nikdy na škodu).

Stiahnite si ukážkový súbor

Páčil sa vám návod? Dajte mi vedieť svoje nápady v sekcii komentárov.

Tiež vám môžu byť užitočné nasledujúce návody:

• Dynamický filter programu Excel - extrahujte údaje počas písania.
• Dynamické vyhľadávanie v Exceli pomocou podmieneného formátovania.
• Vytvorte dynamickú rozbaľovaciu ponuku s návrhmi na vyhľadávanie.
• Ako extrahovať podreťazec v programe Excel pomocou vzorcov.
• Ako filtrovať bunky v tučnom formáte písma v programe Excel.