Перейти до основного матеріалу

Сума значень за місяцями (з роком або без) в Excel

У цьому підручнику докладно демонструються дві формули, які допомагають підсумовувати значення за певним місяцем і роком або підсумовувати значення за певним місяцем ігнорувати рік у Excel.

Як підсумувати значення за місяцем і роком?
Як підсумувати значення за місяцями, ігноруючи рік?


Як підсумувати значення за місяцем і роком?

Як показано на скріншоті нижче, щоб підсумувати загальну суму за жовтень того ж року (2019), ви можете використовувати формулу з функціями SUMIFS і EOMONTH.

Загальні формули

=SUMIFS(value_range,date_range,">="&date,date_range,"<="&EOMONTH(date,0))

Аргументи

Діапазон_значення: діапазон значень, які потрібно підсумувати;
Проміжок часу: діапазон дат, у якому потрібно оцінити місяць і рік;
Дата: позначає перший день місяця, на основі якого потрібно підсумувати. Це може бути посилання на клітинку або точна дата, узята в подвійні лапки.

Як користуватися цією формулою?

У цьому випадку $ D $ 3: $ D $ 11 - це діапазон1, Китай - критерій1, який розміщується у $ G3, $ E $ 3: $ E $ 11 - діапазон2, а H $ 2 - посилання на клітинку, що містить критерії2.

1. Виберіть порожню клітинку, щоб вивести результат. У цьому випадку я вибираю H6.

2. Скопіюйте або введіть наведену нижче формулу у вибрану клітинку та натисніть Що натомість? Створіть віртуальну версію себе у ключ. Виберіть комірку результату, перетягніть маркер автозаповнення вниз, щоб отримати суму за інший місяць і рік.

=SUMIFS($E$5:$E$10,$B$5:$B$10,">="&G6,$B$5:$B$10,"<="&EOMONTH(G6,0))

примітки: У діапазоні G6:G7 я ввів фактичні дати у форматі «м/д/рррр» у клітинки, а потім вручну відформатував дати як «ммм-рррр», щоб відображати лише місяць і рік. Якщо ви вручну введете місяць і рік як текст у клітинку, ви не отримаєте правильного результату.

Пояснення формули

=SUMIFS($E$5:$E$10,$B$5:$B$10,">="&G6,$B$5:$B$10,"<="&EOMONTH(G6,0))

$E$5:$E$10,$B$5:$B$10,">="&G6: Тут збігаються дати в діапазоні дат E5:E10, які більше або дорівнюють першому дню місяця в G6.
$B$5:$B$10,"<="&EOMONTH(G6,0): Функція EOMONTH повертає серійний номер останнього дня місяця, зазначеного датою в G6. Як і вище, тут збігаються дати в діапазоні дат E5:E10, менші або рівні останньому дню місяця.
Потім функція SUMIFS підсумовує загальну суму по місяцях за двома вищеназваними критеріями.

Як підсумувати значення за місяцями, ігноруючи рік?

За допомогою наведеної вище формули ви можете підсумовувати значення лише за місяцями з одного року. Щоб підсумувати значення за місяцями за різні роки, можна застосувати формулу на основі функцій SUMPROFUCT і MONTH.

Загальна формула

=SUMPRODUCT((MONTH(date_range)=month)*value_range)

Аргументи

Проміжок часу: діапазон дат, у якому потрібно оцінити місяць;
місяць: це число, що представляє місяць, на основі якого ви будете підсумовувати значення;
Діапазон_значення: діапазон значень, які потрібно підсумувати;

Як користуватися цією формулою?

1. Виберіть порожню клітинку. Тут я вибираю H6.

2. Скопіюйте або введіть наведену нижче формулу та натисніть клавішу Enter, щоб отримати результат.

=SUMPRODUCT((MONTH($B$5:$B$10)=10)*$E$5:$E$10)

примітки: у формулі число 10 позначає місяць жовтень. Цифри від 1 до 12 окремо представляють січень – грудень, і ви можете змінити їх відповідно до ваших потреб.

Пояснення формули

=SUMPRODUCT((MONTH($B$5:$B$10)=10)*$E$5:$E$10)

МІСЯЦЬ($B$5:$B$10)=10: функція MONTH витягує номер місяця з кожної клітинки дати в діапазоні B5:B10: {10;10;12;12;10;10} і порівнює кожне число в масиві з числом 10: {10;10; 12;12;10;10}=10, нарешті він повертає масив TRUE FALSE {TRUE;TRUE;FALSE;FALSE;TRUE;TRUE}.
{TRUE;TRUE;FALSE;FALSE;TRUE;TRUE}*$E$5:$E$10: цей масив множиться на кожну суму, пов’язану з жовтнем, і ви отримаєте новий масив, такий як {1428;2010;0;0;2069;1728};
SUMPRODUCT{1428;2010;0;0;2069;1728}: функція SUMPRODUCT нарешті підсумовує всі числа в масиві і повертає результат у вигляді 7235.

Супутні функції

Функція Excel SUMIFS
Функція Excel SUMIFS допомагає підсумовувати значення клітинок на основі кількох критеріїв

Функція Excel SUMPRODUCT
Функцію Excel SUMPRODUCT можна використовувати для множення двох або більше стовпців або масивів разом, а потім для отримання суми продуктів.

Функція Excel EOMONTH
Функція Excel EOMONTH отримує дату, яка є певною кількістю місяців до або після заданої дати початку

Функція Excel МІСЯЦЬ
Функція Excel MONTH витягує номер місяця з формату дати в Excel


Пов’язані формули

Сума значень за тиждень певної дати
У цьому посібнику розповідається про те, як підсумувати значення за тижнем, на який припадає певна дата

Сума значень за тижнем або днем ​​тижня
У цьому підручнику покроково демонструються формули, які допоможуть вам підсумувати значення в певному діапазоні на основі вказаного номера тижня або дня тижня в Excel.

Підсумуйте кожні n рядків або стовпців діапазону
У цьому посібнику пояснюються дві формули на основі функцій SUM і OFFSET для підсумовування кожні n рядків або стовпців у визначеному діапазоні в Excel.

Підсумуйте кожен n-й рядок або стовпець діапазону
Цей підручник покаже вам, як створити формулу на основі функцій SUMPRODUCT, MOD і COLUMN для підсумовування кожного n-го рядка або стовпця в діапазоні в Excel.


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер VLookup: Кілька критеріїв  |  Множинне значення  |  На кількох аркушах  |  Нечіткий пошук...
Adv. Випадаючий список: Легкий спадний список  |  Залежний спадний список  |  Виберіть розкривний список, що вибирається...
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  Порівняти стовпці з Виберіть однакові та різні клітини ...
Особливості: Фокус сітки  |  Перегляд дизайну  |  Велика панель формул  |  Диспетчер робочих книг і аркушів | Бібліотека ресурсів (автотекст)  |  Вибір дати  |  Об’єднайте робочі аркуші  |  Шифрування/розшифрування клітинок  |  Надсилайте листи за списком  |  Супер фільтр  |  Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи ...)  |  50 + Графік типи (діаграма Ганта ...)  |  40+ Практичний Формули (Розрахуйте вік на основі дня народження ...)  |  19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху ...)  |  12 Перетворення Tools (Числа до слів, Валютна конверсія ...)  |  7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділити клітинки Excel ...)  |  ... і більше

Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...


Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
Comments (1)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
terima kasih banyak, saya menemukan artikel ini ketika butuh menyelesaikan perhitungan dengan bantuan excel. alhamdulillaah fungsinya bekerja. penjelasannya cukup mudah untuk dimengerti
Rated 5 out of 5
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations