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:

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)

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 đủ nội lực tạo như sau:

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.

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

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&# 160;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…

: định hướng ngày tháng trong Excel

– 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:

nội dung trong ô E9 đến ô AI9 sẽ hiển thị ra ngày giống như trong bảng trên.

•  : 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
• : 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)

+ 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.

+ Tại mục Format values where this formula is true, ta nhập hàm

=if(weekday(e$9)=1;true;false)

: Nếu ngày trong tuần tại ô e9 là 1 (tức là chủ nhật) thì là đúng, 

không

 thì sẽ sai. Nếu dúng thì sẽ 

ứng dụng

 

định dạng

 của Conditional formatting. Đặt dấu cố định e$9 là cố định hàng 9, tính từ cột e.

+ Sau khi đặt hàm, ta 

thiết lập

 

định dạng

 cho CF. Trong bảng 

định hình

, bạn 

đủ nội lực

 

lựa chọn

 font chữ, màu chữ tại Font, màu nền tại Fill, kẻ đường viền ô tại Border… (giống 

định dạng

 ô của Format cells thông thường). 

lựa chọn

 

định dạng

 xong 

bấm

 ok.

+ 

sử dụng

 tương tự với 

định hình

 ngày thứ 7:

Bôi đen vùng trong bảng, 

lựa chọn

 

chức năng

 Conditional Formatting/ New rule/ Use a formulas…, nhập hàm =if(weekday(e$9)=7;true;false) , 

chọn

 kiểu 

định dạng

 cho ngày thứ 7

+ 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:

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

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à 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.