Як використовувати функції SUMIF і SUMIFS у Microsoft Excel і Google Sheets? Короткий посібник з умовного підсумовування в програмах для роботи з електронними таблицями

Якщо ви маєте справу з великим набором даних в офісі, ви повинні використовувати Microsoft Excel, Google Sheets або будь-яку іншу подібну програму, щоб упорядкувати свої дані. Хоча функція «SUM» є дуже поширеною функцією електронної таблиці, яку ми всі вивчали в наші шкільні роки, вона дуже обмежена, очевидно, тому що вона просто дає вам суму деяких вибраних чисел у вказаному діапазоні. Але якщо вам потрібно щось більше, наприклад суму, яка задовольняє умову, ви можете скористатися функцією SUMIF.

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

Отже, сьогодні я розповім про функції SUMIF і SUMIFS у Microsoft Excel або Google Sheets, оскільки синтаксис функцій однаковий на обох платформах.

SUMIF – це функція, доступна в популярних програмах для роботи з електронними таблицями, яка використовується для обчислення суми значень, які відповідають певним критеріям. Ілюстрація зробить вам зрозумілою функцію.

Синтаксис функції SUMIF має такий вигляд.

=sumif(criteria_range, input_criteria, sum_range)

Діапазон_критеріїв:Це діапазон, у якому функція шукатиме критерії. На основі значень тут буде розраховано фактичну суму.

Input_criteria:Наведені тут вхідні дані шукатимуться в діапазоні_критеріїв для вибору значень.

Ілюстрація зробить вам зрозумілою функцію.

Сумовий_діапазон:Сума значень у цьому діапазоні буде розрахована, якщо критерії виконуються.

Ілюстрація функції SUMIF

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

У нас є імена співробітниківколонка А, назва відділу, в якому вони працюютьколонка Б, а їх зарплати вколонка C.

Мені потрібно розрахувати витрати на заробітну плату в організації по відділах.

Щоб обчислити те, що я щойно сказав, нам потрібно перерахувати назви відділів у клітинкахF8до F13, і ми отримаємо витрати на заробітну плату відповідних підрозділів у стовпці F у клітинкахG8доG13.

Отже, формула в першій комірці, тобто комірціG8відбуватиметься наступним чином.

=sumif(B2:B17,F8,C2:C17)

Тут B2:B17 відноситься до відділів, що є критерієм для функції SUMIF.

Комірка F8 посилається на ключ, який буде зіставлено зі значеннями в діапазоніB7:B17.

Сума значень у діапазоніC2:C17буде розраховано, якщо критерії виконано.

Скажімо, наприклад, відповідно до формули в G8, він шукатиме ключове слово в F8, яке є IT.

Тут ключове слово IT шукатиметься в кожній клітинці, починаючи з B2 до B17. У разі збігу відповідна клітинка у стовпці C, тобто C2, буде вибрана, а потім інші клітинки у стовпці B, якщо запис відповідає терміну «IT». Отже, значення в клітинках C2, C5 і C8 будуть додані, оскільки всі вони містять термін IT у відповідних клітинках у стовпці B.

У наступних клітинках стовпця G замініть F8 на F9, F10, F11 і так далі.

Отже, ось як все просто.

Що таке функція SUMIFS у Microsoft Excel і Google Sheets?

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

Синтаксис функції SUMIFS виглядає наступним чином.

=SUMIFS(sum_range, criteria_range_1, criteria_1, … , criteria_range_n, criteria_n)

Сумовий_діапазон:Це діапазон значень, у якому сума буде виконана, якщо умови виконуються.

Діапазон_критеріїв:Це діапазон, у якому шукатимуть критерії.

Критерії: це ключове слово, яке функція SUMIFS шукатиме в діапазоні критеріїв.

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

Крім того, unlike theSUMIFфункція, вСУМИдіапазон значень, які потрібно додати, буде на початку.

Ілюстрація функції SUMIFS

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

Отже, я буду використовувати функцію SUMIFS для розрахунку витрат на зарплату в організації між відділами та персоналом.

Отже, якщо я хочу витрати організації на зарплату для ІТ-керівників у камеріK8, я маю зберегти назви відділів у стовпці I, а кадрів у стовпці J. Формула в клітинці K8 виглядатиме так.

=sumifs(D2:D17,B2:B17,I8,C2:C17,J8)

Тут функція SUMIFS знайде критерії в клітинках I8 і J8. Критерії в комірці I8 шукатимуть у діапазоні B2:B17, а критерії в J8 – у діапазоні C2:C17. Якщо умови збігаються, буде додано відповідні комірки, у яких збігаються обидві умови.

Те саме буде зроблено в наступних клітинках у стовпці K, де I8 та J8 буде замінено на I9, J9 тощо.

Отже, це все про SUMIF і функцію SUMIFS. У випадку великих електронних таблиць, якщо ви використовуєте функції SUMIF і SUMIFS, багато ручних завдань можна звести до мінімуму. Функції SUMIF і SUMIFS також можна інтегрувати з іншими функціями Microsoft Excel або Google Sheets, щоб зробити ваші завдання ще простішими.

Сподіваюся, тепер ви зрозуміли, як використовувати функції SUMIF і SUMIFS. Маєте питання? Не соромтеся коментувати те саме нижче.