Všeobecný vzorec
=SUMPRODUCT((MONTH(dates)=month)*amounts)
Zhrnutie
Ak chcete zhrnúť údaje podľa mesiacov, rok ignorujete, môžete použiť vzorec založený na funkciách SUMPRODUCT a MONTH. V zobrazenom príklade je vzorec v H6:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
Výsledkom je súčet všetkých predajov v marci, rok sa ignoruje.
Vysvetlenie
Táto množina údajov obsahuje viac ako 2 900 záznamov a vyššie uvedený vzorec používa dva pomenované rozsahy:
dates = B5:B2932 amounts = E5:E2932
Vo vnútri funkcie SUMPRODUCT sa funkcia MONTH používa na extrahovanie čísla mesiaca pre každý dátum v množine údajov a na porovnanie s číslom 3:
(MONTH(dates)=3)
Ak predpokladáme malú množinu údajov obsahujúcu 3 dátumy každý v januári, februári a marci (v tomto poradí), výsledkom by bolo pole obsahujúce deväť čísel, ako je toto:
(1;1;1;2;2;2;3;3;3)
kde každé číslo je „číslo mesiaca“ pre dátum. Keď sa hodnoty porovnajú s 3, výsledkom bude pole ako je toto:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE)
Toto pole sa potom vynásobí hodnotami čiastok spojených s každým dátumom marca. Ak predpokladáme, že všetkých deväť súm sa rovná 100, operácia vyzerá takto:
(0;0;0;0;0;0;1;1;1) * (100;100;100;100;100;100;100;100;100)
Všimnite si, že matematická operácia zmení hodnoty TRUE FALSE na jednotky a nuly. Po násobení máme v SUMPRODUCT jediné pole:
=SUMPRODUCT((0;0;0;0;0;0;100;100;100))
Upozorňujeme, že jediné prežívajúce sumy sú spojené s marcom, zvyšok je nula.
Nakoniec program SUMPRODUCT vráti súčet všetkých položiek - 300 v skrátenom príklade vyššie a 25 521 na snímke obrazovky so skutočnými údajmi.
Počítajte po mesiacoch, rok ignorujte
Ak chcete získať počet za mesiac ignorujúci rok, môžete použiť SUMPRODUCT takto:
=SUMPRODUCT(--(MONTH(dates)=3))
Priemer za mesiac ignorujúci rok
Ak chcete vypočítať a spriemerovať mesiac bez ohľadu na rok, skombinujete dva vyššie uvedené vzorce SUMPRODUCT takto:
=SUMPRODUCT((MONTH(dates)=3)*amounts)/SUMPRODUCT(--(MONTH(dates)=3))
