Crearea unui filtru drop-down pentru extragerea datelor pe baza selecției

Vizionați videoclipuri - Extrageți datele folosind o listă derulantă în Excel

În acest tutorial, vă voi arăta cum să creați un filtru drop-down în Excel, astfel încât să puteți extrage date pe baza selecției din meniul drop-down.

După cum se arată în imaginea de mai jos, am creat o listă derulantă cu nume de țări. De îndată ce selectez orice țară din meniul derulant, datele pentru țara respectivă sunt extrase în dreapta.

Rețineți că de îndată ce selectez India din filtrul drop-down, toate înregistrările pentru India sunt extrase.

Extrageți date din lista de selecție drop-down în Excel

Iată pașii pentru a crea un filtru drop-down care va extrage date pentru elementul selectat:

1. Creați o listă unică de articole.
2. Adăugați un filtru drop-down pentru a afișa aceste elemente unice.
3. Utilizați coloane de ajutor pentru a extrage înregistrările pentru elementul selectat.

Să ne scufundăm profund și să vedem ce trebuie făcut în fiecare dintre acești pași.

Creați o listă unică de articole

Deși ar putea exista repetări ale unui articol din setul dvs. de date, avem nevoie de nume unice de articole, astfel încât să putem crea un filtru drop-down folosindu-l.

În exemplul de mai sus, primul pas este să obțineți lista unică a tuturor țărilor.

Iată pașii pentru a obține o listă unică:

1. Selectați toate țările și lipiți-o în altă parte a foii de lucru.
2. Accesați Date -> Eliminați duplicatele.
3. În caseta de dialog Eliminare duplicate, selectați coloana în care aveți lista țărilor. Aceasta vă va oferi o listă unică, așa cum se arată mai jos.

Acum vom folosi această listă unică pentru a crea lista derulantă.

Vezi si: Ghidul final pentru găsirea și eliminarea duplicatelor în Excel.

Crearea filtrului drop-down

Iată pașii pentru a crea o listă derulantă într-o celulă:

1. Accesați Date -> Validare date.
2. În caseta de dialog Validare date, selectați fila Setări.
3. În fila Setări, selectați „Listă” în meniul derulant și, în câmpul „Sursă”, selectați lista unică a țărilor pe care le-am generat.
4. Faceți clic pe OK.

Scopul este acum de a selecta orice țară din lista derulantă, iar aceasta ar trebui să ne ofere lista înregistrărilor pentru țară.

Pentru a face acest lucru, ar trebui să folosim coloane și formule de ajutor.

Creați coloane de ajutor pentru a extrage înregistrările pentru elementul selectat

De îndată ce faceți selecția din meniul derulant, aveți nevoie de Excel pentru a identifica automat înregistrările care aparțin acelui element selectat.

Acest lucru se poate face folosind trei coloane de ajutor.

Iată pașii pentru a crea coloane de ajutor:

• Coloana de ajutor # 1 - Introduceți numărul de serie pentru toate înregistrările (20 în acest caz, puteți utiliza funcția ROWS () pentru a face acest lucru).
• Coloana de ajutor # 2 - Utilizați această funcție simplă IF Function: = IF (D4 = $ H $ 2, E4, ””)
  • Această formulă verifică dacă țara din primul rând se potrivește cu cea din meniul derulant. Deci, dacă selectez India, verifică dacă primul rând are India ca țară sau nu. Dacă este adevărat, returnează numărul rândului respectiv, altfel revine gol („”). Acum, când selectăm orice țară, sunt afișate numai acele numere de rând (în a doua coloană de ajutor) care are țara selectată în ea. (De exemplu, dacă India este selectată, atunci va arăta ca imaginea de mai jos).

Acum trebuie să extragem doar datele pentru aceste rânduri, care afișează numărul (deoarece este rândul care conține țara respectivă). Cu toate acestea, vrem acele înregistrări fără spații goale unul după altul. Acest lucru se poate face folosind o a treia coloană de ajutor

• Cea de-a treia coloană de ajutor - Utilizați următoarea combinație de funcții IFERROR și SMALL:
  = IFERROR (MIC ($ F $ 4: $ F $ 23, E4), ””)

Acest lucru ne-ar da ceva așa cum se arată mai jos în imagine:

Acum, când avem numărul împreună, trebuie doar să extragem datele din acel număr. Acest lucru se poate face cu ușurință folosind funcția INDEX (utilizați această formulă în celulele în care aveți nevoie de rezultatul extras):
= IFERROR (INDEX ($ B $ 4: $ D $ 23, $ G4, COLUMNE ($ J $ 3: J3)), ””)

Această formulă are 2 părți:
INDEX - Aceasta extrage datele pe baza numărului de rând
IFERROR - Această funcție revine necompletată atunci când nu există date

Iată un instantaneu cu ceea ce obțineți în cele din urmă:

Acum puteți ascunde datele originale dacă doriți. De asemenea, puteți avea datele originale și datele extrase și în două foi de lucru diferite.

Dați-i drumul. folosește această tehnică și impresionează-ți șeful și colegii (un mic show-off nu este niciodată un lucru rău).

Descărcați fișierul de exemplu

Ți-a plăcut tutorialul? Spuneți-mi părerile dvs. în secțiunea de comentarii.

Puteți găsi utile și următoarele tutoriale:

• Filtru Excel dinamic - Extrageți date pe măsură ce introduceți text.
• Căutare dinamică în Excel folosind formatarea condiționată.
• Creați un meniu derulant dinamic cu sugestii de căutare.
• Cum se extrage un șir în Excel folosind formule.
• Cum se filtrează celulele cu formatarea fonturilor în gras în Excel.