Bảng phân công là một lịch trình phân chia nhiệm vụ hay lịch nghỉ việc trong một doanh nghiệp hay nhóm. Từ theo dõi điểm danh đến lập kế hoạch sự kiện, bảng phân công rất hữu ích để theo dõi nhân sự và tiến độ công việc. Microsoft Excel là một công cụ tuyệt vời để tạo bảng phân công cho các tổ chức. Trong bài viết dưới đây, UniTrain sẽ hướng dẫn bạn cách tạo bảng phân công trong Excel.
Bước 1: Tạo sheets cho các mục khác nhau trong Excel
Đầu tiên, người dùng cần một sheet, bao gồm: Months, Years, Weekends, Shift Types, Shift Codes và Employee Name (như hình dưới).
Bước 2: Tạo phạm vi được đặt tên
Sử dụng hàm OFFSET và COUNTA để tạo phạm vi được đặt tên bằng cách:
2.1. Để đặt tên cho một phạm vi, hãy chọn phạm vi và chèn tên vào Name Box.
2.2. Để đặt tên cho một phạm vi một cách linh hoạt, hãy chọn tab Formula > Name Manager từ Defined Name và chọn New từ hộp thoại Name Manager.
2.3. Trong hộp Edit Name, hãy đặt tên cho phạm vi bên cạnh trường Name và chèn các công thức trong mục Refer to… > OK.
Trong trường hợp này, người dùng sử dụng hàm OFFSET như sau:
Với Employee_List (employee name):
=OFFSET(Settings!$G$4,1,0,COUNTA(Settings!$G:$G)-1,1)=OFFSET(Settings!$F$4,1,0,COUNTA(Settings!$F:$F)-1,1)Với YearList (Years):
=OFFSET(Settings!$C$4,1,0,COUNTA(Settings!$C:$C)-1,1)
Bước 3: Thay đổi kích thước ô cho Master Sheet
3.1. Hợp nhất B2:C4 để giữ một vị trí cho logo.
3.2. Hợp nhất phạm vi E4:V3 để nhập tiêu đề, phạm vi W2:AB3 để nhập giá trị hàng tháng, phạm vi AC2:AF3 để nhập giá trị của năm và phạm vi AG2:AI3 để nhập danh sách bên dưới.
3.3. Hợp nhất E4:I4, K4:L4, M4:O4, P4:R4, S4:T4, U4:W4, X4:Z4, AA4:AB4, AC4:AE4 và AG4:AI4 (như hình dưới).
3.4. Chèn logo và tiêu đề
Tiếp theo, bạn sẽ chèn logo và tiêu đề vào các ô đã hợp nhất.
Bước 5: Chuẩn bị ô cho tháng và năm
Người dùng sẽ chỉ định các danh sách như một trình đơn thả xuống để chọn từ tháng và năm bằng cách:
5.1. Để chọn tháng từ danh sách thả xuống, hãy chọn ô W2 và chọn Data Validation từ nhóm Data Tools của Data.
5.2. Trong hộp thoại Data Validation, chọn List trong mục Allow và Month_List trong mục Source > nhấn F3 và chọn phạm vi tháng.
5.3. Chọn OK. Bạn sẽ thấy một danh sách thả xuống trong ô với tất cả các tháng trong đó.
Làm tương tự với các giá trị năm.
Bước 6: Chuẩn bị ô cho các tháng
Bạn sẽ dùng hàm DATEVALUE, IFERROR và EOMONTH để tạo ngày và tháng bằng cách:
6.1. Trong ô P4, nhập công thức dưới đây:
=IFERROR(DATEVALUE(J4&W2&AC2),"")
=IFERROR(EOMONTH(P4,0)+J4-1,"")
=X4-P4+1
Bước 7: Chèn ngày
7.1. Người dùng sẽ nhập “=P4″ trong ô E7 để duy trì ngày bắt đầu.
7.2. Trong ô F7, người dùng sẽ sử dụng công thức sau và định dạng dd.
=IF(E7>=$X$4,"",E7+1)
7.3. Người dùng sẽ sao chép công thức cho đến ô AI7.
7.4. Trong ô E6, hãy nhập công thức dưới đây:
=TEXT(E7,"ddd")
Bước 8: Sắp xếp các cột bảng nhân viên
Bước 9: Tổng hợp các ca làm
Bước 10: Nhập các ca làm
=IF(OR($C8="",E$7=""),"",IF(D8="",C8,INDEX(Shift_Codes,IF(MATCH(D8,Shift_Codes,0)+1>COUNTA(Shift_Codes),1,MATCH(D8,Shift_Codes,0)+1))))
Bước 11: Tạo báo cáo dựa trên số ngày
=COUNTIF(E$8:E$17,"D*")
=COUNTIF(E$8:E$17,"N*")
Bước 12: Tạo báo cáo dựa theo nhân viên
=COUNTIF($E8:$AI8,"D*")
Bước 13: Thêm định dạng
=LEFT(E8)="N"
13.3. Chọn kiểu định dạng bằng cách chọn Format mà người dùng đã chọn để tô màu xám cho ca đêm.
=LEFT(E8)="D"
