Yleinen kaava
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Yhteenveto
Voit keskimäärin kuukausittain käyttää AVERAGEIFS-funktioon perustuvaa kaavaa EOMONTH-toiminnon avulla. Esitetyssä esimerkissä F4: n kaava on:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Tässä kaavassa käytetään nimettyjä alueita "määrät" (D5: D104) ja "päivämäärät" (C5: C104).
Selitys
AVERAGEIFS-toiminto voi keskiarvoistaa vaihteluvälit useiden ehtojen perusteella. Tässä tapauksessa määritämme AVERAGEIFS keskimääräisiin määriin kuukausittain kahdella kriteerillä: (1) ottelun päivämäärät, jotka ovat suurempia tai yhtä suuria kuin kuukauden ensimmäinen päivä, (2) otteluiden päivämäärät ovat pienempiä tai yhtä suuria kuin kuukauden viimeinen päivä. Jos koodattaisimme tammikuun 2016 päivämäärät kaavaan DATE-funktiota käyttäen, se näyttäisi tältä.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Mutta emme halua koodata päivämääriä, haluamme Excelin luomaan nämä päivämäärät meille. Normaalisti tämä on tuskaa, koska jos lisäät kuukausien nimet tekstinä (esim. "Tammikuu", "helmikuu", "maaliskuu" jne.) Sarakkeeseen F, sinun on mentävä ylimääräisiin vaikeuksiin luoda päivämääriä, joita voit käyttää kriteereinä .
Tässä tapauksessa käytämme kuitenkin yksinkertaista temppua helpottaaksemme asioita: Sarakkeeseen F lisätään kuukausien nimien kirjoittamisen sijasta kunkin kuukauden ensimmäisen päivämäärät (1.1.2016, 1.2.2016, 3 / 1/2016, jne.), Ja käytä mukautettua päivämäärämuotoa ("mmm") kuun nimien näyttämiseen.
Tämän ansiosta keskiarvoja varten tarvittavat kriteerit voidaan luoda helposti. 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:
"<="&EOMONTH(E4,0)
EOMONTH palauttaa automaattisesti saman kuukauden viimeisen päivän, koska annamme nollan kuukausien argumentille.
Huomaa: ketjutus tähdellä (&) on välttämätöntä, kun rakennetaan kriteerejä soluviittauksen perusteella.
Kääntöpöydän ratkaisu
Pivot-taulukko on erinomainen ratkaisu, kun sinun on koottava tiedot vuoden, kuukauden, vuosineljänneksen ja niin edelleen, koska pivot-taulukot tarjoavat automaattisen ryhmittelyn päivämäärän mukaan. Kaavojen ja pivot-taulukoiden vertailu vierekkäin on tässä videossa: Miksi pivot-taulukot.
