Використання критеріїв в Microsoft Excel

Програма Microsoft Excel є не просто табличним редактором, а ще й потужним додатком для різних обчислень. Не в останню чергу така можливість з'явилася завдяки вбудованим функціям. За допомогою деяких функцій (операторів) можна задавати навіть умови обчислення, які прийнято називати критеріями. Давайте докладніше дізнаємося, яким чином можна їх використовувати при роботі в Ексель.

застосування критеріїв

Критерії являють собою умови, при яких програма виконує певні дії. Вони застосовуються в цілому ряді вбудованих функцій. У їх назві найчастіше присутня вираз «ЯКЩО». До даної групи операторів, перш за все, потрібно віднести СЧЁТЕСЛІ, СЧЁТЕСЛІМН, СУММЕСЛИ, СУММЕСЛІМН. Крім вбудованих операторів, критерії в Excel використовуються також при умовному форматуванні. Розглянемо їх застосування при роботі з різними інструментами даного табличного процесора більш докладно.

СЧЁТЕСЛІ

Головним завданням оператора СЧЁТЕСЛІ, що відноситься до статистичної групі, є підрахунок зайнятих різними значеннями осередків, які задовольняють певним заданій умові. Його синтаксис наступний:

=СЧЁТЕСЛИ(диапазон;критерий)

Як бачимо, у даного оператора два аргументи. «Діапазон» є адреса масиву елементів на аркуші, в яких слід провести підрахунок.

«Критерій» - це аргумент, який задає умову, що саме повинні містити осередки зазначеної області, щоб бути включеними в підрахунок. Як параметр може бути використано числовий вираз, текст або посилання на осередок, в якій критерій міститься. При цьому, для вказівки критерію можна використовувати такі знаки: «<» ( «менше»), «>» ( «більше»), «=» ( «дорівнює»), «<>» ( «не дорівнює»). Наприклад, якщо задати вираз «<50», то при підрахунку будуть враховуватися тільки елементи, задані аргументом «Діапазон», в яких знаходяться числові значення менше 50. Використання даних знаків для вказівки параметрів будуть актуальними і для всіх інших варіантів, про які піде мова в даному уроці нижче.

А тепер давайте на конкретному прикладі подивимося, як працює даний оператор на практиці.

Отже, є таблиця, де представлена ​​виручка по п'яти магазинах за тиждень. Нам потрібно дізнатися кількість днів за цей період, в яких в Магазині 2 дохід від реалізації перевищив 15000 рублів.

1. Виділяємо елемент листа, в який оператор буде виводити результат обчислення. Після цього клацаємо по піктограмі «Вставити функцію».
2. Виробляється запуск Майстра функцій. Здійснюємо переміщення в блок «Статистичні». Там знаходимо і виділяємо найменування «СЧЁТЕСЛІ». Потім слід клацнути по кнопці «OK».
3. Відбувається активація вікна аргументів вищевказаного оператора. В поле «Діапазон» слід вказати область осередків, серед яких буде проводитися підрахунок. У нашому випадку слід виділити вміст рядка «Магазин 2», в якій розташовані значення виручки по днях. Ставимо курсор в вказане поле і, затиснувши ліву кнопку миші, виділяємо відповідний масив в таблиці. Адреса виділеного масиву відобразиться у вікні.

   В наступному полі «Критерій» якраз потрібно задати безпосередній параметр відбору. У нашому випадку потрібно підрахувати тільки ті елементи таблиці, в яких значення перевищує 15000. Тому за допомогою клавіатури вбиваємо в вказане поле вираз «> 15000».

   Після того, як всі вищевказані маніпуляції проведені, клацають по кнопці «OK».

4. Програма проводить підрахунок і виводить результат в елемент листа, який був виділений перед активацією Майстра функцій. Як бачимо, в даному випадку результат дорівнює числу 5. Це означає, що в виділеному масиві в п'яти осередках знаходяться значення перевищують 15000. Тобто, можна зробити висновок, що в Магазині 2 в п'ять днів з аналізованих семи виручка перевищила 15000 рублів.

урок: Майстер функцій в програмі Ексель

СЧЁТЕСЛІМН

Наступною функцією, яка оперує критеріями, є СЧЁТЕСЛІМН. Вона також відноситься до статистичної групі операторів. Завданням СЧЁТЕСЛІМН є підрахунок осередків в зазначеному масиві, які задовольняють певним набором умов. Саме той факт, що можна задати не один, а кілька параметрів, і відрізняє цього оператора від попереднього. Синтаксис наступний:

=СЧЁТЕСЛИМН(диапазон_условия1;условие1;диапазон_условия2;условие2;…)

«Діапазон умови» є ідентичним першому аргументу попереднього оператора. Тобто, він є посилання на область, в якій буде проводитися підрахунок осередків, які відповідають зазначеним умовам. Даний оператор дозволяє задати відразу кілька таких областей.

«Умова» являє собою критерій, який визначає, які елементи з відповідного масиву даних увійдуть в підрахунок, а які не увійдуть. Кожній заданої області даних потрібно вказувати умова окремо, навіть в тому випадку, якщо воно збігається. Обов'язково потрібно, щоб все масиви, використовувані в якості областей умови, мали однакову кількість рядків і стовпців.

Для того, щоб задати кілька параметрів однієї і тієї ж області даних, наприклад, щоб підрахувати кількість осередків, в яких розташовані величини більше за певну кількість, але менше іншого числа, слід як аргумент «Діапазон умови» кілька разів вказати один і той же масив . Але при цьому в якості відповідних аргументів «Умова» слід вказувати різні критерії.

На прикладі все тієї ж таблиці з тижневої виручкою магазинів подивимося, як це працює. Нам потрібно дізнатися кількість днів тижня, коли дохід у всіх зазначених торгових точках досягав встановленої для них норми. Норми виручки наступні:

• Магазин 1 - 14000 рублів;
• Магазин 2 - 15000 рублів;
• Магазин 3 - 24000 рублів;
• Магазин 4 - 11000 рублів;
• Магазин 5 - 32000 рублів.

1. Для виконання вищевказаної завдання, виділяємо курсором елемент робочого листа, куди буде виводитися підсумок обробки даних СЧЁТЕСЛІМН. Клацають по іконці «Вставити функцію».
2. Перейшовши в Майстер функцій, знову подорожуємо у блок «Статистичні». У переліку слід відшукати найменування СЧЁТЕСЛІМН і зробити його виділення. Після виконання зазначеної дії потрібно зробити натискання на кнопку «OK».
3. Слідом за виконанням вищевказаного алгоритму дій відкривається вікно аргументів СЧЁТЕСЛІМН.

   В поле «Діапазон условія1» слід ввести адресу рядка, в якій розташовані дані по виручці Магазину 1 за тиждень. Для цього ставимо курсор в поле і виділяємо відповідний рядок в таблиці. Координати відображаються у вікні.

   З огляду на, що для Магазину 1 денна норма виручки становить 14000 рублів, то в поле «Умова 1» вписуємо вираз «> 14000».

   У поля «Діапазон условія2 (3,4,5)» слід внести координати рядків з тижневої виручкою відповідно Магазину 2, Магазину 3, Магазину 4 і Магазину 5. Дія виконуємо за тим же алгоритмом, що і для першого аргументу даної групи.

   У поля «Условіе2», «Условіе3», «Условіе4» і «Условіе5» вносимо відповідно значення «> 15000», «> 24000», «> 11000» і «> 32000». Як неважко здогадатися, ці значення відповідають інтервалу виручки, що перевищує норму для відповідного магазину.

   Після того, як був зроблений уведення всіх необхідних даних (всього 10 полів), тиснемо на кнопку «OK».

4. Програма проводить підрахунок і виводить результат на екран. Як бачимо, він дорівнює числу 3. Це означає, що в трьох днях з аналізованої тижні виручка у всіх торгових точках перевищувала встановлену для них норму.

Тепер трохи змінимо завдання. Нам слід порахувати кількість днів, в яких Магазин 1 отримав виручку, що перевищує 14000 рублів, але меншу, ніж 17000 рублів.

1. Ставимо курсор в елемент, де буде проведений висновок на лист результатів підрахунку. Клацають по піктограмі «Вставити функцію» над робочою площею листа.
2. Так як ми зовсім недавно застосовували формулу СЧЁТЕСЛІМН, то тепер не обов'язково переходити в групу «Статистичні» Майстра функцій. Найменування даного оператора можна знайти в категорії «10 недавно використовувалися». Виділяємо його і клацаємо по кнопці «OK».
3. Відкривається вже знайоме нам віконце аргументів оператора СЧЁТЕСЛІМН. Ставимо курсор в поле «Діапазон условія1» і, зробивши затиск лівої кнопки миші, виділяємо все осередки, в яких міститься виручка по днях Магазину 1. Вони розташовані в рядку, яка так і називається «Магазин 1». Після цього координати зазначеної області будуть відображені у вікні.

   Далі встановлюємо курсор в поле «Условіе1». Тут нам потрібно вказати нижню межу значень в осередках, які будуть брати участь у підрахунку. Вказуємо вираз «> 14000».

   В поле «Діапазон условія2» вводимо ту ж адресу тим же способом, який вводили в поле «Діапазон условія1», тобто, знову вносимо координати осередків зі значеннями виручки по першій торговій точці.

   В поле «Условіе2» вказуємо верхню межу відбору: «<17000».

   Після того, як всі зазначені дії зроблені, клацають по кнопці «OK».

4. Програма видає результат розрахунку. Як бачимо, підсумкове значення дорівнює 5. Це означає, що в 5 днях з досліджуваних семи виручка в першому магазині була в інтервалі від 14000 до 17000 рублів.

СУММЕСЛИ

Ще одним оператором, який використовує критерії, є СУММЕСЛИ. На відміну від попередніх функцій, він відноситься до математичного блоку операторів. Його завданням є підсумовування даних в осередках, які відповідають певній умові. Синтаксис такий:

=СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования])

Аргумент «Діапазон» вказує на область осередків, які будуть перевірятися на дотримання умови. По суті, він задається за тим же принципом, що і однойменний аргумент функції СЧЁТЕСЛІ.

«Критерій» - є обов'язковим аргументом, що задає параметр відбору осередків з зазначеній галузі даних, які будуть підсумовуватися. Принципи вказівки ті ж, що і у аналогічних аргументів попередніх операторів, які були розглянуті нами вище.

«Діапазон підсумовування» - це необов'язковий аргумент. Він вказує на конкретну область масиву, в якій буде проводитися підсумовування. Якщо його опустити і не вказувати, то за замовчуванням вважається, що він дорівнює значенню обов'язкового аргументу «Діапазон».

Тепер, як завжди, розглянемо застосування даного оператора на практиці. На основі тієї ж таблиці перед нами стоїть завдання підрахувати суму виручки в Магазині 1 за період, починаючи з 11.03.2017.

1. Виділяємо осередок, в якій буде проводитися висновок результату. Клацаємо по піктограмі «Вставити функцію».
2. Перейшовши в Майстер функцій в блоці «Математичні» знаходимо і виділяємо найменування «СУММЕСЛИ». Клацають по кнопці «OK».
3. Запускається вікно аргументів функції СУММЕСЛИ. У ньому є три поля, відповідних аргументів зазначеного оператора.

   В поле «Діапазон» вводимо область таблиці, в якій будуть розташовуватися значення, перевіряються на дотримання умов. У нашому випадку це буде рядок дат. Ставимо курсор в цьому полі і виділяємо все осередки, в яких містяться дати.

   Так як нам потрібно скласти тільки суми виручки, починаючи з 11 березня, то в поле «Критерій» вбиваємо значення «> 10.03.2017».

   В поле «Діапазон підсумовування» потрібно вказати область, значення якої, відповідають зазначеним критеріям, будуть підсумовуватися. У нашому випадку це значення виручки рядки «Магазін1». Виділяємо відповідний масив елементів листа.

   Після того, як вироблено введення всіх зазначених даних, тиснемо на кнопку «OK».

4. Після цього в попередньо вказаний елемент робочого листа буде виведений результат обробки даних функцією СУММЕСЛИ. У нашому випадку він дорівнює 47921,53. Це означає, що починаючи з 11.03.2017, і до кінця аналізованого періоду, загальна виручка за Магазину 1 склала 47921,53 рубля.

СУММЕСЛІМН

Завершимо вивчення операторів, які використовують критерії, зупинившись на функції СУММЕСЛІМН. Завданням даної математичної функції є підсумовування значень зазначених областей таблиці, відібраних за кількома параметрами. Синтаксис зазначеного оператора такий:

=СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1;диапазон_условия2;условие2;…)

«Діапазон підсумовування» - це аргумент, який є адресою того масиву, осередки в якому, що відповідають певним критерієм, будуть складатися.

«Діапазон умови» - аргумент, який представляє собою масив даних, що перевіряється на відповідність умові;

«Умова» - аргумент, який представляє собою критерій відбору для складання.

Ця функція має на увазі операції відразу з декількома наборами подібних операторів.

Подивимося, як цей оператор можна застосувати для вирішення завдань в контексті нашої таблиці виручки від реалізації в торгових точках. Нам потрібно буде підрахувати дохід, який приніс Магазин 1 за період з 09 по 13 березня 2017 року. При цьому при підсумовуванні доходу повинні враховуватися тільки ті дні, виручка в яких перевищила 14000 рублів.

1. Знову виділяємо клітинку для виведення результату і клацають по піктограмі «Вставити функцію».
2. У Майстрі функцій, перш за все, виконуємо переміщення в блок «Математичні», а там виділяємо пункт під назвою «СУММЕСЛІМН». Виробляємо клік по кнопці «OK».
3. Виробляється запуск віконця аргументів оператора, найменування якого було зазначено вище.

   Встановлюємо курсор в поле «Діапазон підсумовування». На відміну від наступних аргументів, цей єдиний у своєму роді і вказує на той масив значень, де буде проводитися підсумовування підійшли під зазначені критерії даних. Потім виділяємо область рядки «Магазін1», в якій розміщені значення виручки за відповідною торговій точці.

   Після того, як адресу відобразився у вікні, переходимо до поля «Діапазон условія1». Тут нам потрібно буде відобразити координати рядка з датами. Виробляємо затиск лівої кнопки миші і виділяємо все дати в таблиці.

   Ставимо курсор в поле «Условіе1». Першою умовою є те, що нами будуть підсумовуватися дані не раніше 09 березня. Тому вводимо значення «> 08.03.2017».

   Переміщаємося до аргументу «Діапазон условія2». Тут потрібно внести ті ж координати, які були записані в полі «Діапазон условія1». Робимо це тим же способом, тобто, шляхом виділення рядки з датами.

   Встановлюємо курсор в поле «Условіе2». Другою умовою є те, що дні, за які буде підсумовуватися виручка, повинні бути не пізніше 13 березня. Тому записуємо такий вираз: «<14.03.2017».

   Переходимо в поле «Діапазон условія2». В даному випадку нам потрібно виділити той самий масив, адреса якого був внесений, як масив підсумовування.

   Після того, як адресу зазначеного масиву відобразився у вікні, переходимо до поля «Условіе3». З огляду на, що в підсумовуванні братимуть участь тільки значення, величина яких перевищує 14000 рублів, вносимо запис наступного характеру: «> 14000».

   Після виконання останньої дії клацають по кнопці «OK».

4. Програма виводить результат на лист. Він дорівнює 62491,38. Це означає, що за період з 09 по 13 березня 2017 року сума виручки при додаванні її за дні, в яких вона перевищує 14000 рублів, склала 62491,38 рубля.

Умовне форматування

Останнім, описаним нами, інструментом, при роботі з яким використовуються критерії, є умовне форматування. Він виконує зазначений вид форматування осередків, які відповідають заданим умовам. Погляньмо на приклад роботи з умовним форматуванням.

Виділимо ті елементи таблиці синім кольором, де значення за день перевищують 14000 рублів.

1. Виділяємо весь масив елементів в таблиці, в якому вказана виручка торговельних точок по днях.
2. Пересуваємося у вкладку «Головна». Клацають по піктограмі «Умовне форматування», розміщеної в блоці «Стилі» на стрічці. Відкривається список дій. Клацають в ньому по позиції «Створити правило ...».
3. Активується віконце генерації правила форматування. В області вибору типу правила виділяємо найменування «Форматувати тільки осередки, які містять». У першому полі блоку умов зі списку можливих варіантів вибираємо «Значення комірки». В наступному полі вибираємо позицію «Більше». В останньому - вказуємо саме значення, більше якого потрібно відформатувати елементи таблиці. У нас це 14000. Щоб вибрати тип форматування, клацають по кнопці «Формат ...».
4. Активується вікно форматування. Пересуваємося у вкладку «Заливка». Из предложенных вариантов цветов заливки выбираем синий, щелкая по нему левой кнопкой мыши. После того, как выбранный цвет отобразился в области «Образец» , клацаем по кнопке «OK» .
5. Автоматически происходит возврат к окну генерации правила форматирования. В нём также в области «Образец» отображается синий цвет. Тут нам нужно произвести одно единственное действие: клацнуть по кнопке «OK» .
6. После выполнения последнего действия, все ячейки выделенного массива, где содержится число большее, чем 14000, будут залиты синим цветом.

Более подробно о возможностях условного форматирования рассказывается в отдельной статье.

урок: Условное форматирование в программе Эксель

Как видим, с помощью инструментов, использующих при своей работе критерии, в Экселе можно решать довольно разноплановые задачи. Это может быть, как подсчет сумм и значений, так и форматирование, а также выполнение многих других задач. Основными инструментами, работающими в данной программе с критериями, то есть, с определенными условиями, при выполнении которых активируется указанное действие, является набор встроенных функций, а также условное форматирование.

• ← Побудова графіка залежності в Microsoft Excel
• Створення мережевого графіка в Microsoft Excel →