Ứ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 4

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. 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

Phần 3: Tạo thêm các giá trị bằng công thức tính trong Power Query

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

Trong phần này, chúng ta sẽ cùng tìm hiểu cách sử dụng Pivot Table và Pivot Chart để lập báo cáo phân tích kết quả năng suất lao động. Lưu ý rằng việc này chỉ thực hiện được sau khi chúng ta đã hoàn thành 3 phần trên.

Thế nào là 1 báo cáo phân tích dữ liệu? Bạn đã từng nghe tới khái niệm này chưa? Khi hiểu rõ về mục đích, đặc điểm của báo cáo này thì chúng ta mới có thể xây dựng được.

Báo cáo phân tích là dạng báo cáo dùng cho mục đích phân tích dữ liệu. Dựa trên kết quả phân tích để tìm ra phương pháp, mục tiêu hành động.

Đối tượng của loại báo cáo này dành cho những người làm công việc liên quan tới quản lý, giám đốc. Đặc điểm của công việc này thường bao gồm các nội dung sau:

Để đảm bảo báo cáo đạt được các mục tiêu trên, yêu cầu của dạng báo cáo này thường là:

Do đó công cụ chủ yếu trong Excel để làm việc này chính là Pivot Table và Pivot Chart. Bởi Pivot Table tạo ra các báo cáo động, giúp linh hoạt trong việc thể hiện nội dung báo cáo. Pivot Chart là dạng biểu đồ được vẽ nên từ Pivot Table. Đồng thời từ Pivot Table chúng ta có thể tạo ra các Slicer, Timeline để làm bộ lọc giúp phân tích theo 1 số chỉ tiêu cụ thể.

Trước khi xây dựng báo cáo, chúng ta nên có 1 số hình dung về cấu trúc, bố cục báo cáo. Bởi có rất nhiều nội dung sẽ cần được trình bày trên báo cáo này. Do đó việc sắp xếp, trình bày cũng rất quan trọng.

Một số tiêu chí cần có trong bố cục báo cáo:

Các bạn có thể tham khảo 1 số mẫu bố cục báo cáo trong file sau: http://bit.ly/2p76jZ1

Ví dụ chúng ta sử dụng mẫu báo cáo như hình bên dưới để biểu diễn cho báo cáo phân tích năng suất lao động:

Các bạn có thể copy mẫu báo cáo trong file Template, sau đó dán vào file Bài tập đang làm để có mẫu báo cáo mà không cần phải tự vẽ lại.

Đặc điểm dữ liệu trong các cột sẽ liên quan tới việc bạn có thể biểu diễn được những gì trên báo cáo:

Tiếp đến chúng ta hình dung 1 số dạng báo cáo có thể dùng để phân tích, phản ánh năng suất lao động:

Bước này rất quan trọng, bởi dựa trên các yếu tố đã có và mối quan hệ giữa các yếu tố đó, chúng ta có thể định hình được sẽ biểu diễn nội dung gì trên báo cáo và cách phân tích báo cáo.

Bằng cách chọn bảng Query1, chúng ta chọn tiếp thẻ Insert > Pivot Table.

Mục đích của việc này:

Nếu đã có Sheet báo cáo sẵn rồi thì chúng ta chọn Existing Worksheet và chọn vị trí nơi đặt Pivot Table trong Sheet đó (nếu bạn đã thực hiện Bước 2 thì có thể đặt Pivot table vào cùng Sheet có chứa mẫu báo cáo). Còn không thì sẽ đặt Pivot Table tại 1 Sheet mới thì chọn New Worksheet.

Trong Pivot Table vừa tạo ở bước 3, bạn bấm vào thẻ PivotTable Analyze (hoặc PivotTable Option), chọn mục Insert Slicer

Cụ thể ở đây chúng ta lấy theo 2 tiêu chí là Dự án và Khu vực

Các thẻ ở đây chính là phần xác định các chỉ tiêu chứa Số và biến động (phần bên trái của mẫu báo cáo).

Ví dụ: Xác định tổng giá trị của các nội dung được biểu thị và tỷ lệ % của số này so với toàn bộ các giá trị.

=SUM(Query1[Giá trị])

Tính tổng trong toàn bộ cột Giá trị của bảng Query1 (khi viết hàm SUM rồi tham chiếu tới cột Giá trị thì sẽ tự động nhận được cách viết hàm SUM như trên)

=GETPIVOTDATA(“Giá trị”,$A$3)/A7

Định dạng kết quả dưới dạng %, chúng ta có:

Để đưa được nội dung vào trong các thẻ (là các hình khối): Bạn chọn hình khối, bấm phím F2 để có thể viết công thức gắn giá trị, sau đó nhập tọa độ muốn tham chiếu (=A4 với số tổng giá trị, =A10 với số tỷ lệ)

Ngoài ra để rút gọn giá trị trong phần tổng giá trị, bạn có thể tạo công thức chuyển đổi giá trị tại ô A4 sang 1 ô khác, sau đó tham chiếu tới ô đó:

Ví dụ: Dùng hàm ROUND để làm tròn cho phép chia giá trị tại ô A4 cho 1 triệu, sau đó nối thêm chữ “triệu” để biểu thị giá trị kết quả đã rút gọn lên hàng triệu.

Sau đó gán giá trị trong thẻ với ô A2.

Bạn hoàn toàn có thể tạo thêm các thẻ khác: Năng suất lớn nhất, năng suất trung bình, năng suất nhỏ nhất, tổng số lao động… để biểu diễn lên các thẻ tùy theo nhu cầu báo cáo.

Để thể hiện nội dung trên biểu đồ, các bước cần thực hiện như sau:

Row: Chi nhánh

Value: Năng suất

Vì có rất nhiều chi nhánh nên chúng ta có thể lọc theo Top10 chi nhánh có năng suất lớn nhất: Bấm nút lọc trong cột Chi nhánh > Chọn Value Filters > Chọn Top 10… > xuất hiện cửa sổ lọc theo Top10, bấm OK

Tiếp theo chúng ta tạo Pivot Chart từ PivotTable này:

+ Bỏ bớt tên tiêu đề, ghi chú

+ Ẩn các nút không cần thiết (bấm chuột phải vào vị trí các nút, chọn Hide All Field Buttons on Chart như hình dưới)

Tại Bước 6, chúng ta tạo ra thêm PivotTable khác. Để Slicer có thể đồng thời tác dụng lên PivotTable mới tạo, chúng ta cần kết nối các PivotTable vào cùng Slicer. Cách làm như sau:

Lưu ý:

Sau khi hoàn tất các bước, bạn chỉ cần lặp lại nếu muốn có thêm biểu đồ, thêm các thẻ… để làm phong phú thêm cho báo cáo.

Ví dụ về 1 kết quả hoàn thiện:

Các bạn có thể tải file mẫu tại địa chỉ: http://bit.ly/2VyQfeD

Như vậy qua Seri bài viết gồm 4 phần, chúng ta đã có thể nắm được toàn bộ kiến thức giúp xây dựng báo cáo quản trị, báo cáo phân tích dữ liệu. Bằng cách sử dụng các công cụ trong Excel như Power Query, Pivot Table, Pivot Chart, Slicer… chúng ta đã chuyển từ bảng dữ liệu rời rạc, khô cứng với dữ liệu số thành dạng báo cáo linh hoạt trên biểu đồ, có thể tự động thay đổi theo nội dung cần phân tích và tự động cập nhật dữ liệu mà không cần thay đổi phương pháp thu thập dữ liệu ban đầu.