G-Learning
G-Learning
Thảo luận 0 thảo luận
Vỗ tay 0 vỗ tay
Lượt xem 269 lượt xem

Hướng dẫn theo dõi đi làm muộn từ dữ liệu máy chấm công

G-Learning
G-Learning
Aug 03 2018

Hiện nay nhiều công ty, đơn vị thường sử dụng máy chấm công để theo dõi thời gian đi làm, thời gian làm việc của nhân viên. Tuy nhiên không phải dữ liệu từ máy chấm công cũng đáp ứng được ngay nhu cầu theo dõi. Trong bài viết này chúng ta sẽ cùng Gitiho.com tìm hiểu cách theo dõi đi làm muộn từ dữ liệu máy chấm công. Cụ thể như sau:

Bài tập theo dõi đi muộn từ dữ liệu máy chấm công

Tại 1 đơn vị có dữ liệu trích xuất từ máy chấm công (sheet Data):

Dữ liệu này tính cho 10 nhân viên có mã nhân viên từ NV01 đến NV10, gồm 212 dòng dữ liệu và 1 dòng tiêu đề.

Yêu cầu của người chấm công là theo dõi số lần đi làm muộn của từng người và điền vào bảng dưới đây (Sheet BCC):

Bảng theo dõi đi muộn

Bảng theo dõi đi muộn

Cách làm

Bước 1: Đặt tên các cột trong bảng dữ liệu chấm công

Để cho tiện theo dõi và việc viết công thức được dễ hơn, đầu tiên chúng ta sẽ tiến hành đặt tên cho các cột dữ liệu trong bảng dữ liệu chấm công.

Sử dụng Name Box hoặc công cụ Define Name trong thẻ Formulas để đặt tên cho các vùng dữ liệu tại sheet Data như sau:

  • A2:A213 là cot_ID
  • B2:B213 là cot_Ngay
  • C2:C213 là cot_GioVao

Bước 2: Thiết lập bảng chấm công đi muộn

Khi thiết lập bảng này, cần lưu ý các vị trí: Tháng (B2), Năm (B3), Mốc thời gian tính đi muộn (B4).

Dòng thể hiện các ngày trong bảng chấm công thực hiện như sau:

  • Ngày đầu tháng: Dùng hàm Date lấy theo năm tại ô B3, tháng tại B2, ngày là ngày 01 => B6=Date(B3,B2,1)
  • Những ngày tiếp theo: bằng ngày trước đó cộng thêm 1
  • Ngày cuối tháng: biện luận trường hợp bắt đầu sang tháng sau. Nội dung này các bạn có thể xem thêm tại bài viết: Hướng dẫn cách tạo bảng chấm công trên Excel chi tiết nhất

Bước 4: Xây dựng công thức tính đi muộn

Việc theo dõi số lần đi muộn thực tế là đếm xem tại ngày nào thì nhân viên đó có giờ vào muộn hơn so với giờ quy định.

Như vậy chúng ta sử dụng hàm COUNTIFS để đếm theo nhiều điều kiện, trong đó:

  • cot_ID tìm theo mã nhân viên tương ứng của BCC
  • cot_Ngay tìm theo ngày tương ứng với dòng 6 trong BCC
  • cot_GioVao tìm theo giá trị lớn hơn mốc thời gian

Tại ô B7 cho nhân viên 01, ngày 1 ta có công thức như sau:

B7=COUNTIFS(cot_ID,$A7,cot_Ngay,B$6,cot_GioVao,”>”&$B$4)

Để tìm hiểu cách cố định tọa độ trong các điều kiện của công thức trên, các bạn có thể tham khảo bài viết:

Hướng dẫn cách viết điều kiện hàm SUMIFS để đạt hiệu quả tốt nhất

Áp dụng công thức tại B7 cho các ngày còn lại (fillright) và các nhân viên còn lại (filldown), chúng ta có kết quả như sau:

Bảng theo dõi đi muộn

Bảng theo dõi đi muộn

Toàn bộ đều bằng 0. Như vậy chắc chắn là không đúng rồi. Ở đây có 1 lỗi mà đang ra chúng ta phải làm ngay từ đầu, đó là kiểm tra tính chính xác của dữ liệu trước khi tính toán, báo cáo. Cụ thể ở đây là kiểm tra dữ liệu về thời gian Ngày, giờ đã chính xác chưa, đã đúng loại dữ liệu thời gian chưa.

Bước 5: Kiểm tra và sửa lỗi dữ liệu

Quay trở lại bảng dữ liệu, kiểm tra 2 cột Date và InTime bằng hàm IsNumber xem dữ liệu tại đây có phải là dữ liệu dạng Số hay không

Kết quả chúng ta thấy đều là FALSE, tức là không phải dữ liệu dạng số. Chính vì vậy mà hàm COUNTIFS đã không ra kết quả đúng.

Để sửa lỗi dữ liệu này chúng ta làm như sau:

Sử dụng hàm Value kết hợp hàm Text để sửa lỗi dữ liệu cho từng cột

  • Với dữ liệu dạng ngày (cột B, từ B2): =VALUE(TEXT(B2,”dd/mm/yyyy”))
  • Với dữ liệu dạng giờ (cột C, từ C2): =VALUE(TEXT(C2,”hh:mm:ss”))

Tham khảo bài viết: Cách sửa định dạng thời gian bị lỗi về dạng chuẩn cả giờ phút giây

Sau khi có được kết quả, chúng ta sẽ tiến hành định dạng dữ liệu về chuẩn dạng thời gian:

Cuối cùng, để trả lại kết quả đúng vào các cột Date, InTime, chúng ta sẽ copy dữ liệu từ cột E, F; Sau đó Paste Value vào cột B, C

Khi paste value thì chỉ đảm bảo dữ liệu về đúng loại dữ liệu và giá trị, còn định dạng sẽ ở dạng mặc định sẵn trong ô.

  • Định dạng lại dữ liệu tại cột B về dạng dd/mm/yyyy
  • Định dạng lại dữ liệu tại cột C về dạng hh:mm:ss

Bây giờ quay trở lại bảng chấm công đi muộn và xem kết quả của công thức COUNTIFS tự động thay đổi nào:

Đây chính là kết quả mong muốn của chúng ta rồi. Các bạn có thể tải về file mẫu tại địa chỉ: http://bit.ly/2n71uKI

Xem thêm:

Hướng dẫn cách theo dõi số ngày phép còn lại trong năm trên Excel

Hướng dẫn cách quản lý công văn, hợp đồng trên Excel

Ngoài việc theo dõi đi muộn, công việc của hành chính-nhân sự còn phải theo dõi nhiều nội dung khác như: Công văn giấy tờ, hợp đồng lao động, quản lý cấp phát đồ dùng văn phòng phẩm, chấm công tính lương… Để nắm được những kiến thức và cách ứng dụng Excel trong công việc hành chính-nhân sự, mời bạn tham gia khóa học Hành chính nhân sự từ A-Z của Gitiho.com. Bật mí là bạn còn được tặng kèm phần mềm quản lý nhân sự khi tham gia khóa học này nhé. Chi tiết xem tại:

Đánh giá bài viết này

Gitiho.com Nền tảng học online duy nhất tại Việt Nam tập trung vào phát triển kỹ năng làm việc dành cho người đi làm
Với sứ mệnh: Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người”, đội ngũ phát triển Gitiho.com đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án...
Gitiho tự hào khi được đồng hành cùng:
  • 50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
  • 100.000+ học viên trên khắp Việt Nam

Tìm hiểu ngay các khóa học của Gitiho TẠI ĐÂY



Thảo luận 0 câu trả lời
Lượt xem 269 lượt xem
Vỗ tay vỗ tay

0 Bình luận

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội