Formula generică
=SUMPRODUCT((MONTH(dates)=month)*amounts)
rezumat
Pentru a însuma datele pe lună, ignorând anul, puteți utiliza o formulă bazată pe funcțiile SUMPRODUCT și MONTH. În exemplul prezentat, formula din H6 este:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
Rezultatul este un total al tuturor vânzărilor din martie, ignorând anul.
Explicaţie
Acest set de date conține peste 2900 de înregistrări, iar formula de mai sus utilizează două intervale denumite:
dates = B5:B2932 amounts = E5:E2932
În interiorul funcției SUMPRODUCT, funcția LUNĂ este utilizată pentru a extrage numărul lunii pentru fiecare dată din setul de date și pentru a-l compara cu numărul 3:
(MONTH(dates)=3)
Dacă presupunem un mic set de date care conține câte 3 date în ianuarie, februarie și martie (în această ordine), rezultatul ar fi un tablou care conține nouă numere de acest fel:
(1;1;1;2;2;2;3;3;3)
unde fiecare număr este „numărul lunii” pentru o dată. Când valorile sunt comparate cu 3, rezultatul este un tablou de genul acesta:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE)
Această matrice este apoi înmulțită cu valorile sumelor asociate cu fiecare dată din martie. Dacă presupunem că toate cele nouă sume sunt egale cu 100, operația arată astfel:
(0;0;0;0;0;0;1;1;1) * (100;100;100;100;100;100;100;100;100)
Observați că operația matematică modifică valorile ADEVĂRATE FALSE în unii și zerouri. După multiplicare, avem o singură matrice în SUMPRODUCT:
=SUMPRODUCT((0;0;0;0;0;0;100;100;100))
Rețineți că singurele sume supraviețuitoare sunt asociate cu luna martie, restul sunt zero.
În cele din urmă, SUMPRODUCT returnează suma tuturor articolelor - 300 în exemplul prescurtat de mai sus și 25.521 în captura de ecran cu date reale.
Numărați lunar ignorând anul
Pentru a obține un număr pe lună, ignorând anul, puteți utiliza SUMPRODUCT astfel:
=SUMPRODUCT(--(MONTH(dates)=3))
Media lunii ignorând anul
Pentru a calcula și a calcula media lunii ignorând anul, combinați cele două formule SUMPRODUCT de mai sus astfel:
=SUMPRODUCT((MONTH(dates)=3)*amounts)/SUMPRODUCT(--(MONTH(dates)=3))
