Všeobecný vzorec
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Zhrnutie
Ak chcete vypočítať pracovný čas medzi dvoma dátumami s vlastným harmonogramom, môžete použiť vzorec založený na funkciách WEEKDAY a SUMPRODUCT s pomocou ROW, INDIRECT a MID. V zobrazenom príklade je vzorec v F8:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Vďaka čomu sa vráti 36 hodín na základe vlastného rozvrhu, kde sa pracuje 8 hodín od pondelka do piatku, 4 hodiny sa odpracujú v sobotu a pondelok 3. septembra je sviatok. Prázdniny sa dodávajú ako pomenovaný rozsah G6: G8. Pracovný plán je zadaný ako textový reťazec v stĺpci D a je možné ho podľa potreby zmeniť.
Poznámka: Toto je vzorec poľa, ktorý je potrebné zadať pomocou kombinácie klávesov Control + Shift + Enter. Ak máte štandardný 8-hodinový pracovný deň, tento vzorec je jednoduchší.
Vysvetlenie
Tento vzorec v jadre využíva funkciu WEEKDAY na zistenie dňa v týždni (tj. Pondelok, utorok atď.) Pre každý deň medzi dvoma danými dátumami. WEEKDAY vráti číslo od 1 do 7. Pri predvolenom nastavení je nedeľa = 1 a sobota = 7.
Trik v tomto vzorci spočíva v zostavení množstva dátumov, ktoré môžete vložiť do funkcie WEEKDAY. Toto sa deje pomocou ROW s INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW interpretuje zreťazené dátumy ako čísla riadkov a vráti pole ako je toto:
(43346;43347;43348;43349;43350;43351;43352)
Každé číslo v poli predstavuje dátum. Funkcia WEEKDAY potom vyhodnotí pole a vráti pole hodnôt v pracovných dňoch:
(2;3;4;5;6;7;1)
Tieto čísla zodpovedajú dňu v týždni každého dátumu. Poskytujú sa funkcii MID ako argument počiatočného čísla spolu s hodnotou v D6 „0888884“ pre text:
MID("0888884",(2;3;4;5;6;7;1),1)
Pretože dávame programu MID pole počiatočných čísel, vráti pole výsledkov takto:
("8";"8";"8";"8";"8";"4";"0")
Tieto hodnoty zodpovedajú odpracovaným hodinám každý deň od dátumu začatia do dátumu ukončenia. Upozorňujeme, že hodnoty v tomto poli sú text, nie čísla. Na prevod na skutočné čísla vynásobíme druhým poľom vytvoreným na správu sviatkov, ako je vysvetlené nižšie. Matematická operácia vynúti text na číselné hodnoty.
Prázdniny
Na vybavovanie sviatkov používame ISNA, MATCH a pomenovaný rozsah „sviatky“ takto:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Tento výraz používa MATCH na vyhľadanie dátumov, ktoré sú v pomenovaných prázdninách rozsahu, pomocou rovnakého poľa dátumov vygenerovaných vyššie s nepriamymi a riadkovými. MATCH vráti číslo, keď sa nájdu sviatky, a chyba # N / A, ak nie. Funkcia ISNA „preklopí“ výsledky tak, že TRUE predstavuje sviatky a FALSE predstavuje sviatky. ISNA vráti pole alebo výsledky takto:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Nakoniec sa obe polia vo vnútri SUMPRODUCTu navzájom znásobia. Matematická operácia vynúti TRUE a FALSE na 1 a nulu a textové hodnoty v prvom poli na číselné hodnoty (ako je vysvetlené vyššie), takže nakoniec máme:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Po vynásobení máme vo vnútri SUMPRODUCT jediné pole obsahujúce všetky pracovné hodiny v rozsahu dátumov:
=SUMPRODUCT((0;8;8;8;8;4;0))
SUMPRODUCT potom sčíta všetky položky v poli a vráti výsledok 36.
