rezumat
Pentru a însuma lunar în coloane, puteți utiliza funcția SUMIFS împreună cu funcția EOMONTH. În exemplul prezentat, formula din G5 este:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Această formulă utilizează trei intervale denumite: „sumă” (D5: D15), „client” (B5: B15) și „dată” (C5: C15).
Explicaţie
Funcția SUMIFS este concepută pentru a suma valorilor într-un interval bazat pe unul sau mai multe criterii. În acest caz, avem nevoie de trei criterii:
- Numele clientului = clientul din coloana F
- Data> = prima lună (de la data în rândul 4)
- Data <= sfârșitul lunii (de la data din rândul 4)
Punct cheie: numele lunilor din G4: I4 sunt de fapt date valide, formatate cu formatul numeric personalizat „mmm”. Acest lucru ne permite să folosim valorile datei din G4: I4 direct pentru criteriile # 2 și # 3 de mai sus.
Înapoi la SUMIFS, prima pereche interval / criterii este utilizată pentru a verifica numele clientului:
client,$F5
Observația F5 este o referință mixtă, cu coloana blocată. Acest lucru permite rândul să se schimbe pe măsură ce formula este copiată în tabel, dar numele clientului provine întotdeauna din coloana F.
A doua pereche interval / criterii este utilizată pentru a verifica datele în raport cu prima lună:
date,">="&G$4
După cum s-a menționat mai sus, valorile din G4: I4 sunt de fapt date: 1 ianuarie 2019, 1 februarie 2019 și 1 martie 2019. Deci, acest criteriu verifică pur și simplu datele mai mari decât datele din prima lună din rândul 4. Observați că aceasta este o altă referință mixtă, de data aceasta cu rândul blocat. Aceasta permite coloanei să se schimbe pe măsură ce formula este copiată pe masă, dar menține numărul rândului fix. Concatenarea cu un ampersand (&) este necesară atunci când se construiesc criterii care utilizează un operator logic și o valoare dintr-o altă celulă.
A treia pereche interval / criterii este utilizată pentru a verifica datele față de ultima zi a lunii:
date,"<="&EOMONTH(G$4,0)
Pentru a obține ultima zi a fiecărei luni, folosim funcția EOMONTH la data din antetul coloanei din rândul 4. Cu zero prevăzut pentru argumentul luni, EOMONTH returnează ultima zi a aceleiași luni. Din nou, referința la G4 este amestecată pentru a împiedica schimbarea rândului.
Soluție pentru tabelul pivot
Un tabel pivot ar fi o soluție excelentă pentru această problemă, deoarece se poate grupa automat pe lună fără niciun fel de formule. Pentru o comparație side-by-side a formulelor față de tabelele pivot, consultați acest videoclip: De ce tabelele pivot.
