Obecný vzorec
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
souhrn
Chcete-li vypočítat pracovní dobu mezi dvěma daty s vlastním plánem, můžete použít vzorec založený na funkcích WEEKDAY a SUMPRODUCT s pomocí ROW, INDIRECT a MID. V zobrazeném příkladu je vzorec v F8:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Který vrací 36 hodin na základě vlastního rozvrhu, kde se pracuje 8 hodin po-pá, 4 hodiny se pracuje v sobotu a v pondělí 3. září je svátek. Prázdniny jsou dodávány jako pojmenovaný rozsah G6: G8. Pracovní plán se zadává jako textový řetězec ve sloupci D a lze jej podle potřeby změnit.
Poznámka: Toto je maticový vzorec, který musí být zadán pomocí kláves Control + Shift + Enter. Pokud máte standardní 8hodinový pracovní den, je tento vzorec jednodušší.
Vysvětlení
Tento vzorec v jádru používá funkci WEEKDAY k výpočtu dne v týdnu (tj. Pondělí, úterý atd.) Pro každý den mezi dvěma uvedenými daty. WEEKDAY vrátí číslo mezi 1 a 7. Při výchozím nastavení neděle = 1 a sobota = 7.
Trik v tomto vzorci spočívá v sestavení řady dat, která můžete vložit do funkce WEEKDAY. To se provádí pomocí ROW s INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW interpretuje zřetězená data jako čísla řádků a vrací pole takto:
(43346;43347;43348;43349;43350;43351;43352)
Každé číslo v poli představuje datum. Funkce WEEKDAY poté vyhodnotí pole a vrátí pole hodnot v pracovní dny:
(2;3;4;5;6;7;1)
Tato čísla odpovídají dnu v týdnu každého data. Jsou poskytovány funkci MID jako argument počátečního čísla spolu s hodnotou v D6 „0888884“ pro text:
MID("0888884",(2;3;4;5;6;7;1),1)
Protože dáváme MID pole počátečních čísel, vrátí pole výsledků, jako je tato:
("8";"8";"8";"8";"8";"4";"0")
Tyto hodnoty odpovídají hodinám odpracovaným každý den od data zahájení do data ukončení. Hodnoty v tomto poli jsou text, nikoli čísla. Chcete-li převést na skutečná čísla, vynásobíme druhým polem vytvořeným pro správu svátků, jak je vysvětleno níže. Matematická operace vynucuje text na číselné hodnoty.
Dovolená
Ke zpracování svátků používáme ISNA, MATCH a pojmenovaný rozsah „svátky“ takto:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Tento výraz používá MATCH k vyhledání dat, která jsou v pojmenovaných prázdninách rozsahu, pomocí stejného pole dat generovaných výše s NEPŘÍMÝM a ŘÁDKEM. MATCH vrátí číslo, když jsou nalezeny svátky, a chyba # N / A, pokud ne. Funkce ISNA „převrátí“ výsledky tak, že TRUE představuje svátky a FALSE představuje nesvátky. ISNA vrátí pole nebo výsledky, jako je tento:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Nakonec se obě pole v SUMPRODUCT navzájem znásobí. Matematická operace donutí TRUE a FALSE na 1 a nulu a textové hodnoty v prvním poli na číselné hodnoty (jak je vysvětleno výše), takže na konci máme:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Po násobení máme uvnitř SUMPRODUCT jediné pole obsahující všechny pracovní hodiny v časovém období:
=SUMPRODUCT((0;8;8;8;8;4;0))
SUMPRODUCT poté sečte všechny položky v poli a vrátí výsledek 36.
