Bendroji formulė
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
Santrauka
Apibendrinant pagal mėnesį, galite naudoti formulę, pagrįstą funkcija SUMIFS, naudodamiesi funkcija EOMONTH. Parodytame pavyzdyje formulė F4 yra:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
Šioje formulėje naudojami įvardyti diapazonai „suma“ (C4: C9) ir „data“ (B4: B9).
Paaiškinimas
Funkcija SUMIFS gali susumuoti reikšmes diapazonais, pagrįstais keliais kriterijais.
Tokiu atveju mes sukonfigūruojame sumas pagal mėnesį susumuoti pagal du kriterijus: vienas atitinka datas, didesnes ar lygias pirmajai mėnesio dienai, kitas - datas, mažesnes arba lygias paskutinei mėnesio dienai. Kitaip tariant, mums reikia tokios formulės:
=SUMIFS(amount,date,">="&"1/1/2016",date,"<="&"1/31/2016")
Arba labiau neperšaunama alternatyva:
=SUMIFS(amount,date,">="&DATE(2016,1,1),date,"<="&DATE(2016,1,31))
Ši versija yra patikimesnė, nes datos sudaromos su atskiromis metų, mėnesio ir dienos reikšmėmis, o ne įvedamos kaip tekstas, kurį turi interpretuoti „Excel“.
Paprastai tai yra skausmas, nes jei E stulpelyje pridedate mėnesių pavadinimus kaip tekstą (pvz., „Sausis“, „vasaris“, „kovas“ ir kt.), Turite susidurti su papildomomis problemomis, kad sukurtumėte datas, kurias galite naudoti pagal kriterijus . Tačiau šiuo atveju mes naudojame paprastą triuką, kad viskas būtų lengva: E skiltyje, užuot įrašę mėnesių pavadinimus, mes įtraukiame faktines datas (2016-01-01, 2016-02-01, 2016-03-01), tada naudokite pasirinktinį datos formatą ("mmmm"), kad būtų rodomi mėnesių pavadinimai.
Tai leidžia lengvai nustatyti mums reikalingus SUMIFS kriterijus. Norėdami suderinti datas, didesnes arba lygias mėnesio pirmajam, naudojame:
">="&E4
Norėdami suderinti datas, mažesnes ar lygias paskutinei mėnesio dienai, naudojame:
"<="&EOMONTH(E4,0)
Mėnesiams nulis, funkcija EOMONTH automatiškai grąžina paskutinę mėnesio dieną, nurodytą datos.
Sujungimas su ampersandu (&) yra būtinas kuriant kriterijus, kuriuose naudojamas loginis operatorius su skaitine verte.
„Pivot Table“ tirpalas
„Pivot“ lentelė yra puikus sprendimas, kai reikia apibendrinti duomenis pagal metus, mėnesį, ketvirtį ir pan., Nes jie atliks jums tokį grupavimą be jokių formulių. Norėdami palyginti formules ir suvestines lenteles, žr. Šį vaizdo įrašą: Kodėl reikia sukti lenteles.
