Приклади використання функції СУММЕСЛИ в Microsoft Excel

Синтаксис і створення функції

Функція СУММЕСЛИ популярна, оскільки майже в кожній таблиці потрібно підрахувати суму чисел у клітинках, ігноруючи значення, що не підпадають під основну умову. Завдяки цій формулі підрахунок не стає чимось складним і тривалим. Стандартне оформлення функції виглядає як =СУММЕСЛИ(Діапазон;Критерій;Діапазон_сумування), а "Діапазон сумування" вказується лише за умови, коли є фактичні клітинки, складання яких виконується за будь-яких обставин. Якщо дані в "Діапазоні сумування" відсутні, перевірятимуться всі клітинки, що входять у "Діапазон".

Розглянемо й два залишкові аргументи — "Діапазон" і "Критерій". У перший вписується перелік клітинок (A1:A100), які підлягають перевірці та порівнянню з умовою. У "Критерій" користувач заносить умову, за виконання якої клітинка стає одним із складових. Це може бути умова нерівності чисел (<30,>50) або відповідності заданому тексту ("Text"). Для спрощення розуміння установки аргументів відкрийте графічне вікно "Аргумент функції" і задайте всі умови по черзі в окремих полях.

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

Функція СУММЕСЛИ за умовою нерівності

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

1. Визначтеся з діапазоном клітинок, що підпадають під розгляд формулою, у нас це буде прибуток за місяць.
2. Почніть запис з її вказівки в полі вводу, написавши СУММЕСЛИ.
3. Створіть відкриваючу та закриваючу дужку, де введіть діапазон вибраних клітинок, наприклад C2:C25. Після цього обов'язково поставте знак ;, який означає кінець аргументу.
4. Відкрийте лапки і в них вкажіть умову, що в нашому випадку буде >300000.
5. Як тільки відбудеться натискання клавіші Enter, функція активується. На скріншоті нижче видно, що умові >300000 відповідають лише дві клітинки, отже, формула сумує їх числа і відображає в окремому блоці.

Вище був розглянутий лише один з прикладів зі випадково взятими умовами. Нічого не заважає підставити інші значення, розширити або звузити діапазон — формула все одно нормально обчислить значення, якщо правила синтаксису були дотримані.

Функція СУММЕСЛИ за умови відповідності тексту

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

1. Цього разу, окрім діапазону сумованих клітинок, визначте і ті, де присутні написи, що підпадають під умову.
2. Почніть запис функції з її позначення точно так само, як це вже було показано вище.
3. В першу чергу введіть діапазон написів, поставте ; і задайте умову. Тоді це вираження в синтаксичному форматі набуде приблизно такого вигляду: A2:A25;"Вересень";.
4. В якості останнього аргументу залишається вказати діапазон клітинок, числа яких будуть сумуватися при правильному умові. З правилами запису такого аргументу ви вже знайомі.
5. Перевіримо дію функції.Встановлюємо умовою місяць вересень і дивимося за таблицею, що сумувалися два значення клітинок таблиці, які йому відповідають. Інші дані при перевірці просто ігноруються.

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

Функція СУММЕСЛИМН з кількома умовами

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

1. Візьмемо таблицю, в якій є три різних значення як за знаходженням там даними, так і за їх типами. Це місяць, загальний прибуток і кількість проданих одиниць товару. У формулі з кількома умовами потрібно зробити так, щоб в підсумкову суму потрапили тільки ті результати прибутку, які були отримані у вересні при продажах вище 29 одиниць.
2. Почніть запис зі стандартного її оголошення =СУММЕСЛИМН, відкрийте дужки і напишіть, який діапазон клітинок буде сумуватися. Не забудьте закрити оголошення аргументу знаком ;.
3. Наступний аргумент — збіг за назвою в клітинці. Запис цього параметра вже розглядалася вище і ще раз її можна побачити на скріншоті.
4. Третє умова — відповідність заданому нерівності, з чим ми ознайомилися ще на початку статті. Знову в такому ж форматі вкажіть діапазон значень і саме нерівність в лапках. Не забудьте поставити закриваючу дужку, завершивши тим самим написання функції.
5. Повністю рядок цієї функції має вигляд =СУММЕСЛИМН(C2:C25;A2:A25;"Вересень";B2:B25;">29"), а вам залишається лише підстроїти її під себе і отримати правильний результат. Може здатися, що оголошувати кожен аргумент і не заплутатися в символах складно, але при роздільному підході ніяких проблем виникнути не повинно.

СУММЕСЛИ і СУММЕСЛИМН відносяться до математичних функцій, які мають схоже синтаксичне представлення, але використовуються в різних ситуаціях.Для читання інформації по всім популярним формулам в Excel зверніться до матеріалу за посиланням нижче.

Докладніше: 10 популярних математичних функцій Microsoft Excel