Mange gange vil vi beregne nogle værdier efter måned. Ligesom hvor meget salg der blev foretaget i en relevant måned. Dette kan godt gøres let ved hjælp af pivottabeller, men hvis du forsøger at få en dynamisk rapport, kan vi bruge en SUMPRODUCT- eller SUMIFS -formel til at summere efter måned.
Lad os starte med SUMPRODUCT -løsning.
Her er den generiske formel for at få sum efter måned i Excel
= SUMPRODUCT (sum_range,-(TEXT (date_range, "MMM") = month_text))
Sum_range : Det er det område, du vil summere efter måned.
Datoområde : Det er det datointerval, du vil kigge efter i flere måneder.
Månedstekst: Det er den måned i tekstformat, som du vil opsummere værdier.
Lad os nu se et eksempel:
Eksempel: Sumværdier efter måned i Excel
Her har vi en vis værdi forbundet med datoer. Disse datoer er januar, februar og marts måned i året 2019.
Som du kan se på billedet ovenfor, er alle datoerne for år 2019. Nu mangler vi bare at summe værdier i E2: G2 med måneder i E1: G1.
For at summe værdier i henhold til måneder, skriv denne formel i E2:
= SUMPRODUCT (B2: B9,-(TEKST (A2: A9, "MMM") = E1)))
Hvis du vil kopiere det i tilstødende celler, skal du bruge absolutte referencer eller navngivne områder som dig i billedet.
Dette giver os den nøjagtige sum af hver måned.
Hvordan det virker?
Start indefra, lad os se på TEKST (A2: A9, "MMM") en del. Her udtrækker TEKST -funktion måned fra hver dato i område A2: A9 i tekstformat til et array. Oversættelse til formel til = SUMPRODUCT (B2: B9,-({"Jan"; "Jan"; "Feb"; "Jan"; "Feb"; "Mar"; "Jan"; "Feb"} = E1) )
Dernæst TEKST (A2: A9, "MMM")= E1: Her sammenlignes hver måned i array med tekst i E1. Da E1 indeholder “Jan”, konverteres hver “Jan” i array til TRUE og andre til FALSE. Dette oversætter formlen til = SUMPRODUCT ($ B $ 2: $ B $ 9,-{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE})
Næste -(TEKST (A2: A9, "MMM") = E1), konverterer SAND FALSK til binære værdier 1 og 0. Formlen oversættes 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 -funktionen multiplicerer de tilsvarende værdier i $ B $ 2: $ B $ 9 til array {1; 1; 0; 1; 0; 0; 1; 0} og tilføjer dem. Derfor får vi sum efter værdi som 20052 i E1.
SUM HVIS Måneder fra forskelligt år
I ovenstående eksempel var alle datoer fra samme år. Hvad hvis de var fra forskellige år? Ovenstående formel summerer værdier efter måned uanset år. For eksempel tilføjes januar 2018 og januar 2019, hvis vi bruger ovenstående formel. Hvilket er forkert i de fleste tilfælde.
Dette vil ske, fordi vi ikke har nogen kriterier for året i ovenstående eksempel. Hvis vi også tilføjer årskriterier, vil det fungere.
Den generiske formel for at få sum efter 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 tilføjet endnu et kriterium, der kontrollerer året. Alt andet er det samme.
Lad os løse ovenstående eksempel, skriv denne formel i celle E1 for at få summen af Jan i år 2017.
= SUMPRODUCT (B2: B9,-(TEKST (A2: A9, "MMM") = E1),-(TEKST (A2: A9, "åååå") = TEKST (D2,0)))
Inden du kopierer i nedenstående celler, skal du bruge navngivne områder eller absolutte referencer. På billedet har jeg brugt navngivne områder til kopiering i de tilstødende celler.
Nu kan vi også se summen af værdi i måneder og år.
Hvordan virker det?
Den første del af formlen er den samme som tidligere eksempel. Lad os forstå den ekstra del, der tilføjer årskriterierne.
-(TEKST (A2: A9, "åååå") = TEKST (D2,0)): TEKST (A2: A9, "åååå") konverterer dato i A2: A9 som år i tekstformat til et array. {"2018"; "2019"; "2017"; "2017"; "2019"; "2017"; "2019"; "2017"}.
Det meste af tiden er år skrevet i talformat. For at sammenligne et tal med tekst vi, har jeg konverteret år int tekst ved hjælp af TEKST (D2,0). Dernæst sammenlignede vi dette tekstår med mange år som TEKST (A2: A9, "åååå") = TEKST (D2,0). Dette returnerer en matrix med sand-falsk {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}. Dernæst konverterede vi sand falsk til tal ved hjælp af - operator. Dette giver os {0; 0; 1; 1; 0; 1; 0; 1}.
Så endelig vil formlen blive oversat til = SUMPRODUCT (B2: B9, {1; 1; 0; 1; 0; 0; 1; 0}, {0; 0; 1; 1; 0; 1; 0; 1 }). Hvor første array er værdier. Næste matches måned og tredje er år. Endelig får vi vores sum af værdier som 2160.
Brug SUMIFS -funktionen til at summe efter måned
Generisk formel
= SUMIFS (sum_range, date_range, ”> =” & startdato, date_range, ”<=” & EOMONTH (start_date, 0))
Her,Sum_range : Det er det område, du vil summere efter måned.
Datoområde : Det er det datointerval, du vil kigge efter i flere måneder.
Start dato : Det er startdatoen, du vil summere fra. For dette eksempel vil det være 1. i den givne måned.
Eksempel: Sumværdier efter måned i Excel
Her har vi en vis værdi forbundet med datoer. Disse datoer er januar, februar og marts måned i året 2019.
Vi skal bare opsummere disse værdier med måneden. Nu var det let, hvis vi havde måneder og år adskilt. Men det er de ikke. Vi kan ikke bruge nogen hjælpekolonne her.
Så for at udarbejde rapport har jeg udarbejdet et rapportformat, der indeholder måned og sum af værdier. I månedskolonnen har jeg faktisk en måneds startdato. For bare at se måneden skal du vælge startdato og trykke på CTRL+1.
I tilpasset format skal du skrive "mmm".
Nu har vi vores data klar. Lad os opsummere værdierne efter måned.
Skriv denne formel i E3 for at summere efter måned.
= SUMIFS (B3: B10, A3: A10, "> =" & D3, A3: A10, "<=" & EOMONTH (D3,0))
Brug absolutte referencer eller navngivne områder, før du kopierer formlen ned.
Så endelig fik vi resultatet.
Så hvordan fungerer det?
Som vi ved, kan SUMIFS -funktionen summe værdier på flere kriterier.
I eksemplet ovenfor er de første kriterier summen af alle værdier i B3: B10, hvor dato i A3: A10 er større end eller lig med dato i D3. D3 indeholder 1-jan. Dette oversætter også.
= SUMIFS (B3: B10, A3: A10, "> =" & “1-jan-2019”, A3: A10, "<=" EOMONTH (D3,0))
Næste kriterium er kun sum, hvis Dato i A3: A10 er mindre end eller lig med EOMONTH (D3,0). EOMONTH -funktionen returnerer bare serienummeret på den angivne sidste månedsdato. Endelig oversættes formel 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 ved denne metode er, at du kan justere startdatoen for at opsummere værdierne.
Hvis dine datoer har forskellige år, er det bedst at bruge pivottabeller. Pivottabeller kan hjælpe dig med at adskille dataene i årligt, kvartalsvis og månedligt format er let.
Så ja fyre, sådan kan du summe værdier efter måned. Begge måder har deres egne specialiteter. Vælg hvilken måde du kan lide.
Hvis du har spørgsmål vedrørende denne artikel eller andre excel- og VBA -relaterede forespørgsler, er kommentarfeltet åbent for dig.
Relater artikler:
Sådan bruges SUMIF -funktionen i Excel
SUMIFS med datoer i Excel
SUMIF med ikke-tomme celler
Sådan bruges SUMIFS -funktionen i Excel
SUMIFS ved hjælp af AND-OR logik
Populære artikler
50 Excel -genvej til at øge din produktivitet: Bliv hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.
Sådan bruges than VLOOKUP -funktion i Excel: Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.
Sådan bruges funktionen COUNTIF i Excel: Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. Countif -funktion er afgørende for at forberede dit dashboard.
Sådan bruges SUMIF -funktionen i Excel: Dette er endnu en vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.