Generička formula
=SUMPRODUCT((MONTH(dates)=month)*amounts)
Sažetak
Za sumiranje podataka po mjesecima, zanemarujući godinu, možete upotrijebiti formulu koja se temelji na funkcijama SUMPRODUCT i MONTH. U prikazanom primjeru formula u H6 je:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
Rezultat je ukupna prodaja u ožujku, ne računajući godinu.
Obrazloženje
Ovaj skup podataka sadrži preko 2900 zapisa, a gornja formula koristi dva imenovana raspona:
dates = B5:B2932 amounts = E5:E2932
Unutar funkcije SUMPRODUCT, funkcija MONTH koristi se za izdvajanje broja mjeseca za svaki datum u skupu podataka i usporedbu s brojem 3:
(MONTH(dates)=3)
Ako pretpostavimo da je mali skup podataka s po 3 datuma u siječnju, veljači i ožujku (tim redoslijedom), rezultat bi bio niz koji sadrži devet ovakvih brojeva:
(1;1;1;2;2;2;3;3;3)
pri čemu je svaki broj "broj mjeseca" za datum. Kada se vrijednosti usporede s 3, rezultat je niz poput ovog:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE)
Taj se niz zatim pomnoži s vrijednostima iznosa povezanim sa svakim datumom ožujka. Ako pretpostavimo da je svih devet iznosa jednako 100, operacija izgleda ovako:
(0;0;0;0;0;0;1;1;1) * (100;100;100;100;100;100;100;100;100)
Primijetite da matematička operacija mijenja TRUE FALSE vrijednosti u jedinice i nule. Nakon množenja, imamo jedan niz u SUMPRODUCT:
=SUMPRODUCT((0;0;0;0;0;0;100;100;100))
Imajte na umu da su jedini preživjeli iznosi povezani s ožujkom, a ostali su nula.
Konačno, SUMPRODUCT vraća zbroj svih stavki - 300 u skraćenom primjeru gore i 25 521 na snimci zaslona sa stvarnim podacima.
Broji prema mjesecu zanemarujući godinu
Da biste dobili brojanje po mjesecima zanemarujući godinu, možete koristiti SUMPRODUCT ovako:
=SUMPRODUCT(--(MONTH(dates)=3))
Prosjek za mjesec zanemarujući godinu
Da biste izračunali i prosječili mjesec zanemarujući godinu, kombinirate dvije gore navedene formule SUMPRODUCT ovako:
=SUMPRODUCT((MONTH(dates)=3)*amounts)/SUMPRODUCT(--(MONTH(dates)=3))
