Power Pivot Và Data Model Trong Excel: Phân Tích Dữ Liệu Lớn
Power Pivot là công cụ phân tích dữ liệu lớn nằm ngay trong Excel. Nó giải quyết đúng hai giới hạn mà người làm báo cáo hay gặp: bảng tính chỉ chứa được hơn một triệu dòng, và việc gộp dữ liệu từ nhiều bảng phải làm thủ công bằng VLOOKUP.
Với Power Pivot, dữ liệu không nằm trong ô bảng tính nữa mà nằm trong một kho riêng gọi là Data Model, nén lại nên chứa được nhiều hơn rất nhiều. Các bảng liên kết với nhau bằng quan hệ, không cần hàm tra cứu.
Trước tiên: bản Office của bạn có Power Pivot không?
Đây là câu cần trả lời trước khi làm gì khác. Power Pivot chỉ có ở bản Professional Plus, không có trong bản Home & Student hay Home & Business.
Cách kiểm tra: vào File → Options → Add-ins, ở mục Manage chọn COM Add-ins rồi bấm Go. Nếu trong danh sách có Microsoft Power Pivot for Excel thì bản của bạn có, chỉ cần tích vào để bật. Nếu không thấy dòng đó, bản Office của bạn không kèm Power Pivot.
Trường hợp không có, bạn cần bản Professional Plus, ví dụ key Office 2021 hoặc key Office 2019. Sự khác nhau giữa các bộ ứng dụng được nói rõ ở phân biệt các loại key Office.
Data Model là gì?
Hiểu đơn giản, Data Model là một cơ sở dữ liệu thu nhỏ nằm bên trong file Excel. Dữ liệu ở đó được nén theo cột nên chiếm ít dung lượng hơn nhiều so với để trong sheet.
Ba khác biệt đáng chú ý so với cách làm truyền thống:
- Không giới hạn một triệu dòng. Giới hạn thực tế phụ thuộc vào RAM của máy chứ không phải số dòng bảng tính.
- Các bảng liên kết bằng quan hệ. Bạn nối bảng đơn hàng với bảng khách hàng qua mã khách hàng một lần, sau đó mọi báo cáo đều dùng được, không cần VLOOKUP ở từng chỗ.
- File nhẹ hơn. Cùng lượng dữ liệu, để trong Data Model thường nhẹ hơn đáng kể so với để trong sheet, và đây là hướng xử lý tốt cho các file nặng, xem thêm cách tối ưu file Excel nặng.
Đưa dữ liệu vào Data Model
Có ba đường, chọn theo nguồn dữ liệu của bạn.
Từ bảng có sẵn trong file. Chọn vùng dữ liệu, bấm Ctrl+T để chuyển thành Table, đặt tên dễ hiểu. Sau đó vào tab Power Pivot → Add to Data Model.
Từ file bên ngoài. Dùng Power Query để lấy dữ liệu từ file CSV, file Excel khác hoặc thư mục chứa nhiều file, rồi ở bước cuối chọn nạp vào Data Model thay vì nạp ra sheet. Cách dùng Power Query được hướng dẫn riêng tại Power Query trong Excel.
Từ cơ sở dữ liệu. Trong cửa sổ Power Pivot, chọn Get External Data và trỏ tới máy chủ. Cách này phù hợp khi dữ liệu do hệ thống khác quản lý, tương tự cách Access kết nối SQL Server.
Tạo quan hệ giữa các bảng
Đây là phần quan trọng nhất và cũng dễ làm sai nhất.
Mở cửa sổ Power Pivot, chuyển sang Diagram View. Bạn sẽ thấy các bảng dưới dạng khối. Kéo cột khóa của bảng này thả vào cột tương ứng của bảng kia để tạo quan hệ.
Nguyên tắc cần nhớ: quan hệ đúng là một bảng tra cứu nối với một bảng dữ liệu phát sinh. Bảng tra cứu chứa danh sách không trùng như danh mục khách hàng, danh mục sản phẩm. Bảng dữ liệu phát sinh chứa các dòng lặp lại như đơn hàng, giao dịch.
Nếu Power Pivot báo không tạo được quan hệ, nguyên nhân gần như luôn là cột khóa bên bảng tra cứu có giá trị trùng. Kiểm tra bằng cách lọc giá trị duy nhất, hoặc nếu bản Excel của bạn có nhóm hàm mảng động thì dùng UNIQUE cho nhanh, xem hàm UNIQUE trong Excel.
Bảng lịch: thứ nên làm ngay từ đầu
Hầu hết báo cáo đều cần xem theo tháng, quý, năm. Cách làm chuẩn là tạo một bảng lịch riêng, chứa mọi ngày trong khoảng thời gian bạn phân tích, kèm các cột năm, quý, tháng, tên tháng.
Nối bảng lịch này với cột ngày của bảng dữ liệu, sau đó đánh dấu nó là bảng ngày tháng bằng Design → Mark as Date Table. Từ đó mọi phép tính theo thời gian mới hoạt động đúng.
Bỏ qua bước này là lỗi phổ biến nhất của người mới dùng Power Pivot, và hậu quả là các phép so sánh cùng kỳ cho kết quả sai mà không báo lỗi gì.
Viết phép tính bằng DAX
DAX là ngôn ngữ công thức của Power Pivot. Cú pháp nhìn giống hàm Excel nhưng làm việc trên cả cột thay vì từng ô.
Có hai loại cần phân biệt:
- Cột tính toán - thêm một cột mới vào bảng, tính cho từng dòng. Dùng khi bạn cần phân loại dữ liệu, ví dụ xếp đơn hàng vào nhóm giá trị.
- Measure - phép tính tổng hợp, chỉ tính khi hiển thị trong báo cáo. Dùng cho doanh thu, số lượng, tỷ lệ. Measure hiệu quả hơn nhiều về bộ nhớ, nên ưu tiên dùng nó.
Ba measure nên tạo đầu tiên cho bất kỳ bảng dữ liệu bán hàng nào: tổng doanh thu, số lượng đơn, và doanh thu trung bình mỗi đơn. Từ ba cái này ghép ra được phần lớn báo cáo thông thường.
Xuất báo cáo từ Data Model
Vào Insert → PivotTable, chọn Use this workbook's Data Model. PivotTable lúc này kéo được trường từ mọi bảng trong mô hình, không giới hạn ở một bảng như PivotTable thường.
Thêm Slicer để lọc theo khách hàng hoặc khu vực, thêm Timeline để lọc theo thời gian. Cách dùng hai công cụ này có ở bài PivotTable nâng cao, và cách trình bày thành bảng theo dõi trực quan ở tạo dashboard trong Excel.
Câu hỏi thường gặp
Power Pivot có trong Office 2016 không?
Có, ở bản Professional Plus. Các bản khác của 2016 thì không.
File có Power Pivot mở trên máy không có Power Pivot thì sao?
PivotTable vẫn xem được kết quả đã tính, nhưng không mở được cửa sổ Power Pivot để chỉnh mô hình.
Power Pivot khác Power Query thế nào?
Power Query dùng để lấy và làm sạch dữ liệu. Power Pivot dùng để mô hình hóa và tính toán trên dữ liệu đã sạch. Hai công cụ bổ sung cho nhau, thường dùng nối tiếp.
Dữ liệu bao nhiêu dòng thì nên dùng Power Pivot?
Không có mốc cứng, nhưng từ khoảng vài trăm nghìn dòng trở lên hoặc khi bạn phải nối từ ba bảng trở lên thì Power Pivot rõ ràng hiệu quả hơn cách làm bằng công thức.
