Excel là công cụ hữu ích cho tất cả chúng ta trong công việc kế toán nói chung và kế toán tiền lương, quản lý lao động nói riêng. Một yêu cầu thường gặp trong công việc đó là tất cả chúng ta cần phải xây dựng bảng chấm công trên Excel. Trong nội dung này Học Excel Online sẽ hướng dẫn các bạn cách tạo mẫu bảng chấm công theo thông tư 133 trên Excel, theo mẫu tiên tiến nhất năm 2018. Tất cả chúng ta cùng tìm hiểu cách làm nhé:
Kết quả tất cả chúng ta muốn đạt được là 1 mẫu bảng chấm công như sau:
Table of Contents
Bước 1: Xác nhận 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 nhà cung cấp: gồm tên, địa chỉ của nhà cung cấp. 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” đặt ở trung tâm, dòng phía dưới thông tin nhà cung cấp
- Mẫu bảng: Phần này đặt trong 1 Textbox (Insert/Textbox). Khi đặt trong Textbox thì tất cả 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 tác độ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, mức lương ngạch bậc), 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 nhà cung cấp để 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 phòng ban nào, những ai phụ trách về tính đúng đắn 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 nền tảng pháp lý cho việc tính lương, nhận xét 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 để tất cả chúng ta có thể tham khảo và tùy vào đặc thù nhà cung cấp, yêu cầu công việc và tính chất các loại công thì tất cả chúng ta có thể lựa chọn các ký hiệu chấm công thích 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 trong bảng chấm công
Tại dòng 7 tất cả chúng ta có các ngày trong tháng, gồm 31 cột bắt đầu từ cột D tới cột AH
Để xác định các ngày trong tháng tất cả chúng ta sử dụng hàm DATE như sau:
Dù đã có software, nhưng tuyệt kỹ Excel vẫn cực kỳ trọng yếu 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 ô G5
- Month=Tháng: xác định tại ô C5
- Day=Ngày: tại ô D7 tất cả chúng ta có ngày trước hết là ngày 1
b. Xác định các ngày trong tháng
D7 là ngày 01 trong tháng
D7=Date(G5,C5,1)
E7 là ngày 02 trong tháng, to hơn D7 một ngày do đó:
E7=D7+1
Tương tự các ngày tiếp theo sẽ bằng ngày xưa đó + 1
Tuy nhiên hàm Date cho tất cả 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 tất cả chúng ta chỉ muốn nội dung hiển thị là số ngày. Do đó tất cả chúng ta sẽ thực hiện Format Cells cho vùng D7:AH7 như sau
Chọn toàn bộ vùng D7:AH7, 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ì tất cả 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 ô D7=Weekday(D7,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, tất cả chúng ta sẽ cần phối 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 tất cả chúng ta trả về nội dung tương ứng với thứ tự kết quả
Cách viết như sau:
D8=CHOOSE(WEEKDAY(D7,1),”CN”,”T2″,”T3″,”T4″,”T5″,”T6″,”T7″)
Vận dụng tương tự cho các ngày khác bằng cách sao chép công thức ở D8 tới AH8
Bước 4: Phân biệt ngày chủ nhật trong tháng
Sử dụng tính 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 D7:AH14 (vùng ngày trong tháng, ứng dụng toàn bộ các dòng chấm công)
- Chọn tính năng Conditional formatting (Xem thêm: Cách sử dụng tính 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 để thông 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 thời điểm tiến hành chấm công trong bảng chấm công thì tất cả 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)
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 tất cả chúng ta đã hoàn thiện 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ư vậy nào trong bảng chấm công, mời bạn tham khảo nội dung
Cách chấm công trên bảng chấm công theo thông tư 133 trên Excel tiên tiến nhất 2018
Tải về mẫu bảng chấm công theo thông tư 133 trên Excel 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 trọng yếu không. Theo tôi việc này rất trọng yếu 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 giải trình tài chính. Khi đó bạn có thể dễ dàng xác minh kết quả, biết được những thay đổi dữ liệu trong sổ NKC sẽ tác động như vậy nào tới kết quả trong các giải trình.
