Üldine valem
=SUMPRODUCT((MONTH(dates)=month)*amounts)
Kokkuvõte
Andmete kokkuvõtmiseks kuude kaupa, eirates aastat, võite kasutada valemit, mis põhineb funktsioonidel SUMPRODUCT ja MONTH. Näidatud näites on H6 valem:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
Tulemuseks on kogu märtsis toimunud müük, arvestamata aastat.
Selgitus
See andmekogum sisaldab üle 2900 kirje ja ülaltoodud valem kasutab kahte nimega vahemikku:
dates = B5:B2932 amounts = E5:E2932
Funktsiooni SUMPRODUCT sees kasutatakse funktsiooni MONTH, et eraldada andmekogumi iga kuupäeva kuunumber ja võrrelda seda arvuga 3:
(MONTH(dates)=3)
Kui eeldame, et väike andmekogum loetleb jaanuaris, veebruaris ja märtsis 3 kuupäeva (selles järjekorras), oleks tulemuseks massiiv, mis sisaldab üheksat sellist numbrit:
(1;1;1;2;2;2;3;3;3)
kus iga number on kuupäeva "kuu number". Kui väärtusi võrrelda 3-ga, on tulemuseks selline massiiv:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE)
Seejärel korrutatakse see massiiv iga märtsi kuupäevaga seotud summa väärtustega. Kui eeldame, et kõik üheksa summat on võrdsed 100-ga, näeb operatsioon välja järgmine:
(0;0;0;0;0;0;1;1;1) * (100;100;100;100;100;100;100;100;100)
Pange tähele, et matemaatikaoperatsioon muudab TRUE FALSE väärtused üheks ja nulliks. Pärast korrutamist on meil SUMPRODUCTis üks massiiv:
=SUMPRODUCT((0;0;0;0;0;0;100;100;100))
Pange tähele, et ainsad säilinud summad on seotud märtsiga, ülejäänud on null.
Lõpuks tagastab SUMPRODUCT kõigi üksuste summa - 300 ülaltoodud lühendatud näites ja 25 521 ekraanipildil koos tegelike andmetega.
Lugege kuu kaupa, eirates aastat
Aasta eiramise korral kuude kaupa lugemiseks võite kasutada SUMPRODUCT-i järgmiselt:
=SUMPRODUCT(--(MONTH(dates)=3))
Keskmine kuu lõikes, ignoreerides aastat
Aasta ignoreerimisel ja kuu lõikes keskmise arvutamiseks ühendage kaks ülaltoodud SUMPRODUCT-valemit järgmiselt:
=SUMPRODUCT((MONTH(dates)=3)*amounts)/SUMPRODUCT(--(MONTH(dates)=3))
