Không buộc phải thời điểm làm sao Việc chnóng công bên trên trang bị chnóng công cũng mang lại kết quả tức thì. Trong một vài trường thích hợp bọn họ phải đưa tài liệu từ bỏ máy chnóng công sang Excel thì mới có thể tính được khoảng thời gian 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 đã hướng dẫn các bạn cách tính số giờ làm việc nằm 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ú thiết bị chấm công. Nội dung ví dụ như sau:

Chúng ta gồm bảng dữ liệu trích xuất từ đồ vật chnóng 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 giờ của từng nhân viên cấp dưới, hãy tính số giờ thao tác làm việc, số lần đi muộn của những 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

Bước 1: Khảo gần kề, Review với so sánh dữ liệu nguồn (bảng dữ liệu kết xuất từ sản phẩm chấm công ra excel)

Đây là bước siêu đặc trưng, Việc đối chiếu bảng dữ liệu mối cung cấp để giúp chúng ta bao gồm phương án cách xử trí phù hợp và đúng đắn.

Để triển khai quá trình này, chúng ta nên nắm được các cách thức sau:


Dù vẫn bao gồm ứng dụng, nhưng khả năng Excel vẫn cực kỳ quan trọng đặc biệt với kế tân oán, chúng ta đang vững vàng Excel chưa? Hãy để tôi giúp bạn, ĐK khoá học tập 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 trong tháng, Giờ vào, Giờ raTiêu chí reviews (dựa vào mục đích, kết quả cần đạt được để đặt ra tiêu chí cho 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 vào tháng: Bao bao gồm xác định ngày hồi tháng, lắp thêm trong tuần, biệt lập ngày nghỉ lễ, nghỉ ngơi bù Giờ vào, tiếng ra: Các ngày có tác dụng việc đều có giờ đồng hồ vào, giờ ra. Lúc tính công đang dựa trên việc Đánh Giá giờ đồng hồ vào, giờ ra nhằm tính ra những loại công.

Ta có thể thấy, để tính toán được, bắt buộc phải điều chỉnh lại cấu trúc bảng dữ liệu sao cho đúng các tiêu chí.

Việc tính tân oán bên trên nhiều điều kiện, yêu cầu hàm đã sử dụng nhằm 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 ít gọn bài viết, bản thân sẽ không nêu từng thao tác, cơ mà sẽ chuyển hình mẫu và gợi ý những bí quyết làm để các bạn cũng có thể từ làm được:

(Bảng chấm công này còn có cấu trúc hàng ngày tất cả 2 cột, không giống bài trước là từng ngày 1 cột)

*
*

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

Xem thêm: "Quà Tặng Doraemon Doremon Chế Hay Nhất #39, Tập Cuối Đoremon Trở Về (Full)

Tất cả phần đông ngôn từ trên, mình sẽ chỉ dẫn chi tiết vào bài: Tạo bảng chnóng công trên excel. Vui lòng xem xét lại bài bác này để được gợi ý chi tiết.

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

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

*

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

*

Dữ liệu chấm công sinh hoạt 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 1 vùng, trong đó mỗi ngày chỉ tương ứng 1 cột. Cách cách xử lý ngày, sản phẩm công nghệ kiểu như cùng với phần trước.

=> Mục đích: Tính số giờ công thao tác 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 ít quy ước trên 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ố tiếng * 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 rất có thể filldown, fillright cách làm từ bỏ BN7 mang đến các ô khác.

* Lưu ý:

Có thể gồm số âm. Số âm là ngôi trường vừa lòng Chỉ có giờ vào mà không tồn tại giờ ra => Lý do: Quên chnóng công. Việc này thường xảy ra

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

lấy ví dụ bọn họ đề nghị 1 số ít kết quả chnóng công nhỏng sau:

*

Ta hoàn toàn có thể thấy:

Ngày công đủ 8h tính dựa vào vùng vẫn xác định cụ thể thời gian công, cùng với khoảng thời gian làm >=8 (mục 2.4)

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

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

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

Ngày nghỉ = (Tổng số ngày gồm công = 0) – (Số ngày nghỉ ngơi tuần = chủ nhật) Trường hợp ngày nghỉ ngơi tuần là công ty nhật. Nếu nghỉ ngơi cả T7, CN thì đang trừ thêm số ngày sản phẩm 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 cho, so sánh với chỉ tiêu 8h (tuỳ quy ước giờ đồng hồ vào làm cho 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 rất có thể filldown cách làm xuống cho những mã NV tiếp theo

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

Cách tối ưu

Mình đã gợi ý một vài ba nghệ thuật giúp tối ưu hoá bảng xử lý: (Ưu tiên mang lại mọi chúng ta biết sử dụng VBA, còn nếu không biết VBA thì làm cho bằng tay bởi tay)

Sử dụng VBA nhằm giữ thông tin cách xử trí cùng tác dụng tính được qua 1 Sheet riêng để tàng trữ hiệu quả. Bảng giải pháp xử lý hoàn toàn có thể đứng vững cho những mon sau nhưng không ảnh hưởng cho tới công dụng tháng trước.Sử dụng VBA ẩn/hiện tại những vùng: Dữ liệu chấm công theo giờ đồng hồ vào-ra (cột D mang lại cột BM), Dữ liệu xử lý tính giờ đồng hồ thao tác (Cột BN mang lại cột CR) để làm gọn gàng bảng cách xử trí. Khi yêu cầu làm việc với bảng nào thì ta mở ẩn vùng bảng đó ra. Như vậy sẽ gọn gàng mắt hơn. Vì phương châm sau cuối là kết quả tính công mà thôi.

Công vấn đề chnóng công, tính lương tương đối phức tạp và đa dạng mẫu mã. Để tìm hiểu thêm những kiến thức tương quan cho tới công việc này, Học Excel Online xin giới thiệu cùng với các bạn khóa đào tạo và huấn luyện “Ứng dụng Excel VBA trong quá trình Tiền lương – nhân sự”. Học chấm dứt khóa huấn luyện này chúng ta có thể ráng vững được quy trình chấm công, tính lương vào doanh nghiệp, trường đoản cú tay tạo một phần mượt quản lý lao cồn, chấm công tính lương trên Excel. Chi tiết coi tại:

Bài viết liên quan

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *