=СУММЕСЛИМН ( значение ; дата ; «> =» & A1 ; дата ; «<» & A1 + 7 )
Суммируя недели, вы можете использовать формулу, основанную на функции СУММЕСЛИМН. В показанном примере, формула в F4 является:
=СУММЕСЛИМН($C$4:$C$12; $B$4:$B$12; «>=»&E4; $B$4:$B$12; «<» &E4+7)
Функция СУММЕСЛИМН может суммировать диапазоны на основе нескольких критериев.
В этой задаче мы настроим СУММЕСЛИМН подводить суммы по неделям, используя два критерия: (1) сроки больше или равны дате в колонке Е, (2) дата меньше, чем дата в колонке Е плюс 7 дней:
=СУММЕСЛИМН ( сумма ; дата ; «> =» & E4 ; дата ; «<» & E4 + 7 )
Когда эта формула копируется вниз, СУММЕСЛИМН генерирует сумму за каждую неделю.
Даты в колонке Е являются понедельниками. Первая дата жестко закодирована, а остальные понедельники рассчитываются с помощью простой формулы:
= E4 + 7
Сумма по будням
=СУММПРОИЗВ((ДЕНЬНЕД ( даты ) = Номер_Дня) * значения )
Подводя данные по будним дням (т.е. сумма по понедельникам, вторникам, средам, четвергам и пятницам), вы можете использовать функцию СУММПРОИЗВ вместе с функцией ДЕНЬНЕД.
В показанном примере, формула в H4 является:
=СУММПРОИЗВ((ДЕНЬНЕД($B$4:$B$11;2)=G4)*$D$4:$D$11)
СУММПРОИЗВ вместо СУММЕСЛИ
Вы можете спросить , почему мы не используем СУММЕСЛИ или СУММЕСЛИМН функцию? Это очевидный способ подвести отчет по дням недели. Тем не менее, без добавления вспомогательного столбца со значениями будних дней, нет никакого способа , чтобы создать критерии для СУММЕСЛИ, который принимает во внимание рабочие дни.
Вместо этого мы используем удобную функцию СУММПРОИЗВ, которая обрабатывает массивы изящно, без необходимости использовать Ctrl + Shift + Enter.
Мы используем СУММПРОИЗВ только с одним аргументом, который состоит из этого выражения:
( Пн — пт ( даты ; 2 ) = G4 ) * АМТС
Работая изнутри, функция ДЕНЬНЕД конфигурируется с дополнительным аргументом 2, что приводит к его рассчитать номера 1-7 за дни, с понедельника по воскресенье, соответственно. Это не обязательно, но это делает ему легче перечислить дни в порядке и забрать номера в столбце G в определенной последовательности.
ДЕНЬНЕД оценивает каждое значение в указанном диапазоне дат «» и рассчитывает число. Результатом является массив следующим образом:
{3; 5; 3; 1; 2; 2; 4; 2}
Числа, рассчитанные ДЕНЬНЕД затем сравнивают со значением в G4, которое равно 1.
{3; 5; 3; 1; 2; 2; 4; 2} = 1
Результатом является массив истина/ложь значений.
{ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}
Затем этот массив умножается на значения в названном «АМТС» диапазоне. СУММПРОИЗВ работает только с числами (не текстом или булевыми значениями), но математические операции автоматически преобразуют ИСТИНА/ЛОЖЬ значения в единицы и нули, так что мы имеем:
{0; 0; 0; 1; 0; 0; 0; 0} * {100; 250; 75; 275; 250; 100; 300; 125}
Который дает:
{0; 0; 0; 275; 0; 0; 0; 0}
С помощью всего этого одного массива в процессе СУММПРОИЗВ суммирует элементы и рассчитывает результат.