Công thức chung
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)))=dow))
Tóm lược
Để đếm các ngày trong tuần (Thứ Hai, Thứ Sáu, Chủ Nhật, v.v.) giữa hai ngày, bạn có thể sử dụng công thức mảng sử dụng một số hàm: SUMPRODUCT, WEEKDAY, ROW và INDIRECT. Trong ví dụ được hiển thị, công thức trong ô E6 là
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B6&":"&C6)))=D6))
Trong phiên bản chung của công thức, bắt đầu = ngày bắt đầu, kết thúc = ngày kết thúc và dow = ngày trong tuần.
Giải trình
Về cơ bản, công thức này sử dụng hàm WEEKDAY để kiểm tra một số ngày để xem liệu chúng có hạ cánh vào một ngày nhất định trong tuần (ngày xuống) hay không và hàm SUMPRODUCT để kiểm tra tổng số.
Khi được cung cấp một ngày, WEEKDAY chỉ trả về một số từ 1 đến 7 tương ứng với một ngày cụ thể trong tuần. Với cài đặt mặc định, 1 = Chủ nhật và 7 = Thứ bảy. Vì vậy, 2 = Thứ Hai, 6 = Thứ Sáu, v.v.
Mẹo cho công thức này là hiểu rằng ngày trong Excel chỉ là số sê-ri bắt đầu từ ngày 1 tháng 1 năm 1900. Ví dụ: ngày 1 tháng 1 năm 2016 là số sê-ri 42370 và ngày 8 tháng 1 là 42377. Ngày trong Excel chỉ giống như ngày khi một định dạng số ngày được áp dụng.
Vì vậy, câu hỏi trở thành - làm thế nào bạn có thể xây dựng một mảng ngày mà bạn có thể đưa vào hàm WEEKDAY để tìm ra các ngày tương ứng trong tuần?
Câu trả lời là sử dụng ROW với các chức năng INDIRECT như vậy:
ROW(INDIRECT(date1&":"&date2))
INDIRECT cho phép các ngày được nối "42370: 42377" được hiểu là số hàng. Sau đó, hàm ROW trả về một mảng như sau:
(42370;42371;42372;42373;42374;42375;42376;42377)
Hàm WEEKDAY đánh giá những số này là ngày tháng và trả về mảng này:
(6;7;1;2;3;4;5;6)
được kiểm tra theo ngày nhất định trong tuần (6 trong trường hợp này là từ D6). Sau khi kết quả của bài kiểm tra được chuyển đổi thành 1s và 0s với dấu gạch nối kép, mảng này được xử lý bởi SUMPRODUCT:
(1;0;0;0;0;0;0;1)
Trả về 2.
Với SEQUENCE
Với hàm SEQUENCE mới, công thức này có thể đơn giản hóa phần nào như sau:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(end-start+1,1,start,1))=dow))
Trong phiên bản này, chúng tôi sử dụng SEQUENCE để tạo mảng ngày trực tiếp mà không cần INDIRECT hoặc ROW.
