„Excel“ filtras yra viena iš dažniausiai naudojamų funkcijų dirbant su duomenimis. Šiame tinklaraščio įraše parodysiu, kaip sukurti dinaminio „Excel“ filtro paieškos laukelį, kad jis filtruotų duomenis pagal tai, ką įvedėte paieškos laukelyje.
Kažkas, kaip parodyta žemiau:
Tai yra dviguba funkcija - išskleidžiamajame sąraše galite pasirinkti šalies pavadinimą arba rankiniu būdu įvesti duomenis į paieškos laukelį, ir jis parodys visus atitinkančius įrašus. Pavyzdžiui, kai įvedate „aš“, pateikiami visi šalių pavadinimai su abėcėle I.
Žiūrėti vaizdo įrašą - dinaminio „Excel“ filtro paieškos laukelio kūrimas
Dinaminio „Excel“ filtro paieškos laukelio kūrimas
Šį dinaminį „Excel“ filtrą galima sukurti trimis veiksmais:
- Gaunamas unikalus elementų sąrašas (šiuo atveju šalys). Tai būtų naudojama kuriant išskleidžiamąjį meniu.
- Paieškos laukelio kūrimas. Čia aš naudoju kombinuotą dėžutę („ActiveX Control“).
- Duomenų nustatymas. Čia norėčiau naudoti tris pagalbinius stulpelius su formulėmis, kad išgautume atitinkamus duomenis.
Štai kaip atrodo neapdoroti duomenys:
NAUDINGAS PATARIMAS: Beveik visada gera mintis konvertuoti duomenis į „Excel“ lentelę. Tai galite padaryti pasirinkę bet kurį duomenų rinkinio langelį ir naudodami spartųjį klavišą „Control + T.
1 žingsnis - gaukite unikalų elementų sąrašą
- Pasirinkite visas šalis ir įklijuokite jas į naują darbalapį.
- Pasirinkite šalių sąrašą -> Eiti į Duomenys -> Pašalinti dublikatus.
- Dialogo lange Pašalinti dublikatus pasirinkite stulpelį, kuriame yra sąrašas, ir spustelėkite Gerai. Tai pašalins dublikatus ir suteiks unikalų sąrašą, kaip parodyta žemiau:
- Kitas papildomas žingsnis yra sukurti pavadinimą šiam unikaliam sąrašui. Padaryti tai:
- Eikite į skirtuką Formulė -> Apibrėžkite vardą
- Dialogo lango apibrėžimo lange:
- Pavadinimas: CountryList
- Taikymo sritis: darbo knyga
- Nurodo: = UniqueList! $ A $ 2: $ A $ 9 (sąrašą turiu atskirame skirtuke, pavadintame UniqueList A2: A9. Galite nurodyti, kur yra jūsų unikalus sąrašas)
PASTABA: Jei naudojate metodą „Pašalinti dublikatus“ ir išplėsite savo duomenis, kad pridėtumėte daugiau įrašų ir naujų šalių, turėsite pakartoti šį veiksmą dar kartą. Taip pat galite sukurti formulę, kad šis procesas būtų dinamiškas.
2 veiksmas - dinaminio „Excel“ filtro paieškos laukelio sukūrimas
Kad ši technika veiktų, turėtume sukurti „paieškos laukelį“ ir susieti jį su langeliu.
Norėdami sukurti šį paieškos laukelio filtrą, galime naudoti „Excel“ sudėtinį laukelį. Tokiu būdu, kai ką nors įvedate į kombinuotą laukelį, jis taip pat bus rodomas langelyje realiuoju laiku (kaip parodyta žemiau).
Štai žingsniai, kaip tai padaryti:
- Eikite į skirtuką Kūrėjas -> Valdikliai -> Įterpti -> „ActiveX“ valdikliai -> Sudėtinis langelis („ActiveX“ valdikliai).
- Jei nematote skirtuko „Kūrėjas“, atlikite toliau nurodytus jo įgalinimo veiksmus.
- Jei nematote skirtuko „Kūrėjas“, atlikite toliau nurodytus jo įgalinimo veiksmus.
- Spustelėkite bet kurią darbalapio vietą. Jis įdės kombinuotąjį langelį.
- Dešiniuoju pelės mygtuku spustelėkite kombinuotąjį laukelį ir pasirinkite Ypatybės.
- Lange Ypatybės atlikite šiuos pakeitimus:
- Susieta ląstelė: K2 (galite pasirinkti bet kurią langelį, kur norite, kad būtų rodomos įvesties vertės. Nustatydami duomenis naudosime šį langelį).
- ListFillRange: CountryList (tai pavadintas diapazonas, kurį sukūrėme atlikdami 1 veiksmą. Išskleidžiamajame meniu būtų rodomos visos šalys).
- „MatchEntry“: 2-fm
- Pasirinkę kombinuotąjį langelį, eikite į skirtuką Kūrėjas -> Valdikliai -> Spustelėkite Dizaino režimas (tai padės jums išeiti iš projektavimo režimo ir dabar galite įvesti bet ką į kombinuotąjį laukelį. Dabar, ką įvesite, atsispindės K2 langelyje realiu laiku)
3 žingsnis - duomenų nustatymas
Galiausiai viską susiejame pagalbiniais stulpeliais. Čia naudoju tris pagalbinius stulpelius duomenims filtruoti.
Pagalbinė 1 skiltis: Įveskite visų įrašų serijos numerį (šiuo atveju - 20). Norėdami tai padaryti, galite naudoti ROWS () formulę.
Pagalbinė 2 skiltis: Pagalbiniame 2 stulpelyje patikriname, ar paieškos laukelyje įvestas tekstas atitinka šalies stulpelio langelių tekstą.
Tai galima padaryti naudojant IF, ISNUMBER ir SEARCH funkcijų derinį.
Čia yra formulė:
= IF (ISNUMBER (PAIEŠKA ($ K $ 2, D4)), E4, "")
Ši formulė ieškos turinio paieškos laukelyje (kuris yra susietas su langeliu K2) langelyje, kuriame yra šalies pavadinimas.
Jei yra atitiktis, ši formulė grąžina eilutės numerį, kitu atveju - tuščią. Pvz., Jei kombinuotojo laukelio reikšmė yra „US“, visi įrašai, kurių šalis yra „US“, turėtų eilutės numerį, o visi kiti bus tušti („“)
Pagalbinė 3 stulpelis: Pagalbinėje 3 skiltyje turime surinkti visus eilutės numerius iš 2 pagalbinio stulpelio. Norėdami tai padaryti, galime naudoti derinį, jei formulės IFERROR ir SMALL. Čia yra formulė:
= IFERROR (SMALL ($ F $ 4: $ F $ 23, E4), "")
Ši formulė sukrauna visus atitinkančius eilutės numerius. Pvz., Jei kombinuotojo laukelio vertė yra JAV, visi eilučių numeriai su „US“ yra sudėti į vieną.
Dabar, kai eilių numeriai yra sudėti kartu, mums tiesiog reikia išgauti šių eilutės numerių duomenis. Tai galima padaryti lengvai naudojant indekso formulę (įveskite šią formulę ten, kur norite išgauti duomenis. Nukopijuokite ją viršutiniame kairiajame langelyje, kur norite išgauti duomenis, tada vilkite žemyn ir į dešinę).
= IFERROR (INDEX ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ I $ 3: I3)), "")
Ši formulė susideda iš 2 dalių:
INDEKSAS - Tai ištraukia duomenis pagal eilutės numerį.
IFERROR - Tai duoda tuščią, kai nėra duomenų.
Čia yra momentinis vaizdas, ką pagaliau gavote:
Sudėtinis laukas yra išskleidžiamasis meniu ir paieškos laukelis. Galite paslėpti pradinius duomenis ir pagalbinius stulpelius, kad būtų rodomi tik filtruoti įrašai. Taip pat galite turėti neapdorotų duomenų ir pagalbinių stulpelių kitame lape ir sukurti šį dinaminį „Excel“ filtrą kitame darbalapyje.
Būkite kūrybingi! Išbandykite kai kuriuos variantus
Galite pabandyti jį pritaikyti pagal savo poreikius. Galbūt norėsite sukurti kelis „Excel“ filtrus, o ne vieną. Pvz., Galbūt norėsite filtruoti įrašus, kuriuose pardavimo atstovas yra Mike, o šalis - Japonija. Tai galima padaryti tiksliai atlikus tuos pačius veiksmus, šiek tiek pakeitus pagalbinių stulpelių formulę.
Kitas variantas gali būti filtruoti duomenis, prasidedančius simboliais, kuriuos įvedate kombinuotame laukelyje. Pvz., Kai įvedate „aš“, galbūt norėsite išgauti šalis, prasidedančias I (palyginti su dabartine konstrukcija, kur ji taip pat suteiktų jums Singapūrą ir Filipinus, nes jame yra I abėcėlė).
Kaip visada, dauguma mano straipsnių yra įkvėpti skaitytojų klausimų/atsakymų. Norėčiau gauti jūsų atsiliepimų ir pasimokyti iš jūsų. Palikite savo mintis komentarų skiltyje.
Pastaba: Jei naudojate „Office 365“, galite naudoti funkciją FILTRAS, kad greitai filtruotumėte duomenis rašydami. Tai lengviau nei šiame vadove parodytas metodas.
