Bảng chấm công trên Excel là một trong những từ khóa được search nhiều nhất trên google và được các bạn quan tâm rất nhiều về chủ đề bảng chấm công trên Excel. Trong bài viết này, topviec.vn sẽ Hướng dẫn lập bảng chấm công trên Excel vô cùng đơn giản
Table of Contents
Hướng dẫn lập bảng chấm công trên Excel vô cùng đơn giản
Tôi sẽ tut phương pháp lập 1 mô hình bảng chấm công ví dụ như sau:
- Gồm 13 sheet, mỗi sheet là 1 tháng và 1 sheet sử dụng danh sách nhân viên
- Trung bình mỗi tháng là 20 nhân viên
- Ký hiệu chấm công có thể cải thiện được tùy người sử dụng (mỗi người có thể chấm 1 kiểu ký hiệu, nhưng sẽ đồng nhất trong 12 tháng)
- Trong mỗi bảng chấm công có ghi rõ ngày, thứ trong tháng. Vào thứ 7 và chủ nhật thì tự đổi màu không giống đối với ngày thường.
- Hàng ngày sẽ chấm công vào các ngày trong tháng. Cuối tháng sẽ tự tính số tổng số công trong tháng.
- Tên nhân sự trong bảng chấm công sẽ lấy từ mục lục nhân viên
- đủ nội lực liên kết giữa các tháng với nhau để easy thao tác.
CÁC BƯỚC THỰC HIỆN CHI TIẾT
BỐ CỤC CÁC SHEET
Về mô hình là 13 sheet, nhưng khi khởi đầu sử dụng ta chỉ cần 2 sheet:+ 1 sheet là danh sách nhân viên (DSNV)
+ 1 sheet là Tháng 1 (làm hoàn chỉnh cho 1 tháng, các tháng sau đủ sức sao chép sheet này rồi đổi tên)
SHEET danh mục NHÂN VIÊN:
content chủ yếu trong sheet này là Tên và Mã nhân sự. Cần tạo mã nhân viên vì đủ nội lực có trường hợp trùng tên. Khi quản lý theo mã sẽ tránh trường hợp bị trùng lặp.
bên cạnh đó còn có các content khác liên quan tới nhân viên, như: ngày sinh, quê quán, số comment, ngày vào làm…
Bạn đủ nội lực tạo như sau:
+ Cột E (ngày sinh), bạn bôi đen cả cột (đưa con trỏ chuột vào vị trí trên cùng của cột – chữ E, click chọn cả cột), bạn định dạng Format cell/Number/Custome/ chọn dd-mm-yyyy . Tác dụng: khi bạn nhập bất kỳ dữ liệu kiểu ngày tháng vào cột này sẽ tự động đưa về dạng dd-mm-yyyy (01-01-2014)
Sheet mục lục nhân sự hầu hết là nhập bằng tay nên k có gì phức tạp.
Lưu ý: bạn chừa ra khoảng 2-3 định dạng trên cùng để tạo link tới các sheet khác. Bên trái cách ra 1 cột để dự phòng nếu cần bổ sung gì thêm.
SHEET THÁNG 1
trước nhất bạn tạo khung cho bảng chấm công, gồm các nội dung: tiêu đề – bảng chấm công, tháng, bộ phận chấm công, định mức ngày công trong tháng, các cột gồm: mã nhân sự, 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 to nhất của 1 tháng), 4-5 cột tính quy ra công, 1 cột để ghi chú
Tiếp sau đó bạn co độ rộng của các cột sao cho gọn và easy Quan sát. Các cột ngày trong tháng đủ sức co lại nhỏ vừa quá đủ để chấm công thôi. Cột quy ra công cũng không cần rộng lắm. Chủ yếu cột tên và mã nhân sự hiển thị quá đủ là được.
cách co độ rộng: bạn bôi đen các cột ngày trong tháng, quy ra công (trong gợi ý là bôi đen từ cột E đến cột AM), sau đó co độ rộng ở cột E khoảng 30 pixels (hoặc bôi đen từ cột E đến cột AM, sau đó click lựa chọn command Column Width, chọn 3,13 tương ứng 30 pixels)
Vậy là bạn đang sử dụng xong phần khung của Bảng chấm công. tiếp theo ta sẽ thực hiện thao tác chọn ngày tháng của bảng chấm công.
TẠO NGÀY THÁNG TRONG BẢNG CHẤM CÔNG
– trước nhất ta dựng lại năm dùng. Tại ô D1 nhập giá trị của năm. gợi ý năm 2014
– Tại ô B4 (xác định tháng chấm công), ta nhập hàm dựng lại ngày: =date($D$1;1;1)
lưu ý dấu ngăn phương pháp các yếu tố trong hàm đủ sức là dấu ; hoặc dấu , tùy xây dựng của máy tính. Ở máy tính của tôi sử dụng dấu ;
nội dung hàm date: định hình giá trị của ngày tháng theo giá trị dựng lại trong hàm. giá trị theo thứ tự là Năm, tháng, ngày. Ở hàm trên là Năm = giá trị tại ô D1, Tháng = 1, Ngày = 1
– Sau khi nhập hàm xong, tại ô B4, chọn format cell / custom / nhập trị giá [“tháng “mm” năm “yyyy] vào ô Type bên phải, xong rồi ấn ok (dấu nháy kép + tháng + dấu phương pháp + dấu nháy kép + mm + dấu nháy kép + năm + dấu mẹo + dấu nháy kép + yyyy)
Bằng việc thấu hiểu rằng hầu hết nhân viên văn phòng thường mất nhiều thời gian tra cứu mẹo dùng các hàm, các tính năng trong Excel, gặp khó khăn trong lồng ghép hàm, giải quyết dữ liệu, công việc bận rộn không có thời gian học. Nên Học Excel Trực tuyến đã design ra chương trình Excel từ cơ bản tới nâng cao dành cho người đi làm. Đây là chương trình học online, học viên đủ sức chủ động học mọi lúc mọi nơi, bàn bạc trực tiếp với giáo viên tại bài giảng trong suốt quá trình học.
Với khoá học này học viên sẽ nắm được: :
- Trọn bộ tool định hình báo cáo chuyên nghiệp: Format, Cell, Number, style,…không mất thời gian cả ngày ngồi sửa báo cáo
- all skill lọc dữ liệu, lọc dữ liệu nâng cao trong Excel giúp sức cho mục đích trích xuất dữ liệu
- Data Validation kiểm soát dữ liệu khi nhập vào một vùng trong bảng tính
- TẤT TẦN TẬT hơn 100 hàm phổ biến, bí quyết mảng trong Excel, học tới đâu nhớ tới đó
- Bộ 36 PHÍM TẮT giúp thao tác mau gấp đôi
- BÁO CÁO SIÊU TỐC trong 1 phút với Pivot Table
- Hỏi đáp trực tiếp tại bài giảng với giáo viên trong suốt công cuộc học
- CHUYÊN NGHIỆP trong báo cáo bằng Biểu đồ, đồ thị,….
- 142+ bài giảng, 13+ giờ học, 200+ gợi ý thực tế cùng chuyên gia
- 100+ kỹ năng, thủ thuật Excel thông dụng với các hàm: SUMIF, HLOOKUP, VLOOKUP, DATEDIF…
xem thêm: định hướng ngày tháng trong Excel
– Bạn có thể marge cell từ ô B4 đến D4 để cho hiển thị đủ content cho dễ Nhìn.
– Tại ô ngày 1 ( ô E9), bạn nhập =b4 để dựng lại ngày đầu tiên trong tháng.
– Tại ô F9, bạn nhập =e9+1 (ngày tiếp theo trong tháng)
– copy cách thức tại ô F9 sang các ô bên cạnh, cho đến ô ngày thứ 31 (ô AI9) bằng cách sau:
- mẹo 1: Bôi đen từ ô F9 đến ô AI9, click Ctrl + R
- hướng dẫn 2: click chuột vào ô F9, giữ chuột tại vị trí dấu chấm đen trong ô để con trỏ chuột trở thành dấu +, kéo chuột tới ô AI9 rồi thả ra
– Bôi đen từ ô E9 đến ô AI9, lựa chọn Format cells / custom / mục Type bạn gõ chữ dd rồi bấm ok (chỉ hiện thị số ngày)
nội dung trong ô E9 đến ô AI9 sẽ hiển thị ra ngày giống như trong bảng trên.
– Tại ô E10 nhập hàm =CHOOSE(WEEKDAY(E9);”Chủ nhật”;”T. Hai”;”T. Ba”;”T. Tư”;”T. Năm”;”T. Sáu”;”T. Bảy”)
content hàm:
- Weekday(e9) : là lấy trị giá ngày trong tuần của ô E9. Nếu content trong hàm weekday k có đặt quy định về thứ tự ngày trong tuần thì sẽ tự trả về theo thứ tự Chủ nhật, thứ hai, thứ 3… thứ 7 (Bắt đầu là chủ nhật, kết thúc là thứ 7), và trả về trị giá số từ 1 đến 8
- Choose: là hàm lựa chọn trị giá trả về. giá trị trước nhất trong hàm là giá trị được lựa chọn để làm căn cứ xác định. Các giá trị kế tiếp là nội dung được trả về tương ứng theo trị giá trước tiên.
Trong hàm trên, ngày 01/01/2014 khi dùng hàm weekday sẽ trả về giá trị là 4, khi sử dụng hàm choose với thứ tự tương ứng là Chủ nhật, thứ hai, thứ ba… thứ bảy thì trị giá 4 sẽ tương ứng với thứ tư. Bởi vì hàm weekday không tự trả về thứ theo tiếng việt, nên ta phải phối hợp với hàm choose để có được nội dung là thứ mấy trong tuần
– sao chép bí quyết tại ô E10 sang các ô tiếp theo bên phải, cho tới ô AI10 (ngày thứ 31)
tuy nhiên không hề tháng nào cũng có 31 ngày, nên để tránh việc hiển thị các ngày của tháng khác vào tháng này, ta thực hiện các thao tác sau:
+ diễn ra từ ngày thứ 29 (ô AG9), ta điều chỉnh về hàm như sau: =IF(DAY(AF9+1)=DAY(E9);””;AF9+1)
content hàm: Nếu giá trị ngày của ô AF9 +1 bằng trị giá ngày của ô E9 sẽ trả về giá trị là rỗng, nếu không bằng thì sẽ trả về giá trị ô AF9 + 1 (Nghĩa là nếu tháng 2 có 28 ngày, thì ngày thứ 29 sẽ là ngày 01/03, vậy giá trị ngày là 1, bằng với giá trị ngày của ô E9, nếu đúng giống như thế thì sẽ là rỗng, k hiển thị ngày 01/03. Còn nếu tháng 2 đó có 29 ngày thì sẽ hiện ngày 29)
+ Tại ô AH9, ta dùng hàm sau: =IF(AG9=””;””;if(DAY(AG9+1)=DAY(E9);””;AG9+1))
content hàm:
- IF thứ 1: Nếu tại ô AG9 là trị giá rỗng, thì ô AH9 cũng sẽ có giá trị rỗng (tức là vào trường hợp tháng 2 có 28 ngày thì sẽ k có ngày thứ 30)
- IF thứ 2: tương tự với hàm if tại ô AG9, tức là trường hợp tháng 2 có 29 ngày, thì sẽ k hiển thị ngày 30.
+ Tại ô AI9, ta sử dụng hàm sau: =IF(AH9=””;””;if(DAY(AH9+1)=DAY(E9);””;AH9+1))
nội dung hàm:
- IF thứ 1: Nếu AH9 là giá trị rỗng, thì AI9 cũng sẽ có giá trị rỗng (tức là trường hợp không có ngày thứ 30 thì cũng k có ngày thứ 31)
- IF thứ 2: trường hợp tháng chỉ có 30 ngày thì sẽ k có ngày thứ 31
+ Khi các ô AG9, AH9, AI9 là rỗng thì hàm choose ở các ô AG10, AH10, AI10 sẽ phát sinh lỗi. Nhưng không ảnh hưởng gì đến bảng tính nên không cần sợ về việc này.
giống như vậy ta vừa mới thực hiện được phần các ngày trong tháng, và các thứ trong tuần.
kế tiếp là việc auto đổi màu cho các ngày thứ 7, chủ nhật cho không giống với ngày thường
+ Bôi đen nội dung trong bảng (Từ E9 đến AI30 – tức là tất cả các nội dung về ngày trong tháng lẫn phần chấm công của các nhân viên), chọn chức năng Conditional Formatting (định dạng theo điều kiện), trong mục Condittional Formatting, chọn New Rule.
+ thiết lập định hướng cho các cột k chứa trị giá ngày (ví dụ tháng 2 có 28 ngày, tự động bôi đen các ngày trống để phân biệt)
Bước 1: Tại ô B4, sửa tạm giá trị tháng là 2 để thị phạm
Bước 2: Bôi đen vùng trong bảng, lựa chọn tính năng Conditional formatting / new rule / use a formulas…, nhập hàm =if(e$9=””;true;false), lựa chọn định dạng là tô màu xám cho chữ và nền, ta có kết quả giống như sau:
Khi làm xong nhớ trả lại trị giá tháng 1 ở ô B4 nhé.
Phù, vậy là xong phần thiết lập ngày tháng cho bảng chấm công. Giờ còn khâu cuối cùng là đặt ký hiệu chấm công nữa là xong.
ĐẶT KÝ HIỆU CHẤM CÔNG
+ Bạn lựa chọn 1 vài ký hiệu chấm công cho 1 số loại công giống như sau:
- Ngày công thực tế (đi làm đủ số công):
- Nửa ngày công (làm nửa ca, nửa ngày…)
- Ngày nghỉ hưởng nguyên lương (đi học, họp, phép… nghỉ mà có lương)
- Nghỉ không lương
…. (Tùy đơn vị mà lựa chọn các hình thức chấm công khác nhau)
lưu ý mỗi kí hiệu chấm công sẽ tương ứng với 1 cột quy ra công (hoặc đủ sức gộp), bên cạnh đó thêm 1 cột tính tổng số công
Tại ví dụ tôi đặt giống như sau:
- Ngày công thực tế: x
- Nửa ngày công : v
- Ngày nghỉ hưởng nguyên lương: P
- Nghỉ không lương: không
- Ốm đau, thai sản: O
HÀM, công thức TÍNH CÔNG
Tại cột Ngày công thực tế (cột AJ), tại ô AJ11, đặt hàm sau:
=COUNTIF($E11:$AI11;$G$34)
nội dung hàm: Đếm số lần xuất hiện của trị giá tại ô G34, trong vùng E11 đến AI11. trị giá tại ô G34 là ký hiệu chấm công của ngày công đủ, vùng E11 đến AI11 là số ngày công trong tháng của người trước nhất (hàng 11), cố định cột E và AI để khi copy phương thức k bị ảnh hưởng tới vùng chấm công
Tương tự với các cột khác, ta đặt công thức:
- Ô AK11 (Nửa công) = countif($E11:$AI11;$G$35)
- Ô AL11 (Nghỉ hưởng lương) = countif($E11:$AI11;$G$36)
- Ô AM11 (nghỉ k lương) = countif($E11:$AI11;$G$37)
- Ô AN11 (ốm đau, thai sản) = countif($E11:$AI11;$G$38)
Tổng số công sẽ tính tùy theo yêu cầu tính công của đơn vị. Ví dụ: tổng ngày công = Ngày công thực tế + Nửa công x 0,5 + Nghỉ hưởng lương + Ốm đau, thai sản
Ô AO11 = AJ11+AK11*0,5+AL11+AN11
Sau khi đặt phương thức xong, ta sao chép mẹo xuống cho các nhân viên khác
Khi hoàn thành sẽ có như sau:
giống như vậy là đã hoàn thành xong việc chấm công, tạo ký hiệu và tính số công. Giờ bạn đủ sức kiểm tra bí quyết bằng phương pháp chấm thử vài ký hiệu vào bảng để test.
giống như vậy là ta vừa mới hoàn chỉnh xong bảng chấm công của tháng 1. Giờ chỉ còn việc copy ra các tháng còn lại.
Có thể bạn quan tâm:
Cách làm đồ handmade – Những đồ handmade dễ làm nhất để kinh doanh
Nên kinh doanh gì hiện nay? Tổng hợp 10+ ý tưởng kinh doanh hiệu quả nhất 2020
Nguồn: hocexcel.online
Discussion about this post