Загальна формула
=SUMPRODUCT((MONTH(dates)=month)*amounts)
Резюме
Для підсумовування даних за місяцями, ігноруючи рік, ви можете використовувати формулу на основі функцій SUMPRODUCT та MONTH. У наведеному прикладі формула в H6 така:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
Результат - загальна сума всіх продажів у березні, ігноруючи рік.
Пояснення
Цей набір даних містить понад 2900 записів, і формула вище використовує два іменовані діапазони:
dates = B5:B2932 amounts = E5:E2932
Усередині функції SUMPRODUCT функція MONTH використовується для вилучення номера місяця для кожної дати в наборі даних та порівняння з номером 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))
Зверніть увагу, що лише збережені суми пов’язані з березнем, решта - нульові.
Нарешті, SUMPRODUCT повертає суму всіх елементів - 300 у скороченому прикладі вище та 25 521 на скріншоті з фактичними даними.
Підрахувати за місяцем, ігноруючи рік
Щоб отримати підрахунок за місяцем, ігноруючи рік, ви можете використовувати SUMPRODUCT так:
=SUMPRODUCT(--(MONTH(dates)=3))
Середнє значення за місяцем, ігноруючи рік
Для розрахунку та середнього значення за місяцем, ігноруючи рік, ви поєднуєте дві формули SUMPRODUCT вище, як це:
=SUMPRODUCT((MONTH(dates)=3)*amounts)/SUMPRODUCT(--(MONTH(dates)=3))
