Các bảng điều khiển Excel hiện đại đã tiến hóa xa hơn so với các biểu đồ và bảng đơn giản. Bằng cách kết hợp Power Query để biến đổi dữ liệu, Power Pivot để mô hình hóa và phân tích dữ liệu nâng cao, và VBA để tự động hóa và tăng cường tương tác, bạn có thể tạo các bảng điều khiển chuyên nghiệp.
Trong hướng dẫn này, chúng tôi sẽ chỉ cách xây dựng bảng điều khiển Excel nâng cao sử dụng Power Query, Power Pivot và VBA.
Hãy tạo một bảng điều khiển bán hàng cho một công ty bán lẻ giả định 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.
- Sales (Bán hàng) – Chứa dữ liệu giao dịch.
- Products (Sản phẩm) – Chi tiết sản phẩm và danh mục.
- Customers (Khách hàng) – Thông tin khách hàng.
- Regions (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:
- Chuyển đến tab Data >> chọn Get Data >> chọn From File >> chọn From Text/CSV.
- Điều hướng đến tệp sales_data.txt của bạn >> nhấp Import.
- Khi Power Query Editor mở ra, xem lại dữ liệu và thực hiện các biến đổi sau:
- Thay đổi kiểu dữ liệu.
- Nhấp chuột phải vào bất kỳ cột nào >> chọn Change Data Type >> chọn Data Type.
- OrderDate thành Date.
- Quantity thành Whole Number.
- UnitPrice và Discount thành Decimal Number.
- Xóa bất kỳ hàng trùng lặp nào bằng Remove Duplicates.
- Lặp lại quy trình nhập cho Products.csv, Customers.csv và Dates.csv, áp dụng các biến đổi kiểu dữ liệu phù hợp.
Biến Đổi Dữ Liệu Với Power Query:
Hãy cải tiến 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ụ sử dụng Power Query.
Thêm Cột Tính Toán:
- Trong Power Query Editor, chọn dữ liệu Sales.
- Chuyển đến tab Add Column >> chọn Custom Column.
- Đặt tên: Revenue (Doanh thu).
- Chèn công thức sau.
= [Quantity] * [UnitPrice] * (1-[DiscountRate])
- Nhấp OK.
- Thêm một cột tùy chỉnh khác.
- Đặt tên: Profit (Lợi nhuận).
- Chèn công thức sau.
Profit = [Revenue] - ([Quantity] * [UnitCost])
- Nhấp OK.
- Chúng ta sẽ cần hợp nhất với bảng Products để lấy Cost.
Hợp Nhất Bảng Để Có Thêm Thông Tin Chi Tiết:
- Trong Power Query Editor với dữ liệu Sales mở.
- Chuyển đến tab Home >> nhấp Merge Queries từ ribbon.
- Chọn ProductID từ bảng Sales.
- Chọn bảng Products và nối trên ProductID.
- Nhấp OK.
- Nhấp vào tùy chọn Expand Table >> chỉ chọn cột UnitCost để đưa vào.
- Nhấp OK.
- Bây giờ, kéo cột Profit xuống dưới bước Expanded Products.
- Bạn sẽ nhận được số tiền lợi nhuận.
- Khi bạn hoàn tất biến đổi dữ liệu.
- Nhấp Close & Load To…
- Trong hộp Import Data;
- Chọn Only Create Connection.
- Chọn Add this data to Data Model cho cả bốn bảng.
- Nhấp OK.
Bước 2: Xây Dựng Mô Hình Dữ Liệu Với Power Pivot
Mở Power Pivot:
Nếu Power Pivot không có sẵn trong ribbon, hãy bật nó.
- Chuyển đến tab File >> chọn Options >> chọn Add-ins.
- Trong hộp Manage >> chọn COM Add-ins >> chọn Go.
- Chọn Microsoft Power Pivot for Excel.
- Nhấp OK.
- Chuyển đến tab Power Pivot >> chọn Manage.
- Nó sẽ mở dữ liệu đã nhập từ Power Query.
Tạo Mối Quan Hệ:
- Chuyển đến tab Home >> chọn Diagram View.
- Kéo Sales[ProductID] đến Products[ProductID].
- Kéo Sales[CustomerID] đến Customers[CustomerID].
- Kéo Customers[RegionID] đến Regions[RegionID].
- Hoặc chuyển đến tab Design >> chọn Create Relationship.
- Sau đó chọn các cột khớp.
Tạo Các Measure Tính Toán:
- Trong Power Pivot, nhấp vào bảng Sales.
- Chuyển đến tab Home >> chọn Measures >> nhấp New Measure.
- Hoặc, chuyển đến tab Home >> chọn Calculation Area.
- Chèn các measure tính toán ở đó:
- Total Revenue (Tổng Doanh Thu):
Total Revenue := SUM(Sales[Revenue])
- Total Profit (Tổng Lợi Nhuận):
Total Profit := SUM(Sales[Profit])
- Profit Margin (Biên Lợi Nhuận):
Profit Margin := DIVIDE([Total Profit], [Total Revenue], 0)
- Total Orders (Tổng Đơn Hàng):
Total Orders:=COUNTA(Sales[OrderID])
- Average Order Value (Giá Trị Trung Bình Đơn Hàng):
Average Order Value:=DIVIDE([Total Revenue], DISTINCTCOUNT(Sales[OrderID]), 0)
- YTD Revenue (Doanh Thu Năm Đến Nay):
YTD Revenue:=CALCULATE([Total Revenue], DATESYTD(Sales[OrderDate]))
- Previous Year Revenue (Doanh Thu Năm Trước):
Previous Year Revenue:=CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Sales[OrderDate]))
- YOY Growth (Tăng Trưởng Năm Trên Năm):
YOY Growth := DIVIDE([Total Revenue] - [Previous Year Revenue], [Previous Year Revenue], 0)
Bước 3: Sử Dụng Pivot Table Để Tạo Các Thành Phần Bảng Điều Khiển
Tạo PivotTables
- Chuyển đến tab Insert >> chọn PivotTable >> chọn From Data Model.
- Trong Power Pivot, chuyển đến tab Home >> chọn Pivot Table.
- Trong hộp Create PivotTable;
- Chọn New Worksheet.
- Nhấp OK.
- Tạo các PivotTables riêng biệt cho Total Revenue, Total Profit, Profit Margin và Growth.
- Đặt chúng vào các ô phù hợp với cấu trúc bảng điều khiển của bạn.
- Định dạng thành tiền tệ hoặc phần trăm tùy ý.
Biểu Đồ Và Hình Ảnh Hóa
Biểu Đồ Xu Hướng Doanh Thu:
- Tạo một PivotTable từ Data Model.
- Rows: Sales[OrderDate[Month]]
- Values: Total Revenue
- Chuyển đến tab PivotTable Analyze >> chọn Clustered Column Chart.
Biểu Đồ Sản Phẩm Hàng Đầu:
- Tạo một PivotTable từ Data Model.
- Rows: Products[ProductName]
- Values: Total Revenue
- Sắp xếp theo Total Revenue giảm dần, lọc top 10.
- Chuyển đến tab PivotTable Analyze >> chọn Clustered Bar Chart.
Hiệu Suất Danh Mục:
- Tạo một PivotTable từ Data Model.
- Rows: Products[Category]
- Values: Total Revenue, Total Profit, Profit Margin.
- Chuyển đến tab PivotTable Analyze >> chọn Combo >> chọn Clustered Column – Line on Secondary Axis.
Bước 4: Chèn Các Yếu Tố Tương Tác
Chèn Slicers Và Timeline:
Tạo Slicer Sản Phẩm Và Khu Vực:
- Chuyển đến tab PivotAnalyze >> chọn Insert Slicer.
- Chọn Products[Category] và Customers[Region].
- Nhấp OK.
- Định dạng chúng để phù hợp với thiết kế bảng điều khiển của bạn.
Thêm Timeline Cho Lọc Ngày:
- Chuyển đến tab PivotAnalyze >> chọn Insert Slicer.
- Chọn Sales[OrderDate].
- Nhấp OK.
- Đặt vị trí trên biểu đồ của bạn.
- Định dạng để khớp với phong cách bảng điều khiển.
Kết Nối Tất Cả Slicers Đến PivotTables:
- Nhấp chuột phải vào từng slicer >> chọn Report Connections.
- Chọn tất cả PivotTables để đảm bảo bộ lọc áp dụng toàn cục.
- Nhấp OK.
Bước 5: Tự Động Hóa Và Nâng Cao Tương Tác Với VBA
Chúng ta sẽ sử dụng VBA để làm cho bảng điều khiển động và dễ điều hướng hơn.
Ví Dụ 1: Nút Làm Mới Dữ Liệu
- Chuyển đến tab Developer >> chọn Insert >> chọn Button.
- Đổi tên nút thành Refresh Data.
- Nhấp chuột phải nút >> chọn Assign Macro >> chọn New.
- Sao chép-dán mã sau.
Mã VBA:
Sub RefreshDashboard() ThisWorkbook.RefreshAll MsgBox "Dashboard data refreshed!", vbInformation End Sub
Ví Dụ 2: Nút Đặt Lại Bảng Điều Khiển
- Chuyển đến tab Developer >> chọn Insert >> chọn Button.
- Đổi tên nút thành Reset Dashboard.
- Nhấp chuột phải nút >> chọn Assign Macro >> chọn New.
- Sao chép-dán mã sau.
Mã VBA:
Sub ResetDashboardFilter()
Dim ws As Worksheet
Dim slicer As SlicerCache
Dim pivotTable As PivotTable
' Xóa tất cả bộ lọc slicer
For Each slicer In ActiveWorkbook.SlicerCaches
slicer.ClearAllFilters
Next slicer
' Đặt lại các timeline (đã dùng SlicerCaches)
For Each slicer In ActiveWorkbook.SlicerCaches
If slicer.SourceType = xlTimeline Then
slicer.ClearAllFilters
End If
Next slicer
' Làm mới 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 Bảng Điều Khiển
Tạo một worksheet mới tên là Dashboard.
- Thiết Lập Cấu Trúc Bảng Điều Khiển:
- Tiêu đề bảng điều khiển và bộ lọc ngày.
- Phần KPI (Doanh thu, Lợi nhuận, Biê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 cấu hình màu nhất quán xuyên suốt.
- Căn chỉnh tất cả các yếu tố đúng cách.
- Thêm viền để tách các phần bảng điều khiển.
- Thêm Định Dạng Có Điều Kiện Cho Bảng Dữ Liệu:
- Sử dụng thanh dữ liệu và thang màu để làm nổi bật 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 Trang Hướng Dẫn:
- Tạo worksheet mới tên Instructions.
- Thêm văn bản giải thích cách sử dụng bảng điều khiển.
- Bao gồm thông tin về làm mới dữ liệu, 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ả Yếu Tố Tương Tác:
- Đảm bảo slicers lọc tất cả hình ảnh hóa liên quan.
- Xác minh nút thực thi macro đúng cách.
- Kiểm tra điều khiển timeline đảm bảo phạm vi dữ liệu đúng.
- Chọn Beauty từ slicer Category.
- Chọn Jan-April 2024 từ Timeline.
- Điều này sẽ cập nhật toàn bộ bảng điều khiển dựa trên lựa chọn lọc.
Kiểm Tra Đặt Lại Bộ Lọc Dữ Liệu:
- Nhấp vào Reset Dashboard.
- Thông báo sẽ hiện: "Dashboard filters have been reset".
- Nhấp OK.
- Tất cả bộ lọc sẽ bị xóa và bạn nhận được bảng điều khiển mới.
Kiểm Tra Làm Mới Dữ Liệu:
- Sửa đổi dữ liệu nguồn.
- Nhấp Refresh Data.
- Đảm bảo tất cả tính toán cập nhật đúng.
- Kiểm tra kết nối hỏng hoặc công thức lỗi.
Tải Xuống Sổ Làm Việc Thực Hành
Kết Luận
Bằng cách làm theo tất cả các bước, bạn có thể xây dựng một bảng điều khiển Excel nâng cao hoạt động như một công cụ business intelligence mạnh mẽ. Bảng điều khiển này tận dụng sức mạnh của Power Query để chuẩn bị dữ liệu, Power Pivot cho mô hình hóa và phân tích, và VBA để tăng cường tương tác. Bảng điều khiển 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 sản phẩm, khu vực và khoảng thời gian. Hãy thử nghiệm với bảng điều khiển và thêm nhiều chức năng nâng cao hơn.
Nhận Bài Tập Excel Nâng Cao Miễn Phí Kèm Giải Pháp!