Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodys, kaip naudoti funkciją SUMIFS, norint susumuoti duomenis, atitinkančius konkrečius mėnesius „Excel“ ir „Google“ skaičiuoklėse.
Suma, jei pagal mėnesį
Pirmiausia parodysime, kaip susumuoti duomenis, atitinkančius datas, kurios patenka į konkretų mėnesį ir metus.
Mes galime naudoti funkciją SUMIFS kartu su DATE, YEAR, MONTH ir EOMONTH funkcijomis, kad susumuotume Pardavimų skaičius kiekvieno viduje Mėnuo.
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (METAI (E3), MĖNESIS (E3), 1), B3: B9, "<=" ir EOMONTH (E3,0)) |
Norėdami sukurti aukščiau pateiktą formulę, pirmiausia nustatome kiekvieno mėnesio dienų seką. Mes naudojame funkciją DATE, kad nustatytume pirmąją mėnesio dieną (pvz., 2021-05-01). Tai galime padaryti „užkoduodami“ datą į formulę:
| 1 | "> =" & DATA (2021,5,1) |
Arba dar geriau, mes galime padaryti kriterijus lanksčius, nurodydami datą langelyje E3, ištraukdami datos metus ir mėnesį (ir nustatydami dieną lygią 1), kaip parodyta aukščiau pateiktame pavyzdyje:
| 1 | "> =" & DATA (METAI (E3), MĖNESIS (E3), 1) |
Norėdami apibrėžti paskutinę mėnesio dieną, galime naudoti funkciją EOMONTH:
| 1 | "<=" Ir EOMONTH (E3,0) |
Sudėję visus šiuos kriterijus, galime parašyti tokią SUMIFS formulę:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (METAI (E3), MĖNESIS (E3), 1), B3: B9, "<=" ir EOMONTH (E3,0)) |
Ląstelių nuorodų užrakinimas
Kad mūsų formules būtų lengviau skaityti, parodėme formules be užrakintų langelių nuorodų:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (METAI (E3), MĖNESIS (E3), 1), B3: B9, "<=" ir EOMONTH (E3,0)) |
Tačiau šios formulės neveiks tinkamai, kai bus nukopijuotos ir įklijuotos kitur jūsų faile. Vietoj to turėtumėte naudoti užrakintas langelių nuorodas:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" & DATE (YEAR (E3), MONTH (E3), 1), $ B $ 3: $ B $ 9, "<=" & EOMONTH (E3,0)) |
Norėdami sužinoti daugiau, perskaitykite mūsų straipsnį apie ląstelių nuorodų užrakinimą.
Mėnesio verčių formatavimas
Šiame pavyzdyje E stulpelyje nurodėme mėnesius. Šios mėnesio vertės iš tikrųjų yra datos, suformuotos taip, kad būtų praleista diena, naudojant pasirinktinį skaičių formatavimą.
Tinkintas duomenų formatas yra „mmmmmmmm“, kad būtų rodomas 2022 m. Gegužės mėn.
Suma pagal kelis mėnesius per kelis metus
Aukščiau pateiktas pavyzdys apibendrino duomenis su datomis, kurios pateko į konkretų mėnesį ir metus. Vietoj to, galite susumuoti duomenis su datomis, kurios patenka į mėnesį bet kuriais metais, naudodami funkciją SUMPRODUCT.
| 1 | = SUMPRODUKTAS (C3: C8,-(MĖNESIS (B3: B8) = MĖNESIS (G3))) |
Šiame pavyzdyje mes naudojame SUMPRODUCT funkciją sudėtingiems „sum if“ skaičiavimams atlikti. Eikime per formulę.
Tai yra mūsų galutinė formulė:
| 1 | = SUMPRODUKTAS (C3: C8,-(MĖNESIS (B3: B8) = MĖNESIS (G3))) |
Pirma, funkcijoje SUMPRODUCT pateikiamas sąrašas Pardavimų skaičius kiekvienam Pardavimo data ir tada lygina kiekvieno mėnesį Pardavimo data prieš nurodytą Mėnuo, grąžinama TIESA, jei mėnesiai sutampa, arba KLAIDA, jei neatitinka:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34},-({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})) |
Tada dvigubi brūkšneliai (-) konvertuoja TRUE ir FALSE reikšmes į 1 ir 0:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
Funkcija SUMPRODUCT tada padaugina kiekvieną masyvų įrašų porą:
| 1 | = SUMPRODUCT ({30; 0; 51; 0; 17; 0}) |
Galiausiai masyvo skaičiai yra sumuojami:
| 1 | =98 |
Daugiau informacijos apie „Boolean“ teiginių naudojimą ir komandą „-“ funkcijoje SUMPRODUCT rasite čia
Suma pagal mėnesį „Google“ skaičiuoklėse
Šios formulės „Google“ skaičiuoklėse veikia lygiai taip pat, kaip „Excel“.
