Функція SUMIF в Excel і сума за кількома умовами

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

Сьогодні ми поговоримо про одну з таких функцій − SUMMESLIMN.

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

Детальний опис функції SUMIFS

Перед розглядом функції SUMMESLIMN, ви повинні спочатку зрозуміти, що таке простіша версія – СУММЕСЛІ, оскільки саме на ньому заснована функція, яку ми розглядаємо. Кожен з нас, напевно, вже знайомий з двома часто використовуваними функціями – SUM (здійснює підсумовування значень) і ЯКЩО (перевіряє значення на певну умову).

Якщо їх об’єднати, ви отримаєте іншу функцію – СУММЕСЛІ, який перевіряє дані на відповідність визначеним користувачем критеріям і підсумовує лише ті числа, які відповідають цим критеріям. Якщо говорити про англійську версію Excel, то ця функція називається SUMIF. Простими словами, -мовна назва є прямим перекладом англомовної. Цю функцію можна використовувати для різних цілей. Зокрема, його можна використовувати як альтернативу VPR, тобто запишіть

Основна відмінність функції SUMMESLIMN  від звичайної функції СУММЕСЛІ полягає в тому, що використовується кілька критеріїв. Її синтаксис на перший погляд досить складний, але при найближчому розгляді виявляється, що логіка цієї функції дуже проста. Спочатку потрібно вибрати діапазон, в якому будуть перевірятися дані, а потім задати умови, на відповідність яким буде проводитися аналіз. І така операція може проводитися при досить великій кількості умов.

Сам синтаксис такий:

SUMIFS(діапазон_суми, діапазон_умови1, умова1, [діапазон_умови2, умова2], …)

У відповідних місцях необхідно розмістити масиви комірок, придатних у конкретному випадку. 

Розглянемо аргументи докладніше:

1. Діапазон_суми. Цей аргумент, а також діапазон умови 1 і умови 1 є обов’язковими. Це набір клітинок, які потрібно підсумувати.
2. Діапазон_умов1. Це діапазон, де буде перевірятися умова. Він поєднується з наступним аргументом – Condition1. Підсумовування значень, що відповідають критерію, здійснюється в межах комірок, зазначених у попередньому аргументі.
3. Умова1. Цей аргумент визначає критерії для перевірки. Його можна встановити, наприклад, таким чином: «> 32».
4. Діапазон умов 2, Умова 2… Тут такі ж умови встановлюються. Якщо потрібно вказати більше кількох умов, додаються аргументи «Діапазон умов 3» і «Умова 3». Синтаксис такий самий для наступних аргументів.

Функція дозволяє максимально обробляти до 127 пар умов і діапазонів. 

Використовувати його можна відразу в кількох сферах (ми наведемо лише деякі, насправді список ще довший):

1. Бухгалтерський облік. Наприклад, добре використовувати функцію SUMMESLIMN створювати зведені звіти, наприклад, по кварталах для витрат понад певну суму. Або створити звіт по одному товару з певної цінової категорії.
2. Управління продажами. Тут також функція може бути дуже корисною. Наприклад, перед нами стоїть завдання підсумувати тільки вартість товарів, які були продані певному покупцеві в певний час. І в такій ситуації функція SUMMESLIMN може бути дуже корисним.
3. Освіта. Сьогодні ми наведемо більше практичних прикладів з цієї області. Зокрема, ви можете використовувати його для отримання зведених оцінок студентів. Ви можете вибрати для окремого предмета або для окремих оцінок. Людина може відразу задати кілька критеріїв, за якими буде вибиратися оцінка, що дуже зручно і може значно заощадити час.

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

1. Можливість встановлення кількох критеріїв. Чому це перевага? Ви можете використовувати звичайну функцію СУММЕСЛІ! А все тому, що це зручно. Немає необхідності проводити окремі розрахунки для кожного з критеріїв. Всі дії можна запрограмувати заздалегідь, навіть раніше. як буде сформована таблиця даних. Це чудова економія часу.
2. автоматизація. Сучасна епоха – це епоха автоматизації. Тільки та людина, яка вміє правильно автоматизувати свою роботу, може багато заробити. Саме тому вміння володіти Excel і функцією SUMMESLIMN зокрема, так важливо для будь-якої людини, яка хоче побудувати кар'єру. Знання однієї функції дозволяє виконувати кілька дій одночасно, як одну. І тут ми переходимо до наступної переваги цієї функції.
3. Економія часу. Просто за рахунок того, що одна функція виконує відразу кілька завдань.
4. Простота. Незважаючи на те, що синтаксис досить важкий на перший погляд через свою громіздкість, насправді логіка цієї функції дуже проста. Спочатку вибирається діапазон даних, потім діапазон значень, які будуть перевірятися на відповідність певній умові. І, звичайно, сама умова також повинна бути вказана. І так кілька разів. Насправді ця функція базується лише на одній логічній конструкції, що робить її простішою за загальновідому VPR незважаючи на те, що його можна використовувати для тих же цілей, також з урахуванням більшої кількості критеріїв. 

Особливості використання функції СУМІФС

Є кілька особливостей використання цієї функції, на які необхідно звернути увагу. По-перше, ця функція ігнорує діапазони з текстовими рядками або нульовими значеннями, оскільки ці типи даних не можна додавати разом у арифметичний шаблон, лише об’єднувати як рядки. Ця функція не може цього зробити. Також потрібно звернути увагу на такі умови:

1. Ви можете використовувати ці типи значень як умови для вибору комірок для подальшого додавання значень, що містяться в них: числові значення, логічні вирази, посилання на комірки тощо. 
2. Якщо перевіряється текст, логічні вирази або математичні знаки, то такі критерії вказуються в лапках.
3. Не можна використовувати терміни, довші за 255 символів.
4. Можливе використання приблизних критеріїв вибору значень за допомогою символів підстановки. Знак питання використовується для заміни одного символу, а знак множення (зірочка) необхідний для заміни кількох символів. 
5. Логічні значення, які знаходяться в діапазоні підсумовування, автоматично перетворюються в числові значення відповідно до їх типу. Таким чином, значення «ІСТИНА» перетворюється на одиницю, а «ХИБНІСТЬ» – в нуль. 
6. Якщо #VALUE! помилка з'являється в комірці, це означає, що кількість комірок в діапазонах умови та підсумовування різна. Необхідно переконатися, що розміри цих аргументів однакові. 

Приклади використання функції SUMIFS

функція SUMMESLIMN Виявляється, не так складно, як здається на перший погляд. Але для більшої ясності давайте розглянемо кілька практичних прикладів використання цієї функції SUMMESLIMN. Так буде набагато легше заглибитися в тему.

Динамічний діапазон підсумовування умов

Отже, почнемо з першого прикладу. Припустимо, у нас є таблиця, яка містить інформацію про те, як учні справляються з навчальною програмою з певного предмету. Є набір оцінок, успішність оцінюється за 10-бальною шкалою. Завдання - знайти оцінку за іспит тим студентам, прізвище яких починається на літеру А, а мінімальний бал - 5.

Таблиця виглядає так.

Щоб розрахувати загальний бал на основі описаних вище критеріїв, нам потрібно застосувати таку формулу.

Опишемо аргументи докладніше:

1. C3:C14 - це наш діапазон підсумовування. У нашому випадку він збігається з діапазоном умов. З нього будуть обрані бали, які використовуються для розрахунку суми, але тільки ті, які підпадають під наші критерії.
2. «>5» — наша перша умова.
3. B3:B14 — це другий діапазон підсумовування, який обробляється для відповідності другому критерію. Ми бачимо, що немає збігу з діапазоном підсумовування. Звідси робимо висновок, що діапазон підсумовування та діапазон умови можуть бути ідентичними або не бути ідентичними. 
4. «А*» — другий діапазон, який визначає відбір оцінок лише для тих студентів, прізвище яких починається на А. У нашому випадку зірочка означає будь-яку кількість символів. 

Після розрахунків отримуємо наступну таблицю.

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

Вибіркове підсумовування за умовою в Excel

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

Сама таблиця виглядає так. 

Для визначення кінцевого результату нам потрібна така формула.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

У результаті розрахунків, проведених за цією формулою, отримуємо наступний результат.

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

Функція SUMIFS для підсумовування значень за кількома умовами

Тепер наведемо ще один приклад для ілюстрації. У цьому випадку таблиця залишається такою ж, як і в попередньому випадку. 

Використовуємо наступну формулу (але записуємо її як формулу масиву, тобто вводимо її комбінацією клавіш CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Після функції SUMMESLIMN підсумує масив значень за критеріями, зазначеними у формулі (тобто країни Китай і Грузія), отриманий масив підсумовується звичайною функцією SUM, який записується як формула масиву.

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

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

Як бачите, нам це вдалося. У вас теж точно вийде. Великий успіх на цьому терені. Це дуже проста функція, яку може зрозуміти людина, яка тільки ступила на шлях вивчення Excel. І ми вже знаємо, що функція SUMMESLIMN дозволяє бути ефективним у будь-якій сфері діяльності, від бухгалтерії до освіти. Навіть якщо ви будуєте кар’єру в будь-якій іншій сфері, яка не була описана вище, ця функція все одно допоможе вам заробити гроші. Цим вона цінна.

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