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