Usein haluamme laskea joitain arvoja kuukausittain. Kuten kuinka paljon myyntiä tehtiin kuukaudessa. Tämä voidaan tehdä helposti pivot -taulukoiden avulla, mutta jos yrität saada dynaamista raporttia, voimme käyttää summaa kuukausittain SUMPRODUCT- tai SUMIFS -kaavan avulla.
Aloitetaan SUMPRODUCT -ratkaisulla.
Tässä on yleinen kaava, jolla saat summan kuukausittain Excelissä
= SUMPRODUCT (summan_alue,-(TEXT (date_range, "MMM") = month_text))
Summa -alue : Tämä on alue, jonka haluat summata kuukausittain.
Ajanjakso : Tämä on ajanjakso, johon katsot kuukausia.
Kuukausi_teksti: Se on kuukausi, jonka tekstimuodossa haluat laskea yhteen arvot.
Katsotaan nyt esimerkki:
Esimerkki: Summa -arvot kuukausittain Excelissä
Tässä meillä on päivämääriin liittyvä arvo. Nämä päivämäärät ovat tammi, helmikuu ja maaliskuu 2019.
Kuten yllä olevasta kuvasta näkyy, kaikki päivämäärät ovat vuotta 2019. Nyt meidän on vain laskettava yhteen arvot E2: G2 kuukausina E1: G1.
Jos haluat laskea yhteen arvot kuukausien mukaan, kirjoita tämä kaava E2:
= SUMPRODUCT (B2: B9,-(TEXT (A2: A9, "MMM") = E1)))
Jos haluat kopioida sen viereisiin soluihin, käytä absoluuttisia viittauksia tai nimettyjä alueita kuten kuvassa.
Tämä antaa meille tarkan summan jokaisesta kuukaudesta.
Kuinka se toimii?
Aloitetaan sisäpuolelta, katsotaan TEKSTI (A2: A9, "MMM") osa. Tässä TEKSTI -funktio purkaa kuukauden jokaisesta päivämäärästä alueella A2: A9 tekstimuodossa taulukkoon. Käännös kaavalle = SUMPRODUCT (B2: B9,-({"tammi"; "tammi"; "helmikuu"; "tammi"; "helmikuu"; "maalis"; "tammi"; "helmikuu"} = E1) )
Seuraava, TEKSTI (A2: A9, "MMM")= E1: Täällä jokaista taulukon kuukautta verrataan E1: n tekstiin. Koska E1 sisältää "Jan", jokainen taulukon "Jan" muunnetaan TOSI -arvoksi ja muut FALSE -arvoksi. Tämä muuttaa kaavan muotoon = SUMPRODUCT ($ B $ 2: $ B $ 9,-{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE})
Seuraava -(TEKSTI (A2: A9, "MMM") = E1), muuntaa TOSI EPÄTOSI binaariarvoiksi 1 ja 0. Kaava kääntää = SUMPRODUCT ($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}).
Lopuksi SUMPRODUCT($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}): SUMPRODUCT -funktio kertoo vastaavat arvot $ B $ 2: $ B $ 9 taulukkoon {1; 1; 0; 1; 0; 0; 1; 0} ja laskee ne yhteen. Näin saamme summan arvoksi 20052 E1: ssä.
SUMMA JOS kuukausia eri vuodelta
Yllä olevassa esimerkissä kaikki päivämäärät olivat samasta vuodesta. Mitä jos ne olisivat eri vuosilta? Yllä oleva kaava laskee arvot kuukausittain vuodesta riippumatta. Esimerkiksi tammikuu 2018 ja tammikuu 2019 lisätään, jos käytämme yllä olevaa kaavaa. Mikä on useimmissa tapauksissa väärin.
Tämä tapahtuu, koska yllä olevassa esimerkissä ei ole mitään kriteereitä vuodelle. Jos lisäämme myös vuoden kriteerit, se toimii.
Yleinen kaava, jolla saat summan kuukausittain ja vuosittain Excelissä
= SUMPRODUCT (summa_alue,-(TEXT (date_range, "MMM") = month_text),-(TEXT (date_range, "yyyy") = TEXT (vuosi, 0)))
Tässä olemme lisänneet vielä yhden kriteerin, joka tarkistaa vuoden. Kaikki muu on sama.
Ratkaise yllä oleva esimerkki, kirjoita tämä kaava soluun E1, jotta saat tammikuun summan vuonna 2017.
= SUMPRODUCT (B2: B9,-(TEXT (A2: A9, "MMM") = E1),-(TEXT (A2: A9, "yyyy") = TEXT (D2,0)))
Ennen kuin kopioit alla oleviin soluihin, käytä nimettyjä alueita tai absoluuttisia viittauksia. Kuvassa olen käyttänyt nimettyjä alueita kopioimiseen viereisiin soluihin.
Nyt voimme nähdä arvon summan kuukausina ja vuosina.
Kuinka se toimii?
Kaavan ensimmäinen osa on sama kuin edellinen esimerkki. Ymmärretään lisäosa, joka lisää vuoden kriteerit.
-(TEXT (A2: A9, "yyyy") = TEXT (D2,0)): TEXT (A2: A9, "yyyy") muuntaa A2: A9: n päivämäärän tekstimuodossa olevina vuosina. {"2018"; "2019"; "2017"; "2017"; "2019"; "2017"; "2019"; "2017"}.
Useimmiten vuosi kirjoitetaan numeromuodossa. Vertaakseni lukua tekstiin we, olen muuntaa vuoden int tekstin käyttämällä TEXT (D2,0). Seuraavaksi verrattiin tätä tekstivuotta vuosiryhmään TEXT (A2: A9, "yyyy") = TEXT (D2,0). Tämä palauttaa joukon tosi-vääriä {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}. Seuraavaksi muutimme tosi epätosi numeroksi käyttämällä - operaattoria. Tämä antaa meille {0; 0; 1; 1; 0; 1; 0; 1}.
Joten lopulta kaava käännetään muotoon = SUMPRODUCT (B2: B9, {1; 1; 0; 1; 0; 0; 1; 0}, {0; 0; 1; 1; 0; 1; 0; 1 }). Missä ensimmäinen taulukko on arvot. Seuraavaksi on sovitettu kuukausi ja kolmas vuosi. Lopuksi saamme arvosumman 2160.
SUMIFS -funktion käyttäminen kuukausittain
Yleinen kaava
= SUMIFS (summa_alue, päivämäärä_alue, ”> =” & alkamispäivä, päivämäärä_alue, ”<=” & EOMONTH (alkamispäivä, 0))
Tässä,Summa -alue : Tämä on alue, jonka haluat summata kuukausittain.
Ajanjakso : Tämä on ajanjakso, jota katsot kuukausien ajan.
Aloituspäivämäärä : Se on aloituspäivämäärä, josta haluat tehdä summan. Tässä esimerkissä se on tietyn kuukauden 1. päivä.
Esimerkki: Summa -arvot kuukausittain Excelissä
Tässä meillä on päivämääriin liittyvä arvo. Nämä päivämäärät ovat tammi, helmikuu ja maaliskuu 2019.
Meidän on vain laskettava nämä arvot kuukauteen. Nyt oli helppoa, jos meillä oli kuukausia ja vuosia erikseen. Mutta he eivät ole. Emme voi käyttää tässä mitään saraketta.
Raportin valmistelua varten olen siis laatinut raporttimuodon, joka sisältää kuukauden ja arvojen summan. Kuukausi -sarakkeessa on tosiasiallisesti kuukauden alkamispäivä. Jos haluat nähdä vain kuukauden, valitse aloituspäivä ja paina CTRL+1.
Kirjoita mukautettuun muotoon "mmm".
Nyt meillä on tietomme valmiina. Lasketaan arvot kuukausittain.
Kirjoita tämä kaava E3: een summaamaan kuukausittain.
= SUMIFS (B3: B10, A3: A10, "> =" & D3, A3: A10, "<=" & EOMONTH (D3,0))
Käytä absoluuttisia viittauksia tai nimettyjä alueita ennen kaavan kopiointia.
Joten lopulta saimme tuloksen.
Joten, miten se toimii?
Kuten tiedämme, SUMIFS -funktio voi summata arvoja useista kriteereistä.
Yllä olevassa esimerkissä ensimmäinen kriteeri on kaikkien B3: B10: n arvojen summa, jossa A3: A10: n päivämäärä on suurempi tai yhtä suuri kuin päivämäärä D3. D3 sisältää 1. Tämä kääntää myös.
= SUMIFS (B3: B10, A3: A10, "> =" & "1-tammikuu-2019", A3: A10, "<=" EOMONTH (D3,0))
Seuraava ehto on summa vain, jos Päivämäärä on A3: A10 on pienempi tai yhtä suuri kuin EOMONTH (D3,0). EOMONTH -funktio palauttaa vain annetun kuukauden viimeisen päivämäärän sarjanumeron. Lopuksi kaava kääntää myös.
= SUMIFS (B3: B10, A3: A10, "> = 1-tammikuu-2019", A3: A10, "<= 31-tammikuu-2019")
Näin saamme summan kuukausittain Excelissä.
Tämän menetelmän etuna on, että voit säätää arvojen laskemisen aloituspäivää.
Jos päivämäärilläsi on eri vuodet kuin on parasta käyttää pivot -taulukoita. Pivot -taulukot voivat auttaa sinua erottamaan tiedot vuosittain, neljännesvuosittain ja kuukausittain.
Joten kyllä kaverit, tällä tavalla voit laskea arvot kuukausittain. Molemmilla tavoilla on omat erikoisuutensa. Valitse haluamasi tapa.
Jos sinulla on kysyttävää tästä artikkelista tai muista Excel- ja VBA -aiheisiin liittyvistä kyselyistä, kommentit -osio on avoinna sinulle.
Aiheeseen liittyvät artikkelit:
SUMIF -toiminnon käyttäminen Excelissä
SUMIFS päivämäärien kanssa Excelissä
SUMIF ei-tyhjillä soluilla
SUMIFS -toiminnon käyttäminen Excelissä
SUMIFS käyttäen AND-OR-logiikkaa
Suosittuja artikkeleita
50 Excel -pikakuvake tuottavuuden lisäämiseksi: Nopeuta tehtävääsi. Nämä 50 pikanäppäintä tekevät työskentelystäsi entistä nopeampaa Excelissä.
Kuinka käyttää tVLOOKUP -toiminto Excelissä: Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja taulukoilta.
COUNTIF -toiminnon käyttäminen Excelissä: Laske arvot olosuhteilla tämän hämmästyttävän toiminnon avulla. Sinun ei tarvitse suodattaa tietoja laskeaksesi tiettyjä arvoja. Laskutoiminto on välttämätön kojelaudan valmistelemiseksi.
SUMIF -toiminnon käyttäminen Excelissä: Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.