Công Thức Tính Giờ Tăng Ca Trong Excel

Không cần dịp nào bài toán chnóng công bên trên đồ vật chấm công cũng đem đến tác dụng tức thì. Trong một vài ngôi trường hợp họ phải đưa dữ liệu từ sản phẩm chnóng công sang Excel thì mới tính được 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 chúng ta cách tính số giờ thao tác trong bảng chấm công Excel với dữ liệu được trích xuất trường đoản cú vật dụng 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ỏ vật dụng chnóng công tháng 07 năm 2017 gồm:

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

Bạn đang xem: Công thức tính giờ tăng ca trong excel

Xem nhanh

Cách làm

Cách 1: Khảo gần kề, nhận xét cùng phân tích dữ liệu nguồn (bảng tài liệu kết xuất tự thiết bị chnóng công ra excel)

Đây là bước vô cùng quan trọng đặc biệt, Việc so với bảng dữ liệu nguồn sẽ giúp họ gồm phương án xử trí tương xứng và đúng chuẩn.

Để thực hiện công việc này, chúng ta cần nắm được các hiệ tượng sau:

Dù sẽ gồm ứng dụng, tuy nhiên kỹ năng Excel vẫn cực kì đặc biệt với kế tân oán, bạn vẫn vững Excel chưa? Hãy nhằm tôi giúp đỡ bạn, đăng ký khoá học Excel:

Mục đích công việc: Tính công thao tác làm việc theo thời gianĐối tượng liên quan: Mã nhân viên, Ngày hồi tháng, Giờ vào, Giờ raTiêu chí reviews (dựa vào mục đích, tác dụng cần đạt được để đưa ra tiêu chí mang đến các đối tượng liên quan) Mã Nhân viên: Liên tục theo mẫu, không được để chiếc trống Ngày vào tháng: Bao tất cả xác định ngày trong tháng, sản phẩm trong tuần, phân minh ngày nghỉ lễ, nghỉ ngơi bù Giờ vào, tiếng ra: Các ngày làm việc đều sở hữu tiếng vào, tiếng ra. Khi tính công đã dựa vào việc Đánh Giá giờ đồng hồ vào, giờ ra nhằm tính ra những các loại công.

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

Việc tính tân oán bên trên nhiều ĐK, phải hàm sẽ sử dụng nhằm tính toán là SUMIFS, COUNTIFS cùng 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, mình sẽ không còn nêu từng làm việc, mà lại sẽ chuyển biểu tượng và gợi ý những biện pháp làm để các chúng ta có thể trường đoản cú làm được:

(Bảng chnóng công này còn có cấu trúc từng ngày tất cả 2 cột, khác bài xích trước là từng ngày 1 cột)

Phần Tháng và Năm (chiếc 2, 3): Đặt mặt ngoài để luôn tiện việc thay đổiNgày trong tháng (cái 4): Dùng hàm Date, bắt đầu với ngày 26 mon trước kia (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 từng trải này. Tính công từ ngày 26 tháng trước mang lại ngày 25 mon sau). Riêng 2 ngày cuối rất có thể lịch sự tháng tiếp theo sau (trường thích hợp tháng tất cả 28-29 ngày) đề xuất hoàn toàn có thể xử lý để quăng quật trống nếu như thanh lịch tháng sau, Có nghĩa là ngày 27 mon này trở đi là đã bước sang trọng 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, nhằm tập đúng theo theo mã NV đúng mực hơn.Thứ vào tuần (mẫu 5): Dùng hàm Choose kết hợp với hàm Weekday để xác định. Kết phù hợp kỹ năng Conditional Formatting nhằm đánh màu sắc minh bạch ngày công nhân với ngày thường xuyên.Giờ vào, giờ đồng hồ ra (dòng 6): hoàn toàn có thể quy ước số một là giờ đồng hồ vào, số 2 là giờ ra.Lưu ý: một ngày tất cả 2 tiếng, buộc phải 2 cột và tính cho ngày. Vì vậy Khi cách xử trí ngày sống mẫu 4 nên chú ý câu chữ này.

Tất cả hầu như ngôn từ bên trên, mình đã hướng dẫn cụ thể trong bài: Tạo bảng chấm công trên excel. Vui lòng xem xét lại bài này để được hướng dẫn cụ thể.

Cách 3: Lọc với rước dữ liệu 

Tại bảng kết xuất, cần sử dụng tính năng Data/Filter nhằm lọc bỏ mẫu trống trong cột Mã NV, sau kia copy dữ liệu lịch sự bảng cách xử lý. Sử dụng tính năng Paste Special/Value cần nên dán dữ liệu bỏ lỡ chiếc bị ẩn.

Bảng kết xuất áp dụng chức năng lọc (Data Filter)

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

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

Tạo thêm 1 vùng, trong kia hàng ngày chỉ tương ứng 1 cột. Cách xử trí ngày, lắp thêm giống 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 làm việc trong thời gian ngày = Giờ ra – Giờ vào

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

Giờ vào = (Tổng số phút quy ước tại thời điểm chấm công vào = Số giờ đồng hồ * 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 hoàn toàn có thể filldown, fillright bí quyết tự BN7 cho các ô không giống.

* Lưu ý:

cũng có thể gồm số âm. Số âm là trường hòa hợp Chỉ bao gồm giờ đồng hồ vào mà lại không có giờ đồng hồ ra => Lý do: Quên chnóng 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ụ như chúng ta buộc phải một số kết quả chấm công nhỏng sau:

Ta có thể thấy:

Ngày công đủ 8h tính dựa vào vùng vẫn xác định rõ ràng thời gian công, với 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 vào vùng đã xác định khoảng thời gian công, cùng với khoảng 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 hoàn toàn có thể vẫn được tính là 1 ngày công,hoặc Tổng số ngày công = Đủ 8h + (Quên chnóng công /2) nếu như quy ước quên chấm công đã xem là nửa ngày công.

Xem thêm: Hướng Dẫn Cách Chụp Màn Hình Samsung J1 Mini Nhanh Nhất, Cách Chụp Màn Hình Samsung Galaxy J1

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

Ngày ngủ = (Tổng số ngày tất cả công = 0) – (Số ngày nghỉ tuần = chủ nhật) Trường hợp ngày nghỉ tuần là nhà nhật. Nếu nghỉ ngơi cả T7, công nhân thì sẽ trừ thêm số ngày thiết bị 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 khung giờ vào làm, đối chiếu với tiêu chuẩn 8h (tuỳ quy ước giờ đồng hồ vào có tác dụng của đối kháng 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 hàng hoàn toà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à kết quả ở đầu cuối, hoàn toàn có thể sử dụng nhằm tính lương cho NV

Cách tối ưu

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

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

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