cach lam bang cham cong bang excel

• Gồm 13 sheet, từng sheet là một mon và 1 sheet thực hiện list nhân viên
• Trung bình từng tháng là trăng tròn nhân viên
• Ký hiệu chấm công rất có thể thay cho thay đổi được tùy người tiêu dùng (mỗi người rất có thể chấm 1 loại ký hiệu, tuy nhiên tiếp tục tương đồng vô 12 tháng)
• Trong từng bảng chấm công đem ghi rõ rệt ngày, loại vô mon. Vào loại 7 và ngôi nhà nhật thì tự động thay đổi color không giống đối với ngày thông thường.
• Hàng ngày tiếp tục chấm công vô những ngày vô mon. Cuối mon tiếp tục tự động tính số tổng số công vô mon.
• Tên nhân viên cấp dưới vô bảng chấm công tiếp tục lấy kể từ list nhân viên
• Có thể links Một trong những mon cùng nhau nhằm dễ dàng thao tác.

Các bước triển khai chi tiết

Bố viên những Sheet

Về quy mô là 13 sheet, tuy nhiên khi chính thức thực hiện tớ chỉ việc 2 sheet:

+ 1 sheet là Danh sách nhân viên cấp dưới (DSNV)

+ 1 sheet là Tháng 1 (làm hoàn hảo cho một mon, những mon sau rất 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 vô sheet này là Tên và Mã nhân viên cấp dưới. Cần tạo ra mã nhân viên cấp dưới vì như thế rất có thể đem tình huống trùng thương hiệu. Khi quản lý và vận hành bám theo mã tiếp tục tách tình huống bị trùng.

Ngoài rời khỏi còn tồn tại những nội dung không giống tương quan cho tới nhân viên cấp dưới, như: ngày sinh, quê quán, số CMT, ngày vô làm…
Bạn rất có thể tạo ra như sau:

+ Cột E (ngày sinh), chúng ta bôi đen kịt cả cột (đưa con cái trỏ loài chuột vô địa điểm bên trên nằm trong của cột – chữ E, bấm lựa chọn cả cột), chúng ta định hình Format cell/Number/Custome/ lựa chọn dd-mm-yyyy . Tác dụng: khi chúng ta nhập ngẫu nhiên tài liệu loại tháng ngày vô cột này tiếp tục tự động hóa trả về dạng dd-mm-yyyy (01-01-2014)

Sheet list nhân viên cấp dưới đa số là nhập thủ công bằng tay nên không tồn tại gì phức tạp.
Lưu ý: chúng ta chừa rời khỏi khoảng chừng 2-3 loại bên trên nằm trong muốn tạo links cho tới những sheet không giống. Mé trái ngược cơ hội rời khỏi 1 cột để tham dự chống nếu như cần thiết bổ sung cập nhật gì thêm thắt.

Sheet Tháng 1

Đầu tiên chúng ta tạo ra sườn mang lại bảng chấm công, bao gồm những nội dung: title – bảng chấm công, mon, thành phần chấm công, toan nút ngày công vô mon, những cột gồm: mã nhân viên cấp dưới, thương hiệu nhân viên cấp dưới, ngày vô mon (31 cột ứng với 31 ngày – số ngày lớn số 1 của một tháng), 4-5 cột tính quy ra sức, 1 cột nhằm ghi chú

Tiếp tiếp sau đó chúng ta teo phạm vi của những cột sao mang lại gọn gàng và dễ thương. Các cột ngày vô mon rất có thể thu hẹp nhỏ một vừa hai phải đầy đủ nhằm chấm công thôi. Cột quy ra sức cũng ko cần thiết rộng lớn lắm. Chủ yếu ớt cột thương hiệu và mã nhân viên cấp dưới hiển thị đầy đủ là được.

Cách teo phỏng rộng: chúng ta bôi đen kịt những cột ngày vô mon, quy ra sức (trong ví dụ là bôi đen kịt kể từ cột E cho tới cột AM), tiếp sau đó teo phạm vi ở cột E khoảng chừng 30 pixels (hoặc bôi đen kịt kể từ cột E cho tới cột AM, tiếp sau đó bấm lựa chọn command Column Width, lựa chọn 3,13 ứng 30 pixels)

Vậy là chúng ta đã trải kết thúc phần sườn của Bảng chấm công. Tiếp bám theo tớ tiếp tục triển khai thao tác định ngày mon của bảng chấm công.

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

– Trước hết tớ xác lập năm dùng. Tại dù D1 nhập độ quý hiếm của năm. Ví dụ năm 2014

– Tại dù B4 (xác toan mon chấm công), tớ nhập hàm xác lập ngày: =date($D$1;1;1)
Lưu ý vệt ngăn cơ hội những bộ phận vô hàm rất có thể là vệt ; hoặc vệt , tùy thiết lập của sản phẩm tính. Tại PC của tôi dùng vệt ;
Nội dung hàm date: xác lập độ quý hiếm của tháng ngày bám theo độ quý hiếm xác lập vô hàm. độ quý hiếm bám theo trật tự là Năm, mon, ngày. Tại hàm bên trên là Năm = độ quý hiếm bên trên dù D1, Tháng = 1, Ngày = 1

– Sau khi nhập hàm kết thúc, bên trên dù B4, lựa chọn format cell / custom / nhập độ quý hiếm [“tháng “mm” năm “yyyy] vô dù Type phía bên phải, kết thúc rồi ấn ok (dấu nháy kép + tháng + vệt cách + vệt nháy kép + mm + vệt nháy kép + năm + vệt cách + vệt nháy kép + yyyy)

Xem thêm: Định dạng tháng ngày vô Excel

– quý khách rất có thể marge cell kể từ dù B4 cho tới D4 khiến cho hiển thị đầy đủ nội dung mang lại dễ thương.

– Tại dù ngày một ( dù E9), chúng ta nhập =b4 nhằm xác lập ngày thứ nhất vô mon.

– Tại dù F9, chúng ta nhập =e9+1 (ngày tiếp sau vô tháng)

– Copy công thức bên trên dù F9 sang trọng những dù ở bên cạnh, cho tới dù ngày loại 31 (ô AI9) bằng phương pháp sau:

• Cách 1: Bôi đen kịt kể từ dù F9 cho tới dù AI9, bấm Ctrl + R
• Cách 2: Bấm loài chuột vô dù F9, lưu giữ loài chuột bên trên địa điểm vệt chấm đen kịt vô dù nhằm con cái trỏ loài chuột trở nên vệt +, kéo loài chuột cho tới dù AI9 rồi thả ra

– Bôi đen kịt kể từ dù E9 cho tới dù 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 nay thị số ngày)
Nội dung vô dù E9 cho tới dù AI9 tiếp tục hiển thị rời khỏi ngày như vô bảng bên trên.

– Tại dù 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 độ quý hiếm ngày vô tuần của dù E9. Nếu nội dung vô hàm weekday không tồn tại bịa đặt quy toan về trật tự ngày vô tuần thì tiếp tục tự động trả về bám theo trật tự Chủ nhật, loại nhì, loại 3… loại 7 (Bắt đầu là ngôi nhà nhật, kết cổ động là loại 7), và trả về độ quý hiếm số từ là 1 cho tới 8
• Choose: là hàm lựa chọn độ quý hiếm trả về. Giá trị thứ nhất vô hàm là độ quý hiếm được lựa chọn nhằm thực hiện địa thế căn cứ xác lập. Các độ quý hiếm tiếp sau là nội dung được trả về ứng bám theo độ quý hiếm thứ nhất.

Trong hàm bên trên, ngày 01/01/2014 khi sử dụng hàm weekday tiếp tục trả về độ quý hiếm là 4, khi sử dụng hàm choose với trật tự ứng là Chủ nhật, loại nhì, loại ba… loại bảy thì độ quý hiếm 4 tiếp tục ứng với loại tư. Bởi vì như thế hàm weekday ko tự động trả về loại bám theo giờ việt, nên tớ cần kết phù hợp với hàm choose để sở hữu được nội dung là loại bao nhiêu vô tuần

– Copy công thức bên trên dù E10 sang trọng những dù tiếp nối phía bên phải, cho đến dù AI10 (ngày loại 31)

Tuy nhiên ko cần mon nào là cũng có thể có 31 ngày, nên nhằm tách việc hiển thị những ngày của mon không giống vô mon này, tớ triển khai những thao tác sau:
+ Bắt đầu từ thời điểm ngày loại 29 (ô AG9), tớ kiểm soát và điều chỉnh về hàm như sau: =IF(DAY(AF9+1)=DAY(E9);””;AF9+1)
Nội dung hàm: Nếu độ quý hiếm ngày của dù AF9 +1 vị độ quý hiếm ngày của dù E9 tiếp tục trả về độ quý hiếm là trống rỗng, còn nếu như không vị thì tiếp tục trả về độ quý hiếm dù AF9 + 1 (Nghĩa là nếu như mon 2 đem 28 ngày, thì ngày loại 29 được xem là ngày 01/03, vậy độ quý hiếm ngày là một, vị với độ quý hiếm ngày của dù E9, nếu như đúng như vậy thì được xem là trống rỗng, ko hiển thị ngày 01/03. Còn nếu như mon 2 cơ đem 29 ngày thì tiếp tục hiện nay ngày 29)

+ Tại dù AH9, tớ người sử dụng hàm sau: =IF(AG9=””;””;if(DAY(AG9+1)=DAY(E9);””;AG9+1))
Nội dung hàm:

• IF loại 1: Nếu bên trên dù AG9 là độ quý hiếm trống rỗng, thì dù AH9 cũng sẽ có được độ quý hiếm trống rỗng (tức là vô tình huống mon 2 đem 28 ngày thì tiếp tục không tồn tại ngày loại 30)
• IF loại 2: tương tự động với hàm if bên trên dù AG9, tức là tình huống mon 2 đem 29 ngày, thì sẽ không còn hiển thị ngày 30.

+ Tại dù AI9, tớ người sử dụng hàm sau: =IF(AH9=””;””;if(DAY(AH9+1)=DAY(E9);””;AH9+1))

Nội dung hàm:

Xem thêm: cach quay thit bang lo vi song

• IF loại 1: Nếu AH9 là độ quý hiếm trống rỗng, thì AI9 cũng sẽ có được độ quý hiếm trống rỗng (tức là tình huống không tồn tại ngày loại 30 thì cũng không tồn tại ngày loại 31)
• IF loại 2: tình huống mon chỉ mất 30 ngày thì tiếp tục không tồn tại ngày loại 31

+ Khi những dù AG9, AH9, AI9 là trống rỗng thì hàm choose ở những dù AG10, AH10, AI10 tiếp tục đột biến lỗi. Nhưng ko tác động gì cho tới bảng tính nên ko cần thiết phiền lòng về sự việc này.

Như vậy tớ đang được triển khai được phần những ngày vô mon, và những loại vô tuần.

Tiếp bám theo là sự việc tự động hóa thay đổi color cho những ngày loại 7, ngôi nhà nhật mang lại không giống với ngày thường

+ Bôi đen kịt nội dung vô bảng (Từ E9 cho tới AI30 – tức là toàn bộ những nội dung về ngày vô mon lẫn lộn phần chấm công của những nhân viên), lựa chọn tính năng Conditional Formatting (định dạng bám theo điều kiện), vô mục Condittional Formatting, lựa chọn New Rule.

Trong bảng New Formatting Rule, chúng ta lựa chọn loại ở đầu cuối vô mục Select a Rule Type là Use a formula to lớn determine which cells to lớn format (Dùng công thức nhằm tế bào mô tả cơ hội định hình cells)

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

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

Nội dung hàm: Nếu ngày vô tuần bên trên dù e9 là một (tức là ngôi nhà nhật) thìa là đích thị, ko thì tiếp tục sai. Nếu dúng thì tiếp tục vận dụng định hình của Conditional formatting. Đặt vệt thắt chặt và cố định e$9 là thắt chặt và cố định mặt hàng 9, tính kể từ cột e.

+ Sau lúc để hàm, tớ thiết lập định hình mang lại CF. Trong bảng định hình, bạn cũng có thể lựa chọn font text, color chữ bên trên Font, color nền bên trên Fill, kẻ đàng viền dù bên trên Border… (giống định hình dù của Format cells thông thường). Chọn định hình kết thúc bấm ok.

Kết trái ngược khi triển khai Conditional formatting với ĐK ngày ngôi nhà nhật.

+ Làm tương tự động với định hình ngày loại 7:

Bôi đen kịt vùng vô bảng, lựa chọn tính năng Conditional Formatting/ New rule/ Use a formulas…, nhập hàm =if(weekday(e$9)=7;true;false) , lựa chọn loại định hình cho một ngày loại 7

Kết trái ngược khi triển khai định hình loại 7 và ngôi nhà nhật

+ Thiết lập định hình cho những cột ko chứa chấp độ quý hiếm ngày (ví dụ mon 2 đem 28 ngày, tự động hóa bôi đen kịt những ngày trống trải nhằm phân biệt)

Bước 1: Tại dù B4, sửa tạm thời độ quý hiếm mon là 2 nhằm thực hiện thử

Bước 2: Bôi đen kịt vùng vô 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 hình là tô color xám mang lại chữ và nền, tớ đem thành phẩm như sau:

Khi thực hiện kết thúc lưu giữ trả lại độ quý hiếm mon 1 ở dù B4 nhé.

Phù, vậy là kết thúc phần thiết lập tháng ngày mang lại bảng chấm công. Giờ còn vá ở đầu cuối là bịa đặt ký hiệu chấm công nữa là kết thúc.

Đặt ký hiệu chấm công

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

• Ngày công thực tiễn (đi thực hiện đầy đủ số công):
• Nửa ngày công (làm nửa ca, nửa ngày…)
• Ngày nghỉ ngơi hưởng trọn vẹn toàn lộc (đi học tập, họp, phép… nghỉ ngơi tuy nhiên đem lương)
• Nghỉ ko lương

…. (tùy đơn vị chức năng tuy nhiên lựa chọn những mẫu mã chấm công không giống nhau)

Lưu ý từng kí hiệu chấm công tiếp tục ứng với một cột quy ra sức (hoặc rất có thể gộp), ngoại giả thêm một cột tính tổng số công

Tại ví dụ tôi bịa đặt như sau:

• Ngày công thực tế: x
• Nửa ngày công : v
• Ngày nghỉ ngơi hưởng trọn vẹn toàn lương: P
• Nghỉ ko lương: K
• Ốm nhức, bầu 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), bên trên dù AJ11, bịa đặt hàm sau:

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

Nội dung hàm: Đếm số phiên xuất hiện nay của độ quý hiếm bên trên dù G34, vô vùng E11 cho tới AI11. Giá trị bên trên dù G34 là ký hiệu chấm công của ngày công đầy đủ, vùng E11 cho tới AI11 là số ngày công vô mon của những người thứ nhất (hàng 11), thắt chặt và cố định cột E và AI nhằm khi copy công thức không xẩy ra tác động cho tới vùng chấm công

Tương tự động với những cột không giống, tớ bịa đặt công thức:

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

Tổng số công tiếp tục tính tùy từng đòi hỏi tính công của đơn vị chức năng. 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 trọn lương + Ốm nhức, bầu sản

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

Sau lúc để công thức kết thúc, tớ copy công thức xuống cho những nhân viên cấp dưới khác

Khi hoàn mỹ sẽ có được như sau:

Như vậy là đang được hoàn mỹ kết thúc việc chấm công, tạo ra ký hiệu và tính số công. Giờ bạn cũng có thể đánh giá công thức bằng phương pháp chấm test vài ba ký hiệu vô bảng nhằm đánh giá.

Như vậy là tớ đang được hoàn hảo kết thúc bảng chấm công của mon 1. Giờ chỉ từ việc copy rời khỏi những mon sót lại.

Ngoài việc chấm công, tất cả chúng ta còn cần được thực hiện những việc làm không giống tương quan cho tới chi phí lộc, bảo đảm, thuế TNCN… Để cầm được những kỹ năng và kiến thức và cơ hội phần mềm Excel vô việc làm hành chính-nhân sự, chào chúng ta nhập cuộc khóa học Hành chủ yếu nhân sự kể từ A-Z của Học Excel Online. Bật mí là chúng ta còn được tặng kèm cặp ứng dụng quản lý và vận hành nhân sự khi nhập cuộc khóa đào tạo và huấn luyện này nhé. À quên, bạn cũng có thể vận chuyển về tệp tin excel bám theo dõi nghỉ ngơi phép tắc khi nhập cuộc khóa đào tạo và huấn luyện nữa. Chi tiết coi tại: Học Excel Online

Xem thêm: dong long tat can bien dong