CÁCH TÍNH NGÀY CÔNG TRONG EXCEL

     

Việc chấm công hằng ngày là điều vô cùng quan trọng đối với những doanh nghiệp. Bài viết dưới đây, chúng tôi sẽ hướng dẫn chúng ta cách lập bảng chấm công vào excel 1-1 giản, cấp tốc chóng. Từ đó, hoàn toàn có thể giúp tiết kiệm chi phí nhân lực, thời gian ngân sách một cách hiệu quả.

Bạn đang xem: Cách tính ngày công trong excel


1. Chế tác bảng chấm công trên excel

Trước lúc lập 1 tệp tin excel, bạn nên tưởng tượng, tưởng tượng ra trước xem tệp tin đó có những gì, bao nhiêu sheet, nội dung như thế nào, cách vận động ra sao? Khi trả lời được các câu hỏi thì các bạn càng biết rõ phương pháp làm bên trên excel nhằm có hiệu quả như ý muốn.


Hoatieu.vn đang hướng dẫn biện pháp lập 1 mô hình bảng chấm công ví dụ như sau:

Gồm 13 sheet, từng sheet là một trong tháng và 1 sheet làm list nhân viên

Trung bình mỗi tháng là 20 nhân viênKý hiệu chấm công gồm thể thay đổi được tùy người sử dụng (mỗi người có thể chấm 1 kiểu ký kết hiệu, nhưng sẽ nhất quán trong 12 tháng)Trong từng bảng chấm công tất cả ghi rõ ngày, thứ trong tháng. Vào thiết bị 7 và chủ nhật thì tự đổi màu khác đối với ngày thường.Hàng ngày đã chấm công vào những ngày vào tháng. Vào cuối tháng sẽ trường đoản cú tính số toàn bô công vào tháng.Tên nhân viên trong bảng chấm công đã lấy từ danh sách nhân viênCó thể link giữa các tháng với nhau để dễ thao tác.

2. Lý giải lập bảng chấm công bằng excel

BỐ CỤC CÁC SHEET

Về mô hình là 13 sheet, mà lại khi ban đầu làm ta chỉ việc 2 sheet:

1 sheet là Danh sách nhân viên (DSNV)1 sheet là tháng 1 (làm trả chỉnh cho 1 tháng, những tháng sau hoàn toàn có thể copy sheet này rồi thay đổi tên)

SHEET DANH SÁCH NHÂN VIÊN

Nội dung hầu hết trong sheet này là tên gọi và Mã nhân viên. Bắt buộc tạo mã nhân viên vì có thể có trường thích hợp trùng tên. Khi thống trị theo mã sẽ tránh sự cố bị trùng.

Ngoài ra còn tồn tại các ngôn từ khác tương quan tới nhân viên, như: ngày sinh, quê quán, số CMT, ngày vào làm

Bạn rất có thể tạo như sau:


*


*

Sheet list nhân viên phần nhiều là nhập bằng tay nên không có gì phức tạp.

Lưu ý: các bạn chừa ra khoảng tầm 2-3 dòng trên cùng để tạo links tới những sheet khác. Bên trái cách ra 1 cột để tham gia phòng giả dụ cần bổ sung gì thêm.

SHEET THÁNG 1

Đầu tiên chúng ta tạo khung đến bảng chấm công, gồm các nội dung: title bảng chấm công, tháng, bộ phận chấm công, định nút ngày công vào 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 xứ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 nhằm ghi chú


*

*

Tiếp kế tiếp bạn teo độ rộng của các cột sao để cho gọn và dễ nhìn. Những cột ngày trong tháng hoàn toàn có thể co lại nhỏ dại vừa đủ để chấm công thôi. Cột quy ra công cũng không bắt buộc rộng lắm. Chủ yếu cột tên cùng mã nhân viên cấp dưới hiển thị đầy đủ là được.

Cách teo độ rộng: bạn bôi đen những cột ngày vào tháng, quy ra công (trong ví dụ như là bôi đen từ cột E mang đến cột AM), kế tiếp co độ rộng ở cột E khoảng chừng 30 pixels (hoặc bôi black từ cột E mang lại cột AM, tiếp nối bấm lựa chọn command Column Width, chọn 3,13 tương ứng 30 pixels)


*

Vậy là bạn đã làm dứt phần form của Bảng chấm công. Tiếp theo sau ta đang 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 vào BẢNG CHẤM CÔNG

Đầu tiên ta xác định năm sử dụng. Tại ô D1 nhập quý giá của năm. Lấy một ví dụ năm 2014

tại ô B4 (xác định mon 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 bên trong hàm có thể là vết ; hoặc giấu , tùy cấu hình thiết lập của máy tính. Ở máy vi tính của tôi thực hiện dấu ;

Nội dung hàm date: xác định giá trị của ngày tháng theo giá trị khẳng định trong hàm. Quý hiếm theo thiết bị tự là Năm, tháng, ngày. Ở hàm trên là Năm = cực hiếm tại ô D1, mon = 1, Ngày = 1


sau khoản thời gian nhập hàm xong, trên ô B4, chọn format cell / custom / nhập quý hiếm vào ô Type bên phải, xong xuôi rồi ấn ok (dấu nháy kép + tháng + dấu cách + vết nháy kép + mm + lốt nháy kép + năm + dấu biện pháp + dấu nháy kép + yyyy)


chúng ta cũng có thể marge cell tự ô B4 đến D4 làm cho hiển thị đủ văn bản cho dễ nhìn.

trên ô ngày 1 ( ô E9), chúng ta nhập =b4 để xác minh ngày trước tiên trong tháng.

trên ô F9, chúng ta nhập =e9+1 (ngày tiếp theo sau trong tháng)

Copy phương pháp tại ô F9 sang các ô mặt cạnh, cho đến ô ngày thiết bị 31 (ô AI9) bằng cách sau:

Cách 1: Bôi black từ ô F9 đến ô AI9, bấm Ctrl + R

Cách 2: nhấp chuột vào ô F9, giữ loài chuột tại vị trí vệt chấm đen trong ô để nhỏ trỏ chuột biến dấu +, kéo con chuột tới ô AI9 rồi thả ra Bôi black từ ô E9 đến ô AI9, lựa chọn Format cells / custom / mục Type chúng ta gõ chữ dd rồi bấm ok (chỉ hiện lên số ngày)

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

Xem thêm: Top 10 Cách Giữ Quần Bò Lâu Phai Màu? #5+ Cách Giặt Quần Jean Mới Mua Không Bị Phai Màu


trên ô 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 câu chữ trong hàm weekday không tồn tại đặt quy định về sản phẩm tự ngày trong tuần thì sẽ tự trả về theo thiết bị tự chủ nhật, trang bị hai, máy 3 lắp thêm 7 (Bắt đầu là nhà nhật, xong xuôi là đồ vật 7), với trả về quý hiếm số từ là một đến 8

Choose: là hàm lựa chọn giá trị trả về. Giá trị đầu tiên trong hàm là quý hiếm được chọn để gia công căn cứ xác định. Những giá trị tiếp theo sau là văn bản được trả về khớp ứng theo cực hiếm đầu tiên.

Copy công thức tại ô E10 sang các ô sau đó bên phải, cho tới ô AI10 (ngày sản phẩm công nghệ 31)


Tuy nhiên không phải tháng nào cũng đều có 31 ngày, cần để tránh vấn đề hiển thị những ngày của mon khác trong tháng này, ta thực hiện các làm việc sau:

+ ban đầu từ ngày sản phẩm 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 cực hiếm ngày của ô AF9 +1 bởi giá trị ngày của ô E9 vẫn trả về cực hiếm là rỗng, nếu không bằng thì sẽ trả về quý giá ô AF9 + 1 (Nghĩa là giả dụ tháng 2 có 28 ngày, thì ngày thiết bị 29 đã là ngày 01/03, vậy quý giá ngày là 1, bằng với giá trị ngày của ô E9, trường hợp đúng như thế thì đang là rỗng, ko hiển thị ngày 01/03. Còn nếu như tháng 2 đó tất cả 29 ngày thì đang 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 vật dụng 1: ví như tại ô AG9 là quý giá rỗng, thì ô AH9 cũng trở thành có cực hiếm rỗng (tức là vào trường vừa lòng tháng 2 bao gồm 28 ngày thì sẽ không có ngày sản phẩm công nghệ 30)

IF thiết bị 2: tương tự với hàm if tại ô AG9, tức là trường hòa hợp tháng 2 tất cả 29 ngày, thì sẽ không hiển thị ngày 30.

+ trên ô AI9, ta sử dụng hàm sau: =IF(AH9=;;if(DAY(AH9+1)=DAY(E9);;AH9+1))

Nội dung hàm:

IF thiết bị 1: trường hợp AH9 là cực hiếm rỗng, thì AI9 cũng trở thành có cực hiếm rỗng (tức là ngôi trường hợp không có ngày lắp thêm 30 thì cũng không có ngày trang bị 31)

IF trang bị 2: trường hợp tháng chỉ tất cả 30 ngày thì sẽ không có ngày lắp thêm 31

+ Khi những ô AG9, AH9, AI9 là trống rỗng thì hàm choose ở các ô AG10, AH10, AI10 đã phát sinh lỗi. Tuy nhiên không tác động gì đến bảng tính bắt buộc không cần lo lắng về vấn đề này.

Như vậy ta đã thực hiện được phần những ngày trong tháng, và những thứ vào tuần.

Tiếp theo là việc tự động hóa đổi màu cho các ngày vật dụng 7, nhà nhật mang lại khác với ngày thường

+ Bôi black nội dung trong bảng (Từ E9 cho 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 những nhân viên), chọn tính năng Conditional Formatting (định dạng theo điều kiện), trong mục Condittional Formatting, chọn New Rule. Biện pháp lấy công dụng này ra thanh lao lý các bạn có thể tham khảo bài tùy chỉnh thiết lập thanh chế độ trong excel


Trong bảng New Formatting Rule, bạn chọn dòng sau cùng trong mục Select a Rule Type là Use a formula lớn determine which cells khổng lồ format (Dùng phương pháp để tế bào tả bí quyết định dạng cells)+ tại mục Format values where this formula is true, ta nhập hàm =if(weekday(e$9)=1;true;false)

Nội dung hàm: nếu ngày vào tuần trên ô e9 là một (tức là nhà nhật) cho nên đúng, không thì đã sai. Nếu dúng thì sẽ vận dụng định dạng của Conditional formatting. Đặt dấu cố định e$9 là cố định và thắt chặt hàng 9, tính trường đoản cú cột e.+ sau khi đặt hàm, ta thiết lập cấu hình định dạng đến CF. Vào bảng định dạng, bạn cũng có thể chọn phông chữ, màu chữ trên Font, color nền trên Fill, kẻ con đường viền ô trên Border (giống định dạng ô của Format cells thông thường). Lựa chọn định dạng kết thúc bấm ok.


Khi làm chấm dứt nhớ trả lại cực hiếm tháng 1 sống ô B4 nhé.

Phù, vậy là xong xuôi phần tùy chỉnh thiết lập ngày tháng đến bảng chấm công. Tiếng còn khâu cuối cùng là đặt cam kết hiệu chấm công nữa là xong.

ĐẶT KÝ HIỆU CHẤM CÔNG

+ Bạn lựa chọn một vài ký kết hiệu chấm công cho 1 số một số loại công như sau:

Ngày công thực tế (đi có tác dụng đủ số công):Nửa ngày công (làm nửa ca, nửa ngày)Ngày nghỉ tận hưởng nguyên lương (đi học, họp, phép nghỉ mà tất cả lương)Nghỉ ko lương. (tùy đơn vị chức năng mà chọn các hình thức chấm công khác nhau)

Lưu ý từng kí hiệu chấm công vẫn tương ứng với 1 cột quy ra sức (hoặc rất có thể gộp), trong khi thêm 1 cột tính tổng thể công.

Tại ví dụ để như sau:

Ngày công thực tế: xNửa ngày công: vNgày nghỉ hưởng nguyên lương: PNghỉ ko 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 tiễn (cột AJ), trên ô AJ11, để hàm sau:

=COUNTIF($E11:$AI11;$G$34)

Nội dung hàm: Đếm số lần lộ diện của quý giá tại ô G34, trong vùng E11 đến AI11. Giá trị tại ô G34 là cam kết 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 cùng AI nhằm khi copy công thức không bị tác động tới vùng chấm công

Tương từ bỏ với những cột khác, ta đặt công thức:

Ô AK11 (Nửa công) = countif($E11:$AI11;$G$35)Ô AL11 (Nghỉ tận hưởng lương) = countif($E11:$AI11;$G$36)Ô AM11 (nghỉ ko lương) = countif($E11:$AI11;$G$37)Ô AN11 (ốm đau, bầu sản) = countif($E11:$AI11;$G$38)

Tổng số công sẽ tính phụ thuộc vào yêu ước tính công của đối kháng vị. Ví dụ: tổng ngày công = Ngày công thực tế + Nửa công x 0,5 + Nghỉ tận hưởng lương + Ốm đau, bầu sản

Ô AO11 = AJ11+AK11*0,5+AL11+AN11

Sau khi để công thức xong, ta copy bí quyết xuống cho những nhân viên khác


Khi hoàn thiện sẽ sở hữu như sau:


Như vậy là đang hoàn thiện ngừng việc chấm công, tạo ký hiệu cùng tính số công. Giờ chúng ta có thể kiểm tra công thức bằng phương pháp chấm demo vài ký kết hiệu vào bảng nhằm kiểm tra.

Xem thêm: Bài Tham Luận Văn Hóa Ứng Xử Trong Cand Việt Nam, Nâng Cao Văn Hóa Ứng Xử Trong Công An Nhân Dân

3. Mẫu mã bảng chấm công mới nhất

Mời các bạn bài viết liên quan tại mục thủ thuật văn phòng và công sở trong phần tài liệu.

kimsa88
cf68