Загрузите образец книги
В этом руководстве будет показано, как использовать функцию СУММЕСЛИМН для суммирования данных, соответствующих определенным месяцам, в Excel и Google Таблицах.
Сумма по месяцам
Сначала мы продемонстрируем, как суммировать данные, соответствующие датам, приходящимся на определенный месяц и год.
Мы можем использовать функцию СУММЕСЛИМН вместе с функциями ДАТА, ГОД, МЕСЯЦ и МЕСЯЦ, чтобы суммировать Количество продаж внутри каждого Месяц.
| 1 | = СУММЕСЛИМН (C3: C9, B3: B9, "> =" & ДАТА (ГОД (E3), МЕСЯЦ (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Чтобы построить формулу выше, мы начнем с определения диапазона дат для каждого месяца.. Мы используем функцию DATE, чтобы определить первый день месяца (например, 01.05.2021). Мы можем сделать это, «жестко закодировав» дату в формуле:
| 1 | "> =" & ДАТА (2021,5,1) |
Или, что еще лучше, мы можем сделать критерии гибкими, обратившись к дате в ячейке E3, извлекая год и месяц даты (и установив день равным 1), как показано в примере выше:
| 1 | "> =" & ДАТА (ГОД (E3), МЕСЯЦ (E3), 1) |
Чтобы определить последний день месяца, мы можем использовать функцию EOMONTH:
| 1 | "<=" & EOMONTH (E3,0) |
Объединив все эти критерии, мы можем написать следующую формулу СУММЕСЛИМН:
| 1 | = СУММЕСЛИМН (C3: C9, B3: B9, "> =" & ДАТА (ГОД (E3), МЕСЯЦ (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Блокировка ссылок на ячейки
Чтобы наши формулы было легче читать, мы показали формулы без заблокированных ссылок на ячейки:
| 1 | = СУММЕСЛИМН (C3: C9, B3: B9, "> =" & ДАТА (ГОД (E3), МЕСЯЦ (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Но эти формулы не будут работать должным образом при копировании и вставке в другое место в файле. Вместо этого вы должны использовать ссылки на заблокированные ячейки следующим образом:
| 1 | = СУММЕСЛИМН ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" & ДАТА (ГОД (E3), МЕСЯЦ (E3), 1), $ B $ 3: $ B $ 9, "<=" & EOMONTH (E3,0)) |
Прочтите нашу статью о блокировке ссылок на ячейки, чтобы узнать больше.
Форматирование значений месяца
В этом примере мы перечислили месяцы в столбце E. Эти значения месяцев на самом деле представляют собой даты, отформатированные так, чтобы не указывать день, с использованием пользовательского форматирования чисел.
Пользовательский формат данных - «ммм гггг» для отображения мая 2022 года.
Сумма по месяцам за несколько лет
В приведенном выше примере суммированы данные с датами, относящимися к определенному месяцу и году. Вместо этого вы можете суммировать данные с датами, приходящимися на месяц любого года, с помощью функции СУММПРОИЗВ.
| 1 | = СУММПРОИЗВ (C3: C8; - (МЕСЯЦ (B3: B8) = МЕСЯЦ (G3))) |
В этом примере мы используем функцию СУММПРОИЗВ для выполнения сложных вычислений «сумма если». Давайте пройдемся по формуле.
Это наша окончательная формула:
| 1 | = СУММПРОИЗВ (C3: C8; - (МЕСЯЦ (B3: B8) = МЕСЯЦ (G3))) |
Во-первых, функция СУММПРОИЗВ перечисляет Количество продаж для каждого Дата продажи а затем сравнивает месяц каждого Дата продажи против указанного Месяц, возвращая ИСТИНА, если месяцы совпадают, или ЛОЖЬ, если они не совпадают:
| 1 | = СУММПРОИЗВ ({30; 42; 51; 28; 17; 34}, - ({ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ})) |
Затем двойные дефисы (-) преобразуют значения ИСТИНА и ЛОЖЬ в единицы и нули:
| 1 | = СУММПРОИЗВ ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
Затем функция СУММПРОИЗВ умножает каждую пару записей в массивах:
| 1 | = СУММПРОИЗВ ({30; 0; 51; 0; 17; 0}) |
Наконец, суммируются числа в массиве:
| 1 | =98 |
Более подробную информацию об использовании логических операторов и команды «-» в функции SUMPRODUCT можно найти здесь.
Сумма по месяцам в Google Таблицах
Эти формулы работают в Google Таблицах точно так же, как и в Excel.
