Dynaaminen Excel -suodattimen hakukenttä (poimi tiedot kirjoittaessasi)

Excel -suodatin on yksi käytetyimmistä toiminnoista, kun käsittelet tietoja. Tässä blogikirjoituksessa näytän sinulle, miten voit luoda dynaamisen Excel -suodattimen hakukentän siten, että se suodattaa tiedot hakukenttään kirjoittamasi perusteella.

Jotain alla olevan kuvan mukaisesti:

Tässä on kaksi toimintoa - voit valita maan nimen avattavasta luettelosta tai kirjoittaa tiedot manuaalisesti hakukenttään, ja se näyttää kaikki vastaavat tietueet. Esimerkiksi kun kirjoitat "I", se antaa sinulle kaikkien maiden nimet, joissa on aakkoset I.

Katso video - Dynaamisen Excel -suodattimen hakukentän luominen

Dynaamisen Excel -suodattimen hakukentän luominen

Tämä dynaaminen Excel -suodatin voidaan luoda kolmessa vaiheessa:

1. Ainutlaatuisen luettelon saaminen kohteista (tässä tapauksessa maat). Tätä käytetään avattavan valikon luomiseen.
2. Hakukentän luominen. Tässä olen käyttänyt yhdistelmälaatikkoa (ActiveX -ohjaus).
3. Tietojen asettaminen. Tässä käytän kolmea auttajasaraketta kaavojen kanssa vastaavien tietojen poimimiseksi.

Raakadata näyttää tältä:

HYÖDYLLINEN VINKKI: On melkein aina hyvä idea muuntaa tiedot Excel -taulukkoksi. Voit tehdä tämän valitsemalla minkä tahansa solun tietojoukosta ja käyttämällä pikanäppäintä Control + T.

Vaihe 1 - Hanki ainutlaatuinen luettelo kohteista

1. Valitse kaikki maat ja liitä se uuteen laskentataulukkoon.
2. Valitse maaluettelo -> Siirry Tiedot -> Poista kaksoiskappaleet.
3. Valitse Poista kaksoiskappaleet -valintaikkunasta sarake, jossa luettelo on, ja napsauta OK. Tämä poistaa kaksoiskappaleet ja antaa sinulle ainutlaatuisen luettelon alla esitetyllä tavalla:
4. Yksi lisävaihe on luoda nimetty alue tälle ainutlaatuiselle luettelolle. Tehdä tämä:
   • Siirry Kaava -välilehteen -> Määritä nimi
   • Määritä nimi -valintaikkunassa:
     • Nimi: CountryList
     • Soveltamisala: Työkirja
     • Viittaa kohteeseen: = UniqueList! $ A $ 2: $ A $ 9 (luettelo on erillisellä välilehdellä nimeltä UniqueList kohdassa A2: A9. Voit viitata mihin tahansa ainutlaatuisen luettelosi sijaintiin)

HUOMAUTUS: Jos käytät Poista kaksoiskappaleet -menetelmää ja laajennat tietojasi lisäämään tietueita ja uusia maita, sinun on toistettava tämä vaihe uudelleen. Vaihtoehtoisesti voit myös tehdä kaavan, jolla tämä prosessi muuttuu dynaamiseksi.

Vaihe 2 - Dynaamisen Excel -suodattimen hakukentän luominen

Jotta tämä tekniikka toimisi, meidän on luotava hakukenttä ja linkitettävä se soluun.

Voimme käyttää Excel -yhdistelmäruutua tämän hakukenttäsuodattimen luomiseen. Näin aina, kun kirjoitat jotain yhdistelmäruutuun, se näkyy myös solussa reaaliajassa (kuten alla).

Tässä on seuraavat vaiheet:

1. Valitse Kehittäjä -välilehti -> Ohjaimet -> Lisää -> ActiveX -komponentit -> Yhdistelmäruutu (ActiveX -komponentit).
   • Jos kehittäjävälilehti ei ole näkyvissä, ota se käyttöön seuraavasti.
2. Napsauta mitä tahansa laskentataulukon kohtaa. Se asettaa yhdistelmälaatikon paikalleen.
3. Napsauta yhdistelmäruutua hiiren kakkospainikkeella ja valitse Ominaisuudet.
4. Tee Ominaisuudet -ikkunassa seuraavat muutokset:
   • Linkitetty solu: K2 (voit valita minkä tahansa solun, jossa haluat sen näyttävän syöttöarvot. Käytämme tätä solua tietojen asettamisessa).
   • ListFillRange: CountryList (tämä on nimetty alue, jonka loimme vaiheessa 1. Tämä näyttää kaikki maat avattavasta valikosta).
   • MatchEntry: 2-fmMatchEntryNone (tämä varmistaa, että sana ei täyty automaattisesti kirjoittaessasi)
5. Kun yhdistelmäruutu on valittuna, siirry Kehittäjä -välilehteen -> Säätimet -> Napsauta Suunnittelutila (tämä vie sinut pois suunnittelutilasta ja voit nyt kirjoittaa mitä tahansa yhdistelmäruutuun. Nyt mitä kirjoitat, näkyy solussa K2 oikeassa ajassa)

Vaihe 3 - Tietojen asettaminen

Lopuksi linkitämme kaiken auttajasarakkeilla. Käytän tässä kolmea auttajasaraketta tietojen suodattamiseen.

Aukon sarake 1: Syötä kaikkien tietueiden sarjanumero (tässä tapauksessa 20). Voit käyttää tätä ROWS () -kaavalla.

Aukon sarake 2: Tarkistamme apusarakkeessa 2, vastaako hakukenttään syötetty teksti maa -sarakkeen solujen tekstiä.

Tämä voidaan tehdä käyttämällä IF-, ISNUMBER- ja SEARCH -toimintojen yhdistelmää.

Tässä on kaava:

= JOS (ISNUMBER (HAKU ($ K $ 2, D4)), E4, "")

Tämä kaava etsii sisällön hakukentästä (joka on linkitetty soluun K2) solussa, jossa on maan nimi.

Jos osuma on olemassa, tämä kaava palauttaa rivin numeron, muuten se palauttaa tyhjän. Jos esimerkiksi yhdistelmäruudun arvo on "US", kaikissa tietueissa, joiden maa on "US", on rivinumero ja loput kaikki ovat tyhjiä ("")

Aukon sarake 3: Helper -sarakkeessa 3 meidän on kerättävä kaikki rivinumerot Helper -sarakkeesta 2 yhteen. Tätä varten voimme käyttää yhdistelmää, jos IFERROR- ja SMALL -kaavat. Tässä on kaava:

= VIRHE (PIENI ($ F $ 4: $ F $ 23, E4), "")

Tämä kaava niputtaa kaikki vastaavat rivinumerot yhteen. Jos yhdistelmäruudun arvo on esimerkiksi US, kaikki rivinumerot, joissa on ”US”, pinotaan yhteen.

Nyt kun rivinumerot on pinottu yhteen, meidän tarvitsee vain poimia tiedot näistä rivinumeroista. Tämä voidaan tehdä helposti käyttämällä indeksikaavaa (lisää tämä kaava kohtaan, josta haluat poimia tiedot. Kopioi se vasemman yläkulman soluun, josta haluat tiedot poimittavaksi, ja vedä se sitten alas ja oikealle).

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

Tässä kaavassa on 2 osaa:
INDEKSI - Tämä poimii tiedot rivinumeron perusteella.
IFERROR - Tämä palauttaa tyhjän, kun tietoja ei ole.

Tässä on otos siitä, mitä lopulta saat:

Yhdistelmäruutu on pudotusvalikko ja hakukenttä. Voit piilottaa alkuperäiset tiedot ja apusarakkeet näyttämään vain suodatetut tietueet. Voit myös saada raakatiedot ja apusarakkeet jollekin muulle taulukolle ja luoda tämän dynaamisen Excel -suodattimen toisessa laskentataulukossa.

Ole luova! Kokeile joitain muunnelmia

Voit yrittää muokata sitä tarpeidesi mukaan. Haluat ehkä luoda useita Excel -suodattimia yhden sijasta. Voit esimerkiksi suodattaa tietueet, joissa myyntiedustaja on Mike ja maa on Japani. Tämä voidaan tehdä täsmälleen noudattamalla samoja vaiheita jonkin verran muokkaamalla auttajasarakkeiden kaavaa.

Toinen vaihtoehto voisi olla suodattaa tietoja, jotka alkavat yhdistelmäruutuun syöttämilläsi merkeillä. Esimerkiksi, kun kirjoitat "I", saatat haluta poimia maita, jotka alkavat I: llä (verrattuna nykyiseen rakenteeseen, jossa se antaisi sinulle myös Singaporen ja Filippiinit, koska se sisältää aakkosen I).

Kuten aina, suurin osa artikkeleistani on saanut inspiraationsa lukijoiden kysymyksistä/vastauksista. Haluaisin saada palautetta ja oppia sinulta. Jätä ajatuksesi kommenttiosioon.

Huomautus: Jos käytät Office 365: tä, voit suodattaa tiedot nopeasti kirjoittaessasi suodatintoiminnon avulla. Se on helpompaa kuin tässä opetusohjelmassa esitetty menetelmä.