Microsoft Excel бағдарламасында критерийлерді қолдану

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

Қолдану критерийлері

Критерийлер - бұл бағдарлама белгілі бір әрекеттерді орындайтын шарттар. Олар бірқатар кіріктірілген функцияларда қолданылады. Олардың атауында көбінесе өрнек кездеседі IF. Бұл операторлар тобына, ең алдымен, анықтау керек ЕСЕП, COUNTIMO, ТҮЙІНДЕР, SUMMESLIMN. Кірістірілген операторлардан басқа, шартты пішімдеу үшін Excel бағдарламасындағы өлшемдер де қолданылады. Осы кестелік процессордың әртүрлі құралдарымен жұмыс істеу кезінде оларды пайдалану туралы толығырақ қарастырыңыз.

ЕСЕП

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

= COUNTIF (ауқым; шарт)

Көріп отырғаныңыздай, бұл операторда екі дәлел бар. «Ауқым» санау керек парақтағы элементтер массивінің мекен-жайын білдіреді.

«Критерий» - бұл санауға қосылу үшін көрсетілген аймақтың ұяшықтары нақты болу шартын анықтайтын дәлел. Параметр ретінде сандық өрнек, мәтін немесе критерий орналасқан ұяшыққа сілтеме қолданылады. Бұл жағдайда критерийді көрсету үшін келесі таңбаларды пайдалануға болады: "<" (аз), ">" (көбірек), "=" (тең), "" (тең емес) Мысалы, өрнекті көрсетсеңіз "<50", онда есептеу кезінде тек дәлелмен көрсетілген элементтер ғана ескеріледі «Ауқым», сандық мәндер 50-ден кем болатын параметрлерді көрсету үшін осы белгілерді пайдалану барлық басқа параметрлерге қатысты болады, олар төменде осы сабақта талқыланады.

Енді осы оператордың тәжірибеде қалай жұмыс істейтінінің нақты мысалын қарастырайық.

Сонымен, аптасына бес дүкеннен түсетін табыс кестесі бар. 2-ші дүкенде сатудан түскен табыс 15 000 рубльден асатын осы кезеңдегі күндер санын табу керек.

1. Оператор есептеу нәтижесін шығаратын парақ элементін таңдаңыз. Осыдан кейін белгішені нұқыңыз «Кірістіру функциясы».



2. Бастау Функция шеберлері. Біз блокқа көшеміз «Статистикалық». Онда біз есімді тауып, бөлектейміз «COUNTIF». Содан кейін түймесін басыңыз. «Жарайды».



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

   Келесі өрісте «Критерий» дереу таңдау параметрін орнату керек. Біздің жағдайда, бізде кестенің 15000 шамадан асатын элементтерін ғана санау керек. Сондықтан, пернетақтаны пайдаланып өрнекті көрсетілген өріске жүргіземіз ">15000".

   Жоғарыда аталған барлық манипуляциялар аяқталғаннан кейін түймесін басыңыз «Жарайды».



4. Бағдарлама нәтижені санау және активтендіруге дейін таңдалған парақ элементінде көрсетеді Функция шеберлері. Көріп отырғаныңыздай, бұл жағдайда нәтиже 5-ке тең болады, демек, бес ұяшықтағы таңдалған массивте 15000-нан асатын шамалар бар деген сөз, яғни 2-ші дүкенде жеті анализден бес күн ішінде табыс 15000 рубльден асады деген қорытынды жасауға болады.

Сабақ: Excel мүмкіндіктері шебері

COUNTIMO

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

= COUNTIME (шарт_күн1; 1-шарт; шарт = өзгерту 2; жағдай2; ...)

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

«Шарт» сәйкес деректер массивіндегі қай элементтер есептелетінін және қайсысын есептемейтінін анықтайтын өлшемді білдіреді. Әрбір берілген аймақ, егер ол сәйкес болса да, бөлек көрсетілуі керек. Шартты аймақ ретінде пайдаланылатын барлық массивтерде жолдар мен бағандардың саны бірдей болуы өте маңызды.

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

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

• 1-дүкен - 14 000 рубль;
• 2-дүкен - 15 000 рубль;
• 3-дүкен - 24 000 рубль;
• 4-дүкен - 11 000 рубль;
• 5-дүкен - 32 000 рубль.

1. Жоғарыда келтірілген тапсырманы орындау үшін, жұмыс парағының жүгіргісі бар элементін таңдаңыз, онда деректерді өңдеу нәтижесі көрсетіледі. COUNTIMO. Белгішені нұқыңыз «Кірістіру функциясы».



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



3. Жоғарыда келтірілген әрекеттер алгоритмі орындалғаннан кейін аргумент терезесі ашылады COUNTIMO.

   Алаңда «1-шарт диапазоны» 1-ші аптадағы 1-ші кірістер туралы мәліметтер орналасқан жолдың мекен-жайын енгізіңіз. Ол үшін курсорды өріске қойып, кестеде сәйкес жолды таңдаңыз. Терезеде координаттар көрсетіледі.

   Дүкен үшін 1 күнделікті кіріс мөлшерлемесі 14000 рубль, содан кейін далада «1-шарт» өрнекті жазыңыз ">14000".

   Өрістерге «2-шарт диапазоны (3,4,5)» сәйкесінше Store 2, Store 3, Store 4 және Store 5 дүкендерінің апта сайынғы кірісі бар сызықтардың координаталарын енгізу керек.Әрекет осы топтың бірінші дәлеліндей алгоритм бойынша орындалады.

   Өрістерге «2-шарт», «3-шарт», «4-шарт» және «5-шарт» сәйкесінше мәндерді енгіземіз ">15000", ">24000", ">11000" және ">32000". Сіз болжағандай, бұл мәндер тиісті дүкен үшін нормадан асатын кіріс аралықына сәйкес келеді.

   Барлық қажетті деректерді енгізгеннен кейін (барлығы 10 өріс), батырманы басыңыз «Жарайды».



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

Енді тапсырманы өзгертейік. Біз 1-дүкеннен 14000 рубльден асатын, бірақ 17000 рубльден кем табыс алған күндер санын есептеуіміз керек.

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



2. Біз жақында формуланы қолдандық COUNTIMO, енді топқа барудың қажеті жоқ «Статистикалық» Функция шеберлері. Бұл оператордың атауын санаттан табуға болады «Жақында қолданылған 10». Оны таңдап, түймесін басыңыз. «Жарайды».



3. Таныс оператор аргументінің терезесі ашылады. COUNTIMO. Меңзерді өріске салыңыз «1-шарт диапазоны» және тінтуірдің сол жақ батырмасын басып, дүкенде 1 күн ішінде кірісі бар барлық ұяшықтарды таңдаңыз, олар сызықта орналасқан, деп аталады. «Дүкен 1». Осыдан кейін көрсетілген аймақтың координаттары терезеде көрінеді.

   Әрі қарай, жүгіргіні өріске қойыңыз «1-шарт». Мұнда есептеуге қатысатын ұяшықтардағы мәндердің төменгі шегін көрсету керек. Өрнекті көрсетіңіз ">14000".

   Алаңда «2-шарт диапазоны» өріске енгізілген жолмен дәл сол мекен-жайды енгізіңіз «1-шарт диапазоны», яғни қайтадан бірінші розетка үшін кіріс мәндері бар ұяшықтардың координаталарын енгіземіз.

   Алаңда «2-шарт» таңдаудың жоғарғы шегін көрсетіңіз: "<17000".

   Барлық көрсетілген әрекеттер аяқталғаннан кейін түймесін басыңыз «Жарайды».



4. Бағдарлама есептеу нәтижесін береді. Көріп отырғаныңыздай, түпкілікті мәні - бұл 5 зерттелген жеті күннің ішінде 5 күнде бірінші дүкендегі табыс 14 000-нан 17000 рубльге дейін болды.

ТҮЙІНДЕР

Критерийлерді қолданатын басқа оператор ТҮЙІНДЕР. Алдыңғы функциялардан айырмашылығы, ол операторлардың математикалық блогына жатады. Оның міндеті - белгілі бір жағдайға сәйкес келетін ұяшықтардағы мәліметтерді жинақтау. Синтаксис келесідей:

= SUMMES (ауқым; шарт; [қосымшаның ауқымы])

Дәлел «Ауқым» жағдайға сәйкестігі тексерілетін ұяшықтардың ауданын көрсетеді. Шын мәнінде, ол бірдей аттың функциясының дәлелі сияқты бірдей принциппен орнатылады ЕСЕП.

«Критерий» - қосылатын деректер аймағындағы ұяшықтарды таңдауды көрсететін қажетті дәлел. Дәлелдеу принциптері біз жоғарыда қарастырған алдыңғы операторлардың дәлелдерімен бірдей.

«Қорытынды жиынтығы» Бұл қосымша дәлел. Ол жиынтық орындалатын массивтің нақты ауданын көрсетеді. Егер сіз оны жіберіп алсаңыз және оны көрсетпесеңіз, әдепкіде ол талап етілетін аргументтің мәніне тең деп саналады. «Ауқым».

Енді, әдеттегідей, осы оператордың тәжірибеде қолданылуын қарастырыңыз. Сол кестеге сүйене отырып, біздің алдымызда 2017 жылдың 11 наурызынан басталатын кезеңдегі 1-ші дүкендегі табыс мөлшерін есептеу міндеті тұр.

1. Нәтиже шығатын ұяшықты таңдаңыз. Белгішені нұқыңыз. «Кірістіру функциясы».



2. Бару Мүмкіндік шебері блокта «Математикалық» атын тауып, ерекшелеу ТҮЙІНДЕР. Түймесін басыңыз «Жарайды».



3. Функция аргументінің терезесі басталады ТҮЙІНДЕР. Бұл көрсетілген оператордың дәлелдеріне сәйкес үш өріс бар.

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

   Біз 11-ші наурыздан бастап өріске түскен қаржыны ғана қосуымыз керек «Критерий» мәнін жүргізіңіз ">10.03.2017".

   Алаңда «Қорытынды жиынтығы» көрсетілген өлшемдерге сәйкес келетін мәндер жинақталатын ауданды көрсету керек. Біздің жағдайда, бұл сызықтық кірістің мәні «Дүкен1». Парақ элементтерінің сәйкес массивін таңдаңыз.

   Барлық көрсетілген мәліметтер енгізілгеннен кейін түймесін басыңыз «Жарайды».



4. Осыдан кейін функциямен деректерді өңдеу нәтижесі жұмыс парағының бұрын көрсетілген элементінде көрсетіледі. ТҮЙІНДЕР. Біздің жағдайда ол 47921.53-ке тең. Бұл 2017 жылғы 11 наурыздан бастап және талданатын кезеңнің соңына дейін 1-дүкеннің жалпы кірісі 47 921,53 рубльді құрағанын білдіреді.

SUMMESLIMN

Функцияларға назар аудара отырып, өлшемдерді қолданатын операторларды зерттеуді аяқтаймыз SUMMESLIMN. Бұл математикалық функцияның мақсаты бірнеше параметрлер бойынша таңдалған кестенің көрсетілген аудандарының мәндерін қорытындылау болып табылады. Көрсетілген оператордың синтаксисі келесідей:

= SUMMER (сом_ ауқым; шарт_көрсеткіш1; шарт1; жағдай_рен2; жағдай2; ...)

«Қорытынды жиынтығы» - бұл дәлел, белгілі бір өлшемге сәйкес келетін ұяшықтар қосылатын массивтің адресі.

«Жағдай диапазоны» - аргументтер, бұл мәліметтер жиынтығы, жағдайға сәйкестігі тексеріледі;

«Шарт» - қосу үшін таңдау өлшемін білдіретін дәлел.

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

Бұл оператордың сауда нүктелеріндегі сатылымдардан түсетін таблица контекстіндегі мәселелерді шешуге қалай қолданылатынын көрейік. Бізге 1-дүкеннің 2017 жылғы 09 наурыздан 13 наурызға дейінгі кезеңдегі кірісін есептеу керек. Бұл жағдайда кірісті қорытындылау кезінде табыс 14 000 рубльден асатын күндерді ғана ескеру керек.

1. Тағы да, жиынтықты көрсету үшін ұяшықты таңдап, белгішені нұқыңыз «Кірістіру функциясы».



2. Ішінде Функция шеберіБіріншіден, біз блокқа көшеміз «Математикалық», сонда біз аталатын элементті таңдаймыз SUMMESLIMN. Түймесін басыңыз. «Жарайды».



3. Жоғарыда көрсетілген оператор операторының аргументтері терезесі іске қосылды.

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

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

   Меңзерді өріске салыңыз «1-шарт». Бірінші шарт - біз деректерді 09 наурыздан ерте жинамаймыз. Сондықтан, мәнді енгізіңіз ">08.03.2017".

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

   Меңзерді өріске қойыңыз «2-шарт». Екінші шарт - бұл түсім қосылатын күндер 13 наурыздан кешіктірілмеуі керек. Сондықтан біз келесі өрнекті жазамыз: "<14.03.2017".

   Далаға барыңыз «2-шарт диапазоны». Бұл жағдайда біз мекен-жайы жинақтау массиві ретінде енгізілген бірдей жиымды таңдауымыз керек.

   Көрсетілген массивтің мекен-жайы терезеде көрсетілгеннен кейін өріске өтіңіз «3-шарт». Қорытындыға тек құны 14 000 рубльден асатын құндылықтар ғана қатысатындығын ескере отырып, біз келесі сипаттаманы енгіземіз: ">14000".

   Соңғы әрекетті орындағаннан кейін түймесін басыңыз «Жарайды».



4. Бағдарлама нәтижені параққа шығарады. Бұл 62491,38-ге тең. Бұл 2017 жылғы 9 наурыздан 13 наурызға дейінгі кезең үшін оны 14000 рубльден асатын күндерге қосқандағы табыс сомасы 62 491,38 рубльді құрағанын білдіреді.

Шартты форматтау

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

Біз кестедегі ұяшықтарды көк түспен таңдаймыз, онда күнделікті құндылықтар 14 000 рубльден асады.

1. Біз кестеде элементтердің барлық массивін таңдаймыз, олар сауда нүктелерінің күндік кірісін көрсетеді.



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



3. Пішімдеу ережесін құру терезесі іске қосылды. Ереже түрінің таңдау аймағында атауды таңдаңыз «Тек құрамында ұяшықтарды пішімдеу». Шарт блогының бірінші өрісінде мүмкін опциялар тізімінен таңдаңыз «Ұяшық мәні». Келесі өрісте орынды таңдаңыз Тағы. Соңғысында - кестенің элементтерін пішімдегіңіз келетін мәннің өзін көрсетіңіз. Бізде ол 14000. Пішімдеу түрін таңдау үшін батырманы басыңыз «Пішім ...».



4. Пішімдеу терезесі іске қосылды. Қойындыға өтіңіз «Толтыру». Түстерді толтырудың ұсынылған нұсқаларынан сол жақ батырманы басып, көк түстерді таңдаңыз. Таңдалған түс аймақта көрсетілгеннен кейін Үлгітүймесін басыңыз «Жарайды».



5. Пішімдеу ережесін құру терезесі автоматты түрде қайтарылады. Бұл сонымен қатар далада Үлгі көк түс көрсетіледі. Мұнда біз бір әрекетті орындауымыз керек: батырманы шертіңіз «Жарайды».



6. Соңғы әрекеттен кейін 14000-нан асатын таңдалған массивтің барлық ұяшықтары көк түспен толтырылады.

Шартты форматтау мүмкіндіктері туралы қосымша ақпарат жеке мақалада талқыланады.

Сабақ: Excel-де шартты форматтау

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