Formuła ogólna
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Podsumowanie
Aby obliczyć godziny pracy między dwiema datami z niestandardowym harmonogramem, możesz użyć formuły opartej na funkcjach WEEKDAY i SUMPRODUCT, z pomocą ROW, INDIRECT i MID. W pokazanym przykładzie formuła w F8 to:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Co daje 36 godzin, w oparciu o niestandardowy harmonogram, w którym 8 godzin jest przepracowanych od poniedziałku do piątku, 4 godziny w sobotę, a poniedziałek 3 września jest dniem wolnym od pracy. Święta są dostarczane jako nazwany zakres G6: G8. Harmonogram pracy jest wprowadzany jako ciąg tekstowy w kolumnie D i można go dowolnie zmieniać.
Uwaga: to jest formuła tablicowa, którą należy wprowadzić za pomocą kombinacji Control + Shift + Enter. Jeśli masz standardowy 8-godzinny dzień pracy, ta formuła jest prostsza.
Wyjaśnienie
Zasadniczo ta formuła wykorzystuje funkcję WEEKDAY do wyliczenia dnia tygodnia (np. Poniedziałek, wtorek itp.) Dla każdego dnia między dwoma podanymi datami. WEEKDAY zwraca liczbę z przedziału od 1 do 7. Przy ustawieniach domyślnych niedziela = 1 i sobota = 7.
Sztuczka w tej formule polega na zestawieniu tablicy dat, którą można wprowadzić do funkcji WEEKDAY. Odbywa się to za pomocą ROW z INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW interpretuje połączone daty jako numery wierszy i zwraca tablicę w następujący sposób:
(43346;43347;43348;43349;43350;43351;43352)
Każda liczba w tablicy reprezentuje datę. Następnie funkcja WEEKDAY oblicza tablicę i zwraca tablicę wartości dni tygodnia:
(2;3;4;5;6;7;1)
Liczby te odpowiadają dniu tygodnia każdej daty. Są one przekazywane do funkcji MID jako argument numeru początkowego, wraz z wartością w D6, „0888884” dla tekstu:
MID("0888884",(2;3;4;5;6;7;1),1)
Ponieważ podajemy MID tablicę liczb początkowych, zwraca tablicę wyników w następujący sposób:
("8";"8";"8";"8";"8";"4";"0")
Wartości te odpowiadają godzinom przepracowanym każdego dnia od daty rozpoczęcia do daty zakończenia. Zwróć uwagę, że wartości w tej tablicy to tekst, a nie liczby. Aby przekonwertować na rzeczywiste liczby, mnożymy przez drugą tablicę utworzoną do zarządzania świętami, jak wyjaśniono poniżej. Operacja matematyczna przekształca tekst na wartości liczbowe.
Wakacje
Do obsługi świąt używamy ISNA, MATCH i nazwanego zakresu „święta” w następujący sposób:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
To wyrażenie używa funkcji PODAJ.POZYCJĘ do zlokalizowania dat, które należą do nazwanych dni wolnych od pracy, przy użyciu tej samej tablicy dat wygenerowanej powyżej z parametrami ADR.POŚR. I WIERSZ. PODAJ.POZYCJĘ zwraca liczbę w przypadku znalezienia świąt i błąd # N / D, gdy nie. Funkcja ISNA odwraca wyniki, tak aby PRAWDA reprezentowała dni wolne, a FAŁSZ reprezentowała dni inne niż dni wolne. ISNA zwraca tablicę lub wyniki w następujący sposób:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Na koniec obie tablice są mnożone przez siebie wewnątrz SUMPRODUCT. Operacja matematyczna przekształca TRUE i FALSE na 1 i zero, a wartości tekstowe w pierwszej tablicy na wartości liczbowe (jak wyjaśniono powyżej), więc na końcu mamy:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Po pomnożeniu mamy pojedynczą tablicę wewnątrz SUMPRODUCT zawierającą wszystkie godziny pracy w zakresie dat:
=SUMPRODUCT((0;8;8;8;8;4;0))
SUMPRODUCT następnie sumuje wszystkie elementy w tablicy i zwraca wynik 36.
