Excel пайдаланушыларының басым көпшілігі үшін «деректерді сүзу» сөзі пайда болған кезде, қойындыдағы әдеттегі классикалық сүзгі ғана. Деректер – Сүзгі (Деректер — Сүзгі):
Мұндай сүзгі таныс нәрсе, күмәнсіз және көп жағдайда ол жасайды. Дегенмен, бірден бірнеше бағандардағы күрделі шарттардың көп саны бойынша сүзгілеу қажет болатын жағдайлар бар. Мұнда кәдімгі сүзгі өте ыңғайлы емес және мен күштірек нәрсені қалаймын. Мұндай құрал болуы мүмкін кеңейтілген сүзгі, әсіресе кішкене «файлмен аяқтау» (дәстүр бойынша).
Негізі
Жұмысты бастау үшін деректер кестесінің үстіне бірнеше бос жолды енгізіңіз және кесте тақырыбын сол жерге көшіріңіз – бұл шарттары бар ауқым болады (анық болу үшін сары түспен белгіленген):
Сары ұяшықтар мен бастапқы кесте арасында кемінде бір бос жол болуы керек.
Дәл сары ұяшықтарда критерийлерді (шарттарды) енгізу керек, соған сәйкес сүзгілеу орындалады. Мысалы, егер сізге III тоқсанда Мәскеудегі «Ашандағы» банандарды таңдау керек болса, онда шарттар келесідей болады:
Сүзу үшін бастапқы деректері бар ауқымдағы кез келген ұяшықты таңдаңыз, қойындыны ашыңыз мәліметтер Және түймесін басыңыз Сонымен қатар (Деректер — Жетілдірілген). Ашылған терезеде деректері бар ауқым автоматты түрде енгізілуі керек және бізге тек шарттар ауқымын көрсету керек, яғни A1:I2:
Шарттар ауқымын «маржамен» бөлуге болмайтынын, яғни қосымша бос сары жолдарды таңдауға болмайтынын ескеріңіз, өйткені шарттар ауқымындағы бос ұяшықты Excel бағдарламасы шарттың жоқтығы, ал тұтас бос ұяшық ретінде қабылдайды. жолды барлық деректерді таңдаусыз көрсету сұрауы ретінде.
қосқыш Нәтижені басқа орынға көшіріңіз тізімді дәл осы парақта емес (әдеттегі сүзгі сияқты) сүзуге мүмкіндік береді, бірақ таңдалған жолдарды өрісте көрсету қажет басқа ауқымға түсіруге мүмкіндік береді. Нәтижені диапазонға қойыңыз. Бұл жағдайда біз бұл функцияны қолданбаймыз, біз кетеміз Сүзгі тізімі орнында және нұқыңыз OK. Таңдалған жолдар парақта көрсетіледі:
Макрос қосу
«Ал, мұндағы ыңғайлылық қайда?» Сіз сұрайсыз және сіз дұрыс боласыз. Қолыңызбен сары ұяшықтарға шарттарды енгізіп қана қоймай, диалогтық терезені ашып, сол жерге диапазондарды енгізіңіз, түймесін басыңыз. OK. Өкінішті, келісемін! Бірақ «олар келгенде бәрі өзгереді ©» – макростар!
Жетілдірілген сүзгімен жұмысты шарттар енгізілгенде, яғни кез келген сары ұяшықты өзгерткенде, қосымша сүзгіні автоматты түрде іске қосатын қарапайым макростың көмегімен айтарлықтай жылдамдатуға және жеңілдетуге болады. Ағымдағы парақтың қойындысын тінтуірдің оң жақ түймешігімен басып, пәрменді таңдаңыз Бастапқы мәтін (бастапқы код). Ашылған терезеде келесі кодты көшіріп, қойыңыз:
Private Sub Worksheet_Change(ByVal мақсатты диапазон ретінде) Егер қиылыспаса(Нысана, ауқым("A2:I5")) Ештеңе жоқ Қателік Келесі ActiveSheet.ShowAllData диапазонын("A7") жалғастыру).CurrentRegion.AdvancedFilter Action:=xlFilterInaPilter, :=Ауқым («A1»).Ағымдағы аймақтың соңы, егер соңы ішкі Бұл процедура ағымдағы жұмыс парағындағы кез келген ұяшық өзгертілгенде автоматты түрде орындалады. Егер өзгертілген ұяшықтың мекенжайы сары диапазонға түссе (A2:I5), онда бұл макрос барлық сүзгілерді (бар болса) жояды және кеңейтілген сүзгіні A7-ден басталатын бастапқы деректер кестесіне қайта қолданады, яғни бәрі бірден, бірден сүзіледі. келесі шартты енгізгеннен кейін:
Сонда бәрі әлдеқайда жақсы, солай ма? 🙂
Күрделі сұрауларды орындау
Енді бәрі жылдам сүзіліп жатқандықтан, біз нюанстарға сәл тереңірек кіріп, кеңейтілген сүзгідегі күрделі сұраулардың механизмдерін бөлшектей аламыз. Нақты сәйкестіктерді енгізуден басқа, шамамен іздеуді жүзеге асыру үшін әртүрлі қойылмалы таңбаларды (* және ?) және математикалық теңсіздік белгілерін шарттар ауқымында пайдалануға болады. Кейіпкердің жағдайы маңызды емес. Түсінікті болу үшін мен барлық мүмкін нұсқаларды кестеде жинақтадым:
| критерий | нәтиже |
| гр* немесе гр | дегеннен басталатын барлық ұяшықтар GrIe Grқұлақ, Grапфрут, Grанат және т.б. |
| = пияз | барлық ұяшықтар дәл және тек сөзбен садақ, яғни дәл сәйкестік |
| *тірі* немесе *тірі | жасушалары бар Лив қалай астын сызу, яғни ОЛивсол, Ливep, айтуыншаЛив және т.б. |
| =p*v | -ден басталатын сөздер П және аяқталады В ie Пбіріншів, Пэфирв және т.б. |
| a*s | -ден басталатын сөздер А және одан әрі қамтиды СIe Апелсin, АNanaс, Asai және т.б. |
| =*с | аяқталатын сөздер С |
| =???? | 4 таңбадан тұратын мәтіні бар барлық ұяшықтар (бос орындарды қосқанда әріптер немесе сандар) |
| =м??????n | 8 таңбадан басталатын мәтіні бар барлық ұяшықтар М және аяқталады НIe Мандарин, Ммазасызданун және т.б. |
| =*n??a | -мен аяқталатын барлық сөздер А, соңындағы 4-ші әріп қайда НIe Жарықнikа, айтуыншанozа және т.б. |
| >=e | -ден басталатын барлық сөздер Э, Ю or Я |
| <>*o* | құрамында әріп жоқ барлық сөздер О |
| <>*вич | аяқталатын сөздерден басқа барлық сөздер ВИЧ (мысалы, әйелдерді әкесінің аты бойынша сүзіңіз) |
| = | барлық бос ұяшықтар |
| <> | барлық бос емес ұяшықтар |
| > = 5000 | мәні 5000-нан жоғары немесе оған тең барлық ұяшықтар |
| 5 немесе =5 | 5 мәні бар барлық ұяшықтар |
| >=3/18/2013 | 18 жылдың 2013 наурызынан кейінгі күні бар барлық ұяшықтар (қоса алғанда) |
Жіңішке нүктелер:
- * белгісі кез келген таңбалардың кез келген санын білдіреді және ? – кез келген кейіпкер.
- Мәтіндік және сандық сұрауларды өңдеудегі логика сәл өзгеше. Сонымен, мысалы, 5 саны бар шарт ұяшығы бестен басталатын барлық сандарды іздеуді білдірмейді, бірақ В әрпі бар шарт ұяшығы В*-ға тең, яғни В әрпінен басталатын кез келген мәтінді іздейді.
- Егер мәтіндік сұрау = белгісінен басталмаса, соңында * қоюға болады.
- Күндер АҚШ пішімінде ай-күн-жыл және бөлшек арқылы енгізілуі керек (тіпті Excel және аймақтық параметрлеріңіз болса да).
Логикалық жалғаулар ЖӘНЕ-НЕМЕСЕ
Әртүрлі ұяшықтарда жазылған, бірақ бір жолда жазылған шарттар логикалық оператор арқылы өзара байланысқан деп есептеледі. И (ЖӘНЕ):
Анау. үшінші тоқсанда мен үшін банандарды сүзіңіз, дәл Мәскеуде және сонымен бірге Аушаннан.
Шарттарды логикалық оператормен байланыстыру қажет болса OR (OR), содан кейін оларды әртүрлі жолдарға енгізу керек. Мысалы, егер біз менеджер Волинаның Мәскеу шабдалыларына барлық тапсырыстарын және Самарадағы үшінші тоқсандағы пиязға барлық тапсырыстарды табу керек болса, онда оны келесідей шарттарда көрсетуге болады:
Бір бағанға екі немесе одан да көп шарттарды енгізу қажет болса, онда сіз жай ғана шарттар ауқымындағы баған тақырыбын көшіруге және оның астына екінші, үшінші және т.б. енгізуге болады. шарттар. Мысалы, наурыздан мамырға дейінгі барлық транзакцияларды таңдауға болады:
Жалпы, «файлмен аяқтағаннан» кейін кеңейтілген сүзгі кейбір жерлерде классикалық автофильтрден де жаман емес, әбден лайықты құрал болып шығады.
- Макростардағы суперфильтр
- Макростар дегеніміз не, макрокодты Visual Basic-те қайда және қалай енгізу керек
- Microsoft Excel бағдарламасындағы смарт кестелер