Nắm vững các mô hình dữ liệu phức tạp và mối quan hệ bảng với Power Pivot

Power Pivot là một phần bổ trợ Excel mạnh mẽ 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 phép tính 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 tôi sẽ hướng dẫn cách xây dựng các mô hình và mối quan hệ dữ liệu phức tạp bằng Power Pivot bằng các ví dụ thực tế.

Power Pivot là gì?

Power Pivot là một phần bổ trợ mạnh mẽ trong Excel cho phép người dùng:

• Nó nhập các 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ách sử dụng các trường khóa/chính.
• Thực hiện các phép tính nâng cao với Biểu thức phân tích dữ liệu (DAX).
• Tạo bảng thông tin và PivotTable tương tác, hiệu quả.

Bật tab Power Pivot:

• Đi tới Tệp tab>> chọn Tùy chọn>> chọn Phần bổ trợ từ Tùy chọn Excel .
• Trong phần Quản lý hộp> > chọn Phần bổ trợ COM>> nhấp vào Đi .
• Từ Phần bổ trợ COM hộp thoại>> chọn Microsoft Power Pivot cho Excel>> nhấp vào OK .

1. Chuẩn bị dữ liệu của bạn cho Power Pivot

Trước khi xây dựng các mối quan hệ và mô hình dữ liệu phức tạp 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ột mã định danh duy nhất hoặc khóa chính cho từng thực thể.

Hãy xem xét tập dữ liệu bán hàng mà bạn sẽ cần:

• Doanh số :SaleID (Mã định danh duy nhất cho mỗi lần bán hàng)
• 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 :ID khu vực (Mã định danh duy nhất cho từng khu vực)
• Ngày :Ngày (Mã định danh duy nhất cho mỗi ngày)

Đảm bảo rằng mỗi trường trong số này nhất quán trên các bảng, đặc biệt là những trường sẽ được sử dụng để tạo mối quan hệ, chẳng hạn như ProductID, CustomerID và RegionID.

2. Đang 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:

• Đi tới Power Pivot tab>> nhấp vào Quản lý để mở cửa sổ Power Pivot.
• Trong cửa sổ Power Pivot>> nhấp vào Nhận dữ liệu bên ngoài>> chọn Từ các 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.
• Đi tới Chèn tab>> chọn Bảng .

• Đặt tên cho từng bảng của bạn như Sales , Sản phẩm , Khách hàng , Khu vực và Ngày .

• Đi tới Power Pivot tab>> chọn Thêm vào mô hình dữ liệu để mở Power Pivot Editor .

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, đi tới Thiết kế tab>> chọn Tạo mối quan hệ .
• Tạo mối quan hệ hộp thoại sẽ bật lên,
• “Chọn các bảng và cột có liên quan với nhau”.
• Xác định các mối quan hệ bằng cách sử dụng từng ánh xạ sau:
  • Doanh số[ProductID] -> Sản phẩm[ProductID] :Kết nối mỗi lần bán hàng với sản phẩm tương ứng.
  • Doanh số[CustomerID] -> Khách hàng[CustomerID] :Kết nối mỗi lần bán hàng với khách hàng tương ứng.
  • Doanh số[Ngày] -> Ngày[Ngày] :Kết nối mỗi lần bán hàng với ngày tương ứng.
  • Khách hàng[RegionID] -> Vùng[RegionID] :Kết nối từng khách hàng với khu vực tương ứng của họ.

Mối quan hệ:

Thay thế:Từ chế độ xem sơ đồ

• Chuyển sang Chế độ xem sơ đồ trong Power Pivot.
• Kéo ProductID từ Bán hàng bảng vào Sản phẩm bàn.
• Kéo ID khách hàng từ Bán hàng bàn cho Khách hàng bàn.
• Kéo Ngày từ Bán hàng bảng theo Ngày bàn.
• Kéo ID vùng từ Khách hàng bảng tới các Khu vực bàn.

4. Tạo cột và số đo được tính toán

Khi xây dựng mối quan hệ, bạn có thể bắt đầu thực hiện các phép tính và phân tích. Power Pivot cho phép bạn tạo các cột và thước đo được tính toán để có thông tin chuyên sâu hơn.

Ví dụ:Cột được tính

Hãy tạo một cột được tính toán trong phần Doanh số bảng để tính Lợi nhuận từ mỗi lần bán hàng:

• Trong Power Pivot cửa sổ>> chọn Bán hàng bàn.
• Nhấp vào Thêm cột và chèn công thức sau để tính lợi nhuận.

Cột được tính toán này bây giờ sẽ xuất hiện trong phần Doanh số bảng có giá trị lợi nhuận. Bạn có thể đổi tên cột thành Profit.

Ví dụ:Tính số đo

Biện pháp 1:Tổng doanh thu

Để tính Tổng doanh thu trên tất cả doanh số bán hàng, bạn có thể tạo thước đo trong Power Pivot:

• Trong Bán hàng bảng, đi tới Vùng tính toán .
• Nhập công thức DAX sau để tạo Tổng doanh thu thước đo:

Biện pháp này sẽ tự động tính toán tổng doanh thu, điều chỉnh dựa trên bất kỳ bộ lọc hoặc bộ phân tích nào được áp dụng cho mô hình dữ liệu của bạn.

Biện pháp 2:Tổng lợi nhuận

Để tính tổng lợi nhuận, hãy chèn công thức DAX sau vào vùng tính toán.

Đo lường 3:Thu nhập trung bình của khách hàng

Để tính thu nhập trung bình của khách hàng, hãy chèn công thức DAX sau vào vùng tính toán.

= AVERAGE(Customers[Income])

Đầu ra:

5. Phân tích nâng cao:Thông tin về thời gian

Với Ngày bảng, 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 toán xu hướng bán hàng theo thời gian. Power Pivot hỗ trợ Thông tin về thời gian các hàm, chẳng hạn như TOTALYTD (Từ đầu năm đến nay) và SAMEPERIODLASTYEAR, cho phép bạn so sánh hiệu suất giữa các giai đoạn khác nhau.

Để tính Doanh thu từ đầu năm đến nay , bạn có thể tạo thước đo như thế này:

=TOTALYTD(SUM(Sales[Revenue]),Dates[Date])

Biện pháp này sẽ tính toán doanh thu lũy kế từ đầu năm đến ngày đã chọn.

Để tính toán Doanh thu hàng năm tăng trưởng, hãy 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 toán phần trăm tăng trưởng so với cùng kỳ năm ngoái.

6. Tạo bảng tổng hợp để phân tích dữ liệu

Sau khi đã có các mối quan hệ và phép tính, bạn có thể bắt đầu tạo PivotTable và PivotChart để phân tích dữ liệu.

• Đi tới Chèn tab>> chọn PivotTable .
• Trong phần Tạo PivotTable hộp thoại>> chọn Từ mô hình dữ liệu .
• Trong danh sách trường PivotTable, bạn sẽ thấy tất cả các bảng và trường mà 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 Hàng , Cột và Giá trị các lĩnh vực để 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 Tên sản phẩm từ Sản phẩm bảng vào Hàng khu vực và Tổng doanh thu đo lường theo Giá trị khu vực.
• Để phân tích Doanh thu theo khu vực;
  • Kéo Tên vùng từ Khu vực bảng vào Hàng khu vực và Tổng doanh thu đo lường theo Giá trị khu vực.

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 bộ cắt cho Tháng để lọc dữ liệu theo các tháng khác nhau.

Kết luận

Với tập dữ liệu thực tế, chúng tôi đã trình bày quy trình xây dựng các mô hình và mối quan hệ dữ liệu phức tạp trong Power Pivot. Điều này giúp bạn thực hiện các phân tích phức tạp mà chỉ sử dụng các hàm Excel truyền thống khó có thể thực hiện được. Bằng cách liên kết các bảng có liên quan cũng như sử dụng các cột và thước đo được tính toán, bạn có thể hiểu 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 của khách hàng.