Завантажте зразок робочої книги
У цьому посібнику буде показано, як використовувати функцію SUMIFS для підсумовування даних, відповідних певним місяцям, у Excel та Google Таблицях.
Сума, якщо за місяць
По -перше, ми покажемо, як підсумовувати дані, що відповідають датам, які припадають на певний місяць і рік.
Ми можемо використовувати функцію SUMIFS разом із функціями DATE, YEAR, MONTH та EOMONTH Кількість продажів всередині кожного Місяць.
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & ДАТА (РІК (E3), МІСЯЦЬ (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Щоб побудувати формулу вище, ми почнемо з визначення діапазону дат для кожного місяця. Ми використовуємо функцію DATE для визначення першого дня місяця (наприклад, 5/1/2021). Ми можемо зробити це, «жорстко кодуючи» дату у формулі:
| 1 | "> =" & ДАТА (2021,5,1) |
Або, що ще краще, ми можемо зробити критерії гнучкими, посилаючись на дату всередині комірки E3, витягуючи рік і місяць дати (і встановлюючи день рівним 1), як показано у прикладі вище:
| 1 | "> =" & ДАТА (РІК (E3), МІСЯЦЬ (E3), 1) |
Щоб визначити останній день місяця, ми можемо скористатися функцією EOMONTH:
| 1 | "<=" & EOMONTH (E3,0) |
Поєднавши всі ці критерії, ми можемо записати таку формулу SUMIFS:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & ДАТА (РІК (E3), МІСЯЦЬ (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Блокування посилань на клітинки
Щоб полегшити читання наших формул, ми показали формули без заблокованих посилань на клітинки:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & ДАТА (РІК (E3), МІСЯЦЬ (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Але ці формули не працюватимуть належним чином при копіюванні та вставці в інше місце у файлі. Замість цього, ви повинні використовувати заблоковані посилання на клітинки, як це:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" & ДАТА (РІК (E3), МІСЯЦЬ (E3), 1), $ B $ 3: $ B $ 9, "<=" & EOMONTH (E3,0)) |
Прочитайте нашу статтю про блокування посилань на клітинки, щоб дізнатися більше.
Форматування значень місяця
У цьому прикладі ми перерахували місяці у стовпці Е. Ці значення місяця насправді є датами, відформатованими так, щоб опустити день, за допомогою форматування спеціального числа.
Спеціальний формат даних - "ммм рррр", щоб показати травень 2022 року.
Сума за місяць за кілька років
У наведеному вище прикладі підсумовуються дані з датами, які потрапили протягом певного місяця та року. Замість цього ви можете підсумовувати дані з датами, які припадають на місяць у будь -якому році, використовуючи функцію SUMPRODUCT.
| 1 | = ПІДПРИЄМСТВО (C3: C8,-(МІСЯЦЬ (B3: B8) = МІСЯЦЬ (G3))) |
У цьому прикладі ми використовуємо функцію SUMPRODUCT для виконання складних обчислень "сума якщо". Пройдемо формулу.
Ось наша остаточна формула:
| 1 | = ПІДПРИЄМСТВО (C3: C8,-(МІСЯЦЬ (B3: B8) = МІСЯЦЬ (G3))) |
По -перше, функція SUMPRODUCT містить список Кількість продажів для кожного Дата продажу а потім порівнює місяць кожного Дата продажу проти зазначеного Місяць, повертаючи TRUE, якщо місяці збігаються, або FALSE, якщо вони не відповідають:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34},-({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})) |
Далі подвійні тире (-) перетворюють значення TRUE і FALSE в 1s і 0s:
| 1 | = ПОВНІННЯ ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
Потім функція SUMPRODUCT перемножує кожну пару записів у масивах:
| 1 | = ПІДПРИЄМСТВО ({30; 0; 51; 0; 17; 0}) |
Нарешті, числа в масиві підсумовуються:
| 1 | =98 |
Докладніше про використання булевих операторів та команди “-” у функції SUMPRODUCT можна знайти тут
Сума за місяць у Таблицях Google
Ці формули працюють точно так само в Google Таблицях, як і в Excel.
