Резюме
Для підсумовування за місяцями у стовпцях можна використовувати функцію SUMIFS разом із функцією EOMONTH. У наведеному прикладі формула в G5 має вигляд:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Ця формула використовує три іменовані діапазони: "сума" (D5: D15), "клієнт" (B5: B15) і "дата" (C5: C15).
Пояснення
Функція SUMIFS призначена для підсумовування значень у діапазоні на основі одного або декількох критеріїв. У цьому випадку нам потрібні три критерії:
- Ім'я клієнта = клієнт у стовпці F
- Дата> = перше числа (з дати в рядку 4)
- Дата <= кінець місяця (з дати в рядку 4)
Ключовий момент: назви місяців у G4: I4 є фактично дійсними датами, відформатованими у форматі користувацького числа "mmm". Це дозволяє нам використовувати значення дати в G4: I4 безпосередньо для критеріїв №2 та №3 вище.
Назад у SUMIFS перша пара діапазону / критеріїв використовується для перевірки імені клієнта:
client,$F5
Примітка F5 є змішаним посиланням із заблокованою колонкою. Це дозволяє рядку змінюватися під час копіювання формули через таблицю, але ім'я клієнта завжди надходить із стовпця F.
Друга пара діапазонів / критеріїв використовується для перевірки дат щодо першого числа місяця:
date,">="&G$4
Як зазначалося вище, значення в G4: I4 насправді є датами: 1 січня 2019 року, 1 лютого 2019 року та 1 березня 2019 року. Отже, цей критерій просто перевіряє дати, більші за дати першого місяця в рядку 4. Зверніть увагу, що це ще одне змішане посилання, на цей раз із заблокованим рядком. Це дозволяє стовпцю змінюватися під час копіювання формули по таблиці, але зберігає фіксований номер рядка. Конкатенація за допомогою амперсанда (&) необхідна при побудові критеріїв, що використовують логічний оператор та значення з іншої комірки.
Третя пара діапазонів / критеріїв використовується для перевірки дат щодо останнього дня місяця:
date,"<="&EOMONTH(G$4,0)
Щоб отримати останній день кожного місяця, ми використовуємо функцію EOMONTH на дату із заголовка стовпця в рядку 4. Якщо для аргументу місяці вказано нуль, EOMONTH повертає останній день того ж місяця. Знову, посилання на G4 змішується, щоб рядок не змінювався.
Рішення зведеної таблиці
Зведена таблиця була б чудовим рішенням для цієї проблеми, оскільки вона може автоматично групуватися за місяцями, не маючи взагалі жодних формул. Порівняльне порівняння формул із зведеними таблицями див. У цьому відео: Навіщо зведені таблиці.
