Dynamic Excel Filter Search Box (Udtræk data, mens du skriver)

Excel -filter er en af ​​de mest anvendte funktioner, når du arbejder med data. I dette blogindlæg vil jeg vise dig, hvordan du opretter en Dynamic Excel Filter Search Box, sådan at den filtrerer data baseret på, hvad du skriver i søgefeltet.

Noget som vist herunder:

Der er en dobbelt funktionalitet til dette - du kan vælge et lands navn på rullelisten, eller du kan manuelt indtaste dataene i søgefeltet, og det viser dig alle de matchende poster. For eksempel, når du skriver "I", giver det dig alle landenavne med alfabetet I i.

Se video - Oprettelse af et søgefelt til dynamisk Excel -filter

Oprettelse af et dynamisk Excel -filter søgefelt

Dette dynamiske Excel -filter kan oprettes i 3 trin:

1. Få en unik liste over varer (lande i dette tilfælde). Dette ville blive brugt til at oprette drop down.
2. Oprettelse af søgefeltet. Her har jeg brugt en kombinationsboks (ActiveX Control).
3. Indstilling af data. Her ville jeg bruge tre hjælperkolonner med formler til at udtrække de matchende data.

Sådan ser rådataene ud:

NYTTIGT TIP: Det er næsten altid en god idé at konvertere dine data til en Excel -tabel. Du kan gøre dette ved at markere en hvilken som helst celle i datasættet og bruge tastaturgenvejen Control + T.

Trin 1 - Få en unik liste over varer

1. Vælg alle lande, og indsæt det i et nyt regneark.
2. Vælg landelisten -> Gå til Data -> Fjern dubletter.
3. I dialogboksen Fjern dubletter skal du vælge den kolonne, hvor du har listen, og klikke på Ok. Dette fjerner dubletter og giver dig en unik liste som vist herunder:
4. Et yderligere trin er at oprette et navngivet område til denne unikke liste. At gøre dette:
   • Gå til fanen Formel -> Definer navn
   • I dialogboksen Definer navn:
     • Navn: CountryList
     • Anvendelsesområde: Arbejdsbog
     • Henviser til: = UniqueList! $ A $ 2: $ A $ 9 (jeg har listen i en separat fane med navnet UniqueList i A2: A9. Du kan henvise til, hvor din unikke liste findes)

BEMÆRK: Hvis du bruger metoden 'Fjern dubletter', og du udvider dine data til at tilføje flere poster og nye lande, bliver du nødt til at gentage dette trin igen. Alternativt kan du også bruge en formel til at gøre denne proces dynamisk.

Trin 2 - Oprettelse af søgefeltet til dynamisk Excel -filter

For at denne teknik skal fungere, skal vi oprette en 'søgefelt' og linke den til en celle.

Vi kan bruge kombinationsboksen i Excel til at oprette dette søgefeltfilter. På denne måde, når du indtaster noget i kombinationsboksen, vil det også blive afspejlet i en celle i realtid (som vist nedenfor).

Her er trinene til at gøre dette:

1. Gå til fanen Udvikler -> Kontrolelementer -> Indsæt -> ActiveX -kontrolelementer -> Kombinationsboks (ActiveX -kontrolelementer).
   • Hvis du ikke har fanen Udvikler synlig, er her trinene for at aktivere den.
2. Klik hvor som helst på regnearket. Det vil indsætte kombinationsboksen.
3. Højreklik på kombinationsboksen, og vælg Egenskaber.
4. Foretag følgende ændringer i vinduet Egenskaber:
   • Tilknyttet celle: K2 (du kan vælge en hvilken som helst celle, hvor du vil have den til at vise inputværdierne. Vi vil bruge denne celle til indstilling af dataene).
   • ListFillRange: CountryList (dette er det navngivne område, vi oprettede i trin 1. Dette viser alle lande i rullemenuen).
   • MatchEntry: 2-fmMatchEntryNone (dette sikrer, at et ord ikke udfyldes automatisk, mens du skriver)
5. Når kombinationsboksen er valgt, skal du gå til fanen Udvikler -> kontrolelementer -> Klik på designtilstand (dette får dig ud af designtilstand, og nu kan du skrive alt i kombinationsboksen. Uanset hvad du skriver, vil det blive afspejlet i celle K2 i realtid)

Trin 3 - Indstilling af data

Endelig forbinder vi alt ved hjælpekolonner. Jeg bruger tre hjælperkolonner her til at filtrere dataene.

Hjælperkolonne 1: Indtast serienummeret for alle poster (20 i dette tilfælde). Du kan bruge formlen ROWS () til at gøre dette.

Hjælperkolonne 2: I hjælperkolonne 2 kontrollerer vi, om teksten, der er indtastet i søgefeltet, matcher teksten i cellerne i landkolonnen.

Dette kan gøres ved hjælp af en kombination af IF, ISNUMBER og SEARCH funktioner.

Her er formlen:

= HVIS (ISNUMBER (SØG ($ K $ 2, D4)), E4, "")

Denne formel søger efter indholdet i søgefeltet (som er knyttet til celle K2) i cellen, der har landets navn.

Hvis der er et match, returnerer denne formel rækkenummeret, ellers returnerer det et tomt. For eksempel, hvis kombinationsboksen har værdien 'US', ville alle poster med land som 'US' have rækkenummeret, og resten ville være tomme ("")

Hjælperkolonne 3: I hjælperkolonne 3 skal vi stakke alle rækkenumre fra hjælperkolonne 2 sammen. For at gøre dette kan vi bruge en kombination, hvis IFERROR og SMALL formler. Her er formlen:

= FEJL (SMÅ ($ F $ 4: $ F $ 23, E4), "")

Denne formel stabler alle de matchende rækkenumre sammen. For eksempel, hvis kombinationsboksen har værdien US, stakkes alle rækkenumre med ‘US’ i.

Når vi nu har rækkenumrene stablet sammen, skal vi bare udtrække dataene i disse rækkenumre. Dette kan let gøres ved hjælp af indeksformlen (indsæt denne formel, hvor du vil udtrække dataene. Kopier dem i cellen øverst til venstre, hvor du vil have dataene udtrækket, og træk dem derefter ned og til højre).

= IFERROR (INDEX ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ I $ 3: I3)), "")

Denne formel har 2 dele:
INDEKS - Dette udtrækker dataene baseret på rækkenummeret.
IFERROR - Dette returnerer tomt, når der ikke er data.

Her er et øjebliksbillede af, hvad du endelig får:

Kombinationsboksen er en rullemenu samt et søgefelt. Du kan skjule de originale data og hjælperkolonner for kun at vise de filtrerede poster. Du kan også have rådata og hjælperkolonner i et andet ark og oprette dette dynamiske excel -filter i et andet regneark.

Bliv kreativ! Prøv nogle variationer

Du kan prøve at tilpasse det til dine krav. Du vil måske oprette flere excel -filtre i stedet for et. Du kan f.eks. Filtrere poster, hvor salgsrepræsentant er Mike, og Country er Japan. Dette kan gøres nøjagtigt ved at følge de samme trin med en vis ændring i formlen i hjælperkolonner.

En anden variant kan være at filtrere data, der starter med de tegn, du indtaster i kombinationsboksen. Når du f.eks. Indtaster 'I', vil du måske udtrække lande, der starter med I (sammenlignet med den nuværende konstruktion, hvor det også ville give dig Singapore og Filippinerne, da det indeholder alfabetet I).

Som altid er de fleste af mine artikler inspireret af mine læsers spørgsmål/svar. Jeg ville elske at få din feedback og lære af dig. Efterlad dine tanker i kommentarfeltet.

Bemærk: Hvis du bruger Office 365, kan du bruge FILTER -funktionen til hurtigt at filtrere dataene, mens du skriver. Det er lettere end metoden vist i denne vejledning.