Yleinen kaava
=SUMPRODUCT((MONTH(dates)=month)*amounts)
Yhteenveto
Tietojen yhteenlaskeminen kuukausittain, huomioimatta vuosi, voit käyttää kaavaa, joka perustuu SUMPRODUCT- ja MONTH-funktioihin. Esitetyssä esimerkissä kaava H6: ssa on:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
Tuloksena on maaliskuun koko myynti, vuosi huomioimatta.
Selitys
Tämä tietojoukko sisältää yli 2900 tietuetta, ja yllä olevassa kaavassa käytetään kahta nimettyä aluetta:
dates = B5:B2932 amounts = E5:E2932
SUMPRODUCT-funktion sisällä MONTH-funktiota käytetään poimimaan tietojoukon jokaisen päivämäärän kuukauden numero ja vertaamaan sitä numeroon 3:
(MONTH(dates)=3)
Jos oletetaan pieni tietojoukko, jossa luetellaan 3 päivämäärää kussakin tammikuussa, helmikuussa ja maaliskuussa (tässä järjestyksessä), tulos olisi taulukko, joka sisältää yhdeksän tällaista lukua:
(1;1;1;2;2;2;3;3;3)
missä kukin numero on päivämäärän "kuukauden numero". Kun arvoja verrataan arvoon 3, tuloksena on tällainen taulukko:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE)
Tämä taulukko kerrotaan sitten kuhunkin maaliskuun päivämäärään liittyvillä arvoarvoilla. Jos oletamme, että kaikki yhdeksän summaa ovat yhtä suuret kuin 100, operaatio näyttää tältä:
(0;0;0;0;0;0;1;1;1) * (100;100;100;100;100;100;100;100;100)
Huomaa, että matemaattinen operaatio muuttaa TOSI EPÄTOSI -arvot yksiköiksi ja nolliksi. Kertomisen jälkeen meillä on yksi taulukko SUMPRODUCT: ssa:
=SUMPRODUCT((0;0;0;0;0;0;100;100;100))
Huomaa, että ainoat jäljellä olevat määrät liittyvät maaliskuussa, loput ovat nolla.
Lopuksi SUMPRODUCT palauttaa kaikkien kohteiden summan - 300 yllä olevassa lyhennetyssä esimerkissä ja 25 521 kuvakaappauksessa todellisten tietojen kanssa.
Laske kuukausittain huomiotta vuosi
Jos haluat laskea kuukausittain lukuun ottamatta vuotta, voit käyttää SUMPRODUCTia näin:
=SUMPRODUCT(--(MONTH(dates)=3))
Keskimääräinen kuukausittain huomioimatta vuosi
Voit laskea ja keskiarvon kuukausittain huomioimatta vuoden yhdistämällä kaksi yllä olevaa SUMPRODUCT-kaavaa seuraavasti:
=SUMPRODUCT((MONTH(dates)=3)*amounts)/SUMPRODUCT(--(MONTH(dates)=3))
