XMCD кеңейтімі бар файлдарды ашыңыз

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

Іріктеу

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

1-әдіс: жетілдірілген автофильтрді пайдаланыңыз

Таңдау жасаудың ең оңай жолы - жетілдірілген автофильтрді пайдалану. Мұны нақты мысалмен қалай жасау керектігін қарастырыңыз.

1. Парақтағы аумақты, сіз таңдағыңыз келетін деректердің арасында таңдаңыз. Қойындысындағы «Үй» батырмасын басыңыз «Сұрыптау және сүзгілеу». Ол параметрлер блогында орналасады. Өңдеу. Бұдан кейін ашылатын тізімде түймешігін басыңыз. «Сүзгі».

   

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



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



3. Теңшелетін сүзгілеу терезесі іске қосылды. Таңдау жасалатын шектеу қоюға болады. Мысал ретінде пайдаланатын сан пішіміндегі ұяшықтарды қамтитын бағанның ашылмалы тізімінде сіз бес шарттың біреуін таңдауға болады:
   • тең
   • тең емес;
   • көп;
   • үлкен немесе тең;
   • аз

   Келісім шартты 10 мың рубльдан асатын құнды ғана таңдауға болатындай етіп орнатайық. Қосқышты орнына орнатыңыз «Көбірек». Оң жақ шетіндегі мәнді енгізіңіз "10000". Әрекетті орындау үшін түймесін басыңыз. «ОК».



4. Көріп отырғандай, сүзуден кейін пайда көлемі 10 мың рубльден асатын сызықтар ғана бар.



5. Бірақ сол бағанда екінші шартты қосуға болады. Мұны істеу үшін, теңшелетін сүзгі терезесіне оралыңыз. Көріп отырғанымыздай, оның төменгі бөлігінде басқа шартты қосқыш және тиісті енгізу өрісі бар. Енді жоғарғы іріктеу лимитін 15 000 рубльге орнатайық. Ол үшін қосқышты орнына орнатыңыз «Аз», ал оң жақта өріс мәнін енгізіңіз "15000".

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



6. Енді кестеде тек кіріс сомасы кемінде 10 000 рубль болатын, бірақ 15 000 рубльден асатын сызықтар ғана бар.



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



8. Пайдаланушы автофильтр терезесі қайтадан басталады. Нәтижелерді кестеде 4-тен 6-мамырға дейін қоса алғанда 6-мамырға дейін қоса орындаңыз. Селекторлық сөндіргіште, көріп тұрғаныңыздай, сандық пішімнен гөрі көп нұсқалар бар. Орналасқан жерді таңдаңыз «Кейін немесе тең». Оң жағындағы өрісте мәнді орнатыңыз "04.05.2016". Төменгі блокта қосқышты орнына орнатыңыз «Немесе тең». Оң жақ өрістегі мәнді енгізіңіз "06.05.2016". Шарты үйлесімділік қосқышы әдепкіде қалды - «Және». Қолданыстағы сүзгіні қолдану үшін түймесін басыңыз «ОК».



9. Көріп отырғанымыздай, біздің тізіміміз одан да көп болды. Онда қазір тек 04.05-ден 06.05.2016-ға дейінгі кезеңде кіріс сомасы 10 000-нан 15 000 рубльге дейін өзгереді.



10. Сүзгілерді бағандардың бірінде қалпына келтіруге болады. Мұны кіріс мәндері үшін орындаңыз. Тиісті бағанда автофильтр белгішесін басыңыз. Ашылмалы тізімде элементті нұқыңыз. «Сүзгіні алып тастау».



11. Көріп отырғаныңыздай, осы әрекеттерден кейін табыс сомасы бойынша үлгі өшіріледі және күндер бойынша таңдау ғана қалады (04.05.2016 бастап 06.05.2016 дейін).



12. Бұл кестеде басқа баған бар - «Аты». Мәліметтер мәтіндік форматта бар. Осы мәндер бойынша сүзгіні пайдалану арқылы үлгіні құру әдісін көрейік.

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



13. Пайдаланушы автофильтр терезесі қайта ашылады. Атау бойынша үлгі жасайық. «Картоп» және «Ет». Бірінші блокта күй қосқышы орнатылады «Теңге». Оның оң жағындағы өрісіне сөзді енгізіңіз «Картоп». Төменгі блоктың қосқышы да орнына қойылады «Теңге». Оның қарама-қарсы жағында біз кіріспеден - «Ет». Ал содан кейін біз бұрын істемегендігімізді орындаймыз: орнымызға үйлесімділік қосқышын орнаттық «НЕМЕСЕ». Енді көрсетілген шарттардың кез келгенін қамтитын жол экранда көрсетіледі. Түймешікті басыңыз «ОК».



14. Көріп отырғаныңыздай, жаңа үлгіде күні (04/05/2016 - 05/06/2016 ж.) Және аты (картоп және ет) бойынша шектеулер бар. Табыс сомасына шектеу жоқ.



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

    

    Екінші опция қойындыға ауысуды қамтиды «Үй». Онда біз таспада кнопканы басамыз. «Сұрыптау және сүзгілеу» блокта Өңдеу. Белсенді тізімде батырмасын басыңыз. «Сүзгі».

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

Сабақ: Excel бағдарламасында автоматты сүзгі функциясы

2-әдіс: Массив формуласын пайдаланыңыз

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

1. Сол парақта бас тақырыптағы бірдей баған атаулары бар бос кестені бастапқы код ретінде жасаңыз.



2. Жаңа кестенің бірінші бағанының барлық бос ұяшықтарын таңдаңыз. Курсорды формула жолына орнатыңыз. Мұнда көрсетілген өлшемдерге сәйкес іріктеу формуласы енгізіледі. Біз желілерді таңдаймыз, онда 15 мың рубльден асатын кіріс мөлшері бар. Біздің нақты мысалға сіз енгізген формула келесідей болады:

   STRING () - STRING ($ C $ 1)) - STRING (C $ 1)) = INDEX (A2: A29; LOWEST (IF (15000 <= C2: C29; STRING (C2: C29);

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



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



4. Екінші бағанды ​​күндермен таңдау және меңзерді формула жолында орнату келесі өрнекті енгізіңіз:

   STRING () - STRING (C $ 1)) - STRING ($ C $ 1)) = INDEX (B2: B29; LOWEST (IF (15000 <= C2: C29; STRING (C2: C29); STRING

   Пернелер тіркесімін басыңыз Ctrl + Shift + Enter пернелер тіркесімі.



5. Сол сияқты, табыстары бар бағанда біз мынадай формуланы енгіземіз:

   STRING () - STRING ($ C $ 1)) - STRING (C $ 1)) = INDEX (C2: C29; LOWEST (IF (15000 <= C2: C29; STRING (C2: C29);

   Тағы да, біз тіркесімді енгіземіз Ctrl + Shift + Enter пернелер тіркесімі.

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



6. Көріп отырғаныңыздай, кесте деректермен толтырылған, бірақ оның көрінісі өте тартымды емес, сонымен қатар, күннің мәндері дұрыс емес. Бұл кемшіліктерді түзету қажет. Қате күн тиісті бағандағы ұяшықтардың пішімі жалпы болып табылады және біз күн пішімін орнатуымыз керек. Барлық бағанды, оның ішінде қателер бар ұяшықтарды таңдап, тінтуірдің оң жақ түймешігімен таңдауды басыңыз. Элементте пайда болатын тізімде «Ұяшық форматы ...».



7. Ашылатын пішімдеу терезесінде қойындысын ашыңыз «Нөмір». Блокта «Сан пішімдері» таңдау мәнін таңдаңыз «Күн». Терезенің оң жақ бөлігінде күннің дисплейінің қажетті түрін таңдауға болады. Параметрлер орнатылғаннан кейін түймесін басыңыз. «ОК».



8. Енді күн дұрыс көрсетіледі. Бірақ, көріп тұрғаныңыздай, кестенің барлық түбі қате мәнді қамтитын ұяшықтармен толтырылады. «#NUM!». Іс жүзінде, бұл үлгідегі деректер жеткіліксіз болған ұяшықтар. Олар мүлде бос болмаған жағдайда, олар тартымды болар еді. Осы мақсаттарда шартты пішімдеуді қолданамыз. Кестедегі барлық ұяшықтарды тақырыптан басқа таңдаңыз. Қойындыда болу «Үй» батырмасын басыңыз «Шартты пішімдеу»құралдар блогында орналасқан «Стильдер». Пайда болған тізімде элементті таңдаңыз «Ереже жасау ...».



9. Ашылатын терезеде ереженің түрін таңдаңыз «Құрамында тек ұяшықтарды пішімдеу». Жазуы бар бірінші өрісте «Келесі шарт орындалатын ұяшықтарды ғана пішімдеу» позицияны таңдаңыз «Қателер». Бұдан әрі батырманы басыңыз «Формат ...».



10. Ашылатын пішімдеу терезесінде қойындыға өтіңіз «Қаріп» және сәйкес өріске ақ түсті таңдаңыз. Осы әрекеттерден кейін түймесін басыңыз. «ОК».



11. Кондиционер терезесіне оралғаннан кейін дәл сол атаумен батырманы басыңыз.

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

Сабақ: Excel бағдарламасында шартты пішімдеу

3-әдіс: формуланы пайдаланып бірнеше шарт бойынша іріктеу

Фильтрді пайдаланған кезде формуланы пайдалана отырып, сіз бірнеше шарт бойынша іріктеп ала аласыз. Мысалға, барлық бірдей көз кестесін, сондай-ақ, нәтиже көрсетілетін бос кестені, қазірдің өзінде жасалған сандық және шартты пішімдеуді алып көрейік. Алғашқы лимит 15 000 рубльден асатын таңдаудың төменгі шегіне қойыңыз, екінші шарт - 20 000 рубльдің жоғарғы шегі.

1. Біз үлгідегі шекаралық шарттарды бөлек бағанда енгіземіз.



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

   STRING (C2: C29); STRING (C2: C29) - STRING ($ C $ 1)) - STRING ($ D = $ 2 = C2: C29); C $ 1))

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

   Әр кіргеннен кейін пернелер тіркесімдерін ұмытпаңыз Ctrl + Shift + Enter пернелер тіркесімі.



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

4-әдіс: кездейсоқ іріктеу

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

1. Кестенің сол жағында бір бағанды ​​өткізіп жіберіңіз. Кестедегі деректермен бірге бірінші ұяшыққа қарайтын келесі бағандағы ұяшыққа формуланы енгізіңіз:

   = RAND ()

   Бұл функция кездейсоқ санды көрсетеді. Оны белсендіру үшін батырманы басыңыз ENTER.



2. Кездейсоқ сандардың тұтас бағасын жасау үшін, меңзерді формуласы бар ұяшықтың төменгі оң жақ бұрышына орнатыңыз. Толтыру белгісі пайда болады. Деректерді аяғына дейін үстелге параллель басып, сол жақ тінтуірдің түймесімен оны төмен қарай тартыңыз.



3. Енді бізде кездейсоқ сандарға толы жасушалар бар. Бірақ, ол формуланы қамтиды SLCIS. Біз таза құндылықтармен жұмыс істеуіміз керек. Ол үшін оң жағындағы бос бағанға көшіріңіз. Кездейсоқ сандары бар ұяшықтар ауқымын таңдаңыз. Қойындыда орналасқан «Үй»белгішені нұқыңыз «Көшіру» таспаға арналған.



4. Бос бағанды ​​таңдап, контекстік мәзірді шақыру үшін тінтуірдің оң жақ түймешігімен нұқыңыз. Құралдар тобында «Ендіру параметрлері» элементті таңдаңыз «Мәндер»сандармен пиктограмма ретінде бейнеленген.



5. Содан кейін қойындыда болу «Үй»белгішесін басыңыз «Сұрыптау және сүзгілеу». Ашылмалы тізімде элементтегі таңдауды тоқтатыңыз «Пайдаланушы сұрыптау».



6. Сұрыптау параметрлері терезесі іске қосылады. Параметрдің жанындағы құсбелгіні тексеріңіз. «Менің деректерімтегі тақырыптар бар»егер қақпақ бар болса, бірақ ешқандай құсбелгі жоқ. Алаңда «Іріктеу» кездейсоқ сандардың көшірілген мәндерін қамтитын бағанның атын көрсетіңіз. Алаңда «Сұрыптау» әдепкі параметрлерді қалдырыңыз. Алаңда «Тапсырыс» сіз опцияны келесідей таңдай аласыз «Өсуі», Сонымен «Кему бойынша». Кездейсоқ үлгі үшін бұл маңызды емес. Параметрлер жасалғаннан кейін түймесін басыңыз. «ОК».



7. Осыдан кейін, кестенің барлық мәндері кездейсоқ сандардың өсу немесе кему ретімен реттеледі. Кестеден (5, 10, 12, 15, және т.б.) кез келген бірінші жолдарды қабылдауға болады және оларды кездейсоқ үлгідегі нәтиже деп санауға болады.

Сабақ: Деректерді Excel бағдарламасында сұрыптаңыз және сүзіңіз

Көріп отырғаныңыздай, Excel электрондық кестесіндегі үлгіні автоматты сүзгі арқылы және арнайы формулаларды қолдану арқылы жасауға болады. Бірінші жағдайда, нәтиже бастапқы кестеде, ал екіншісі - жеке аймақта көрсетіледі. Бір шарт бойынша және бірнеше жағдайда таңдау мүмкіндігі бар. Сонымен қатар, функцияны пайдалана отырып кездейсоқ іріктеуді орындауға болады SLCIS.