Обща формула
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Обобщение
За да изчислите работното време между две дати с персонализиран график, можете да използвате формула, базирана на функциите WEEKDAY и SUMPRODUCT, с помощта на ROW, INDIRECT и MID. В показания пример формулата във F8 е:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Което връща 36 часа, според персонализиран график, при който 8 часа се работят от понеделник до петък, 4 часа се работят в събота, а понеделник на 3 септември е почивен ден. Празниците се предоставят като посочения диапазон G6: G8. Графикът на работа се въвежда като текстов низ в колона D и може да се променя по желание.
Забележка: Това е формула на масив, която трябва да се въведе с Control + Shift + Enter. Ако имате стандартен 8-часов работен ден, тази формула е по-проста.
Обяснение
В основата си тази формула използва функцията WEEKDAY, за да определи деня от седмицата (т.е. понеделник, вторник и т.н.) за всеки ден между двете дати. WEEKDAY връща число между 1 и 7. С настройките по подразбиране неделя = 1 и събота = 7.
Трикът с тази формула е сглобяването на масив от дати, които можете да подадете във функцията WEEKDAY. Това се прави с ROW с INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW интерпретира обединените дати като номера на редове и връща масив като този:
(43346;43347;43348;43349;43350;43351;43352)
Всяко число в масива представлява дата. След това функцията WEEKDAY оценява масива и връща масив от стойности от делничния ден:
(2;3;4;5;6;7;1)
Тези числа съответстват на деня от седмицата на всяка дата. Те се предоставят на функцията MID като аргумент за начален номер, заедно със стойността в D6, "0888884" за текст:
MID("0888884",(2;3;4;5;6;7;1),1)
Тъй като даваме на MID масив от начални числа, той връща масив от резултати като този:
("8";"8";"8";"8";"8";"4";"0")
Тези стойности съответстват на отработените часове на всеки ден от началната дата до крайната дата. Обърнете внимание, че стойностите в този масив са текст, а не числа. За да конвертираме в реални числа, умножаваме по втори масив, създаден за управление на празници, както е обяснено по-долу. Операцията по математика принуждава текста към числови стойности.
Почивни дни
За да се справим с празници, използваме ISNA, MATCH и посочения диапазон "празници" по следния начин:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Този израз използва MATCH за намиране на дати, които са в празниците на именувания диапазон, използвайки същия масив от дати, генерирани по-горе с INDIRECT и ROW. MATCH връща номер, когато са открити празници и грешка # N / A, когато не е. Функцията ISNA "обръща" резултатите, така че TRUE представлява празници, а FALSE - непразнични. ISNA връща масив или резултати като този:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
И накрая, двата масива се умножават помежду си в SUMPRODUCT. Операцията по математика принуждава TRUE и FALSE до 1 и нула, а текстовите стойности в първия масив до числови стойности (както е обяснено по-горе), така че в края имаме:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
След умножението имаме един масив вътре в SUMPRODUCT, съдържащ всички работни часове в периода от време:
=SUMPRODUCT((0;8;8;8;8;4;0))
След това SUMPRODUCT сумира всички елементи в масива и връща резултат от 36.
