Hướng dẫn xử lý dữ liệu xuất ra từ máy chấm công dạng ma trận bảng

Hướng Dẫn Xử Lý Dữ Liệu Máy Chấm Công Dạng Ma Trận Thành Dạng Bảng Chuẩn Bằng Power Query & Excel

Nhiều máy chấm công hiện nay xuất dữ liệu báo cáo chấm công dưới dạng ma trận (Grid Format) – trong đó tên nhân viên, mã nhân viên gộp ô (Merge) và các ngày trong tháng được dàn theo hàng ngang. Dạng dữ liệu này rất khó để lập công thức tính toán giờ công hay làm báo cáo tổng hợp. Bài viết này sẽ hướng dẫn bạn phương pháp chuẩn hóa, unmerge dữ liệu, áp dụng Power Query Unpivot để chuyển dữ liệu ma trận thành dạng bảng (Table) và lập công thức tính giờ hành chính, tăng ca tự động.

Video hướng dẫn chi tiết cách xử lý dữ liệu chấm công dạng ma trận bằng Power Query trên kênh Excel Thỉnh Vũ.

1. Những Bất Cập Khi Dữ Liệu Chấm Công Ở Dạng Ma Trận

• Các ô thông tin bị gộp (Merge cells): Mã nhân viên, Tên nhân viên hoặc Bộ phận bị gộp ô trên nhiều dòng khiến việc lọc (Filter) hay dùng hàm tra cứu bị sai số liệu.
• Dữ liệu dàn trải hàng ngang: Các ngày từ Ngày 1 đến Ngày 31 dàn theo từng cột, khó tập hợp dữ liệu để tính tổng giờ công hay chuyển sang các hệ thống quản lý khác.
• Xử lý thủ công tốn thời gian: Việc copy-paste hoặc chỉnh sửa thủ công hàng tháng ngốn nhiều giờ làm việc và dễ xảy ra nhầm lẫn.

2. Quy Trình Xử Lý Dữ Liệu Bằng Power Query & Excel

a) Bỏ gộp ô (Unmerge) & Tự động điền giá trị (Fill Down)

Sử dụng tính năng Unmerge trên Excel kết hợp với công cụ Go To Special -> Blanks và công thức copy dòng trên để tự động điền đầy đủ Mã NV/Tên NV vào tất cả các ô trống sau khi bỏ gộp.

b) Áp dụng Power Query Unpivot Columns

Đưa bảng dữ liệu vào Power Query, chọn các cột cố định (Mã NV, Tên NV, Bộ phận) và thực hiện thao tác Unpivot Other Columns để xoay toàn bộ các cột ngày tháng hàng ngang thành dạng hàng dọc chuẩn quản trị dữ liệu.

c) Tách cột Pivot Columns trong Power Query

Sử dụng tính năng Pivot Column hoặc bóc tách dữ liệu để phân định rõ ràng các trường dữ liệu: Giờ vào, Giờ ra, Số giờ công và Ca làm việc.

d) Công thức tính giờ hành chính & Tăng ca (OT)

Sau khi dữ liệu đã ở dạng bảng chuẩn, lập công thức Excel tự động xác định:

• Số giờ làm việc hành chính: Thời gian làm việc trong ca chuẩn.
• Giờ tăng ca ngày thường (OT 150%): Số giờ làm thêm ngoài ca quy định vào các ngày làm việc trong tuần.
• Giờ tăng ca Chủ Nhật / Ngày lễ (OT 200% - 300%): Số giờ làm việc phát sinh vào ngày nghỉ hàng tuần hoặc ngày lễ tết.

Lợi ích vượt trội: Quy trình tự động hóa giúp bạn biến file chấm công ma trận phức tạp thành bảng dữ liệu sạch chỉ trong vài nhấp chuột, tiết kiệm đến 90% thời gian xử lý dữ liệu hàng tháng.

---

KHÓA HỌC & TƯ VẤN EXCEL CHO NGƯỜI ĐI LÀM & DOANH NGHIỆP

Bạn cần tư vấn giải pháp xử lý dữ liệu chấm công, Bảng lương tự động hoặc Khóa học Power Query / Excel chuyên sâu cho doanh nghiệp?

📞 Liên hệ ĐT/Zalo: 038 696 1334

🌐 Kênh YouTube: Excel Thỉnh Vũ