Общая формула
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)))=dow))
Резюме
Для подсчета дней недели (понедельник, пятница, воскресенье и т. Д.) Между двумя датами вы можете использовать формулу массива, которая использует несколько функций: СУММПРОИЗВ, ДЕНЬ НЕДЕЛИ, СТРОКА и КОСВЕННО. В показанном примере формула в ячейке E6 выглядит так:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B6&":"&C6)))=D6))
В универсальной версии формулы start = дата начала, end = дата окончания, а dow = день недели.
Объяснение
По сути, эта формула использует функцию WEEKDAY для проверки количества дат, чтобы увидеть, попадают ли они в данный день недели (dow), и функцию SUMPRODUCT для подведения итогов.
Если задана дата, WEEKDAY просто возвращает число от 1 до 7, которое соответствует определенному дню недели. По умолчанию 1 = воскресенье и 7 = суббота. Итак, 2 = понедельник, 6 = пятница и так далее.
Уловка этой формулы заключается в понимании того, что даты в Excel - это просто порядковые номера, начинающиеся 1 января 1900 года. Например, 1 января 2016 года - это серийный номер 42370, а 8 января - 42377. Даты в Excel выглядят как даты только тогда, когда применяется числовой формат даты.
Итак, возникает вопрос - как вы можете создать массив дат, который вы можете передать в функцию WEEKDAY, чтобы узнать соответствующие дни недели?
Ответ - использовать СТРОКУ с НЕПРЯМЫМИ функциями, например:
ROW(INDIRECT(date1&":"&date2))
INDIRECT позволяет интерпретировать объединенные даты «42370: 42377» как номера строк. Затем функция СТРОКА возвращает такой массив:
(42370;42371;42372;42373;42374;42375;42376;42377)
Функция WEEKDAY оценивает эти числа как даты и возвращает этот массив:
(6;7;1;2;3;4;5;6)
который проверяется для данного дня недели (в данном случае 6 из D6). После того, как результаты теста преобразуются в единицы и нули с двойным дефисом, этот массив обрабатывается функцией SUMPRODUCT:
(1;0;0;0;0;0;0;1)
Что возвращает 2.
С ПОСЛЕДОВАТЕЛЬНОСТЬЮ
С помощью новой функции SEQUENCE эту формулу можно упростить примерно так:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(end-start+1,1,start,1))=dow))
В этой версии мы используем SEQUENCE для генерации массива дат напрямую, без необходимости в INDIRECT или ROW.
