Summere måned og kvartal ut fra en datorekke med SummerProdukt
Jeg er sikkert ikke den eneste som har benyttet en hjelpekolonne for å fiske ut måneden av en datorekke for så å summere med summer.hvis.sett. I dette eksemplet skal vi se på hvordan vi kan gjøre det uten å bruke en hjelpekolonne ved å benytte SummerProdukt. Og vi skal se på noen andre lure triks.
(Artikkelen fortsetter under videoruten)
SUMMERPRODUKT – Summere en datorekke per måned
I eksemplet har vi datoene i A-kolonnen og beløpene i B-kolonnen
Formelen i E2 er
=SUMMERPRODUKT( (MÅNED($A$2:$A$1001)=MÅNED(D2&1)) * ($B$2:$B$1001) )
Vi deler den opp litt
MÅNED($A$2:$A$1001)
SummerProdukt har den egenskapen at vi kan bruke (enkelte) funksjoner på matrisen vi skal summere, i dette tilfellet Måned(). Vi er ikke interessert i datoen i området A2-A1001, men måneden de representerer. Det Summerprodukt gjør, er å sjekke dette området mot den neste biten av formelen:
=MÅNED(D2&1)
… og lage en serie med ettall for de linjene som tilfredsstiller betingelsen, og nuller for de som ikke gjør det. (Se lenger ned for en forklaring på D2&1)
Så ganges resultatet, altså enten 1 eller 0 med tallet i B-kolonnen på samme rad, og hele stasen summeres deretter.
SUMMERPRODUKT – Summere en datorekke per kvartal
I H2 bruker vi formelen
=SUMMERPRODUKT((AVRUND.OPP(MÅNED($A$2:$A$1001)/3;0)=G2)* ($B$2:$B$1001) )
Vi finner måneden som datoene representerer, deler den på tre og avrunder opp uten desimaler, og ønsker at resultatet skal stemme med kvartalnummeret i G2. Deretter ganger vi resultatet med verdiene i B2-B1001
Formel som finner kvartal ut fra måned.
Hvis vi har måneden i A2, kan vi finne kvartalet med formelen
=AVRUND.OPP(A2/3;0)
Altså måneden delt på tre avrundet opp uten desimaler. Du kan teste formelen her ved å endre måned:
Se også: Summer.Hvis.Sett – litt i dybden
Hvordan lure ut nummeret på måneden ut fra navnet på måneden
Funksjonen Måned() finner nummeret på måneden (1-12) ut fra en dato.
=Måned("01.01.2017")vil returnere 1
Hvis vi forsøker oss med
=Måned("Desember")får vi en feil…
Men hvis vi forsøker oss med
=Måned("Desember1")tror Excel at vi har skrevet en dato, og returnerer 12 🙂
Hvis du har måneden din i D2, kan du altså fiske ut nummeret på måneden ved å skrive
=Måned(D2&1)
Hihi.
Funksjonen UKENR() kan ikke benyttes på matriser (f.eks A2-A1001), og fungerer ikke sammen med SUMMERPRODUKT
Last ned regnearket her: OmsetningPerMaanedogKvartal.xlsx (63 downloads)
