Formula generik
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Ringkasan
Untuk mengira waktu kerja antara dua tarikh dengan jadual tersuai, anda boleh menggunakan formula berdasarkan fungsi WEEKDAY dan SUMPRODUCT, dengan bantuan dari ROW, INDIRECT, dan MID. Dalam contoh yang ditunjukkan, formula dalam F8 adalah:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Yang kembali 36 jam, berdasarkan jadual khusus di mana 8 jam bekerja Isnin-Jumaat, 4 jam bekerja pada hari Sabtu, dan Isnin 3 September adalah hari cuti. Cuti dibekalkan sebagai julat bernama G6: G8. Jadual kerja dimasukkan sebagai rentetan teks di lajur D dan dapat diubah seperti yang diinginkan.
Catatan: Ini adalah formula array yang mesti dimasukkan dengan Control + Shift + Enter. Sekiranya anda mempunyai 8 jam kerja biasa, formula ini lebih mudah.
Penjelasan
Pada intinya, formula ini menggunakan fungsi WEEKDAY untuk mengetahui hari dalam seminggu (iaitu Isnin, Selasa, dll.) Untuk setiap hari antara dua tarikh yang diberikan. WEEKDAY mengembalikan nombor antara 1 dan 7. Dengan tetapan lalai, Ahad = 1 dan Sabtu = 7.
Caranya untuk formula ini adalah menyusun pelbagai tarikh yang dapat anda masukkan ke dalam fungsi WEEKDAY. Ini dilakukan dengan ROW dengan LANGSUNG:
ROW(INDIRECT(B6&":"&C6))
ROW menafsirkan tarikh gabungan sebagai nombor baris dan mengembalikan tatasusunan seperti ini:
(43346;43347;43348;43349;43350;43351;43352)
Setiap nombor dalam array mewakili tarikh. Fungsi WEEKDAY kemudian menilai susunan dan mengembalikan pelbagai nilai hari kerja:
(2;3;4;5;6;7;1)
Angka-angka ini sesuai dengan hari dalam setiap tarikh. Mereka diberikan kepada fungsi MID sebagai argumen nombor permulaan, bersama dengan nilai dalam D6, "0888884" untuk teks:
MID("0888884",(2;3;4;5;6;7;1),1)
Kerana kami memberikan MID pelbagai nombor permulaan, ia mengembalikan pelbagai hasil seperti ini:
("8";"8";"8";"8";"8";"4";"0")
Nilai-nilai ini sesuai dengan jam kerja pada setiap hari dari tarikh mula hingga tarikh akhir. Perhatikan nilai dalam tatasusunan ini adalah teks, bukan nombor. Untuk menukar ke nombor sebenar, kami mengalikan dengan susunan kedua yang dibuat untuk menguruskan percutian, seperti yang dijelaskan di bawah. Operasi matematik menyusun teks menjadi nilai berangka.
Cuti
Untuk menangani percutian, kami menggunakan ISNA, MATCH, dan julat bernama "cuti" seperti ini:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Ungkapan ini menggunakan MATCH untuk mencari tarikh yang berada dalam julat cuti yang dinamakan menggunakan susunan tarikh yang sama yang dihasilkan di atas dengan INDIRECT dan ROW. MATCH mengembalikan nombor apabila cuti dijumpai dan kesalahan # N / A jika tidak. Fungsi ISNA "membalikkan" hasilnya sehingga BENAR mewakili hari cuti dan SALAH mewakili bukan hari cuti. ISNA mengembalikan array atau hasil seperti ini:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Akhirnya, kedua-dua susunan dikalikan satu sama lain di dalam SUMPRODUCT. Operasi matematik membentuk TRUE dan FALSE menjadi 1 dan zero, dan nilai teks pada array pertama hingga nilai numerik (seperti yang dijelaskan di atas), jadi pada akhirnya kita mempunyai:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Setelah pendaraban, kami mempunyai satu array dalam SUMPRODUCT yang mengandungi semua waktu bekerja dalam julat tarikh:
=SUMPRODUCT((0;8;8;8;8;4;0))
SUMPRODUCT kemudian menjumlahkan semua item dalam array dan mengembalikan hasil 36.
