Праграма Microsoft Excel з'яўляецца не проста таблічным рэдактары, а яшчэ і наймагутным дадаткам для розных вылічэнняў. Не ў апошнюю чаргу такая магчымасць з'явілася дзякуючы убудаваным функцый. З дапамогай некаторых функцый (аператараў) можна задаваць нават ўмовы вылічэнні, якія прынята называць крытэрамі. Давайце падрабязней даведаемся, якім чынам можна іх выкарыстоўваць пры працы ў Эксэля.
прымяненне крытэрыяў
Крытэрыі ўяўляюць сабой ўмовы, пры якіх праграма выконвае пэўныя дзеянні. Яны ўжываюцца ў цэлым шэрагу ўбудаваных функцый. У іх назве часцей за ўсё прысутнічае выраз "КАЛІ". Да дадзенай групы аператараў, перш за ўсё, трэба аднесці СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СУММЕСЛИ, СУММЕСЛИМН. Акрамя ўбудаваных аператараў, крытэрыі ў Excel выкарыстоўваюцца таксама пры ўмоўным фарматаванні. Разгледзім іх прымяненне пры працы з рознымі інструментамі дадзенага таблічнага працэсара больш падрабязна.
СЧЁТЕСЛИ
Галоўнай задачай аператара СЧЁТЕСЛИ, Які адносіцца да статыстычнай групе, з'яўляецца падлік занятых рознымі значэннямі вочак, якія задавальняюць вызначаным зададзеным умове. Яго сінтаксіс наступны:
= СЧЁТЕСЛИ (дыяпазон; крытэр)
Як бачым, у дадзенага аператара два аргументу. "Дыяпазон" ўяўляе сабой адрас масіва элементаў на лісце, у якіх варта зрабіць падлік.
"Крытэрый" - гэта аргумент, які задае ўмова, што менавіта павінны ўтрымліваць ячэйкі названай галіне, каб быць уключанымі ў падлік. У якасці параметру можа быць выкарыстана лікавае выраз, тэкст або спасылка на вочка, у якой крытэрый утрымліваецца. Пры гэтым, для ўказанні крытэра можна выкарыстоўваць наступныя знакі: "<" ("Менш"), ">" ("Больш"), "=" ("Роўна"), "" ("Ня роўнае"). Напрыклад, калі задаць выраз "<50", То пры падліку будуць Улічваюцца толькі складнікі, зададзеныя аргументам "Дыяпазон", У якіх знаходзяцца лікавыя значэння менш за 50. Выкарыстанне дадзеных знакаў для ўказанні параметраў будуць актуальнымі і для ўсіх іншых варыянтаў, пра якія пойдзе гаворка ў дадзеным ўроку ніжэй.
А зараз давайце на канкрэтным прыкладзе паглядзім, як працуе дадзены аператар на практыцы.
Такім чынам, маецца табліца, дзе прадстаўлена выручка па пяці крамах за тыдзень. Нам трэба даведацца колькасць дзён за гэты перыяд, у якіх у Краме 2 даход ад рэалізацыі перавысіў 15000 рублёў.
- Вылучаем элемент ліста, у які аператар будзе выводзіць вынік вылічэнні. Пасля гэтага пстрыкаем па піктаграме "Уставіць функцыю".
- вырабляецца запуск майстры функцый. Здзяйсняем перасоўванне ў блок "Статыстычныя". Там знаходзім і вылучаем найменне "СЧЁТЕСЛИ". Затым варта клацнуть па кнопцы "OK".
- Адбываецца актывацыя вокны аргументаў вышэйпаказанага аператара. У полі "Дыяпазон" варта паказаць вобласць вочак, сярод якіх будзе праводзіцца падлік. У нашым выпадку варта вылучыць змесціва радкі "Крама 2", У якой размешчаны значэнні выручкі па днях. Ставім курсор у паказаны поле і, заціснуўшы левую кнопку мышы, вылучаем адпаведны масіў ў табліцы. Адрас вылучанага масіва адлюструецца ў акне.
У наступным поле "Крытэрый" як раз трэба задаць непасрэдны параметр адбору. У нашым выпадку трэба падлічыць толькі тыя элементы табліцы, у якіх значэнне перавышае 15000. Таму з дапамогай клавіятуры ўбіваем ў паказаны поле выраз ">15000".
Пасля таго, як усе вышэйпаказаныя маніпуляцыі зроблены, клацает па кнопцы "OK".
- Праграма вырабляе падлік і выводзіць вынік у элемент ліста, які быў выдзелены перад актывацыяй майстры функцый. Як бачым, у дадзеным выпадку вынік роўны ліку 5. Гэта азначае, што ў вылучаным масіве ў пяці вочках знаходзяцца значэння перавышаюць 15000. Гэта значыць, можна зрабіць выснову, што ў краме 2 у пяці днях з аналізаваных сямі выручка перавысіла 15000 рублёў.
Урок: Майстар функцый у праграме Эксэля
СЧЁТЕСЛИМН
Наступнай функцыяй, якая аперуе крытэрамі, з'яўляецца СЧЁТЕСЛИМН. Яна таксама ставіцца да статыстычнай групе аператараў. задачай СЧЁТЕСЛИМН з'яўляецца падлік вочак у паказаным масіве, якія задавальняюць вызначаным набору умоў. Менавіта той факт, што можна задаць не адзін, а некалькі параметраў, і адрознівае гэтага аператара ад папярэдняга. Сінтаксіс наступны:
= СЧЁТЕСЛИМН (диапазон_условия1; условие1; диапазон_условия2; условие2; ...)
"Дыяпазон ўмовы" з'яўляецца ідэнтычным першаму аргументу папярэдняга аператара. Гэта значыць, ён уяўляе сабой спасылку на вобласць, у якой будзе праводзіцца падлік вочак, якія задавальняюць названым умовам. Дадзены аператар дазваляе задаць адразу некалькі такіх абласцей.
"Умова" ўяўляе сабой крытэр, які вызначае, якія элементы з адпаведнага масіва дадзеных ўвойдуць у падлік, а якія ня ўвойдуць. Кожнай зададзенай вобласці дадзеных трэба паказваць ўмова асобна, нават у тым выпадку, калі яно супадае. Абавязкова патрабуецца, каб усе масівы, якія выкарыстоўваюцца ў якасці абласцей ўмовы, мелі аднолькавую колькасць радкоў і слупкоў.
Для таго, каб задаць некалькі параметраў адной і той жа вобласці дадзеных, напрыклад, каб падлічыць колькасць вочак, у якіх размешчаны велічыні больш вызначанага ліку, але менш іншага колькасці, варта ў якасці аргументу "Дыяпазон ўмовы" некалькі разоў пазначыць адзін і той жа масіў. Але пры гэтым у якасці адпаведных аргументаў "Умова" варта паказваць розныя крытэры.
На прыкладзе ўсё той жа табліцы з тыднёвай выручкай крам паглядзім, як гэта працуе. Нам трэба даведацца колькасць дзён тыдня, калі прыбытак ва ўсіх названых гандлёвых кропках дасягаў усталяванай для іх нормы. Нормы выручкі наступныя:
- Крама 1 - 14000 рублёў;
- Крама 2 - 15000 рублёў;
- Крама 3 - 24000 рублёў;
- Крама 4 - 11000 рублёў;
- Крама 5 - 32000 рублёў.
- Для выканання вышэйпаказанай задачы, вылучаем курсорам элемент працоўнага ліста, куды будзе выводзіцца вынік апрацоўкі дадзеных СЧЁТЕСЛИМН. Клацает па абразку "Уставіць функцыю".
- перайшоўшы ў майстар функцый, Зноў перамяшчаемся ў блок "Статыстычныя". У пераліку варта адшукаць найменне СЧЁТЕСЛИМН і вырабіць яго вылучэнне. Пасля выканання названага дзеяння патрабуецца вырабіць націск на кнопку "OK".
- Услед за выкананнем вышэйпаказанага алгарытму дзеянняў адкрываецца акно аргументаў СЧЁТЕСЛИМН.
У полі "Дыяпазон условия1" варта ўвесці адрас радкі, у якой размешчаны дадзеныя па выручцы Крамы 1 за тыдзень. Для гэтага ставім курсор у полі і вылучаем адпаведны радок у табліцы. Каардынаты адлюстроўваюцца ў акне.
Улічваючы, што для Крамы 1 дзённая норма выручкі складае 14000 рублёў, то ў поле "Умова 1" ўпісваем выраз ">14000".
У палі "Дыяпазон условия2 (3,4,5)" варта ўнесці каардынаты радкоў з тыднёвай выручкай адпаведна Крамы 2, Крамы 3, Крамы 4 і Крамы 5. Дзеянне выконваем па тым жа алгарытме, што і для першага аргументу дадзенай групы.
У палі "Условие2", "Условие3", "Условие4" і "Условие5" ўносім адпаведна значэння ">15000", ">24000", ">11000" і ">32000". Як няцяжка здагадацца, гэтыя значэнні адпавядаюць інтэрвалу выручкі, якая перавышае норму для адпаведнага крамы.
Пасля таго, як быў выраблены ўвод ўсіх неабходных дадзеных (усяго 10 палёў), ціснем на кнопку "OK".
- Праграма вырабляе падлік і выводзіць вынік на экран. Як бачым, ён роўны ліку 3. Гэта азначае, што ў трох днях з аналізаванай тыдня выручка ва ўсіх гандлёвых кропках перавышала усталяваную для іх норму.
Зараз некалькі зменім задачу. Нам варта палічыць колькасць дзён, у якіх Крама 1 атрымаў выручку, якая перавышае 14000 рублёў, але меншую, чым 17000 рублёў.
- Ставім курсор у элемент, дзе будзе праведзены вывад на ліст вынікаў падліку. Клацает па піктаграме "Уставіць функцыю" над працоўнай плошчай ліста.
- Бо мы зусім нядаўна ўжывалі формулу СЧЁТЕСЛИМН, То зараз не абавязкова пераходзіць у групу "Статыстычныя" майстры функцый. Найменне дадзенага аператара можна знайсці ў катэгорыі "10 нядаўна якія выкарыстоўваліся". Вылучаем яго і пстрыкаем па кнопцы "OK".
- Адкрываецца ўжо знаёмае нам акенца аргументаў аператара СЧЁТЕСЛИМН. Ставім курсор у полі "Дыяпазон условия1" і, вырабячы заціск левай кнопкі мышы, вылучаем усе вочкі, у якіх змяшчаецца выручка па днях Крамы 1. Яны размешчаны ў радку, якая так і называецца "Крама 1". Пасля гэтага каардынаты названай вобласці будуць адлюстраваны ў акне.
Далей усталёўваем курсор у полі "Условие1". Тут нам трэба пазначыць ніжнюю мяжу значэнняў у вочках, якія будуць прымаць удзел у падліку. паказваем выраз ">14000".
У полі "Дыяпазон условия2" ўводзім той жа адрас тым жа спосабам, які ўводзілі ў поле "Дыяпазон условия1", Гэта значыць, зноў ўносім каардынаты вочак са значэннямі выручкі па першай гандлёвай кропцы.
У полі "Условие2" паказваем верхнюю мяжу адбору: "<17000".
Пасля таго, як усе паказаныя дзеянні зроблены, клацает па кнопцы "OK".
- Праграма выдае вынік разліку. Як бачым, выніковае значэнне роўна 5. Гэта значыць, што ў 5 днях з доследных сямі выручка ў першым краме была ў інтэрвале ад 14000 да 17000 рублёў.
СУММЕСЛИ
Яшчэ адным аператарам, які выкарыстоўвае крытэры, з'яўляецца СУММЕСЛИ. У адрозненне ад папярэдніх функцый, ён ставіцца да матэматычнаму блоку аператараў. Яго задачай з'яўляецца сумаванне дадзеных у вочках, якія адпавядаюць пэўнаму умове. Сінтаксіс такі:
= СУММЕСЛИ (дыяпазон; крытэр; [диапазон_суммирования])
аргумент "Дыяпазон" паказвае на вобласць вочак, якія будуць правярацца на захаванне ўмовы. Па сутнасці, ён задаецца па тым жа прынцыпе, што і аднайменны аргумент функцыі СЧЁТЕСЛИ.
"Крытэрый" - з'яўляецца абавязковым аргументам, задавалым параметр адбору вочак з названай вобласці дадзеных, якія будуць падсумоўвацца. Прынцыпы ўказанні тыя ж, што і ў аналагічных аргументаў папярэдніх аператараў, якія былі разгледжаны намі вышэй.
"Дыяпазон падсумоўвання" - гэта неабавязковы аргумент. Ён паказвае на канкрэтную вобласць масіва, у якой будзе праводзіцца сумаванне. Калі яго апусціць і не паказваць, то па змаўчанні лічыцца, што ён роўны значэнню абавязковага аргументу "Дыяпазон".
Цяпер, як заўсёды, разгледзім прымяненне дадзенага аператара на практыцы. На аснове той жа табліцы перад намі стаіць задача падлічыць суму выручкі ў Краме 1 за перыяд, пачынаючы з 2017/03/11.
- Вылучаем вочка, у якой будзе рабіцца выснова выніку. Пстрыкаем па піктаграме "Уставіць функцыю".
- перайшоўшы ў майстар функцый ў блоку "Матэматычныя" знаходзім і вылучаем найменне "СУММЕСЛИ". Клацает па кнопцы "OK".
- Запускаецца акно аргументаў функцыі СУММЕСЛИ. У ім маецца тры палі, адпаведных аргументаў названага аператара.
У полі "Дыяпазон" ўводзім вобласць табліцы, у якой будуць размяшчацца значэння, правяраныя на выкананне умоў. У нашым выпадку гэта будзе радок дат. Ставім курсор у дадзенае поле і вылучаем усе вочкі, у якіх утрымліваюцца даты.
Бо нам трэба скласці толькі сумы выручкі, пачынаючы з 11 сакавіка, то ў поле "Крытэрый" ўбіваем значэнне ">10.03.2017".
У полі "Дыяпазон падсумоўвання" трэба паказаць вобласць, значэнні якой, якія адказваюць названым крытэрам, будуць падсумоўвацца. У нашым выпадку гэта значэнні выручкі радкі "Магазин1". Вылучаем адпаведны масіў элементаў ліста.
Пасля таго, як выраблена ўвядзенне ўсіх паказаных дадзеных, ціснем на кнопку "OK".
- Пасля гэтага ў папярэдне ўказаны элемент працоўнага ліста будзе выведзены вынік апрацоўкі дадзеных функцыяй СУММЕСЛИ. У нашым выпадку ён роўны 47921,53. Гэта азначае, што пачынаючы з 2017/03/11, і да канца аналізаванай перыяду, агульная выручка па Краме 1 склала 47921,53 рубля.
СУММЕСЛИМН
Завершым вывучэнне аператараў, якія выкарыстоўваюць крытэрыі, спыніўшыся на функцыі СУММЕСЛИМН. Задачай дадзенай матэматычнай функцыі з'яўляецца сумаванне значэнняў названых абласцей табліцы, адабраных па некалькіх параметрах. Сінтаксіс названага аператара такі:
= СУММЕСЛИМН (диапазон_суммирования; диапазон_условия1; условие1; диапазон_условия2; условие2; ...)
"Дыяпазон падсумоўвання" - гэта аргумент, які з'яўляецца адрасам таго масіву, вочкі ў якім, якія адказваюць вызначаным крытэру, будуць складвацца.
"Дыяпазон ўмовы" - аргумент, які ўяўляе сабой масіў дадзеных, правяраемы на адпаведнасць умове;
"Умова" - аргумент, які ўяўляе сабой крытэр адбору для складання.
Дадзеная функцыя мае на ўвазе аперацыі адразу з некалькімі наборамі падобных аператараў.
Паглядзім, як дадзены аператар выкарыстоўваецца і ў дачыненні для вырашэння задач у кантэксце нашай табліцы выручкі ад рэалізацыі ў гандлёвых кропках. Нам трэба будзе падлічыць прыбытак, які прынёс Крама 1 за перыяд з 09 па 13 сакавіка 2017 года. Пры гэтым пры сумаванні даходу павінны ўлічвацца толькі тыя дні, выручка ў якіх перавысіла 14000 рублёў.
- Зноў вылучаем вочка для вываду выніку і клацает па піктаграме "Уставіць функцыю".
- У майстру функцый, Перш за ўсё, выконваем перасоўванне ў блок "Матэматычныя", А там вылучаем пункт пад назвай "СУММЕСЛИМН". Вырабляем клік па кнопцы "OK".
- Вырабляецца запуск акенца аргументаў аператара, назва якога было паказана вышэй.
Усталёўваем курсор у полі "Дыяпазон падсумоўвання". У адрозненне ад наступных аргументаў, гэты адзіны ў сваім родзе і паказвае на той масіў значэнняў, дзе будзе вырабляцца сумаванне надышлі пад названыя крытэрыі дадзеных. Затым вылучаем вобласць радкі "Магазин1", У якой размешчаны значэнні выручкі па адпаведнай гандлёвым пункце.
Пасля таго, як адрас адлюстраваўся ў акне, пераходзім да поля "Дыяпазон условия1". Тут нам трэба будзе адлюстраваць каардынаты радкі з датамі. Вырабляем заціск левай кнопкі мышы і вылучаем ўсе даты ў табліцы.
Ставім курсор у полі "Условие1". Першай умовай з'яўляецца тое, што намі будуць падсумоўвацца дадзеныя не раней 09 сакавіка. Таму ўводзім значэнне ">08.03.2017".
Перамяшчаемся да аргументу "Дыяпазон условия2". Тут трэба ўнесці тыя ж каардынаты, якія былі запісаныя ў поле "Дыяпазон условия1". Робім гэта тым жа спосабам, гэта значыць, шляхам выдзялення радкі з датамі.
Усталёўваем курсор у полі "Условие2". Другім умовай з'яўляецца тое, што дні, за якія будзе падсумоўвацца выручка, павінны быць не пазней 13 сакавіка. Таму запісваем наступнае выраз: "<14.03.2017".
Пераходзім у поле "Дыяпазон условия2". У дадзеным выпадку нам трэба вылучыць той самы масіў, адрас якога быў унесены, як масіў сумавання.
Пасля таго, як адрас названага масіву адлюстраваўся ў акне, пераходзім да поля "Условие3". Улічваючы, што ў сумаванні будуць прымаць удзел толькі значэння, велічыня якіх перавышае 14000 рублёў, ўносім запіс наступнага характару: ">14000".
Пасля выканання апошняга дзеянні клацает па кнопцы "OK".
- Праграма выводзіць вынік на ліст. Ён роўны 62491,38. Гэта азначае, што за перыяд з 09 па 13 сакавіка 2017 года сума выручкі пры складанні яе за дні, у якіх яна перавышае 14000 рублёў, склала 62491,38 рубля.
ўмоўнае фарматаванне
Апошнім, апісаным намі, інструментам, пры працы з якім выкарыстоўваюцца крытэрыі, з'яўляецца ўмоўнае фарматаванне. Ён выконвае названы вiд фарматавання вочак, якія адказваюць зададзеным умовам. Зірнем на прыклад працы з умоўным фарматаваннем.
Вылучым тыя вочка табліцы сінім колерам, дзе значэння за дзень перавышаюць 14000 рублёў.
- Вылучаем ўвесь масіў элементаў у табліцы, у якім указана выручка гандлёвых кропак па днях.
- Перасоўваемся ва ўкладку "Галоўная". Клацает па піктаграме "Умоўнае фарматаванне", Размешчанай у блоку "Стылі" на стужцы. Адкрываецца спіс дзеянняў. Клацает ў ім па пазіцыі "Стварыць правіла ...".
- Актывуецца акенца генерацыі правілы фарматавання. У вобласці выбару тыпу правілы вылучаем найменне "Фарматаваць толькі вочкі, якія ўтрымліваюць". У першым полі блока умоў з спісу магчымых варыянтаў выбіраем "Значэнне ячэйкі". У наступным поле выбіраем пазіцыю "Больш". У апошнім - паказваем само значэнне, больш якога патрабуецца адфарматаваць элементы табліцы. У нас гэта 14000. Каб выбраць тып фарматавання, клацает па кнопцы "Фармат ...".
- Актывуецца акно фарматавання. Перасоўваемся ва ўкладку "Заліванне". З прапанаваных варыянтаў кветак залівання выбіраем сіні, пстрыкаючы па ім левай кнопкай мышы. Пасля таго, як абраны колер адлюстраваўся ў галіне "Узор", Клацает па кнопцы "OK".
- Аўтаматычна адбываецца зварот да акна генерацыі правілы фарматавання. У ім таксама ў галіне "Узор" адлюстроўваецца сіні колер. Тут нам трэба вырабіць адно адзінае дзеянне: клацнуть па кнопцы "OK".
- Пасля выканання апошняй дзеі, усе вочкі вылучанага масіва, дзе ўтрымліваецца лік большае, чым 14000, будуць заліты сінім колерам.
Больш падрабязна аб магчымасцях ўмоўнага фарматавання распавядаецца ў асобным артыкуле.
Урок: Умоўнае фарматаванне ў праграме Эксэля
Як бачым, з дапамогай інструментаў, якія выкарыстоўваюць пры сваёй працы крытэрыі, у Эксэля можна вырашаць даволі разнапланавыя задачы. Гэта можа быць, як падлік сум і значэнняў, так і фарматаванне, а таксама выкананне многіх іншых задач. Асноўнымі інструментамі, якія працуюць у дадзенай праграме з крытэрамі, гэта значыць, з пэўнымі ўмовамі, пры выкананні якіх актывуецца азначанае дзеянне, з'яўляецца набор ўбудаваных функцый, а таксама ўмоўнае фарматаванне.
