kaava

Summa vuosittain

Summa Vuosittain

Summa vuosittain

  Excel-kaava: Summa vuosittain Yleinen kaava
=SUMPRODUCT((YEAR(dates)=A1)*amounts)
Yhteenveto

Summaaksesi arvot vuoden mukaan, voit käyttää kaavaa, joka perustuu SUMPRODUCT-toiminto yhdessä YEAR-toiminto . Esitetyssä esimerkissä solun G5 kaava on:





=SUMPRODUCT((YEAR(data[Date])=F5)*data[Amount])

missä tiedot on Excel-taulukko alueella B5:D16. Kun kaava kopioidaan alas, se palauttaa kunkin sarakkeessa F näkyvän vuoden kokonaissumman.

Selitys

Tässä esimerkissä tavoitteena on laskea kokonaissumma jokaiselle sarakkeessa F näkyvälle vuodelle. Kaikki tiedot ovat an Excel-taulukko nimeltään tiedot alueella B5:D16. Suurin haaste on, että meillä on päivämäärät sarakkeessa B, mutta meillä ei ole yksittäisiä vuosiarvoja työskennelläksemme. Yksinkertaisin tapa ratkaista tämä ongelma on SUMPRODUCT-toiminto käyttämällä solussa G5 näkyvää kaavaa. Ongelma voidaan ratkaista myös käyttämällä SUMIFS-toiminto , tai a dynaaminen taulukkokaava . Kaikki kolme lähestymistapaa selitetään alla.





SUMPRODUCT-toiminto

Esitetyssä esimerkissä solun G5 kaava on:

=SUMPRODUCT((YEAR(data[Date])=F5)*data[Amount])

Työskenneltäessä sisältä ulospäin, ensimmäinen askel on vuosiarvojen poimiminen sarakkeen B päivämääristä, mikä tehdään YEAR-toiminnolla:



YEAR(data[Date]) // get years

Koska sarakkeessa on 12 päivämäärää, YEAR palauttaa 12 tulosta an joukko kuten tämä:

{2020;2020;2021;2022;2020;2021;2021;2022;2020;2021;2022;2022}

Jokainen vuosi tässä taulukossa vastaa päivämäärää datassa [Päiväys]. Seuraavaksi vuosiarvoja verrataan vuoteen solussa F5:

YEAR(data[Date])=F5

Tuloksena on 12 TRUE- ja FALSE-arvon joukko, kuten tämä:

{TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}

Huomaa, että TRUE-arvot vastaavat päivämääriä, jotka esiintyvät vuonna 2020, arvo F5:ssä. Seuraavaksi tämä taulukko kerrotaan D-sarakkeen summilla. Matemaattinen toiminto muuntaa automaattisesti arvot TRUE ja FALSE arvoiksi 1 ja 0, joten voimme visualisoida tämän toiminnon seuraavasti:

{1;1;0;0;1;0;0;0;1;0;0;0}*data[Amount]

Muista, että ensimmäisen taulukon ykköset vastaavat päivämääriä, jotka tapahtuvat vuonna 2021, kun taas 0:t osoittavat muiden vuosien päivämääriä. Ensimmäisen taulukon nollat ​​'kumoavat pois' muiden vuosien summat. Tuloksena oleva taulukko palautetaan SUMPRODUCT:iin seuraavasti:

=SUMPRODUCT({1500;1250;0;0;1850;0;0;0;1250;0;0;0})

Kun käsiteltävänä on vain yksi taulukko, SUMPRODUCT summaa taulukon ja palauttaa lopputuloksen 5850. Kun kaava kopioidaan alas, soluviittaus F5, joka on suhteellinen, muuttuu jokaisella uudella rivillä, ja saamme oikean summan jokaiselle vuodelle. esitetty.

SUMIFS-toiminto

Toinen tapa ratkaista tämä ongelma on SUMIFS-funktio ja seuraava kaava:

=SUMIFS(data[Amount],data[Date],">="&DATE(O5,1,1),data[Date],"<="&DATE(O5,12,31))

Jos olet uusi SUMIFS-käyttäjä, Tämä artikkeli kattaa perusasiat.

Tämä kaava on monimutkaisempi kuin SUMPRODUCT-kaava, koska SUMIFS:llä ei ole mahdollisuutta päästä suoraan vuosiarvoihin. Tämä rajoitus on keskustellaan tarkemmin täällä . Sen sijaan meidän on luotava kaksi päivämäärää kullekin vuodelle DATE-toiminto :

DATE(O5,1,1) // start date
DATE(O5,12,31) // end date

Aloituspäivämääräksi annamme yksinkertaisesti 1 kuukausi ja päivä perusteluja saada päivämäärä 1. tammikuuta. Lopetuspäivämääräksi koodaamme 12 for kuukausi ja 31 puolesta päivä luodaksesi päivämäärän 31. joulukuuta. Molemmille päivämäärille vuosi tulee solusta O5, joka sisältää 2020.

Tarvitsemme myös loogisia operaattoreita 'sulkemaan' joka vuosi. Me ketjuttaa tarvitsemme seuraavat operaattorit:

mitä t-testiä käytetään Excelissä
">="&DATE(O5,1,1) // start date
"<="&DATE(O5,12,31) // end date

Vaatimus operaattoreiden ketjuttamisesta näin on jälleen SUMIFS-funktion 'ominaisuus', joka jakaa tämän omituisen syntaksin kahdeksan muuta toimintoa .

Kun DATE-funktio on arvioitu ja ketjutus on valmis, meillä on:

=SUMIFS(data[Amount],data[Date],">=43831",data[Date],"<=44196")

Numero 43831 on Excel-sarjanumeron päivämäärä 1. tammikuuta 2020. Numero 44196 on sarjanumero 31. joulukuuta 2020.

Huomautus: jommallakummalla yllä olevista vaihtoehdoista voit käyttää AINUTLAATUINEN toiminto noutaaksesi yksilölliset vuodet päivämääristä ensimmäiseksi, jos sinulla on nykyaikainen Excel-versio.

Dynaaminen matriisiratkaisu

On myös mahdollista ratkaista tämä ongelma yhdellä dynaamisella taulukkokaavalla, kuten seuraava:

=LET(
years,YEAR(data[Date]),
values,data[Amount],
uyears,SORT(UNIQUE(years)),
sums, BYROW(uyears, LAMBDA(r, SUM(--(years=r)*values))),
VSTACK({"Year","Total"},HSTACK(uyears, sums))
)

Selitys tulossa pian.

Tekijä Dave Bruns


^