Exceli filter on andmetega töötamisel üks enim kasutatud funktsioone. Selles ajaveebi postituses näitan teile, kuidas luua dünaamiline Exceli filtri otsingukast, nii et see filtreeriks andmed vastavalt sellele, mida sisestate otsingukasti.
Midagi, nagu allpool näidatud:
Sellel on kaks funktsionaalsust - saate ripploendist valida riigi nime või sisestada andmed otsingukasti käsitsi ja see näitab teile kõiki vastavaid kirjeid. Näiteks kui sisestate "I", annab see teile kõigi riikide nimed, milles on tähestik I.
Vaadake videot - dünaamilise Exceli filtri otsingukasti loomine
Dünaamilise Exceli filtri otsingukasti loomine
Selle dünaamilise Exceli filtri saab luua kolmes etapis:
- Ainulaadse üksuste loendi hankimine (antud juhul riigid). Seda kasutatakse rippmenüü loomisel.
- Otsingukasti loomine. Siin olen kasutanud liitkasti (ActiveX Control).
- Andmete seadistamine. Siin kasutaksin sobivate andmete ekstraheerimiseks kolme abistajaveergu koos valemitega.
Toorandmed näevad välja järgmised:
KASULIK NÕUANNE: Peaaegu alati on hea mõte teisendada oma andmed Exceli tabeliks. Seda saate teha, valides andmestiku mis tahes lahtri ja kasutades kiirklahvi Control + T.
Samm - ainulaadse esemete loendi hankimine
- Valige kõik riigid ja kleepige see uuele töölehele.
- Valige riikide loend -> Ava Andmed -> Eemalda duplikaadid.
- Valige dialoogiboksis Eemalda duplikaadid veerg, milles teil on loend, ja klõpsake nuppu OK. See eemaldab duplikaadid ja annab teile kordumatu loendi, nagu allpool näidatud:
- Üks täiendav samm on selle ainulaadse loendi jaoks nimega vahemiku loomine. Selleks tehke järgmist.
- Minge vahekaardile Valem -> Määrake nimi
- Dialoogiboksi nime määratlemine:
- Nimi: CountryList
- Reguleerimisala: töövihik
- Viitab: = UniqueList! $ A $ 2: $ A $ 9 (mul on nimekiri eraldi vahekaardil nimega UniqueList A2: A9. Võite viidata kõikjale, kus teie ainulaadne loend asub)
MÄRKUS. Kui kasutate meetodit „Eemalda duplikaadid” ja laiendate oma andmeid, et lisada rohkem kirjeid ja uusi riike, peate seda sammu uuesti kordama. Teise võimalusena saate selle protsessi dünaamiliseks muutmiseks kasutada ka valemit.
2. samm - dünaamilise Exceli filtri otsingukasti loomine
Selle tehnika toimimiseks peame looma otsingukasti ja linkima selle lahtriga.
Selle otsingukasti filtri loomiseks saame kasutada Exceli liitkasti. Nii peegeldab see liitkasti alati midagi reaalajas (nagu allpool näidatud).
Selleks toimige järgmiselt.
- Avage vahekaart Arendaja -> Juhtelemendid -> Lisa -> ActiveX -juhtelemendid -> liitkast (ActiveX -juhtelemendid).
- Kui teil pole arendaja vahekaarti näha, toimige selle lubamiseks järgmiselt.
- Kui teil pole arendaja vahekaarti näha, toimige selle lubamiseks järgmiselt.
- Klõpsake töölehel suvalist kohta. See lisab liitkasti.
- Paremklõpsake liitkastil ja valige Atribuudid.
- Tehke aknas Atribuudid järgmised muudatused.
- Lingitud lahter: K2 (saate valida mis tahes lahtri, kus soovite sisendväärtusi näidata. Me kasutame seda lahtrit andmete seadistamisel).
- ListFillRange: CountryList (see on nimega vahemik, mille lõime 1. etapis. See näitaks rippmenüüs kõiki riike).
- MatchEntry: 2-fmMatchEntryNone (see tagab, et sõna ei lõpetata tippimise ajal automaatselt)
- Kui liitkast on valitud, minge vahekaardile Arendaja -> Juhtelemendid -> Klõpsake kujundusrežiimi (see viib teid kujundusrežiimist välja ja nüüd saate kombineeritud kasti sisestada kõik. Nüüd, kõik, mida sisestate, kajastub lahtris K2 reaalajas)
3. samm - andmete seadistamine
Lõpuks seome kõik abistajate veergude abil. Andmete filtreerimiseks kasutan siin kolme abistajaveergu.
Abistaja veerg 1: Sisestage kõigi kirjete seerianumber (antud juhul 20). Selleks saate kasutada valemit ROWS ().
Abistaja veerg 2: Abiveerus 2 kontrollime, kas otsingukasti sisestatud tekst vastab riigi veeru lahtrites olevale tekstile.
Seda saab teha funktsioonide IF, ISNUMBER ja SEARCH kombinatsiooni abil.
Siin on valem:
= IF (ISNUMBER (OTSI ($ K $ 2, D4)), E4, "")
See valem otsib riigi nimega lahtrist otsingukasti (mis on seotud lahtriga K2) sisu.
Kui on vaste, tagastab see valem rea numbri, vastasel juhul tagastab tühiku. Näiteks kui liitkasti väärtus on „USA”, oleks kõigil kirjetel, mille riik on „USA”, rea number ja ülejäänud tühjad („”)
Abistaja veerg 3: Abistajaveerus 3 peame koguma kõik abiveeru 2 reanumbrid kokku. Selleks saame kasutada kombinatsiooni IFERROR ja SMALL valemite korral. Siin on valem:
= IFERROR (VÄIKE ($ F $ 4: $ F $ 23, E4), "")
See valem koondab kõik sobivad reanumbrid kokku. Näiteks kui liitkastil on väärtus US, laotakse kõik reanumbrid, mille sees on „US”.
Nüüd, kui oleme reanumbrid kokku ladunud, peame lihtsalt nende reanumbrite andmed välja võtma. Seda saab hõlpsasti teha indeksi valemi abil (sisestage see valem kohta, kust soovite andmeid ekstraheerida. Kopeerige see vasakus ülanurgas lahtrisse, kust soovite andmeid ekstraheerida, ja lohistage see alla ja paremale).
= IFERROR (INDEX ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ I $ 3: I3)), "")
Sellel valemil on 2 osa:
INDEX - See ekstraheerib andmed rea numbri alusel.
IFERROR - See tagastab tühjaks, kui andmeid pole.
Siin on ülevaade sellest, mida lõpuks saate:
Liitkast on rippmenüü ja otsingukast. Saate peita algandmed ja abiveerud, et kuvada ainult filtreeritud kirjed. Teil võib olla ka algandmed ja abiveerud mõnel teisel lehel ning luua selle dünaamilise Exceli filtri mõnel teisel töölehel.
Ole loominguline! Proovige mõnda varianti
Võite proovida seda oma vajadustele kohandada. Võib -olla soovite ühe asemel luua mitu Exceli filtrit. Näiteks võite filtreerida kirjeid, kus müügiesindaja on Mike ja riik on Jaapan. Seda saab teha täpselt samu samme järgides, abivahendite veergude valemis mõningaid muudatusi tehes.
Teine variant võib olla andmete filtreerimine, mis algavad liitkasti sisestatud märkidega. Näiteks kui sisestate „I”, võiksite välja võtta riigid, mis algavad I -ga (võrreldes praeguse konstruktsiooniga, kus see annaks teile ka Singapuri ja Filipiinid, kuna see sisaldab tähestikku I).
Nagu alati, on enamik minu artikleid inspireeritud minu lugejate küsimustest/vastustest. Tahaksin teie tagasisidet saada ja sinult õppida. Jäta oma mõtted kommentaaride sektsiooni.
Märkus. Kui kasutate Office 365, saate funktsiooni FILTER abil andmete sisestamise ajal kiiresti filtreerida. See on lihtsam kui selles õpetuses näidatud meetod.
