Formula generik
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)))=dow))
Ringkasan
Untuk mengira hari kerja (Isnin, Jumaat, Ahad, dan lain-lain) antara dua tarikh, anda boleh menggunakan formula tatasusunan yang menggunakan beberapa fungsi: SUMPRODUCT, WEEKDAY, ROW, dan INDIRECT. Dalam contoh yang ditunjukkan, formula dalam sel E6 adalah
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B6&":"&C6)))=D6))
Dalam versi formula umum, mula = tarikh mula, akhir = tarikh akhir, dan dow = hari dalam seminggu.
Penjelasan
Pada intinya, formula ini menggunakan fungsi WEEKDAY untuk menguji sebilangan tarikh untuk melihat apakah ia tiba pada hari tertentu dalam seminggu (dow) dan fungsi SUMPRODUCT untuk menghitung jumlahnya.
Apabila diberi tarikh, WEEKDAY hanya mengembalikan nombor antara 1 dan 7 yang sepadan dengan hari tertentu dalam seminggu. Dengan tetapan lalai, 1 = Ahad dan 7 = Sabtu. Jadi, 2 = Isnin, 6 = Jumaat, dan seterusnya.
Caranya untuk formula ini adalah memahami bahawa tarikh di Excel hanyalah nombor siri yang bermula pada 1 Januari 1900. Contohnya, 1 Januari 2016 adalah nombor siri 42370, dan 8 Januari adalah 42377. Tarikh di Excel hanya kelihatan seperti tarikh ketika format nombor tarikh digunakan.
Oleh itu, persoalannya menjadi - bagaimana anda dapat membina pelbagai tarikh yang dapat anda masukkan ke dalam fungsi WEEKDAY untuk mengetahui hari-hari dalam seminggu?
Jawapannya adalah menggunakan ROW dengan fungsi LANGSUNG seperti:
ROW(INDIRECT(date1&":"&date2))
LANGSUNG membolehkan tarikh gabungan "42370: 42377" ditafsirkan sebagai nombor baris. Kemudian fungsi ROW mengembalikan array seperti ini:
(42370;42371;42372;42373;42374;42375;42376;42377)
Fungsi WEEKDAY menilai nombor ini sebagai tarikh dan mengembalikan array ini:
(6;7;1;2;3;4;5;6)
yang diuji terhadap hari tertentu dalam seminggu (6 dalam kes ini, dari D6). Setelah keputusan ujian ditukar menjadi 1s dan 0s dengan tanda hubung berganda, susunan ini diproses oleh SUMPRODUCT:
(1;0;0;0;0;0;0;1)
Yang mengembalikan 2.
Dengan KEBERSIHAN
Dengan fungsi SEQUENCE yang baru, formula ini dapat dipermudah seperti ini:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(end-start+1,1,start,1))=dow))
Dalam versi ini, kami menggunakan SEQUENCE untuk menghasilkan susunan tarikh secara langsung, tanpa memerlukan LANGSUNG atau ROW.
