Функція SUMIF і її варіанти в Excel

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

Додавання в Excel

Визначення суми елементів у програмі можливо з допомогою найпростіших арифметичних операцій, які позначаються знаком «+». Для додавання невеликої кількості доданків цей метод підходить найкраще. Наприклад, для підрахунку підсумкової вартості туристичної заявки, яка включає ціни на готель, трансфери та екскурсії, можна використовувати операцію додавання за допомогою формули. Формула вноситься у клітинку після знака рівності («=»). Для вибору осередків, включених в суму, достатньо клікнути на потрібну, і її розташування буде зазначено у формулі.

Знаходження суми

Наступний етап – знаходження суми по стовпцях. У вже наведеному прикладі поставимо завдання підрахувати загальний дохід з продажу всіх заявок, що включає оплату готелів, транспорту та екскурсійних програм. Для цього потрібно підсумувати усі поля колонки «Загальна вартість». Застосування арифметичної суми тут незручно, так як кількість записів у таблиці може бути дуже великим. Для таких ситуацій підходить функція Excel СУМ(). Вона додає в зазначеному діапазоні клітинок без необхідності прописування розташування кожного поля: СУММ(число1;[число2];), де в якості аргументу «число» може бути вказаний діапазон чисел у комірках. Функція приймає не більше 255 аргументів. У прикладі заявок туристичного агентства вона буде застосована до стовпця I:

Умовне підсумовування

Бувають завдання, в яких необхідно знайти суму всіх елементів таблиці в стовпці або діапазоні, а тільки тих, що відповідають поставленим вимогам. У таких випадках користувачеві необхідна можливість зазначення умови для включення числа обчислення. Для цього призначена функція SUMIF. Її синтаксис: SUMIF(діапазон; умова;[диапазон_суммирования]). В інтервалі полів, зазначеному аргументом «діапазон» перевіряється заданий вимога для додавання числа до суми. Цей атрибут і наступний за ним – «умова» є обов'язковими для використання в формулі. Тому в полі «Критерій» ми вказуємо свій вимога. Третій параметр команди вказує клітинки, дані з яких будуть підсумовуватися. Це поле не є обов'язковим. Якщо воно не зазначено, в якості діапазону береться вже прописана в першому аргументі. Але ці поля можуть відрізнятися, якщо функція SUMIF перевіряє умову по одному стовпцю, а додає в іншому. Покажемо це на прикладі.

Приклади використання

Підрахуємо доходи тільки від тих туристів, які замовляли у нас індивідуальні трансфери. В якості полів для перевірки вимоги вказуємо стовпець «Трансфери», сам критерій – рівність поля значенням «Індивідуальні».
І в цьому випадку ми повинні вказати колонку доданків у полі «Диапазон_суммирования» вікна параметрів функції SUMIF: В результаті отримуємо суму даних тільки по заявках з індивідуальними трансферами. Ще один приклад – використання команди в сукупності з формулою ВВР: ВПР(шуканий показник; інтервал пошуку; номер колонки в інтервалі; точна або приблизна рівність — 0 (точне) або 1(приблизний)). На практиці для використання ВВР Excel виділяється підтаблиця (параметр 2), перший стовпець якої містить шукане значення (параметр 1). Команда визначає рядок, в якому воно міститься, і показує в результаті вміст поля у знайденій зазначеному рядку і стовпці (3). Припустимо, в таблиці доходів трансфери вказані не найменуваннями, а тільки кодами, назви ж містяться в іншій таблиці. Для вирішення попередньої задачі визначимо зі списку кодів потрібний і знайдемо його в стовпці таблиці доходів. Для вибору потрібного коду скористаємося функцією ВВР: =ВПР(A11;Таблица12;2). Вона поверне число 2 – код індивідуальних трансферів. Тепер підставляємо цю функцію у вікно параметрів SUMIF в Excel і отримуємо те ж значення, що і в першому випадку.

Сума з кількома умовами

В ускладненою задачі потрібно обчислити суму з зазначенням не одного, а декількох умов. Але описана формула працює тільки з однією вимогою, тому нам потрібна інша. Аналогом попередньої функції є SUMIFS, яка складає клітинки, враховуючи кілька обмежень, вказаних користувачем.
За аналогією з функцією SUMIF у вікні параметрів даної формули вказуємо діапазон комірок для складання та умови. Синтаксис команди: SUMIFS(диапазон_суммирования; диапазон_условия1; условие1;[диапазон_условия2; условие2]; ). Тут діапазон підсумовування вже є обов'язковим аргументом. Крім нього, ставлять ряд умов для пошуку із зазначенням критеріїв відбору. На прикладі порахуємо дохід тільки від тих заявок, трансфери в яких індивідуальні, а туристи замовили оглядову екскурсію. Цим критеріям відповідає лише одна заявка, тому результат буде дорівнює доходу по ній. Використання функції SUMIF в Excel полегшує виконання багатьох завдань. Її синтаксис простий та доступний для застосування навіть недосвідченому користувачеві програми.