Yhteenveto
Yhteenvetona sarakkeittain kuukaudessa voit käyttää SUMIFS-funktiota yhdessä EOMONTH-funktion kanssa. Esitetyssä esimerkissä kaava G5: ssä on:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Tässä kaavassa käytetään kolmea nimettyä aluetta: "määrä" (D5: D15), "asiakas" (B5: B15) ja "päivämäärä" (C5: C15).
Selitys
SUMIFS-toiminto on suunniteltu yhteenlaskemaan arvot yhdelle tai useammalle kriteerille. Tässä tapauksessa tarvitsemme kolme kriteeriä:
- Asiakkaan nimi = asiakas sarakkeessa F
- Päivämäärä> = kuukauden ensimmäinen päivä (rivillä 4 olevasta päivämäärästä)
- Päivämäärä <= kuukauden loppu (päivämäärä rivillä 4)
Keskeinen asia: kuukausien nimet G4: I4: ssä ovat todellisuudessa kelvollisia päivämääriä, jotka on muotoiltu mukautetulla lukumuodolla "mmm". Tämä antaa meille mahdollisuuden käyttää G4: I4: n päivämääräarvoja suoraan yllä oleviin kriteereihin 2 ja 3.
Takaisin SUMIFS-tiedostoon ensimmäistä väli / kriteeriparia käytetään tarkistamaan asiakkaan nimi:
client,$F5
Huomautus F5 on sekoitettu viite, sarake lukittu. Tämän avulla rivi voi muuttua, kun kaava kopioidaan taulukon läpi, mutta asiakkaan nimi tulee aina sarakkeesta F.
Toista väli / kriteeriparia käytetään päivämäärien tarkistamiseen kuukauden ensimmäisenä:
date,">="&G$4
Kuten edellä mainittiin, G4: I4: n arvot ovat tosiasiallisesti päivämääriä: 1.1.2019, 1.2.2019 ja 1.3.2019. Nämä kriteerit tarkistavat yksinkertaisesti päivämäärät, jotka ovat suurempia kuin rivin 4 ensimmäisen kuukauden päivämäärät. Huomaa, että tämä on toinen sekoitettu viite, tällä kertaa rivi lukittu. Tämän avulla sarake voi muuttua, kun kaava kopioidaan taulukon yli, mutta rivin numero pysyy kiinteänä. Yhdistäminen tähtimerkillä (&) on välttämätöntä, kun rakennetaan ehtoja, jotka käyttävät loogista operaattoria ja toisen solun arvoa.
Kolmatta väli / kriteeriparia käytetään päivämäärien tarkistamiseen kuukauden viimeiseen päivään nähden:
date,"<="&EOMONTH(G$4,0)
Saadaksesi kunkin kuukauden viimeisen päivän käytämme EOMONTH-funktiota päivämääränä rivin 4 sarakeotsikosta. Kun kuukausiargumentille on annettu nolla, EOMONTH palauttaa saman kuukauden viimeisen päivän. Jälleen viittaus G4: ään sekoitetaan, jotta rivi ei muutu.
Kääntöpöydän ratkaisu
Pivot-taulukko olisi erinomainen ratkaisu tähän ongelmaan, koska se voi ryhmitellä automaattisesti kuukausittain ilman yhtään kaavaa. Kaavojen ja pivot-taulukoiden vertailu vierekkäin on tässä videossa: Miksi pivot-taulukot.
