Note: The other languages of the website are Google-translated. Back to English

Підсумуйте значення за числом тижня або днем ​​тижня в 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;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 для Excel надає 300 потужних розширених функцій (об’єднання робочих книг, підсумовування за кольором, розділення вмісту клітинок, перетворення дати тощо) і заощаджує 80% вашого часу.

  • Розроблений для 1500 сценаріїв роботи, допомагає вирішити 80% проблем Excel.
  • Щодня зменшуйте тисячі клацань клавіатури та миші, знімайте втомлені очі та руки.
  • Станьте експертом Excel за 3 хвилини. Більше не потрібно згадувати будь-які болісні формули та коди VBA.
  • Безкоштовна 30-денна безкоштовна пробна версія. 60-денна гарантія повернення грошей. Безкоштовне оновлення та підтримка протягом 2 років.
Стрічка Excel (із встановленим Kutools для Excel)

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

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Приносить ефективні вкладки в Office (включаючи Excel), як і Chrome, Firefox та New Internet Explorer.
Знімок екрана Excel (із встановленою вкладкою Office)
Коментарі (0)
Поки немає оцінок. Оцініть першим!
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL