Hướng dẫn cách tạo Dashboard với Excel Pivot Table (Có dataset thực hành)

marketing foundation

TM Data School – Khi nhắc đến xây dựng dashboard nhiều người thường nghĩ ngay đến PowerBI hay Tableau – những công cụ (BI Tools) chuyên dụng cho việc trực quan hóa dữ liệu. Tuy nhiên, bạn có biết người dùng hoàn toàn có thể tạo những “interactive dashboard” đơn giản để phục vụ những những nhu cầu phân tích dữ liệu cơ bản ngay trên Excel? 

Trong bài viết này, TM Data School sẽ hướng dẫn bạn từng bước để tạo ra một dashboard trên Excel với sự hỗ trợ của công cụ Pivot table nhé. 

Tổng quan về Pivot table trong Excel

Pivot Table là một công cụ trong Excel cho phép người dùng tổng hợp, phân tích, sắp xếp, và nhóm các tệp dữ liệu một cách nhanh chóng và dễ dàng. 

Với khả năng kéo và thả, bạn có thể di chuyển các cột và hàng để xem xét dữ liệu từ nhiều khía cạnh khác nhau, đơn giản hóa việc tạo ra các báo cáo, dashboard mà không cần sử dụng đến các hàm tính toán phức tạp.

Demo Dashboard trên Excel được tạo với Pivot Table – Bài làm cuối khóa của học viên khóa Data Visualization & Analytics with Excel  

Đọc thêm: Hướng dẫn sử dụng Pivot Table trong Google Sheet để phân tích dữ liệu nhanh chóng, hiệu quả hơn

3 bước tạo báo cáo tự động với Excel Pivot Table

Bước 1: Tải Dataset & Tạo Pivot Table

Dataset được cung cấp bởi MyOnlineTrainingHub. Bạn có thể truy cập vào đây để tải Dataset và thực hành cùng TM nhé!

dataset thực hành

Đây là tệp dữ liệu bán hàng của hai chuỗi cửa hàng thời trang có phạm vi hoạt động trên toàn nước Úc là Fashions Direct và Next Look. 

  • Dữ liệu được lưu trữ dưới dạng bảng trong Excel, bao gồm các thông tin được phân loại theo ngày, năm tài chính, chuỗi cửa hàng, địa điểm (khu vực/ bang/ mã bưu điện/ quốc gia), quản lý, danh mục sản phẩm và người mua sản phẩm.
  • Dữ liệu trong bảng này bao gồm thông tin từ tháng 1 năm 2016 đến tháng 6 năm 2017 với khoảng 68.000 dòng dữ liệu.
  • Ngoài ra trong trang tính có tên ‘Data Aug’, có thêm 4.500 dòng dữ liệu cho tháng 8 năm 2017.

Sau khi đã tải dữ liệu bạn tiến hành tạo Pivot table theo các bước sau: 

Tạo bảng 

Nhấn “CTRL + T”, hệ thống sẽ tự động chọn phạm vi dữ liệu được sử dụng để tạo bảng. Tick vào “My table has headers”, sau đó nhấn “OK” để tạo bảng.

tạo bảng mới

Và đây là kết quả sau khi tạo bảng: 

bảng mới được tạo

Bạn có thể đặt tên cho bảng là Raw_data để chỉ rõ đây là dữ liệu thô, giúp dễ dàng nhận biết và thao tác trong các bước tiếp theo.

Lưu ý: 

  • Bảng mới tạo cho phép bạn lọc, sắp xếp, và chọn dữ liệu theo nhu cầu giúp dễ dàng quản lý dữ liệu.
  • Khi bạn sao chép và dán bảng với cùng định dạng, bảng sẽ tự động cập nhật các thay đổi mới mà không cần thực hiện thêm bất kỳ bước nào khác.

Tạo Pivot Table

Truy cập vào “Insert” chọn PivotTable. Ở phần “Table/Range” hãy hãy chọn bảng Raw_data mà bạn vừa tạo. Chọn “New Worksheet” tạo Pivot Table trong một trang tính mới. 

Tạo Pivot Table

Sau khi Pivot Table được tạo, bạn có thể thấy danh sách các trường (Field List) ở phía bên phải màn hình. Nếu danh sách trường không hiển thị, hãy nhấp chuột vào PivotTable.  

Giao diện Pivot Table

Bạn có thể kéo và thả các trường dữ liệu vào các khu vực như Rows, Columns, Values và Filters để tùy chỉnh cách dữ liệu được tổng hợp và hiển thị trong biểu đồ của bạn. Trong phần tiếp theo TM Data School sẽ hướng dẫn bạn chi tiết cách kéo thả các trường để cách tạo các biểu đồ với Pivot Table nhé!

Bước 2: Tạo các biểu đồ riêng lẻ với Pivot Table

Trong phần này, bạn sẽ cần tạo ra các biểu đồ riêng lẻ để đưa vào dashboard. 

Vì mục tiêu của bài blog này là tạo ra một dashboard, TM Data School sẽ không đi quá sâu vào chi tiết cách tạo ra từng biểu đồ mà chỉ hướng dẫn biểu đồ cơ bản là Line Chart, và một biểu đồ phức tạp hơn là: Map. 

Với các biểu đồ khác TM sẽ cung cấp phần mô tả biểu đồ và outcome của biểu đồ. Dựa vào đó, bạn hoàn toàn có thể tạo ra các biểu đồ còn lại với Pivot Table. 

Bạn có thể tham khảo thêm về cách trực quan hóa dữ liệu với Pivot Table trong khóa Data Visualization & Analytics with Excel của TM Data School. 

trực quan hóa dữ liệu trên Excel Pivot Table

Demo cách trực quan hóa dữ liệu trên Excel Pivot Table để trả lời các câu hỏi kinh doanh – Slide khóa học Data Visualization & Analytics with Excel

Line chart (Sale by Chain & Time)

Biểu đồ đầu tiên là biểu đồ đường thể hiện doanh số bán hàng theo thời gian của 2 chuỗi cửa hàng là Fashions Direct và Next Look. 

Line chart (Sale by Chain & Time)

Để tạo biểu đồ, bạn hãy lần lượt làm theo các bước sau đây để thêm dữ liệu vào Pivot table: 

  • Trong Pivot Table, kéo trường “Year”“Month”  vào khu vực “Rows” để hiển thị dữ liệu theo trục thời gian.
  • Kéo trường “Chain” vào khu vực “Columns” để so sánh giữa các chuỗi cửa hàng khác nhau.
  • Kéo trường “Sales” vào khu vực “Values”. Pivot table sẽ giúp bạn tự động tính toán tổng doanh số cho mỗi chuỗi cửa hàng theo từng thời điểm.
Line Pivot Table

Lưu ý: Bạn nên đổi tên Pivot table để không bị lẫn lộn các bảng với nhau

Line Pivot Table

Sau khi đã có bảng dữ liệu được tính toán, chọn “PivotChart”, chọn loại chart mà mình muốn dùng để trực quan hóa dữ liệu, ở đây chúng ta sẽ chọn “Line” (biểu đồ đường), sau đó chọn “OK”

vẽ line chart

Nhấn chuột phải và chọn “Hide All Field Buttons on Chart” để ẩn bớt các nút bấm trên chart đi. 

vẽ line chart

Và đây là biểu đồ thể hiện doanh số bán hàng theo thời gian của 2 chuỗi cửa hàng là Fashions Direct và Next Look: 

vẽ line chart

Map (Sale by State)

Biểu đồ thể hiện doanh thu theo từng bang (State)

Map (Sale by State)

Để tạo biểu đồ, bạn hãy lần lượt làm theo các bước sau đây: 

  • Chọn trường dữ liệu bạn muốn hiển thị trên bản đồ: Ở đây chúng ta sẽ kéo “Sales” vào khu vực “Values”, kéo “Country” “State” vào khu vực “Rows”
vẽ biểu đồ Map với Pivot Table
  • Do không thể trực tiếp tạo biểu đồ bản đồ (Map Chart) từ Pivot Table, bạn cần phải sắp xếp dữ liệu về một định dạng bảng chuẩn trước. Nhấn chuột phải vào “Australia”, chọn “Field Settings”
vẽ biểu đồ Map với Pivot Table

Chọn “Layout & Print”, tiếp tục chọn “Show item labels” in tabular form” và “Repeat item labels”

vẽ biểu đồ Map với Pivot Table

Và đây là kết quả, copy giá trị của bảng sang bên cạnh sau đó vẽ chart: 

vẽ biểu đồ Map với Pivot Table

Lưu ý: Sau khi vẽ biểu đồ xong chúng ta sẽ cần linking data của map ngược lại về Pivot table thay vì bảng dữ liệu copy vừa được tạo. 

Để làm được điều này, chọn biểu đồ map sau đó nhấn chuột trái, chọn “Select Data”

vẽ biểu đồ Map với Pivot Table

Thay đổi giá trị trong “Chart data range” từ “Map!$E$3:$G$11” thành “Map!$A$3:$C$11”

vẽ biểu đồ Map với Pivot Table

Vậy là vùng dữ liệu của Map Chart đã được thay đổi qua Pivot table

vẽ biểu đồ Map với Pivot Table

Pie chart (Sale by Chain)

Biểu đồ tròn thể hiện tỷ lệ phân bổ doanh số theo từng chuỗi cửa hàng (Chain).

Pie chart (Sale by Chain)

Bar Chart (Sale by Category & Chain)

Biểu đồ cột thể hiện doanh số bán hàng theo từng danh mục sản phẩm (Category) của 2 chuỗi cửa hàng (Chain).

Bar Chart (Sale by Category & Chain)

Manager Plot (Sale by Manager & State)

Biểu đồ cột thể hiện doanh số từng chuỗi cửa hàng theo theo từng người quản lý (Manager) của các bang khác nhau (State).

Manager Plot (Sale by Manager & State)

Đọc thêm: Data Visualization – Cách chọn loại biểu đồ minh hoạ tốt nhất cho metrics của bạn

Bước 3: Tạo Dashboard

Sắp xếp các chart đã tạo vào cùng một sheet

Sau khi đã tạo xong các chart, TM sẽ cùng bạn tạo một sheet mới và lần lướt sắp xếp các chart vào để tạo thành Dashboard.

Sử dụng phím tắt “Ctr+X” để cắt các biểu đồ đã tạo từ trước, sau đó “Ctr+V” để paste vào sheet Dashboard mới. 

Sắp xếp các chart đã tạo vào cùng một sheet

Lưu ý: Việc sắp xếp các biểu đồ theo một trình tự logic và có ý nghĩa sẽ giúp người xem dễ dàng theo dõi từ tổng quát đến chi tiết hoặc theo một trình tự thời gian. Ví dụ, bạn có thể bắt đầu từ biểu đồ tổng quan về doanh thu, sau đó là phân tích theo khu vực, và cuối cùng là phân tích theo sản phẩm. Quan trọng là bạn cần biết cách sắp xếp các biểu đồ thành một “câu chuyện” rõ ràng để truyền tải được tốt nhất thông điệp của bài phân tích. Lúc này kỹ năng Data Storytelling sẽ đóng vai trò vô cùng quan trọng. 

5 bước kể chuyện bằng dữ liệu

5 bước kể chuyện bằng dữ liệu – Slide khóa học Data Visualization with Excel

Ngoài ra để có thêm ý tưởng về cách trình bày từng dashboard cho bộ phận, ngành hàng của mình bạn có thể tham khảo thêm các mẫu Dashboard có sẵn. Dưới đây là một số dashboard mẫu mà TM Data School đã tổng hợp lại: 

Tạo Slicer để filter dữ liệu của các chart

Slicer là một “công cụ” trong Excel cho phép lọc dữ liệu trong Pivot Table một cách nhanh chóng và dễ dàng. Điều này giúp cho Dashboard của bạn có tính “tương tác” hơn thay vì một file báo cáo tĩnh. 

Đọc thêm: Kết hợp hàm INDEX và MATCH trong Excel để dò nhiều điều kiện như thế nào?

Để tạo Slicer bạn chọn 1 biểu đồ bất kỳ, ở đây TM sẽ chọn biểu đồ đường, sau đó vào phần “PivotChart Analyze” và chọn “Insert Slicer”.  

Tạo Slicer để filter dữ liệu của các chart

Tại đây bạn sẽ có thể lựa chọn nhiều bộ lọc để cho vào báo cáo, ở đây chúng ta sẽ lựa chọn bộ lọc: Financial Year, State và Category

Tạo Slicer để filter dữ liệu của các chart

Sau khi đã tạo các bộ lọc, chúng ta sẽ sắp xếp vào bên trái của Dashboard. Vậy là chúng ta đã có một Dashboard tương đối hoàn chỉnh rồi. 

Tạo Slicer để filter dữ liệu của các chart

Tuy nhiên nếu bạn nhấn chuột thử vào các bộ lọc sẽ thấy các bộ lọc (Slicer) vừa được tạo ra chỉ đang filter cho một biểu đồ duy nhất là line chart mà mình đã chọn để tạo Slicer từ đầu, các biểu đồ khác hoàn toàn không được filter theo. 

Để khắc phục tình trạng này, chúng ta sẽ cần liên kết Slicer với các biểu đồ còn lại. 

Tạo Slicer để filter dữ liệu của các chart

Chọn Slicer Financial Year, nhấn chuột phải, chọn “Report Connections”

Tạo Slicer để filter dữ liệu của các chart

Tick chọn các Pivot table đã tạo để connect với Slicer.  

Tạo Slicer để filter dữ liệu của các chart

Với các Slicer khác bạn cũng liên kết tương tự. Vậy là bạn đã có thể filter tất cả các Chart theo mong muốn để tìm ra những insight từ dữ liệu 

kết quả dashboard

Đọc thêm: Hướng dẫn tạo Data Model và DAX measure trong Excel

Tạm kết

Qua những nội dung trên, TM Data School hy vọng bạn đã nắm được các bước cơ bản để tạo một dashboard cơ bản trên Excel với Pivot Table. Việc sử dụng Dashboard thay vì các bản báo cáo “khô khan toàn số” sẽ cung cấp một cái nhìn tổng quan và toàn diện, giúp bạn dễ dàng tìm ra được những insight hữu ích, nhận diện xu hướng, từ đó đưa ra quyết định đúng đắn và nhanh chóng hơn. 

Nếu bạn muốn tìm hiểu thêm về tư duy trực quan hóa dữ liệu, xây dựng báo cáo/ dashboard trên Excel, hãy tham khảo khóa học Data Visualization & Analytics with Excel của TM Data School. Khóa học sẽ cung cấp cho bạn: 

► Kỹ năng xử lý dữ liệu để xây dựng dashboard: Biết cách xác định các vấn đề với dữ liệu (data issue) và xử lý, làm sạch dữ liệu với Excel Power Query.

► Trực quan hóa dữ liệu một cách chuyên nghiệp: Biết cách tự xây dựng báo cáo, dashboard tự động (integrated dashboard) từ các file Excel riêng lẻ.

► Kỹ năng Data storytelling: Biết cách kể chuyện bằng dữ liệu để truyền tải thông tin từ dữ liệu một cách hấp dẫn, dễ hiểu thông qua dashboard.

► Khai phá các tool phân tích nâng cao của Excel: Biết cách sử dụng Excel Solver, Excel Toolpak để giải quyết những bài toán nâng cao như xác định xu hướng, dự báo kết quả, từ đó định hướng chiến lược kinh doanh.

Tham khảo chi tiết khóa học tại đây!

Tagged: