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.

Phần 4: Xây dựng báo cáo phân tích dữ liệu

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.

Đặc điểm của báo cáo phân tích dữ liệu

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:

• Tư duy bao quát: Nắm được số liệu tổng của các chỉ tiêu, nhóm chỉ tiêu. Ví dụ: tổng của toàn bộ, tổng của 1 nhóm, tỷ lệ của 1 đối tượng trong tổng số, hoặc tình hình tăng/giảm của 1 chỉ tiêu so với kỳ trước…
• Tư duy phân tích: vẽ biểu đồ, phân tích các chỉ tiêu trên biểu đồ. Bởi biểu đồ sẽ cung cấp cái nhìn trực quan nhất về các nội dung. Thay vì nhìn vào 1 bảng gồm quá nhiều các chỉ tiêu và con số, việc biểu diễn trên biểu đồ sẽ dễ nhìn hơn rất nhiều. Thêm vào đó biểu đồ còn giúp dễ dàng nhận ra sự chênh lệch, xu hướng biến động của các nội dung được biểu diễn.
• Ra quyết định: dựa trên những điều phân tích được có thể đưa ra quyết định. Bởi mục đích cuối cùng của việc phân tích chính là chỉ ra được mục tiêu để hành động.

Cách lập báo cáo phân tích dữ liệu trên Excel

Để đả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à:

• Bảng báo cáo gồm nhiều bảng / biểu đồ trên cùng 1 màn hình để có thể có nhiều góc nhìn, nhiều chiều phân tích
• Các bảng / biểu đồ cần liên kết với nhau thống nhất theo cùng 1 hướng cần phân tích (nếu các bảng / biểu đồ độc lập với nhau thì có thể không cần liên kết này)

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

Hướng dẫn chi tiết cách lập báo cáo phân tích năng suất lao động trên Excel

Bước 1: Xác định cấu trúc báo cáo

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:

• Trong phạm vi được xác định, tốt nhất là trong đúng 1 khung màn hình. Bởi việc vượt quá khung hình sẽ dẫn tới việc phải di chuyển qua lại để xem báo cáo. Khi đó sẽ làm giảm sự tập trung hoặc khó theo dõi toàn bộ báo cáo.
• Có sự phân cấp theo mức độ chi tiết để phù hợp với quá trình phân tích báo cáo. Nên phân cấp theo chiều từ trên > dưới, trái > phải.
• Trong cùng 1 nhóm nội dung thì các nội dung cần được thống nhất về cách trình bày, kích cỡ, màu sắc, loại biểu đồ… để đảm bảo tính thống nhất cho nội dung cần thể hiện.

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.

Bước 2: Xác định các nội dung cần phân tích, biểu diễn trên báo cáo

Đặ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:

• Những cột chứa nội dung theo dạng danh sách (các giá trị lặp lại trong phạm vi 1 danh sách nhất định, thường là ngắn, dưới 8 dòng) thì có thể dùng làm bộ lọc (Slicer) cho báo cáo hoặc dùng cho biểu đồ dạng nhóm, tỷ lệ nhóm. Ví dụ như cột Dự án, Khu vực
• Những cột chứa giá trị số (số lao động, giá trị, năng suất lao động) thì có thể dùng để biểu diễn các chỉ tiêu dạng số và là giá trị để biểu diễn trên biểu đồ.
• Những cột chứa nội dung không trùng lặp hoặc theo danh sách dài (chi nhánh) thì thường dùng để biểu diễn các đối tượng trên biểu đồ dạng cột, bar, line

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:

• Năng suất là 1 giá trị số, có thể định lượng được. Do đó có thể tìm được số lớn nhất, số nhỏ nhất, số trung bình. Ngoài ra năng suất liên quan tới từng chi nhánh, từng dự án, từng khu vực => xác định năng suất theo các đối tượng này.
• Liên quan tới thời gian: Tháng, Năm: chúng ta có thể dùng để tính lũy tiến theo thời gian để so sánh năng suất giữa các năm.
• Năng suất có được là do Giá trị / Số lao động. Do đó chúng ta có thể biểu diễn thêm các biểu đồ về giá trị, số lao động để so sánh, đối chiếu thêm với năng suất.

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ước 3: Tạo Pivot Table từ bảng dữ liệu

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:

• Pivot Table là dạng báo cáo động, dữ liệu của báo cáo này được lấy từ bảng Query1. Mỗi Pivot Table sẽ thể hiện 1 nội dung, do đó cần nhiều Pivot Table. Tất cả các Pivot Table đều lấy chung từ 1 nguồn để đảm bảo tính thống nhất và kết nối được các Pivot Table với nhau.
• Khi bảng Query1 được cập nhật (bằng refresh) thì toàn bộ các báo cáo Pivot Table cũng sẽ được cập nhật theo vì cùng chung 1 nguồn.

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.

Bước 4: Tạo các Slicer để phân tích dữ liệu

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

Bước 5: Tạo nội dung cho các thẻ

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

• Tạo PivotTable 1
• Trong PivotTable 1 bạn đưa chỉ tiêu Giá trị vào vị trí Value, kết quả là:

• Giá trị này sẽ thay đổi khi bạn bấm chọn các nội dung trong Slicer

• Để tạo ra 1 giá trị không đổi, các bạn có thể đặt công thức tính giá trị đó (ví dụ đặt tại ô A7):

=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)

• Tính tỷ lệ % giữa giá trị của PivotTable1 với tổng toàn bộ Giá trị:

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

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

• Cuối cùng, đưa các kết quả đã tính được vào trong thẻ tại báo cáo:

Để đư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.

Bước 6: Vẽ biểu đồ bằng Pivot Chart từ Pivot Table

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

• Tạo PivotTable mới. Mỗi PivotTable ứng với 1 biểu đồ. Các PivotTable này đều tạo từ bảng Query1.
• Xác định nội dung cần biểu diễn. Ví dụ: Top 10 chi nhánh có năng suất cao nhất. Khi đó giá trị cần biểu diễn trong PivotTable sẽ là:

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ấm vào PivotTable > Trên thanh công cụ xuất hiện thẻ dành riêng cho PivotTable
• Bấm chọn PivotTable Analyze (hay PivotTable Options), chọn Pivot Chart
• Chọn loại biểu đồ phù hợp (ví dụ biểu đồ cột đứng)
• Co dãn kích thước biểu đồ cho phù hợp với khung đã được thiết kế sẵn
• Khi vẽ ra biểu đồ, bạn có thể bỏ bớt các nội dung không cần thiết để chỉ tập trung vào nội dung biểu đồ:

+ 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)

Bước 7: Kết nối các PivotTable vào cùng Slicer

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:

• Bấm chuột phải vào Slicer, chọn Report Connections…

• Chọn các PivotTable sẽ được kết nối:

Lưu ý:

• Nếu có nhiều Slicer thì mỗi Slicer đều phải tạo kết nối với các PivotTable.
• Có thể lựa chọn PivotTable nào sẽ bị ảnh hưởng từ việc lọc của Slicer hay không bằng việc kết nối này.

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.