Yleinen kaava
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
Yhteenveto
Yhteenvetona kuukausittain voit käyttää SUMIFS-funktioon perustuvaa kaavaa EOMONTH-funktion avulla. Esitetyssä esimerkissä F4: n kaava on:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
Tässä kaavassa käytetään nimettyjä alueita "määrä" (C4: C9) ja "päivämäärä" (B4: B9).
Selitys
SUMIFS-funktio voi laskea arvot alueisiin useiden ehtojen perusteella.
Tässä tapauksessa määrittelemme SUMIFS laskemaan summat kuukausittain kahdella kriteerillä: yhden vastaamaan päivämääriä, jotka ovat suurempia tai yhtä suuria kuin kuukauden ensimmäinen päivä, toisen vastaamaan päivämääriä, jotka ovat pienempiä tai yhtä suuria kuin kuukauden viimeinen päivä. Toisin sanoen tarvitsemme seuraavan kaavan:
=SUMIFS(amount,date,">="&"1/1/2016",date,"<="&"1/31/2016")
Tai luodinkestävämpi vaihtoehto:
=SUMIFS(amount,date,">="&DATE(2016,1,1),date,"<="&DATE(2016,1,31))
Tämä versio on vankempi, koska päivämäärät muodostetaan erillisillä vuosi-, kuukausi- ja päiväarvoilla, eikä niitä syötetä tekstinä, jonka Excelin on tulkittava.
Normaalisti tämä on tuskaa, koska jos lisäät kuukausien nimet tekstinä (esim. "Tammikuu", "helmikuu", "maaliskuu" jne.) Sarakkeeseen E, sinun on mentävä ylimääräisiin vaivoihin luoda päivämääriä, joita voit käyttää kriteereinä . Tässä tapauksessa käytämme kuitenkin yksinkertaista temppua helpottaaksemme asioita: Sarakkeeseen E lisätään kuukausien nimien kirjoittamisen sijaan todelliset päivämäärät (1.1.2016, 1.2.2016, 1.3.2016), käytä sitten mukautettua päivämäärämuotoa ("mmmm") kuun nimien näyttämiseen.
Tämän ansiosta SUMIFS-järjestelmää varten tarvittavien kriteerien luominen on helppoa. Jos haluat sovittaa päivämäärät, jotka ovat suurempia tai yhtä suuria kuin kuukauden ensimmäinen, käytämme:
">="&E4
Ja vastaamaan päivämääriä, jotka ovat pienempiä tai yhtä suuria kuin kuukauden viimeinen päivä, käytämme tätä:
"<="&EOMONTH(E4,0)
Kun nolla kuukausien ajan, EOMONTH-toiminto palauttaa automaattisesti päivämäärän antaman kuukauden viimeisen päivän.
Yhdistäminen tähtimerkillä (&) on välttämätön, kun rakennetaan ehtoja, joissa käytetään numeerisen arvon omaavaa loogista operaattoria.
Kääntöpöydän ratkaisu
Pivot-taulukko on erinomainen ratkaisu, kun sinun on koottava tiedot vuoden, kuukauden, vuosineljänneksen ja niin edelleen, koska he tekevät tällaisen ryhmittelyn sinulle ilman yhtään kaavaa. Kaavojen ja pivot-taulukoiden vertailu vierekkäin on tässä videossa: Miksi pivot-taulukot.
