Descărcați exemplul de registru de lucru
Acest tutorial va arăta cum să utilizați funcția SUMIFS pentru a însuma datele corespunzătoare lunilor specifice în Excel și Foi de calcul Google.
Suma dacă pe lună
În primul rând, vom demonstra cum să însumăm datele corespunzătoare datelor care se încadrează într-o anumită lună și an.
Putem folosi funcția SUMIFS, împreună cu funcțiile DATE, AN, LUNĂ și EOMONTH pentru a însuma Număr de vânzări în cadrul fiecăruia Lună.
| 1 | = SUME (C3: C9, B3: B9, "> =" & DATA (AN (E3), LUNĂ (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Pentru a construi formula de mai sus, începem prin definirea intervalului de date pentru fiecare lună. Folosim funcția DATE pentru a defini prima zi a lunii (ex. 01.05.2021). Putem face acest lucru prin „hard-coding” data în formula:
| 1 | "> =" & DATA (2021,5,1) |
Sau, mai bine, putem simplifica criteriile făcând referire la o dată din celula E3, extragând anul și luna datei (și setând ziua la 1), așa cum se arată în exemplul de mai sus:
| 1 | "> =" & DATE (AN (E3), LUNĂ (E3), 1) |
Pentru a defini ultima zi a lunii, putem folosi funcția EOMONTH:
| 1 | „<=” & EOMONTH (E3,0) |
Punând împreună toate aceste criterii, putem scrie următoarea formulă SUMIFS:
| 1 | = SUME (C3: C9, B3: B9, "> =" & DATA (AN (E3), LUNĂ (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Blocarea referințelor celulei
Pentru a simplifica citirea formulelor, am arătat formulele fără referințe de celule blocate:
| 1 | = SUME (C3: C9, B3: B9, "> =" & DATA (AN (E3), LUNĂ (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Dar aceste formule nu vor funcționa corect atunci când sunt copiate și lipite în altă parte a fișierului. În schimb, ar trebui să utilizați referințe de celule blocate ca aceasta:
| 1 | = SUME ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" & DATE (AN (E3), LUNĂ (E3), 1), $ B $ 3: $ B $ 9, "<=" & EOMONTH (E3,0)) |
Citiți articolul nostru despre blocarea referințelor celulei pentru a afla mai multe.
Formatarea valorilor lunare
În acest exemplu, am enumerat luni în coloana E. Aceste valori lunare sunt de fapt date formatate pentru a omite ziua, utilizând formatarea numerelor personalizate.
Formatul de date personalizat este „mmm aaaa” pentru a fi afișat în mai 2022.
Suma pe lună pe mai mulți ani
Exemplul de mai sus a rezumat date cu date care au intrat într-o anumită lună și an. În schimb, puteți însuma date cu date care se încadrează într-o lună în orice an utilizând funcția SUMPRODUCT.
| 1 | = SUMPRODUCT (C3: C8, - (LUNĂ (B3: B8) = LUNĂ (G3))) |
În acest exemplu, folosim funcția SUMPRODUCT pentru a efectua calcule „sum dacă” complicate. Să trecem prin formulă.
Aceasta este formula noastră finală:
| 1 | = SUMPRODUCT (C3: C8, - (LUNĂ (B3: B8) = LUNĂ (G3))) |
În primul rând, funcția SUMPRODUCT listează Număr de vânzări pentru fiecare Data vânzării și apoi compară luna fiecăruia Data vânzării împotriva celor specificate Lună, returnând TRUE dacă lunile se potrivesc sau FALSE dacă nu:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34}, - ({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})) |
Apoi liniuțele duble (-) convertesc valorile TRUE și FALSE în 1s și 0s:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
Funcția SUMPRODUCT multiplică apoi fiecare pereche de intrări din tablouri:
| 1 | = SUMPRODUCT ({30; 0; 51; 0; 17; 0}) |
În cele din urmă, numerele din matrice sunt însumate:
| 1 | =98 |
Mai multe detalii despre utilizarea instrucțiunilor booleene și comanda „-” într-o funcție SUMPRODUCT pot fi găsite aici
Suma dacă lunar în Foi de calcul Google
Aceste formule funcționează exact la fel în Foi de calcul Google ca în Excel.
