Обща формула
=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))
