Vispārēja formula
=SUMPRODUCT((MONTH(dates)=month)*amounts)
Kopsavilkums
Apkopojot datus pa mēnešiem, neņemot vērā gadu, varat izmantot formulu, kuras pamatā ir funkcijas SUMPRODUCT un MONTH. Parādītajā piemērā formula H6 ir:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
Rezultāts ir visu marta pārdošanas apjomu kopsumma, neņemot vērā gadu.
Paskaidrojums
Šajā datu kopā ir vairāk nekā 2900 ierakstu, un iepriekšminētajā formulā tiek izmantoti divi nosauktie diapazoni:
dates = B5:B2932 amounts = E5:E2932
Funkcijas SUMPRODUCT iekšpusē funkcija MONTH tiek izmantota, lai mēneša numuru izgūtu katram datu kopas datumam un salīdzinātu to ar skaitli 3:
(MONTH(dates)=3)
Ja mēs pieņemam nelielu datu kopu, kurā katrs ir 3 datumi janvārī, februārī un martā (tādā secībā), rezultāts būtu masīvs, kurā būtu deviņi šādi skaitļi:
(1;1;1;2;2;2;3;3;3)
kur katrs skaitlis ir datuma "mēneša numurs". Salīdzinot vērtības ar 3, rezultāts ir šāds masīvs:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE)
Pēc tam šis masīvs tiek reizināts ar summas vērtībām, kas saistītas ar katru marta datumu. Ja pieņemam, ka visas deviņas summas ir vienādas ar 100, darbība izskatās šādi:
(0;0;0;0;0;0;1;1;1) * (100;100;100;100;100;100;100;100;100)
Ievērojiet, ka matemātikas operācija TRUE FALSE vērtības maina uz vienām un nullēm. Pēc reizināšanas mums SUMPRODUCT ir viens masīvs:
=SUMPRODUCT((0;0;0;0;0;0;100;100;100))
Ņemiet vērā, ka atlikušās summas ir saistītas tikai ar martu, pārējās ir nulle.
Visbeidzot, SUMPRODUCT atgriež visu vienumu summu - 300 iepriekš minētajā saīsinātajā piemērā un 25 521 ekrānuzņēmumā ar faktiskajiem datiem.
Skaitīt pa mēnešiem, neņemot vērā gadu
Lai iegūtu skaitīšanu pa mēnešiem, neņemot vērā gadu, varat izmantot SUMPRODUCT šādi:
=SUMPRODUCT(--(MONTH(dates)=3))
Vidēji pa mēnešiem, neņemot vērā gadu
Lai aprēķinātu un vidēji mēnesī ignorētu gadu, abas iepriekš minētās SUMPRODUCT formulas apvienojat šādi:
=SUMPRODUCT((MONTH(dates)=3)*amounts)/SUMPRODUCT(--(MONTH(dates)=3))
