Splošna formula
=SUMPRODUCT((MONTH(dates)=month)*amounts)
Povzetek
Za seštevanje podatkov po mesecih, ne upoštevajoč leta, lahko uporabite formulo, ki temelji na funkcijah SUMPRODUCT in MONTH. V prikazanem primeru je formula v H6:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
Rezultat je skupna celotna prodaja marca, ne glede na leto.
Pojasnilo
Ta nabor podatkov vsebuje več kot 2900 zapisov, zgornja formula pa uporablja dva imenovana obsega:
dates = B5:B2932 amounts = E5:E2932
Znotraj funkcije SUMPRODUCT se s funkcijo MONTH izvleče številka meseca za vsak datum v naboru podatkov in primerja s številko 3:
(MONTH(dates)=3)
Če predpostavimo majhen nabor podatkov, v katerem so navedeni trije datumi januarja, februarja in marca (v tem vrstnem redu), bi bil rezultat matrika, ki vsebuje devet takih števil:
(1;1;1;2;2;2;3;3;3)
kjer je vsaka številka "številka meseca" za datum. Ko primerjamo vrednosti s 3, je rezultat matrika, kot je ta:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE)
Nato se ta matrika pomnoži z vrednostmi zneska, povezanimi z vsakim datumom marca. Če predpostavimo, da je vseh devet zneskov enako 100, je operacija videti takole:
(0;0;0;0;0;0;1;1;1) * (100;100;100;100;100;100;100;100;100)
Upoštevajte, da matematična operacija spremeni TRUE FALSE vrednosti v enote in ničle. Po množenju imamo v SUMPRODUCT eno matriko:
=SUMPRODUCT((0;0;0;0;0;0;100;100;100))
Edini preživeli zneski so povezani z marcem, ostali pa nič.
Končno, SUMPRODUCT vrne vsoto vseh elementov - 300 v skrajšanem primeru zgoraj in 25.521 na posnetku zaslona z dejanskimi podatki.
Štetje po mesecu, ne upoštevajoč leta
Če želite dobiti štetje po mesecih, zanemarjajoč leto, lahko uporabite SUMPRODUCT tako:
=SUMPRODUCT(--(MONTH(dates)=3))
Povprečje glede na leto, zanemarjeno leto
Če želite izračunati in izračunati povprečje glede na leto po letu, združite zgornji formuli SUMPRODUCT tako:
=SUMPRODUCT((MONTH(dates)=3)*amounts)/SUMPRODUCT(--(MONTH(dates)=3))
