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

Підсумуйте значення за числом тижня або днем ​​тижня в Excel

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

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

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

Як показано на знімку екрана нижче, для підсумовування значень у стовпці «Сума» на основі дат, на які припадає даний номер тижня, ми надаємо дві формули.

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

=SUMIFS( value_range, helper_column_range, week_number)

=SUMPRODUCT(--(WEEKNUM(date_range+0,1)=week_number),value_range)

Аргументи

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

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

Використання формули SUMIFS і допоміжного стовпця

Для використання SUMIFS формулу, по-перше, вам потрібно застосувати ТИЖДЕНЬ функція для створення допоміжного стовпця для обчислення відповідних номерів тижнів для дат у стовпці Дата.

=WEEKNUM (serial_num, [return_type])

Функція WEEKNUM повертає номер тижня заданої дати в році.

1. У цьому випадку я вибираю клітинку (F5) поруч із першим значенням у стовпці Сума.

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

=WEEKNUM(C5)

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

=SUMIFS($E$5:$E$14, $F$5:$F$14, H6)

Використання формули ПІДСУМУ ПРОИЗВОДУ

Вам не потрібно створювати допоміжний стовпець при використанні формули SUMPROFUCT для вирішення цієї проблеми.

1. Виділіть порожню комірку, введіть в неї формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ

=SUMPRODUCT(--(WEEKNUM($C$5:$C$14+0,1)=G6),$E$5:$E$14)

2. Виберіть комірку результату, перетягніть її Ручка автозаповнення вниз, щоб отримати інші результати відповідно до ваших потреб.

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

=SUMIFS($E$5:$E$14, $F$5:$F$14, H6)

Після обчислення номерів тижнів за допомогою функції WEEKNUM функція SUMIFS сумує значення в діапазоні E5:E14, якщо номери тижнів у допоміжному стовпці F5:F14 відповідають заданому тижню в H6.

=SUMPRODUCT(--(WEEKNUM($C$5:$C$14+0,1)=G6),$E$5:$E$14)

WEEKNUM($C$5:$C$14+0,1): Функція WEEKNUM обчислює номер кожного тижня для дат у діапазоні C5:C14 і повертає такий масив: {1;1;5;6;6;9;11;11;13;14};
{1;1;5;6;6;9;11;11;13;14}=G6: Тут порівнюється кожен номер тижня в масиві з заданим номером тижня в G6 і повертає новий масив: {TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE};
--{ІСТИНА;ІСТИНА;ХИБНЯ;ХИБНЯ;ХИБНЯ;ХИБНЯ;ХИБНЯ;ХИБНЯ;ХИБНЯ;ХИБНЯ}: Ці два знаки мінус перетворюють «TRUE» і «FALSE» в 1s і 0s. Тут ви отримаєте новий масив як {1;1;0;0;0;0;0;0;0;0};
{1;1;0;0;0;0;0;0;0;0},{1428;2010;2288;1831;2069;1728;1325;1469;1023;1100}: кожна пара записів у масивах множиться разом і повертає {1428;2010;0;0;0;0;0;0;0;0};
SUMPRODUCT({1428;2010;0;0;0;0;0;0;0;0}): функція SUMPRODUCT підсумовує всі числа в масиві і повертає кінцевий результат 1428+2010=3438.

Як підсумувати значення по днях тижня в Excel?

Як показано на знімку екрана нижче, щоб підсумувати значення в стовпці «Сума» до певного дня тижня в Excel, ви можете застосувати функцію SUMPRODUCT разом із функцією WEEKDAY, щоб це зробити.

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

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

=SUMPRODUCT((WEEKDAY(date_range,2)=weekday_number)*value_range)

Аргументи

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

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

1. Виділіть клітинку поруч із коміркою номер 1. Тут я вибираю I6.

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

=SUMPRODUCT((WEEKDAY($C$5:$C$14,2)=H6)*$E$5:$E$14)

Примітка. Якщо вам потрібно просто підсумувати значення на основі одного вказаного дня тижня, наприклад, п’ятниці, ви можете безпосередньо ввести число 5 у формулу, як показано нижче.

=SUMPRODUCT((WEEKDAY($C$5:$C$14,2)=5)*$E$5:$E$14)

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

=SUMPRODUCT((WEEKDAY($C$5:$C$14,2)=H6)*$E$5:$E$14)

БУДНІ ($C$5:$C$14,2): Функція WEEKDAY повертає цілі числа від 1 до 7 для представлення дня тижня з понеділка по неділю в діапазоні C5:C14: {5;7;1;3;6;7;1;5;5;6};
{5;7;1;3;6;7;1;5;5;6}=H6: Тут порівнюється кожен день тижня в масиві з заданим номером дня тижня в H6 і повертається новий масив: {FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE};
{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}*$E$5:$E$14: Тут значення TRUE і False перетворюються в 1s і 0s, а потім помножуються на значення в E5:E14, щоб повернути масив: {0;0;2288;0;0;0;1325;0;0;0};
SUMPRODUCT{0;0;2288;0;0;0;1325;0;0;0}: функція SUMPRODUCT підсумовує всі числа в масиві і повертає кінцевий результат 2288+1325=3613.

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

Функція Excel WEEKNUM
Функція Excel SUMIFS повертає номер тижня заданої дати в році, який починає відлік тижнів з 1 січня.

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

Функція Excel WEEKDAY
Функція Excel EOMONTH повертає ціле число від 1 до 7 для представлення дня тижня для певної дати в Excel.

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

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

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

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

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

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

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

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер 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 (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations