Обща формула
=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 с INDIRECT функции по следния начин:
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, за да генерираме масива от дати директно, без да има нужда от INDIRECT или ROW.
