De multe ori, vrem să calculăm unele valori pe lună. De exemplu, cât de multe vânzări s-au realizat într-o lună specială. Ei bine, acest lucru se poate face cu ușurință folosind tabele pivot, dar dacă încercați să aveți un raport dinamic, putem folosi o formulă SUMPRODUCT sau SUMIFS pentru a suma prin lună.
Să începem cu soluția SUMPRODUCT.
Iată formula generică pentru a obține suma lunară în Excel
= SUMPRODUCT (sum_range, - (TEXT (date_range, "MMM") = month_text))
Suma_interval : Este intervalul pe care doriți să-l însumați pe lună.
Interval de date : Este intervalul de date pe care îl veți căuta luni întregi.
Text_lună: Este luna în format text, a cărei sumă doriți să sumați valorile.
Acum să vedem un exemplu:
Exemplu: Suma valorilor în funcție de lună în Excel
Aici avem o anumită valoare asociată cu datele. Aceste date sunt lunile ianuarie, februarie și martie ale anului 2019.
După cum puteți vedea în imaginea de mai sus, toate datele sunt pentru anul 2019. Acum trebuie doar să sumăm valorile în E2: G2 pe luni în E1: G1.
Acum, pentru a suma valorilor în funcție de luni, scrieți această formulă în E2:
= SUMPRODUCT (B2: B9, - (TEXT (A2: A9, "MMM") = E1)))
Dacă doriți să îl copiați în celulele adiacente, utilizați referințe absolute sau intervale denumite ca și voi în imagine.
Aceasta ne oferă suma exactă a fiecărei luni.
Cum functioneaza?
Începând din interior, să ne uităm la TEXT (A2: A9, „MMM”) parte. Aici funcția TEXT extrage lună din fiecare dată din intervalul A2: A9 în format text într-o matrice. Se transformă în formulă în = SUMPRODUCT (B2: B9, - ({"Jan"; "Jan"; "Feb"; "Jan"; "Feb"; "Mar"; "Jan"; "Feb"} = E1) )
Apoi, TEXT (A2: A9, „MMM”)= E1: Aici fiecare lună în matrice este comparată cu textul din E1. Deoarece E1 conține „Jan”, fiecare „Jan” din matrice este convertit în TRUE și altele în FALSE. Aceasta traduce formula la = SUMPRODUCT ($ B $ 2: $ B $ 9, - {TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE})
În continuare - (TEXT (A2: A9, "MMM") = E1), convertește FALS ADEVĂRAT în valori binare 1 și 0. Formula se traduce prin = SUMPRODUCT ($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}).
In cele din urma SUMPRODUCT($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}): funcția SUMPRODUCT înmulțește valorile corespunzătoare în $ B $ 2: $ B $ 9 la matrice {1; 1; 0; 1; 0; 0; 1; 0} și le adaugă. Prin urmare, obținem suma în valoare ca 20052 în E1.
SUMA DACĂ Luni din an diferit
În exemplul de mai sus, toate datele au fost din același an. Dacă ar fi din ani diferiți? Formula de mai sus va însuma valorile pe lună, indiferent de an. De exemplu, ianuarie 2018 și ianuarie 2019 vor fi adăugate, dacă folosim formula de mai sus. Ceea ce este greșit în majoritatea cazurilor.
Acest lucru se va întâmpla deoarece nu avem niciun criteriu pentru anul din exemplul de mai sus. Dacă adăugăm și criterii de an, va funcționa.
Formula generică pentru a obține suma în funcție de lună și an în Excel
= SUMPRODUCT (sum_range, - (TEXT (data_range, "MMM") = month_text), - (TEXT (date_range, "aaaa") = TEXT (anul, 0)))
Aici, am adăugat încă un criteriu care verifică anul. Orice altceva este la fel.
Să rezolvăm exemplul de mai sus, scrieți această formulă în celula E1 pentru a obține suma lui Jan în anul 2017.
= SUMPRODUCT (B2: B9, - (TEXT (A2: A9, "MMM") = E1), - (TEXT (A2: A9, "aaaa") = TEXT (D2,0)))
Înainte de a copia în celulele de mai jos, utilizați intervale denumite sau referințe absolute. În imagine, am folosit intervale denumite pentru copiere în celulele adiacente.
Acum putem vedea suma valorii și pe luni și ani.
Cum functioneazã?
Prima parte a formulei este aceeași cu exemplul anterior. Să înțelegem partea suplimentară care adaugă criteriile anului.
- (TEXT (A2: A9, "aaaa") = TEXT (D2,0)): TEXT (A2: A9, "aaaa") convertește data în A2: A9 ca ani în format text într-o matrice. {"2018"; "2019"; "2017"; "2017"; "2019"; "2017"; "2019"; "2017"}.
De cele mai multe ori, anul este scris în format numeric. Pentru a compara un număr cu textul noi, am convertit anul int text folosind TEXT (D2,0). Apoi am comparat acest an text cu o serie de ani ca TEXT (A2: A9, "aaaa") = TEXT (D2,0). Aceasta returnează o serie de adevărat-fals {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}. Apoi am convertit adevăratul fals în număr folosind - operator. Acest lucru ne dă {0; 0; 1; 1; 0; 1; 0; 1}.
Deci, în cele din urmă, formula va fi tradusă în = SUMPRODUCT (B2: B9, {1; 1; 0; 1; 0; 0; 1; 0}, {0; 0; 1; 1; 0; 1; 0; 1 }). Unde prima matrice este valori. Următoarea este potrivită lună și a treia este anul. În cele din urmă, obținem suma valorilor noastre ca 2160.
Folosind funcția SUMIFS pentru a suma prin lună
Formula generică
= SUMIFS (sum_range, date_range, ”> =” & startdate, date_range, ”<=” & EOMONTH (start_date, 0))
Aici,Suma_interval : Este intervalul pe care doriți să-l însumați pe lună.
Interval de date : Este intervalul de date pe care îl veți căuta luni întregi.
Data de început : Este data de începere de la care doriți să însumați. Pentru acest exemplu, va fi 1 din luna dată.
Exemplu: Suma valorilor în funcție de lună în Excel
Aici avem o valoare asociată cu datele. Aceste date sunt lunile ianuarie, februarie și martie ale anului 2019.
Trebuie doar să însumăm aceste valori până în fiecare lună. Acum era ușor dacă aveam luni și ani separat. Dar ele nu sunt. Nu putem folosi nicio coloană de ajutor aici.
Deci, pentru a pregăti raportul, am pregătit un format de raport care conține luna și suma valorilor. În coloana lună, de fapt, am o dată de începere a lunii. Pentru a vedea doar luna, selectați data de începere și apăsați CTRL + 1.
În format personalizat, scrieți „mmm”.
Acum avem datele noastre pregătite. Să însumăm valorile pe lună.
Scrieți această formulă în E3 pentru a însuma pe lună.
= SUME (B3: B10, A3: A10, "> =" & D3, A3: A10, "<=" & EOMONTH (D3,0))
Utilizați referințe absolute sau intervale denumite înainte de a copia formula.
Deci, în cele din urmă am obținut rezultatul.
Deci, cum funcționează?
După cum știm că funcția SUMIFS poate însuma valori pe criterii multiple.
În exemplul de mai sus, primul criteriu este suma tuturor valorilor din B3: B10 unde, data în A3: A10 este mai mare sau egală cu data din D3. D3 conține 1-ianuarie. Și asta se traduce.
= SUME (B3: B10, A3: A10, "> =" & „1-ianuarie-2019”, A3: A10, "<=" EOMONTH (D3,0))
Criteriile următoare sunt suma numai dacă Data în A3: A10 este mai mic sau egal cu EOMONTH (D3,0). Funcția EOMONTH returnează doar numărul de serie al ultimei date a lunii furnizate. În cele din urmă, formula se traduce și ea.
= SUME (B3: B10, A3: A10, "> = 1-ianuarie-2019”, A3: A10, „<= 31-ianuarie-2019”)
Prin urmare, obținem suma pe lună în Excel.
Beneficiul acestei metode este că puteți ajusta data de începere pentru însumarea valorilor.
Dacă datele dvs. au ani diferiți, este mai bine să utilizați tabele pivot. Tabelele pivot vă pot ajuta să separați datele în format anual, trimestrial și lunar.
Deci, băieți, așa puteți să sumați valorile pe lună. Ambele moduri au propriile lor specialități. Alegeți în ce mod doriți.
Dacă aveți întrebări referitoare la acest articol sau la orice alte întrebări legate de Excel și VBA, secțiunea de comentarii este deschisă pentru dvs.
Articole legate:
Cum se utilizează funcția SUMIF în Excel
SUME cu date în Excel
SUMIF cu celule care nu sunt goale
Cum se utilizează funcția SUMIFS în Excel
SUMIFE folosind logica AND-OR
Articole populare
50 Comandă rapidă Excel pentru a vă crește productivitatea: Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.
Cum se folosește tFuncția VLOOKUP în Excel: Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
Cum se utilizează funcția COUNTIF în Excel: Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel: Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.