trên mô hình có 13 trang tính, nhưng khi bắt đầu làm việc, chúng tôi chỉ cần 2 trang tính:
+ 1 trang tính là danh sách nhân viên (dsnv)
+ 1 sheet là tháng 1 (đầy đủ 1 tháng, bạn có thể copy sheet này và đổi tên sau)
tệp nhân viên:
Nội dung chính của sheet này là tên và mã của nhân viên. bạn cần tạo mã nhân viên vì có thể có các trường hợp trùng tên. quản lý bằng mã sẽ tránh trường hợp trùng lặp.
Ngoài ra còn có các nội dung khác liên quan đến nhân viên như: ngày sinh, thành phố xuất xứ, số cmt, ngày nhập cảnh … bạn có thể tạo theo cách sau:
+ cột e (ngày sinh), làm tối toàn bộ cột (đặt con trỏ chuột lên đầu cột – chữ e, nhấp để chọn toàn bộ cột), định dạng ô / số / khách hàng / chọn dd-mm-yyyy. hiệu lực: khi bạn nhập bất kỳ dữ liệu ngày nào vào cột này, dữ liệu đó sẽ tự động trở về dạng dd-mm-yyyy (01-01-2014)
Bảng danh sách nhân viên chủ yếu được nhập bằng tay nên không phức tạp chút nào. lưu ý: chừa khoảng 2-3 dòng trên cùng để tạo liên kết đến các sheet khác. 1 cột bên trái để dự phòng nếu bạn cần thêm thứ gì đó.
Tờ tháng 1
đầu tiên bạn tạo khung cho bảng chấm công gồm các nội dung sau: tiêu đề: bảng chấm công, tháng, bộ phận chấm công, quy tắc các ngày làm việc trong tháng, các cột gồm: mã nhân viên, tên nhân viên, ngày trong tháng (31 cột tương ứng với 31 ngày – số ngày lớn nhất trong tháng), 4-5 cột để tính toán, 1 cột cho ghi chú
sau đó giảm chiều rộng của các cột để chúng rõ ràng và dễ nhìn. các cột ngày tháng có thể được rút ngắn đủ để chấm công. cột để làm việc không cần phải quá rộng. chủ yếu là cột tên nhân viên và mã là đủ.
cách giảm chiều rộng: bôi đen các cột ngày tháng, tính toán công việc (trong ví dụ, tô sáng từ cột e đến cột am), sau đó giảm chiều rộng trong cột e khoảng 30 pixel (hoặc tắt từ cột e thành cột m, sau đó nhấp vào lệnh chiều rộng cột, chọn 3,13 tương ứng với 30 pixel)
vậy là bạn đã hoàn thành khung bảng chấm công. Tiếp theo, chúng ta sẽ thực hiện thao tác chọn ngày trên bảng chấm công.
tạo ngày trong bảng chấm công
– trước tiên chúng tôi xác định năm sử dụng. trong ô d1 nhập giá trị năm. Ví dụ năm 2014
– tại ô b4 (xác định tháng thời gian), chúng ta nhập hàm xác định ngày: = date ($ d $ 1; 1; 1) lưu ý rằng dấu phân cách giữa các thành phần của hàm có thể là a; hoặc, tùy thuộc vào cấu hình máy tính của bạn. trong việc sử dụng máy tính của tôi; nội dung của hàm date: xác định giá trị của ngày theo giá trị được chỉ định trong hàm. các giá trị theo thứ tự là năm, tháng, ngày. trong hàm trên, nó là năm = giá trị trong ô d1, tháng = 1, ngày = 1
– sau khi nhập hàm, trong ô b4, chọn định dạng ô / tùy chỉnh / nhập giá trị [“tháng” mm “năm” yyyy] vào hộp loại bên phải, sau đó nhấn ok (dấu ngoặc kép + tháng + dấu cách + dấu ngoặc kép trích dẫn + mm + trích dẫn kép + năm + không gian + trích dẫn kép + yyyy)
xem thêm : định dạng ngày tháng trong excel
: Bạn có thể hợp nhất các ô từ ô b4 đến d4 để hiển thị đủ nội dung cho dễ xem.
– trong ô đầu tiên (ô e9), hãy nhập = b4 để chỉ định ngày đầu tiên của tháng.
– trong ô f9, nhập = e9 + 1 (ngày tiếp theo của tháng)
– sao chép công thức trong ô f9 sang các ô liền kề, lên đến ô 31 (ô ai9) như sau:
- phương pháp 1: đánh dấu từ ô f9 đến ô ai9, nhấn ctrl + r
- phương pháp 2: nhấp vào ô f9, giữ chuột tại vị trí của chấm đen trong ô để con trỏ chuột trở thành +, kéo chuột đến ô ai9, rồi thả
- ngày trong tuần (e9) : là lấy giá trị của ngày trong tuần từ ô e9. nếu nội dung trong hàm ngày trong tuần không có quy định về thứ tự các ngày trong tuần, nó sẽ được trả về theo thứ tự Chủ nhật, Thứ hai, Thứ ba … Thứ bảy (bắt đầu từ chủ nhật, kết thúc thứ bảy), và trả về một giá trị số từ 1 đến 8
- select : là một hàm chọn giá trị trả về. giá trị đầu tiên trong hàm là giá trị được chọn để xác định. các giá trị tiếp theo là những giá trị được trả về dựa trên giá trị đầu tiên.
- if: nếu ô ag9 là rỗng, thì ô ah9 cũng sẽ là rỗng (tức là trong trường hợp 28 ngày trong tháng 2, sẽ không có 30)
- if thứ 2: Tương tự như hàm if trong ô ag9, tức là nếu tháng 2 có 29 ngày thì ngày 30 sẽ không được hiển thị.
- if 1st: nếu ah9 là null, thì ai9 cũng sẽ là null (tức là nếu không có ngày 30 thì cũng không có ngày 31)
- nếu thứ 2: nếu tháng chỉ có 30 ngày, thì sẽ không có 31 ngày
- ngày làm việc thực tế (ngày làm việc đầy đủ):
- làm việc nửa ngày (nửa ca, nửa ngày …)
- ngày nghỉ được hưởng nguyên lương (đi học, họp, đi nghỉ … nghỉ phép có lương)
- nghỉ không lương
- ngày làm việc thực tế: x
- nửa ngày làm việc: v
- ngày nghỉ hưởng lương: p
- nghỉ không lương: k
- bệnh tật, thai sản: hoặc
- ô ak11 (một nửa công việc) = countif ($ e11: $ ai11; $ g $ 35)
- ô al11 (giấy phép trả phí) = countif ($ e11: $ ai11; $ g $ 36)
- ô am11 (nghỉ không lương) = countif ($ e11: $ ai11; $ g $ 37)
- an11 (ốm đau, thai sản) = countif ($ e11: $ ai11; $ g $ 38)
– bôi đen từ ô e9 đến ô ai9, chọn ô định dạng / tùy chỉnh / gõ mục ghi dd rồi nhấn ok (chỉ hiển thị số ngày) nội dung trong ô e9 đến ô ai9 sẽ hiển thị ngày như hình bảng trên.
– trong ô e10, nhập hàm = select (ngày trong tuần (e9); “Chủ nhật”; “t. hai”; “t. ba”; “t. thứ tư”; “t. năm”; “t. sáu “”; “T. Bảy”)
nội dung chức năng:
trong hàm trên, ngày 1 tháng 1 năm 2014 khi sử dụng hàm ngày trong tuần sẽ trả về giá trị 4, khi sử dụng hàm select với thứ tự tương ứng là chủ nhật, thứ hai, thứ ba … thứ bảy thì giá trị 4 sẽ tương ứng với thứ tư. vì hàm ngày trong tuần không tự trả về tiếng Việt nên chúng ta phải kết hợp nó với hàm lựa chọn để lấy nội dung của ngày nào trong tuần
– sao chép công thức trong ô e10 sang các ô tiếp theo bên phải, tối đa ô ai10 (ngày 31)
tuy nhiên, không phải tháng nào cũng có 31 ngày, vì vậy để tránh hiển thị ngày của các tháng khác trong tháng này, chúng ta thực hiện các thao tác sau: + từ ngày 29 (ô ag9), chúng ta điều chỉnh hàm như sau: = if (day (af9 + 1) = day (e9); ””; af9 + 1) nội dung hàm: nếu giá trị ngày của ô af9 +1 bằng giá trị ngày của ô e9 sẽ trả về null, nếu không bằng giá trị trả về giá trị ô af9 + 1 (tức là nếu tháng 2 có 28 ngày thì ngày 29 sẽ là ngày 1 tháng 3, do đó giá trị ngày là 1, bằng giá trị ngày của ô e9, nếu vậy, sẽ trống và không hiển thị ngày 1 tháng 3. Nếu tháng 2 đó có 29 ngày, thì nó sẽ đánh dấu 29)
+ trong ô ah9, chúng tôi sử dụng hàm sau: = if (ag9 = ””; ””; if (day (ag9 + 1) = day (e9); ””; ag9 + 1)) nội dung của chức năng:
+ trong ô ai9, chúng ta sử dụng hàm sau: = if (ah9 = ””; ””; if (day (ah9 + 1) = day (e9); ””; ah9 + 1))
nội dung chức năng:
+ khi các ô ag9, ah9, ai9 trống, chức năng chọn trên các ô ag10, ah10, ai10 sẽ không thành công. nhưng nó sẽ không ảnh hưởng đến bảng tính, vì vậy đừng lo lắng.
Đây là cách chúng tôi tạo các ngày trong tháng và các ngày trong tuần.
tiếp theo là tính năng tự động thay đổi màu sắc để thứ bảy và chủ nhật khác với ngày thường
+ bôi đen nội dung trong bảng (từ e9 đến ai30, tức là toàn bộ nội dung về ngày trong tháng và chấm công của nhân viên), chọn chức năng định dạng có điều kiện (condition formatting), trong phần có định dạng tùy chỉnh, hãy chọn quy tắc mới.
+ đặt định dạng cho các cột không chứa giá trị ngày (ví dụ: tháng 2 có 28 ngày, tự động đánh dấu các ngày trống để phân biệt chúng)
Bước 1: Trong ô b4, tạm thời thay đổi giá trị tháng thành 2 để kiểm tra
bước 2: đánh dấu vùng trong bảng, chọn chức năng định dạng có điều kiện / quy tắc mới / sử dụng công thức …, nhập hàm = if (e $ 9 = ””; true; false), chọn định dạng là màu màu xám cho văn bản và nền, chúng tôi có kết quả sau:
Khi bạn hoàn tất, hãy nhớ trả lại giá trị của tháng 1 trong ô b4.
ugh, vậy là xong việc đặt ngày cho bảng chấm công. bây giờ bước cuối cùng là đặt lại ký hiệu giờ.
đặt dấu thời gian
+ chọn một số ký hiệu thời gian cho một số loại công việc như sau:
…. (tùy từng đơn vị mà chọn cách tính thời gian khác nhau)
lưu ý rằng mỗi ký hiệu thời gian sẽ tương ứng với 1 cột công việc (hoặc có thể kết hợp), cộng với 1 cột để tính tổng khối lượng công việc
Trong ví dụ tôi đưa ra như sau:
hàm, công thức làm việc
trong cột ngày thực tế (cột aj), trong ô aj11, hãy đặt hàm sau:
= count.if ($ e11: $ ai11; $ g $ 34)
nội dung hàm: đếm số lần xuất hiện của giá trị trong ô g34, trong khoảng từ e11 đến ai11. giá trị trong ô g34 là ký hiệu giờ hiện hành ngày làm việc đầy đủ, phạm vi e11 đến ai11 là số ngày làm việc trong tháng đầu tiên (hàng 11), cố định cột e và ai để khi bạn sao chép công thức, bạn không thấy vùng .timing bị ảnh hưởng
tương tự như các cột khác, chúng tôi đặt công thức:
tổng công việc sẽ được tính toán dựa trên yêu cầu công việc của đơn vị. Ví dụ: tổng số ngày làm việc = số ngày làm việc thực tế + công việc bình quân x 0,5 + nghỉ phép có lương + ốm đau, thai sản
ok11 = aj11 + ak11 * 0.5 + al11 + an11
sau khi đăng công thức, chúng tôi sao chép nó cho các nhân viên khác
khi hoàn tất, nó sẽ giống như sau:
vậy là đã hoàn thành việc định thời gian, tạo các ký hiệu và tính toán các con số công việc. bây giờ bạn có thể kiểm tra công thức bằng cách chạm vào một số ký hiệu trên bàn để kiểm tra.
vậy là chúng tôi đã hoàn thành bảng thời gian cho tháng Giêng. bây giờ chỉ là vấn đề sao chép những tháng còn lại.
Ngoài công việc chấm công chúng ta còn cần làm các công việc khác liên quan đến tiền lương, bảo hiểm, thuế thu nhập … để nắm bắt được kiến thức và cách ứng dụng excel trong công việc hành chính – nhân sự, mời các bạn cùng tham gia quản trị nguồn nhân lực. khóa học excel trực tuyến a-z tiết lộ rằng bạn còn nhận được phần mềm quản lý nhân sự miễn phí khi tham gia khóa học này. Nhân tiện, bạn cũng có thể tải file excel để tiện theo dõi các giấy phép khi tham gia khóa học. Thông tin chi tiết xem tại: học excel trực tuyến