Trong bài viết này, chúng ta cùng tìm hiểu một ứng dụng của Power Query trong việc xây dựng mô hình dữ liệu về phân tích năng suất lao động. Mô hình này cũng thường gặp trong thực tế khi làm việc với Excel. Hãy xem với Power Query chúng ta có thể làm được gì nhé.

Bài tập xây dựng mô hình dữ liệu phân tích năng suất lao động

Trước hết, các bạn có thể tải file bài tập tại địa chỉ: http://bit.ly/31Yt0gn

Nội dung gồm 3 bảng dữ liệu tương ứng với 3 năm: 2017, 2018, 2019. Trong đó tại mỗi bảng gồm:

• Chi nhánh: ứng với các tỉnh, thành phố của Việt Nam.
• Dự án: có 5 dự án, các chi nhánh đều tham gia vào 5 dự án này.
• Số lao động và Giá trị thực hiện các dự án: tùy theo từng tháng, từng năm mà có sự thay đổi, không cố định. Các giá trị này là giá trị được ghi nhận lại theo bảng kết quả đã có như trong bài.

Có thể thấy bảng dữ liệu là khá lớn, có nhiều chỉ tiêu để phân tích. Nhưng có quá nhiều dữ liệu! Làm sao phân tích được năng suất lao động với 3 bảng này? Bạn đã sẵn sàng đi tìm câu trả lời chưa nào.

Đây là kết quả sau khi bạn theo dõi đến hết Seri bài viết này:

Phân tích cấu trúc dữ liệu

Đặc điểm bảng dữ liệu trong bài tập

Nhìn vào cả 3 bảng, chúng ta nhận thấy có 1 đặc điểm chung:

Dữ liệu được tổ chức theo dạng bảng 2 chiều, gồm:

• Chiều dọc (đường mũi tên màu đỏ) : Mỗi chi nhánh sẽ ứng với 1 dòng
• Chiều ngang (đường mũi tên màu cam và màu xanh) : Ứng với các tháng, trong mỗi tháng sẽ gồm thông tin về số lao động và các dự án.

Giao điểm của 2 chiều này chính là giá trị tương ứng:

• Ô B3: Số lao động trong tháng 1 của chi nhánh An Giang
• Ô G8: Giá trị của dự án 5 trong tháng 1 của chi nhánh Bắc Ninh

Có thể thấy việc tổ chức dữ liệu như vậy sẽ giúp cho bạn nhanh chóng ghi nhận các dữ liệu phát sinh, nhưng lại rất khó khăn trong việc phân tích dữ liệu (không sử dụng được Pivot Table).

Vậy làm thê nào để có thể vừa nhập dữ liệu trên cấu trúc bảng cũ, vừa có bảng dữ liệu đúng cấu trúc có thể dùng được Pivot Table?

Cấu trúc lại bảng dữ liệu bằng Power Query

Bằng cách sử dụng Power Query, chúng ta có thể tái cấu trúc lại bảng dữ liệu trong các năm. Quy trình thực hiện gồm các bước sau:

Bước 1: Chuyển bảng dữ liệu gốc về dạng Table

Mục đích: Hầu hết mọi thao tác xử lý dữ liệu với Power Query đều thực hiện tốt hơn khi dữ liệu ở dạng Table.

Ví dụ với bảng dữ liệu năm 2017 (Sheet 2017):

• Chọn toàn bộ bảng dữ liệu
• Trong thẻ Insert, chọn mục Table (phím tắt là Ctrl + T)
• Khi hiện lên hộp thoại xác nhận việc chuyển sang dạng Table, bạn bỏ dấu tích chọn tại mục ‘My Data has Headers’

Khi thực hiện thao tác này, Excel sẽ bỏ trộn ô và tạo ra thêm 1 dòng làm dòng tiêu đề. Bạn không cần lo lắng về việc này. Chúng ta vẫn có thể nhập dữ liệu bình thường vào bảng. Chỉ khác là thay vì ở dạng Range thì nhập ở dạng Table.

Bước 2: Đặt tên cho bảng Table

Vì có nhiều bảng khác nhau nên chúng ta có thể đặt tên cho các bảng để tránh nhầm lẫn.

Tên có thể đặt dưới dạng

• Nam_2017 cho bảng trong Sheet 2017
• Nam_2018 cho bảng trong Sheet 2018
• Nam_2019 cho bảng trong Sheet 2019

Bước 3: Nạp dữ liệu vào Power Query

Trong Power Query, chọn mục From Table/Range (Với Excel 2016 hoặc Office 365 thì tìm trong thẻ Data):

Bước 4: Định hình bảng dữ liệu đúng

(bước này có thể làm ngay từ trước bước 1)

Cấu trúc đúng là cấu trúc mà với mỗi 1 giá trị sẽ bao gồm đầy đủ các nội dung có liên quan trên cùng 1 dòng.

Ví dụ:

• Chi nhánh A, trong tháng 1, có Số lao động là 201 => Như vậy sẽ gồm 3 cột: Chi nhánh, Tháng. Số lao động
• Chi nhánh B, trong tháng 2, với Dự án 1, có giá trị là 20000 => Như vậy sẽ gồm 4 cột: Chi nhánh, Tháng, Dự án, Giá trị

Thông qua nhận định một vài nội dung, chúng ta có thể tổng hợp lại cấu trúc 1 bảng dữ liệu đúng chuẩn sẽ gồm:

• Các cột: Chi nhánh, Tháng, Dự án, Số lao động, Giá trị
• Với cột Số lao động và Giá trị sẽ có dữ liệu tương ứng theo Chi nhánh, Tháng, Dự án

Do đó cấu trúc trên Excel sẽ là:

Bước 5: Xử lý trong Power Query để ra được cấu trúc đúng

Trong Power Query, giao diện bảng dữ liệu khi nạp vào có dạng:

Có thể thấy 2 dòng đầu tiên chứa thông tin về các tháng và Số lao động, Dự án. Do đó công việc sẽ gồm:

5.1. Đưa dòng 1 và 2 về cùng 1 dòng:

• Trong thẻ Transform, chọn mục Transpose (cả bảng): Chuyển dòng thành cột, cột thành dòng. Mục đích đưa nội dung cần xử lý (dữ liệu tại các cột trong phạm vi 2 dòng đầu) xuống thành các dòng (thành 2 cột, nhiều dòng)
• Trong thẻ Transform, chọn mục Fill > Down cho cột Column1: Để làm đầy các giá trị Null bằng nội dung ở dòng trên (giá trị Null có nghĩa là ô trống, xuất hiện khi bỏ trộn ô)

• Transform > Merge Columns: Trộn cột Column1 và Column2 vào chung 1 cột (để từ 2 cột chuyển về 1 cột). Khi gộp nên lấy theo 1 ký tự đặc biệt để có thể dùng cho vi tách lại 2 dữ liệu này. Ví dụ: Dấu phẩy (comma)

• Sau ghi ghép cột xong, thực hiện lại thao tác xoay bảng với Transpose. Lúc này chỉ còn lại dòng 1 chứa thông tin với vai trò dòng tiêu đề.
• Trong thẻ Transform, sử dụng Use First Row as Headers: Để sử dụng dòng 1 làm dòng tiêu đề cho bảng.

5.2. Đưa bảng dữ liệu về đúng cấu trúc

• Trong thẻ Transform, sử dụng chức năng Unpivot Columns bằng cách: Chọn cột Chi nhánh, sau đó chọn mục Unpivot Other Column (Unpivot các cột còn lại).

Kết quả sẽ có dạng:

Chức năng Unpivot Column sẽ giúp đưa các dữ liệu trên các dòng tiêu đề thành dữ liệu trên 1 cột, tương ứng với từng dòng dữ liệu phát sinh.

Có thể thấy trong cột Attribute chính là cột có chứa tên Tháng, Số lao động, tên Dự án. Cần phải tách các giá trị đang bị trộn lẫn với nhau ra từng cột. Tiếp theo chúng ta sẽ dùng chức năng Split Column để làm việc này.

Tận dụng dấu phẩy (comma) đã sử dụng ở trên làm căn cứ tách, chúng ta sẽ chọn tách cột theo dấu phẩy (Split Column by Delimiter):

Sau khi tách ra ta được kết quả:

Để đưa nội dung Số lao động lên vị trí cột, ta có:

• Chọn cột Attribute.2 và chọn Pivot Column, lấy theo giá trị trong cột Value
• Unpivot Column các cột Dự án: Chọn các cột từ Dự án 1 đến dự án 5 rồi bấm Unpivot Columns (Unpivot Selected Columns)

Kết quả thu được là:

Việc còn lại là sắp xếp lại các cột theo thứ tự, đặt lại tên tiêu đề cho các cột.

Khi đó chúng ta đã hoàn thành toàn bộ các bước xử lý lại cấu trúc bảng dữ liệu trong Power Query rồi. Các thao tác sẽ được ghi lại tại mục Applied Steps để có thể tự động cập nhật mỗi khi bảng dữ liệu gốc có thay đổi nội dung.

Bước 6: Xuất dữ liệu kết quả ra Excel

Trong thẻ Home của cửa sổ Power Query, bạn chọn mục Close & Load để xuất kết quả ra Excel.

Các bạn có thể áp dụng tương tự cho bảng Nam_2018 và Nam_2019 để hoàn tất công việc này.

– – –

Đón xem phần 2: Cách gộp các bảng dữ liệu Năm 2017, 2018, 2019 vào chung 1 bảng dữ liệu