Daugumai „Excel“ vartotojų, kai į galvą ateina žodis „duomenų filtravimas“, skirtuko lape pasirodo tik įprastas klasikinis filtras. Duomenys – filtras (Duomenys – filtras):
Toks filtras yra žinomas dalykas, be jokios abejonės, ir daugeliu atvejų jis tiks. Tačiau yra situacijų, kai reikia filtruoti pagal daugybę sudėtingų sąlygų keliuose stulpeliuose vienu metu. Įprastas filtras čia nėra labai patogus ir norisi kažko galingesnio. Tokia priemonė galėtų būti pažangus filtras, ypač šiek tiek „užbaigus byla“ (pagal tradiciją).
Pagrindas
Norėdami pradėti, įterpkite kelias tuščias eilutes virš duomenų lentelės ir nukopijuokite lentelės antraštę – tai bus diapazonas su sąlygomis (paryškintas geltonai, kad būtų aiškumo):
Tarp geltonų langelių ir pradinės lentelės turi būti bent viena tuščia eilutė.
Būtent geltonuose langeliuose reikia įvesti kriterijus (sąlygas), pagal kuriuos bus atliekamas filtravimas. Pavyzdžiui, jei jums reikia pasirinkti bananus Maskvos „Auchan“ III ketvirtyje, sąlygos atrodys taip:
Norėdami filtruoti, pasirinkite bet kurį diapazono langelį su šaltinio duomenimis, atidarykite skirtuką Duomenys Ir spustelėkite Be (Duomenys – išplėstiniai). Atsidariusiame lange jau turėtų būti automatiškai įvestas diapazonas su duomenimis ir turėsime nurodyti tik sąlygų diapazoną, ty A1:I2:
Atkreipkite dėmesį, kad sąlygų diapazonas negali būti priskirtas „su parašte“, ty negalima pasirinkti papildomų tuščių geltonų eilučių, nes tuščią langelį sąlygų diapazone „Excel“ suvokia kaip kriterijaus nebuvimą, o visą tuščią. eilutę kaip prašymą rodyti visus duomenis be atskyrimo.
jungiklis Nukopijuokite rezultatą į kitą vietą leis jums filtruoti sąrašą ne čia pat šiame lape (kaip naudojant įprastą filtrą), bet iškelti pasirinktas eilutes į kitą diapazoną, kurį tada reikės nurodyti lauke Įveskite rezultatą į diapazoną. Šiuo atveju šios funkcijos nenaudojame, išeiname Filtrų sąrašas yra vietoje ir spauskite OK. Pasirinktos eilutės bus rodomos lape:
Makrokomandos pridėjimas
– Na, kur čia patogumas? paklausk ir būsi teisus. Reikia ne tik rankomis įvesti sąlygas į geltonus langelius, bet ir atidaryti dialogo langą, ten įvesti diapazonus, paspausti OK. Liūdna, sutinku! Bet „viskas pasikeičia, kai ateina ©“ – makrokomandos!
Darbą su išplėstiniu filtru galima labai paspartinti ir supaprastinti naudojant paprastą makrokomandą, kuri automatiškai paleis išplėstinį filtrą, kai bus įvestos sąlygos, ty pakeičiamas bet koks geltonas langelis. Dešiniuoju pelės mygtuku spustelėkite dabartinio lapo skirtuką ir pasirinkite komandą Šaltinio tekstas (Pirminis kodas). Atsidariusiame lange nukopijuokite ir įklijuokite šį kodą:
Private Sub Worksheet_Change(ByVal Target As Range) Jei nesikerta(tikslas, diapazonas("A2:I5")) yra nieko, tada yra klaida Tęsti kitą ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=RlaceFilterInria :=Rangas("A1").DabartinisRegionas Pabaiga Jei Pabaiga sub Ši procedūra bus paleista automatiškai, kai bus pakeistas bet kuris dabartinio darbalapio langelis. Jei pakeisto langelio adresas patenka į geltoną diapazoną (A2:I5), tada ši makrokomanda pašalina visus filtrus (jei yra) ir iš naujo pritaiko išplėstinį filtrą šaltinio duomenų lentelei pradedant nuo A7, ty viskas bus filtruojama akimirksniu, iš karto. įvedus kitą sąlygą:
Taigi viskas daug geriau, tiesa? 🙂
Sudėtingų užklausų įgyvendinimas
Dabar, kai viskas filtruojama sklandžiai, galime šiek tiek įsigilinti į niuansus ir išardyti sudėtingesnių užklausų mechanizmus išplėstiniame filtre. Be tikslių atitikčių įvedimo, galite naudoti įvairius pakaitos simbolius (* ir ?) ir matematinius nelygybės ženklus įvairiomis sąlygomis, kad atliktumėte apytikslę paiešką. Charakterio atvejis neturi reikšmės. Kad būtų aiškumo, visas galimas parinktis apibendrinau lentelėje:
| kriterijus | Pasekmė |
| gr* arba gr | visos ląstelės, prasidedančios GrIe Grausis, Grafrutas, Granat ir taip toliau |
| = svogūnas | visos ląstelės tiksliai ir tik su žodžiu Lankasty tikslią atitiktį |
| *liv* arba *liv | ląstelės, kuriose yra Liv kaip pabraukti, t ОLivkad, Livep, PagalLiv ir taip toliau |
| =p*v | žodžiai prasidedantys П ir baigiant В ie Пpirmasв, Пeterisв ir taip toliau |
| a*s | žodžiai prasidedantys А ir toliau turintis СIe Аodaсin, АNanaс, Asai ir taip toliau |
| =*s | žodžiai, kurie baigiasi С |
| =???? | visi langeliai su 4 simbolių tekstu (raidės arba skaičiai, įskaitant tarpus) |
| =m??????n | visi langeliai su 8 simbolių tekstu, prasidedančiu М ir baigiant НIe Мandariн, Мnerimasн ir taip toliau |
| =*n??a | visi žodžiai baigiasi А, kur yra 4 raidė nuo galo НIe Sijaнikа, Pagalнozа ir taip toliau |
| >=e | visi žodžiai prasideda Э, Ю or Я |
| <>*o* | visi žodžiai, kuriuose nėra raidės О |
| <>*vich | visi žodžiai, išskyrus tuos, kurie baigiasi ŽIV (pavyzdžiui, filtruokite moteris pagal antrąjį vardą) |
| = | visos tuščios ląstelės |
| <> | visos netuščios ląstelės |
| > = 5000 | visi langeliai, kurių reikšmė didesnė arba lygi 5000 |
| 5 arba =5 | visi langeliai, kurių reikšmė yra 5 |
| >=3-18-2013 | visi langeliai, kurių data yra po 18 m. kovo 2013 d. (imtinai) |
Subtilūs taškai:
- Ženklas * reiškia bet kokį skaičių bet kokių simbolių ir ? - bet kuris personažas.
- Teksto ir skaitmeninių užklausų apdorojimo logika šiek tiek skiriasi. Taigi, pavyzdžiui, sąlygos langelis su skaičiumi 5 nereiškia, kad reikia ieškoti visų skaičių, prasidedančių penkiais, tačiau sąlygos langelis su raide B yra lygus B*, ty ieškos bet kokio teksto, prasidedančio raide B.
- Jei teksto užklausa neprasideda ženklu =, galite mintyse įdėti * pabaigoje.
- Dates must be entered in the US format month-day-year and through a fraction (even if you have Excel and regional settings).
Loginiai ryšiai IR-ARBA
Sąlygos, parašytos skirtinguose langeliuose, bet toje pačioje eilutėje, laikomos tarpusavyje sujungtomis loginiu operatoriumi И (IR):
Tie. filtruoti bananus man trečiame ketvirtyje, būtent Maskvoje ir tuo pačiu iš Auchan.
Jei reikia susieti sąlygas su loginiu operatoriumi OR (ARBA), tada juos tereikia įvesti skirtingomis eilutėmis. Pavyzdžiui, jei mums reikia rasti visus vadybininkės Volinos užsakymus Maskvos persikams ir visus svogūnų užsakymus trečiajame ketvirtyje Samaroje, tai galima nurodyti įvairiomis sąlygomis:
Jei vienam stulpeliui reikia nustatyti dvi ar daugiau sąlygų, galite tiesiog nukopijuoti stulpelio antraštę kriterijų diapazone ir po ja įrašyti antrą, trečią ir pan. terminai. Taigi, pavyzdžiui, galite pasirinkti visas operacijas nuo kovo iki gegužės:
Apskritai, „pabaigus failą“, pažangus filtras yra visai neblogas įrankis, kai kur ne prastesnis už klasikinį automatinį filtrą.
- Superfiltras makrokomandoms
- Kas yra makrokomandos, kur ir kaip įterpti makrokomandos kodą „Visual Basic“.
- Išmaniosios lentelės programoje Microsoft Excel