Általános képlet
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Összegzés
Két dátum közötti munkaidő egyéni ütemezéssel történő kiszámításához használhatja a WEEKDAY és a SUMPRODUCT függvényeken alapuló képletet, a ROW, INDIRECT és MID segítségével. A bemutatott példában az F8 képlete a következő:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Ami 36 órát ad vissza, egy egyéni ütemezés alapján, ahol 8 órát dolgoznak hétfőtől péntekig, 4 órát dolgoznak szombaton, és szeptember 3-án, hétfőn ünnepnap van. Az ünnepeket a G6: G8 megnevezett tartományként szállítjuk. A munkarend szöveges karakterláncként kerül be a D oszlopba, és tetszés szerint módosítható.
Megjegyzés: Ez egy tömbképlet, amelyet a Control + Shift + Enter billentyűkombinációval kell megadni. Ha szabványos 8 órás munkanapja van, akkor ez a képlet egyszerűbb.
Magyarázat
Lényegében ez a képlet a HÉTNAP funkciót használja arra, hogy kitalálja a hét napját (azaz hétfő, kedd stb.) A két megadott dátum között minden napra. A WEEKDAY 1 és 7 közötti számot ad vissza. Alapértelmezett beállításokkal vasárnap = 1 és szombat = 7.
Ennek a képletnek a trükkje egy dátumtömb összegyűjtése, amelyet betölthet a WEEKDAY függvénybe. Ez a ROW-val történik a INDIRECT használatával:
ROW(INDIRECT(B6&":"&C6))
A ROW az összefűzött dátumokat sorszámként értelmezi, és egy ilyen tömböt ad vissza:
(43346;43347;43348;43349;43350;43351;43352)
A tömbben minden szám egy dátumot jelent. A WEEKDAY függvény kiértékeli a tömböt, és hétköznapi értékek tömbjét adja vissza:
(2;3;4;5;6;7;1)
Ezek a számok az egyes dátumok hétnapjának felelnek meg. Ezeket a MID függvény kapja meg kezdő szám argumentumként, a D6-ban szereplő értékkel együtt, a "0888884" szöveggel:
MID("0888884",(2;3;4;5;6;7;1),1)
Mivel a MID-nek indulási számok tömbjét adjuk meg, az ilyen eredménytömböt ad vissza:
("8";"8";"8";"8";"8";"4";"0")
Ezek az értékek megegyeznek az egyes napok ledolgozott óráival a kezdő dátumtól a befejezés dátumáig. Vegye figyelembe, hogy ennek a tömbnek az értékei szöveg, nem pedig számok. A tényleges számokká való átváltáshoz megszorozzuk az ünnepek kezelésére létrehozott második tömböt, az alábbiakban leírtak szerint. A matematikai művelet a szöveget numerikus értékekre kényszeríti.
Ünnepek
Az ünnepek kezeléséhez az ISNA-t, a MATCH-ot és az így megnevezett "ünnepek" tartományt használjuk:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Ez a kifejezés a MATCH használatával keresi meg a megnevezett tartományban lévő dátumokat a fenti, INDIRECT és ROW segítségével generált dátum tömb felhasználásával. A MATCH számot ad vissza, ha ünnepnapok találhatók, és a # N / A hibát, ha nem. Az ISNA függvény úgy "fordítja" az eredményeket, hogy a TRUE az ünnepeket, a FALSE pedig a nem ünnepeket jelenti. Az ISNA egy ilyen tömböt ad vissza, vagy ilyen eredményeket ad:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Végül mindkét tömböt megszorozzuk egymással a SUMPRODUCT-en belül. A matematikai művelet a TRUE és FALSE értékeket 1-re és nulla értékre, az első tömb szövegértékeit pedig numerikus értékekre kényszeríti (a fentiek szerint), így végül:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Szorzás után a SUMPRODUCT-en belül egyetlen tömb van, amely tartalmazza a dátumtartomány összes munkaidejét:
=SUMPRODUCT((0;8;8;8;8;4;0))
A SUMPRODUCT ezután összesíti a tömb összes elemét, és 36-os eredményt ad vissza.
