BẢNG CHẤM CÔNG THEO GIỜ BẰNG EXCEL

Không phải thời điểm làm sao Việc chnóng công trên thứ chnóng công cũng mang đến tác dụng tức thì. Trong một số trong những ngôi trường vừa lòng bọn họ phải chuyển dữ liệu từ vật dụng chnóng công sang Excel thì mới có thể tính được khoảng thời gian công làm việc của nhân viên cấp dưới. Trong bài học kinh nghiệm này Học Excel Online sẽ giải đáp các bạn cách tính số giờ làm việc trong bảng chnóng công Excel cùng với dữ liệu được trích xuất trường đoản cú sản phẩm công nghệ chnóng công. Nội dung rõ ràng nlỗi sau:

Chúng ta bao gồm bảng dữ liệu trích xuất từ bỏ thiết bị chấm công tháng 07 năm 2017 gồm:

*

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

Bạn đang xem: 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 sánh tài liệu mối cung cấp (bảng tài liệu kết xuất tự vật dụng chnóng công ra excel)

Đây là bước khôn cùng đặc biệt quan trọng, Việc phân tích bảng dữ liệu mối cung cấp sẽ giúp họ bao gồm phương án cách xử lý tương xứng và đúng đắn.

Để thực hiện quá trình này, họ cần nắm được những cơ chế sau:


Dù đã có ứng dụng, tuy vậy tài năng Excel vẫn cực kỳ đặc biệt quan trọng với kế toán, các bạn vẫn vững vàng Excel chưa? Hãy để tôi khiến 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 vào thời điểm tháng, Giờ vào, Giờ raTiêu chí Review (dựa trên mục đích, kết quả cần đạt được để đưa ra tiêu chí mang đến các đối tượng người dùng liên quan) Mã Nhân viên: Liên tục theo loại, không được nhằm dòng trống Ngày trong tháng: Bao bao gồm xác định ngày trong tháng, sản phẩm trong tuần, phân biệt ngày nghỉ dịp, nghỉ bù Giờ vào, tiếng ra: Các ngày làm việc đều có giờ vào, giờ đồng hồ ra. Lúc tính công vẫn dựa vào việc đánh giá giờ đồng hồ vào, giờ ra nhằm tính ra các các loại công.

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

Việc tính toán thù bên trên nhiều ĐK, đề xuất hàm sẽ sử dụng để tính toán thù là SUMIFS, COUNTIFS cùng hàm IF

Cách 2: Cấu trúc lại bảng dữ liệu

Để rút gọn nội dung bài viết, mình sẽ không nêu từng làm việc, cơ mà sẽ đưa hình mẫu cùng gợi ý các bí quyết làm nhằm những bạn cũng có thể tự làm được:

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

*
*

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

Xem thêm: Chuyển File Word Sang Jpg Online & Free), Word Đến Jpg

Tất cả các văn bản bên trên, mình đang gợi ý chi tiết trong bài: Tạo bảng chấm công trên excel. Vui lòng xem lại bài xích này để được chỉ dẫn cụ thể.

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

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

*

Bảng kết xuất áp dụng công dụng lọc (Data Filter)

*

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

Cách 4: Tính giờ công trong ngày

*

Tạo thêm một vùng, trong đó hàng ngày chỉ tương ứng 1 cột. Cách cách xử lý ngày, thứ giống như cùng với phần trước.

=> Mục đích: Tính thời gian công thao tác theo từng ngày

Giờ công làm việc trong thời gian 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 tại 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ông thức trường đoản cú BN7 mang lại các ô khác.

* Lưu ý:

cũng có thể bao gồm số âm. Số âm là ngôi trường hòa hợp Chỉ bao gồm giờ đồng hồ vào cơ mà không có giờ đồng hồ ra => Lý do: Quên chấm công. Việc này thường xuyên xảy ra

Cách 5: Xác định tác dụng chấm công

ví dụ như chúng ta đề xuất một số ít tác dụng chnóng công nlỗi sau:

*

Ta rất có thể thấy:

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

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

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

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

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

Ngày nghỉ ngơi = (Tổng số ngày bao gồm công = 0) – (Số ngày nghỉ tuần = nhà nhật) Trường phù hợp ngày nghỉ ngơi tuần là chủ nhật. Nếu nghỉ cả T7, công nhân thì vẫn trừ thêm số ngày lắp thêm 7 trong tháng

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

Đi muộn: Cnạp năng lượng cđọng vào giờ vào làm cho, đối chiếu với tiêu chuẩn 8h (tuỳ quy ước tiếng vào làm của đơn 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)

=> Bạn có thể filldown phương pháp xuống cho các mã NV tiếp theo

Kết quả của mục 2.5 là công dụng sau cùng, hoàn toàn có thể sử dụng để tính lương mang lại NV

Cách tối ưu

Mình đang gợi ý một vài ba chuyên môn góp tối ưu hoá bảng xử lý: (Ưu tiên mang đến các bạn biết áp dụng VBA, còn nếu không biết VBA thì làm cho bằng tay thủ công bằng tay)

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

Công vấn đề chnóng công, tính lương tương đối tinh vi và đa dạng và phong phú. Để đọc thêm các kiến thức và kỹ năng liên quan tới công việc này, Học Excel Online xin trình làng với chúng ta khóa huấn luyện và đào tạo “Ứng dụng Excel VBA trong quá trình Tiền lương – nhân sự”. Học dứt khóa huấn luyện và đào tạo này bạn có thể gắng vững được tiến trình chnóng công, tính lương trong doanh nghiệp lớn, trường đoản cú tay xây cất 1 phần mượt thống trị lao động, chấm công tính lương trên Excel. Chi tiết coi tại: