{Hướng Dẫn} Tạo Bảng Chấm Công Excel Đơn Giản Nhất

Trước khi lập file excel, bạn nên hình dung trước khi xem file dot sheet bảng chấm công excel là gì, bao nhiêu trang, nội dung, cách thức hoạt động ra sao…? Khi bạn có thể trả lời các câu hỏi, bạn càng biết cách làm trong excel để đạt được kết quả như mong muốn.

Tôi sẽ chỉ cho bạn cách thiết lập một mô hình bảng chấm công như sau:

Bảng 13, 1 tờ cho mỗi tháng và 1 tờ cho danh sách nhân viên
Trung bình 20 nhân viên mỗi tháng
Người dùng có thể thay đổi ký hiệu công ty (mỗi người có thể chấm 1 ký hiệu kiểu, nhưng sẽ giống nhau trong 12 tháng)
Mỗi công thức bảng có ngày và ngày trong tháng được chỉ định. Vào thứ bảy và chủ nhà Nhật Bản, tự động thay đổi màu sắc khác với ngày thường.
Hàng ngày sẽ kết thúc các ngày trong tháng. Cuối tháng sẽ tính tổng cho tháng.
Tên nhân viên trong bảng chấm công sẽ được lấy từ danh sách nhân viên
Có thể liên kết giữa các tháng với nhau để dễ dàng thao tác.

Xem thêm >>> Bảng giá máy chấm công nhận diện khuôn mặt TimeFace

Các bước tạo bảng chấm công excel

bảng chấm công excel

Bố cục SHEET

Về mô hình là 13 tờ, nhưng khi bắt đầu, chúng tôi chỉ cần 2 tờ:

1 sheet là danh sách nhân viên (DSNV)
1 sheet là tháng (hoàn thành trong 1 tháng, bạn có thể copy sheet này và đổi tên sau)

Xem thêm >>> Review Hệ thống kiểm soát ra vào nhận diện khuôn mặt tốt nhất hiện nay

SHEET danh sách các nhân viên

Phần tử chính của nội dung trong trang này là Tên và ID nhân viên. Cần tạo một công nhân mã vì có thể có các tên trường trùng lặp. Khi quản lý mã hóa sẽ tránh được trường hợp trùng lặp.

Ngoài ra còn có các nội dung khác liên quan đến nhân viên, chẳng hạn như: ngày sinh, quê quán, số CMT, ngày vào làm việc, v.v.
Bạn có thể tạo như sau:

+ Cột E (ngày tháng năm sinh), bạn bôi đen cả cột (đưa con trỏ chuột lên vị trí đầu cột – chữ E, bấm 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ỳ ngày kiểu dữ liệu nào vào cột này, cột sẽ tự động trở về dạng dd-mm-yyyy (01-01-2014)

bảng chấm công excel

Các bảng tính của nhân viên hầu hết được nhập bằng tay và không phức tạp chút nào.
Lưu ý: Bạn chừa khoảng 2-3 dòng trên cùng để tạo liên kết đến các sheet khác. Để lại 1 cột cho căn phòng nếu bạn cần thêm plug-in.

Xem thêm >>> máy chấm công face id 2021

SHEET tháng 1

Đầu tiên, bạn tạo khung cho công cụ bảng gồm các nội dung sau: tiêu đề – công cụ bảng, tháng, phòng ban, công cụ định mức của tháng, các cột gồm: mã nhân viên, 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 lớn nhất trong tháng), 4-5 cột quy tắc tính toán, 1 cột ghi chú

Sau đó bạn kéo giãn chiều rộng của các cột sao cho dễ nhìn và dễ nhìn. Các cột ngày trong tháng có thể được thu nhỏ vừa đủ để chấm công. Công cụ cột được chỉ định cũng không cần thiết lắm. Tên cột chính và mã hiển thị nhân viên là đủ.

Co width: bạn bôi đen các cột ngày tháng, chuyển đổi sang nơi làm việc (trong ví dụ: bôi đen từ cột E sang cột AM), sau đó bôi đen từ cột E khoảng 30 pixel (hoặc bôi đen từ cột E đến cột AM, sau đó nhấp vào Cột Lệnh Width, chọn 3,13 tương ứng với 30 pixel)

Vậy là xong, bạn đã hoàn thành phần khung của bảng chấm công excel. Tiếp theo chúng ta sẽ thực hiện thao tác chọn ngày của công thức bảng.

Tạo ngày tháng trong bảng chấm công excel

Năm đầu tiên sử dụng được xác định. Trong giá trị đầu vào D1 của năm. Ví dụ 2014
Tại ô B4 (ký hiệu tháng), ta nhập ngày tháng để xác định hàm: = date ($ D $ 1; 1; 1)
Lưu ý rằng các phần tử trong hàm có thể có dấu; hoặc đánh dấu, thiết lập của máy tính. Tại máy tính của nhãn hiệu sử dụng của tôi;
Ngày nội dung hàm: xác định giá trị của tháng theo định nghĩa giá trị trong hàm. Các giá trị theo thứ tự là Năm, tháng, ngày. Trong hàm trên là Năm = giá trị trong ô D1, Tháng = 1, Ngày = 1
Sau khi nhập hàm, tại ô B4, chọn định dạng / ô tùy chỉnh / nhập giá trị [“tháng” mm “năm” yyyy] vào ô Loại bên phải, sau đó nhấn ok (dấu ngoặc kép + tháng + dấu + dấu kép trích dẫn + mm + nháy kép + năm + dấu cách + nháy kép + yyyy)
Bạn có thể di chuyển ô từ ô B4 đến D4 để hiển thị đủ nội dung cho người xem.
Tại ô ngày 1 (ô E9), bạn nhập = b4 để xác định ngày đầu tháng.
Trong ô F9, nhập = e9 + 1 (ngày tiếp theo của tháng)
Sao chép công thức trong ô F9 sang ô tiếp theo, lên đến ô thứ 31 (ô AI9) bằng cách sau:
Cách 1: Đánh dấu từ ô F9 đến ô AI9, nhấn Ctrl + RED
Cách 2: Nháy chuột vào ô F9, giữ chuột tại vị trí chấm đen trong ô sao cho trỏ chuột trở thành dấu +, rê chuột đến ô AI9 rồi thả
Đánh dấu từ ô E9 đến ô AI9, chọn Format Cells / custom / Item type bạn nhập chữ dd rồi nhấn ok (chỉ mục ngày)
Nội dung trong các ô từ E9 đến AI9 sẽ hiển thị ngày tháng như bảng trên.
Nội dung chức năng:
Ngày trong tuần (e9): là ngày nhận giá trị trong tuần của ô E9. Nếu nội dung của tuần có hàm không đặt quy tắc vào ngày trong tuần, nó sẽ tự trả về theo thứ tự Chủ nhật, Thứ hai, Thứ ba … Thứ bảy (Bắt đầu từ Chủ nhật, kết thúc Thứ bảy), và trả về giá trị giá trị từ 1 đến 8

bảng chấm công excel

Chọn: là một hàm giá trị trả về. Đầu tiên, giá trị trong hàm là giá trị được chọn làm cơ sở xác định. Theo sau giá trị là nội dung trả về tương ứng với đầu giá trị.
Ở hàm trên, vào ngày 01/01/2014 khi sử dụng ngày trong tuần, hàm sẽ trả về giá trị là 4, khi sử dụng hàm select với thứ tự tương ứng là Chủ nhật, Thứ 2, Thứ 3 … Thứ 7 thì giá trị sẽ là 4 sẽ tương ứng với cuộc tham vấn. Bởi vì hàm ngày trong tuần không trả về thứ tự cho ngôn ngữ, nên chúng ta phải Kết hợp với hàm chọn để có được ngày đó là ngày nào trong tuần
Sao chép công thức trong ô E10 sang ô bên phải, cho đến ô AI10 (ngày thứ 31)
Tuy nhiên, không phải tháng nào cũng có 31 ngày nên để tránh hiển thị ngày của các tháng khác trong tháng, chúng ta thực hiện các thao tác sau:
+ Bắt đầu từ ngày thứ 29 (ô AG9) ta điều chỉnh hàm như sau: = IF (DAY (AF9 + 1) = DAY (E9); “”; AF9 + 1)
Hàm nội dung: Nếu giá trị ngày của AF9 +1 theo giá trị của ô O9 thì ô E9 sẽ trả về giá trị trống, nếu không bằng, sẽ trả về giá trị AF9 + 1 (Có nghĩa là nếu tháng 2 có 28 ngày thì ngày 29 ngày sẽ là 01/03, do đó, ngày giá trị là 1, bằng với ngày giá trị của ô E9, nếu có thì sẽ để trống, không hiển thị 01/03. 29 ngày sẽ đến ngày 29)
Tại ô AH9, ta sử dụng hàm sau: = IF (AG9 = “”; “”; if (DAY (AG9 + 1) = DAY (E9); “”; AG9 + 1))
Nội dung chức năng:IF thứ nhất: Nếu ô AG9 là rỗng, thì ô AH9 cũng sẽ là rỗng (nghĩa là trong trường hợp 2 có 28 ngày, sẽ không có ngày thứ 30)
IF thứ 2: tương tự như hàm if ở ô AG9, tức là trường hợp tháng 2 có 29 ngày thì 30 ngày sẽ không hiển thị.
Tại ô AI9, ta sử dụng hàm sau: = IF (AH9 = “”; “”; if (DAY (AH9 + 1) = DAY (E9); “”; AH9 + 1))Nội dung chức năng:
IF thứ nhất: Nếu AH9 là rỗng, thì AI9 cũng sẽ là rỗng (tức là nếu không có ngày thứ 30 thì không có ngày thứ 31)
NẾU THỨ 2: nếu tháng chỉ có 30 ngày thì sẽ không có ngày thứ 31

Khi các ô AG9, AH9, AI9 trống thì hàm chọn trong các ô AG10, AH10, AI10 sẽ phát sinh lỗi. Nhưng nó không ảnh hưởng đến máy tính bảng, vì vậy không cần phải lo lắng về công việc này.
Vậy là chúng ta đã thực hiện xong các ngày trong tháng, và các ngày trong tuần.
Tiếp tục làm việc tự động đổi màu thứ 7, chủ nhật cho những ngày khác với ngày thường
Đánh dấu 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 và bộ phận của người lao động), chọn chức năng Conditional Formatting (định dạng có điều kiện), trong mục Định dạng Điều kiện, chọn Quy tắc mới.
Trong bảng Quy tắc Định dạng Mới, bạn chọn cuối dòng trong phần Chọn loại quy tắc là Sử dụng công thức để xác định các ô cần định dạng (Sử dụng công thức để mô tả các ô định dạng)
Tại mục Định dạng giá trị mà công thức này đúng, chúng ta nhập hàm
= if (ngày trong tuần (e $ 9) = 1; true; false)
Nội dung hàm: Nếu ngày trong tuần tại ô e9 là 1 (tức là chủ nhật) là đúng, ngược lại sẽ là sai. Nếu có, sẽ áp dụng định dạng Định dạng có điều kiện. Đặt e $ 9 được cố định là hàng 9, tính từ cột e.
Sau khi thiết lập chức năng, chúng ta thiết lập định dạng cho CF. Trong định dạng bảng, bạn có thể chọn chữ cái, màu văn bản tại Phông chữ, màu nền tại Tô, hộp kiểm tại Viền … (giống như ô định dạng của ô Định dạng thông thường). Sau khi chọn định dạng, nhấp vào ok
Làm tương tự với định dạng ngày thứ 7:
Đánh dấu vùng bảng, chọn định dạng có điều kiện chức năng / Quy tắc mới / Sử dụng công thức…, nhập hàm = if (ngày trong tuần (e $ 9) = 7; true; false), chọn định dạng kiểu cho thứ bảy
Cài đặt định dạng cho các cột không chứa giá trị ngày (ví dụ: tháng 2 có 28 ngày, tự động đánh dấu các ngày trống để phân biệt)Bước 1: Trong ô B4, giá trị tháng đã sửa đổi là 2 để thửBước 2: Đánh dấu vùng trong bảng, chọn chức năng Định dạng có điều kiện / quy tắc mới / sử dụng công thức…, nhập hàm = if (e $ 9 = “”; true; false), chọn định dạng là tô màu xám và nền, chúng ta có kết quả sau:
Khi hoàn tất, hãy nhớ trả lại giá trị tháng 1 trong ô B4.Phù, đó là phần đặt ngày cho công thức bảng. Hết giờ là đặt lại các ký hiệu chấm công là xong.ĐẶC ĐIỂM ĐẶC ĐIỂM
Bạn chọn một vài công cụ ký hiệu cho một số loại công cụ như sau:Số ngày thực tế (làm việc toàn thời gian):
Nửa ngày làm việc (nửa ca, nửa ngày …)
Ngày nghỉ được trả lương (đi học, đi họp, cho phép… đi nghỉ không lương)
Nghỉ phép không lương
…. (đơn vị lựa chọn của các công thức hình ảnh khác nhau)Lưu ý rằng mỗi công cụ ký hiệu sẽ tương ứng với 1 công cụ cột (hoặc có thể thay thế), bên ngoài thêm 1 công cụ cột tổngTại ví dụ, tôi đặt như sau:Ngày thực tế: x
Nửa ngày:
Nghỉ có lương: P
Nghỉ không lương: CZK
Ốm đau, thai sản: O

Hàm chấm công tính lương trong bảng chấm công Excel
Trong cột ngày thực tế của hàm (cột AJ), trong ô AJ11, hãy đặt sau hàm:= COUNTIF ($ E11: $ AI11; $ G $ 34)
Hàm nội dung: Đếm số lần giá trị xuất hiện trong ô G34, trong phạm vi từ E11 đến AI11. Giá trị trong ô G34 là ký hiệu dấu chấm của ngày làm việc đầy đủ, phạm vi E11 đến AI11 là công thức số trong tháng đầu tiên (hàng 11), cố định cột E và AI để việc sao chép công thức không ảnh hưởng đến dấu chấm.
Tương tự với các cột khác, chúng tôi đặt công thức:Ô AK11 (Nửa công việc) = countif ($ E11: $ AI11; $ G $ 35)
Ô AL11 (Nghỉ có trả tiền) = countif ($ E11: $ AI11; $ G $ 36)
Ô AM11 (nghỉ không lương) = countif ($ E11: $ AI11; $ G $ 37)
Ô AN11 (đau, thai sản) = countif ($ E11: $ AI11; $ G $ 38)
Tổng số công ty sẽ tính toán theo máy tính yêu cầu đơn vị. Ví dụ: tổng số ngày làm việc = Số ngày làm việc thực tế + Một nửa công việc x 0,5 + Nghỉ phép + Chế độ ốm đau, thai sảnÔ AO11 = AJ11 + AK11 * 0.5 + AL11 + AN11
Sau khi thiết lập công thức, chúng tôi sao chép công thức xuống cho các nhân viên khác
Khi hoàn thành, nó sẽ như thế này: