Üldine valem
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Kokkuvõte
Tööaja arvutamiseks kahe kuupäeva vahel kohandatud ajakavaga saate kasutada funktsioone WEEKDAY ja SUMPRODUCT põhinevat valemit ROW, INDIRECT ja MID abiga. Näidatud näites on valemis F8 järgmine:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Mis tagastab 36 tunni, lähtudes kohandatud ajakavast, kus 8 tundi töötatakse E-R, 4 tundi laupäeval ja esmaspäev, 3. september on puhkus. Puhkused pakutakse nimetatud vahemikuna G6: G8. Töögraafik sisestatakse veergu D tekstistringina ja seda saab vastavalt soovile muuta.
Märkus. See on massiivivalem, mis tuleb sisestada klahvikombinatsiooniga Control + Tõst + Enter. Kui teil on tavaline 8-tunnine tööpäev, on see valem lihtsam.
Selgitus
Põhimõtteliselt kasutab see valem WEEKDAY funktsiooni, et välja selgitada kahe päeva vahel iga päeva nädalapäev (st esmaspäev, teisipäev jne). WEEKDAY tagastab numbri vahemikus 1 kuni 7. Vaikimisi seadistustega pühapäev = 1 ja laupäev = 7.
Selle valemi nipp on kuupäevade massiivi kokkupanek, mille saate sisestada funktsiooni WEEKDAY. Seda tehakse ROW-ga, millel on INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW tõlgendab liidetud kuupäevi rea numbritena ja tagastab sellise massiivi:
(43346;43347;43348;43349;43350;43351;43352)
Iga massiivi number tähistab kuupäeva. Funktsioon WEEKDAY hindab seejärel massiivi ja tagastab massiivi nädalapäeva väärtustest:
(2;3;4;5;6;7;1)
Need numbrid vastavad iga kuupäeva nädalapäevale. Need esitatakse funktsioonile MID algusnumbri argumendina koos tekstis D6 oleva väärtusega "0888884":
MID("0888884",(2;3;4;5;6;7;1),1)
Kuna anname MID-le algusnumbrite massiivi, tagastab see massiivi selliseid tulemusi:
("8";"8";"8";"8";"8";"4";"0")
Need väärtused vastavad iga päeva töötatud tundidele alates alguskuupäevast kuni lõppkuupäevani. Pange tähele, et selle massiivi väärtused on tekst, mitte numbrid. Reaalseks arvuks teisendamiseks korrutame pühade haldamiseks loodud teise massiiviga, nagu allpool selgitatud. Matemaatikaoperatsioon sunnib teksti arvväärtusteks.
Pühad
Pühade käsitlemiseks kasutame ISNA, MATCH ja nimega vahemikku "pühad" järgmiselt:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
See väljend kasutab MATCH-i kuupäevade leidmiseks, mis on nimetatud vahemikus pühad, kasutades sama kuupäevade massiivi, mis on loodud ülaltoodud funktsioonidega INDIRECT ja ROW. MATCH tagastab arvu, kui pühad on leitud, ja vea # N / A, kui seda pole. ISNA funktsioon "pöörab" tulemused nii, et TRUE tähistab pühi ja FALSE tähistab puhkust. ISNA tagastab sellise massiivi või tulemused:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Lõpuks korrutatakse mõlemad massiivid SUMPRODUCT-is üksteisega. Matemaatikaoperatsioon sunnib TÕENE ja VALE väärtuseks 1 ja null ning esimese massiivi tekstiväärtused arvväärtusteks (nagu eespool selgitatud), nii et lõpuks on meil:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Pärast korrutamist on meil SUMPRODUCT-is üks massiiv, mis sisaldab kõiki kuupäevavahemiku töötunde:
=SUMPRODUCT((0;8;8;8;8;4;0))
Seejärel summeerib SUMPRODUCT massiivi kõik üksused ja tagastab tulemuse 36.
