Obecný vzorec
=SUMPRODUCT((MONTH(dates)=month)*amounts)
souhrn
Chcete-li shrnout data podle měsíce, ignorujeme rok, můžete použít vzorec založený na funkcích SUMPRODUCT a MONTH. V zobrazeném příkladu je vzorec v H6:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
Výsledkem je celkový součet všech prodejů v březnu, rok ignorujeme.
Vysvětlení
Tato datová sada obsahuje více než 2900 záznamů a výše uvedený vzorec používá dva pojmenované rozsahy:
dates = B5:B2932 amounts = E5:E2932
Uvnitř funkce SUMPRODUCT se funkce MĚSÍC používá k extrakci čísla měsíce pro každé datum v datové sadě a porovnání s číslem 3:
(MONTH(dates)=3)
Pokud předpokládáme malou sadu dat se 3 daty v lednu, únoru a březnu (v tomto pořadí), výsledkem by bylo pole obsahující devět čísel, jako je tato:
(1;1;1;2;2;2;3;3;3)
kde každé číslo je „číslo měsíce“ pro datum. Když jsou hodnoty porovnány s 3, výsledkem je pole jako toto:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE)
Toto pole se poté vynásobí hodnotami množství spojenými s každým březnovým datem. Pokud předpokládáme, že všech devět částek se rovná 100, operace vypadá takto:
(0;0;0;0;0;0;1;1;1) * (100;100;100;100;100;100;100;100;100)
Všimněte si, že matematická operace změní hodnoty TRUE FALSE na jedničky a nuly. Po násobení máme v SUMPRODUCT jediné pole:
=SUMPRODUCT((0;0;0;0;0;0;100;100;100))
Všimněte si, že jediné přežívající částky jsou spojeny s březnem, zbytek je nula.
Nakonec SUMPRODUCT vrací součet všech položek - 300 ve zkráceném příkladu výše a 25 521 na snímku obrazovky se skutečnými daty.
Počítat měsíc ignorovat rok
Chcete-li získat počet za měsíc ignorující rok, můžete použít SUMPRODUCT takto:
=SUMPRODUCT(--(MONTH(dates)=3))
Průměr za měsíc ignoruje rok
Chcete-li vypočítat a průměrovat podle měsíce s ignorováním roku, zkombinujte výše uvedené dva vzorce SUMPRODUCT takto:
=SUMPRODUCT((MONTH(dates)=3)*amounts)/SUMPRODUCT(--(MONTH(dates)=3))
