Приклади функції «СУММЕСЛИ» в Excel

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

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

Розберемо і два що залишилися аргументу - «Діапазон» і «Критерій». У перший вписується перелік осередків (A1: A100), які будуть потрапляти під перевірку і порівняння з умовою. У «Критерій» користувач заносить умова, при виконанні якого осередок стає одним з доданків. Це може бути умова нерівності чисел (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