Daugeliu atvejų mes norime apskaičiuoti kai kurias vertes per mėnesį. Pavyzdžiui, kiek pardavimų buvo atlikta per tam tikrą mėnesį. Na, tai galima padaryti lengvai naudojant suvestines lenteles, bet jei bandote turėti dinamišką ataskaitą, mes galime naudoti SUMPRODUCT arba SUMIFS formulę, kad susumuotume pagal mėnesį.
Pradėkime nuo SUMPRODUCT sprendimo.
Čia yra bendroji formulė, kaip gauti sumą pagal mėnesį „Excel“
= SUMPRODUCT (sumos diapazonas,-(TEKSTAS (datos_diapazonas, „MMM“) = mėnesio_tekstas))
Sumos_dydis : Tai diapazonas, kurį norite apibendrinti per mėnesį.
Data asortimentas : Tai dienų sekos, kurias žiūrėsite mėnesius.
Mėnesio_tekstas: Tai mėnuo, kurio teksto formatu norite susumuoti reikšmes.
Dabar pažiūrėkime pavyzdį:
Pavyzdys: sumos vertės pagal mėnesį „Excel“
Čia mes turime tam tikrą vertę, susijusią su datomis. Šios datos yra 2019 metų sausio, vasario ir kovo mėnesiai.
Kaip matote aukščiau esančiame paveikslėlyje, visos datos yra 2019 m. Dabar mums tereikia susumuoti E2: G2 reikšmes pagal mėnesius E1: G1.
Dabar, kad susumuotumėte reikšmes pagal mėnesius, parašykite šią formulę E2:
= SUMPRODUCT (B2: B9,-(TEXT (A2: A9, "MMM") = E1)))
Jei norite nukopijuoti jį į gretimus langelius, naudokite absoliučias nuorodas arba pavadintus diapazonus, kaip ir paveikslėlyje.
Tai suteikia tikslią kiekvieno mėnesio sumą.
Kaip tai veikia?
Pradėdami nuo vidaus, pažvelkime į TEKSTAS (A2: A9, „MMM“) dalis. Čia funkcija TEXT ištraukia mėnesį iš kiekvienos datos diapazone A2: A9 teksto formatu į masyvą. Vertimas į formulę į = SUMPRODUCT (B2: B9,-({"sausis"; "sausis"; "vasaris"; "sausis"; "vasaris"; "kovas"; "sausis"; "vasaris"} = E1) )
Toliau, TEXT (A2: A9, "MMM")= E1: Čia kiekvienas masyvo mėnuo lyginamas su tekstu E1. Kadangi E1 yra „Jan“, kiekvienas „Jan“ masyve yra paverčiamas į TRUE ir kitas į FALSE. Tai reiškia, kad formulė yra = SUMPRODUCT ($ B $ 2: $ B $ 9,-{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE})
Kitas -(TEXT (A2: A9, "MMM") = E1), konvertuoja TRUE FALSE į dvejetaines reikšmes 1 ir 0. Formulė verčiama į = SUMPRODUCT ($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}).
Pagaliau SUMPRODUCT($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}): funkcija SUMPRODUCT padaugina atitinkamas reikšmes $ B $ 2: $ B $ 9 į masyvą {1; 1; 0; 1; 0; 0; 1; 0} ir juos sudeda. Taigi E1 gauname sumą pagal vertę kaip 20052.
SUM, jei mėnesiai iš skirtingų metų
Pirmiau pateiktame pavyzdyje visos datos buvo iš tų pačių metų. O kas, jei jie būtų iš skirtingų metų? Aukščiau pateikta formulė susumuoja vertes pagal mėnesius, nepriklausomai nuo metų. Pavyzdžiui, 2018 m. Sausis ir 2019 m. Sausis bus pridėti, jei naudosime aukščiau pateiktą formulę. Kas daugeliu atvejų yra neteisinga.
Taip atsitiks, nes aukščiau pateiktame pavyzdyje neturime jokių metų kriterijų. Jei pridėsime ir metų kriterijus, tai veiks.
Bendroji formulė, skirta „Excel“ gauti mėnesio ir metų sumą
= SUMPRODUCT (sumos diapazonas,-(TEXT (date_range, "MMM") = month_text),-(TEXT (date_range, "yyyy") = TEXT (year, 0)))
Čia mes pridėjome dar vieną kriterijų, kuris tikrina metus. Visa kita yra ta pati.
Išspręskime aukščiau pateiktą pavyzdį, parašykite šią formulę E1 langelyje, kad gautumėte sausio sumą 2017 m.
= SUMPRODUCT (B2: B9,-(TEXT (A2: A9, "MMM") = E1),-(TEXT (A2: A9, "yyyy") = TEXT (D2,0)))
Prieš kopijuodami žemiau esančius langelius, naudokite pavadintus diapazonus arba absoliučias nuorodas. Paveikslėlyje kopijavimui gretimuose langeliuose naudoju pavadintus diapazonus.
Dabar taip pat galime pamatyti vertės sumą mėnesiais ir metais.
Kaip tai veikia?
Pirmoji formulės dalis yra tokia pati kaip ir ankstesniame pavyzdyje. Leiskite suprasti papildomą dalį, kuri prideda metų kriterijus.
-(TEXT (A2: A9, "yyyy") = TEXT (D2,0)): TEXT (A2: A9, "yyyy") konvertuoja datą A2: A9 kaip metus teksto formatu į masyvą. {"2018"; "2019"; "2017"; "2017"; "2019"; "2017"; "2019"; "2017"}.
Dažniausiai metai rašomi skaičių formatu. Norėdami palyginti skaičių su tekstu mes, aš pakeičiau metų int tekstą naudodamas TEXT (D2,0). Toliau šiuos teksto metus palyginome su metų masyvu kaip TEKSTAS (A2: A9, "yyyy") = TEXT (D2,0). Grąžinamas masyvas „true-false“ {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}. Toliau „true false“ konvertavome į skaičių naudodami - operatorių. Tai suteikia mums {0; 0; 1; 1; 0; 1; 0; 1}.
Taigi pagaliau formulė bus išversta į = SUMPRODUCT (B2: B9, {1; 1; 0; 1; 0; 0; 1; 0}, {0; 0; 1; 1; 0; 1; 0; 1 }). Kur pirmasis masyvas yra reikšmės. Kitas yra suderintas mėnuo, o trečias - metai. Galiausiai gauname 2160 reikšmių sumą.
Funkcijos SUMIFS naudojimas sumuojant pagal mėnesį
Bendra formulė
= SUMIFS (sumos diapazonas, datos diapazonas, ”> =” & pradžios data, datos sritis, ”<=” ir EOMONTH (pradžios data, 0))
Čia,Sumos_dydis : Tai diapazonas, kurį norite apibendrinti per mėnesį.
Data asortimentas : Tai dienų sekos, kurias žiūrėsite mėnesius.
Pradžios data : Tai pradžios data, nuo kurios norite sudėti sumą. Šiame pavyzdyje tai bus duoto mėnesio 1 d.
Pavyzdys: „Excel“ sumos vertės pagal mėnesį
Čia mes turime tam tikrą vertę, susijusią su datomis. Šios datos yra 2019 metų sausio, vasario ir kovo mėnesiai.
Turime tik susumuoti šias vertes iki mėnesio. Dabar buvo lengva, jei mėnesius ir metus turėjome atskirai. Bet jie nėra. Čia negalime naudoti jokios pagalbos skilties.
Taigi, norėdamas parengti ataskaitą, aš paruošiau ataskaitos formatą, kuriame yra mėnuo ir verčių suma. Mėnesio stulpelyje iš tikrųjų turiu mėnesio pradžios datą. Norėdami pamatyti tik mėnesį, pasirinkite pradžios datą ir paspauskite CTRL+1.
Pasirinktiniu formatu parašykite „mmm“.
Dabar mes turime paruoštus duomenis. Susumuojame vertes pagal mėnesį.
Parašykite šią formulę E3, kad susumuotumėte pagal mėnesį.
= SUMIFS (B3: B10, A3: A10, "> =" & D3, A3: A10, "<=" & EOMONTH (D3,0))
Prieš nukopijuodami formulę, naudokite absoliučias nuorodas arba pavadintus diapazonus.
Taigi, pagaliau gavome rezultatą.
Taigi, kaip tai veikia?
Kaip žinome, funkcija SUMIFS gali susumuoti reikšmes pagal kelis kriterijus.
Pirmiau pateiktame pavyzdyje pirmasis kriterijus yra visų B3: B10 reikšmių suma, kur A3 data: A10 yra didesnė arba lygi datai D3. D3 yra sausio 1 d. Tai taip pat verčia.
= SUMIFS (B3: B10, A3: A10, "> =" & "1-jan-2019", A3: A10, "<=" EOMONTH (D3,0))
Kitas kriterijus yra suma tik tuo atveju, jei data yra A3: A10 yra mažesnis arba lygus EOMONTH (D3,0). Funkcija EOMONTH tik grąžina paskutinės pateiktos mėnesio datos serijos numerį. Galiausiai formulė taip pat verčiama.
= SUMIFS (B3: B10, A3: A10, "> = 2019 m. Sausio 1 d.", A3: A10, "<= 2019 m. Sausio 31 d.")
Taigi, „Excel“ gauname sumą per mėnesį.
Šio metodo pranašumas yra tas, kad galite koreguoti verčių sumavimo pradžios datą.
Jei jūsų datų metai skiriasi, geriau naudoti suvestines lenteles. „Pivot“ lentelės gali padėti lengvai atskirti metinius, ketvirčio ir mėnesio duomenis.
Taigi taip, vaikinai, taip galite apibendrinti vertes pagal mėnesį. Abu būdai turi savo ypatybes. Pasirinkite, kuris būdas jums patinka.
Jei turite klausimų apie šį straipsnį ar kitas su „Excel“ ir VBA susijusias užklausas, komentarų skiltis jums atvira.
Susiję straipsniai:
Kaip naudoti „SUMIF“ funkciją „Excel“
SUMIFS su datomis „Excel“
SUMIF su tuščiomis ląstelėmis
Kaip naudoti SUMIFS funkciją „Excel“
SUMIFS naudojant AND-OR logiką
Populiarūs straipsniai
50 „Excel“ spartusis klavišas produktyvumui padidinti: Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų leis dar greičiau dirbti naudojant „Excel“.
Kaip naudotis t„VLOOKUP“ funkcija „Excel“: Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama vertei iš įvairių diapazonų ir lapų ieškoti.
Kaip naudoti funkciją „COUNTIF“ programoje „Excel“: Skaičiuokite vertes su sąlygomis naudodami šią nuostabią funkciją. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.
Kaip naudotis „SUMIF“ funkcija „Excel“: Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.