Splošna formula
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Povzetek
Za izračun delovnih ur med dvema datumoma po urniku po meri lahko uporabite formulo, ki temelji na funkcijah WEEKDAY in SUMPRODUCT, s pomočjo ROW, INDIRECT in MID. V prikazanem primeru je formula v F8:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Kar vrne 36 ur, glede na urnik po meri, kjer se od ponedeljka do petka dela 8 ur, v soboto 4 ure, ponedeljek 3. septembra pa je praznik. Prazniki so na voljo kot imenovani obseg G6: G8. Urnik dela se vnese kot besedilni niz v stolpec D in ga lahko po želji spremenite.
Opomba: To je formula matrike, ki jo je treba vnesti s tipkama Control + Shift + Enter. Če imate običajni 8-urni delovnik, je ta formula preprostejša.
Pojasnilo
V osnovi ta formula uporablja funkcijo WEEKDAY za določitev dneva v tednu (tj. Ponedeljek, torek itd.) Za vsak dan med navedenima datumoma. WEEKDAY vrne številko med 1 in 7. S privzetimi nastavitvami je nedelja = 1 in sobota = 7.
Trik te formule je sestaviti vrsto datumov, ki jih lahko vnesete v funkcijo WEEKDAY. To se naredi z ROW z INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW interpretira združene datume kot številke vrstic in vrne matriko, kot je ta:
(43346;43347;43348;43349;43350;43351;43352)
Vsaka številka v matriki predstavlja datum. Nato funkcija WEEKDAY ovrednoti matriko in vrne matriko vrednosti v tednu:
(2;3;4;5;6;7;1)
Te številke ustrezajo dnevu v tednu vsakega datuma. Na voljo so funkciji MID kot argument začetne številke, skupaj z vrednostjo v D6, "0888884" za besedilo:
MID("0888884",(2;3;4;5;6;7;1),1)
Ker MID damo matriko začetnih številk, vrne vrsto rezultatov, kot je ta:
("8";"8";"8";"8";"8";"4";"0")
Te vrednosti ustrezajo številu ur, opravljenih vsak dan od začetka do konca. Vrednosti v tej matriki so besedilo in ne številke. Za pretvorbo v dejanske številke pomnožimo z drugo matriko, ustvarjeno za upravljanje počitnic, kot je razloženo spodaj. Matematična operacija prisili besedilo v številske vrednosti.
Počitnice
Za ravnanje s počitnicami uporabljamo ISNA, MATCH in imenovani obseg "počitnice", kot je ta:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Ta izraz uporablja MATCH za iskanje datumov, ki so v prazničnih dneh imenovanega obsega, z uporabo istega niza datumov, ustvarjenih zgoraj z INDIRECT in ROW. MATCH vrne številko, ko so najdeni prazniki, in napako # N / A, kadar ni. Funkcija ISNA "prevrne" rezultate, tako da TRUE predstavlja praznike, FALSE pa ne praznike. ISNA vrne matriko ali rezultate, kot je ta:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Na koncu se oba polja v SUMPRODUCT pomnožijo med seboj. Matematična operacija prisili TRUE in FALSE na 1 in nič, besedilne vrednosti v prvem polju pa na številske vrednosti (kot je razloženo zgoraj), tako da na koncu imamo:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Po množenju imamo znotraj SUMPRODUCT eno matriko, ki vsebuje vse delovne ure v časovnem obdobju:
=SUMPRODUCT((0;8;8;8;8;4;0))
Nato SUMPRODUCT sešteje vse elemente v matriki in vrne rezultat 36.
