Prenesite primer delovnega zvezka
Ta vadnica bo pokazala, kako uporabiti funkcijo SUMIFS za seštevanje podatkov, ki ustrezajo določenim mesecem v Excelu in Google Preglednicah.
Vsota za mesec
Najprej bomo pokazali, kako sešteti podatke, ki ustrezajo datumom, ki spadajo v določen mesec in leto.
Funkcijo SUMIFS skupaj s funkcijami DATE, YEAR, MONTH in EOMONTH lahko uporabimo za seštevanje Število prodaj znotraj vsakega Mesec.
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (LETO (E3), MESEC (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Za izdelavo zgornje formule začnemo z določanjem časovnega obdobja za vsak mesec. Funkcijo DATE uporabljamo za določanje prvega dne v mesecu (npr. 5/1/2021). To lahko storimo tako, da datum "formuliramo trdo" v formulo:
| 1 | "> =" & DATE (2021,5,1) |
Ali še bolje, lahko merila prilagodimo tako, da se sklicujemo na datum v celici E3, izvlečemo leto in mesec datuma (in dan nastavimo na 1), kot je prikazano v zgornjem primeru:
| 1 | "> =" & DATUM (LETO (E3), MESEC (E3), 1) |
Za določitev zadnjega dne v mesecu lahko uporabimo funkcijo EOMONTH:
| 1 | "<=" & EOMONTH (E3,0) |
Če združimo vsa ta merila, lahko zapišemo naslednjo formulo SUMIFS:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (LETO (E3), MESEC (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Zaklepanje referenc celic
Za lažje branje formul smo prikazali formule brez zaklenjenih sklicev na celice:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (LETO (E3), MESEC (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Toda te formule ne bodo delovale pravilno, če jih kopirate in prilepite drugam v datoteko. Namesto tega uporabite zaklenjene sklice na celice, kot je ta:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" & DATE (LETO (E3), MESEC (E3), 1), $ B $ 3: $ B $ 9, "<=" & EOMONTH (E3,0)) |
Če želite izvedeti več, preberite naš članek o zaklepanju referenc celic.
Oblikovanje mesečnih vrednosti
V tem primeru smo v stolpcu E. našteli mesece. Te mesečne vrednosti so dejansko datumi, oblikovani tako, da dan izpustijo z oblikovanjem številk po meri.
Podatkovni format po meri je "mmm llll" za prikaz maja 2022.
Vsota po mesecih v več letih
Zgornji primer je povzel podatke z datumi, ki so padli v določenem mesecu in letu. Namesto tega lahko s funkcijo SUMPRODUCT seštejete podatke z datumi, ki so v enem mesecu v katerem koli letu.
| 1 | = PODROČJE (C3: C8,-(MESEC (B3: B8) = MESEC (G3))) |
V tem primeru uporabljamo funkcijo SUMPRODUCT za izvajanje zapletenih izračunov "vsote, če". Pojdimo skozi formulo.
To je naša zadnja formula:
| 1 | = PODROČJE (C3: C8,-(MESEC (B3: B8) = MESEC (G3))) |
Prvič, funkcija SUMPRODUCT navaja Število prodaj za vsakogar Datum prodaje in nato primerja vsak mesec Datum prodaje proti navedenemu Mesec, vrne TRUE, če se meseci ujemajo, ali FALSE, če se ne ujemajo:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34},-({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})) |
Nato dvojne črtice (-) pretvorijo vrednosti TRUE in FALSE v 1s in 0s:
| 1 | = PODROČJE ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
Funkcija SUMPRODUCT nato pomnoži vsak par vnosov v matrikah:
| 1 | = PODROČJE ({30; 0; 51; 0; 17; 0}) |
Končno se seštejejo številke v matriki:
| 1 | =98 |
Več podrobnosti o uporabi logičnih stavkov in ukaza "-" v funkciji SUMPRODUCT najdete tukaj
Vsota za mesec v Google Preglednicah
Te formule delujejo popolnoma enako v Google Preglednicah kot v Excelu.
