Функція SUMIF в Excel: приклади застосування

Excel – незамінний помічник у роботі таких фахівців, як економісти, бухгалтери, фінансисти, менеджери, технологи та багатьох інших. При прийомі на роботу за цими вакансіями все частіше кандидати змушені проходити тестування на знання програми. У завданнях перевіряється володіння ВВР, вміння побудувати зведену таблицю, знання основних функцій, серед яких обов'язково присутня функція SUMIF. Вона дуже важлива. В Excel функція значно SUMIF прискорює процес проведення обчислень, коли необхідно обчислити суму по одному або декільком умовам.

Сутність функції

В Excel функція SUMIF відноситься до категорії математичні. На відміну від простої функції суми, ця дозволяє обчислити суму тільки за елементами, що відповідають певним критеріям.


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

Приклад застосування функції SUMIF з однією умовою

Для функції SUMIF в Excel найпростішим прикладом є вибіркове підсумовування з однією умовою. Для аналізу надана таблиця (на малюнку нижче), у яку внесена інформація про те, на яку суму, якому замовнику, в якому регіоні продали замовлення менеджери компанії.


Дані таблиці заповнюються щоденно. Наприкінці місяця для розрахунку премії Щербакову А. К. потрібно дізнатися, яка загальна сума замовлень, реалізованих цим працівником. В таких випадках в Excel функція SUMIF і використовується. Для обчислення необхідно виконати дії: Поставити курсор в комірку, куди необхідно вивести результат На стрічці вибрати «Формули»-«Математичні»-SUMIF. Відкриється вікно «Аргументи функції». На полі певного аргументу вибираються необхідні дані. У розглянутому випадку діапазон повинен включати прізвища, тому виділяється стовпець з прізвищами співробітників. Критерієм є текст. Слід пам'ятати, що Excel дізнається текст, якщо він взятий в лапки. Діапазон підсумовування буде стовпець вартості замовлень, адже саме цей показник потрібно отримати в результаті. Коли всі поля заповнені, натисканням клавіші ОК виводиться результат обчислень. Менеджер Щербаков А. К. за минулий місяць реалізував продукції на 54302 рубля (поле «Значення» в діалоговому показує результат ще до натискання клавіші ОК). На розглянутому вище прикладі видно, що в Excel функція SUMIF дозволяє оперативно підсумувати потрібні показники. Бувають випадки, коли підсумовування необхідно провести не по одному, а по декількох умов. Починаючи з 2007 року, підсумовувати із зазначенням декількох умов можна за допомогою функції Sumifs, яка функціонально розширює в Excel функцію SUMIF. До версії Excel 2007 року припадало на додаток до використання можливостей застосовувати інші інструменти Excel, наприклад, фільтр.

Функція SUMIF в Excel: приклад з кількома умовами

У розглянутому вище прикладі необхідно знайти суму замовлень реалізованим менеджером Воробйовим В. І. замовнику ІП Васькову А. П. Таким чином, критеріїв відбору два: співробітник і замовник. Як і в попередньому прикладі, ставимо курсор у комірку, де буде результат. «Формули»-«Математичні»-Sumifs. З'являється вікно «Аргументи». Заповнюється воно дещо відмінно від попереднього прикладу. Тут діапазон підсумовування стоїть першим, необхідно виділити стовпець з вартістю замовлень. Далі йдуть діапазони умов, потрібно виділити стовпці, які їх містять. Самі умови прописуються як текстові, аналогічно до попереднього прикладу.

SUMIF з логічним критерієм (більше, менше, дорівнює)

Часто виникає необхідність використовувати в якості критерію не певне значення, а область значень, більше, менше або дорівнює точці відліку. В таких випадках при зазначенні в критерії заданого умови його укладають в подвійні лапки. На малюнку показано, як порахувати суму всіх замовлень вартістю більше 10000 рулів. Аналогічно використовується функція при інших умовах, змінюються тільки знаки в області критеріїв: < – меньше; = – дорівнює (можна не вказувати); – не дорівнює; >= – більше або дорівнює; <= – меньше или равно. В Excel функція SUMIF з критерієм у вигляді логічного виразу застосовується досить часто.

SUMIF з текстовим критерієм

Раннє вже було розглянуто застосування тексту в якості критерію у функції SUMIF. З особливостей застосування слід зазначити, що збіг по тексту може бути не точним. Для знаходження суми на замовлення в Москві і Московській області в полі вводиться значення критерію "*моск*". Виділення зірочками передбачає частковий збіг за критерієм. При роботі з текстовими критеріями немає необхідності чітко дотримуватися регістр.

Функція SUMIF з датою в якості критерію

Приклад з датами для функції SUMIF в Excel можна уявити, якщо додати в вихідній таблиці стовпець з датами продажів. Нехай завданням стане підсумовування всіх замовлень, реалізованих 15102017 року і пізніше. Можуть змінюватися знаки порівняння, замість конкретної дати може використовуватися функція "сьогодні". Для підсумовування значень, що відносяться до періодів між двома датами, застосовується функція SUMIF. Звичайно, без Excel той ритм роботи у багатьох галузях, який заданий сьогодні, неможливий. Вдосконалення у цій програмі – запорука успішного кар'єрного зростання для багатьох фахівців. І функція SUMIF – не остання помічниця у всіх професійних починаннях.