Trong bài viết dưới đây, chúng tôi sẽ hướng dẫn bạn cách tính bảng chấm công trong excel đơn thuần, nhanh gọn. Từ đó, hoàn toàn có thể giúp tiết kiệm chi phí nhân lực, thời hạn ngân sách một cách hiệu suất cao. Cùng khám phá ý nghĩa, cú pháp, cách sử dụng của hàm excel trong việc tính bảng chấm công dưới đây nhé !
Camnangbep.com cũng giúp giải đáp những vấn đề sau đây:
- Cách in bảng chấm công bằng Excel
- File Excel chấm công và tính lương theo giờ
- Cách làm bảng chấm công theo giờ bằng Excel
- Tạo bảng chấm công trên Google sheet
- Cách làm bảng chấm công trên điện thoại
- Cách làm bảng chấm công trên word
- file mẫu bảng chấm công theo giờ bằng excel-tính số phút đi muộn về sớm
- Bảng chấm công bằng Excel rất đẹp mắt
1 .Ý nghĩa của việc tính bảng chấm công trong excel
Nếu bạn đang quản trị một số lượng tương đối công nhân viên trong công ty, … thì việc chấm công hàng ngày là điều vô cùng thiết yếu. Thông qua cách quản trị công nhân viên như theo dõi ngày công thực tiễn thao tác, nghỉ việc, nghỉ hưởng BHXH … bạn hoàn toàn có thể tính tiền lương, tính điểm cần mẫn, … một cách đúng mực nhất. Từ đó, có giải pháp và giải pháp quản trị nhân sự hiệu suất cao hơn .
Mẫu bảng chấm công được tính bằng excel
Trong cách tính bảng chấm công trong excel, bạn cần chăm sóc đến hàm COUNTIF – đây là hàm dùng để đếm số ô thỏa mãn nhu cầu điều kiện kèm theo trong excel .
Cú pháp của hàm : COUNTIF ( range, criteria )
Hàm COUNTIF gồm 2 thành phần :
Range : Vùng chứa tác dụng hay chứa điều kiện kèm theo cần đếm
Criteria : Điều kiện cần đếm .
Ví dụ như sau :
Áp dụng hàm COUNTIF để tính số ngày công ( được chấm công minh ký tự X )
range : vùng chứa hiệu quả hay chứa điều kiện kèm theo cần đếm. Ở đây là phần từ cột B tới cột H
criteria : điều kiện kèm theo cần đếm. Ở đây là ký tự “ X ”
Cách viết hàm COUNTIF như sau :
I3 = COUNTIF ( USD B3 : $ H3, ” X ” )
I4 = COUNTIF ( USD B4 : $ H4, ” X ” )
Lưu ý : Khi tính công cho nhân viên cấp dưới A ( dòng 3 ) thì khoanh vùng phạm vi Range của hàm COUNTIF cũng chỉ vận dụng tương ứng với dòng 3. Chỉ dùng khi chấm công minh ký hiệu
2 .Cách tính bảng chấm công minh excel
Đầu tiên là bạn cần xác lập phải dùng hàm nào để tính công của nhân sự : Hàm COUNTIF
Tiếp theo là xác lập các thành phần, để thống kê giám sát thuận tiện hơn .
Range : Vùng chứa điều kiện kèm theo cần đếm C4 : I4
Criteria : Điều kiện cần đếm là X
Sau khi xác lập tuy nhiên thì vận dụng vào công thức tính lương lương của nhân viên cấp dưới Nguyễn Văn A
Cú pháp của hàm sau khi xác lập xong : = COUNTIF ( C4 : I4, “ X ” )
Sau khi nhập công thức đúng mực rồi, bạn nhấn Enter. Lúc này, sẽ ra hiệu quả 6 ngày đi làm
Muốn tính ngày công của các nhân sự khác thì chỉ kích vào góc bên phải ô J4, khi Open dấu cộng. Sau đó bạn chỉ cần kéo thả đến hết bảng. Như vậy bạn đã tính được số ngày công của tổng thể nhân sự trong công ty rồi .
Thật đơn thuần, nhanh gọn, tiện nghi, không mất nhiều thời hạn đúng không nào ? !
Hầu hết khi thiết lập cách tính bảng chấm công trên excel, bộ phận kế toán nhân sự thường không xây dựng theo một tiêu thức, tiêu chuẩn cụ thể nào. Mỗi nơi chấm công một kiểu, thậm chí cùng 1 người nhưng lại chấm công mỗi lúc một kiểu khác nhau.
Cách tính bảng chấm công trên excel bằng hàm Countif
Có một số quy ước chấm công mà chúng ta vẫn thường làm theo như:
Ký hiệu X : Chấm cho 1 công thao tác đủ 8 tiếng .
Nếu ít hơn 8 giờ công đó, thì ghi số giờ .
Làm nửa ngày thì ký hiệu N, tức là 4 tiếng một ngày .
3 .Cách tính bảng chấm công dựa vào tài liệu máy chấm công
Việc chấm công theo chiêu thức truyền thống cuội nguồn ( chấm tay ) được thực thi bằng các hàm trong bảng excel, không còn thực sự tương thích trong thời đại công nghệ tiên tiến tiên tiến và phát triển lúc bấy giờ. Bởi, nó sẽ khiến doanh nghiệp bị hạ thấp vị thế, sự chuyên nghiệp trong việc quản trị nhân sự so với người mua và đối tác chiến lược .
Theo đó, máy chấm công với công dụng là thiết bị điện tử dùng để ghi lại đúng mực thời hạn đi và về của từng nhân viên cấp dưới trong công ty. Bên cạnh đó, máy chấm công còn là thiết bị tương hỗ đắc lực và đúng chuẩn nhất trong việc chấm công và tính công của nhân viên cấp dưới .
Công ty giải pháp Tinh Hoa chuyên cung ứng máy chấm công chất lượng
Có thể nói, máy chấm công là công cụ chấm công hiệu quả và mang lại sự thuận tiện tối đa cho doanh nghiệp. Nó có tính năng chấm công, tích lũy và tàng trữ tài liệu của nhân viên cấp dưới, công nhân, … tạo ra file báo cáo giải trình để ship hàng cho nhiều mục tiêu quản trị nhân sự tương quan như trấn áp ra vào, chấm công, tính lương, …
Các chức năng chính của máy chấm công:
- Giám sát và quản trị trong qua mạng lưới hệ thống mạng
- Quản lý việc ra sớm vào trễ, làm ngoài giờ của công nhân viên
- Quản lý tăng ca, lịch thao tác và chủ trương nghỉ lễ, nghỉ phép
- Xuất và tùy chỉnh file báo cáo giải trình chấm công dưới nhiều định dạng như PDF, Word, Excel, Text, CSV và XML
- Tích hợp trực tiếp với ứng dụng chấm công, tính lương, quản trị nhân sự, quản lý tài nguyên doanh nghiệp, …
Bạn hoàn toàn có thể học và vận dụng cách tính bảng chấm công trong excel. Tuy nhiên, cách làm này lại gây tốn nhiều thời hạn, công sức của con người và cả ngân sách. Nên không đáng để các công ty, doanh nghiệp thời đại 4.0 tiêu tốn hoang phí như vậy. Thay vào đó, việc ứng dụng máy chấm công để tính bảng chấm công nhân viên được nhìn nhận là hiệu suất cao hơn gấp nhiều lần .
Nếu bạn quan tâm đến máy chấm công chất lượng, giá tốt. Đừng chần chừ, hãy liên hệ ngay đến Công ty giải pháp Tinh Hoa qua hotline: 0919.397.169 (gặp Nguyễn Nhã) hoặc 0919.039.665 (gặp Quỳnh Như) để được tư vấn lựa chọn máy chấm công phù hợp nhất cho công ty, doanh nghiệp mình!
Hướng dẫn cách tạo bảng chấm công trên Excel chi tiết nhất
Xác định mô hình bảng chấm công:
Tôi sẽ hướng dẫn cách 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 làm 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ể thay đổi đượ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ác so 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 viên trong bảng chấm công sẽ 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ễ 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 bắt đầu làm 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 có thể copy sheet này rồi đổi tên)
Sheet Danh sách nhân viên:
Nội dung chủ yếu trong sheet này là Tên và Mã nhân viên. Cần tạo mã nhân viên vì có thể 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.
Ngoài ra còn có các nội dung khác liên quan tới nhân viên, như: ngày sinh, quê quán, số CMT, ngày vào làm…
Bạn có thể 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, bấm 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 danh sách nhân viên hầu hết là nhập bằng tay nên không có gì phức tạp.
Lưu ý: bạn chừa ra khoảng 2-3 dòng trên cùng để tạo liên kết 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
Đầu tiên 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 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 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à dễ nhìn. Các cột ngày trong tháng có thể co lại nhỏ vừa đủ để 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 viên hiển thị đủ 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 ví dụ 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 đó bấm chọn command Column Width, chọn 3,13 tương ứng 30 pixels)
Vậy là bạn đã làm 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
– Đầu tiên ta xác định năm sử dụng. Tại ô D1 nhập giá trị của năm. Ví dụ năm 2014
– Tại ô B4 (xác định tháng chấm công), ta nhập hàm xác định ngày: =date($D$1;1;1)
Lưu ý dấu ngăn cách các thành phần trong hàm có thể là dấu ; hoặc dấu , tùy thiết lập 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: xác định giá trị của ngày tháng theo giá trị xác định 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 giá trị [“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 cách + dấu nháy kép + mm + dấu nháy kép + năm + dấu cách + dấu nháy kép + yyyy)
– Bạn có thể marge cell từ ô B4 đến D4 để cho hiển thị đủ nội dung cho dễ nhìn.
– Tại ô ngày 1 ( ô E9), bạn nhập =b4 để xác định 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ông thức tại ô F9 sang các ô bên cạnh, cho đến ô ngày thứ 31 (ô AI9) bằng cách sau:
- Cách 1: Bôi đen từ ô F9 đến ô AI9, bấm Ctrl + R
- Cách 2: Bấm 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, 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 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”)
Nội dung hàm:
- Weekday(e9) : là lấy giá trị ngày trong tuần của ô E9. Nếu nội dung trong hàm weekday không 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ề giá trị số từ 1 đến 8
- Choose: là hàm chọn giá trị trả về. Giá trị đầu tiên trong hàm là giá trị được chọn để làm căn cứ xác định. Các giá trị tiếp theo là nội dung được trả về tương ứng theo giá trị đầu 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 dùng hàm choose với thứ tự tương ứng là Chủ nhật, thứ hai, thứ ba… thứ bảy thì giá trị 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 kết hợp với hàm choose để có được nội dung là thứ mấy trong tuần
– Copy công thức tại ô E10 sang các ô kế tiếp bên phải, cho tới ô 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 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:
+ Bắt đầu từ ngày thứ 29 (ô AG9), ta điều chỉnh về hàm như sau: =IF(DAY(AF9+1)=DAY(E9);””;AF9+1)
Nội dung hàm: Nếu giá trị ngày của ô AF9 +1 bằng giá trị 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 như thế thì sẽ là rỗng, không 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))
Nội dung hàm:
- IF thứ 1: Nếu tại ô AG9 là giá trị 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ẽ không 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ẽ không hiển thị ngày 30.
+ Tại ô AI9, ta 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 không có ngày thứ 31)
- IF thứ 2: trường hợp tháng chỉ có 30 ngày thì sẽ không 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 lo lắng về việc này.
Như vậy ta đã thực hiện được phần các ngày trong tháng, và các thứ trong tuần.
Tiếp theo là việc tự động đổi màu cho các ngày thứ 7, chủ nhật cho khác 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 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 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 để làm thử
Bước 2: Bôi đen vùng trong bảng, chọn chức năng Conditional formatting / new rule / use a formulas…, nhập hàm =if(e$9=””;true;false), chọn định dạng là tô màu xám cho chữ và nền, ta có kết quả như sau:
Khi làm xong nhớ trả lại giá trị 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 chọn 1 vài ký hiệu chấm công cho 1 số loại cô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à 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 có thể gộp), ngoài ra thêm 1 cột tính tổng số công
Tại ví dụ tôi đặt 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: K
- Ố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 giá trị tại ô G34, trong vùng E11 đến AI11. Giá trị 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 đầu tiên (hàng 11), cố định cột E và AI để khi copy công thức không 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ỉ không 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 công thức xong, ta copy công thức xuống cho các nhân viên khác
Khi hoàn thiện sẽ có như sau:
Như vậy là đã hoàn thiện xong việc chấm công, tạo ký hiệu và tính số công. Giờ bạn có thể kiểm tra công thức bằng cách chấm thử vài ký hiệu vào bảng để kiểm tra.
Như vậy là ta đã 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.
Source: https://camnangbep.com
Category: Học tập
Camnangbep.com cũng giúp giải đáp những vấn đề sau đây:
- Cách in bảng chấm công bằng Excel
- File Excel chấm công và tính lương theo giờ
- Cách làm bảng chấm công theo giờ bằng Excel
- Tạo bảng chấm công trên Google sheet
- Cách làm bảng chấm công trên điện thoại
- Cách làm bảng chấm công trên word
- file mẫu bảng chấm công theo giờ bằng excel-tính số phút đi muộn về sớm
- Bảng chấm công bằng Excel rất đẹp mắt