Ứng dụng Power Query xây dựng mô hình dữ liệu phân tích năng suất lao động – Phần 3

Qua 2 phần trước, chúng ta đã ứng dụng Power Query để có thể tái cấu trúc bảng dữ liệu, giúp xây dựng bảng dữ liệu đúng chuẩn, đồng thời gộp dữ liệu từ 3 bảng vào chung 1 bảng.  Phần này Học Excel Online sẽ cùng các bạn tìm hiểu cách tạo ra các giá trị cần sử dụng trong báo cáo bằng cách sử dụng công thức tính trong Power Query.

Bạn có thể xem lại các phần tại:

Phần 1: Tái cấu trúc bảng dữ liệu đúng tiêu chuẩn

Phần 2: Gộp các bảng dữ liệu vào 1 bảng duy nhất

File kết quả sau 2 phần –  Link tải file: http://bit.ly/2Mo7Guc

Phần 3: cách sử dụng công thức tính trong Power Query

Tính năng suất lao động

Trong bảng dữ liệu kết quả của Power Query (bảng có tên Query1, trong Sheet1) chúng ta có Giá trị của từng dự án, tương ứng với Số lao động. Do đó Năng suất lao động tính bằng Giá trị chia cho Số lao động. Các bước để có thể tạo thêm cột có chứa công thức tính trong Power Query như sau:

Bước 1: Chọn Edit Query để trở về cửa sổ làm việc của Power Query

Bấm chọn bảng Query1, trên thanh công cụ sẽ xuất hiện thẻ Query, bạn bấm chọn Edit

(hoặc bấm chuột phải vào bảng Query1, chọn Table > Edit)

Bước 2: Tạo thêm cột trong Power Query

Chọn thẻ Add Column > chọn Custom Column. Khi đó sẽ xuất hiện cửa sổ Custom Column như hình sau:

• Trong mục New column name: nhập tên cho cột mới tạo này
• Trong mục Custome column formula: viết công thức tính để tạo ra kết quả cho cột. Bạn có thể chọn các cột có liên quan tới việc tính toán trong mục Avaiable columns (nhấn đúp chuột vào tên cột để lấy tên cột đó vào công thức)

Công thức cần sử dụng là:

Khi nhấn OK, chúng ta sẽ có cột mới. Cột này chính là Năng suất lao động.

Bước 3: Nhấn Close & Load để cập nhật bảng dữ liệu

(cột Năm đã được đưa lại gần cột Tháng để dễ nhìn hơn)

Khi đó chúng ta có thể thấy nội dung trong cột G (năng suất LĐ) là dạng giá trị, không phải dạng công thức tính. Việc tính toán được thực hiện qua Power Query và trả kết quả lại vào bảng Query1 một cách tự động.

Phân nhóm theo số lao động

Vì số lượng các chi nhánh là rất nhiều nên không thể trình bày hết tất cả các chi nhánh trên báo cáo. Do đó chúng ta có thể phân nhóm các chi nhánh để có thể báo cáo phân tích riêng trong từng nhóm. Như vậy sẽ giúp việc phân tích được chi tiết hơn.

Để làm điều này, chúng ta cần có 1 cột thể hiện Nhóm chi nhánh.

Giả sử chúng ta có tiêu chí phân nhóm chi nhánh theo số lao động như sau (dữ liệu giả định, bạn hoàn toàn có thể tự thay đổi các tiêu chí này):

• Khu vực 1: có số lượng lao động <= 180
• Khu vực 2: có số lượng lao động <= 400
• Khu vực 3: có số lượng lao động > 400

Với trường hợp này, chúng ta có thể thực hiện 1 cách đơn giản trong Excel: sử dụng hàm IF trong bảng Query1. Cách làm như sau:

• Bước 1: tại cột H, nhập tên tiêu đề cột vào ô H1 là Khu vực. Khi đó cột H sẽ được tính chung vào bảng Query1
• Bước 2: viết công thức tại ô H2 như sau:

=IF([@[Số lao động]]<=180, “Khu vực 1”, IF([@[Số lao động]]<=400, “Khu vực 2”, “Khu vực 3”))

Khi kết thúc công thức với phím Enter, toàn bộ cột H sẽ tự động được tính theo công thức này. Kết quả thu được là:

* Ưu điểm:

Trong Excel, bạn đã quen với việc viết công thức, do đó cách này rất dễ thực hiện.

Chức năng của Table sẽ giúp cột H tạo trực tiếp trên Excel cũng được tính tự động ngay khi bảng Query1 cập nhật kết quả (dù cột H lúc này không có sẵn trong Query1)

* Nhược điểm:

Việc này sẽ khiến bảng Query1 có chứa công thức động. Công thức này có thể làm file nặng và hoạt động chậm hơn.

* Cách khắc phục: Sử dụng chức năng tạo cột trực tiếp trong Power Query

Tạo cột theo điều kiện trong Power Query

Bước 1: Đưa bảng tính trở lại cửa sổ làm việc Power Query, tại thẻ Add Column, chúng ta bấm chọn mục Conditional Column

Bước 2: Trong cửa sổ Add Conditional Column chúng ta thiết lập như sau:

Cách thiết lập này giống với câu lệnh IF mà chúng ta đã viết ở trên. Tuy nhiên không viết trực tiếp trong Power Query mà phải chia ra từng phần, đặt tại từng vị trí đã quy định.

Mở rộng: cấu trúc trên gần giống với cấu trúc viết hàm IF trong VBA, bạn có thể tham khảo thêm tại đây:

Hướng dẫn cách viết cấu trúc IF THEN ELSE trong VBA Excel

Khi đó kết quả thu được sẽ là cột Khu vực được tạo ngay trong Power Query. Khi bấm Close & Load thì toàn bộ kết quả của cột Khu vực sẽ là dạng giá trị, không phải công thức tính.

Kết luận

Như vậy qua 2 ví dụ trên, chúng ta có thể biết cách tạo ra các giá trị không có sẵn trong bảng dữ liệu gốc bằng Power Query. Việc này sẽ giúp chúng ta có thêm các hướng để phân tích dữ liệu tốt hơn: thêm chỉ tiêu phân tích, phân nhóm chia nhỏ phạm vi để phân tích sâu hơn.

Các bạn có thể tải file kết quả tại địa chỉ: http://bit.ly/33hR3XW

Trong bài tiếp theo, chúng ta sẽ bắt đầu đi vào việc xây dựng các báo cáo phân tích dữ liệu về năng suất lao động. Cảm ơn các bạn đã theo dõi bài viết. Học Excel Online xin chào các bạn.