Загальна формула
=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 з непрямим:
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.
