Xây dựng bảng thông tin Excel nâng cao với Power Query, Power Pivot &VBA – Từ dữ liệu đến thông tin chuyên sâu

Trong hướng dẫn này, chúng tôi sẽ trình bày cách xây dựng bảng thông tin Excel nâng cao bằng Power Query, Power Pivot và VBA.

Hãy tạo bảng thông tin bán hàng cho một công ty bán lẻ hư cấu bán sản phẩm trên nhiều khu vực. Chúng ta sẽ làm việc với tập dữ liệu chứa các bảng sau.

• Doanh số – Chứa dữ liệu giao dịch.
• Sản phẩm – Chi tiết và danh mục sản phẩm.
• Khách hàng – Thông tin khách hàng.
• Khu vực – Thông tin địa lý.

Bước 1:Sử dụng Power Query để nhập dữ liệu

Nhập dữ liệu:

• Đi tới Dữ liệu tab>> chọn Nhận dữ liệu>> chọn Từ tệp>> chọn Từ văn bản/CSV .
• Điều hướng đến sales_data.txt của bạn tệp> > nhấp vào Nhập .

• Khi Power Query Editor mở ra, hãy xem lại dữ liệu và thực hiện các chuyển đổi sau:
• Thay đổi loại dữ liệu.
• Nhấp chuột phải vào bất kỳ cột nào>> chọn Thay đổi kiểu dữ liệu>> chọn Loại dữ liệu .
  • Ngày đặt hàng tới Ngày .
  • Số lượng đến Số nguyên .
  • Đơn giá và Giảm giá thành Số thập phân .

• Xóa mọi hàng trùng lặp bằng cách sử dụng Xóa hàng trùng lặp .
• Lặp lại quy trình nhập cho Products.csv, Customer.csv và Dates.csv, áp dụng các chuyển đổi loại dữ liệu thích hợp.

Chuyển đổi dữ liệu bằng Power Query:

Hãy nâng cao dữ liệu bán hàng của chúng ta bằng cách thực hiện các tác vụ bằng Power Query.

Thêm cột được tính toán:

• Trong Power Query Editor, chọn Dữ liệu bán hàng.
• Đi tới Thêm cột tab>> chọn Cột tùy chỉnh .
• Đặt tên là:Doanh thu .
• Chèn công thức sau.

= [Quantity] * [UnitPrice] * (1-[DiscountRate])

• Nhấp vào OK .

• Thêm một cột tùy chỉnh khác.
• Đặt tên là:Lợi nhuận .
• Chèn công thức sau.

Profit = [Revenue] - ([Quantity] * [UnitCost])

• Nhấp vào OK .
• Chúng ta sẽ cần hợp nhất với bảng Sản phẩm để lấy Chi phí.

Hợp nhất các bảng để có thêm thông tin chi tiết:

• Trong Power Query Editor có dữ liệu Bán hàng đang mở.
• Truy cập Trang chủ tab>> nhấp vào Hợp nhất truy vấn từ dải băng.
  • Chọn ID sản phẩm từ bảng Bán hàng.
  • Chọn Sản phẩm bảng và tham gia trên ProductID .
  • Nhấp vào OK .

• Nhấp vào Mở rộng bảng tùy chọn>> chỉ chọn Chi phí đơn vị cột cần mang vào.
• Nhấp vào OK .

• Bây giờ, hãy kéo Lợi nhuận cột trong Sản phẩm mở rộng bước.
• Bạn sẽ nhận được số tiền lãi.

• Sau khi bạn hoàn tất việc chuyển đổi dữ liệu.
• Nhấp vào Đóng và tải vào…

• Trong phần Nhập dữ liệu hộp;
  • Chọn Chỉ tạo kết nối .
  • Chọn Thêm dữ liệu này vào Mô hình Dữ liệu cho cả bốn bảng.
  • Nhấp vào OK .

Bước 2:Xây dựng mô hình dữ liệu bằng Power Pivot

Mở Power Pivot:

Nếu Power Pivot không có sẵn trong ribbon thì hãy bật nó.

• Chuyển tới Tệp tab>> chọn Tùy chọn>> chọn Phần bổ trợ .
• Trong Quản lý hộp> > chọn Phần bổ trợ COM>> chọn Đi .

• Chọn Microsoft Power Pivot cho Excel .
• Nhấp vào OK .

• Đi tới Power Pivot tab>> chọn Quản lý .
• Nó sẽ mở dữ liệu đã nhập từ Power Query.

Tạo mối quan hệ :

• Truy cập Trang chủ tab>> chọn Chế độ xem sơ đồ .
  • Kéo Doanh số[ProductID] tới Sản phẩm[ProductID] .
  • Kéo Doanh số[CustomerID] tới Khách hàng[CustomerID] .
  • Kéo Khách hàng[RegionID] tới Vùng[RegionID] .

• Hoặc đi tới phần Thiết kế tab>> chọn Tạo mối quan hệ .
• Sau đó chọn các cột phù hợp.

Tạo các thước đo được tính toán:

• Trong Power Pivot, hãy nhấp vào bảng Bán hàng.
• Đi tới Trang chủ tab>> chọn Biện pháp >> nhấp vào Biện pháp mới .
• Hoặc truy cập Trang chủ tab>> chọn Diện tích tính toán .
• Chèn các số đo đã tính vào đó:

• Tổng doanh thu:

Total Revenue := SUM(Sales[Revenue])

• Tổng lợi nhuận:

Total Profit := SUM(Sales[Profit])

• Tỷ suất lợi nhuận:

Profit Margin := DIVIDE([Total Profit], [Total Revenue], 0)

• Tổng số đơn đặt hàng:

Total Orders:=COUNTA(Sales[OrderID])

• Giá trị đặt hàng trung bình:

Average Order Value:=DIVIDE([Total Revenue], DISTINCTCOUNT(Sales[OrderID]), 0)

• Doanh thu từ đầu năm đến nay:

YTD Revenue:=CALCULATE([Total Revenue], DATESYTD(Sales[OrderDate]))

• Doanh thu năm trước:

Previous Year Revenue:=CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Sales[OrderDate]))

• Tăng trưởng so với cùng kỳ:

YOY Growth := DIVIDE([Total Revenue] - [Previous Year Revenue], [Previous Year Revenue], 0)

Bước 3:Sử dụng Bảng tổng hợp để tạo thành phần trang tổng quan

Tạo PivotTable

• Đi tới Chèn tab>> chọn PivotTable >> chọn Từ mô hình dữ liệu .
• Trong Power Pivot, đi tới Trang chủ tab>> chọn Bảng tổng hợp .

• Trong phần Tạo PivotTable hộp;
  • Chọn bảng tính mới.
  • Nhấp vào OK .

• Tạo các PivotTable riêng biệt cho Tổng doanh thu, Tổng lợi nhuận, Tỷ suất lợi nhuận và mức tăng trưởng.
• Đặt chúng vào các ô phù hợp với cấu trúc trang tổng quan của bạn.
• Định dạng dưới dạng tiền tệ hoặc tỷ lệ phần trăm nếu thích hợp.

Biểu đồ và hình ảnh hóa

Biểu đồ xu hướng doanh thu:

• Tạo PivotTable từ Mô hình Dữ liệu.
  • Hàng:Doanh số[Ngày đặt hàng[Tháng]]
  • Giá trị: Tổng doanh thu
• Đi tới Phân tích PivotTable tab>> chọn Biểu đồ cột theo cụm .

Bảng xếp hạng sản phẩm hàng đầu:

• Tạo PivotTable từ Mô hình Dữ liệu.
  • Hàng:Sản phẩm[Tên sản phẩm]
  • Giá trị:Tổng doanh thu
• Sắp xếp theo Tổng doanh thu giảm dần, lọc vào top 10.

• Đi tới Phân tích PivotTable tab>> chọn Biểu đồ thanh theo cụm .

Hiệu suất danh mục :

• Tạo PivotTable từ Mô hình Dữ liệu.
  • Hàng:Sản phẩm[Danh mục]
  • Giá trị:Tổng doanh thu, Tổng lợi nhuận, Tỷ suất lợi nhuận .
• Đi tới Phân tích PivotTable tab>> chọn Kết hợp>> chọn Cột phân cụm – Dòng trên trục phụ .

Bước 4:Chèn các phần tử tương tác

Chèn bộ cắt và dòng thời gian:

Tạo bộ cắt sản phẩm và khu vực:

• Đi tới PivotAnalyze tab>> chọn Chèn Bộ cắt .
• Chọn Sản phẩm[Danh mục] và Khách hàng[Vùng] .
• Nhấp vào OK .

• Định dạng chúng để phù hợp với thiết kế trang tổng quan của bạn.

Thêm dòng thời gian để lọc ngày:

• Đi tới PivotAnalyze tab>> chọn Chèn Bộ cắt .
• Chọn Doanh số[OrderDate] .
• Nhấp vào OK .

• Vị trí phía trên biểu đồ của bạn.
• Định dạng phù hợp với kiểu trang tổng quan của bạn.

Kết nối tất cả các bộ cắt với PivotTable :

• Nhấp chuột phải vào từng bộ cắt>> chọn Báo cáo kết nối .

• Kiểm tra tất cả PivotTable để đảm bảo các bộ lọc được áp dụng trên toàn cầu.
• Nhấp vào OK .

Bước 5:Tự động hóa và tăng cường khả năng tương tác với VBA

Chúng tôi sẽ sử dụng VBA để làm cho trang tổng quan trở nên linh hoạt và dễ điều hướng hơn.

Ví dụ 1:Nút làm mới dữ liệu

• Đi tới Nhà phát triển tab>> chọn Chèn >> chọn Nút .
• Đổi tên nút thành Làm mới dữ liệu .
• Nhấp chuột phải>> chọn Gán Macro>> chọn Mới .

• Sao chép-dán đoạn mã sau.

Mã VBA:

Sub RefreshDashboard()
 ThisWorkbook.RefreshAll
 MsgBox "Dashboard data refreshed!", vbInformation
End Sub

Ví dụ 2:Nút đặt lại trang tổng quan

• Đi tới Nhà phát triển tab>> chọn Chèn >> chọn Nút .
• Đổi tên nút thành Đặt lại trang tổng quan .
• Nhấp chuột phải>> chọn Gán Macro>> chọn Mới .
• Sao chép-dán đoạn mã sau.

Mã VBA:

Sub ResetDashboardFilter()
 Dim ws As Worksheet
 Dim slicer As slicerCache
 Dim pivotTable As pivotTable
 
 ' Clear all slicer caches
 For Each slicer In ActiveWorkbook.SlicerCaches
 slicer.ClearAllFilters
 Next slicer
 
 ' Reset any timelines (already using SlicerCaches)
 For Each slicer In ActiveWorkbook.SlicerCaches
 If slicer.SourceType = xlTimeline Then
 slicer.ClearAllFilters
 End If
 Next slicer
 
 ' Refresh pivot tables
 For Each ws In ActiveWorkbook.Worksheets
 For Each pivotTable In ws.PivotTables
 pivotTable.RefreshTable
 Next pivotTable
 Next ws
 
 MsgBox "Dashboard filters have been reset!", vbInformation, "Reset Filters"
End Sub

Bước 6:Tạo bố cục trang tổng quan

Tạo một bảng tính mới có tên Trang tổng quan.

• Thiết lập cấu trúc trang tổng quan:
  • Tiêu đề trang tổng quan và bộ lọc ngày.
  • Phần KPI (Doanh thu, Lợi nhuận, Tỷ suất lợi nhuận, Tăng trưởng).
  • Biểu đồ (Xu hướng bán hàng, Sản phẩm hàng đầu, Hiệu suất khu vực).

• Bạn có thể tạo bảng tóm tắt cho các bảng Dữ liệu và phân tích chi tiết.

• Áp dụng định dạng nhất quán :
  • Sử dụng bảng màu nhất quán xuyên suốt.
  • Căn chỉnh tất cả các phần tử cho hợp lý.
  • Thêm đường viền vào các phần trang tổng quan riêng biệt.

• Thêm định dạng có điều kiện vào bảng dữ liệu:
  • Sử dụng thanh dữ liệu và thang màu để làm nổi bật các giá trị quan trọng.
  • Thêm biểu tượng KPI để hiển thị hiệu suất so với mục tiêu.
• Tạo bảng hướng dẫn:
  • Tạo trang tính mới có tên Hướng dẫn.
  • Thêm văn bản giải thích cách sử dụng trang tổng quan.
  • Bao gồm thông tin về làm mới dữ liệu, tính tương tác và các tính năng có sẵn.

Bước 7:Kiểm tra và khắc phục sự cố

Kiểm tra tất cả các yếu tố tương tác:

• Đảm bảo bộ cắt lọc tất cả các hình ảnh trực quan có liên quan.
• Xác minh rằng các nút thực thi macro chính xác.
• Kiểm tra các điều khiển dòng thời gian để đảm bảo phạm vi dữ liệu chính xác.
  • Chọn Vẻ đẹp từ Danh mục máy thái.
  • Chọn Tháng 1 đến tháng 4 năm 2024 từ Dòng thời gian.
• Thao tác này sẽ cập nhật toàn bộ trang tổng quan dựa trên lựa chọn bộ lọc.

Kiểm tra bộ lọc dữ liệu đặt lại:

• Nhấp vào Đặt lại trang tổng quan .
• Một thông báo sẽ bật lên, “Bộ lọc bảng điều khiển đã được đặt lại” .
• Nhấp vào OK .
• Tất cả bộ lọc sẽ bị xóa và bạn sẽ có trang tổng quan mới.

Làm mới dữ liệu thử nghiệm :

• Sửa đổi dữ liệu nguồn.
• Nhấp vào Làm mới dữ liệu .
• Đảm bảo tất cả các tính toán cập nhật chính xác.
• Kiểm tra mọi kết nối hoặc công thức bị hỏng.

Tải xuống sổ tay thực hành

Kết luận

Làm theo tất cả các bước, bạn có thể xây dựng bảng điều khiển Excel nâng cao sẽ hoạt động như một công cụ thông minh kinh doanh mạnh mẽ. Bảng thông tin này tận dụng sức mạnh của Power Query để chuẩn bị dữ liệu, Power Pivot để lập mô hình và phân tích cũng như VBA để nâng cao khả năng tương tác. Trang tổng quan này cung cấp giao diện thân thiện với người dùng để phân tích hiệu suất bán hàng trên các sản phẩm, khu vực và khoảng thời gian. Hãy thử nghiệm trang tổng quan và thêm các chức năng nâng cao hơn.