Содержание:
Функция SUMIFS (СУММЕСЛИМН) в Google Таблицах весьма полезна в различных ситуациях, в том числе в тех, где вы хотите найти сумму значений ячеек в заданном диапазоне дат. Это означает, что мы можем использовать функцию SUMIFS, чтобы найти сумму значений в столбце, если соответствующие даты в другом столбце попадают между двумя заданными датами.
В этом уроке мы объясним, как это сделать. Мы также покажем вам различные способы использования функции SUMIFS в Google Таблицах с датами в разных ситуациях.
Что делает функция СУММЕСЛИМН?
Функция СУММЕСЛИМН (SUMIFS) используется для нахождения суммы диапазона значений, удовлетворяющих заданному набору условий.
По сути, она просматривает диапазон ячеек, извлекает значения тех, которые соответствуют всем заданным условиям, а затем суммирует эти извлеченные значения.
Синтаксис функции SUMIFS
Синтаксис функции СУММЕСЛИМН (SUMIFS) следующий:
=SUMIFS(sum_range, criteria_range1, criteria1,[ criteria_range2, criteria2, ... criteria_range_n, criterian])
Здесь,
- sum_range — это диапазон ячеек, содержащих значения, которые вы хотите проверить.
- criteria_range1 — это диапазон, который вы хотите проверить на соответствие критерию1.
- criteria1 — это условие, которому должен удовлетворять диапазон_критерия1.
- criteria_range2, criteria2 и т. д. — дополнительные диапазоны и критерии для проверки (при необходимости).
Как видно из синтаксиса, вы можете добавить столько критериев, сколько вам нужно.
Однако само собой разумеется, что в функции должен быть как минимум 1 диапазон критериев и параметр критерия.
Почему мы используем SUMIFS вместо SUMIF для суммирования значений ячеек между двумя датами?
Основное различие между функциями SUMIF (СУММЕСЛИ) и SUMIFS (СУММЕСЛИМН) заключается в том, что СУММЕСЛИ позволяет указать только одно условие. СУММЕСЛИМН, с другой стороны, позволяет указать более одного условия.
Синтаксис для Google Таблиц SUMIFS с датой
Поскольку мы хотим учитывать значения ячеек между парой дат (скажем, date1 и date2), нам необходимо указать два условия:
- Дата должна быть после указанной даты1
- Дата должна быть раньше указанной даты2
Таким образом, синтаксис функции SUMIFS для нашей ситуации можно сузить до следующего:
=SUMIFS(sum_range,criteria_range1,”>date1”, criteria_range2,”<date2”)
Использование SUMIFS для суммирования значений ячеек между двумя датами в Google Таблицах
Рассмотрим следующий набор данных, содержащий даты и количество проданных единиц (гипотетического продукта).
=SUMIFS(B2:B10,C2:C10,”>”&E4,C2:C10,”<”&F4)
Вот результат, который мы получаем, применяя приведенную выше формулу к нашим данным:
Использование СУММЕСЛИМН для суммирования значений ячеек между заданной датой и текущей датой
Давайте рассмотрим особый случай, когда мы хотим найти общее количество проданных единиц, начиная с заданной даты до текущей даты (сегодня).
Для этого вы можете использовать функцию DATE (ДАТА), например TODAY (СЕГОДНЯ), в параметрах критериев следующим образом:
=SUMIFS(B2:B10,C2:C10,”>”&E4,C2:C10,”<”&TODAY())
Функция TODAY просто возвращает текущую дату при открытии таблицы Google. Итак, вот что вы получите, если откроете лист 18.07.2021.
Использование СУММЕСЛИМН для суммирования значений ячеек с заданной даты до конца месяца
Еще одна полезная функция DATE — это функция EOMONTH, которая возвращает последний день месяца для заданной даты.
Давайте посмотрим на другой частный случай, когда мы хотим найти общее количество проданных единиц, начиная с заданной даты до конца этого месяца. В этом случае вы можете использовать функцию EOMONTH следующим образом:
=SUMIFS(B2:B10,C2:C10,”>=”&E4,C2:C10,”<”&EOMONTH(E4,0)
Первый параметр EOMONTH указывает дату, для которой вы хотите последний день месяца. Второй параметр указывает количество месяцев после заданной даты, для которого нам нужен последний день месяца.
Поскольку нам нужен последний день того же месяца, мы указали второй параметр функции EOMONTH как 0.
Вот результат, который мы получаем, когда эта формула применяется к данным:
Использование СУММЕСЛИМН (SUMIFS) для суммирования значений ячеек за текущий месяц
Наконец, давайте рассмотрим случай, когда мы хотим найти сумму значений ячеек за весь текущий месяц. Это потребует использования функций TODAY и EOMONTH в параметрах критерия SUMIFS.
Нам нужно будет найти как первый день, так и последний день текущего месяца, потому что нам нужны оба из них в параметрах критериев нашей функции СУММЕСЛИМН.
Чтобы найти первый день текущего месяца, воспользуемся формулой:
=EOMONTH(TODAY(),-1)+1
Здесь первым параметром функции EOMONTH является функция TODAY, которая возвращает текущую дату. Второй параметр указан как -1. Таким образом, EOMONTH (TODAY (), — 1) даст нам последний день месяца, который выпадает ровно за месяц до TODAY. Другими словами, последний день прошлого месяца.
Итак, первый день этого месяца, очевидно, выпадает на следующий день после этого, поэтому мы просто добавляем 1 к дате, возвращаемой этой функцией.
Теперь нам нужно найти последний день текущего месяца. Для этого воспользуемся формулой:
=EOMONTH(TODAY(),0)
Теперь мы можем объединить обе эти формулы, чтобы получить сумму значений ячеек между первым и последним днями текущего месяца, как показано ниже:
=SUMIFS(B2:B10,C2:C10,”>=”& EOMONTH(TODAY(),-1)+1,C2:C10,”<=”& EOMONTH(TODAY(),0))
Вот результат, который мы получаем, когда формулу применяем к выборке данных:
Поэтому, если бы мы хотели найти сумму проданных единиц за текущий месяц только для ковров, мы бы записали формулу как:
=SUMIFS(B2:B10,C2:C10,”>=”& EOMONTH(TODAY(),-1)+1,C2:C10,”<=”& EOMONTH(TODAY(),0),A2:A10,”Carpet”)
