Cách làm bảng chấm công theo giờ bằng excel

Không cần dịp làm sao câu hỏi chnóng công bên trên sản phẩm chnóng công cũng mang về tác dụng ngay. Trong một trong những ngôi trường phù hợp chúng ta phải đưa tài liệu tự máy chnóng công sang Excel thì mới tính được số giờ công thao tác của nhân viên cấp dưới. Trong bài học này Học Excel Online đang trả lời chúng ta cách tính thời gian thao tác trong bảng chấm công Excel với dữ liệu được trích xuất tự sản phẩm chnóng công. Nội dung cụ thể nhỏng sau:

Chúng ta bao gồm bảng tài liệu trích xuất tự vật dụng chnóng công mon 07 năm 2017 gồm:

*

Yêu cầu: Dựa vào số giờ vào, giờ ra theo từng ngày một của từng nhân viên cấp dưới, hãy tính khoảng thời gian thao tác làm việc, số lần đi muộn của những nhân viên cấp dưới.

Bạn đang xem: Cách làm bảng chấm công theo giờ bằng excel


Xem nhanh


Cách làm

Bước 1: Khảo ngay cạnh, Đánh Giá và so với dữ liệu nguồn (bảng tài liệu kết xuất từ đồ vật chnóng công ra excel)

Đây là bước siêu đặc biệt quan trọng, Việc phân tích bảng tài liệu mối cung cấp để giúp họ tất cả phương án cách xử trí tương xứng với chính xác.

Để thực hiện công việc này, bọn họ buộc phải nắm được những vẻ ngoài sau:


Dù vẫn gồm phần mềm, tuy nhiên tài năng Excel vẫn cực kỳ đặc trưng với kế toán, chúng ta đã vững Excel chưa? Hãy nhằm tôi giúp cho bạn, ĐK khoá học Excel:



Mục đích công việc: Tính công thao tác theo thời gianĐối tượng liên quan: Mã nhân viên, Ngày trong tháng, Giờ vào, Giờ raTiêu chí nhận xét (dựa trên mục đích, kết quả cần đạt được để đề ra tiêu chí cho các đối tượng người tiêu dùng liên quan) Mã Nhân viên: Liên tục theo dòng, không được nhằm dòng trống Ngày vào tháng: Bao có xác định ngày vào thời điểm tháng, sản phẩm công nghệ trong tuần, khác nhau ngày nghỉ dịp, nghỉ ngơi bù Giờ vào, giờ đồng hồ ra: Các ngày làm việc đều phải có tiếng vào, tiếng ra. lúc tính công sẽ dựa trên việc đánh giá tiếng vào, tiếng ra để tính ra những nhiều loại công.

Ta rất có thể thấy, nhằm tính toán được, nên phải kiểm soát và điều chỉnh lại cấu trúc bảng dữ liệu sao cho đúng các tiêu chuẩn.

Việc tính toán trên nhiều điều kiện, bắt buộc hàm vẫn sử dụng để tính toán là SUMIFS, COUNTIFS và hàm IF

Bước 2: Cấu trúc lại bảng dữ liệu

Để rút gọn nội dung bài viết, bản thân sẽ không còn nêu từng thao tác, nhưng mà sẽ đưa biểu trưng và gợi ý những giải pháp làm để các chúng ta cũng có thể từ làm được:

(Bảng chnóng công này còn có cấu tạo mỗi ngày gồm 2 cột, khác bài trước là hàng ngày 1 cột)

*
*

Phần Tháng với Năm (mẫu 2, 3): Đặt bên ngoài nhằm luôn tiện vấn đề thay đổiNgày vào tháng (loại 4): Dùng hàm Date, bắt đầu với ngày 26 mon trước kia (bởi vì bảng nguyên vật liệu kết xuất tính từ thời điểm ngày 26 bắt buộc ta vẫn giữ nguyên thử khám phá này. Tính công từ thời điểm ngày 26 mon trước cho ngày 25 mon sau). Riêng 2 ngày cuối có thể lịch sự mon tiếp theo (trường đúng theo mon bao gồm 28-29 ngày) yêu cầu rất có thể xử lý để quăng quật trống nếu như sang tháng sau, có nghĩa là ngày 27 mon này trở đi là vẫn bước quý phái mon sau của bảng tính công.Phần Mã NV (cột A, B, C): Mỗi mã 1 chiếc, để tập đúng theo theo mã NV đúng đắn rộng.Thđọng trong tuần (loại 5): Dùng hàm Choose kết phù hợp với hàm Weekday nhằm xác định. Kết hợp công dụng Conditional Formatting để sơn color riêng biệt ngày CN với ngày thường xuyên.Giờ vào, giờ đồng hồ ra (cái 6): hoàn toàn có thể quy ước số 1 là giờ vào, số 2 là giờ đồng hồ ra.Lưu ý: 1 ngày gồm 2 giờ, buộc phải 2 cột công thêm cho ngày. Vì vậy Lúc giải pháp xử lý ngày sinh sống loại 4 đề xuất chú ý văn bản này.

Xem thêm: Cách Tạo File Run Bat - Những Lệnh Cần Thiết Để Tạo Ra Một File Lệnh

Tất cả gần như nội dung bên trên, mình đang lí giải cụ thể vào bài: Tạo bảng chnóng công trên excel. Vui lòng xem lại bài xích này để được khuyên bảo chi tiết.

Bước 3: Lọc và đem dữ liệu 

Tại bảng kết xuất, dùng tính năng Data/Filter để thanh lọc vứt loại trống trong cột Mã NV, sau kia copy tài liệu lịch sự bảng xử trí. Sử dụng tác dụng Paste Special/Value để dán dữ liệu bỏ qua mất mẫu bị ẩn.

*

Bảng kết xuất thực hiện công dụng lọc (Data Filter)

*

Dữ liệu chnóng công sinh sống bảng kết xuất => Copy / Paste Special -> Value => Sang bảng xử lý

Cách 4: Tính tiếng công vào ngày

*

Tạo thêm một vùng, trong kia mỗi ngày chỉ tương ứng 1 cột. Cách giải pháp xử lý ngày, thiết bị kiểu như cùng với phần trước.

=> Mục đích: Tính khoảng thời gian công làm việc theo từng ngày

Giờ công thao tác làm việc trong ngày = Giờ ra – Giờ vào

Giờ ra = (Tổng số phút ít quy ước tại thời điểm chnóng công ra = Số tiếng * 60 + Số phút)/60

Giờ vào = (Tổng số phút ít quy ước trên thời điểm chnóng công vào = Số giờ * 60 + Số phút)/60

BN7 =ROUND(((HOUR(SUMIFS($D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,2))*60+MINUTE(SUMIFS($D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,2)))-(HOUR(SUMIFS($D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,1))*60+MINUTE(SUMIFS($D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,1))))/60,2)

quý khách hàng có thể filldown, fillright cách làm trường đoản cú BN7 cho các ô không giống.

* Lưu ý:

Có thể tất cả số âm. Số âm là trường thích hợp Chỉ có tiếng vào nhưng mà không có giờ ra => Lý do: Quên chấm công. Việc này thường xảy ra

Cách 5: Xác định kết quả chnóng công

Ví dụ họ bắt buộc 1 số ít tác dụng chấm công nhỏng sau:

*

Ta có thể thấy:

Ngày công đủ 8h tính dựa trên vùng sẽ xác định ví dụ số giờ công, cùng với thời gian làm cho >=8 (mục 2.4)

CS7 = COUNTIF(BN7:CR7,”>=”&8)

Quên chấm công tính dựa trên vùng sẽ xác định khoảng thời gian công, với số giờ

CT7 = COUNTIFS(BN7:CR7,”Tổng số ngày công = Đủ 8h + Quên chấm công. Vì quên chấm công hoàn toàn có thể vẫn được tính là 1 trong ngày công,hoặc Tổng số ngày công = Đủ 8h + (Quên chấm công /2) ví như quy ước quên chnóng công sẽ coi là nửa ngày công.

CU7 = CS7+CT7 hoặc CU7 = CS7+(CT7/2)

Ngày nghỉ = (Tổng số ngày tất cả công = 0) – (Số ngày nghỉ tuần = công ty nhật) Trường phù hợp ngày nghỉ tuần là công ty nhật. Nếu nghỉ ngơi cả T7, CN thì đã trừ thêm số ngày vật dụng 7 vào tháng

CV7 = IF(B7=””,0,COUNTIF(BN7:CR7,0)-COUNTIF(BN$5:CR$5,”CN”)-COUNTIF(BN$4:CR$4,””))

Đi muộn: Căn uống cđọng vào giờ vào làm, so sánh cùng với tiêu chí 8h (tuỳ quy ước giờ đồng hồ vào có tác dụng của 1-1 vị)

Muộn 5′ => CW7 = COUNTIFS($D7:$BM7,”>”&”8:05″,$D7:$BM7,” CX7 = COUNTIFS($D7:$BM7,”>”&”8:10″,$D7:$BM7,” CY7 = COUNTIFS($D7:$BM7,”>”&”8:15″,$D$6:$BM$6,1)

=> quý khách có thể filldown công thức xuống cho các mã NV tiếp theo

Kết trái của mục 2.5 là hiệu quả sau cùng, rất có thể sử dụng nhằm tính lương cho NV

Cách tối ưu

Mình đã gợi ý một vài kỹ thuật góp tối ưu hoá bảng xử lý: (Ưu tiên đến rất nhiều bạn biết áp dụng VBA, nếu như không biết VBA thì làm bằng tay thủ công bằng tay)

Sử dụng VBA nhằm lưu ban bố xử lý cùng công dụng tính được qua một Sheet riêng để lưu trữ công dụng. Bảng cách xử trí có thể làm tiếp cho những tháng sau cơ mà không tác động tới kết quả tháng trước.Sử dụng VBA ẩn/hiện tại các vùng: Dữ liệu chnóng công theo giờ vào-ra (cột D đến cột BM), Dữ liệu cách xử trí tính giờ đồng hồ làm việc (Cột BN đến cột CR) để triển khai gọn bảng xử lý. Lúc đề xuất thao tác làm việc cùng với bảng làm sao thì ta mở ẩn vùng bảng đó ra. vì thế đã gọn đôi mắt rộng. Vì kim chỉ nam cuối cùng là hiệu quả tính công nhưng thôi.

Công Việc chnóng công, tính lương hơi phức hợp và phong phú và đa dạng. Để tìm hiểu thêm những kỹ năng tương quan tới công việc này, Học Excel Online xin giới thiệu với các bạn khóa đào tạo và huấn luyện “Ứng dụng Excel VBA trong các bước Tiền lương – nhân sự”. Học chấm dứt khóa đào tạo này chúng ta cũng có thể cố kỉnh vững được quá trình chnóng công, tính lương vào doanh nghiệp, từ bỏ tay thành lập một phần mềm cai quản lao cồn, chnóng công tính lương trên Excel. Chi huyết coi tại: