Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parādīs, kā izmantot funkciju SUMIFS, lai summētu datus, kas atbilst noteiktiem mēnešiem programmā Excel un Google izklājlapas.
Summa pēc mēneša
Pirmkārt, mēs parādīsim, kā apkopot datus, kas atbilst datumiem, kas ietilpst noteiktā mēnesī un gadā.
Mēs varam izmantot funkciju SUMIFS kopā ar funkcijām DATE, YEAR, MONTH un EOMONTH, lai summētu Pārdošanas gadījumu skaits katrā Mēnesis.
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (GADS (E3), MĒNESIS (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Lai izveidotu iepriekš minēto formulu, mēs vispirms definējam datumu diapazonu katram mēnesim. Mēs izmantojam funkciju DATE, lai definētu mēneša pirmo dienu (piem., 01.01.2021.). Mēs to varam izdarīt, “kodējot” datumu formulā:
| 1 | "> =" & DATUMS (2021,5,1) |
Vai, vēl labāk, mēs varam padarīt kritērijus elastīgus, atsaucoties uz datumu šūnā E3, izvelkot datuma gadu un mēnesi (un iestatot dienu vienādu ar 1), kā parādīts iepriekšējā piemērā:
| 1 | "> =" & DATUMS (GADS (E3), MĒNESIS (E3), 1) |
Lai noteiktu mēneša pēdējo dienu, mēs varam izmantot funkciju EOMONTH:
| 1 | "<=" & EOMONTH (E3,0) |
Saliekot visus šos kritērijus kopā, mēs varam uzrakstīt šādu SUMIFS formulu:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (GADS (E3), MĒNESIS (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Šūnu atsauču bloķēšana
Lai mūsu formulas būtu vieglāk lasāmas, mēs esam parādījuši formulas bez bloķētām šūnu atsaucēm:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (GADS (E3), MĒNESIS (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Bet šīs formulas nedarbosies pareizi, kopējot un ielīmējot citur jūsu failā. Tā vietā jums vajadzētu izmantot šādas bloķētas šūnu atsauces:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" & DATE (GADS (E3), MĒNESIS (E3), 1), $ B $ 3: $ B $ 9, "<=" & MĒNESIS (E3,0)) |
Lai uzzinātu vairāk, izlasiet mūsu rakstu par šūnu atsauču bloķēšanu.
Mēneša vērtību formatēšana
Šajā piemērā E slejā esam uzskaitījuši mēnešus. Šīs mēneša vērtības faktiski ir datumi, kas formatēti, lai izlaistu dienu, izmantojot pielāgotu skaitļu formatējumu.
Pielāgotais datu formāts ir “mmmm gggg”, lai parādītu 2022. gada maiju.
Summa pa mēnešiem vairāku gadu laikā
Iepriekš minētajā piemērā tika apkopoti dati ar datumiem, kas iekrituši noteiktā mēnesī un gadā. Tā vietā, izmantojot funkciju SUMPRODUCT, varat apkopot datus ar datumiem, kas ietilpst mēneša laikā jebkurā gadā.
| 1 | = SUMPRODUCT (C3: C8,-(MĒNESIS (B3: B8) = MĒNESIS (G3))) |
Šajā piemērā mēs izmantojam funkciju SUMPRODUCT, lai veiktu sarežģītus summas aprēķinus. Ejam cauri formulai.
Šī ir mūsu galīgā formula:
| 1 | = SUMPRODUCT (C3: C8,-(MĒNESIS (B3: B8) = MĒNESIS (G3))) |
Pirmkārt, SUMPRODUCT funkcija uzskaita Pārdošanas gadījumu skaits katram Pārdošanas datums un pēc tam salīdzina katra mēnesi Pārdošanas datums pret norādīto Mēnesis, atgriežot TRUE, ja mēneši sakrīt, vai FALSE, ja tie neatbilst:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34},-({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})) |
Tālāk dubultās svītras (-) pārvērš TRUE un FALSE vērtības par 1s un 0s:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
Funkcija SUMPRODUCT reizina katru masīvu ierakstu pāri:
| 1 | = SUMPRODUCT ({30; 0; 51; 0; 17; 0}) |
Visbeidzot, masīvā esošie skaitļi tiek summēti:
| 1 | =98 |
Sīkāku informāciju par Būla paziņojumu izmantošanu un komandu “-” SUMPRODUCT funkcijā var atrast šeit
Summa pēc mēneša Google izklājlapās
Šīs formulas Google izklājlapās darbojas tieši tāpat kā programmā Excel.
