Резюме
Для суммирования по месяцам в столбцах вы можете использовать функцию СУММЕСЛИМН вместе с функцией EOMONTH. В показанном примере формула в G5:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
В этой формуле используются три именованных диапазона: «сумма» (D5: D15), «клиент» (B5: B15) и «дата» (C5: C15).
Объяснение
Функция СУММЕСЛИМН предназначена для суммирования значений в диапазоне на основе одного или нескольких критериев. В этом случае нам понадобятся три критерия:
- Имя клиента = клиент в столбце F
- Дата> = первого числа месяца (с даты в строке 4)
- Дата <= конец месяца (с даты в строке 4)
Ключевой момент: названия месяцев в G4: I4 на самом деле являются действительными датами, отформатированными в пользовательском числовом формате «ммм». Это позволяет нам использовать значения даты в G4: I4 непосредственно для критериев №2 и №3 выше.
Вернемся к СУММЕСЛИМН, первая пара диапазон / критерий используется для проверки имени клиента:
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 смешана, чтобы строка не изменялась.
Решение сводной таблицы
Сводная таблица была бы отличным решением этой проблемы, потому что она может автоматически группироваться по месяцам без каких-либо формул. Для параллельного сравнения формул и сводных таблиц см. Это видео: Зачем нужны сводные таблицы.
