Приклади функції SUMIF і SUMIFS в Excel


Функції SUMIF і SUMIFS допоможуть порахувати суму для тих значень стовпця, які відповідають зазначеному критерію.

Для прикладу розглянемо наступну таблицю. В ній вказано ім'я та стать учня, в якому класі навчається та середній бал. Використовуючи функцію SUMIF, розв'яжемо таку задачу: порахуємо суму значень стовпця «Середній бал» для хлопчиків і для всіх дівчаток. Завдання для функції SUMIFS така: порахувати суму середнього балу для хлопчиків і дівчаток, які навчаються в 8 і 9 класі окремо.

Примеры функции СУММЕСЛИ и СУММЕСЛИМН в Excel

Розглянемо аргументи для SUMIF.

=SUMIF(діапазон; критерій; [діапазон підсумовування])

Діапазон – тут необхідно вказати, той стовпець, дані в якому будуть порівнюватися із заданим Критерієм. У прикладі, це діапазону D2:D26.

Критерій – тут потрібно вибрати одне значення з раніше зазначеного Діапазону. Спочатку будемо вказувати хлопчиків, потім дівчаток.

Діапазон підсумовування – це стовпець, значення в якому потрібно підсумувати, якщо вони відносяться до вибраного Критерію. Наприклад, якщо для обраного Діапазону D2:D26 зазначений Критерій «хлопчик», значить у стовпці «Середній бал»: С2:С26, будуть додаватися всі значення, які відносяться до хлопчиків.

Приклад функції SUMIF.

Виділяємо комірку G4 і ставимо «=». Пишемо в ній SUMIF і відкриваємо дужку «(». В якості Діапазону, з якого будуть вибиратися значення, виділяємо D2:D26. В якості Критерію, потрібно вибрати одне із значень у раніше вказаному Діапазоні. У нас це «хлопчик», тому виділяємо комірку D3. Діапазон підсумовування – це стовпець, в якому вказано «Середній бал». Виділяємо комірки С2:С26. Між аргументами ставте«; » в кінці, закрийте дужку «)». Натисніть «Enter».

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

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

Виділяємо комірку G4 і в Рядку формул після D2, D26, C2, C26 натискаємо клавішу «F4». В адресах на клітинки, з'являться знаки «$». Таким чином, ми робимо посилання абсолютними. Щоб розтягнути формулу за стовпцем, потягніть за правий нижній кут комірки, курсор змінить при цьому вид на чорний плюсик.

Якщо Вам цікаво, як зробити посилання в Ексель: абсолютні, відносні, на інший аркуш або книгу, прочитати статтю по цій темі.

Далі дивимося на «Рядок формул». В якості аргументів для Діапазону і Діапазон підсумовування беруться правильні значення: D2:D26, C2:C26. В якості Критерію вкажіть адресу потрібної клітинки. Оскільки, потрібно порахувати середній бал для всіх дівчаток, у стовпці D вибираємо адресу будь-якої комірки, в якій зазначено «дівчинка»: D4.

Тепер розглянемо аргументи функції SUMIFS в Excel.

=SUMIFS(діапазон підсумовування; діапазон умови1; условие1; діапазон условия2; условие2)

Діапазон підсумовування – це стовпець, значення якого будуть вибиратися, якщо вони відповідають одному й другому заданій умові. У прикладі, це С2:С26.

Діапазон умови1 – це перший діапазон для порівняння. Потрібно вибрати стать учня, тому діапазон D2:D26.

Условие1 – потрібно вказати те значення, яке буде вибиратися з Діапазону условий1. Нас цікавлять хлопчики, тому вкажемо клітинку D3.

Діапазон условия2 – другий діапазон порівняння. Тут будемо вибирати клас, в якому навчається дитина. Відповідно вкажемо діапазон В2:В26.

Условие2 – конкретне значення, яке нас цікавить з Діапазону условій2. Спочатку потрібно буде виділити комірку, в якій зазначено «8 клас», потім «9 клас».

Приклад функції SUMIFS.

Вирішувати будемо другу задачу. Виділяємо комірку Н8, ставимо «=», пишемо SUMIFS і відкриваємо дужку «(». Як Діапазон підсумовування вказуємо стовпець з «Середнім балом»: С2:С26. Діапазон умови1 – це стать учня: D2:D26, Условие1 – це хлопчики: клітинку D3. Діапазон условия2 – це клас: В2:В26, Условие2 – 8 клас: осередок В3. Закрийте дужку, натисніть «Enter».

У Рядку формул відразу зробіть абсолютні посилання на стовпці: Клас, Середній бал, Малий/Дів.

Розтягуємо формулу з клітинки Н8:Н11. Тепер вкажемо правильні клітинки як Умови1 і Условия2.

У комірці Н9 вважається середній бал для хлопчиків, які навчаються у 9 класі. Тому, Условие1 для першого діапазону «хлопчик»: D3, Условие2 для другого діапазону «9»: В6.

У комірці Н10 вважаємо середній бал дівчаток, які навчаються у 8 класі. Условие1 – «дівчинка», D4, Условие2 – «8», В3.

У комірці Н11 вважаємо середній бал дівчаток, які навчаються в 9 класі. Условие1 – «дівчинка», D4, Условие2 – «9», В2.

Таким чином, ми порахували суму середнього бала для хлопчиків і дівчаток, які навчаються в 8 і 9 класах окремо.

Тепер Давайте перевіримо, щоб результат був правильним. Порахуємо суму «Середнього балу» для всіх значень вихідної таблиці, Завдання 1 та Завдання 2. Сума має бути однаковою.

Щоб детальніше дізнатися, як порахувати суму в Ексель, перейдіть по посиланню і прочитайте статтю.

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