Daudzas reizes mēs vēlamies aprēķināt dažas vērtības pa mēnešiem. Piemēram, cik daudz pārdošanas tika veikts mēneša laikā. To var viegli izdarīt, izmantojot rakurstabulas, bet, ja jūs mēģināt izveidot dinamisku pārskatu, mēs varam izmantot SUMPRODUCT vai SUMIFS formulu, lai summētu pa mēnešiem.
Sāksim ar SUMPRODUCT risinājumu.
Šeit ir vispārīga formula, kā iegūt summu pa mēnešiem programmā Excel
= SUMPRODUCT (summas_diapazons,-(TEXT (date_range, "MMM") = month_text))
Summas diapazons : Tas ir diapazons, kuru vēlaties apkopot pa mēnešiem.
Datuma_diapazons : Tas ir datumu diapazons, kurā jūs meklēsit mēnešus.
Month_text: Tas ir mēnesis, kura teksta formātā vēlaties summēt vērtības.
Tagad apskatīsim piemēru:
Piemērs: Summu vērtības pēc mēneša programmā Excel
Šeit mums ir kāda vērtība, kas saistīta ar datumiem. Šie datumi ir 2019. gada janvāris, februāris un marts.
Kā redzat attēlā iepriekš, visi datumi ir 2019. gads. Tagad mums vienkārši jāsummē vērtības E2: G2 pa mēnešiem E1: G1.
Tagad, summējot vērtības pēc mēnešiem, ierakstiet šo formulu E2:
= SUMPRODUCT (B2: B9,-(TEXT (A2: A9, "MMM") = E1)))
Ja vēlaties to kopēt blakus esošajās šūnās, izmantojiet attēla absolūtās atsauces vai nosauktos diapazonus.
Tas dod mums precīzu katra mēneša summu.
Kā tas strādā?
Sākot no iekšpuses, apskatīsim TEKSTS (A2: A9, "MMM") daļa. Šeit funkcija TEXT izraksta mēnesi no katra datuma diapazonā A2: A9 teksta formātā masīvā. Tulkošana formulā uz = SUMPRODUCT (B2: B9,-({"Jan"; "Jan"; "Feb"; "Jan"; "Feb"; "Mar"; "Jan"; "Feb"} = E1) )
Tālāk teksts (A2: A9, "MMM")= E1: Šeit katru mēnesi masīvā salīdzina ar tekstu E1. Tā kā E1 satur “Jan”, katrs “Jan” masīvā tiek pārvērsts par TRUE un cits par FALSE. Formula tiek tulkota uz = SUMPRODUCT ($ B $ 2: $ B $ 9,-{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE})
Tālāk -(TEKSTS (A2: A9, "MMM") = E1), pārvērš TRUE FALSE binārajās vērtībās 1 un 0. Formula tiek tulkota uz = SUMPRODUCT ($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}).
Visbeidzot SUMPRODUCT($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}): funkcija SUMPRODUCT reizina atbilstošās vērtības $ B $ 2: $ B $ 9 līdz masīvam {1; 1; 0; 1; 0; 0; 1; 0} un tos saskaita. Tādējādi E1 iegūstam summu pēc vērtības kā 20052.
SUMMA, ja mēneši no cita gada
Iepriekš minētajā piemērā visi datumi bija no tā paša gada. Ko darīt, ja tie būtu no dažādiem gadiem? Iepriekš minētā formula summē vērtības pa mēnešiem neatkarīgi no gada. Piemēram, 2018. gada janvāris un 2019. gada janvāris tiks pievienoti, ja izmantosim iepriekš minēto formulu. Kas vairumā gadījumu ir nepareizi.
Tas notiks, jo iepriekš minētajā piemērā mums nav gada kritēriju. Ja mēs pievienosim arī gada kritērijus, tas darbosies.
Vispārīga formula, lai programmā Excel iegūtu summu pa mēnešiem un gadiem
= SUMPRODUCT (summas_diapazons,-(TEXT (date_range, "MMM") = month_text),-(TEXT (date_range, "yyyy") = TEXT (gads, 0)))
Šeit mēs esam pievienojuši vēl vienu kritēriju, kas pārbauda gadu. Viss pārējais ir vienāds.
Atrisināsim iepriekš minēto piemēru, ierakstiet šo formulu šūnā E1, lai iegūtu Jāņa summu 2017.
= SUMPRODUCT (B2: B9,-(TEXT (A2: A9, "MMM") = E1),-(TEXT (A2: A9, "yyyy") = TEXT (D2,0)))
Pirms kopēšanas zemāk esošajās šūnās izmantojiet nosauktos diapazonus vai absolūtās atsauces. Attēlā esmu izmantojis nosauktos diapazonus, lai kopētu blakus esošajās šūnās.
Tagad mēs varam redzēt arī vērtību summu pa mēnešiem un gadiem.
Kā tas darbojas?
Formulas pirmā daļa ir tāda pati kā iepriekšējā piemērā. Ļaujiet mums saprast papildu daļu, kas pievieno gada kritērijus.
-(TEXT (A2: A9, "yyyy") = TEXT (D2,0)): TEXT (A2: A9, "yyyy") pārvērš datumu A2: A9 par gadiem teksta formātā masīvā. {"2018"; "2019"; "2017"; "2017"; "2019"; "2017"; "2019"; "2017"}.
Lielākoties gads tiek rakstīts skaitļu formātā. Lai salīdzinātu skaitli ar tekstu mēs, es esmu konvertējis gada int tekstu, izmantojot TEXT (D2,0). Tālāk mēs salīdzinājām šo teksta gadu ar gadu masīvu kā TEKSTS (A2: A9, "gggg") = TEKSTS (D2,0). Tas atgriež patiesu-nepatiesu masīvu {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}. Tālāk mēs pārveidojām patieso nepatieso skaitlī, izmantojot - operatoru. Tādējādi mēs iegūstam {0; 0; 1; 1; 0; 1; 0; 1}.
Visbeidzot, formula tiks tulkota uz = SUMPRODUCT (B2: B9, {1; 1; 0; 1; 0; 0; 1; 0}, {0; 0; 1; 1; 0; 1; 0; 1 }). Kur pirmais masīvs ir vērtības. Nākamais ir saskaņots mēnesis, bet trešais - gads. Visbeidzot, mēs iegūstam savu vērtību summu 2160.
Funkcijas SUMIFS izmantošana summēšanai pa mēnešiem
Vispārīgā formula
= SUMIFS (summas diapazons, datuma_diapazons, ”> =” & sākuma datums, datuma_diapazons ”, <<” un EOMONTH (sākuma_datums, 0))
Šeit,Summas diapazons : Tas ir diapazons, kuru vēlaties apkopot pa mēnešiem.
Datuma_diapazons : Tas ir datumu diapazons, kurā jūs meklēsit mēnešus.
Sākuma datums : Tas ir sākuma datums, no kura vēlaties summēt. Šajā piemērā tas būs konkrētā mēneša 1. datums.
Piemērs: Summu vērtības pēc mēneša programmā Excel
Šeit mums ir kāda vērtība, kas saistīta ar datumiem. Šie datumi ir 2019. gada janvāris, februāris un marts.
Mums vienkārši jāapkopo šīs vērtības mēnesī. Tagad tas bija viegli, ja mums bija mēneši un gadi atsevišķi. Bet tie nav. Mēs šeit nevaram izmantot nevienu palīdzības sleju.
Tātad, lai sagatavotu pārskatu, esmu sagatavojis pārskata formātu, kurā ir mēnesis un vērtību summa. Slejā Mēnesis man faktiski ir mēneša sākuma datums. Lai redzētu tikai mēnesi, atlasiet sākuma datumu un nospiediet taustiņu kombināciju CTRL+1.
Pielāgotā formātā ierakstiet “mmm”.
Tagad mūsu dati ir gatavi. Apkoposim vērtības pa mēnešiem.
Ierakstiet šo formulu E3, lai summētu pa mēnešiem.
= SUMIFS (B3: B10, A3: A10, "> =" & D3, A3: A10, "<=" & EOMONTH (D3,0))
Pirms formulas nokopēšanas izmantojiet absolūtās atsauces vai nosauktos diapazonus.
Tātad, beidzot mēs saņēmām rezultātu.
Tātad, kā tas darbojas?
Kā mēs zinām, funkcija SUMIFS var summēt vērtības pēc vairākiem kritērijiem.
Iepriekš minētajā piemērā pirmais kritērijs ir visu B3: B10 vērtību summa, kur datums A3: A10 ir lielāks vai vienāds ar datumu D3. D3 satur 1. janvāri. Tas arī tulko.
= SUMIFS (B3: B10, A3: A10, "> =" & "1-jan-2019", A3: A10, "<=" EOMONTH (D3,0))
Nākamie kritēriji ir summa tikai tad, ja datums ir norādīts A3: A10 ir mazāks vai vienāds ar EOMONTH (D3,0). Funkcija EOMONTH tikai atgriež norādītā mēneša pēdējā datuma sērijas numuru. Visbeidzot, tulko arī formula.
= SUMIFS (B3: B10, A3: A10, "> = 1-jan-2019", A3: A10, "<= 31-jan-2019")
Tādējādi Excel iegūstam summu pa mēnešiem.
Šīs metodes priekšrocība ir tā, ka jūs varat pielāgot vērtību summēšanas sākuma datumu.
Ja jūsu datumiem ir atšķirīgi gadi, nekā vislabāk ir izmantot rakurstabulas. Pivot tabulas var palīdzēt viegli nošķirt datus gada, ceturkšņa un mēneša formātā.
Jā, puiši, šādā veidā jūs varat apkopot vērtības pa mēnešiem. Abiem veidiem ir savas īpatnības. Izvēlieties, kurš veids jums patīk.
Ja jums ir kādi jautājumi par šo rakstu vai citi ar Excel un VBA saistīti jautājumi, komentāru sadaļa ir atvērta jums.
Saistītie raksti:
Kā lietot SUMIF funkciju programmā Excel
SUMIFS ar datumiem programmā Excel
SUMIF ar šūnām, kas nav tukšas
Kā lietot funkciju SUMIFS programmā Excel
SUMIFS, izmantojot AND-OR loģiku
Populāri raksti
50 Excel saīsne produktivitātes paaugstināšanai: Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.
Kā lietot tVLOOKUP funkcija programmā Excel: Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā programmā Excel izmantot funkciju COUNTIF: Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot funkciju SUMIF programmā Excel: Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.