Excel -filter er en av de mest brukte funksjonene når du arbeider med data. I dette blogginnlegget vil jeg vise deg hvordan du oppretter en søkeboks for dynamisk Excel -filter, slik at den filtrerer dataene basert på det du skriver i søkeboksen.
Noe som vist nedenfor:
Det er en dobbel funksjonalitet i dette - du kan velge et lands navn fra rullegardinlisten, eller du kan legge inn dataene manuelt i søkeboksen, og det vil vise deg alle matchende poster. For eksempel, når du skriver "I", gir det deg alle landnavnene med alfabetet I i.
Se video - Opprette en søkeboks for dynamisk Excel -filter
Opprette en søkeboks for dynamisk Excel -filter
Dette dynamiske Excel -filteret kan opprettes i tre trinn:
- Få en unik liste over varer (land i dette tilfellet). Dette vil bli brukt til å lage rullegardinmenyen.
- Opprette søkeboksen. Her har jeg brukt en kombinasjonsboks (ActiveX Control).
- Angi data. Her ville jeg bruke tre hjelperkolonner med formler for å trekke ut de matchende dataene.
Slik ser rådata ut:
Nyttig tips: Det er nesten alltid en god idé å konvertere dataene dine til en Excel -tabell. Du kan gjøre dette ved å velge hvilken som helst celle i datasettet og bruke hurtigtasten Control + T.
Trinn 1 - Få en unik liste over varer
- Velg alle landene og lim det inn i et nytt regneark.
- Velg landelisten -> Gå til Data -> Fjern dubletter.
- I dialogboksen Fjern dubletter velger du kolonnen du har listen i, og klikker OK. Dette vil fjerne duplikater og gi deg en unik liste som vist nedenfor:
- Et ekstra trinn er å lage et navngitt område for denne unike listen. Å gjøre dette:
- Gå til Formel -fanen -> Definer navn
- I dialogboksen Definer navn:
- Navn: CountryList
- Omfang: Arbeidsbok
- Henviser til: = UniqueList! $ A $ 2: $ A $ 9 (jeg har listen i en egen fane som heter UniqueList i A2: A9. Du kan referere til hvor din unike liste befinner seg)
MERK: Hvis du bruker metoden "Fjern dubletter" og du utvider dataene dine for å legge til flere poster og nye land, må du gjenta dette trinnet igjen. Alternativt kan du også bruke en formel for å gjøre denne prosessen dynamisk.
Trinn 2 - Opprette søkeboksen for dynamisk Excel -filter
For at denne teknikken skal fungere, må vi opprette en "søkeboks" og koble den til en celle.
Vi kan bruke kombinasjonsboksen i Excel for å lage dette søkefeltfilteret. På denne måten, når du skriver inn noe i kombinasjonsboksen, vil det også gjenspeiles i en celle i sanntid (som vist nedenfor).
Her er trinnene for å gjøre dette:
- Gå til Utvikler -fanen -> Kontroller -> Sett inn -> ActiveX -kontroller -> Kombinasjonsboks (ActiveX -kontroller).
- Hvis du ikke har fanen Utvikler synlig, følger du trinnene for å aktivere den.
- Hvis du ikke har fanen Utvikler synlig, følger du trinnene for å aktivere den.
- Klikk hvor som helst på regnearket. Den vil sette inn kombinasjonsboksen.
- Høyreklikk på kombinasjonsboksen og velg Egenskaper.
- Gjør følgende endringer i vinduet Egenskaper:
- Tilknyttet celle: K2 (du kan velge hvilken som helst celle der du vil at den skal vise inngangsverdiene. Vi bruker denne cellen til å sette dataene).
- ListFillRange: CountryList (dette er det navngitte området vi opprettet i trinn 1. Dette vil vise alle landene i rullegardinlisten).
- MatchEntry: 2-fmMatchEntryNone (dette sikrer at et ord ikke blir fullført automatisk mens du skriver)
- Med kombinasjonsboksen valgt, gå til Utvikler -fanen -> Kontroller -> Klikk på Designmodus (dette kommer deg ut av designmodus, og nå kan du skrive alt i kombinasjonsboksen. Nå vil det du skriver, gjenspeiles i celle K2 i virkeligheten)
Trinn 3 - Angi data
Til slutt kobler vi alt etter hjelpekolonner. Jeg bruker tre hjelperkolonner her for å filtrere dataene.
Hjelpekolonne 1: Skriv inn serienummeret for alle postene (20 i dette tilfellet). Du kan bruke formelen RADER () for å gjøre dette.
Hjelpekolonne 2: I hjelperkolonne 2 sjekker vi om teksten som er angitt i søkeboksen, samsvarer med teksten i cellene i landkolonnen.
Dette kan gjøres ved å bruke en kombinasjon av IF, ISNUMBER og SEARCH funksjoner.
Her er formelen:
= HVIS (ISNUMBER (SØK ($ K $ 2, D4)), E4, "")
Denne formelen søker etter innholdet i søkeboksen (som er koblet til celle K2) i cellen som har landsnavnet.
Hvis det er et treff, returnerer denne formelen radnummeret, ellers returnerer det et tomt. For eksempel, hvis kombinasjonsboksen har verdien 'US', vil alle postene med land som 'US' ha radnummeret, og resten alle være tomme ("")
Hjelpekolonne 3: I hjelperkolonne 3 må vi samle alle radnummerene fra hjelperkolonne 2. For å gjøre dette kan vi bruke en kombinasjon hvis IFERROR og SMALL formler. Her er formelen:
= FEIL (LITEN ($ F $ 4: $ F $ 23, E4), "")
Denne formelen stabler alle matchende radnumre sammen. For eksempel, hvis kombinasjonsboksen har verdien US, blir alle radnummerene med "US" i den stablet sammen.
Når vi nå har radnummerene stablet sammen, trenger vi bare å trekke ut dataene i disse radnummerene. Dette kan enkelt gjøres ved å bruke indeksformelen (sett inn denne formelen der du vil trekke ut dataene. Kopier den i cellen øverst til venstre der du vil ha dataene hentet, og dra den deretter ned og til høyre).
= IFERROR (INDEX ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ I $ 3: I3)), "")
Denne formelen har to deler:
INDEKS - Dette trekker ut dataene basert på radnummeret.
IFERROR - Dette returnerer tomt når det ikke er data.
Her er et øyeblikksbilde av hva du endelig får:
Kombinasjonsboksen er en rullegardinmeny samt en søkeboks. Du kan skjule de originale dataene og hjelperkolonnene for å vise bare de filtrerte postene. Du kan også ha rådata og hjelperkolonner i et annet ark og opprette dette dynamiske Excel -filteret i et annet regneark.
Bli kreativ! Prøv noen varianter
Du kan prøve å tilpasse den til dine krav. Det kan være lurt å opprette flere excel -filtre i stedet for et. For eksempel kan det være lurt å filtrere poster der salgsrepresentant er Mike og Country er Japan. Dette kan gjøres nøyaktig ved å følge de samme trinnene med noen endringer i formelen i hjelperkolonner.
En annen variant kan være å filtrere data som starter med tegnene du skriver inn i kombinasjonsboksen. For eksempel, når du skriver inn ‘I’, vil du kanskje trekke ut land som begynner med I (sammenlignet med den nåværende konstruksjonen der den også vil gi deg Singapore og Filippinene, da den inneholder alfabetet I).
Som alltid er de fleste av artiklene mine inspirert av spørsmålene/svarene til leserne mine. Jeg vil gjerne få tilbakemeldinger og lære av deg. Legg igjen tankene dine i kommentarfeltet.
Merk: Hvis du bruker Office 365, kan du bruke FILTER -funksjonen til raskt å filtrere dataene mens du skriver. Det er enklere enn metoden vist i denne opplæringen.
