Yleinen kaava
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Yhteenveto
Voit laskea kahden päivämäärän väliset työtunnit mukautetulla aikataululla käyttämällä WEEKDAY- ja SUMPRODUCT-funktioihin perustuvaa kaavaa ROW, INDIRECT ja MID avulla. Esitetyssä esimerkissä kaava kohdassa F8 on:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Joka palauttaa 36 tuntia mukautetun aikataulun mukaan, jossa 8 tuntia tehdään ma-pe, 4 tuntia lauantaina ja maanantai 3. syyskuuta on loma. Lomat toimitetaan nimellä nimetty alue G6: G8. Työn aikataulu syötetään tekstimerkkijonona sarakkeeseen D ja sitä voidaan muuttaa halutulla tavalla.
Huomautus: Tämä on matriisikaava, joka on syötettävä painamalla Control + Vaihto + Enter. Jos sinulla on tavallinen 8 tunnin työpäivä, tämä kaava on yksinkertaisempi.
Selitys
Ytimessä tämä kaava käyttää WEEKDAY-funktiota selvittääkseen viikonpäivän (ts. Maanantai, tiistai jne.) Jokaiselle päivälle kahden annetun päivämäärän välillä. WEEKDAY palauttaa luvun välillä 1 ja 7. Oletusasetuksilla sunnuntai = 1 ja lauantai = 7.
Tämän kaavan temppu on koota joukko päivämääriä, jotka voit syöttää WEEKDAY-funktioon. Tämä tehdään rivillä INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW tulkitsee liitetyt päivämäärät rivinumeroiksi ja palauttaa seuraavanlaisen taulukon:
(43346;43347;43348;43349;43350;43351;43352)
Jokainen taulukon numero edustaa päivämäärää. WEEKDAY-toiminto arvioi sitten taulukon ja palauttaa taulukon viikonpäivän arvoista:
(2;3;4;5;6;7;1)
Nämä numerot vastaavat kunkin päivän viikonpäivää. Ne toimitetaan MID-funktiolle aloitusnumero-argumenttina yhdessä tekstin D6 arvon "0888884" kanssa tekstille:
MID("0888884",(2;3;4;5;6;7;1),1)
Koska annamme MID: lle joukon aloitusnumeroita, se palauttaa seuraavanlaisen joukon tuloksia:
("8";"8";"8";"8";"8";"4";"0")
Nämä arvot vastaavat joka päivä tehtyjä tunteja aloituspäivästä päättymispäivään. Huomaa, että tämän taulukon arvot ovat tekstiä, ei numeroita. Jos haluat muuntaa todellisiksi numeroiksi, kerrotaan toisella matriisilla, joka on luotu lomien hallintaan, kuten alla selitetään. Matemaattinen operaatio pakottaa tekstin numeerisiin arvoihin.
Lomat
Lomien käsittelemiseksi käytämme ISNA: ta, MATCHia ja nimettyä aluetta "loma" seuraavasti:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Tämä lauseke käyttää MATCH-toimintoa paikantamaan päivämäärät, jotka ovat nimettyjen alueiden vapaapäiviä, käyttäen samaa taulukoiden taulukkoa, joka on luotu yllä kohdilla INDIRECT ja ROW. MATCH palauttaa luvun, kun lomia löytyy, ja # N / A -virheen, kun sitä ei löydy. ISNA-funktio "kääntää" tuloksia siten, että TOSI edustaa vapaapäiviä ja EPÄTOSI edustaa muita kuin lomia. ISNA palauttaa tällaisen taulukon tai tulokset:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Lopuksi molemmat taulukot kerrotaan keskenään SUMPRODUCT-sovelluksessa. Matemaattinen operaatio pakottaa TOSI ja EPÄTOSI arvoksi 1 ja nolla, ja ensimmäisen matriisin tekstiarvot numeerisiin arvoihin (kuten yllä selitettiin), joten lopulta meillä on:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Kertomisen jälkeen meillä on yksi taulukko SUMPRODUCTissa, joka sisältää kaikki ajanjaksot ajanjaksolla:
=SUMPRODUCT((0;8;8;8;8;4;0))
SUMPRODUCT summaa sitten kaikki matriisin kohteet ja palauttaa tuloksen 36.
