Vispārēja formula
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Kopsavilkums
Lai aprēķinātu darba stundas starp diviem datumiem ar pielāgotu grafiku, varat izmantot formulu, kas balstīta uz WEEKDAY un SUMPRODUCT funkcijām, izmantojot ROW, INDIRECT un MID palīdzību. Parādītajā piemērā formula F8 ir:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Atgriežot 36 stundas, pamatojoties uz pielāgotu grafiku, kur 8 stundas strādā no pirmdienas līdz piektdienai, 4 stundas strādā sestdienā un pirmdien, 3. septembrī, ir brīvdiena. Brīvdienas tiek piegādātas kā nosauktais diapazons G6: G8. Darba grafiks tiek ievadīts kā teksta virkne D slejā, un to var mainīt pēc vēlēšanās.
Piezīme: Šī ir masīva formula, kas jāievada ar Control + Shift + Enter. Ja jums ir standarta 8 stundu darba diena, šī formula ir vienkāršāka.
Paskaidrojums
Būtībā šī formula izmanto funkciju WEEKDAY, lai noskaidrotu nedēļas dienu (ti, pirmdienu, otrdienu utt.) Katrai dienai starp diviem norādītajiem datumiem. WEEKDAY atgriež skaitli no 1 līdz 7. Ar noklusējuma iestatījumiem svētdiena = 1 un sestdiena = 7.
Šīs formulas triks ir datumu masīva apkopošana, kurus varat ievadīt WEEKDAY funkcijā. Tas tiek darīts ar ROW ar INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW tulko datumus kā rindu numurus un atgriež šādu masīvu:
(43346;43347;43348;43349;43350;43351;43352)
Katrs masīva numurs apzīmē datumu. Tad WEEKDAY funkcija novērtē masīvu un atgriež masīvu ar nedēļas dienu vērtībām:
(2;3;4;5;6;7;1)
Šie skaitļi atbilst katra datuma nedēļas dienai. Tie tiek nodrošināti funkcijai MID kā sākuma numura arguments kopā ar vērtību D6 "0888884" tekstam:
MID("0888884",(2;3;4;5;6;7;1),1)
Tā kā mēs piešķiram MID starta numuru masīvu, tas atgriež šādu rezultātu masīvu:
("8";"8";"8";"8";"8";"4";"0")
Šīs vērtības atbilst nostrādātajām stundām katrā dienā no sākuma datuma līdz beigu datumam. Ņemiet vērā, ka šī masīva vērtības ir teksts, nevis skaitļi. Lai konvertētu uz faktiskajiem skaitļiem, mēs reizinām ar otro masīvu, kas izveidots, lai pārvaldītu brīvdienas, kā paskaidrots tālāk. Matemātikas operācija piesaista tekstu skaitliskām vērtībām.
Brīvdienas
Lai apstrādātu brīvdienas, mēs izmantojam ISNA, MATCH un nosaukto diapazonu "brīvdienas" šādi:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Šis izteiciens izmanto MATCH, lai atrastu datumus, kas ir nosauktajā diapazona brīvdienās, izmantojot to pašu datumu masīvu, kas iepriekš ģenerēts ar INDIRECT un ROW. MATCH atgriež skaitli, kad tiek atrastas brīvdienas, un # N / A kļūdu, ja nav. ISNA funkcija "apvērš" rezultātus tā, ka TRUE apzīmē brīvdienas un FALSE - brīvdienas. ISNA atgriež masīvu vai šādus rezultātus:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Visbeidzot, abi masīvi tiek reizināti viens ar otru SUMPRODUCT iekšpusē. Matemātikas operācija TRUE un FALSE piespiež 1 un nulli, bet teksta vērtības pirmajā masīvā - skaitliskas vērtības (kā paskaidrots iepriekš), tāpēc beigās mums ir:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Pēc reizināšanas mums SUMPRODUCT ir viens masīvs, kas satur visas darba stundas datumu diapazonā:
=SUMPRODUCT((0;8;8;8;8;4;0))
Pēc tam SUMPRODUCT summē visus masīva vienumus un atgriež rezultātu 36.
