Құрамдас бөліктер

Excel бағдарламасында бағандар бойынша қалай сүзуге болады. Excel бағдарламасында кеңейтілген сүзгіні пайдаланып деректерді сүзу

Excel бағдарламасындағы деректерді сүзу арқылы ақпаратты бір/бірнеше параметр бойынша көрсетуге болады.

Бұл мақсат үшін екі құрал бар: автосүзгі және кеңейтілген сүзгі. Олар жоймайды, бірақ шартқа сәйкес келмейтін деректерді жасырады. Автосүзгі ең қарапайым әрекеттерді орындайды. Жетілдірілген сүзгіде көптеген опциялар бар.

Excel бағдарламасындағы автосүзгі және кеңейтілген сүзгі

Пішімделмеген және тізім ретінде жарияланбаған қарапайым кесте бар. Негізгі мәзір арқылы автоматты сүзгіні қосуға болады.

Деректер ауқымын кесте ретінде пішімдесеңіз немесе оны тізім ретінде жарияласаңыз, автоматты сүзгі дереу қосылады.

Автосүзгіні пайдалану оңай: көмегімен жазбаны таңдау керек қалаған мән. Мысалы, №4 дүкенге жеткізілімдерді көрсетіңіз. Сәйкес сүзу шартының жанына құсбелгі қойыңыз:

Біз нәтижені бірден көреміз:

Құралдың ерекшеліктері:

Автосүзгі тек үзілмейтін ауқымда жұмыс істейді. Бір парақтағы әртүрлі кестелер сүзгіден өтпейді. Тіпті оларда деректердің бір түрі болса да.
Құрал жоғарғы жолды баған тақырыптары ретінде қарастырады - бұл мәндер сүзгіге қосылмаған.
Бір уақытта бірнеше сүзу шарттарын қолдануға болады. Бірақ әрбір алдыңғы нәтиже келесі сүзгіге қажетті жазбаларды жасыра алады.

Жетілдірілген сүзгіде көптеген басқа опциялар бар:

Сүзгілеуге қажетті шарттарды орнатуға болады.
Деректерді таңдау критерийлері анық көрінеді.
Жетілдірілген сүзгінің көмегімен пайдаланушы оңай таба алады бірегей құндылықтаркөпжолды массивте.

Excel бағдарламасында қосымша сүзгіні қалай жасауға болады

Дайын мысал Excel бағдарламасында кеңейтілген сүзгіні пайдалану жолы:

Түпнұсқа кестеде «Мәскеу» мәнін қамтитын жолдар ғана қалды. Сүзуден бас тарту үшін «Сұрыптау және сүзу» бөліміндегі «Тазалау» түймесін басу керек.

Excel бағдарламасында қосымша сүзгіні қалай пайдалану керек

«Мәскеу» немесе «Рязань» сөздері бар жолдарды таңдау үшін Excel бағдарламасында кеңейтілген сүзгіні пайдалануды қарастырыңыз. Сүзу шарттары бірдей бағанда болуы керек. Біздің мысалда бірі екіншісінің астында.

Қосымша сүзгі мәзірін толтырыңыз:

Біз берілген критерий бойынша таңдалған жолдар бар кестені аламыз:

«Дүкен» бағанында «No 1» мәні бар жолдарды таңдап алайық, ал шығындар бағанында «> 1 000 000 рубль». Сүзгілеу шарты шарт белгісінің сәйкес бағандарында болуы керек. Бір жолда.

Сүзу параметрлерін толтырыңыз. Біз OK түймесін басамыз.

Кестеде «Аймақ» бағанында «Рязань» сөзі немесе «Шығын» бағанында «> 10 000 000 рубль» мәні бар жолдарды ғана қалдырайық. Таңдау шарты әртүрлі бағандарға қатысты болғандықтан, біз оларды орналастырамыз әртүрлі сызықтартиісті тақырыптар астында.

Кеңейтілген сүзгі құралын қолданыңыз:

Бұл құрал формулалармен жұмыс істей алады, бұл пайдаланушыға массивтерден мәндерді таңдау кезінде кез келген дерлік тапсырманы шешуге мүмкіндік береді.

Негізгі ережелер:

Формуланың нәтижесі таңдау критерийі болып табылады.
Жазбаша формула TRUE немесе FALSE мәнін береді.
Бастапқы диапазон абсолютті сілтемелер арқылы көрсетіледі, ал таңдау шарты (формула түрінде) салыстырмалы сілтемелер арқылы көрсетіледі.
Егер TRUE қайтарылса, жол сүзгі қолданылғаннан кейін көрсетіледі. ЖАЛҒАН - жоқ.

Орташа мәннен жоғары шаманы қамтитын жолдарды көрсетейік. Ол үшін критерийлері бар тақтайшадан бөлек (I1 ұяшықта) атын енгізіңіз. Ең үлкен сан«. Төменде формула берілген. Біз AVERAGE функциясын қолданамыз.

Бастапқы ауқымдағы кез келген ұяшықты таңдап, «Қосымша сүзгіні» шақырыңыз. Таңдау шарты ретінде I1:I2 көрсетеміз (салыстырмалы сілтемелер!).

Кестеде «Сома» бағанындағы мәндер орташадан жоғары болатын жолдар ғана қалды.

Кестеде тек қайталанбайтын жолдарды қалдыру үшін «Қосымша сүзгі» терезесінде «Тек бірегей жазбалар» жанындағы құсбелгіні қойыңыз.

OK түймесін басыңыз. Қайталанатын жолдар жасырылады. Парақта тек бірегей жазбалар қалады.

Кейде Excel құжатына енгізілген деректерде мыңдаған жолдар болуы мүмкін және олардың арасынан қажетті жолдарды табу өте қиын. Бірақ егер сіз іздеген деректер қандай да бір нақты критерийлерді қанағаттандырса, соның арқасында қажетсіз ақпаратты жоюға болады, онда Excel бағдарламасында сүзгіні пайдалану керек.

Excel бағдарламасындағы барлық кестелер мәзір арқылы жасалады «Үстел»қойындысы «Кірістіру»немесе кесте сияқты пішімделген және кірістірілген сүзгісі бар.

Егер кесте түріндегі деректерде сүзгі жоқ болса, онда оны орнату керек. Ол үшін кесте тақырыбын таңдаңыз немесе жай кестенің ішіндегі ұяшықты таңдаңыз, қойындыға өтіңіз «Деректер»және мәзірден таңдаңыз «Сүзгі». Дәл сол мәзір элементін қойындыдан табуға болады «Үй»мәзірде «Сұрыптау және сүзу». Әрбір бағанның тақырыбының оң жағындағы кестенің тақырыбына сүзгіні қосқаннан кейін сәйкес белгі пайда болады, төңкерілген үшбұрышы бар шаршы.

Сүзгіні қарапайым қолдану - таңдалғандардан басқа тізімдегі барлық мәндерді жасыру. Сүзгі белгішесін басып, қажетті мәндерге қарама-қарсы мәндер тізімінде құсбелгі қалдырыңыз. Сүзгіні қолданғаннан кейін сүзгіден өткен бағандағы белгіше де өзгереді, бұл сүзгінің деректерге қолданылғанын көзбен анықтауға мүмкіндік береді. Сүзгіні жою үшін сүзгі белгішесін басып, таңдаңыз «Бағаннан сүзгіні алып тастау...».

Сондай-ақ күрделірек сүзгілерді конфигурациялауға болады. Біздің бірінші бағанымызда тек мәтіндік деректер бар, сондықтан сүзгі белгішесін басқан кезде осы бағанбелсенді элемент бар «Мәтіндік сүзгілер»мұнда кейбір шарттарды таңдауға болады. Сандық деректер жағдайында, екінші бағандағы сияқты, элемент қол жетімді болады «Сандық сүзгілер».

Кез келген теңшелетін опцияны таңдаған кезде, теңшелетін сүзгі терезесі ашылады, мұнда комбинациясы бар екі шартты бірден таңдауға болады «ЖӘНЕ»және "НЕМЕСЕ".

Сондай-ақ мәтін түсі және ұяшық түсі бойынша сүзгіні орнатуға болады. Бұл жағдайда тізімде тек осы бағанда қолданылатын мәтін мен ұяшық түстері көрсетіледі.

В соңғы нұсқалары Excel бағдарламасында кестелердің деректер бөліктерін жасау мүмкіндігі бар. Ол үшін кестедегі кез келген ұяшықты белсендіріп, пайда болатын қойындыға өтіңіз «Конструктор», және мәзірді таңдаңыз «Кесінді кірістіру». Деректер тілігі қолданылатын бағанды таңдау қажет терезе пайда болады. Бағанды таңдағаннан кейін басқару элементтері бар өзгермелі терезе пайда болады, онда барлық шектегіш элементтері тізімде болады. Кез келген мәнді таңдау арқылы біз сәйкес деректер бөлігін аламыз.

Содан кейін қабілетті болу үшін тезтек қажетті деректерді таңдаңыз, жасыруқажетсіз сызықтаркестелер. Осылайша, сүзгі мүмкіндік береді алып тастамай Excel кесте жолдары оларды уақытша жасырады.

Сүзгі арқылы жасырылған кесте жолдары жоғалмайды. Сіз олардың биіктігі нөлге тең болатынын шартты түрде елестете аласыз (бұрын жолдардың биіктігі мен бағандардың енін өзгерту туралы айтқанмын). Осылайша, сүзгіден жасырын емес қалған сызықтар, «бір-біріне жабыстырылған» сияқты. Нәтижесінде алынған сүзгі қолданылған кесте.

Сырттай, Excel бағдарламасындағы сүзгісі бар кесте кез келген басқа кестеге ұқсайды, бірақ оның жоғарғы бөлігінде әр бағанда арнайы көрсеткілер пайда болады. Төмендегі суретте сүзгісі қолданылған кестенің мысалын көре аласыз.

Енді кестеге сүзгілерді қалай қосуға болатынын көрейік.

Excel кестесінде сүзгіні қалай жасауға (қоюға) болады

Бұл өтте оңай! Тақырыптары бар кесте бар делік. Excel кестесіндегі сүзгі кестенің белгілі бір бөлігіне салынғандықтан, ең алдымен осы бөлік болуы керек бөлектеу. Excel бағдарламасының өзі ешқашан қалағаныңызды болжай алмайды, сондықтан төмендегі суретте көрсетілгендей кесте тақырыптары бар жолдан қажетті ұяшықтарды таңдаймыз.

[үлкейту үшін суретті басыңыз]

Назар аударыңыз:

БАРЛЫҚ жолды таңдасаңыз, Excel бағдарламасы автоматты түрде кем дегенде бір нәрсесі бар БАРЛЫҚ бағандарға сүзгіні орнатады. Бағанда ештеңе болмаса, сүзгі қолданылмайды.

Қажетті бағандарды таңдағаннан кейін сүзгіні нақты орнату керек. Ең оңай жолы - «Басты» / «Сүзгілеу және сұрыптау» таспа қойындысына өтіп, сүзгіні басу. Мұнда Office 2010 скриншоты берілген, Excel бағдарламасының басқа нұсқаларында сүзгі кестеге дәл осылай қолданылады.

[үлкейту үшін суретті басыңыз]

Осы түймені басқаннан кейін таңдалған ұяшықтарға сүзгі қосылады.

Бірнеше ұяшықты таңдамай, жай ғана біреуін шертсеңіз, сүзгі сіз таңдағандай БҮТІЛ жолға қосылады.

Менің мысалда суретте көрсетілген үш бағанның ішінен сүзгі ұяшықтары бұрын таңдалған екеуіне ғана қосылады. Нәтижені төмендегі скриншоттан көре аласыз.

[үлкейту үшін суретті басыңыз]

Кестеге сүзгіні қосудың көрсетілген мысалы ең қарапайым болып табылады. Оның қалай жұмыс істейтінін көрейік.

Excel бағдарламасында сүзгілерді пайдалану жолы

Сүзгіні қосқаннан кейін бірден кесте өзгермейді (баған тақырыптарында пайда болатын көрсеткілерді есептемегенде). Кейбір қажетті деректерді сүзгілеу үшін сүзгіңіз келетін бағандағы көрсеткіні басыңыз. Іс жүзінде ол төмендегі суретке ұқсайды.

[үлкейту үшін суретті басыңыз]

Сүзгі мағынасы:

Excel бағдарламасы ОСЫ (конфигурацияланған сүзгісі бар) бағанында таңдалған мәні бар ұяшықты қамтитын кесте жолдарын ғана қалдырады. Басқа жолдар жасырылады.

Сүзуді жою үшін (сүзгіні жоймай!) жай ғана барлық ұяшықтарды белгілеңіз. Дәл осындай әсер сүзгіні толығымен алып тастағанда болады - кесте қайтадан өзінің бастапқы пішінін алады.

Excel электрондық кестесіне бірнеше сүзгілерді қолдану

Excel электрондық кестесіндегі сүзгілерді біріктіруге болады. Бірнеше сүзгілерді енгізу логикалық «ЖӘНЕ» принципі бойынша жүреді. Бұл нені білдіреді, мысалды қарастырайық.

Егер кестеде бірнеше бағандар болса, онда біз алдымен сүзгіні орнатуға болады, мысалы, бірінші бағанға. Бұл жағдайда сүзгі жағдайына сәйкес сызықтардың бір бөлігі жасырылады. Содан кейін келесі бағанға сүзгіні орнатамыз. Мұндай жағдайда бірінші сүзгіден кейін қаладысызықтар болады қосымшасүзгіден өткізіледі. Қарапайым сөзбен айтқанда, әр жолға бірден 2 сүзгі қолданылады - бірінші бағанда және екіншісінде.

Әрбір келесі сүзгіні қолданғанда, БАРЛЫҚ сүзгілердің шарттарын қанағаттандыратын кестеде көрсетілетін жолдар саны азаяды. Осылайша, бірнеше сүзгілерді дәйекті қолдана отырып, мыңдаған жолдардан тұратын үлкен кестеден шағын таңдауды жылдам жасауға болады.

Алынған үлгімен қосымша әрекеттерді орындауға болады. Мысалы, бұл жолдарды көшіріп, басқа кестеге тасымалдауға болады. Сіз керісінше, мүлдем жойғыңыз келетін жолдарды сүзгіден өткізе аласыз! Содан кейін оларды алып тастағаннан кейін сүзгіні алып тастасаңыз, қажетсіз деректерден тазартылған кестені аласыз.

Енді мен 15 000-нан астам жолдан тұратын прейскуранттағы деректерді сүзудің нақты мысалын көрсететін бейнені қараңыз.

Жинақтау

Excel бағдарламасында үлкен кестелермен жұмыс істеуге тура келсе және белгілі бір деректерді жылдам таңдай білу қажет болса, сүзгілерді қолдану сізге көп уақытты үнемдейді. Кесте неғұрлым үлкен болса, сүзгілерді қолданудың пайдасы соғұрлым көп болады.

Бұл мысал менің жазбамнан алынған оқу курсықосулы Microsoft Excel. Мен сабақта күрделірек таңдау шарттары бар сүзгілерді пайдалануды қарастырамын.

Сіз файлды жүктей аласыз, оның мысалында мен сүзгілермен жұмыс істеуді қарастырдым,

Стандарттың негізгі кемшілігі ( ) - бұл қолданба туралы көрнекі ақпараттың болмауы осы сәтсүзгі: жазбаларды таңдау критерийлерін есте сақтау үшін сүзгі мәзіріне кіру қажет болған сайын. Бұл, әсіресе, бірнеше критерийлер қолданылғанда ыңғайсыз. Жетілдірілген сүзгінің бұл кемшілігі жоқ - барлық критерийлер сүзгіленген жазбалардың үстінен бөлек кестеде орналастырылған.

Құру алгоритмі кеңейтілген сүзгіқарапайым:

Сүзгі қолданылатын кестені жасаңыз (бастапқы кесте);
Біз критерийлері бар пластинаны жасаймыз (іріктеу шарттарымен);
Біз іске қосамыз Жетілдірілген сүзгі.

Диапазонда рұқсат етіңіз А 7:C 83 өрістерді (бағандарды) қамтитын тауарлар тізімі бар бастапқы кесте бар Өнім, Саныжәне Бағасы(мысалы, файлды қараңыз). Кестеде бос жолдар мен бағандар болмауы керек, әйтпесе Жетілдірілген сүзгі(және қалыпты ) дұрыс жұмыс істемейді.

1-тапсырма (басталуы...)

Өнім атауында мәндері бар жолдарды таңдау үшін сүзгіні орнатыңыз басысөзінен Тырнақтар. Бұл таңдау шарты тауарлары бар сызықтармен қанағаттандырылады шегелер 20 мм, Шегелер 10 мм, Шегелер 10 ммжәне Тырнақтар.

А 1 :A2 A2 сөзді көрсетіңіз Тырнақтар.

Ескерту: Критерийлер құрылымы y кеңейтілген сүзгіанық анықталған және ол BDSUMM() , COUNT() және т.б. функцияларының критерийлерінің құрылымымен сәйкес келеді.

Әдетте критерийлер кеңейтілген сүзгісүзгі қолданылатын үстелдің үстіне орналастырылады, бірақ оларды үстелдің бүйіріне де орналастыруға болады. Түпнұсқа кестенің астына критерийлері бар жапсырманы қоюдан аулақ болыңыз, бірақ бұл тыйым салынбағанымен, бұл әрқашан қолайлы емес, өйткені бастапқы кестеге жаңа жолдарды қосуға болады.

НАЗАР АУДАРЫҢЫЗ!
Таңдау шарттарының мәндері бар кесте мен бастапқы кесте арасында кем дегенде бір бос жол бар екеніне көз жеткізіңіз (бұл онымен жұмыс істеуді жеңілдетеді) кеңейтілген сүзгі).

Жетілдірілген сүзгі:

қоңырау шалу Жетілдірілген сүзгі();
алаңда бастапқы диапазон А 7:C 83 );
алаңда Шарттар ауқымы А1 :A2 .

Қажет болса, ауыстырғышты күйге орнату арқылы таңдалған жолдарды басқа кестеге көшіруге болады Нәтижені басқа орынға көшіріңіз. Бірақ біз бұл жерде мұны істемейміз.

OK түймесін басыңыз, сонда сүзгі қолданылады - кестеде бағандағы Өнім атауы бар жолдар ғана қалады. шегелер 20 мм, Шегелер 10 мм, Шегелер 50 ммжәне Тырнақтар. Қалған жолдар жасырылады.

Таңдалған жол нөмірлері көк түспен бөлектеледі.

Сүзгі әрекетінен бас тарту үшін кестедегі кез келген ұяшықты таңдап, басыңыз CTRL+SHIFT+L(тақырып және әрекет қолданылады кеңейтілген сүзгіжойылады) немесе мәзір түймесін басыңыз Таза ().

2-тапсырма (дәл сәйкестік)

Өнім бағанында бар жолдарды таңдау үшін сүзгіні орнатайық дәлсөзді қамтиды Тырнақтар. Бұл таңдау шарты тек тауарлары бар жолдармен қанағаттандырылады шегелержәне Тырнақтар(есепке алынбайды). Құндылықтар шегелер 20 мм, Шегелер 10 мм, Шегелер 50 ммесепке алынбайды.

Таңдау шарты бар пластинаны орналастырамыз Біз оны диапазонға орналастырамыз B1:B2 . Пластинада таңдау жасалатын баған тақырыбының атауы да болуы керек. Ұяшықта критерий ретінде B2 = формуласын көрсетіңіз Тырнақтар».

Енді барлығы жұмыс істеуге дайын Жетілдірілген сүзгі:

кестенің кез келген ұяшығын таңдаңыз (бұл қажет емес, бірақ ол сүзгі параметрлерін толтыруды тездетеді);
қоңырау шалу кеңейтілген сүзгі ( Деректер/ Сұрыптау және сүзу/ Кеңейтілген);
алаңда бастапқы диапазонкесте ұяшықтары ауқымы тақырыптармен бірге көрсетілгеніне көз жеткізіңіз ( А 7:C 83 );
алаңда Шарттар ауқымышарты бар белгі бар ұяшықтарды көрсетіңіз, яғни. диапазон B1:B2 .
OK түймесін басыңыз

Қолдану Жетілдірілген сүзгімұндай қарапайым критерийлермен оның мағынасы аз, өйткені бұл тапсырмаларды оңай орындаңыз. Автосүзгі. Күрделі сүзгілеу тапсырмаларын қарастырайық.

Шарт ретінде ="=Nails" емес, жай ғана көрсетсеңіз Тырнақтар, содан кейін атаулары бар барлық жазбалар көрсетіледі басы Nails сөзінен ( Шегелер 80 мм, Тырнақтар2). Өнім желілерін көрсету үшін, қамтитынсөз бойынша шегелер, Мысалға, Жаңа шегелер, шарт ретінде ="=*Nails" параметрін көрсету керек немесе жай ғана * Тырнақтар қайда* таңбалардың кез келген тізбегін білдіреді және білдіреді.

3-тапсырма (НЕМЕСЕ бір бағанның шарты)

Өнім бағанында сөзден басталатын мән бар жолдарды таңдау үшін сүзгіні орнатыңыз ТырнақтарНЕМЕСЕ Түсқағаз.

Бұл жағдайда таңдау критерийлері тиісті баған тақырыбының астына орналастырылуы керек ( Өнім) және бір бағанда бірінің астында бірінің астында орналасуы керек (төмендегі суретті қараңыз). Критерийлер тақтасын диапазонға қойыңыз C1:C3 .

Қосымша сүзгі опциялары бар терезе және сүзгіленген деректері бар кесте келесідей болады.

OK түймешігін басқаннан кейін бағандағы барлық жазбалар көрсетіледі Өнімөнімдер Тырнақтар НЕМЕСЕ Түсқағаз.

4-тапсырма (ЖӘНЕ шарты)

дәлбағанда қамтиды Өнімөнімдер Тырнақтар, және бағанда Санымәні >40. Бұл жағдайда таңдау критерийлері тиісті тақырыптардың астына (өнім және саны) орналастырылуы және бір жолда болуы керек. Таңдау шарттары арнайы форматта жазылуы керек: ="= Тырнақтар» және =">40" . Таңдау шарты бар пластинаны орналастырамыз Біз оны диапазонға орналастырамыз E1:F2 .

OK түймешігін басқаннан кейін бағандағы барлық жазбалар көрсетіледі Өнімөнімдер Тырнақтарсаны >40.

КЕҢЕС:Таңдау критерийлерін өзгерткен кезде критерийлері бар тақтайшаны әр уақытта жасаған дұрыс және сүзгіні шақырғаннан кейін оларға сілтемені ғана өзгертіңіз.

Ескерту: Кеңейтілген сүзгі опцияларын өшіру қажет болса ( Деректер/ Сұрыптау және сүзгілеу/ Өшіру), содан кейін сүзгіні шақырмас бұрын кестедегі кез келген ұяшықты таңдаңыз - EXCEL автоматты түрде кесте алып жатқан диапазонға сілтеме енгізеді (егер кестеде бос жолдар болса, сілтеме бүкіл кестеге кірістірілмейді, бірақ тек бірінші бос жолға).

5-тапсырма (НЕМЕСЕ әртүрлі бағандарға арналған шарт)

Алдыңғы мәселелерді, егер қаласаңыз, қарапайым . Дәл осындай мәселені кәдімгі сүзгі арқылы шешу мүмкін емес.

Кестенің тек сол жолдарын таңдайық дәлбағанда қамтиды Өнімөнімдер Тырнақтар, НЕМЕСЕ бағанда орналасқан Саны>40 мәнді қамтиды. Бұл жағдайда таңдау критерийлері тиісті тақырыптардың астына орналастырылуы керек (зат және саны) және орналастырылуы керек әртүрлі сызықтарда. Таңдау шарттары арнайы форматта жазылуы керек: = "> 40" және ="= Тырнақтар». Таңдау шарты бар пластинаны орналастырамыз Біз оны диапазонға орналастырамыз E4:F6 .

OK түймешігін басқаннан кейін бағандағы жазбалар көрсетіледі Өнімөнімдер ТырнақтарНЕМЕСЕ мәні >40 (кез келген өнім үшін).

6-тапсырма (Формуланы қолдану нәтижесінде жасалған таңдау шарттары)

нақты күш кеңейтілген сүзгіформулалар таңдау шарттары ретінде пайдаланылғанда көрінеді.

Жолды таңдау шарттарын көрсетудің екі нұсқасы бар:

критерий үшін мәндерді тікелей енгізіңіз (жоғарыдағы тапсырмаларды қараңыз);
формуланы орындау нәтижелері бойынша критерийді құру.

Формула арқылы берілген критерийлерді қарастырыңыз. Таңдау шарты ретінде көрсетілген формула TRUE немесе FALSE мәнін қайтаруы керек.

Мысалы, кестеде тек 1 рет болатын Өнімді қамтитын жолдарды көрсетейік. Мұны істеу үшін ұяшыққа енгізіңіз H2 формула =COUNTIF(1-парақ!$A$8:$A$83,A8)=1, және ішінде H1 тақырыбының орнынакейбір түсіндірме мәтінді енгізіңіз, мысалы, Қайталанбайтын мәндер. Қолданылатын Жетілдірілген сүзгі, ұяшық жағдайларының ауқымын көрсету H1:H2 .

Мәнді іздеу ауқымы арқылы енгізілгенін және COUNTIF() функциясындағы шарт салыстырмалы сілтемемен енгізілгенін ескеріңіз. Бұл қажет, өйткені пайдалану кезінде кеңейтілген сүзгі EXCEL мұны көреді A8 салыстырмалы сілтеме болып табылады және бір уақытта Өнім бағанында бір жазбаны төмен жылжытады және ШЫНДЫҚ немесе ЖАЛҒАН қайтарады. Егер TRUE қайтарылса, сәйкес кесте жолы көрсетіледі. Егер FALSE қайтарылса, сүзгіні қолданғаннан кейін жол көрсетілмейді.

Мысал файлындағы басқа формулалардың мысалдары:

Кестедегі 3-ші ең жоғары бағадан жоғары бағалары бар шығару жолдары. =C8>ҮЛКЕН($C$8:$C$83 ;5)Бұл мысал BIGGEST() функциясының қиындығын анық көрсетеді. Бағанды сұрыптасаңыз МЕН (бағалар), біз аламыз: 750; 700; 700 ; 700; 620, 620, 160, ... Адам тұрғысынан «3-ші ең жоғары баға» 620-ға сәйкес келеді, ал GREAT() функциясын түсінуде - 700 . Нәтижесінде 4 жол емес, тек бір жол (750) көрсетіледі;
Жолдың шығыс регистріне сезімтал =ДӘЛ("тырнақтар", A8). Тек өнім көрсетілетін жолдар ғана көрсетіледі. шегелеркіші әріптермен енгізіледі;
Бағасы орташадан жоғары жолдарды шығару =C8>Орташа($C$8:$C$83);

НАЗАР АУДАРЫҢЫЗ!
Қолдану кеңейтілген сүзгікестеге қолданылған сүзгіні болдырмайды ( Деректер/ Сұрыптау және Сүзгі/ Сүзгі).

7-тапсырма (Таңдау шарттарында формулалар мен әдеттегі критерийлер бар)

Енді басқа кестені қарастырыңыз парақтағы үлгі файл 7-тапсырма.

Бағанда Өнімөнімнің атауы беріледі, ал бағанда Өнім түрі- оның түрі.

Тапсырма берілген өнім түрі бойынша орташадан төмен бағасы бар өнімдерді көрсету. Яғни, бізде 3 критерий бар: бірінші критерий Өнімді, 2-ші - оның Түрін, ал 3-ші критерий (формула түрінде) орташадан төмен бағаны белгілейді.

Шарттарды 6 және 7-жолдарға орналастырамыз. Қажетті Өнім мен Өнім түрін енгізіңіз. Берілген өнім түрі үшін орташа мәнді есептеп, анық болу үшін оны бөлек F7 ұяшығында көрсетеміз. Негізінде, формуланы C7 ұяшығындағы критерий формуласына тікелей енгізуге болады.

4 өнімнің 2-і (көрсетілген өнім түрінен) көрсетіледі.

Мәселе 7.1. (Бір жолдағы 2 мән бірдей ме?)

Автокөліктің шығарылған жылы мен сатып алынған жылын көрсететін кесте бар.

Шығарылған жылы сатып алынған жылмен бірдей жолдарды ғана көрсеткіңіз келеді. Мұны =B10=C10 элементар формуласы арқылы жасауға болады.

8 есеп (Символ сан ма?)

Бізде тізімі бар кесте бар делік әртүрлі түрлерішегелер.

Өнім бағаны бар жолдарды ғана сүзу қажет Тырнақтар 1 дюйм, Тырнақтар 2 дюймжәне т.б. өнімдер Тот баспайтын шегелер, хромдалған тырнақтаржәне т.б. сүзуге болмайды.

Мұны істеудің ең оңай жолы - Nails сөзінен кейін сан болуы керек деген шартты сүзгі ретінде орнату. Мұны формула арқылы жасауға болады =ISNUMBER(--MID(A11,LSTR($A$8)+2,1))

Формула Nails сөзінен кейін өнім атынан 1 таңбаны жояды (бос орындарды қоса). Егер бұл таңба сан (цифр) болса, онда формула TRUE мәнін қайтарады және жол көрсетіледі, ішінде әйтпесесызық шығарылмайды. F бағаны формуланың қалай жұмыс істейтінін көрсетеді, яғни. оны іске қосу алдында сынауға болады кеңейтілген сүзгі.

9-тапсырма (Берілген Тауарларды ҚҰРАМЫСЫНДА БОЛМАЙТЫН шығыс жолдар)

Өнім бағанында ЖОҚ жолдарды ғана сүзу қажет: Шегелер, тақтайша, желім, тұсқағаз.

Бұл үшін сізге қолдануға тура келеді қарапайым формула =АЯҚТАЛУ(ВЛООУ(A15,$A$8:$A$11,1,0))

Excel бағдарламасындағы кеңейтілген сүзгі қосымша деректерді басқару опцияларын ұсынады электрондық кестелер. Бұл параметрлерде күрделірек, бірақ әрекетте әлдеқайда тиімді.

Стандартты сүзгінің көмегімен Microsoft Excel пайдаланушысы барлық тапсырмалардан алыс шеше алады. Қолданылған сүзу жағдайларының визуалды көрсетілімі жоқ. Екіден артық таңдау критерийін қолдану мүмкін емес. Бірегей жазбаларды қалдыру үшін қайталанатын мәндерді сүзгілеу мүмкін емес. Ал критерийлердің өзі схемалық және қарапайым. Жетілдірілген сүзгі функциясы әлдеқайда бай. Оның мүмкіндіктерін толығырақ қарастырайық.

Excel бағдарламасында қосымша сүзгіні қалай жасауға болады?

Жетілдірілген сүзгі деректерді шектеусіз шарттар жиынтығы бойынша сүзуге мүмкіндік береді. Құралдың көмегімен пайдаланушы:

екіден артық іріктеу критерийлерін қою;
сүзу нәтижесін басқа параққа көшіру;
формулаларды пайдаланып кез келген күрделілік шартын қою;
бірегей мәндерді шығарып алыңыз.

Жетілдірілген сүзгіні қолдану алгоритмі қарапайым:

Жоғарғы кесте сүзгілеудің нәтижесі болып табылады. Түсінікті болу үшін шарттары бар төменгі тақтайша берілген.

Excel бағдарламасында қосымша сүзгіні қалай пайдалануға болады?

Жетілдірілген сүзгінің әрекетінен бас тарту үшін курсорды кестенің кез келген жеріне қойып, Ctrl + Shift + L немесе «Деректер» - «Сұрыптау және сүзу» - «Тазалау» пернелер тіркесімін басыңыз.

«Қосымша сүзгі» құралын пайдаланып, «Орнату» сөзі бар мәндер туралы ақпаратты табайық.

Шарт кестесіне критерийлерді қосамыз. Мысалы, мыналар:

Бұл жағдайда бағдарлама атауларында «Орнату» сөзі бар өнімдер туралы барлық ақпаратты іздейді.

Нақты мәнді іздеу үшін «=" белгісін пайдалануға болады. Шарттар кестесіне келесі критерийлерді қосамыз:

Excel «=" белгісін пайдаланушының формуланы енді енгізіп жатқаны туралы сигнал ретінде қабылдайды. Бағдарлама дұрыс жұмыс істеуі үшін формула жолында келесі пішімдегі жазба болуы керек: = = 6 ұяшықтар аймағы.

«Қосымша сүзгіні» пайдаланғаннан кейін:

Енді әртүрлі бағандарға арналған «НЕМЕСЕ» шарты бойынша бастапқы кестені сүзіп көрейік. «НЕМЕСЕ» операторы «Автосүзгі» құралында да бар. Бірақ бұл жерде оны бір баған ішінде пайдалануға болады.

Шарттар кестесіне таңдау шартын енгіземіз: = = 6 ұяшықтар аймағының жиыны. («Аты» бағанында) және ="

Назар аударыңыз: критерийлер сәйкес тақырыптардың астында ТҮРЛІ жолдарда жазылуы керек.

Таңдау нәтижесі:

Жетілдірілген сүзгі формулаларды шарт ретінде пайдалануға мүмкіндік береді. Мысал қарастырайық.

Максималды берешегі бар жолды таңдау: =MAX(1-кесте[Борыш]).

Осылайша, біз нәтижелерді бір Excel парағында бірнеше сүзгілерді іске қосқаннан кейін аламыз.

Excel бағдарламасында бірнеше сүзгілерді қалай жасауға болады?

Бірнеше мән бойынша сүзгіні жасайық. Ол үшін шарттар кестесіне бірден бірнеше деректерді таңдау критерийлерін енгіземіз:

Кеңейтілген сүзгі құралын қолданыңыз:

Енді таңдалған деректермен кестеден біз шығарып аламыз жаңа ақпаратбасқа критерийлер бойынша таңдалады. Мысалы, тек 2014 жылғы жөнелтілімдер.

Шарттар кестесіне жаңа критерий енгіземіз және сүзгі құралын қолданамыз. Бастапқы диапазон - алдыңғы критерийге сәйкес таңдалған деректері бар кесте. Бірнеше бағандарда осылай сүзгілеу.

Бірнеше сүзгілерді пайдалану үшін жаңа парақтарда бірнеше шарт кестелерін жасауға болады. Орындау әдісі пайдаланушы қойған тапсырмаға байланысты.