Excel бағдарламасында үлгіні қалай жасауға болады: 4 жұмыс сәні

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

Сынамаларды алу

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

1-әдіс: кеңейтілген авто толтыруды қолданыңыз

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

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

   

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



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



3. Пайдаланушыны сүзу терезесі іске қосылды. Онда сіз таңдау жасалатын шектеуді орната аласыз. Біз қолданатын сандық формат ұяшығының бағанының ашылмалы тізімінде сіз келесі бес түрдің біреуін таңдай аласыз:
   • тең;
   • тең емес;
   • Көбірек;
   • көп немесе тең;
   • Кішігірім.

   Шартты мысал ретінде келтірейік, мысалы, кіріс мөлшері 10 000 рубльден асатын мәндерді таңдайық. Біз «көбірек» позицияға ауысуды орнатамыз. Дұрыс өрісте «10 000» мәніне сәйкес келеді. Әрекетті орындау үшін «ОК» түймесін басыңыз.



4. Көріп отырғанымыздай, фильтрациядан кейін тек жолдар қалды, оның ішінде кірістердің мөлшері 10 000 рубльден асады.



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

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



6. Енді кестеде тек бір ғана жолдар қалды, онда кірістердің мөлшері 10 000 рубльден кем емес, бірақ 15 000 рубльден аспайды.



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



8. Custom AutoFill іске қосу терезесі қайтадан басталады. 2016 жылғы 4-6 мамыр аралығындағы кестеде нәтижелер таңдауды орындаңыз. Таңдау қосқышында, сіз көріп отырғаныңыздай, одан да көп опциялар, одан да көп параметрлер. «Кейіннен немесе тең» позициясын таңдаңыз. Оң жақтағы өрісте «04.05.2016» мәнін орнатыңыз. Төменгі блокта біз коммутаторды «Жоғарыға немесе одан тең» күйіне орнатамыз. Дұрыс өрісте «06.05.2016» мәнін енгізіңіз. Жағдайдың үйлесімділігі қосқышы Әдепкі позицияда қалдырыңыз - «және». Сүзуді қолдану үшін «OK» түймесін басыңыз.



9. Көріп отырғаныңыздай, біздің тізім одан әрі төмендеді. Енді оның ішінде тек жолдар қалды, онда кірістердің мөлшері 04,05-тен 06.2016-дан бастап 10,0 000-нан 15000 рубльге дейін өзгереді.



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



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



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

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



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



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



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

    

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

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

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

2-әдіс: Массив формуласын қолдану

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

1. Бір парақта, біз тақырыптың бірдей атаулары бар бос үстел жасаймыз.



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

   = Индекс (A2: A29; ең кішкентай (егер (егер (15000 »

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



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



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

   = Индекс (B2: B29; ең кішкентай (егер (егер (15000

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



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

   = Индекс (C2: C29; ең кішкентай (егер (егер (15000

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

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



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



7. Ашылған пішімдеу терезесінде «Сан» қойындысын ашыңыз. «Сандық форматтар» блогында «Күн» мәнін бөліңіз. Терезенің оң жағында, сіз DISTION DISPLAY SIPE таңдауға болады. Параметрлер көрсетілсе, «OK» түймесін басыңыз.



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



9. Ашылған терезеде «Тек тек бар ұяшықтарды пішімдеу» ережесінің түрін таңдаңыз. Бірінші өрісте «Тек жазба астында» форматта келесі шарт, «қате» позициясын таңдаңыз. Әрі қарай, «Пішімдеу ...» түймесін басыңыз.



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



11. Дәл сол атаумен «Жасау» Жүргізу терезесіне оралғаннан кейін басыңыз.

Қазір бізде белгіленген лимитке дайын үлгіде дайындалған үлгі бар.

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

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

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

1. Жеке бағанға, іріктеудің шекаралық шарттарын енгізіңіз.



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

   = Индекс (A2: A29; кіші (егер ($ d $ 2 = c2: c29); line (c2: c29); «»); Жол (C2: C29) -strkok ($ c29) -strkok ($ c2)) - сызық ($ C $ 1))

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

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



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

4-әдіс: Кездейсоқ сынама

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

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

   = Жабысқақ ()

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



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



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



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



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



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



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

Сабақ: Деректерді Excel-ге сұрыптау және сүзу

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