Power Pivot là một tiện ích bổ sung mạnh mẽ của Excel cho phép người dùng xây dựng các mô hình dữ liệu phức tạp và mối quan hệ giữa các bảng. Nó tạo ra các mô hình dữ liệu mạnh mẽ để thực hiện các tính toán dữ liệu nâng cao. Nó mở rộng khả năng của Excel bằng cách cho phép tạo các mô hình dữ liệu động, quy mô lớn mà không cần phần mềm bên ngoài. Trong bài viết này, chúng ta sẽ chỉ cách xây dựng mô hình dữ liệu phức tạp và mối quan hệ với Power Pivot thông qua các ví dụ thực tế.
Power Pivot là gì?
Power Pivot là một tiện ích bổ sung mạnh mẽ trong Excel cho phép người dùng:
- Nhập tập dữ liệu lớn từ nhiều nguồn.
- Tạo mối quan hệ giữa các bảng bằng các trường khóa/khóa chính.
- Thực hiện các tính toán nâng cao với Biểu thức Phân tích Dữ liệu (DAX).
- Tạo bảng điều khiển tương tác, PivotTable hiệu quả.
Bật tab Power Pivot:
- Vào tab Tệp >> chọn Tùy chọn >> chọn Tiện ích bổ sung từ Tùy chọn Excel.
- Trong hộp Quản lý >> chọn COM Add-ins >> nhấp Chuyển đến.
- Từ hộp thoại COM Add-ins >> chọn Microsoft Power Pivot for Excel >> nhấp OK.
1. Chuẩn bị Dữ liệu cho Power Pivot
Trước khi xây dựng mô hình dữ liệu phức tạp và mối quan hệ trong Power Pivot, hãy đảm bảo rằng mỗi bảng trong tập dữ liệu của bạn có mã định danh duy nhất hoặc khóa chính cho mỗi thực thể.
Hãy xem xét một tập dữ liệu bán hàng nơi bạn sẽ cần:
- Bán hàng: SaleID (Mã định danh duy nhất cho mỗi lần bán)
- Sản phẩm: ProductID (Mã định danh duy nhất cho mỗi sản phẩm)
- Khách hàng: CustomerID (Mã định danh duy nhất cho mỗi khách hàng)
- Khu vực: RegionID (Mã định danh duy nhất cho mỗi khu vực)
- Ngày: Date (Mã định danh duy nhất cho mỗi ngày)
Đảm bảo rằng mỗi trường này nhất quán trên các bảng, đặc biệt là những trường sẽ được dùng để tạo mối quan hệ, như ProductID, CustomerID và RegionID.
2. Tải Dữ liệu vào Power Pivot
Để nhập dữ liệu vào Power Pivot, bạn có thể sử dụng các tùy chọn khác nhau dựa trên loại dữ liệu của mình.
Để nhập dữ liệu từ các nguồn khác, hãy làm theo các bước sau:
- Vào tab Power Pivot >> nhấp Quản lý để mở cửa sổ Power Pivot.
- Trong cửa sổ Power Pivot >> nhấp Lấy Dữ liệu Bên ngoài >> chọn Từ Nguồn Khác để nhập dữ liệu của bạn.
Để nhập dữ liệu từ Sổ làm việc Excel hiện có:
- Chọn phạm vi dữ liệu.
- Vào tab Chèn >> chọn Bảng.
- Đặt tên cho từng bảng của bạn như Bán hàng, Sản phẩm, Khách hàng, Khu vực và Ngày.
- Vào tab Power Pivot >> chọn Thêm vào Mô hình Dữ liệu để mở Trình chỉnh sửa Power Pivot.
Bằng cách làm theo các bước này, bạn có thể nhập dữ liệu của mình.
3. Tạo Mối quan hệ
Bây giờ dữ liệu của bạn đã được tải, bạn cần thiết lập mối quan hệ giữa các bảng này.
- Trong cửa sổ Power Pivot, vào tab Thiết kế >> chọn Tạo Mối quan hệ.
- Hộp thoại Tạo Mối quan hệ sẽ xuất hiện,
- "Chọn bảng và cột liên quan đến nhau".
- Xác định các mối quan hệ bằng các ánh xạ sau một cách lần lượt:
- Bán hàng[ProductID] → Sản phẩm[ProductID]: Kết nối mỗi lần bán với sản phẩm tương ứng.
- Bán hàng[CustomerID] → Khách hàng[CustomerID]: Kết nối mỗi lần bán với khách hàng tương ứng.
- Bán hàng[Date] → Ngày[Date]: Kết nối mỗi lần bán với ngày tương ứng.
- Khách hàng[RegionID] → Khu vực[RegionID]: Kết nối mỗi khách hàng với khu vực tương ứng.
Mối quan hệ:
Cách thay thế: Từ Chế độ xem Sơ đồ
- Chuyển sang Chế độ xem Sơ đồ trong Power Pivot.
- Kéo ProductID từ bảng Bán hàng đến bảng Sản phẩm.
- Kéo CustomerID từ bảng Bán hàng đến bảng Khách hàng.
- Kéo Date từ bảng Bán hàng đến bảng Ngày.
- Kéo RegionID từ bảng Khách hàng đến bảng Khu vực.
4. Tạo Cột Tính toán và Đơn vị đo lường
Khi bạn xây dựng mối quan hệ, bạn có thể bắt đầu thực hiện các tính toán và phân tích. Power Pivot cho phép bạn tạo cột tính toán và đơn vị đo lường để có thông tin chi tiết sâu hơn.
Ví dụ: Cột Tính toán
Hãy tạo một cột tính toán trong bảng Bán hàng để tính Lợi nhuận từ mỗi lần bán:
- Trong cửa sổ Power Pivot >> chọn bảng Bán hàng.
- Nhấp Thêm Cột và chèn công thức sau để tính lợi nhuận.
Cột tính toán này sẽ xuất hiện trong bảng Bán hàng với các giá trị lợi nhuận. Bạn có thể đổi tên cột thành Profit.
Ví dụ: Tính toán Đơn vị đo lường
Đơn vị đo lường 1: Tổng Doanh thu
Để tính Tổng Doanh thu trên tất cả các lần bán, bạn có thể tạo một đơn vị đo lường trong Power Pivot:
- Trong bảng Bán hàng, vào khu vực Khu vực Tính toán.
- Gõ công thức DAX sau để tạo đơn vị đo lường Tổng Doanh thu:
Đơn vị đo lường này sẽ tính toán động tổng doanh thu, điều chỉnh dựa trên bất kỳ bộ lọc hoặc slicer nào được áp dụng cho mô hình dữ liệu của bạn.
Đơn vị đo lường 2: Tổng Lợi nhuận
Để tính tổng lợi nhuận, chèn công thức DAX sau vào khu vực tính toán.
Đơn vị đo lường 3: Thu nhập Trung bình Khách hàng
Để tính thu nhập trung bình của khách hàng, chèn công thức DAX sau vào khu vực tính toán.
= AVERAGE(Customers[Income])
Đầu ra:
5. Phân tích Nâng cao: Thông minh Thời gian
Với bảng Ngày, bạn có thể thực hiện phân tích dựa trên thời gian, chẳng hạn như tính xu hướng bán hàng theo thời gian. Power Pivot hỗ trợ các hàm Thông minh Thời gian, như TOTALYTD (Năm đến nay) và SAMEPERIODLASTYEAR, cho phép bạn so sánh hiệu suất qua các kỳ khác nhau.
Để tính Doanh thu Năm đến nay, bạn có thể tạo một đơn vị đo lường như sau:
=TOTALYTD(SUM(Sales[Revenue]),Dates[Date])
Đơn vị đo lường này sẽ tính doanh thu tích lũy từ đầu năm đến ngày được chọn.
Để tính Tăng trưởng Doanh thu So với Cùng kỳ Năm trước, chèn công thức DAX sau.
=DIVIDE( SUM(Sales[Revenue]) - CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Dates[Date])), CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Dates[Date])), 0)
Điều này tính tỷ lệ phần trăm tăng trưởng so với cùng kỳ năm trước.
6. Tạo Bảng Pivot để Phân tích Dữ liệu
Khi mối quan hệ và tính toán của bạn đã sẵn sàng, bạn có thể bắt đầu tạo PivotTable và PivotChart để phân tích dữ liệu.
- Vào tab Chèn >> chọn Bảng Pivot.
- Trong hộp thoại Tạo Bảng Pivot >> chọn Từ Mô hình Dữ liệu.
- Trong danh sách trường Bảng Pivot, bạn sẽ thấy tất cả các bảng và trường bạn đã thêm vào mô hình dữ liệu. Kéo các trường từ bảng của bạn vào khu vực Hàng, Cột và Giá trị để thực hiện các phân tích khác nhau.
Thông tin chi tiết Nâng cao từ Mô hình:
- Để phân tích Tổng Doanh thu theo Sản phẩm:
- Kéo ProductName từ bảng Sản phẩm vào khu vực Hàng và đơn vị đo lường Tổng Doanh thu vào khu vực Giá trị.
- Để phân tích Doanh thu theo Khu vực:
- Kéo RegionName từ bảng Khu vực vào khu vực Hàng và đơn vị đo lường Tổng Doanh thu vào khu vực Giá trị.
Bạn cũng có thể thêm slicer để tương tác tốt hơn. Ví dụ, bạn có thể thêm slicer cho Tháng để lọc dữ liệu theo các tháng khác nhau.
Kết luận
Với một tập dữ liệu thực tế, chúng tôi đã trình bày quy trình xây dựng mô hình dữ liệu phức tạp và mối quan hệ trong Power Pivot. Điều này giúp bạn thực hiện các phân tích tinh vi mà sẽ khó khăn đạt được chỉ với các hàm Excel truyền thống. Bằng cách liên kết các bảng liên quan và sử dụng cột tính toán và đơn vị đo lường, bạn có thể có được thông tin chi tiết sâu hơn về dữ liệu của mình, chẳng hạn như hiểu hiệu suất bán hàng theo sản phẩm, khu vực hoặc nhân khẩu học khách hàng.
Nhận bài tập Excel nâng cao miễn phí kèm lời giải!