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

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

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

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


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

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

Общие формулы

=SUMIFS( value_range, helper_column_range, week_number)

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

аргументы

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

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

Использование формулы СУММЕСЛИМН и вспомогательного столбца

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

=WEEKNUM (serial_num, [return_type])

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

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

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

=WEEKNUM(C5)

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

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

Используя формулу СУММПРОИЗВ

Вам не нужно создавать вспомогательный столбец при использовании формулы СУММПРОФУКТ для решения этой проблемы.

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

=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 функция СУММЕСЛИМН суммирует значения в диапазоне E5: E14, если номера недель в вспомогательном столбце F5: F14 соответствуют данной неделе в H6.

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

WEEKNUM (5 канадских долларов: 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 и возвращает новый массив: {ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ};
- {ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}: Эти два знака минус преобразуют «ИСТИНА» и «ЛОЖЬ» в единицы и нули. Здесь вы получите новый массив как {1; 0; 1; 1; 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}): Функция СУММПРОИЗВ суммирует все числа в массиве и возвращает окончательный результат 1428 + 2010 = 3438.

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

Как показано на снимке экрана ниже, чтобы суммировать значения в столбце «Сумма» по определенному дню недели в Excel, вы можете применить функцию СУММПРОИЗВ вместе с функцией ДЕНЬНЕД.

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

Общая формула

=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)

Примечание: Если вам просто нужно суммировать значения на основе одного указанного дня недели, например пятницы, вы можете напрямую ввести число 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 (5 канадских долларов: 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 и возвращается новый массив: {ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ};
{ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ} * $ E $ 5: $ E $ 14: Здесь ИСТИНА и Ложь преобразуются в единицы и нули, а затем умножаются на значения в E1: E0 для возврата массива: {5; 14; 0; 0; 2288; 0; 0; 0; 1325; 0};
SUMPRODUCT{0;0;2288;0;0;0;1325;0;0;0}: Функция СУММПРОИЗВ суммирует все числа в массиве и возвращает окончательный результат 2288 + 1325 = 3613.

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

Функция ЧИСЛО НЕДЕЛ в Excel
Функция СУММЕСЛИМН в Excel возвращает номер недели указанной даты в году, который начинает отсчет недель с 1 января.

Функция СУММПРОИЗВ в Excel
Функцию СУММПРОИЗВ в Excel можно использовать для умножения двух или более столбцов или массивов, а затем получения суммы произведений.

Функция ДЕНЬ НЕДЕЛИ в Excel
Функция Excel EOMONTH возвращает целое число от 1 до 7, представляющее день недели для заданной даты в Excel.


Родственные формулы

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

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

Суммируйте каждые n строк или столбцов в диапазоне
В этом руководстве объясняются две формулы на основе функций СУММ и СМЕЩЕНИЕ для суммирования каждых n строк или столбцов в указанном диапазоне в Excel.

Суммируйте каждую n-ю строку или столбец в диапазоне
В этом руководстве показано, как создать формулу на основе функций СУММПРОИЗВ, МОД и СТОЛБЕЦ для суммирования каждой n-й строки или столбца в диапазоне в Excel.


Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.

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

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

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (0)
Оценок пока нет. Оцените первым!
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места