Công thức chung
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Tóm lược
Để tính toán giờ làm việc giữa hai ngày với lịch biểu tùy chỉnh, bạn có thể sử dụng công thức dựa trên hàm WEEKDAY và hàm SUMPRODUCT, với sự trợ giúp từ ROW, INDIRECT và MID. Trong ví dụ được hiển thị, công thức trong F8 là:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Trong đó trả về 36 giờ, dựa trên lịch trình tùy chỉnh trong đó 8 giờ làm việc từ Thứ Hai đến Thứ Sáu, 4 giờ làm việc vào Thứ Bảy và Thứ Hai ngày 3 tháng 9 là ngày nghỉ. Các ngày lễ được cung cấp dưới dạng phạm vi được đặt tên là G6: G8. Lịch làm việc được nhập dưới dạng chuỗi văn bản trong cột D và có thể thay đổi theo ý muốn.
Lưu ý: Đây là công thức mảng phải được nhập bằng Control + Shift + Enter. Nếu bạn có một ngày làm việc tiêu chuẩn 8 giờ, công thức này đơn giản hơn.
Giải trình
Về cơ bản, công thức này sử dụng hàm WEEKDAY để tìm ra ngày trong tuần (tức là Thứ Hai, Thứ Ba, v.v.) cho mỗi ngày giữa hai ngày nhất định. WEEKDAY trả về một số từ 1 đến 7. Với cài đặt mặc định, Chủ nhật = 1 và Thứ bảy = 7.
Mẹo cho công thức này là tập hợp một mảng ngày tháng mà bạn có thể đưa vào hàm WEEKDAY. Điều này được thực hiện với ROW với INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW diễn giải các ngày được nối dưới dạng số hàng và trả về một mảng như sau:
(43346;43347;43348;43349;43350;43351;43352)
Mỗi số trong mảng đại diện cho một ngày. Sau đó, hàm WEEKDAY đánh giá mảng và trả về một mảng các giá trị ngày trong tuần:
(2;3;4;5;6;7;1)
Những con số này tương ứng với ngày trong tuần của mỗi ngày. Chúng được cung cấp cho hàm MID dưới dạng đối số số bắt đầu, cùng với giá trị trong D6, "0888884" cho văn bản:
MID("0888884",(2;3;4;5;6;7;1),1)
Bởi vì chúng tôi đang cung cấp cho MID một mảng số bắt đầu, nó trả về một mảng kết quả như sau:
("8";"8";"8";"8";"8";"4";"0")
Các giá trị này tương ứng với số giờ làm việc mỗi ngày từ ngày bắt đầu đến ngày kết thúc. Lưu ý các giá trị trong mảng này là văn bản, không phải số. Để chuyển đổi thành số thực, chúng tôi nhân với một mảng thứ hai được tạo để quản lý ngày nghỉ, như được giải thích bên dưới. Phép toán ép buộc văn bản thành các giá trị số.
Ngày lễ
Để xử lý các ngày lễ, chúng tôi sử dụng ISNA, MATCH và phạm vi được đặt tên là "ngày nghỉ" như sau:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Biểu thức này sử dụng MATCH để xác định các ngày nằm trong phạm vi ngày nghỉ được đặt tên bằng cách sử dụng cùng một mảng ngày được tạo ở trên với INDIRECT và ROW. MATCH trả về một số khi tìm thấy ngày lễ và lỗi # N / A khi không tìm thấy. Hàm ISNA "lật" các kết quả để TRUE biểu thị ngày lễ và FALSE biểu thị không phải ngày nghỉ. ISNA trả về một mảng hoặc các kết quả như sau:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Cuối cùng, cả hai mảng được nhân với nhau bên trong SUMPRODUCT. Phép toán buộc TRUE và FALSE thành 1 và 0, và các giá trị văn bản trong mảng đầu tiên thành giá trị số (như đã giải thích ở trên), vì vậy cuối cùng chúng ta có:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Sau khi nhân, chúng ta có một mảng bên trong SUMPRODUCT chứa tất cả giờ làm việc trong phạm vi ngày:
=SUMPRODUCT((0;8;8;8;8;4;0))
SUMPRODUCT sau đó tính tổng tất cả các mục trong mảng và trả về kết quả là 36.
