Formula generică
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
rezumat
Pentru a rezuma lunar, puteți utiliza o formulă bazată pe funcția SUMIFS, cu ajutorul funcției EOMONTH. În exemplul prezentat, formula din F4 este:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
Această formulă utilizează intervalele denumite „sumă” (C4: C9) și „dată” (B4: B9).
Explicaţie
Funcția SUMIFS poate însuma valori în intervale bazate pe mai multe criterii.
În acest caz, configurăm SUMIFS pentru a suma sumelor pe lună folosind două criterii: unul pentru a se potrivi cu date mai mari sau egale cu prima zi a lunii, unul pentru a se potrivi cu date mai mici sau egale cu ultima zi a lunii. Cu alte cuvinte, avem nevoie de o formulă de genul acesta:
=SUMIFS(amount,date,">="&"1/1/2016",date,"<="&"1/31/2016")
Sau alternativa mai antiglonț:
=SUMIFS(amount,date,">="&DATE(2016,1,1),date,"<="&DATE(2016,1,31))
Această versiune este mai robustă, deoarece datele sunt construite cu valori separate de an, lună și zi și nu sunt introduse ca text care trebuie interpretat de Excel.
În mod normal, aceasta este o problemă, deoarece dacă adăugați nume de luni ca text (adică „ianuarie”, „februarie”, „martie” etc.) în coloana E, trebuie să faceți probleme suplimentare pentru a crea date pe care le puteți utiliza pentru criterii . Cu toate acestea, în acest caz, folosim un truc simplu pentru a ușura lucrurile: în coloana E, în loc să tastați numele lunilor, adăugăm date efective (01.01.2016, 01.02.2016, 01.03.2016), apoi utilizați un format de dată personalizat („mmmm”) pentru a afișa numele lunii.
Acest lucru facilitează construirea criteriilor de care avem nevoie pentru SUMIF-uri. Pentru a potrivi date mai mari sau egale cu prima din lună, folosim:
">="&E4
Și pentru a potrivi date mai mici sau egale cu ultima zi a lunii, folosim acest lucru:
"<="&EOMONTH(E4,0)
Cu zero pentru luni, funcția EOMONTH returnează automat ultima zi a lunii oferită până la dată.
Concatenarea cu un ampersand (&) este necesară atunci când se construiesc criterii care utilizează un operator logic cu o valoare numerică.
Soluție pentru tabelul pivot
Un tabel pivot este o soluție excelentă atunci când trebuie să rezumați datele în funcție de an, lună, trimestru și așa mai departe, deoarece acestea vor face acest tip de grupare pentru dvs. fără nicio formulă. Pentru o comparație side-by-side a formulelor față de tabelele pivot, consultați acest videoclip: De ce tabelele pivot.
