Nhân Sự Hay Dùng Những Hàm Chấm Công Trong Excel Vô Cùng Đơn Giản

Đầu tiên, tạo bảng biểu dữ liệu với đầy đủ các cột phù hợp với cách chấm công mỗi công ty. Trong bảng công, nhất định chúng ta nên có 1 dòng thể hiện thứ ngày tháng, 1 dòng thể hiện ngày trong tháng. Nếu trường hợp là công ty sản xuất với ngày nghỉ sẽ không rơi vào ngày chủ nhật thì nên thêm dòng thể hiện ngày thường hoặc ngày nghỉ như mẫu của tôi.

Điều chỉnh hiển thị bằng cách, vào Format, chọn Custom, trong ô Type kéo xuống cuối cùng, gõ chữ dd (chỉ hiện ngày), mm (chỉ hiện tháng), yyyy (chỉ hiện năm), ở đây chúng ta chọn chỉ hiện ngày.

Lập công thức thứ trong tuần từ dòng ngày trong tháng. Có nhiều cách để có thể tạo thứ bằng tiếng Việt hoặc tiếng Anh, nhưng thông thường chúng ta dùng tiếng Anh, sử dụng công thức: =TEXT(F7,”ddd”). F7 là địa chỉ ô ngày 01 đầu tháng. Kéo kết quả đến ngày cuối tháng

Tiếp theo lập công thức dòng thể hiện ngày thường hay ngày nghỉ để sau này chúng ta dễ tính toán các điều kiện liên quan đến tăng ca, đến các điều kiện khác trong bảng lương.

=IF(F6=””,””,IF(F6=”Sun”,”N”,”T”)). F6 là địa chỉ ô chứa thứ thuộc ngày đầu tháng.Nếu F6 = Sun thì ra kết quả ngày N, còn lại là ngày thường T.Trường hợp này tôi ví dụ khi ngày nghỉ chủ nhật là ngày nghỉ tuần của người lao động

Trong 1 số trường hợp công ty quy định ngày nghỉ của NLĐ là ngày khác thì điều kiện công thức của chúng ta phải thay đổi.

Vì theo luật, Điều 110. Nghỉ hằng tuần:1. Mỗi tuần, người lao động được nghỉ ít nhất 24 giờ liên tục. Trong trường hợp đặc biệt do chu kỳ lao động không thể nghỉ hằng tuần, thì người sử dụng lao động có trách nhiệm bảo đảm cho người lao động được nghỉ tính bình quân 01 tháng ít nhất 04 ngày.2. Người sử dụng lao động có quyền quyết định sắp xếp ngày nghỉ hằng tuần vào ngày chủ nhật hoặc một ngày cố định khác trong tuần nhưng phải ghi vào nội quy lao động.

Do vậy công ty có thể quy định ngày nghỉ tuần vào 1 ngày bất kỳ thì công thức chúng ta lưu ý phải thay đổi điều kiện nhé.

Như vậy chúng ta hoàn thành việc thiết lập thứ ngày tháng trong bảng công, đây là phần quan trọng khi thiết lập bảng công để dễ tính toán. Khi tạo bảng biểu dữ liệu, phải suy nghĩ cách thể hiện nào thuận tiện cho việc tính toán và quản lý của chúng ta một cách tốt nhất. Vì nó là công cụ để chúng ta hoàn thành công việc.

PHẦN TIẾP THEO, CHÚNG TA TẠO LẬP CÔNG THỨC CHI TIẾTBẮT ĐẦU TỪ SỐ THỨ TỰ2/ Công thức số thứ tự: Bình thường thì ai cũng nghĩ đơn giản là đánh số TT chỉ cần kéo trỏ chuột thủ công bằng những cách khác nhau để tạo ra DS có STT.

Cách 1: Đánh STT 1 , bấm phím Ctrl và rê chuột đến dòng bạn cần.Cách 2: Đánh STT 1 và 2, tại cột A, cột B đã có sẵn 100 danh mục hàng….sau đó kéo trỏ chuột vào vùng dấu cộng bên phải ô thì click double chuột, Khi đó số thứ tự sẽ tự động được điền vào tất cả các hàng có dữ liệu bên phải, và được tự động tăng lên, cho dù dữ liệu của bạn có dài tới đâu nó cũng sẽ tự động tăng mà không cần phải kéo thủ công.

Đó là cách sử dụng thông thường. Nhưng trong trường hợp, chúng ta có 100 lao động hoặc hàng trăm danh mục văn phòng phẩm, vật tư…thì chúng ta nên sử dụng công thức với bảng dữ liệu.

Xem thêm: Bài Tập Nâng Cao Toán 6 Chương 2, Toán Lớp 6 Nâng Cao

Cách thứ nhất: Với những bảng dữ liệu liên tục không phân biệt phòng ban đánh số liên tục từ 1-cuối cùng chẳng hạn thì chúng ta dùng công thức đơn giản: = Row()-số dòng kế trên . Ví dụ chúng ta đang bắt đầu đếm Số 1 từ dòng 9 thì trong công thức là =Row()-8

Công thức này chỉ áp dụng khi đơn giản và không có điều kiện, không phân biệt phòng ban hay khoảng cách. Vậy trong công việc nhân sự chúng ta luôn có danh sách với đủ các phòng ban, thì chúng ta nên dùng công thức nào?

Cách thứ 2: Công thức đáp ứng điều kiện cho bảng công và lương với nhiều phòng ban=IF(ISTEXT(B10),MAX(A$9:A9)+1,””)

– Nếu cột mã nhân viên là text thì lấy STT lớn nhất bên trên cộng với 1, ngược lại khong có text thì để trống. Lưu ý cột Mã NV, nếu là phòng ban thì để trống hoặc điền số, nếu là nhân viên thì điền mã dạng text.– Nếu chúng ta đang dùng dữ liệu dạng bảng thì công thức tự động lấp đầy bảng– Nếu không thì chúng ta kéo kết quả xuống dòng cuối cùng

Như vậy các bạn sẽ thấy công thức bỏ qua ô trống để điền số TT. Nhưng 1 điều bất cập ở công thức này là, nó không phân biệt được mã trùng, giả sử mã nhân viên của chúng ta là mỗi người chỉ một đúng không ạ? Như vậy việc trùng mã nghĩa là 2 mã trùng nhau chỉ là 1 người (đếm chỉ 1) thì công thức này chưa đáp ứng được yêu cầu. Vậy nên cách thứ 3 chúng ta sử dụng công thức: =IF(AND(ISTEXT(B10),COUNTIF(B$10:B10,B10)=1),MAX(A$9:A9)+1,””)

Ở đây chúng ta kết hợp thêm hàm countif mục đích để đếm. Nếu cột mã nhân viên là text đồng thời nếu mã nhân viên >1 thì sẽ bỏ qua không đếm. Áp dụng cho cả cột B, vì vậy giả sử mã trùng nhau nhưng để cách phòng ban thì cũng không đếm.

Vậy công thức này ra kết quả chính xác số lượng nhân sự (lưu ý đảm bảo mỗi nhân viên là 1 mã khác nhau).=IF(AND(ISTEXT(B10),COUNTIF(B$10:B10,B10)=1),MAX(A$9:A9)+1,””)

Countif =1 có nghĩa là chỉ đánh STT cho dòng đầu tiên chứa mã thôi, từ dòng thứ 2 trở đi có cùng mã như vậy thì để trống, không đánh STTVì dòng thứ 2 chứa mã đó thì kết quả COUNTIF sẽ là số 2.

Tóm lại: Nếu cột B có chứa mã và lần đầu tiên xuất hiện mã đó thì đánh STT, còn lần thứ 2 trở đi thì không đánh STT nữa, đảm bảo mỗi mã chỉ được đánh STT một lần. Có thể hiểu là trong trường hợp có nhiều mã giống nhau (1 nhân viên có trên 1 dòng dữ liệu chấm công) thì việc đánh STT chỉ thực hiện cho dòng đầu tiên thôi, các dòng còn lại không đánh STT.

Phần 2: Hướng dẫn tô màu ngày chủ nhậtĐể thuận tiện cho công việc, trong bảng công chúng ta nên dùng tô màu tự động cho các loại ngày nghỉ chủ nhật, lễ tết…nó cũng là 1 cách ghi nhớ để chúng ta tính toán các loại ngày công theo quy định và không bị sai sót.

Từ công thức như video 1 tôi đã hướng dẫn tạo thứ, ngày tháng, chúng ta lập công thức như sau: =WEEKDAY(F7)=1

Mô tả: Hàm trả về thứ trong tuần của 1 ngày tương ứng. Giá trị trả về là số nguyên từ 1 (chủ nhật) đến 7 (thứ 7).1 ở đây là chúng ta xác định chủ nhật.

Lập công thức =WEEKDAY(F7)=1 đặt tại 1 ô nào đó trong bảng công.

Chọn vùng dữ liệu cần tô màu (ở đây là bao gồm từ ngày 1- cuối tháng).

Vào Formating- new ruler – trong mục Select ruler (lựa chọn) chúng ta chọn dòng cuối cùng

Sử dụng công thức cho ô, trong muc Format values…. Nhập công thức chúng ta vừa tạo– tiếp theo vào Format chọn màu phù hợp- ok. Ok – như vậy kết quả đã tô màu cho những ngày chủ nhật.

Những công thức này thông thường chúng ta chỉ cần tạo 1 lần từ tháng sau chúng ta chỉ cần thay đổi ngày tháng, thì bảng màu tự động di chuyển. Rất tiện ích phải không ạ?

Công thức tô màu ngày lễ trong nămTạo list các ngày lễ như mẫu. Nhập công thức kiểm tra vùng dữ liệu tại 1 ô bất kỳ=F$7=IFERROR(VLOOKUP(F$7,BU1:BU10,1,0),””)

Chọn vùng dữ liệu cần tô màu (ở đây là bao gồm từ ngày 1 đến ngày cuối tháng).

Vào Formating- new ruler – trong mục Select ruler (lựa chọn) chúng ta chọn dòng cuối cùng

Sử dụng công thức cho ô, trong muc Format values…. Nhập công thức =COUNTIF($BU$1:$BU$9,F7)>0— tiếp theo vào Format chọn màu phù hợp- ok. Ok – như vậy kết quả đã tô màu cho những ngày chủ nhật.

Vậy là chúng ta đã hoàn thành công việc tô màu cho ngày chủ nhật và ngày lễ

Phần 3: Cách chấm công và kiểm soát các ký tự bảng chấm côngThông thường, đối với mỗi công ty phụ thuộc vào mô hình kinh doanh chúng ta sẽ thiết lập bảng chấm công phù hợp.Đối với công ty thương mại, kinh doanh thì thường chỉ có giờ hành chính nên chúng ta thường sử dụng cách chấm công bằng ký tự chữ vì nó không phát sinh các loại ca làm việc và giờ tăng ca theo ca làm việc.

Còn với sản xuất thì ký tự chữ không đủ đáp ứng khi chấm các ca đêm tăng ca….nên chúng ta nên dùng chấm công bằng số.Mỗi cách chấm công có điểm ưu và nhược riêng.

Vậy tôi giải thích cụ thể về các cách chấm công và các công thức liên quan tổng hợp công như sau:1. Chấm công dạng ký tự: Thì các bạn sẽ phải định nghĩa cho các ký tự là chấm cho ca nào, phân nhóm các loại ngày công. Với dạng này sẽ có nhiều cột tổng hợp vì mỗi loại ký tự là 1 cột tổng công.

Xem thêm: Hướng Dẫn Cách Mở Bluetooth Trên Máy Tính, Cách Bật, Tắt Kết Nối Bluetooth Trên Laptop

2. Chấm công dạng số: Nếu có nhiều ca làm việc thì sẽ cần ít nhất 1 nhân viên có 3 dòng, thể hiện các loại ca khác nhau, còn nếu chỉ có giờ hành chính thì ít phức tạp hơn.

Tổng hợp công của các ký tự, chúng ta dùng hàm Countif thông thường, cộng các ký tự=COUNTIFS(Table2<