Mange ganger vil vi beregne noen verdier etter måned. Som, hvor mye salg ble gjort i en relevant måned. Vel, dette kan enkelt gjøres ved hjelp av pivottabeller, men hvis du prøver å ha en dynamisk rapport, kan vi bruke en SUMPRODUCT- eller SUMIFS -formel for å summere etter måned.
La oss starte med SUMPRODUCT -løsningen.
Her er den generiske formelen for å få sum etter måned i Excel
= SUMPRODUCT (sum_range,-(TEXT (date_range, "MMM") = month_text))
Sum_range : Det er området du vil oppsummere etter måned.
Datointervall : Det er datoperioden du vil se på i flere måneder.
Månedstekst: Det er måneden i tekstformat du vil summere verdier for.
La oss nå se et eksempel:
Eksempel: Sum verdier etter måned i Excel
Her har vi en viss verdi knyttet til datoer. Disse datoene er januar, februar og mars måned 2019.
Som du kan se på bildet ovenfor, er alle datoene for året 2019. Nå trenger vi bare å summere verdier i E2: G2 etter måneder i E1: G1.
For å summere verdier i henhold til måneder, skriv denne formelen i E2:
= SUMPRODUCT (B2: B9,-(TEKST (A2: A9, "MMM") = E1)))
Hvis du vil kopiere den i tilstøtende celler, bruker du absolutte referanser eller navngitte områder som deg i bildet.
Dette gir oss den eksakte summen av hver måned.
Hvordan det fungerer?
Start fra innsiden, la oss se på TEKST (A2: A9, "MMM") del. Her trekker TEKST -funksjon ut måned fra hver dato i område A2: A9 i tekstformat til en matrise. Oversetter til formel til = SUMPRODUCT (B2: B9,-({"Jan"; "Jan"; "Feb"; "Jan"; "Feb"; "Mar"; "Jan"; "Feb"} = E1) )
Deretter TEKST (A2: A9, "MMM")= E1: Her sammenlignes hver måned i array med tekst i E1. Siden E1 inneholder “Jan”, blir hver “Jan” i array konvertert til TRUE og andre til FALSE. Dette oversetter formelen til = SUMPRODUCT ($ B $ 2: $ B $ 9,-{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE})
Neste -(TEKST (A2: A9, "MMM") = E1), konverterer TRUE FALSE til binære verdier 1 og 0. Formelen oversetter til = SUMPRODUCT ($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}).
Endelig SUMPRODUKT($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}): SUMPRODUCT -funksjonen multipliserer de tilsvarende verdiene i $ B $ 2: $ B $ 9 til array {1; 1; 0; 1; 0; 0; 1; 0} og legger dem til. Derfor får vi sum etter verdi som 20052 i E1.
SUMMER HVIS Måneder fra forskjellige år
I eksemplet ovenfor var alle datoene fra samme år. Hva om de var fra forskjellige år? Formelen ovenfor vil summere verdier etter måned uavhengig av år. For eksempel vil januar 2018 og januar 2019 bli lagt til hvis vi bruker formelen ovenfor. Noe som er feil i de fleste tilfeller.
Dette vil skje fordi vi ikke har noen kriterier for året i eksemplet ovenfor. Hvis vi legger til årskriterier også, vil det fungere.
Den generiske formelen for å få sum etter måned og år i Excel
= SUMPRODUCT (sum_range,-(TEXT (date_range, "MMM") = month_text),-(TEXT (date_range, "yyyy") = TEXT (year, 0)))
Her har vi lagt til et kriterium til som kontrollerer året. Alt annet er det samme.
La oss løse eksemplet ovenfor, skriv denne formelen i celle E1 for å få summen av Jan i 2017.
= SUMPRODUCT (B2: B9,-(TEXT (A2: A9, "MMM") = E1),-(TEXT (A2: A9, "yyyy") = TEXT (D2,0)))
Bruk navngitte områder eller absolutte referanser før du kopierer inn under cellene. På bildet har jeg brukt navngitte områder for kopiering i cellene ved siden av.
Nå kan vi også se summen av verdi etter måneder og år.
Hvordan virker det?
Den første delen av formelen er den samme som forrige eksempel. La oss forstå den ekstra delen som legger til årskriteriene.
-(TEKST (A2: A9, "åååå") = TEKST (D2,0)): TEKST (A2: A9, "åååå") konverterer dato i A2: A9 som år i tekstformat til en matrise. {"2018"; "2019"; "2017"; "2017"; "2019"; "2017"; "2019"; "2017"}.
Mesteparten av tiden er året skrevet i tallformat. For å sammenligne et tall med tekst vi, har jeg konvertert år int tekst ved hjelp av TEKST (D2,0). Deretter sammenlignet vi dette tekståret med en rekke år som TEKST (A2: A9, "åååå") = TEKST (D2,0). Dette returnerer en matrise med true-false {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}. Deretter konverterte vi true false til tall ved hjelp av - operator. Dette gir oss {0; 0; 1; 1; 0; 1; 0; 1}.
Så til slutt vil formelen bli oversatt til = SUMPRODUCT (B2: B9, {1; 1; 0; 1; 0; 0; 1; 0}, {0; 0; 1; 1; 0; 1; 0; 1 }). Hvor den første matrisen er verdier. Neste er matchet måned og tredje er år. Til slutt får vi vår verdisum som 2160.
Bruk SUMIFS -funksjonen til å summere etter måned
Generisk formel
= SUMIFS (sum_range, date_range, ”> =” & startdate, date_range, ”<=” & EOMONTH (start_date, 0))
Her,Sum_range : Det er området du vil oppsummere etter måned.
Datointervall : Det er datoperioden du vil se på i flere måneder.
Startdato : Det er startdatoen du vil summere fra. For dette eksemplet vil det være 1. i den angitte måneden.
Eksempel: Sum verdier etter måned i Excel
Her har vi en viss verdi knyttet til datoer. Disse datoene er januar, februar og mars måned 2019.
Vi trenger bare å summere disse verdiene innen måneden. Nå var det enkelt hvis vi hadde måneder og år hver for seg. Men det er de ikke. Vi kan ikke bruke noen hjelpekolonne her.
Så for å utarbeide rapport har jeg utarbeidet et rapportformat som inneholder måned og sum av verdier. I månedskolonnen har jeg faktisk en startdato i måneden. For å bare se måneden, velg startdato og trykk CTRL+1.
I tilpasset format skriver du "mmm".
Nå har vi dataene våre klare. La oss summere verdiene etter måned.
Skriv denne formelen i E3 for å summere etter måned.
= SUMMER (B3: B10, A3: A10, "> =" & D3, A3: A10, "<=" & EOMONTH (D3,0))
Bruk absolutte referanser eller navngitte områder før du kopierer ned formelen.
Så endelig fikk vi resultatet.
Så, hvordan fungerer det?
Som vi vet kan SUMIFS -funksjonen summe verdier på flere kriterier.
I eksempelet ovenfor er det første kriteriet summen av alle verdier i B3: B10 hvor dato i A3: A10 er større enn eller lik dato i D3. D3 inneholder 1-jan. Dette oversetter også.
= SUMIFS (B3: B10, A3: A10, "> =" & “1-jan-2019”, A3: A10, “<=” EOMONTH (D3,0))
Neste kriterier er summen bare hvis dato i A3: A10 er mindre enn eller lik EOMONTH (D3,0). EOMONTH -funksjonen returnerer serienummeret til den siste datoen i måneden som er oppgitt. Endelig oversetter formelen også.
= SUMIFS (B3: B10, A3: A10, "> = 1-jan-2019”, A3: A10, "<= 31-jan-2019”)
Derfor får vi summen i måned i excel.
Fordelen med denne metoden er at du kan justere startdatoen for summering av verdiene.
Hvis datoene dine har andre år enn det er best å bruke pivottabeller. Pivottabeller kan hjelpe deg med å skille dataene i årlige, kvartalsvise og månedlige formater.
Så ja gutta, dette er hvordan du kan summere verdier etter måned. Begge veier har sine egne spesialiteter. Velg hvilken måte du liker.
Hvis du har spørsmål angående denne artikkelen eller andre Excel- og VBA -relaterte spørsmål, er kommentarfeltet åpent for deg.
Relater artikler:
Slik bruker du SUMIF -funksjonen i Excel
SUMIFS med datoer i Excel
SUMIF med ikke-tomme celler
Slik bruker du SUMIFS -funksjonen i Excel
SUMMER ved hjelp av AND-OR logikk
Populære artikler
50 Excel -snarvei for å øke produktiviteten: Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.
Slik bruker du tVLOOKUP -funksjonen i Excel: Dette er en av de mest brukte og populære funksjonene i excel som brukes til å slå opp verdi fra forskjellige områder og ark.
Slik bruker du COUNTIF -funksjonen i Excel: Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel: Dette er en annen viktig funksjon på dashbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.