Microsoft Excel - тек электрондық кесте өңдеушісі ғана емес, сонымен қатар әртүрлі есептеулер үшін ең қуатты бағдарлама. Соңғы, бірақ кем дегенде, бұл мүмкіндік кіріктірілген функциялармен бірге келді. Кейбір функциялардың (операторлардың) көмегімен әдетте аталатын критерийлер болып табылатын есептеулердің шарттарын анықтауға болады. Excel бағдарламасында жұмыс істеу кезінде оларды қалай пайдалануға болатынын білейік.
Критерийлерді қолдану
Критерийлер - бұл бағдарлама белгілі бір әрекеттерді жүзеге асыратын шарттар. Олар әртүрлі кіріктірілген функцияларда қолданылады. Олардың аттарында жиі сөз тіркесі бар «IF». Операторлардың бұл тобы ең алдымен байланыстырылуы керек COUNTES, COUNTERSILN, Сомалар, SUMMESLIMN. Кірістірілген операторлардан басқа, Excel бағдарламасындағы критерийлер шартты пішімдеуде де қолданылады. Осы кесте процессорының әртүрлі құралдарымен жұмыс істеу кезінде оларды пайдалануды қарастырыңыз.
COUNTES
Оператордың негізгі міндеті COUNTESстатистикалық топқа жататын, белгілі бір шартты қанағаттандыратын жасушалардың әртүрлі мәндерімен саналады. Оның синтаксисі келесідей:
= COUNTERS (ауқым, критерий)
Көріп отырғандай, бұл оператор екі дәлелге ие. «Ауқым» - есептегіш жасалуы керек парақтағы элементтің массиві.
«Критерий» - бұл көрсетілген аумақтың ұяшықтары санаққа қосылуы үшін қажетті шартты белгілейтін дәлел. Параметр ретінде сандық өрнек, мәтін немесе критерий бар ұяшыққа сілтеме пайдаланылуы мүмкін. Бұл жағдайда критерийді көрсету үшін келесі таңбаларды қолдануға болады: "<" («аз»), ">" («көп»), "=" (тең), "" («тең емес»). Мысалы, өрнекті көрсетсеңіз "<50", онда есептеу тек дәлелмен көрсетілген элементтерді ескереді «Ауқым»50-ден аз сандық мәндер бар. Бұл таңбаларды параметрлерді көрсету үшін пайдалану төмендегі осы сабақта талқыланатын барлық басқа нұсқалар үшін маңызды болады.
Енді осы оператордың іс жүзінде қалай жұмыс істейтіні туралы нақты мысал қарастырайық.
Мысалы, аптасына бес дүкеннің табысын көрсететін кесте бар. Осы кезеңде дүкеннің сатудан түскен кірісі 15 мың рубльден асатын күндер саны туралы білуіміз керек.
- Оператор есептеу нәтижесін шығаратын парақ элементін таңдаңыз. Содан кейін белгішені басыңыз «Функцияны кірістіру».
- Іске қосу Функцияның шеберлері. Блоктауға жылжытыңыз «Статистикалық». Онда біз атауды таңдап аламыз «COUNTES». Содан кейін түймені басу керек «ОК».
- Жоғарыда келтірілген оператордың дәлел терезесін белсендіру. Алаңда «Ауқым» санақ жасалатын ұяшықтар аумағын көрсетіңіз. Біздің жағдайда жолдың мазмұнын таңдаңыз. «2-дүкен»онда күнделікті кірістің мәндері орналасқан. Жүгіргіні көрсетілген өріске қойыңыз және сол жақ тінтуірдің түймешігін басып ұстап тұрыңыз кестеде тиісті массивді таңдаңыз. Таңдалған жиымның мекенжайы терезеде пайда болады.
Келесі өрісте «Критерий» тікелей таңдау параметрін орнату қажет. Біздің жағдайда, мәнді 15,000 асатын кестенің элементтерін ғана санауыңыз керек, сондықтан пернетақтаны пайдаланып, көрсетілген өріске өрнек енгіземіз. ">15000".
Жоғарыдағы барлық манипуляцияларды орындағаннан кейін, түймені басыңыз. «ОК».
- Бағдарлама активтендіру алдында таңдалған парақтың элементінде нәтижені есептеп, көрсетеді. Функцияның шеберлері. Көріп отырғаныңыздай, бұл жағдайда нәтиже 5-ке тең. Демек, бес ұяшықтағы таңдалған массивте 15 000-нан асатын мәндер бар, яғни Store 2-де талдаған жеті күннің ішінде 15 000 рубльден асатыны туралы қорытынды жасауға болады.
Сабақ: Excel бағдарламасындағы функциялар мастері
COUNTERSILN
Келесі критерийлерді шешетін функция COUNTERSILN. Ол сондай-ақ операторлардың статистикалық тобына жатады. Тапсырма COUNTERSILN - белгілі бір жиынтығымен қанағаттандыратын массивтің ұяшықтарын санау. Бұл біреуді емес, бірнеше параметрді көрсете алатын және алдыңғы оператордан ерекшеленетін факт. Синтаксис мынадай:
= COUNTRY (condition_range1; condition1; condition_range2; condition2; ...)
«Шарт ауқымы» алдыңғы нұсқаның бірінші дәлеліне ұқсас. Яғни, бұл белгілі бір шарттарға сай жасалатын ұяшыққа сілтеме болып саналады. Бұл оператор бірден бірнеше осындай аумақты бірден көрсетуге мүмкіндік береді.
«Шарт» тиісті деректер жиынтығының қай элементтері есептелетінін және қайсысы болмайтынын анықтайтын критерий болып табылады. Әрбір берілген деректер аймағы, егер ол сәйкес келсе де, бөлек көрсетуге тиіс. Шартты аудандар ретінде пайдаланылатын барлық массивтердің жолдар мен бағандар саны бірдей болуы қажет.
Бірдей деректер аймағының бірнеше параметрлерін орнату үшін, мысалы, белгілі бір саннан асатын, бірақ басқа саннан аз болатын ұяшықтар санын есептеу үшін келесі аргумент қолданылады: «Шарт ауқымы» бірдей массивді бірнеше рет анықтайды. Бірақ дәл сол кезде дәлелдер «Шарт» әр түрлі критерийлерді көрсету керек.
Осындай кестені мысал ретінде дүкендердің апта сайын сатылымымен бірге қолданып көрейік. Аптаның барлық күндерінде анықталған барлық сауда нүктелеріндегі табыс олар үшін белгіленген нормаға жеткенде. Табыс нормалары келесідей:
- Дүкен 1 - 14 мың рубль;
- Дүкен 2 - 15 000 рубль;
- 3 - 24 мың рубль сақтаңыз;
- 4 - 11 000 рубль сақтаңыз;
- 5 - 32 000 рубль сақтаңыз.
- Жоғарыда көрсетілген тапсырманы орындау үшін жұмыс парағының элементін курсормен бірге таңдаңыз, онда деректерді өңдеу нәтижесі көрсетіледі. COUNTERSILN. Біз белгішені басамыз «Функцияны кірістіру».
- Келу Функция шебері, қайтадан блоктауға көшу «Статистикалық». Тізімде атау болуы керек COUNTERSILN және оны таңдау жасаңыз. Көрсетілген әрекетті орындағаннан кейін, түймені басу керек. «ОК».
- Жоғарыда көрсетілген әрекет алгоритмін орындағаннан кейін дәлел терезесі ашылады. COUNTERSILN.
Алаңда «Range1» дүкеннің аптадағы табысы туралы деректер келтірілген желінің мекенжайын енгізуіңіз керек. Ол үшін жүгіргіні өріске қойып, кестеде сәйкес жолды таңдаңыз. Терезеде координаталар көрсетіледі.
Дүкеннің күнделікті кіріс мөлшерлемесі 14 мың рубль болғанын ескерсек, сол жерде «1-шарт» өрнекті енгізу ">14000".
Өрістерде «Шарттың ауқымы2 (3,4,5)» Сәйкесінше, 2-дүкен, 3-дүкен, 4-дүкен және 5 дүкеннің апта сайынғы табыстары бар сызықтардың координаттары енгізілуге тиіс. Біз осы топтың бірінші дәлеліне ұқсас әрекетті сол алгоритмге сәйкес жүзеге асырамыз.
Өрістерде «2-шарт», «Condition3», «4-шарт» және «Condition5» біз құндылықтарды сәйкес келтіреміз ">15000", ">24000", ">11000" және ">32000". Сіз болжап отырғандай, бұл мәндер тиісті дүкен үшін нормадан асып түсетін кіріс интервалына сәйкес келеді.
Барлық қажетті деректерді енгізгеннен кейін (барлығы 10 өріс), түймені басыңыз «ОК».
- Бағдарлама экрандағы нәтижені есептеп, көрсетеді. Көріп отырғанымыздай, бұл сан 3-ге тең. Бұл дегеніміз, талданатын аптадан бастап үш күннің ішінде барлық бөлімшелердегі табыс олар үшін белгіленген мөлшерден асып түсті.
Енді тапсырманы сәл өзгертейік. Біз 1-дүкеннің түсімі 14 мың рубльден асатын күндер санын есептеп шығаруымыз керек, бірақ 17 мың рубльден аз.
- Жүгіргіні нәтиже санау парағында көрсетілетін элементке қойыңыз. Біз белгішені басамыз «Функцияны кірістіру» парақтың жұмыс аймағы бойынша.
- Жақында формуланы қолданғандықтан COUNTERSILN, енді топқа барудың қажеті жоқ «Статистикалық» Функцияның шеберлері. Бұл оператордың атауын санатта табуға болады «10 Жақында қолданылған». Оны таңдаңыз және түймесін басыңыз. «ОК».
- Бізге бұрыннан таныс операторлық дәлелдер терезесі ашылады. COUNTERSILN. Курсорды өріске қойыңыз «Range1» және тінтуірдің сол жақ батырмасын қысып қойып, Store 1 күндері кірістері бар барлық ұяшықтарды таңдаңыз. Олар жолда орналасқан «1-дүкен». Осыдан кейін көрсетілген аймақтың координаттары терезеде көрсетіледі.
Содан кейін курсорды өріске орнатыңыз «Condition1». Мұнда есептеуге қатысатын ұяшықтардағы мәндердің төменгі шегі көрсетілу керек. Өрнекті көрсетіңіз ">14000".
Алаңда «2-шарт аралығы» біз сол мекен-жайға далада кіргендей кіреміз «Range1», яғни, бірінші шығыршықтың кірісі бар ұяшықтардың координаттарын қайтадан енгіземіз.
Алаңда «2-шарт» таңдаудың жоғарғы шегін белгілеңіз: "<17000".
Көрсетілген барлық әрекеттер орындалғаннан кейін, түймені басыңыз. «ОК».
- Бағдарлама есептеудің нәтижесін береді. Көріп отырғанымыздай, жалпы құны 5-ті құрайды. Бұл дегеніміз, жеті оқушының 5 күнінде алғашқы дүкендегі кіріс 14,000-нан 17 000 рубльге дейін болды.
Сомалар
Критерийлерді қолданатын басқа оператор Сомалар. Алдыңғы функциялардан айырмашылығы операторлардың математикалық блогына қатысты. Оның міндеті - белгілі бір шартқа сәйкес келетін ұяшықтардағы деректерді қосу. Синтаксис:
= SUMMERS (ауқым; критерий; [sum_range]]
Аргумент «Ауқым» сәйкесінше тексерілетін ұяшықтар аумағына меңзейді. Шын мәнінде, ол сол атаудың функционалдық дәлелі сияқты принциппен белгіленеді. COUNTES.
«Критерий» - қосу үшін көрсетілген деректер аймағынан ұяшықтарды таңдау параметрін орнататын міндетті дәлел. Жоғарыда қарастырылған бұрынғы операторлардың ұқсас дәлелдерімен дәлелдеу қағидалары.
«Жиынтық ауқымы» - Бұл қосымша дәлел. Ол жиынтықтың орындалатын массивтің нақты аймағын көрсетеді. Егер сіз оны өткізіп тастасаңыз және көрсетпеген болсаңыз, әдепкі бойынша ол қажетті аргумент мәніне тең деп есептеледі «Ауқым».
Енді, әрдайым, бұл оператордың қолданысын іс жүзінде қарастырайық. Сол кестеге сүйене отырып, дүкенімізде 11.03.2017 жылдан басталатын кезеңде кіріс көлемін есептеу міндеті тұр.
- Нәтиже көрсетілетін ұяшықты таңдаңыз. Белгішені басыңыз «Функцияны кірістіру».
- Келу Функция шебері блокта «Математикалық» атын тауып, таңдап алыңыз «SUMMESLI». Біз батырманы басамыз «ОК».
- Функция дәлелінің терезесі басталады. Сомалар. Көрсетілген оператордың аргументтеріне сәйкес келетін үш өріс бар.
Алаңда «Ауқым» біз шарттарды сақтау үшін тексерілетін мәндер орналасқан кестенің аумағын енгіземіз. Біздің жағдайда бұл күндер кестесі болады. Курсорды осы өріске енгізіп, күндерді қамтитын барлық ұяшықтарды таңдаңыз.
Өйткені, біз тек 11 наурыздан бастап, сол салада кірістерді қосуымыз керек «Критерий» біз құндылыққа жетеміз ">10.03.2017".
Алаңда «Жиынтық ауқымы» Сізге көрсетілген критерийлерге сәйкес келетін мәнді анықтау керек. Біздің жағдайда бұл жолдың кіріс мәндері. «Shop1». Парақтардың тиісті элементтер жиынын таңдаңыз.
Барлық көрсетілген мәліметтерді енгізгеннен кейін түймесін басыңыз «ОК».
- Осыдан кейін функция бойынша деректерді өңдеу нәтижесі жұмыс парағының бұрын көрсетілген элементінде көрсетіледі. Сомалар. Біздің жағдайда 47921,53 тең. Бұл 11.03.2017 жылдан бастап және талдау кезеңінің аяғына дейін 1-дүкеннің жалпы түсімі 47 921,53 рубльді құрады.
SUMMESLIMN
Функцияға назар аударатын критерийлерді қолданатын операторларды зерттеуді аяқтаймыз SUMMESLIMN. Бұл математикалық функцияның міндеті кестенің аталған бағыттарының мәндерін бірнеше параметрге сәйкес іріктеу болып табылады. Бұл оператордың синтаксисі:
= SUMMESLIMN (sum_range_range; condition_range1; condition1; condition_range2; condition2; ...)
«Жиынтық ауқымы» - Бұл дәлел, массивтің мекен-жайы, белгілі бір критерийге сәйкес келетін ұяшықтар қосылады.
«Шарт ауқымы» - шарттың сақталуы үшін тексерілген деректер массивін білдіретін дәлел;
«Шарт» - Қосылу үшін іріктеу критерийлерін білдіретін дәлел.
Бұл функция ұқсас операторлардың бірнеше жиынтығымен бірден әрекет етеді.
Келтірейік, бұл оператор сату нүктелеріндегі сату кестесінің контекстінде мәселелерді шешуге қалай қолданылатынын көрейік. Біз дүкен 1-ден 2017 жылдың 09 наурызынан 13 наурызына дейінгі кезеңде кірісті есептеуіміз керек. Бұл жағдайда табыстың жиынтығы 14 000 рубльден асатын күндер ғана ескерілуі тиіс.
- Барлығын көрсету үшін ұяшықты қайта таңдап, белгішені басыңыз. «Функцияны кірістіру».
- Ин Функция шеберіЕң алдымен біз блокқа көшеміз. «Математикалық», сонда біз аталған элементті таңдаймыз «SUMMESLIMN». Түймешікті басыңыз «ОК».
- Жоғарыда көрсетілген атауы оператордың аргументтерінің терезесі іске қосылды.
Курсорды өріске орнатыңыз «Жиынтық ауқымы». Кейінгі аргументтерден айырмашылығы, бұл белгілі бір критерийлерге сәйкес келетін деректердің жинақталуы жасалатын құндылықтардың бір түрінің бірі болып табылады. Содан кейін жолдың ауданын таңдаңыз «Shop1»Оған тиісті шығыс кірісінің мәндері орналастырылады.
Мекенжайды терезеден көрсеткеннен кейін, өріске өтіңіз «Range1». Мұнда жолдың координаттарын күндермен көрсету керек. Біз тінтуірдің сол жақ түймесінің клипін жасап, үстелдегі барлық күндерді таңдаңыз.
Курсорды өріске қойыңыз «Condition1». Бірінші шарт 09 наурыздан кешіктірмей деректерді жинақтайтынымыз. Сондықтан біз мәнді енгіземіз ">08.03.2017".
Аргументке көшіңіз «2-шарт аралығы». Мұнда сіз далада жазылған бірдей координаттарды енгізуіңіз керек «Range1». Біз мұны дәл солай жасаймыз, яғни күнді сызықпен бөле отырып.
Курсорды өріске орнатыңыз «2-шарт». Екінші шарты - кірістердің қосылатын күндері 13 наурыздан кешікпеуі керек. Сондықтан келесі өрнек жазыңыз: "<14.03.2017".
Өрістерге барыңыз «2-шарт аралығы». Бұл жағдайда, мекен-жай біріктіру жиынтығы ретінде енгізілген бірдей массивді таңдау керек.
Көрсетілген жиымның мекенжайы терезеде көрсетілсе, өріске өтіңіз «Condition3». Жинаққа тек 14 000 рубльден асатын құндылықтар ғана қатысатындығын ескерсек, келесі жазба жасалады: ">14000".
Соңғы әрекеттен кейін түймені басыңыз «ОК».
- Бағдарлама парақтағы нәтижені көрсетеді. Бұл 62491.38 тең. Бұл дегеніміз, 2017 жылдың 09-нан 13-наурызына дейін 14 000 рубльден асатын күндерге қосқан кіріс сомасы 62 491,38 рубльді құрады.
Шартты пішімдеу
Қандай критерийлермен жұмыс жасағанда, біз сипаттайтын соңғы құрал шартты пішімдеу болып табылады. Көрсетілген шарттарға сай келетін форматтағы ұяшықтардың нақты түрін орындайды. Шартты пішімдеумен жұмыс істеу мысалын қараңыз.
Кестедегі сол ұяшықтарды көк түске бөліп, күннің мәндері 14 000 рубльден асады.
- Кестеде бөлшек сауда нүктелерінің күнделікті табысын көрсететін барлық элементтер жиынын таңдаңыз.
- Қойындыға жылжытыңыз «Үй». Біз белгішені басамыз «Шартты пішімдеу»блокқа орналастырылған «Стильдер» таспаға арналған. Әрекеттер тізімі ашылады. Біз оған ұстанамыз «Ереже жасау ...».
- Пішімдеу ережесін жасау терезесі іске қосылады. Түрді таңдау ережелерінің өрісінде атауды таңдаңыз «Құрамында тек ұяшықтарды пішімдеу». Бірінші жолда ықтимал таңдаулар тізімінен шартты блокты таңдаңыз «Ұяшық мәні». Келесі өрісте позицияны таңдаңыз «Көбірек». Соңғы кезде біз мәнді өзі көрсетеміз, оның үлкен бөлігі кестенің элементтерін пішімдеу болып табылады. Бізде 14,000 пішімдеу түрін таңдау үшін түймесін басыңыз. «Формат ...».
- Пішімдеу терезесі іске қосылды. Қойындыға жылжытыңыз «Толтыру». Ұсынылған толтыру түстің опцияларынан көк тінтуір түймешігімен басып, көк түсті таңдаңыз. Таңдалған түс көрсетілімінен кейін «Үлгі»түймені басыңыз «ОК».
- Ол пішімдеу ережесін жасау терезесіне автоматты түрде оралады. Бұл аймақта да «Үлгі» көк түспен көрсетіледі. Мұнда бір ғана әрекетті орындау керек: түймені басыңыз «ОК».
- Соңғы әрекеттен кейін таңдалған массивтің барлық ұяшықтары 14000-нан жоғары, көк түспен толтырылады.
Шартты пішімдеу мүмкіндіктері туралы қосымша ақпарат бөлек мақалада сипатталған.
Сабақ: Excel бағдарламасында шартты пішімдеу
Көріп отырғанымыздай, өз жұмысында критерийлерді қолданатын құралдардың көмегімен Excel бағдарламасында әртүрлі тапсырмаларды шешуге болады. Бұл сандар мен мәндерді санау, сондай-ақ пішімдеу, сондай-ақ басқа да көптеген тапсырмаларды орындау. Осы бағдарламада критерийлермен жұмыс істейтін негізгі құралдар, яғни белгілі бір жағдайларда, бұл әрекет іске қосылғанда, кіріктірілген функциялар жиынтығы, сондай-ақ шартты пішімдеу.
