Ngoài việc theo dõi, chấm công làm việc trong thời gian quy định, bộ phận quản lý lao động, tiền lương, kế toán lương còn phải theo dõi cả việc chấm công làm thêm giờ, làm việc ngày nghỉ tuần, làm việc ngày lễ, làm việc ban đêm. Vậy làm thế nào để có thể xây dựng mẫu bảng chấm công làm thêm giờ trên Excel? Cụ thể hơn là cách tạo mẫu bảng chấm công làm thêm giờ theo quy định của thông tư 133/2016/TT-BTC áp dụng cho năm 2018 như thế nào? Hãy cùng Học Excel Online tìm hiểu nhé.

Kết quả chúng ta mong muốn đạt được là 1 mẫu bảng chấm công như sau:

Bước 1: Xác định bố cục chung của bảng chấm công

Bảng chấm công theo thông tư 133/2016/TT-BTC được quy định theo mẫu số 01a-LĐTL gồm các phần cụ thể như sau:

• Thông tin đơn vị: gồm tên, địa chỉ của đơn vị. Mục này đặt ở góc trên cùng bên trái (2 dòng)
• Tên tiêu đề: “bảng chấm công làm thêm giờ” đặt ở chính giữa, dòng phía dưới thông tin đơn vị. Viết hoa toàn bộ để làm rõ nội dung này.
• Mẫu bảng: Phần này đặt trong 1 Textbox (Insert/Textbox). Khi đặt trong Textbox thì chúng ta có thể di chuyển toàn bộ đoạn Text này và đặt vào vị trí bất kỳ mà không phải lo việc ảnh hưởng tới các ô trong Sheet

• Thời gian chấm công: là tháng nào, năm nào. Đây là căn cứ phân biệt các bảng chấm công của các tháng, các năm khác nhau. Đồng thời đây cũng là căn cứ phân biệt các ngày trong tháng. Mỗi ô chứa số Tháng, Năm cần đặt ở những ô riêng để có thể sử dụng làm tham chiếu trong hàm xác định ngày trong tháng.
• Nội dung bảng chấm công: Gồm đối tượng chấm công (họ tên), ngày công trong tháng (ngày trong tháng để ghi nhận chấm công theo từng ngày) và kết quả chấm công (quy ra công). Căn cứ theo đặc điểm đơn vị để xác định bảng chấm công có bao nhiêu dòng, tương ứng với việc chấm công cho bao nhiêu người.
• Thời gian ký và người có trách nhiệm ký tên: Bảng chấm công được lập ra bởi bộ phận nào, những ai chịu trách nhiệm về tính chính xác của kết quả chấm công… đều phải ký tên trên bảng chấm công để đảm bảo các cơ sở pháp lý cho việc tính lương, đánh giá lao động…
• Ký hiệu chấm công: Trong thông tư 133 có quy định sẵn một vài ký hiệu chấm công để chúng ta có thể tham khảo và tùy theo đặc thù đơn vị, yêu cầu công việc và tính chất các loại công thì chúng ta có thể lựa chọn các ký hiệu chấm công phù hợp. Đây là căn cứ để xác định các loại công và tính ra kết quả chấm công.

Bước 2: Hàm Excel xác định ngày trong tháng

Tại dòng 7 chúng ta có các ngày trong tháng, gồm 31 cột bắt đầu từ cột C tới cột AG

Để xác định các ngày trong tháng chúng ta sử dụng hàm DATE như sau:

Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:

a. Cấu trúc hàm Date

DATE(Year, Month, Day)

• Year=Năm: xác định tại ô F5
• Month=Tháng: xác định tại ô C5
• Day=Ngày: tại ô C7 chúng ta có ngày đầu tiên là ngày 1

b. Xác định các ngày trong tháng

C7 là ngày 01 trong tháng

C7=Date(F5,C5,1)

D7 là ngày 02 trong tháng, lớn hơn C7 một ngày do đó:

D7=C7+1

Tương tự các ngày tiếp theo sẽ bằng ngày trước đó + 1

Tuy nhiên hàm Date cho chúng ta định dạng ngày đầy đủ gồm: dd/mm/yyyy (ngày/tháng/năm) trong khi tại dòng 7 chúng ta chỉ muốn nội dung hiển thị là số ngày. Do đó chúng ta sẽ thực hiện Format Cells cho vùng C7:AG7 như sau

Chọn toàn bộ vùng C7:AG7, chọn Format Cells.

Trong tab Number chọn mục Custom

Trong thẻ Type nhập 2 chữ d là dd (chỉ hiển thị cho ngày)

Bước 3: Xác định thứ theo ngày

Muốn biết 1 ngày cụ thể là thứ mấy thì chúng ta sử dụng hàm Weekday

(Xem thêm: Cách sử dụng hàm Weekday trong Excel)

Thứ của ngày trong ô C7=Weekday(C7,1)

Tuy nhiên hàm Weekday chỉ trả về kết quả từ 1 đến 7, tương ứng với thứ tự là (khi chọn tham số trong hàm weekday là 1):

• Số 1 tương ứng với ngày Chủ nhật (CN)
• Số 2 tương ứng với ngày Thứ hai (T2)
• Số 3 tương ứng với ngày Thứ ba (T3)
• Số 4 tương ứng với ngày Thứ tư (T4)
• Số 5 tương ứng với ngày Thứ năm (T5)
• Số 6 tương ứng với ngày Thứ sáu (T6)
• Số 7 tương ứng với ngày Thứ bảy (T7)

Để có thể xác định Thứ về đúng dạng tiếng việt, chúng ta sẽ cần kết hợp hàm Choose với hàm Weekday.

(Xem thêm: Cách sử dụng hàm Choose trong Excel)

Hàm Choose cho phép chúng ta trả về nội dung tương ứng với thứ tự kết quả

Cách viết như sau:

C8=CHOOSE(WEEKDAY(C7,1),”CN”,”T2″,”T3″,”T4″,”T5″,”T6″,”T7″)

Áp dụng tương tự cho các ngày khác bằng cách sao chép công thức ở C8 tới AG8

Bước 4: Phân biệt ngày chủ nhật trong tháng

Sử dụng chức năng Conditional formatting để tự động phân biệt các ngày chủ nhật trong tháng như sau:

Căn cứ vào dòng 8 ta có ngày chủ nhật là ngày có ký hiệu CN

• Chọn toàn bộ vùng C7:AG14 (vùng ngày trong tháng, áp dụng tất cả các dòng chấm công)
• Chọn chức năng Conditional formatting (Xem thêm: Cách sử dụng chức năng Conditional formatting)
• Thiết lập conditional formatting theo điều kiện như sau:

Việc thiết lập định dạng để cảnh báo các cột ngày Chủ nhật sẽ được định dạng tại mục Format (bên cạnh mục Preview)

Kết quả như sau:

Bước 5: Công thức quy ra công

Sau khi tiến hành chấm công trong bảng chấm công thì chúng ta sẽ thực hiện tính toán số công trong tháng đã chấm của từng người.

Cách thực hiện như sau:

Sử dụng hàm COUNTIF để đếm số lần xuất hiện của từng ký hiệu chấm công tương ứng với loại hình công cần tính

(xem thêm: Cách sử dụng hàm COUNTIF)

Ví dụ: Đếm số buổi làm việc ban đêm => Đếm số ký hiệu Đ (giá trị ở ô B20)

AK10=COUNTIF(C10:AG10,$B$20)

Sử dụng hàm SUMIF để tính tổng số giờ công (nếu chấm công theo số giờ)

(xem thêm: Cách sử dụng hàm SUMIF)

Như vậy chúng ta đã hoàn thành xong mẫu bảng chấm công theo thông tư 133 rồi.

Để tìm hiểu kỹ hơn cách chấm công như thế nào trong bảng chấm công, mời bạn tham khảo bài viết

Cách chấm công trên bảng chấm công làm thêm giờ theo thông tư 133 trên Excel mới nhất 2018

—

Chúc các bạn học tốt cùng Học Excel Online!

Tải về mẫu bảng chấm công tại địa chỉ: http://bit.ly/2jKfJ6O

Bạn có nghĩ rằng làm kế toán tổng hợp trên Excel quan trọng không, bởi trên Excel bạn có thể nắm được cách tổ chức, quản lý dữ liệu kế toán, đồng thời tự tay xây dựng công thức lên các báo cáo tài chính. Khi đó bạn có thể dễ dàng kiểm tra kết quả, biết được những thay đổi dữ liệu trong sổ NKC sẽ ảnh hưởng như thế nào tới kết quả trong các báo cáo.