Cách tạo bảng phân công trong Excel

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):

Với YearList (Years):

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),"")

6.2.  Bạn có thể nhận thấy ngày không có định dạng chính xác. Do đó, để đặt ở định dạng thích hợp, hãy chọn ô và nhấn Ctrl+1 để mở Format Cells > chọn Number > chọn Custom trong Category và đặt định dạng mm-dd-yyyy tùy chỉnh ở bên phải > chọn OK.

=IFERROR(EOMONTH(P4,0)+J4-1,"")

6.4. Để tính tổng số ngày, nhập công thức dưới đây vào ô AF4:

=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.

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")

7.5. Để thay đổi căn chỉnh theo chiều dọc, hãy nhấn Ctrl+1 để mở Format Cells và chọn góc 90 độ trong mục Alignment > chọn OK.

Sau đó, kết quả sẽ hiển thị (như hình dưới):

7.6. Kéo biểu tượng điều khiển vào ô AI6 để sao chép công thức.

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))))

10.2. Kéo vùng chọn từ AI8 đến AI17.

Bước 11: Tạo báo cáo dựa trên số ngày

=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*")

12.3. Kéo dữ liệu đến ô AK17.

12.4. Chèn công thức sau vào ô AL8 và kéo đến AL17.

12.5. Hợp nhất AK2:AL4 để tạo tiêu đề khác.

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.

13.4. Thao tác tương tự với ca ngày. Trong hộp thoại New Formatting Rule, chọn Use a formula to determine which cells to format trong mục Select a Rule Type và chèn công thức dưới đây:

=LEFT(E8)="D"

13.5. Chọn OK. Kết quả sẽ hiển thị (như hình dưới):

Bạn có thể tham khảo định dạng các kiểu khác để dễ nhìn hơn (như hình dưới):