Excel кестелерімен жұмыс жасау кезінде оларды белгілі бір өлшемге немесе бірнеше шарттарға сәйкес жиі таңдау керек. Бағдарлама мұны бірқатар құралдарды қолдана отырып жасай алады. Әр түрлі опцияларды пайдаланып Excel бағдарламасында қалай іріктеуді білейік.
Іріктеу
Деректерді таңдау берілген шарттарды қанағаттандыратын нәтижелердің жалпы жиынтығынан іріктеу процедурасынан тұрады, содан кейін парақта жеке тізім түрінде немесе бастапқы диапазонда шығарылады.
1-әдіс: кеңейтілген автофильтрді қолданыңыз
Таңдаудың ең оңай жолы - кеңейтілген автофильтрді пайдалану. Мұны нақты мысалмен қалай жасау керектігін қарастырыңыз.
- Парақтағы аймақты таңдаңыз, оның ішінде сіз таңдағыңыз келетін мәліметтер бар. Қойындыда «Үй» түймесін басыңыз Сұрыптау және сүзу. Ол параметрлер блогында орналасқан. «Өңдеу». Осыдан кейін ашылатын тізімде батырманы басыңыз «Сүзгі».
Әр түрлі әрекет етуге мүмкіндік бар. Мұны істеу үшін парақтағы аймақты таңдағаннан кейін, қойындыға өтіңіз «Деректер». Түймесін басыңыз «Сүзгі»ол топтағы лентаға ілінеді Сұрыптау және сүзу.
- Осы әрекеттен кейін, кестенің үстіңгі жағында пиктограммалар пайда болады, олар ұяшықтардың оң жақ шетінде төңкерілген кішкентай үшбұрыштар түрінде басталады. Біз таңдау жасайтын баған тақырыбындағы осы белгішені нұқамыз. Ашылған мәзірде элементке өтіңіз «Мәтін сүзгілері». Одан кейін орынды таңдаңыз «Жеке сүзгі ...».
- Пайдаланушы сүзу терезесі іске қосылды. Онда таңдау жасалатын шекті орнатуға болады. Біз мысал ретінде қолданатын сандық форматтың ұяшықтары бар бағанның ашылмалы тізімінде шарттардың бес түрінің біреуін таңдауға болады:
- тең;
- тең емес;
- көп;
- көп немесе тең;
- аз.
Табыс мөлшері 10000 рубльден асатын мәндерді таңдау үшін шарт ретінде мысал келтірейік. Ауыстырғышты орнына қойыңыз Тағы. Мәнді дұрыс өріске енгізіңіз "10000". Әрекетті орындау үшін батырманы басыңыз «Жарайды».
- Көріп отырғаныңыздай, сүзгілеуден кейін кірістер сомасы 10 000 рубльден асатын сызықтар ғана қалды.
- Бірақ сол бағанға екінші шартты қосуға болады. Мұны істеу үшін біз пайдаланушы сүзгілеу терезесіне қайта ораламыз. Көріп отырғаныңыздай, оның төменгі бөлігінде тағы бір күй ауыстырғышы және оған сәйкес енгізу өрісі бар. Енді таңдаудың жоғарғы шегін 15 000 рубльге белгілейік. Мұны істеу үшін қосқышты орнына қойыңыз Аз, ал оң жақтағы жолға мән енгіземіз "15000".
Сонымен қатар, шартты ауыстырғыш бар. Ол екі лауазымға ие «Және» және «НЕМЕСЕ». Әдепкі бойынша, ол бірінші орынға орнатылады. Бұл үлгіде тек екі шектеуді қанағаттандыратын жолдар ғана қалады дегенді білдіреді. Егер ол позицияға қойылса «НЕМЕСЕ»онда екі шарттың кез-келгеніне сәйкес келетін мәндер болады. Біздің жағдайда сіз қосқышты орнатуыңыз керек «Және», яғни бұл параметрді әдепкі күйінде қалдырыңыз. Барлық мәндер енгізілгеннен кейін түймесін басыңыз «Жарайды».
- Енді кестеде кірістердің мөлшері 10 000 рубльден кем емес, бірақ 15 000 рубльден аспайтын сызықтар ғана бар.
- Сол сияқты, сіз басқа бағандардағы сүзгілерді теңшей аласыз. Сонымен қатар, бағандарда орнатылған алдыңғы шарттарға сәйкес сүзуді үнемдеуге болады. Сонымен, күн пішіміндегі ұяшықтар үшін сүзгілеу қалай орындалатынын көрейік. Тиісті бағандағы сүзгі белгішесін нұқыңыз. Тізімнің элементтерін дәйекті түрде нұқыңыз «Күні бойынша сүзу» және Реттелетін сүзгі.
- Пайдаланушының авто-сүзгі терезесі қайтадан басталады. Нәтижелерді таңдауды кестеде 2016 жылғы 4 мамырдан 6 мамырға дейін жүргіземіз. Көріп отырғанымыздай, таңдау режимінің ауыстырғышында сандық форматқа қарағанда көбірек параметрлер бар. Позицияны таңдаңыз «Кейін немесе тең». Оң жақтағы өрісте мәнді орнатыңыз "04.05.2016". Төменгі блокта қосқышты орнына қойыңыз «Тең немесе оған тең». Мәнді дұрыс өріске енгізіңіз "06.05.2016". Біз шарттардың үйлесімділік қосқышын әдепкі күйде қалдырамыз - «Және». Сүзуді әрекетте қолдану үшін батырманы басыңыз «Жарайды».
- Көріп отырғаныңыздай, біздің тізім одан әрі қысқарды. Енді онда тек жолдар қалды, оларда кіріс мөлшері 2016 жылғы 4 мамырдан 6 мамырға дейінгі кезең үшін 10000-ден 15000 рубльге дейін өзгереді.
- Бағаналардың бірінде сүзуді қалпына келтіре аламыз. Біз мұны кіріс құндылығы үшін жасаймыз. Тиісті бағандағы авто-сүзгі белгішесін нұқыңыз. Ашылмалы тізімнен элементті нұқыңыз Сүзгіні алып тастаңыз.
- Көріп отырғаныңыздай, осы әрекеттерден кейін кірістердің көлемін таңдау өшіріледі, ал күндер бойынша таңдау ғана қалады (2016 жылғы 05 сәуірден 05.06.2016 жылға дейін).
- Бұл кестеде тағы бір баған бар - «Аты». Онда мәтіндік форматтағы мәліметтер бар. Осы мәндер бойынша сүзгілеу арқылы таңдауды қалай құру керектігін қарастырайық.
Баған атауындағы сүзгі белгішесін нұқыңыз. Біз тізім атауларынан өтеміз «Мәтін сүзгілері» және «Жеке сүзгі ...».
- Пайдаланушының авто-сүзгі терезесі қайтадан ашылады. Заттар бойынша таңдау жасайық «Картоп» және Ет. Бірінші блокта шарт ауыстырғышын орнатыңыз «Тең». Оның оң жағындағы жолға біз сөзді енгіземіз «Картоп». Төменгі блок қосқышы да орналастырылған «Тең». Қарсы өрісте жазба жасаңыз - Ет. Содан кейін біз бұрын істемеген нәрсені істейміз: шарттардың үйлесімділігіне ауысыңыз «НЕМЕСЕ». Енді экранда көрсетілген шарттардың кез келгені көрсетілген сызық пайда болады. Түймесін басыңыз «Жарайды».
- Көріп отырғаныңыздай, жаңа үлгіде (05.04.2016 ж. - 05.06.2016 ж.) Және атаумен (картоп пен ет) шектеулер бар. Табыс мөлшеріне ешқандай шектеулер жоқ.
- Сіз сүзгіні оны орнатқан кездегідей толығымен алып тастай аласыз. Оның үстіне, қай әдіс қолданылғаны маңызды емес. Қойындыда бола отырып, сүзуді қалпына келтіру үшін «Деректер» түймесін басыңыз «Сүзгі»ол топқа орналастырылған Сұрыптау және сүзу.
Екінші нұсқа қойындыға баруды қамтиды «Үй». Онда біз таспадағы түймені басамыз Сұрыптау және сүзу блокта «Өңдеу». Іске қосылған тізімде түймесін басыңыз «Сүзгі».
Жоғарыда аталған екі әдісті қолдана отырып, сүзу жойылады және таңдау нәтижелері жойылады. Яғни, кестеде барлық мәліметтер жиынтығы көрсетіледі.
Сабақ: Excel бағдарламасындағы авто-сүзгі функциясы
2 әдіс: массив формуласын қолдану
Сіз сонымен қатар күрделі массив формуласын қолдану арқылы таңдау жасай аласыз. Алдыңғы нұсқадан айырмашылығы, бұл әдіс нәтиженің жеке кестеде шығарылуын қамтамасыз етеді.
- Сол парақта, дереккөздегідей бірдей баған атаулары бар бос кесте жасаңыз.
- Жаңа кестенің бірінші бағанындағы барлық бос ұяшықтарды таңдаңыз. Курсорды формулалар қатарына орналастырамыз. Дәл осы жерде көрсетілген өлшемдерге сәйкес таңдау жасайтын формула енгізіледі. Табыс мөлшері 15 000 рубльден асатын жолдарды таңдаймыз. Біздің нақты мысалда енгізу формуласы келесідей болады:
= INDEX (A2: A29; ТӨМЕН (IF (15000 <= C2: C29; STRING (C2: C29); «»); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Әрине, әр жағдайда, ұяшықтар мен ауқымдардың мекен-жайы әр түрлі болады. Бұл мысалда сіз формуланы суреттегі координаттармен салыстырып, оны қажеттіліктеріңізге сәйкес келтіре аласыз.
- Бұл массив формуласы болғандықтан, оны іс жүзінде қолдану үшін сіз батырманы баспауыңыз керек Енгізіңізжәне пернелер тіркесімі Ctrl + Shift + Enter. Біз мұны жасаймыз.
- Күні көрсетілген екінші бағанды таңдап, курсорды формула жолына қойып, келесі өрнекті енгіземіз:
= INDEX (B2: B29; ҚАЛА (IF (15000 <= C2: C29; STRING (C2: C29); «»); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Пернетақта тіркесімін басыңыз Ctrl + Shift + Enter.
- Сол сияқты, кірісі бар бағанға формула енгіземіз:
= INDEX (C2: C29; ТӨМЕН (IF (15000 <= C2: C29; STRING (C2: C29); «»); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Тағы да, пернелер тіркесімін теру Ctrl + Shift + Enter.
Барлық үш жағдайда тек бірінші координат мәні өзгереді, ал қалған формула толығымен бірдей.
- Көріп отырғаныңыздай, кесте деректермен толтырылған, бірақ оның көрінісі толығымен тартымды емес, сонымен қатар күн мәндері қате толтырылған. Бұл кемшіліктерді түзету керек. Күн дұрыс емес, себебі сәйкес бағанның ұяшық пішімі ортақ және біз күн форматын белгілеуіміз керек. Бүкіл бағанды, оның ішінде қателері бар ұяшықтарды таңдап, тышқанның оң батырмасымен таңдауды нұқыңыз. Пайда болған тізімде келесіге өтіңіз «Ұяшық пішімі ...».
- Ашылған пішімдеу терезесінде қойындысын ашыңыз «Сан». Блокта «Сандық форматтар» мәнін бөлу Күні. Терезенің оң жағында сіз күнді көрсетудің қажетті түрін таңдай аласыз. Параметрлер орнатылғаннан кейін түймесін басыңыз «Жарайды».
- Енді күн дұрыс көрсетіледі. Бірақ, көріп отырғанымыздай, кестенің төменгі бөлігі қате мәні бар ұяшықтармен толтырылған «# Нөмір!». Шын мәнінде, бұл іріктегі деректер жеткіліксіз болған ұяшықтар. Егер олар бос болып көрінсе, бұл өте тартымды болар еді. Осы мақсатта біз шартты форматтауды қолданамыз. Үстіңгі деректен басқа кестедегі барлық ұяшықтарды таңдаңыз. Қойындыда болу «Үй» түймесін басыңыз Шартты пішімдеуқұралдар блогында орналасқан Стильдер. Пайда болған тізімнен таңдаңыз «Ереже жасау ...».
- Ашылған терезеде ереже түрін таңдаңыз «Тек құрамында ұяшықтарды пішімдеу». Бірінші жолақта жазудың астына «Келесі шарт шынайы болатын ұяшықтарды пішімдеу» позицияны таңдаңыз «Қателер». Одан кейін түймесін басыңыз «Пішім ...».
- Пішімдеу терезесінде қойындыға өтіңіз Шрифт және сәйкес өрісте ақ түсті таңдаңыз. Осы әрекеттерден кейін түймесін басыңыз «Жарайды».
- Шарт жасау үшін терезеге оралғаннан кейін дәл сол батырманы басыңыз.
Енді бізде бөлек белгіленген кестеде көрсетілген шектеулерге дайын үлгі бар.
Сабақ: Excel-де шартты форматтау
3-әдіс: формуланы қолдана отырып бірнеше шарттарға сәйкес іріктеу
Фильтрді қолданғанда, формуланы қолдана отырып, сіз бірнеше шарттарға сәйкес таңдай аласыз. Мысал үшін біз барлық бірдей бастапқы кестені аламыз, нәтижелері көрсетілетін сандық және шартты пішімделген бос кестені аламыз. Біз бірінші шекті іріктеудің төменгі шегін 15000 рубльден, ал екінші шартты 20 000 рубльден жоғары деңгейге қойдық.
- Біз таңдау үшін шекара шарттарын жеке бағанға енгіземіз.
- Алдыңғы әдіс сияқты, біз жаңа кестенің бос бағандарын бір-бірлеп таңдап аламыз және оларға сәйкес үш формуланы енгіземіз. Бірінші бағанда келесі өрнек қосыңыз:
= INDEX (A2: A29; LOW (IF ((($ D $ 2 = C2: C29); LINE (C2: C29); «»); LINE (C2: C29) -LINE ($ C $ 1)) - LINE ($ C $ 1))Келесі бағандарға біз дәл сол формулаларды енгіземіз, тек координаталарды оператор атауынан кейін бірден өзгертеміз INDEX сәйкес бағандарға, алдыңғы әдіске ұқсас.
Кіргеннен кейін әр уақытта пернелер тіркесімін теруді ұмытпаңыз Ctrl + Shift + Enter.
- Алдыңғы әдіске қарағанда артықшылығы мынада, егер біз үлгінің шекараларын өзгерткіміз келсе, онда массивтің формуласын өзгерту қажет емес, ол өздігінен проблемалы болады. Парақтағы шарттар бағанында шекаралық нөмірлерді пайдаланушы қажетіне өзгерту қажет. Таңдау нәтижелері автоматты түрде бірден өзгереді.
4-әдіс: кездейсоқ таңдау
Арнайы формуланы қолданып, Excel бағдарламасында БОЛСЫН кездейсоқ таңдау да қолданыла алады. Кейбір жағдайларда, мәліметтердің үлкен көлемімен жұмыс істеген кезде, массивтегі барлық деректерді жан-жақты талдаусыз жалпы суретті көрсету қажет болған кезде шығарылуы қажет.
- Кестенің сол жағында біз бір бағанды өткізіп жібереміз. Кесте деректері бар бірінші ұяшыққа қарама-қарсы орналасқан келесі бағанның ұяшығына формула енгіземіз:
= RAND ()Бұл функция кездейсоқ санды көрсетеді. Оны іске қосу үшін батырманы басыңыз ЕНГІЗУ.
- Кездейсоқ сандардың бүкіл бағанын құру үшін курсорды формуласы бар ұяшықтың төменгі оң жақ бұрышына орналастырыңыз. Толтыру маркері пайда болады. Біз оны кестенің соңына параллель басылған тышқанның сол жақ батырмасымен сүйрейміз.
- Енді бізде кездейсоқ сандармен толтырылған бірнеше ұяшық бар. Бірақ оның формуласы бар БОЛСЫН. Біз таза құндылықтармен жұмыс жасауымыз керек. Мұны істеу үшін оң жақтағы бос бағанға көшіріңіз. Кездейсоқ сандары бар ұяшықтар ауқымын таңдаңыз. Қойындыда орналасқан «Үй»белгішесін нұқыңыз Көшіру таспада.
- Бос бағанды таңдап, контекстік мәзірді шақыру арқылы тінтуірдің оң жақ батырмасымен нұқыңыз. Құралдар тобында Параметрлерді кірістіру элементті таңдаңыз «Құндылықтар»сандары бар пиктограмма ретінде бейнеленген.
- Осыдан кейін, қойындыда болу «Үй», біз білетін белгішені нұқыңыз Сұрыптау және сүзу. Ашылмалы тізімде таңдауды келесіде тоқтатыңыз Таңдамалы сұрыптау.
- Сұрыптау параметрлері терезесі іске қосылды. Параметрдің жанындағы құсбелгіні қойыңыз «Менің деректерімде тақырыптар бар»егер шляпа болса, бірақ құсбелгі жоқ болса. Алаңда Сұрыптау кездейсоқ сандардың көшірілген мәндері бар бағанның атауын көрсетіңіз. Алаңда «Сұрыптау» әдепкі параметрлерді қалдырыңыз. Алаңда «Тапсырыс» параметрді келесідей таңдауға болады «Көтерілу», Сонымен «Кему». Кездейсоқ таңдау үшін бұл маңызды емес. Параметрлерді орнатқаннан кейін түймесін басыңыз «Жарайды».
- Осыдан кейін кестенің барлық мәндері кездейсоқ сандардың өсу немесе кему реті бойынша орналасады. Кестеден алғашқы жолдардың кез-келген санын алуға болады (5, 10, 12, 15, т.б.) және оларды кездейсоқ іріктеудің нәтижесі деп санауға болады.
Сабақ: Excel-де деректерді сұрыптау және сүзу
Көріп отырғаныңыздай, Excel кестесіндегі таңдауды автофильтр көмегімен немесе арнайы формулаларды қолдану арқылы жасауға болады. Бірінші жағдайда нәтиже бастапқы кестеде, ал екіншісінде - бөлек аймақта көрсетіледі. Таңдауды бір шарт бойынша да, бірнеше шарт бойынша да жасауға болады. Сондай-ақ, функцияны кездейсоқ таңдауға болады БОЛСЫН.
