Excel Power Pivot: Hướng dẫn từng bước

Phân tích các tệp Excel lớn thường dẫn đến hiệu năng ì ạch. 

Power Pivot mang đến một cách tiếp cận khác. Nó kết nối các bảng và xử lý phép tính mà không làm giảm hiệu năng. Thay vì vật lộn với chuỗi VLOOKUP() và các cột phụ trợ, bạn làm việc với một hệ thống có cấu trúc được tích hợp trực tiếp trong Excel.

Trong hướng dẫn này, bạn sẽ học cách thiết lập mô hình dữ liệu, tạo quan hệ giữa bảng, viết công thức DAX và xây dựng báo cáo tương tác bằng Power Pivot.

Power Pivot là gì và vì sao hữu ích?

Power Pivot là công cụ dựng mô hình dữ liệu tích hợp sẵn của Excel. Nó cho phép bạn nạp các tập dữ liệu lớn hơn, kết nối nhiều bảng và chạy các phép tính phức tạp mà không bị chậm như trên trang tính truyền thống.

Power Pivot khác gì

Thay vì lưu dữ liệu trực tiếp trong một sheet, Power Pivot tải mọi thứ vào mô hình dữ liệu nội bộ của Excel. 

Một trang tính tiêu chuẩn có thể đạt khoảng một triệu hàng và thường chậm trước khi chạm ngưỡng đó. Power Pivot bỏ qua giới hạn này bằng cách nén dữ liệu và quản lý riêng, giúp bạn làm việc với hàng chục triệu hàng mà vẫn giữ hiệu năng của sổ làm việc.

Cấu trúc quan hệ thay cho chuỗi VLOOKUP

Khi dữ liệu đã ở trong mô hình, bạn có thể liên kết các bảng bằng khóa, giống như một cơ sở dữ liệu gọn nhẹ. Bạn không phải dẹp phẳng mọi thứ vào một sheet khổng lồ và dùng VLOOKUP() lồng nhau để ghép bảng bằng mọi giá. Power Pivot cho phép bạn phân tích các bảng được liên kết cạnh nhau một cách sạch sẽ và tin cậy.

Phép tính mạnh hơn với DAX

Power Pivot sử dụng DAX (Data Analysis Expressions), một ngôn ngữ công thức được xây dựng riêng cho phân tích. Bạn có thể dùng nó để tạo measure vượt xa khả năng của PivotTable tiêu chuẩn, từ tổng đơn giản đến chỉ số theo thời gian, tỷ lệ, cửa sổ cuốn và nhiều phép tính nâng cao khác.

Tình huống ví dụ

Dưới đây là hai ví dụ về cách doanh nghiệp dùng Power Pivot trong vận hành: 

• Theo dõi hiệu suất bán hàng: Kết hợp lịch sử đơn hàng, bảng sản phẩm và thuộc tính khách hàng, rồi xây dựng measure DAX cho doanh thu theo năm hoặc giá trị vòng đời khách hàng mà không cần ghép thủ công.
• Báo cáo vận hành: Liên kết dữ liệu tồn kho, vận chuyển và nhà cung cấp, rồi tính tỷ lệ lấp đầy, thời gian dẫn hoặc sai lệch dự báo ngay từ cùng một mô hình.

Nói ngắn gọn, Power Pivot mang lại trải nghiệm kiểu cơ sở dữ liệu ngay trong Excel. Nếu bạn làm việc với dữ liệu lớn hoặc nhiều bảng, nó có thể biến quy trình báo cáo lộn xộn thành các mô hình nhanh, mở rộng được để bạn tiếp tục phát triển.

Thiết lập Power Pivot trong Excel

Giờ hãy xem cách bạn có thể bắt đầu dùng Power Pivot trong Excel. 

Bật Power Pivot

Bạn không cần tải Power Pivot. Nó đã có sẵn trong Excel. Để bật:

1. Mở trang tính Excel 
2. Nhấp vào Files trên ribbon
3. Chọn Options > Add-ins 
4. Sau đó chọn COM Add-ins từ menu thả xuống và nhấp Go
5. Một cửa sổ bật lên sẽ hiện ra. Từ đây, chọn Microsoft Power Pivot for Excel, rồi nhấp OK

Giờ thì Power Pivot sẽ xuất hiện trên ribbon của bạn.

Bật tiện ích bổ sung Power Pivot trong Excel. Ảnh: Tác giả.

Lưu ý: Power Pivot chỉ hoạt động trong Excel Professional Plus hoặc Microsoft 365. Nếu bạn không thấy tab sau khi bật, có thể phiên bản Excel trên máy bạn không bao gồm tính năng này.

Nhập dữ liệu từ nhiều nguồn

Giờ bạn có thể nhập dữ liệu từ các nguồn khác nhau, như tệp Excel, tệp CSV hoặc thậm chí cơ sở dữ liệu SQL Server. 

Trong ví dụ này, chúng ta có hai tập dữ liệu trong tệp .xlsb:

1. sales.xlsb 

2. customer.xlsb 

Để nhập chúng vào Power Pivot:

1. Nhấp vào tab Power Pivot và chọn Manage. Một cửa sổ mới sẽ mở
2. Vào Home, nhấp Get External Data và chọn From Other Sources
3. Cuộn xuống và nhấp Excel File

Lấy dữ liệu từ các nguồn khác. Ảnh: Tác giả.

4. Giờ, trong cửa sổ bật lên, nhấp Browse và chọn tệp customer.xlsb 

5. Đánh dấu vào ô Use first row as column header và nhấp Next

Nhập tệp Excel vào Power Pivot. Ảnh: Tác giả.

Ở cửa sổ tiếp theo, nhấp Preview & Filter để xem dữ liệu trông như thế nào trước khi nhập. Khi hài lòng, nhấp OK, và hệ thống sẽ hiển thị mọi hàng đã được chuyển thành công. Sau đó nhấp Close.

Xem trước dữ liệu đã chọn. Ảnh: Tác giả. 

Lặp lại quy trình tương tự với tệp sales.xlsb. Sau đó, ở cuối màn hình, cả hai tệp sẽ hiển thị là đã nhập. Nhấp đúp và đổi tên. 

Cả hai tệp đã được nhập. Ảnh: Tác giả.

Xây dựng quan hệ và mô hình dữ liệu

Giờ khi dữ liệu đã được nạp vào Power Pivot, đến lúc liên kết các bảng để Excel hiểu cách chúng kết nối. Bước này là nền tảng cho mọi báo cáo của bạn.

Tạo quan hệ giữa các bảng

Để tạo quan hệ giữa bảng Sales và Customers :

1. Trong tab Home nhấp Diagram View. Bạn sẽ thấy cả hai bảng đã nhập ở đó
2. Nhấp vào CustomerID trong bảng Sales
3. Kéo nó sang CustomerID trong bảng Customer để tạo quan hệ giữa hai bảng

Lưu ý: Nếu muốn chỉnh sửa quan hệ, nhấp chuột phải vào đường nối và chọn Edit Relationship... Trong cửa sổ, chọn các cột bạn muốn dùng để tạo quan hệ.

Tạo quan hệ giữa các bảng. Ảnh: Tác giả.

Trong quan hệ này, một khách hàng có thể xuất hiện nhiều lần trong bảng Sales, nhưng mỗi khách hàng chỉ xuất hiện một lần trong bảng Customers. Đây là quan hệ một-nhiều đơn giản cho phép chúng ta dùng trường từ cả hai bảng trong PivotTable và tính toán mà không cần công thức dò tìm.

Thiết kế theo mô hình sao (star schema)

Star schema là một trong những cách đơn giản nhất để cấu trúc mô hình Power Pivot. Nó giúp sắp xếp bảng gọn gàng và khiến phép tính dự đoán được.

Đầu tiên, bạn phải chọn bảng fact. Trong trường hợp này, Sales là bảng fact vì nó chứa bản ghi giao dịch: ngày, khách hàng, sản phẩm, số lượng và số tiền.

Tiếp theo, xác định các bảng chiều (dimension) mô tả dữ liệu trong Sales. Một số ví dụ phổ biến gồm:

• Customers (khóa chính: CustomerID)
• Products (khóa chính: ProductID)
• Regions (khóa chính: RegionID)

Mỗi bảng chiều có một khóa chính. Bạn kết nối khóa đó với khóa ngoại tương ứng trong bảng fact:

• Customers.CustomerID → Sales.CustomerID
• Products.ProductID → Sales.ProductID
• Regions.RegionID → Customers.RegionID

Sau khi liên kết, bảng Sales nằm ở trung tâm với các bảng chiều tỏa ra xung quanh. Đó chính là “ngôi sao”. Cấu trúc này giúp mô hình rõ ràng, tăng tốc tính toán và cải thiện tính nhất quán trong báo cáo.

Tạo star schema. Ảnh: Tác giả.

Thêm cột tính toán

Khi đã có các quan hệ, bạn có thể tạo trường mới trực tiếp trong mô hình dữ liệu.

1. Chuyển sang Data View

2. Chọn ô trống Add Column ở cuối bảng.

3. Nhập = [TotalAmount] / [Qty] và nhấn Enter để Excel điền toàn bộ cột

4. Đổi tên tiêu đề thành PricePerUnit

Bằng cách này, các cột tính toán trở thành một phần của chính bảng. Chúng được lưu trong mô hình, làm mới cùng dữ liệu của bạn và sẵn sàng cho bất kỳ PivotTable hoặc measure DAX nào bạn xây dựng sau này.

Thêm cột tính toán bổ sung. Ảnh: Tác giả.

Viết công thức DAX cho phân tích

Giờ mô hình đã sẵn sàng, chúng ta có thể bắt đầu tạo công thức DAX để phân tích dữ liệu. Những công thức này giúp xây dựng tổng, so sánh và tính toán theo thời gian ngay trong báo cáo.

Tạo measures

Bạn nên dùng measures khi muốn các phép tính tự động làm mới bên trong PivotTable.

Để tạo một measure:

1. Mở cửa sổ Power Pivot

2. Vào Home > Calculations > New Measure

3. Nhập công thức như = SUM(Sales[TotalAmount])

4. Đặt tên là Total Sales và chọn OK

Tạo measures. Ảnh: Tác giả. 

Thêm measure phần trăm trên tổng

Bạn cũng có thể dùng công thức này để thêm measure phần trăm trên tổng:

= DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Regions)))

Điều này sẽ hiển thị tỷ trọng doanh thu của mỗi vùng trong tổng thể.

Thêm measure tỷ lệ phần trăm trên tổng. Ảnh: Tác giả.

Dùng “time intelligence”

Các hàm time intelligence là công thức DAX hiểu cách dữ liệu biến đổi theo ngày, tháng, quý và năm. Chúng cho phép bạn tính lũy kế từ đầu năm, so sánh với kỳ trước và đánh giá xu hướng mà không phải chỉnh bộ lọc thủ công.

Để thấy các hàm này hoạt động trong mô hình, trước hết bạn cần một bảng Ngày (Date) chuẩn.

Thiết lập bảng Date

Để thiết lập bảng: 

1. Vào Power Pivot > Add to Data Model
2. Trong Power Pivot, chọn bảng và vào Design > Mark as Date Table

Tạo một bảng dữ liệu. Ảnh: Tác giả.

3. Bây giờ từ Home > Diagram View, liên kết Date[Date] → Sales[OrderDate].

Liên kết Date Table[Date] với Sales[OrderDate]. Ảnh: Tác giả.

Tạo các measure time intelligence

Khi bảng Date đã xong, bạn có thể xây dựng các measure đánh giá hiệu suất qua các giai đoạn khác nhau.

Lũy kế từ đầu năm (YTD):

Total Sales YTD :=
TOTALYTD([Total Sales], 'Date Table'[Date])

So sánh với năm trước:

Sales Last Year :=
CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date Table'[Date]))

Tính toán theo thời gian. Ảnh: Tác giả. 

Khi đã có measures, quay lại Excel và tạo một PivotTable sử dụng Data Model. Sau đó đặt các trường từ bảng Date vào khu vực Rows và thêm Total Sales, Total Sales YTD, và Sales Last Year vào Values. 

Điều này cho thấy cách các measure time intelligence hoạt động với bảng Date bên trong mô hình.

PivotTable hiển thị Total Sales, YTD và Last Year Date. Ảnh: Tác giả. 

Mẫu DAX thường dùng

Một số công thức DAX xuất hiện thường xuyên vì giúp bạn phân tích nhanh và trả lời các câu hỏi phổ biến. Dưới đây là hai mẫu hoạt động tốt trong nhiều mô hình:

Trung bình theo danh mục:

Average Sales Per Category :=
AVERAGE(Sales[TotalAmount])

Tổng lũy kế theo ngày:

Running Total Sales :=
CALCULATE(
    [Total Sales],
    FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]))
)

Khi tạo measures, hãy hình thành vài thói quen đơn giản: 

• Đặt tên rõ ràng
• Giữ công thức dễ đọc
• Dùng biến (VAR) khi measure dài. 

Điều đó giúp mô hình dễ hiểu hơn khi bạn quay lại sau này.

Trực quan hóa và tương tác với mô hình

Giờ mô hình và measures đã xong, hãy biến dữ liệu thành hình ảnh để bạn khám phá và điều chỉnh theo thời gian thực.

Tạo PivotTable và PivotChart

Cách chèn PivotTable từ mô hình dữ liệu để làm việc trực tiếp với các bảng đã kết nối:

1. Mở một trang tính Excel
2. Vào Insert > PivotTable > From Data Model
3. Chọn New Worksheet

Trong ngăn PivotTable Fields, bạn có thể kéo trường từ bất kỳ bảng nào. Ví dụ:

• Kéo RegionName từ bảng Regions vào Rows
• Kéo Total Sales vào Values

Nhờ các quan hệ đã xây dựng trước đó, Excel tự động tổng hợp mọi thứ.

Tạo PivotTable sử dụng dữ liệu Power Pivot. Ảnh: Tác giả.

Nếu bạn muốn biểu đồ, nhấp vào bất kỳ đâu trong PivotTable, vào Insert > PivotChart, chọn loại biểu đồ (như Clustered Column) và xác nhận. Biểu đồ được liên kết với PivotTable, nên mọi thứ sẽ cập nhật đồng bộ.

Thêm PivotChart. Ảnh: Tác giả.

Thêm slicer và bộ lọc

Slicer cung cấp bộ lọc dạng nút bấm giúp báo cáo tương tác. Để thêm:

1. Nhấp vào PivotTable của bạn
2. Vào Insert > Slicer
3. Chọn các trường như RegionName hoặc ProductName

Một slicer xuất hiện dưới dạng hộp trên trang. Khi bạn nhấp các mục khác nhau, PivotTable và biểu đồ cập nhật tức thì. Nếu có nhiều PivotTable, bạn có thể kết nối một slicer với tất cả để lọc nhất quán trên trang.

Thêm Slicer. Ảnh: Tác giả.

Xây dựng KPI

KPI giúp bạn xem hiệu suất so với mục tiêu mà không cần thêm phép tính vào sheet. Cách xây dựng:

1. Trong cửa sổ Power Pivot, vào KPIs > New KPI
2. Đặt Total Sales làm measure cơ sở
3. Dùng Absolute value, nhập mục tiêu (ví dụ 4000), điều chỉnh ngưỡng và chọn kiểu biểu tượng
4. Nhấp OK để tạo KPI

Đặt KPI cho một measure. Ảnh: Tác giả.

5. Trong ngăn PivotTable Fields, mở rộng bảng Sales, sau đó mở rộng Total Sales
6. Từ đó, kéo Total Sales và Status vào trường Value

Giờ bạn có thể thấy hiệu suất so với ngưỡng mục tiêu. 

Hiển thị trạng thái KPI trong PivotTable của Excel. Ảnh: Tác giả.

Tối ưu hiệu năng Power Pivot

Khi mô hình đã được xây dựng, chúng ta muốn giữ nó nhanh và dễ làm việc. Power Pivot có thể xử lý tập dữ liệu lớn, nhưng vài điều chỉnh nhỏ sẽ giúp tệp phản hồi tốt, đặc biệt khi bạn bổ sung thêm dữ liệu theo thời gian.

Giảm kích thước mô hình

Mô hình nhẹ chạy nhanh hơn, vì vậy hãy xóa bất cứ thứ gì bạn không cần.

Bạn có thể xóa các cột không dùng trong Data View. Dù một cột không bao giờ xuất hiện trong PivotTable, nó vẫn chiếm bộ nhớ, vì vậy việc lược bỏ giúp mô hình sạch hơn.

Khi nạp dữ liệu mới, hãy dùng Power Query để lọc hàng và cột trước khi chúng vào mô hình. Như vậy chỉ các trường bạn quan tâm mới được nạp, giúp mọi thứ gọn gàng.

Cố gắng tránh cột tính toán trừ khi thật sự cần, vì chúng lưu một giá trị cho mỗi hàng, khiến kích thước tệp tăng nhanh. Ngược lại, measures hiệu quả hơn vì chỉ tính khi PivotTable cần. 

Chọn kiểu dữ liệu hiệu quả

Power Pivot nén dữ liệu khác nhau tùy theo kiểu dữ liệu. Nếu dùng đúng kiểu, hiệu quả có thể khác biệt rõ rệt.

Trong Data View, chọn một cột và chọn kiểu chính xác nhất dưới mục Data Type trên ribbon. Ví dụ:

• Số nguyên > Whole Number
• Giá trị thập phân > Decimal Number
• ID hoặc mã không dùng cho tính toán > Text

Khi chọn đúng kiểu, Power Pivot nén cột tốt hơn, giảm kích thước và tăng tốc tính toán.

Kiểm tra và dùng đúng kiểu dữ liệu. Ảnh: Tác giả.

Xử lý vấn đề làm mới và tính toán

Nếu PivotTable của bạn không phản ánh dữ liệu mới nhất, hãy vào tab Power Pivot và nhấp Refresh All. Thao tác này tải lại mọi thứ từ nguồn dữ liệu.

Khi con số trông không đúng, mở Diagram View và kiểm tra các quan hệ, vì một quan hệ thiếu hoặc hỏng có thể khiến tổng nhảy sai hoặc lọc không chính xác.

Nếu bạn gặp lỗi DAX, đặc biệt với measure phức tạp, thường là do công thức tham chiếu gián tiếp đến chính nó. Khi đó, hãy viết lại measure với lôgic đơn giản hơn hoặc dùng khối VAR để xử lý vòng tham chiếu.

Tích hợp với Power Query và Power BI

Một trong những lợi thế của Power Pivot là khả năng hoạt động mượt mà với các công cụ dữ liệu khác của Microsoft. Chúng ta có thể dùng Power Query để làm sạch và định hình dữ liệu trước khi đưa vào mô hình, hoặc chuyển toàn bộ mô hình sang Power BI khi bạn cần bảng điều khiển tương tác.

Làm sạch và biến đổi dữ liệu trong Power Query

Power Query là nơi tốt nhất để chuẩn bị dữ liệu trước khi tải vào Power Pivot. Nó cho phép bạn làm sạch, lọc và định hình mọi thứ ngay từ đầu để mô hình luôn ngăn nắp.

Bạn có thể mở Power Query bằng cách vào Data > From Text/CSV > Transform. Dữ liệu sẽ mở trong trình soạn thảo, nơi bạn có thể:

• Loại bỏ hàng trùng
• Đổi tên hoặc sắp xếp lại cột
• Lọc bỏ các giá trị không cần
• Thay đổi kiểu dữ liệu trước khi vào mô hình

Power Query ghi lại từng bước ở bên phải cửa sổ. Điều đó nghĩa là quá trình làm sạch sẽ chạy tự động mỗi khi bạn làm mới tệp.

Khi mọi thứ đã ổn, chọn Close & Load To, rồi chọn Data Model. Dữ liệu đã làm sạch sẽ nạp trực tiếp vào Power Pivot.

Xuất mô hình sang Power BI

Bạn cũng có thể đưa mô hình Power Pivot sang Power BI khi cần trực quan phong phú hơn hoặc bảng điều khiển dùng chung. Cách thực hiện:

1. Lưu sổ làm việc Excel của bạn
2. Mở Power BI Desktop
3. Vào Get Data > Excel Workbook
4. Chọn tệp của bạn

Power BI nhập các bảng và quan hệ y như trong Power Pivot. Từ đó, bạn có thể xây dựng dashboard, cộng tác với nhóm và thiết lập lịch làm mới để báo cáo luôn cập nhật mà không cần thao tác thủ công.

Thực hành tốt cho mô hình bền vững

Khi mô hình lớn dần, việc giữ mọi thứ ngăn nắp sẽ giúp bạn cập nhật, gỡ lỗi và phát triển tiếp dễ dàng hơn. Dưới đây là một vài thói quen giúp mô hình sạch và đáng tin cậy theo thời gian:

Quy ước đặt tên và tổ chức

Tên rõ ràng tạo khác biệt lớn khi bạn quay lại tệp sau vài tuần hoặc tháng. Vì vậy, bạn nên dùng tên measure dễ đọc như Total_Sales, Total_Quantity hoặc Profit_Margin để luôn biết mỗi measure thể hiện điều gì.

Bạn cũng có thể nhóm các measure liên quan vào Display Folders trong cửa sổ Power Pivot. Khi mô hình lớn hơn, các thư mục này giúp bạn dễ tìm phép tính cần thiết.

Xác thực dữ liệu

Trước khi tin tưởng con số, hãy chạy vài kiểm tra nhanh:

• So sánh tổng từ nguồn dữ liệu với tổng trong PivotTable của bạn
• Dùng các phép kiểm DAX đơn giản như:

• COUNTROWS() để xác nhận số hàng trong bảng

• DISTINCTCOUNT() để kiểm tra giá trị duy nhất, như khách hàng hoặc sản phẩm

Những kiểm tra nhỏ này giúp bạn phát hiện quan hệ thiếu, bộ lọc sai hoặc vấn đề dữ liệu trước khi chúng gây lỗi lớn hơn.

Duy trì và cập nhật mô hình

Khi có dữ liệu mới, vào tab Power Pivot và chọn Refresh hoặc Refresh All. Kết quả là Power Pivot tải lại mọi thứ từ các nguồn kết nối.

Trước khi thay đổi cấu trúc lớn như thêm quan hệ mới hoặc viết lại measure chính, hãy lưu một bản sao lưu của tệp. Điều đó cho bạn phương án dự phòng nếu mọi thứ không như kế hoạch.

Lời kết

Power Pivot tập hợp dữ liệu của bạn vào một nơi và giúp bạn xây dựng báo cáo rõ ràng, đáng tin cậy. Khi mô hình đã thiết lập, hãy khám phá con số, tạo trực quan và cập nhật mọi thứ chỉ với một lần làm mới.

Nếu bạn muốn học trọn bộ công cụ của Excel, hãy xem lộ trình Data Analysis with Excel Power Tools cũng như khóa học (dĩ nhiên) Power Pivot in Excel của chúng tôi.