Excel -filter är en av de mest använda funktionerna när du arbetar med data. I det här blogginlägget kommer jag att visa dig hur du skapar en sökrutan för dynamiska Excel -filter, så att den filtrerar data baserat på vad du skriver i sökrutan.
Något som visas nedan:
Det finns en dubbel funktionalitet för detta - du kan välja ett lands namn från listrutan, eller så kan du ange data manuellt i sökrutan, så visas alla matchande poster. Till exempel, när du skriver "I" ger det dig alla landnamn med alfabetet I i.
Titta på video - Skapa en sökrutan för dynamiskt Excel -filter
Skapa en sökrutan för dynamiskt Excel -filter
Detta dynamiska Excel -filter kan skapas i tre steg:
- Få en unik lista med artiklar (länder i det här fallet). Detta skulle användas för att skapa rullgardinsmenyn.
- Skapa sökrutan. Här har jag använt en kombinationsbox (ActiveX Control).
- Ställa in data. Här skulle jag använda tre hjälparkolumner med formler för att extrahera matchande data.
Så här ser rådata ut:
NYTTIGT TIPS: Det är nästan alltid en bra idé att konvertera dina data till en Excel -tabell. Du kan göra detta genom att markera valfri cell i datamängden och använda tangentbordsgenvägen Ctrl + T.
Steg 1 - Få en unik lista med objekt
- Välj alla länder och klistra in det i ett nytt kalkylblad.
- Välj landslistan -> Gå till Data -> Ta bort dubbletter.
- I dialogrutan Ta bort dubbletter markerar du kolumnen där du har listan och klickar på Ok. Detta kommer att ta bort dubbletter och ge dig en unik lista enligt nedan:
- Ett ytterligare steg är att skapa ett namngivet intervall för denna unika lista. Att göra detta:
- Gå till fliken Formel -> Definiera namn
- I dialogrutan Definiera namn:
- Namn: CountryList
- Omfattning: Arbetsbok
- Avser: = UniqueList! $ A $ 2: $ A $ 9 (jag har listan på en separat flik som heter UniqueList i A2: A9. Du kan hänvisa till var din unika lista finns)
OBS! Om du använder metoden "Ta bort dubbletter" och du utökar dina data för att lägga till fler poster och nya länder, måste du upprepa detta steg igen. Alternativt kan du också få en formel för att göra denna process dynamisk.
Steg 2 - Skapa sökrutan för dynamiskt Excel -filter
För att denna teknik ska fungera måste vi skapa en "sökruta" och länka den till en cell.
Vi kan använda kombinationsrutan i Excel för att skapa detta sökrutefilter. På detta sätt, när du anger något i kombinationsrutan, skulle det också återspeglas i en cell i realtid (som visas nedan).
Här är stegen för att göra detta:
- Gå till fliken Utvecklare -> Kontroller -> Infoga -> ActiveX -kontroller -> Kombinationsbox (ActiveX -kontroller).
- Om du inte har utvecklingsfliken synlig, här är stegen för att aktivera den.
- Om du inte har utvecklingsfliken synlig, här är stegen för att aktivera den.
- Klicka var som helst på kalkylbladet. Den kommer att sätta in kombinationsboxen.
- Högerklicka på kombinationsrutan och välj Egenskaper.
- Gör följande ändringar i fönstret Egenskaper:
- Länkad cell: K2 (du kan välja vilken cell som helst där du vill att inmatningsvärdena ska visas. Vi kommer att använda den här cellen för att ställa in data).
- ListFillRange: CountryList (detta är det namngivna intervall vi skapade i steg 1. Detta skulle visa alla länder i rullgardinsmenyn).
- MatchEntry: 2-fmMatchEntryNone (detta säkerställer att ett ord inte fylls i automatiskt när du skriver)
- Med kombinationsrutan vald, gå till fliken Utvecklare -> Kontroller -> Klicka på designläge (detta tar dig ur designläge, och nu kan du skriva vad som helst i kombinationsrutan. Nu, vad du än skriver återspeglas i cell K2 i realtid)
Steg 3 - Ställa in data
Slutligen länkar vi allt med hjälparkolumner. Jag använder tre hjälparkolumner här för att filtrera data.
Hjälpkolumn 1: Ange serienumret för alla poster (20 i detta fall). Du kan använda ROWS () -formeln för att göra detta.
Hjälpkolumn 2: I hjälpkolumn 2 kontrollerar vi om texten som anges i sökrutan matchar texten i cellerna i landkolumnen.
Detta kan göras med en kombination av IF, ISNUMBER och SEARCH -funktioner.
Här är formeln:
= OM (ISNUMBER (SÖK ($ K $ 2, D4)), E4, "")
Denna formel söker efter innehållet i sökrutan (som är länkad till cell K2) i cellen som har landets namn.
Om det finns en matchning returnerar denna formel radnumret, annars returnerar det ett tomt. Till exempel, om kombinationsrutan har värdet "US", skulle alla poster med land som "US" ha radnumret och resten vila vara tomma ("")
Hjälpkolumn 3: I hjälparkolumn 3 måste vi få ihop alla radnummer från hjälpkolumn 2. För att göra detta kan vi använda en kombination om IFERROR och SMALL -formler. Här är formeln:
= IFERROR (Liten ($ F $ 4: $ F $ 23, E4), "")
Denna formel staplar ihop alla matchande radnummer. Till exempel, om kombinationsrutan har värdet US, staplas alla radnummer med "US" i.
Nu när vi har radnumren staplade ihop behöver vi bara extrahera data i dessa radnummer. Detta kan enkelt göras med hjälp av indexformeln (sätt in den här formeln där du vill extrahera data. Kopiera den i cellen längst upp till vänster där du vill ha data extraherad och dra den sedan ner och åt höger).
= IFERROR (INDEX ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ I $ 3: I3)), "")
Denna formel har 2 delar:
INDEX - Detta extraherar data baserat på radnumret.
IFERROR - Detta returnerar tomt när det inte finns några data.
Här är en ögonblicksbild av vad du äntligen får:
Kombinationsrutan är en rullgardinsmeny samt en sökruta. Du kan dölja originaldata och hjälpkolumner för att bara visa de filtrerade posterna. Du kan också ha rådata och hjälpkolumner i något annat ark och skapa detta dynamiska excelfilter i ett annat kalkylblad.
Bli kreativ! Prova några varianter
Du kan försöka anpassa den till dina krav. Du kanske vill skapa flera excelfilter istället för ett. Till exempel kanske du vill filtrera poster där säljare är Mike och Country är Japan. Detta kan göras exakt genom att följa samma steg med viss ändring i formeln i hjälpkolumner.
En annan variant kan vara att filtrera data som börjar med de tecken som du anger i kombinationsrutan. Till exempel, när du anger 'I' kanske du vill extrahera länder som börjar med I (jämfört med den nuvarande konstruktionen där det också skulle ge dig Singapore och Filippinerna eftersom det innehåller alfabetet I).
Som alltid är de flesta av mina artiklar inspirerade av mina läsares frågor/svar. Jag vill gärna få din feedback och lära av dig. Lämna dina tankar i kommentarsfältet.
Obs! Om du använder Office 365 kan du använda FILTER -funktionen för att snabbt filtrera data medan du skriver. Det är lättare än metoden som visas i den här självstudien.
