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

Зміст

• Застосування критеріїв
  • COUNTIF
  • СЧЕТЕСЛИМН
  • SUMIF
  • СУМЕСЛИМН
  • Умовне форматування
• Питання та відповіді

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

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

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

COUNTIF

Головним завданням оператора COUNTIF , Що відноситься до статистичної групи, є підрахунок зайнятих різними значеннями осередків, які задовольняють певному заданому умові. Його синтаксис наступний:

=COUNTIF (діапазон; критерій)

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

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

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

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

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

2. Проводиться запуск майстри функцій . Здійснюємо переміщення в блок »статистичні" . Там знаходимо і виділяємо найменування " СЧЕТЕСЛІ» . Потім слід клацнути по кнопці «OK» .

3. Відбувається активація вікна аргументів вищевказаного оператора. У полі " діапазон» слід вказати область осередків, серед яких буде проводитися підрахунок. У нашому випадку слід виділити вміст рядка "Магазин 2" , в якій розташовані значення виручки по днях. Ставимо курсор в зазначене поле і, затиснувши ліву кнопку миші, виділяємо відповідний масив в таблиці. Адреса виділеного масиву відобразиться у вікні.

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

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


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

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

СЧЕТЕСЛИМН

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

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

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

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

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

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

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 рублів.

SUMIF

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

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

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

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

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

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

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

2. Перехід до Майстер функцій в блоці »Математичні" знаходимо і виділяємо найменування »SUMIF" . Клацаємо по кнопці «OK» .

3. Запускається вікно аргументів функції SUMIF . У ньому є три поля, що відповідають аргументам зазначеного оператора.

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

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

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

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


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

СУМЕСЛИМН

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

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

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

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

" умова» &8212; аргумент, що представляє собою критерій відбору для додавання.

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

Подивимося, як даний оператор застосуємо для вирішення завдань в контексті нашої таблиці виручки від реалізації в торгових точках. Нам потрібно буде підрахувати дохід, який приніс Магазин 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. Активується віконце генерації правила форматування. В області вибору типу правила виділяємо найменування " форматувати лише клітинки, які містять» . У першому полі блоку умов зі списку можливих варіантів вибираємо " значення комірки» . У наступному полі вибираємо позицію «більше» . В останньому & 8212; вказуємо саме значення, більше якого потрібно відформатувати елементи таблиці. У нас це 14000. Щоб вибрати тип форматування, клацаємо по кнопці " Формат...» .

4. Активується вікно форматування. Пересуваємося у вкладку»Заливка" . Із запропонованих варіантів кольорів заливки вибираємо синій, клацаючи по ньому лівою кнопкою миші. Після того, як вибраний колір відобразився в області " Зразок» , клацаємо по кнопці «OK» .

5. Автоматично відбувається повернення до вікна генерації правила форматування. У ньому також в області " Зразок» відображається синій колір. Тут нам потрібно зробити одне єдине дію: клацнути по кнопці «OK» .

6. Після виконання останньої дії, всі осередки виділеного масиву, де міститься число більше, ніж 14000, будуть залиті синім кольором.

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

Урок: Умовне форматування в програмі Ексель

Як бачимо, за допомогою інструментів, що використовують при своїй роботі критерії, в Ексель можна вирішувати досить різнопланові завдання. Це може бути, як підрахунок Сум і значень, так і форматування, а також виконання багатьох інших завдань. Основними інструментами, що працюють в даній програмі з критеріями, тобто, з певними умовами, при виконанні яких активується зазначена дія, є набір вбудованих функцій, а також умовне форматування.