KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

Суммирование значений по номеру недели или дню недели в Excel

АвторSiluviaДата изменения

В этом пошаговом руководстве показаны формулы, которые помогут вам суммировать значения в заданном диапазоне на основе указанного номера недели или дня недели в Excel.

Как суммировать значения по Номер недели в Excel?
Как суммировать значения по дню недели в Excel?

Как суммировать значения по номеру недели в Excel?

Как показано на приведённом ниже снимке экрана, чтобы суммировать значения в столбце «Сумма» по датам, относящимся к указанному номеру недели, мы предлагаем две формулы.

doc-sum-by-week-number-weekday-1

Универсальные формулы

=SUMIFS( value_range, helper_column_range, week_number)

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

Аргументы

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

Как использовать эту формулу?

Использование формулы SUMIFS и вспомогательного столбца

Чтобы использовать формулу SUMIFS, сначала примените функцию WEEKNUM, чтобы создать вспомогательный столбец и рассчитать соответствующий номер недели для дат из столбца «Дата».

=WEEKNUM (serial_num, [return_type])

Функция WEEKNUM возвращает номер недели для указанной даты в году.

1. В данном случае я выбираю ячейку (F5), расположенную рядом с первым значением в столбце «Сумма».

2. Введите в неё приведённую ниже формулу и нажмите клавишу Enter, чтобы получить номер недели первой даты. Выделите ячейку с результатом и перетащите маркер автозаполнения вниз, чтобы получить номера остальных недель. См. снимок экрана:

=WEEKNUM(C5)

doc-sum-by-week-number-weekday-2

3. Выберите ячейку для отображения общей суммы, введите в неё приведённую ниже формулу и нажмите клавишу EnterЗатем выделите ячейку с результатом и перетащите её маркер автозаполнения вниз, чтобы быстро получить остальные значения.

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

doc-sum-by-week-number-weekday-3

Использование формулы SUMPRODUCT

При решении этой задачи с помощью формулы SUMPRODUCT вспомогательный столбец не требуется.

1. Выберите пустую ячейку, введите в неё приведённую ниже формулу и нажмите клавишу Enter.

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

2. Выделите ячейку с результатом и перетащите её маркер автозаполнения вниз, чтобы получить остальные результаты в соответствии с вашими потребностями.

doc-sum-by-week-number-weekday-4

Объяснение формулы

=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» в 1 и 0. В результате вы получите новый массив: {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, чтобы отдельно обозначить понедельник–воскресенье.

doc-sum-by-week-number-weekday-6

Универсальная формула

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

Аргументы

Диапазон_дат: Диапазон дат, в котором вы хотите оценить заданные дни недели;
Диапазон_значений: Диапазон значений, которые вы хотите суммировать на основе дня недели;
2: Число 2 представляет числа от 1 (понедельник) до 7 (воскресенье);
Номер_дня_недели: Номер дня недели, используемый в качестве критерия для суммирования значений. Может быть Ссылка на ячейку или числом от 1 до 7.

Как использовать эту формулу?

1. Выберите ячейку, расположенную рядом с той, в которой содержится число 1. В данном случае я выбираю ячейку I6.

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

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

doc-sum-by-week-number-weekday-7

Примечание. Если вам нужно просто просуммировать значения за один конкретный день недели, например пятницу, укажите число 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)

WEEKDAY($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 преобразуются в 1 и 0, а затем умножаются на значения в диапазоне 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.

Связанные функции

Функция WEEKNUM в Excel
Функция WEEKNUM в Excel возвращает номер недели для указанной даты в году, при этом отсчёт недель начинается с 1 января.

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

Функция WEEKDAY в Excel
Функция WEEKDAY в Excel возвращает целое число от 1 до 7, обозначающее день недели для указанной даты.

Связанные формулы

Sum values by month (with or without year)
В этом руководстве подробно разбираются две формулы, которые позволяют суммировать значения за определённый месяц и год — или только за нужный месяц, игнорируя год, в Excel.

Суммирование значений по неделе заданной даты
В этом руководстве вы узнаете, как суммировать значения за неделю, в которую попадает указанная дата.

Суммирование каждых n строк или столбцов в диапазоне
В этом руководстве описаны две формулы на основе функций SUM и OFFSET для суммирования каждых n строк или столбцов в ограниченном диапазоне Excel.

Суммирование каждой n-й строки или столбца в диапазоне
В этом руководстве показано, как с помощью функций SUMPRODUCT, MOD и COLUMN создать формулу для суммирования каждой n-й строки или столбца в заданном диапазоне Excel.

Лучшие инструменты для повышения продуктивности в Office

Kutools для Excel — Помогает Вам выделяться из толпы

🤖KUTOOLS AI Помощник: Революционизируйте Анализ данных на основе:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и создания диаграмм|  Вызова Расширенные функции…
Популярные функции:Поиск, выделение или Отметить дубликаты  |  Удалить пустые строки  |  Объединить столбцы или ячеек без потери данных  |  Округление без использования формул…
Расширенный VLookup:Несколько критериев  |  Несколько значений  |  Между несколькими листами  |  Распознавание нечетких соответствий…
Расш. Раскрывающийся список:Простой выпадающий список  |  Зависимый выпадающий список  |  Выпадающий список с множественным выбором…
Управление столбцами:Добавление определённого количества столбцов  |  Перемещение столбцов  |  Переключение видимости скрытых столбцов  |Сравнение столбцов для Выбрать одинаковые/разные ячейки…
Избранные функции:Сетка фокусировки  |  Просмотр дизайна  |  Улучшенная строка формулы  |  Управление книгами и листами|Библиотека ресурсов(автотекст)|  Выбор даты  |  Объединить листы  |  Шифрование/Расшифровать ячейки  |  Отправка писем по списку  |  Супер фильтр  |  Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы…)|  50+Типыдиаграмм(Диаграмма Ганта…)|  40+ Практические формулы(Рассчитать возраст на основе даты рождения…)|  19 Инструментывставки(Вставить QR-код,Вставка изображения по пути…)|  12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют…)|  7 Объединить и разделитьинструменты(Расширенное объединение строк,Разделение ячеек Excel…)|…и многое другое
Используйте Kutools на предпочитаемом языке — поддерживает английский, испанский, немецкий, французский, китайский и ещё 40+ языков!

Kutools для Excel предлагает более 300 функций,гарантируя, что всё необходимое всегда под рукой…

Office Tab — Включает вкладочное чтение и редактирование в Microsoft Office (включая Excel)

  • Переключайтесь между десятками открытых документов всего за секунду!
  • Сокращает сотни кликов мышью каждый день — забудьте о «мышечной руке» раз и навсегда!
  • Повышает продуктивность на 50 % при одновременном просмотре и редактировании нескольких документов.
  • Добавляет эффективность вкладок в Office (включая Excel) — как в Chrome, Edge и Firefox.