Общая формула
=SUMPRODUCT((MONTH(dates)=month)*amounts)
Резюме
Чтобы суммировать данные по месяцам, игнорируя год, вы можете использовать формулу, основанную на функциях СУММПРОИЗВ и МЕСЯЦ. В показанном примере формула H6 имеет следующий вид:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
В результате получается сумма всех продаж за март без учета года.
Объяснение
Этот набор данных содержит более 2900 записей, а в приведенной выше формуле используются два именованных диапазона:
dates = B5:B2932 amounts = E5:E2932
Внутри функции СУММПРОИЗВ функция МЕСЯЦ используется для извлечения номера месяца для каждой даты в наборе данных и сравнения его с числом 3:
(MONTH(dates)=3)
Если мы предположим, что небольшой набор данных содержит по 3 даты в январе, феврале и марте (в указанном порядке), результатом будет массив, содержащий девять чисел, подобных этому:
(1;1;1;2;2;2;3;3;3)
где каждое число - это «номер месяца» для даты. Когда значения сравниваются с 3, результатом является такой массив:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE)
Затем этот массив умножается на значения суммы, связанные с каждой датой марта. Если предположить, что все девять сумм равны 100, операция будет выглядеть так:
(0;0;0;0;0;0;1;1;1) * (100;100;100;100;100;100;100;100;100)
Обратите внимание, что математическая операция изменяет значения TRUE FALSE на единицы и нули. После умножения в SUMPRODUCT получается единственный массив:
=SUMPRODUCT((0;0;0;0;0;0;100;100;100))
Обратите внимание, что единственные оставшиеся суммы связаны с мартом, остальные равны нулю.
Наконец, СУММПРОИЗВ возвращает сумму всех элементов - 300 в приведенном выше сокращенном примере и 25 521 на снимке экрана с фактическими данными.
Считать по месяцам без учета года
Чтобы получить счет по месяцам без учета года, вы можете использовать СУММПРОИЗВ следующим образом:
=SUMPRODUCT(--(MONTH(dates)=3))
Среднее по месяцам без учета года
Чтобы вычислить и усреднить по месяцам без учета года, вы комбинируете две приведенные выше формулы СУММПРОИЗВ следующим образом:
=SUMPRODUCT((MONTH(dates)=3)*amounts)/SUMPRODUCT(--(MONTH(dates)=3))
