Корисні функції в Excel: SUMIF, SUMIFS, COUNTIF, COUNTIFS.
Часто буває потрібен розрахунок не по всьому стовпцю даних, а тільки по тих значень, для яких виконуються певні умови в сусідніх стовпцях. Наприклад, по несортованої базі співробітників знадобилося порахувати кількість відгуляли днів відпустки тих, чиї прізвища починаються на «T». Кажуть, що суворі челябінські бухгалтери легко роблять такий розрахунок для бази в 20 тис. Співробітників на калькуляторі, в крайньому випадку, черезSumвExcel.Ну а менш старанні працівники можуть скористатися функціями, з вбудованою фільтрацією, перерахованими в назві цієї статті.
SUMIF, SUMIFS - дозволяють виконувати додавання не по всьому стовпцю, як і їх більш простий побратим - Sum, а тільки з тих рядків, які задовольняють потрібним критеріям в одному стовпці, якщо вико SUMIF, або навіть через кілька стовпчиків, якщо використовуємо SUMIFS.
COUNTIF, COUNTIFS дозволяють розрахувати кількість рядків в масиві, що задовольняють заданим критеріям по одному (COUNTIF) або декільком (COUNTIFS) стовпчиках.
Критерії задаються в якості аргументів функцій. Виходить свого роду фільтрація перед складанням.
За приципу завдання критеріїв і фільтрації всі ці функції дуже схожі. Тому розглянемо один приклад на основі SUMIFS. якщо уловите суть, застосовувати інші буде не складно.
Нижче наведено загальний вигляд функції SUMIFS з її аргументами:
1) Sum_rang - масив з якого будуть братися значення для суми.
2) Criteria_range1 - перший масив в якому буде здійснюватися пошук по першому заданому критерію.
3) Criteria1 - перший критерій, за яким буде здійснено пошук.
4) Criteria_range2 - масив, в якому буде здійснено пошук по наступному критерію.
5) Criteria2 - наступний критерій для перевірки.
Припустимо, що на основі цієї таблиці нам необхідно порахувати загальний бюджет витрачений на регіональне ТБ. Тобто порахувати суму по стовпцю Budget для рядків, де одночасно в стовпці Media є "TV", а в стовпці Region зазначено "Regional".
Щоб зробити такий розрахунок, у формулі SUMIFS в якості першого параметра вказуємо стовпець за яким буде проводиться складання - Budget ($ D $ 1: $ D $ 9).
Наступні параметри - це параметри фільтрації, вони Удут парами.
Другий і третій - це один з Столц, в якому потрібно буде шукати задане значення, у нас це стовпець Media ($ B $ 1: $ B $ 9) і саме значення, яке шукаємо - "TV".
Третій і четвертий параметри - наступний стовпець - Region ($ C $ 1: $ C $ 9) і в такому значенні - "Regional".
В результаті отримаємо такий запис:
Ця формула підсумовує всі рядки з шпальти Budget ($ D $ 1: $ D $ 9). для яких одночасно виконуються дві умови: в стовпці медіа Media ($ B $ 1: $ B $ 9) значення відповідає TV, а в стовпці регіон Region ($ C $ 1: $ C $ 9), значення відповідаючи Regional.
Зверніть увагу, що формула шукає точну відповідність.
Додатково, я рекомендую завжди перевіряти коректність розрахунків.
У наведеному вище прикладі, можна поширити розрахунок з використанням SUMIFS на всі комбінації медіа та регіональності. У нас вийде ще одна таблиця, де кожен рядок бюджету розраховується формулою SUMIFS:
Як бачите, я вивів сумарний бюджет в рядку Total. зіставляючи це значення з сумою бюджету по першій таблиці, легко зрозуміти, чи є помилки в нашому розрахунку.
Звичайно, виглядає трохи громіздко і для разових обчислень простіше користуватися зведеною таблицею або фільтрами. Але коли таблиці необхідно підтримувати і оновлювати, ці формули набагато зручніше, вони економлять час, роблять формули легко читаються.
Головне стежити за коректністю даних, що вводяться.
Трохи пізніше я також розповім про те, як за допомогою текстових функцій можна приводити текст до одного виду.