Tạo bảng chấm công tự động với Excel ứng dụng tại Trường Đại học Công nghiệp Quảng Ninh
SỐ 54/2021
KHOA HỌC VÀ CÔNG NGHỆ QUI
Tạo bảng chấm công tự động với Excel ứng dụng tại Trường Đại học Công
nghiệp Quảng Ninh
Create automatic timesheets with Excel application at Quang Ninh Industrial
University
Nguyễn Thị Thúy Chinh1,*
1Khoa Công nghệ thông tin, Trường Đại học Công nghiệp Quảng Ninh
Tóm tắt
Từ khóa:
Chấm công là một việc làm quen thuộc và vô cùng quan trọng tại những nơi sử
dụng lao động như trong các doanh nghiệp, công ty, cơ quan... và trường Đại học
Công nghiệp Quảng Ninh cũng vậy. Việc chấm công thực hiện thủ công sẽ tốn
nhiều thời gian, đặc biệt với các đơn vị có nhiều nhân viên. Nếu người chấm
công hiểu và có thể tự tạo một bảng chấm công tự động đơn giản với Excel, sẽ
giúp tiết kiệm thời gian và giảm thiểu sai sót trong công tác nhập liệu và thống kê
bảng chấm công.
Bảng chấm công; Công cụ
Conditional
Định dạng dữ liệu theo điều
kiện; Hàm Countif
Formatting;
Abstract
Keywords:
Attendance is a familiar and extremely important job in employers such as
businesses, companies, agencies ... and also Quang Ninh Industrial University.
Manual timekeeping will take a long time, especially for units with a lot of staff.
If timekeeper understands and can create a simple automatic timesheets with
Excel, it will save time and minimize errors in data entry and timesheet statistics.
Conditional
Formatting
Tool; Countif function;
Format data according to the
condition; Timesheets
107/2017/TT-BTC/2014/TT-BTC ngày 10/10/2017
của Bộ Tài chính như Hình 1 dưới đây.
Hoạt động chấm công tại Trường được thực
hiện như sau:
Như đã nói ở trên, tương ứng với mỗi ô và thực
tế ngày công của mỗi cán bộ, giảng viên thì người
phụ trách chấm công sẽ dùng 1 ký hiệu quy định để
hiển thị công theo chú thích Ký hiệu chấm công đã
có trong Bảng 1, cụ thể:
1. Giới thiệu về chấm công
Trước khi thực hiện tạo bảng chấm công tự
động, ta tìm hiểu về khái niệm chấm công và hoạt
động chấm công tại Trường Đại học Công nghiệp
Quảng Ninh.
Khái niệm chấm công [1]:
Bảng chấm công là loại văn bản dùng để theo
dõi ngày công thực tế mà nhân viên đã làm việc/
nghỉ việc/ nghỉ hưởng bảo hiểm xã hội trong tháng
– làm căn cứ tính trả lương cho nhân viên, người
lao động được đầy đủ và chính xác nhất.
Tại trường Đại học Công nghiệp Quảng Ninh,
mỗi phòng ban, trung tâm và khoa đều phải lập
bảng chấm công hàng tháng – chuyển lưu tại phòng
kế toán, sử dụng cùng các loại giấy tờ liên quan
khác áp dụng tính lương trả cho cán bộ và giảng
viên. Bảng chấm công thường được làm trên máy
tính bằng file Excel.
Lương thời gian
Ốm, điều dưỡng
Con ốm
+
Ô
Đi học
Nghỉ bù
H
Nb
Cô Nghỉ không lương No
Thai sản
Tai nạn
Ts Nghỉ lễ
Nl
N
T
Ngừng việc
Nghỉ phép
Nghỉ hè
P
Nh
Làm nửa ngày Nn
công
Cách điền số liệu vào mẫu bảng chấm công
Có nhiều phương pháp chấm công khác nhau,
tại trường Đại học Công nghiệp Quảng Ninh sử
dụng phương pháp chấm công theo ngày. Cán bộ và
giảng viên làm việc tại trường sẽ được thực hiện
chấm công theo từng ngày. Người phụ trách chấm
công sẽ dùng 1 ký hiệu (đã được quy ước) để chấm
công ngày đó tương ứng cho mỗi người.
Tương ứng với mỗi cột trong mẫu bảng chấm
công như hình 1 trên đây, người phụ trách chấm
công thực hiện điền như sau:
Cột A: ghi số thứ tự số nhân viên hiện có trong
phòng ban, trung tâm và khoa.
Cột B: ghi tên từng nhân viên hiện làm việc tại
phòng ban, trung tâm và khoa.
Cột 1-31: ghi các ngày trong tháng chấm công,
từ ngày 1 đến ngày cuối cùng của tháng đó.
Mẫu bảng chấm công mà Trường sử dụng
là Mẫu số C01 – HD Ban hành theo TT
22
KH&CN QUI
KHOA HỌC VÀ CÔNG NGHỆ QUI
SỐ 54/2021
Cột 32: ghi tổng số ngày công hưởng lương
thời gian của từng nhân viên trong tháng.
Cột 33: ghi tổng số ngày công nghỉ không
lương của từng nhân viên trong tháng.
được chấm công và để trống. Nhằm tránh nhầm lẫn
những ngày không chấm công và được chấm công,
những cột tương ứng với những ngày không được
chấm công sẽ được tô màu nền xám.
Cuối tháng, người chấm công, người phụ trách
đơn vị và hiệu trưởng tiến hành ký vào bảng chấm
công – chuyển cho bộ phận kế toán (kèm các loại
giấy tờ liên quan) kiểm tra, đối chiếu để quy ra công
tính lương cho cán bộ và giảng viên trong trường.
Cột 34: ghi tổng số ngày công hưởng bảo hiểm
xã hội của từng nhân viên trong tháng.
2. Một số hàm và kĩ năng trong Excel
Để tạo bảng chấm công tự động trong Excel
bạn cần biết một số kiến thức cơ bản như: Nhập và
định dạng dữ liệu thông thường: nhập dữ liệu cho 1
ô, định dạng in đậm, trộn ô, tăng giảm kích thước
của cột và hàng, …. Ngoài ra, bạn cần nắm được cú
pháp và cách thực hiện một số hàm và các kĩ năng
sau trong Excel.
2.1. Một số hàm trong Excel
Hàm Date
Cú pháp: DATE(year, month, day)
Trong đó, Year là năm, month là tháng, day là
ngày.
Chức năng: Trả về ngày ứng với ngày, tháng,
năm cung cấp trong tham số của hàm.
Ví dụ: = DATE (2021, 1, 2) – trả về kết quả
ngày 2 tháng 1 năm 2021.
Hàm Weekday
Cú pháp:
WEEKDAY(serial_number,[return_type])
Trong đó:
Serial_number: Là một biểu thức thời gian,
ngày cần kiểm tra thứ mấy trong tuần.
Return_type: Là một số xác định kiểu giá trị
trả về của thứ.
Return_type = 1 hoặc để trống: Ngày chủ nhật
có giá trị là 1, cứ lần lượt theo thứ tự thì ngày thứ
bảy có giá trị là 7.
Hình 1. Mẫu bảng chấm công trường Đại học Công
nghiệp Quảng Ninh,
Hàng ngày, người phụ trách chấm công sẽ căn
cứ vào tình hình thực tế của đơn vị mình để chấm
công cho từng nhân viên – ghi vào ngày tương ứng
trong các cột theo đúng ký hiệu quy định.
Tổng số công hưởng lương thời gian của từng
nhân viên là tổng số kí hiệu: +, Nh, H, Nb, Nl, Nn,
P trong tháng của mỗi người.
Tổng số công nghỉ không lương của từng nhân
viên là tổng số kí hiệu: No, N trong tháng của mỗi
người.
Tổng số công hưởng bảo hiểm xã hội của từng
nhân viên là tổng số kí hiệu: Ô, Cô, Ts, T trong
tháng của mỗi người.
Return_type = 2: Ngày thứ hai có giá trị là 1,…
ngày chủ nhật có giá trị là 7.
Return_type = 3: Ngày thứ hai có giá trị là 0,
…ngày chủ nhật có giá trị là 6.
Chức năng: Hàm WEEKDAY cho ta kết quả
các thứ trong tuần tương ứng với một ngày. Theo
mặc định, thứ được trả về là một số nguyên.
Ví dụ: Nhập dữ liệu cho ô B2 là ngày 31 tháng
1 năm 2021 – ngày chủ nhật thì: Weekday(B2) = 1.
Hàm Countif
Cú pháp: COUNTIF (Range, Criteria)
Trong đó:
Range: Là vùng chứa các ô cần đếm.
Chú ý, những ngày nghỉ cuối tuần (thứ 7 và
chủ nhật) và những ngày không có trong tháng (Ví
dụ: Tháng 2 chỉ có 28 hoặc 29 ngày) thì sẽ không
Criteria: Là tiêu chuẩn dạng số, biểu thức, hay
chuỗi văn bản chỉ định ô được đếm.
23
KH&CN QUI
SỐ 54/2021
KHOA HỌC VÀ CÔNG NGHỆ QUI
Chức năng: Đếm các ô trên
vùng Range thoả mãn tiêu chuẩn
Criteria.
Công nghệ thông tin, hiện khoa có tổng cộng 11
giảng viên.
3.1. Các bước thực hiện tạo bảng chấm công tự
động với Excel
Ví dụ: Cho dữ liệu như hình 2:
Ta có, Countif(A1:A6, “A”) =
3. Dễ thấy trong vùng dữ liệu
A1:A6 có 3 giá trị thỏa mãn điều
kiện là “A”.
Bước 1: Nhập và định dạng theo biểu mẫu
bảng chấm công trong Hình 1 ở trên.
Tùy thuộc vào số lượng nhân viên trong đơn vị
mà chúng ta thêm số hàng trong bảng cho phù hợp.
Hình 2. Vùng dữ liệu mẫu
Phần này ta cần lưu ý thao tác trộn ô, nhập dữ
liệu cho dòng số 5 về tháng và năm thì chữ “Tháng”
và chữ “năm” nhập riêng một ô, dữ liệu tháng và dữ
liệu năm nhập riêng một ô.
2.2.Định dạng dữ liệu theo điều kiện sử dụng
công thức với Conditional Formatting
Thông thường nhắc tới định dạng dữ liệu trong
Excel chúng ta sẽ nghĩ tới thao tác: Chọn vùng dữ
liệu cần định dạng, rồi chọn các lệnh định dạng cụ
thể nào đó (như in đậm, in nghiêng, …). Hôm nay,
tôi sẽ giới thiệu một cách định dạng khác mà ít bạn
biết đến: Định dạng dữ liệu theo điều kiện cụ thể
nào đó, bằng cách sử dụng công thức với công cụ
Conditional Formatting và các bước thực hiện như
sau:
Ví dụ như trong biểu mẫu hình 1 nếu ta muốn
nhập nội dung “Tháng 1 năm 2021” thì sẽ được
nhập như sau:
Ô M5 nhập: “Tháng”. Ô O5 nhập: 1. Ô P5
nhập: “năm”. Ô R5: 2021.
Bước 2: Hiển thị số ngày trong tháng trong
vùng dữ liệu C7:AG7.
Bước 1: Chọn các ô hay vùng dữ liệu muốn
định dạng.
Bước 2: Chọn lệnh Home\ Conditional
Formatting\ New Rule…
Chúng ta thực hiện lần lượt các thao tác sau:
Tại ô C7 ta nhập công thức: = DATE(R5,O5,1).
Biết rằng ô R5 chứa giá trị năm, ô O5 chứa giá trị
tháng của bảng chấm công cần lập.
Hộp thoại New Formatting Rule xuất hiện.
Trong mục Select a Rule Type chọn Use a formula
to determine which cells to format.
Tại ô D7 nhập công thức: =C7+1.
Tiếp theo, ta thực hiện thao tác sao chép công
thức từ ô D7 đến các ô từ E7 đến AG7.
Bước 3: Nhập công thức vào ô Format values
where this formula is true:
Bước 3: Định dạng bảng chấm công theo công
thức với công cụ Conditional Formatting.
= <Biểu thức điều kiện>
Tại bước này ta cần đánh dấu những cột tương
ứng với những ngày không được chấm công thay
đổi theo từng tháng.
Biểu thức điều kiện ở đây mô tả điều kiện định
dạng, nó chỉ nhận hai giá trị đúng hoặc sai. Nếu
biểu thức điều kiện đúng thì vùng dữ liệu thỏa mãn
sẽ được định dạng. Còn định dạng như thế nào thì
được thực hiện bằng cách chọn Format… và chọn
các định dạng mong muốn như: tô màu chữ (Font
color), tô màu nền (Fill), …. Chọn OK xác nhận
việc chọn định dạng. Cuối cùng chọn lệnh Ok để
xác nhận định dạng theo điều kiện.
Mặc định các cột không được chấm công sẽ
được tô màu nền là màu xám.
Do khoa Công nghệ thông tin cần quản lý có
11 nhân viên. Vậy vùng dữ liệu chấm công cần định
dạng sẽ là C9:AG19.
Các ngày không được chấm công được chia
làm hai trường hợp:
Ví dụ: Muốn tô màu nền đỏ cho các ô có giá trị
là A trong hình 2 trên. Ta thực hiện như sau:
Trường hợp 1: Các ngày là thứ 7 và chủ nhật
hàng tuần trong tháng
Chọn vùng dữ liệu: A1: A6
Với những cột tương ứng với những ngày
không được chấm công trong trường hợp 1 chỉ cần
thực hiện đánh dấu bằng cách tô màu nền xám cho
phần chấm công.
Home\ Conditional Formatting\ New Rule….
Hộp thoại New Formatting Rule xuất hiện.
Trong mục Select a Rule Type chọn Use a formula
to determine which cells to format.
Trường hợp 2: Các ngày thiếu trong những
tháng không đủ 31 ngày
Nhập công thức vào ô Format values where
this formula is true: = $A1 = “A”
Với ngày không được chấm công trong trường
hợp 2 ngoài việc đánh dấu bằng cách tô màu nền
xám cho phần chấm công thì những ngày thiếu
không được hiển thị ở phần ngày trong tháng. Và
một cách đơn giản để xử lý vấn đề này là sẽ định
Chọn Format để xác nhận định dạng muốn thực
hiện. Chọn Fill\ Chọn màu đỏ\ Ok\ Ok.
3. Xây dựng bảng chấm công tự động với Excel
Trong phần xây dựng bảng chấm công tự động,
tôi sẽ mô phỏng việc tạo bảng chấm công cho khoa
24
KH&CN QUI
KHOA HỌC VÀ CÔNG NGHỆ QUI
SỐ 54/2021
dạng cho ô ngày thiếu trong tháng không đủ 31
ngày có font chữ màu trắng.
Các tháng có ngày thiếu gồm:
Tháng 2 có năm chia hết cho 4 thì tháng 2 năm
đó có 29 ngày, nên ngày 30 và 31 không được chấm
công.
Tháng 2 có năm không chia hết cho 4 thì tháng
2 năm đó có 28 ngày, nên ngày 29, 30 và 31 không
được chấm công.
Tháng chỉ có 30 ngày gồm: 4, 6, 9, 11 nên ngày
31 không được chấm công.
Với những tháng có đủ 31 ngày (tháng: 1, 3, 5,
7, 8, 10, 12) thì ta chỉ cần định dạng đánh dấu các
ngày là thứ 7 và chủ nhật hàng tuần trong tháng như
trong trường hợp 1.
Ví dụ: Tháng 6 năm 2021 có 8 ngày là thứ 7 và
chủ nhật (ngày mùng 5, 6, 12, 13, 19, 20, 26, 27)
phần chấm công được tô nền xám. Và nó có 30
ngày thì cột chấm công 31 được tô nền xám và ngày
thứ 31 không hiển thị trên phần ngày trong tháng
được minh họa cụ thể trong Hình 3.
Định dạng đánh dấu các cột chấm công là
thứ 7 và chủ nhật hàng tuần trong tháng:
Bôi đen vùng dữ liệu muốn định dạng:
C9:AG19 Chọn lệnh Home\ Conditional
Formatting\ New Rule…\ Use a formula to
determine which cells to format Nhập công thức
vào ô Format values where this formula is true:
= OR(WEEKDAY(C$7) = 1, WEEKDAY(C$7) = 7)
Đây được gọi là công thức định dạng.
Chọn Format\ Fill\ chọn màu muốn đánh dấu
(màu xám)\ Ok \ Ok.
Hình 3. Minh họa các ngày không được chấm công
trong tháng.
Định dạng cho tháng 2 có 28 ngày:
Định dạng cho tháng 2 có 29 ngày
+ Định dạng cho phần ngày: Để trống (2 ngày
cuối)
+ Định dạng cho phần ngày: Để trống (3 ngày
cuối)
Các thao tác tương tự như định dạng cho phần
ngày với tháng 2 có 28 ngày chỉ khác:
Bôi đen vùng dữ liệu muốn định dạng:
AE7:AG7 Chọn lệnh Home\ Conditional
Formatting\ New Rule…\ Use a formula to
determine which cells to format Nhập công thức
vào ô Format values where this formula is true:
Vùng dữ liệu định dạng: AF7:AG7
Công thức định dạng:
= AND($O$5= 2, MOD($R$5,4) = 0)
+ Định dạng cho phần cột không được chấm
công tô nền ghi xám
Các thao tác thực hiện tương tự như đánh dấu
các cột chấm công là thứ 7 và chủ nhật chỉ khác:
Vùng dữ liệu định dạng: AF9:AG19 (tương
ứng với 2 cột chấm công cho 2 ngày 30 và 31)
= AND($O$5= 2, MOD($R$5,4) <> 0)
Chọn Format\ Font\ trong mục Color chọn
màu trắng\ Ok\ Ok.
+ Định dạng cho phần cột không được chấm
công tô nền ghi xám:
Các thao tác thực hiện tương tự như đánh dấu
các cột chấm công là thứ 7 và chủ nhật chỉ khác:
Công thức định dạng:
= AND($O$5=2,MOD($R$5,4)=0)
Định dạng cho các tháng có 30 ngày
Vùng dữ liệu định dạng: AE9:AG19 (tương
ứng với 3 cột chấm công cho 3 ngày 29, 30 và 31)
Công thức định dạng:
+ Định dạng cho phần ngày: Để trống (1 ngày
cuối)
= AND($O$5=2, MOD($R$5,4) <> 0)
25
KH&CN QUI
SỐ 54/2021
KHOA HỌC VÀ CÔNG NGHỆ QUI
Các thao tác tương tự như định dạng cho phần
ngày với tháng 2 có 28 ngày chỉ khác:
sheet chấm công tương ứng với 12 tháng trong năm
như sau:
Vùng dữ liệu định dạng: AG7
Công thức định dạng:
= OR($O$5=4, $O$5=6, $O$5=9, $O$5=11)
Thực hiện thao tác sao chép 11 lần sheet vừa
tạo để được 12 sheet có nội dung giống nhau. Sau
đó, đặt tên các sheet lần lượt từ tháng 1 đến tháng
12 và thực hiện nhập giá trị tháng và năm cho 12
sheet vừa tạo.
+ Định dạng cho phần cột không được chấm
công tô nền ghi xám
Các thao tác thực hiện tương tự như đánh dấu
các cột chấm công là thứ 7 và chủ nhật chỉ khác:
Vùng dữ liệu định dạng: AG9:AG19 (tương
ứng với cột chấm công cho ngày 31)
3.2. Hướng dẫn sử dụng và một số lưu ý
Sau khi đã tạo được file bảng chấm công của
năm như trên, việc đơn giản người chấm công cần
làm là mỗi ngày điền dữ liệu chấm công vào các ô
được chấm công. Nhập dữ liệu đến đâu thì bảng
chấm công sẽ tự động thống kê tổng số ngày công
đến đó.
Nhưng lưu ý, vì bảng chấm công là tự động
nên nếu bạn nhập sai kí hiệu chấm công như đã quy
định thì việc thống kê sẽ bị sai.
Công thức định dạng:
= OR($O$5=4, $O$5=6, $O$5=9, $O$5=11)
Bước 5: Thực hiện thống kê phần quy ra công
Nguyên tắc thực hiện: Đếm các ô thỏa mãn
điều kiện với hàm Countif.
Số công hưởng lương theo thời gian gồm
các ô có kí hiệu: +, Nh, H, Nb, Nl, Nn, P. Các bước
thực hiện như sau:
4. Kết quả
Bài báo đã trình bày quy trình và nguyên tắc
chấm công tại Trường Đại học Công nghiệp Quảng
Ninh. Bài báo cũng giúp cho bạn đọc hiểu được một
số hàm Excel, đặc biệt là kĩ năng định dạng dữ liệu
theo điều kiện sử dụng công thức với Conditional
Formatting. Đồng thời hướng dẫn chi tiết các bước
giúp tạo một bảng chấm công tự động trong Excel,
với ví dụ mô phỏng tạo bảng chấm công tự động
cho khoa Công nghệ thông tin tại Trường Đại học
Công nghiệp Quảng Ninh.
Nhập công thức cho ô AH9:
= COUNTIF(C9:AG9,"+")
+COUNTIF(C9:AG9,"Nh")+COUNTIF(C9:AG9,"
H")+COUNTIF(C9:AG9,"Nb")+COUNTIF(C9:AG
9,"Nl")+COUNTIF(C9:AG9,"Nn")/2+COUNTIF(C
9:AG9,"P")
Sao chép công thức từ ô AH9 cho vùng dữ liệu
từ AH10 đến AH19.
Số công nghỉ không lương gồm: No, N. Các
bước thực hiện như sau:
5. Thảo luận
Trong bài báo, tác giả đã thực hiện hướng dẫn
tạo bảng chấm công tự động cho một đơn vị trong
trường Đại học Công nghiệp Quảng Ninh là khoa
Công nghệ thông tin. Các khoa và các đơn vị khác
trong trường có thể theo hướng dẫn và làm bảng
chấm công cho đơn vị mình. Ngoài ra, nó cũng có
thể làm tài liệu tham khảo cho các cá nhân, doanh
nghiệp và cơ quan khác.
Nhập công thức cho ô AI9:
= COUNTIF(C9:AG9,"No")+
COUNTIF(C9:AG9,"N")
Sao chép công thức từ ô AI9 cho vùng dữ liệu
từ AI10 đến AI19.
Số công hưởng BHXH gồm: Ô, Cô, Ts, T.
Các bước thực hiện như sau:
Nhập công thức cho ô AJ9:
=COUNTIF(C9:AG9,"Ô")+ COUNTIF(C9:AG9,"Cô")
+ COUNTIF(C9:AG9,"Ts")+COUNTIF(C9:AG9,"T")
Sao chép công thức từ ô AJ9 cho vùng dữ liệu
từ AJ10 đến AJ19.
Cuối cùng ta cần định dạng cho những ô có giá
trị 0 hiển thị là ô trống: Chọn vùng dữ liệu cần định
dạng Chọn Format Cells\Number\Custom\ Trong
ô Type nhập: 0;-0;;@ Xác nhận lệnh Ok.
6. Kết luận
Việc chấm công sẽ giúp cho chủ doanh nghiệp
hay các bộ phận hành chính nhân sự, kế toán có thể
nắm được số ngày, số giờ công làm việc của mỗi
nhân viên. Người thực hiện chấm công sẽ phải mất
nhiều thời gian và công sức cho một công việc đơn
giản và lặp đi lặp lại. Việc tạo ra bảng chấm công tự
động sẽ rút ngắn thời gian làm việc cho người chấm
công, giảm thiểu sai sót, tăng hiệu quả lao động.
Tuy nhiên, công cụ chấm công trong bài báo đã giới
thiệu chỉ phù hợp với các doanh nghiệp, công ty, cơ
quan có quy mô nhỏ và vừa.
Bước 6. Tạo file chấm công cho 12 tháng trong
năm.
TÀI LIỆU THAM KHẢO
Với bảng chấm công tự động đã tạo được,
chúng ta có thể chấm công cho 1 tháng bất kì trong
1 năm bất kì. Tuy nhiên tại một thời điểm trên cùng
1 sheet chấm công thì chúng ta chỉ lưu lại được dữ
liệu chấm công của 1 tháng. Chúng ta có thể tạo 12
26
KH&CN QUI
5 trang
18760
Free
Bạn đang xem tài liệu "Tạo bảng chấm công tự động với Excel ứng dụng tại Trường Đại học Công nghiệp Quảng Ninh", để tải tài liệu gốc về máy hãy click vào nút Download ở trên
File đính kèm:
- tao_bang_cham_cong_tu_dong_voi_excel_ung_dung_tai_truong_dai.pdf
