Цей підручник демонструє, як користуватися Вимел SUMIF і SUMIFS Функцціонів в Excel і Google Таблицях для підсумовування даних, що відповідають певним критеріям.
Огляд функції SUMIF
Ви можете використовувати функцію SUMIF в Excel для суми клітинок, які містять певне значення, суми клітинок, які більші або рівні значенням тощо.
(Зверніть увагу, як виглядають вхідні дані формул)
Синтаксис та аргументи функції SUMIF:
| 1 | = SUMIF (діапазон, критерії, [сума_діапазону]) |
діапазон - Діапазон клітинок, до яких потрібно застосувати критерії.
критерії - Критерії, що використовуються для визначення, які клітинки додати.
sum_range - [необов’язково] Клітини для об’єднання. Якщо sum_range пропущено, клітини в діапазоні замість цього додаються.
Що таке функція SUMIF?
Функція SUMIF - одна з старих функцій, що використовуються в електронних таблицях. Він використовується для сканування в діапазоні клітинок, перевірки на наявність певного критерію, а потім додавання значень у діапазоні, що відповідає цим значенням. Початкова функція SUMIF була обмежена лише одним критерієм. Після 2007 року була створена функція SUMIFS, яка дозволяє використовувати безліч критеріїв. Більшість загального використання залишається незмінним між ними, але є деякі критичні відмінності в синтаксисі, які ми обговоримо у цій статті.
Якщо ви ще цього не зробили, ви можете переглянути більшість подібної структури та приклади у статті COUNTIFS.
Основний приклад
Давайте розглянемо цей список зареєстрованих продажів і хочемо дізнатися загальний дохід.
Оскільки у нас були витрати, від’ємне значення, ми не можемо просто зробити базову суму. Натомість ми хочемо підсумувати лише значення, які більші за 0. “Більше 0” - це те, що буде нашим критерієм у функції SUMIF. Наша формула стверджує це
| 1 | = SUMIF (A2: A7, "> 0") |
Приклад з двома колонками
Хоча початкова функція SUMIF була розроблена, щоб дозволити вам застосувати критерій до діапазону чисел, які потрібно підсумувати, більшу частину часу вам потрібно буде застосувати один або кілька критеріїв до інших стовпців. Розглянемо цю таблицю:
Тепер, якщо ми використовуємо оригінальну функцію SUMIF, щоб дізнатися, скільки у нас бананів (перераховано в клітині D1), нам потрібно буде надати діапазон, який ми хочемо сума як останній аргумент, і тому наша формула буде такою
| 1 | = SUMIF (A2: A7, D1, B2: B7) |
Однак, коли програмісти врешті -решт зрозуміли, що користувачі хочуть надати більше одного критерію, була створена функція SUMIFS. Для того, щоб створити одну структуру, яка б працювала за будь -якою кількістю критеріїв, SUMIFS вимагає, щоб діапазон сум був вказаний першим. У нашому прикладі це означає, що формула має бути такою
| 1 | = SUMIFS (B2: B7, A2: A7, D1) |
ПРИМІТКА. Ці дві формули отримують однаковий результат і можуть виглядати однаково, тому зверніть пильну увагу на те, яка функція використовується, щоб переконатися, що ви перерахуєте всі аргументи у правильному порядку.
Робота з датами, кілька критеріїв
Під час роботи з датами в електронній таблиці, хоча є можливість ввести дату безпосередньо у формулу, найкраще використовувати цю дату в комірці, щоб ви могли просто посилатися на клітинку у формулі. Наприклад, це допомагає комп’ютеру зрозуміти, що ви хочете використовувати дату 27.05.2020, а не число 5, поділене на 27, поділене на 2022 рік.
Давайте подивимось на нашу наступну таблицю, де реєструється кількість відвідувачів сайту кожні два тижні.
Ми можемо вказати початкову та кінцеву точки діапазону, який ми хочемо подивитися в D2 та E2. Наша формула для підсумовування кількості відвідувачів у цьому діапазоні може бути такою:
| 1 | = SUMIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2) |
Зверніть увагу, як нам вдалося об’єднати порівняння “=” із посиланнями на клітинки для створення критеріїв. Крім того, хоча обидва критерії застосовувалися до одного діапазону клітинок (A2: A7), вам потрібно виписати діапазон двічі, один раз для кожного критерію.
Кілька стовпців
При використанні кількох критеріїв ви можете застосувати їх до того самого діапазону, що й у попередньому прикладі, або застосувати до різних діапазонів. Давайте об’єднаємо наші приклади даних у цю таблицю:
Ми налаштували деякі клітинки, щоб користувач міг вводити те, що він хоче шукати, у клітинках від E2 до G2. Таким чином, нам потрібна формула, яка додасть загальну кількість яблук, зібраних у лютому. Наша формула виглядає так:
| 1 | = SUMIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2) |
SUMIFS з логікою типу OR
До цього моменту всі приклади, які ми використовували, були порівнянням на основі І, де ми шукаємо рядки, які відповідають усім нашим критеріям. Тепер ми розглянемо випадок, коли ви хочете шукати можливість того, щоб рядок відповідав тому чи іншому критерію.
Давайте подивимося на цей список продажів:
Ми хотіли б підсумувати загальний обсяг продажів як для Адама, так і для Боба. Для цього у вас є пара варіантів. Найпростіший спосіб - скласти два SUMIFS разом:
| 1 | = SUMIFS (B2: B7, A2: A7, "Адам")+SUMIFS (B2: B7, A2: A7, "Боб") |
Тут комп’ютер обчислив наші індивідуальні бали, а потім додав їх разом.
Наш наступний варіант хороший для тих випадків, коли у вас більше діапазонів критеріїв, таких, що вам не потрібно повторно переписувати всю формулу. У попередній формулі ми вручну сказали комп’ютеру додати два різних SUMIFS разом. Однак ви також можете зробити це, написавши свої критерії всередині масиву, наприклад:
| 1 | = SUM (SUMIFS (B2: B7, A2: A7, {"Адам", "Боб"})) |
Подивіться, як масив побудований у фігурних дужках. Коли комп’ютер оцінить цю формулу, він буде знати, що ми хочемо обчислити функцію SUMIFS для кожного елемента нашого масиву, створюючи таким чином масив цифр. Зовнішня функція SUM тоді візьме цей масив чисел і перетворить його на єдине число. Перейшовши до оцінки формули, це виглядатиме так:
| 123 | = SUM (SUMIFS (B2: B7, A2: A7, {"Адам", "Боб"}))= SUM (27401, 43470)= 70871 |
Ми отримуємо той самий результат, але нам вдалося записати формулу трохи коротше.
Робота з заготовками
Іноді у наборі даних будуть порожні клітинки, які потрібно або знайти, або уникати. Встановлення критеріїв для них може бути трохи складним, тому давайте розглянемо інший приклад.
Зауважте, що клітинка А3 справді порожня, тоді як комірка А5 має формулу, що повертає рядок нульової довжини “”. Якщо ми хочемо знайти загальну суму справді порожні клітинки, ми б використовували критерій “=”, і наша формула виглядатиме так:
| 1 | = SUMIFS (B2: B7, A2: A7, "=") |
З іншого боку, якщо ми хочемо отримати суму для всіх клітинок, які візуально виглядають порожніми, ми змінимо критерій на "", а формула виглядає так
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Давайте поговоримо: що, якщо ви хочете знайти суму не порожніх клітинок? На жаль, поточний дизайн не дозволяє уникнути рядка нульової довжини. Ви можете використовувати критерій "", але, як ви можете бачити у прикладі, він все ще містить значення з рядка 5.
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Якщо вам не потрібно рахувати клітинки, що містять рядки нульової довжини, вам слід розглянути можливість використання функції LEN всередині SUMPRODUCT
SUMIF у Google Таблицях
Функція SUMIF працює точно так само в Google Таблицях, як і в Excel:
