Hướng dẫn: Phân tích dữ liệu PivotTable bằng Mô hình Dữ liệu trong Excel 2013

Trong không đến một giờ, bạn có thể xây dựng một báo cáo PivotTable trong Excel kết hợp dữ liệu từ nhiều bảng. Phần đầu của hướng dẫn này giới thiệu với bạn các bước nhập và khai thác dữ liệu. Ở phần sau, bạn sẽ sử dụng bổ trợ Power Pivot để tinh chỉnh mô hình dữ liệu ẩn dưới báo cáo, tìm hiểu cách thêm các phép tính, cấu trúc phân cấp và tối ưu hóa cho việc báo cáo của Power View.

Hãy bắt đầu với việc nhập một vài dữ liệu.

  1. Tải xuống dữ liệu mẫu (ContosoV2) cho hướng dẫn này. Hãy xem Lấy dữ liệu mẫu cho hướng dẫn DAX và Mô hình Dữ liệu để biết chi tiết. Trích xuất và lưu các tệp dữ liệu vào một vị trí dễ dàng truy nhập, chẳng hạn như Tải xuống hoặc Tài liệu của Tôi.

  2. Trong Excel, hãy mở một sổ làm việc trống.

  3. Bấm Dữ liệu > Lấy Dữ liệu Ngoài > Từ Access.

  4. Đi tới thư mục chứa các tệp dữ liệu mẫu và chọn ContosoSales.

  5. Bấm Mở. Vì bạn đang kết nối với tệp cơ sở dữ liệu chứa nhiều bảng, hộp thoại Chọn Bảng sẽ xuất hiện để bạn có thể chọn bảng để nhập.

    Hộp thoại Chọn bảng

  6. Trong Chọn Bảng, hãy chọn Bật tính năng chọn nhiều bản.

  7. Chọn tất cả các bảng và bấm OK.

  8. Trong Nhập Dữ liệu, bấm Báo cáo PivotTable và bấm OK.

    Ghi chú   Có thể bạn chưa nhận ra ngay, nhưng bạn vừa tạo một mô hình dữ liệu. Mô hình là một lớp tích hợp dữ liệu được tự động tạo ra khi bạn nhập hoặc làm việc đồng thời với nhiều bảng trong cùng báo cáo PivotTable.

    Mô hình này hầu như trong suốt trong Excel, nhưng bạn có thể xem và sửa đổi mô hình trực tiếp bằng cách sử dụng bổ trợ Power Pivot . Trong Excel, bằng chứng cho sự hiện diện của mô hình dữ liệu là khi bạn thấy một tập hợp các bảng trong danh sách Trường PivotTable. Có rất nhiều cách để tạo mô hình. Hãy xem Tạo Mô hình Dữ liệu trong Excel để biết chi tiết.

Khám phá dữ liệu bằng cách sử dụng PivotTable

Khám phá dữ liệu thật dễ dàng khi bạn kéo các trường đến các vùng Giá trị, Cột, và Hàng trên Danh sách Trường PivotTable.

  1. Trong danh sách trường, hãy cuộn xuống cho đến khi bạn thấy bảng FactSales.

  2. Bấm SalesAmount. Vì dữ liệu ở dạng số, Excel sẽ tự động đặt SalesAmount vào vùng Giá trị.

  3. Trong DimDate, hãy kéo CalendarYear đến Cột.

  4. Trong DimProductSubcategory, hãy kéo ProductSubcategoryName đến Hàng.

  5. Trong DimProduct, hãy kéo BrandName đến Hàng, đặt nó dưới thể loại con.

PivotTable của bạn sẽ trông như màn hình dưới đây.

PivotTable thể hiện dữ liệu mẫu

Chỉ với rất ít công sức, bạn đã có PivotTable cơ bản bao gồm các trường từ bốn bảng khác nhau. Điều làm cho tác vụ này đơn giản đến vậy là vì mối quan hệ giữa các bảng đã tồn tại từ trước. Vì mối quan hệ giữa các bảng đã tồn tại trong nguồn, và vì bạn đã nhập tất cả các bảng trong một thao tác duy nhất, Excel có thể tạo lại các mối quan hệ đó trong mô hình.

Nhưng nếu dữ liệu của bạn xuất phát từ các nguồn khác nhau hoặc được nhập sau đó thì sao? Thông thường, bạn có thể kết hợp dữ liệu mới bằng cách tạo các mối quan hệ dựa trên các cột khớp. Trong bước tiếp theo, bạn sẽ nhập thêm các bảng khác và tìm hiểu các yêu cầu và các bước để tạo các mối quan hệ mới.

Thêm các bảng

Việc tìm hiểu cách thiết lập mối quan hệ bảng đòi hỏi bạn phải có một vài bảng bổ sung, không kết nối để làm việc. Trong bước này, bạn sẽ lấy các dữ liệu còn lại được sử dụng trong hướng dẫn này bằng cách nhập một tệp cơ sở dữ liệu bổ sung và dán dữ liệu từ hai sổ làm việc khác.

Thêm các thể loại sản phẩm

  1. Trong sổ làm việc, hãy mở một trang tính mới. Bạn sẽ sử dụng trang tính mới đó để lưu dữ liệu bổ sung.

  2. Bấm Dữ liệu > Lấy Dữ liệu Ngoài > Từ Access.

  3. Đến thư mục chứa các tệp dữ liệu mẫu và chọn ProductCategories. Bấm Mở.

  4. Trong Nhập Dữ liệu, hãy chọn Bảng và bấm OK.

Thêm dữ liệu địa lý

  1. Hãy chèn một trang tính khác.

  2. Từ các tệp dữ liệu mẫu, hãy mở Geography.xlsx, đặt con trỏ vào ô A1, rồi bấm Ctrl-Shift-End để chọn tất cả dữ liệu.

  3. Sao chép dữ liệu sang Bảng tạm.

  4. Dán dữ liệu vào trang tính trống mà bạn vừa mới thêm.

  5. Bấm Định dạng dưới dạng Bảng, rồi chọn bất kỳ kiểu nào. Việc định dạng dữ liệu dưới dạng bảng sẽ cho phép bạn đặt tên cho bảng, điều này sẽ thuận tiện khi bạn xác định các mối quan hệ ở bước sau này.

  6. Trong Định dạng dưới dạng Bảng, hãy xác nhận rằng Bảng của tôi có tiêu đề đã được chọn. Bấm OK.

  7. Đặt tên bảng là Địa lý. Trong Công cụ Bảng > Thiết kế, hãy nhập Địa lý trong Tên Bảng.

  8. Đóng tệp Địa lý.xlsx để xóa tệp đó khỏi không gian làm việc của bạn.

Thêm dữ liệu lưu trữ

  • Hãy lặp lại các bước trên đây đối với tệp Stores.xlsx, dán nội dung tệp vào một trang tính trống. Đặt tên bảng là Cửa hàng.

Lúc này bạn có bốn trang tính. Trang tính 1 chứa PivotTable, Trang tính 2 chứa ProductCategories, Trang tính 3 chứa Địa lý, và Trang tính 4 chứa Cửa hàng. Vì bạn đã dành thời gian đặt tên cho mỗi bảng, bước tiếp theo, tạo các mối quan hệ, sẽ đơn giản hơn nhiều.

Sử dụng các trường từ các bảng mới được nhập

Bạn có thể bắt đầu ngay việc sử dụng các trường từ các bảng mà bạn vừa mới nhập. Nếu Excel không thể xác định cách kết hợp một trường vào báo cáo PivotTable, bạn sẽ được yêu cầu tạo một mối quan hệ bảng mà mối quan hệ đó kết hợp bảng mới với một bảng đã là một phần của mô hình.

  1. Ở phía đầu Trường PivotTable, hãy bấm Tất cả để xem danh sách đầy đủ của các bảng sẵn có.

  2. Cuộn xuống dưới cùng của danh sách. Đó là nơi bạn sẽ tìm thấy các bảng mới mà bạn vừa thêm vào.

  3. Mở rộng mục Cửa hàng.

  4. Kéo StoreName đến vùng Lọc.

  5. Hãy lưu ý rằng Excel nhắc bạn tạo một mối quan hệ. Thông báo này xuất hiện bởi vì bạn đã sử dụng các trường từ một bảng không liên quan đến mô hình.

  6. Bấm Tạo để mở hộp thoại Tạo Mối quan hệ.

  7. Trong Bảng, hãy chọn FactSales. Trong dữ liệu mẫu bạn đang dùng, FactSales chứa thông tin kinh doanh và chi phí chi tiết về hoạt động kinh doanh của Contoso, cũng như các khóa đến các bảng khác, bao gồm cả các mã cửa hàng hiện có trong tệp Stores.xlsx mà bạn đã nhập ở bước trước đây.

  8. Trong Cột (Ngoại), hãy chọn StoreKey.

  9. Trong Bảng Liên quan, hãy chọn Stores.

  10. Trong Cột Liên quan (Chính), chọn StoreKey.

  11. Bấm OK.

Ở hậu trường, Excel đang xây dựng một Mô hình Dữ liệu có thể được sử dụng trong toàn bộ sổ làm việc với bất kỳ số lượng báo cáo PivotTables, PivotCharts, hoặc Power View nào. Cơ sở của mô hình này là các mối quan hệ bảng giúp xác định các đường dẫn hướng và tính toán được sử dụng trong báo cáo PivotTable. Trong tác vụ tiếp theo, bạn sẽ tạo các mối quan hệ theo cách thủ công để kết nối dữ liệu bạn vừa mới nhập.

Thêm các mối quan hệ

Bạn có thể tạo một cách hệ thống các mối quan hệ bảng cho tất cả các bảng mới mà bạn nhập. Nếu bạn đang chia sẻ sổ làm việc với đồng nghiệp, thì việc có các mối quan hệ được xác định trước sẽ được đánh giá cao nếu các đồng nghiệp không biết rõ dữ liệu như bạn.

Khi tạo các mối quan hệ theo cách thủ công, bạn sẽ làm việc với hai bảng đồng thời. Đối với mỗi bảng, bạn sẽ chọn các cột để cho Excel biết cách tra cứu các hàng liên quan trong một bảng khác.

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Liên kết ProductSubcategory đến ProductCategory

  1. Trong Excel, bấm Dữ liệu > Mối quan hệ > Mới.

  2. Trong Bảng, hãy chọn DimProductSubcategory.

  3. Trong Cột (Ngoại), hãy chọn ProductCategoryKey.

  4. Trong Bảng Liên quan, hãy chọn Table_ProductCategory.accdb.

  5. Trong Cột Liên quan (Chính), chọn ProductCategoryKey.

  6. Bấm OK.

  7. Đóng hộp thoại Quản lý Mối quan hệ.

Thêm các thể loại vào PivotTable

Mặc dù Mô hình Dữ liệu đã được cập nhật để bao gồm các bảng và các mối quan hệ bổ sung, PivotTable chưa sử dụng các bảng và mối quan hệ này. Trong tác vụ này, bạn sẽ thêm ProductCategory vào danh sách Trường PivotTable.

  1. Trong Trường PivotTable, hãy bấm Tất cả để hiển thị các bảng đang tồn tại trong Mô hình Dữ liệu.

  2. Cuộn xuống dưới cùng của danh sách.

  3. Trong vùng Hàng, hãy xóa bỏ BrandName.

  4. Bung rộng Table_DimProductCategories.accdb.

  5. Kéo ProductCategoryName đến vùng Hàng, đặt nó trên ProductSubcategory.

  6. Trong Trường PivotTable, bấm Hiện hoạt để xác nhận rằng các bảng mà bạn vừa sử dụng hiện đang được sử dụng trong PivotTable.

Điểm kiểm tra: Hãy xem lại những điều bạn đã học

Giờ đây, bạn đã có một PivotTable bao gồm dữ liệu từ nhiều bảng, một vài trong số các bảng đó bạn đã nhập vào ở bước tiếp theo. Để đưa các dữ liệu này vào với nhau, bạn đã phải tạo các mối quan hệ bảng mà Excel sử dụng để tạo tương quan cho các hàng. Bạn đã biết rằng cần phải có các cột cung cấp dữ liệu khớp để tra cứu các hàng liên quan. Trong các tệp dữ liệu mẫu, tất cả các bảng đều có một cột có thể được sử dụng cho mục đích này.

Mặc dù PivotTable vẫn hoạt động nhưng có lẽ bạn đã nhận thấy một vài điều có thể được cải thiện hơn nữa. Danh sách Trường PivotTable dường như có các bảng (DimEntity) và các cột (ETLLoadID) dư thừa, không liên quan đến hoạt động kinh doanh của Contoso. Và chúng ta vẫn chưa tích hợp dữ liệu Địa lý.

Tiếp đến: Hãy xem và mở rộng mô hình của bạn với Power Pivot

Trong loạt các tác vụ tiếp theo, bạn sẽ sử dụng bổ trợ Power Pivot trong Microsoft Excel 2013 của Microsoft Office để mở rộng mô hình. Bạn sẽ thấy rằng bạn có thể dễ dàng tạo các mối quan hệ bằng cách sử dụng Dạng xem Sơ đồ mà bổ trợ cung cấp. Bạn cũng sẽ sử dụng bổ trợ để tạo các phép tính và cấu trúc phân cấp, ẩn các mục không nên xuất hiện trong danh sách trường và tối ưu hóa dữ liệu cho việc báo cáo bổ sung.

Ghi chú    Bổ trợ Power Pivot trong Microsoft Excel 2013 sẵn dùng trong Office Professional Plus. Hãy xem bổ trợ Power Pivot trong Microsoft Excel 2013 để biết thêm thông tin.

Thêm Power Pivot vào ribbon Excel bằng cách bật bổ trợ Power Pivot.

  1. Đi đến Tệp > Tùy chọn > Bổ trợ.

  2. Trong hộp Quản lý, hãy bấm Bổ trợ COM> Đến.

  1. Chọn hộp Microsoft Office Power Pivot trong Microsoft Excel 2013, rồi bấm OK.

Ribbon bây giờ có tab Power Pivot.

Thêm một mối quan hệ bằng cách sử dụng Dạng xem Sơ đồ trong Power Pivot

  1. Trong Excel, hãy bấm vào Trang tính 3 để đưa trang tính này thành trang tính hiện hoạt. Trang tính 3 chứa bảng Địa lý mà bạn đã nhập trước đó.

  2. Trên ribbon, bấm Power Pivot > Thêm vào Mô hình Dữ liệu. Thao tác này sẽ thêm bảng Địa lý vào mô hình. Thao tác này cũng mở bổ trợ Power Pivot mà bạn sẽ sử dụng để thực hiện các bước tiếp theo trong tác vụ này.

  3. Hãy lưu ý rằng cửa sổ Power Pivot hiển thị tất cả các bảng trong mô hình bao gồm cả bảng Địa lý. Hãy bấm vào một vài bảng. Trong bổ trợ, bạn có thể xem tất cả dữ liệu chứa trong mô hình của bạn.

  4. Trong cửa sổ Power Pivot, trong phần Xem, bấm Dạng xem Sơ đồ.

  5. Hãy dùng thanh trượt để đổi kích cỡ sơ đồ để bạn có thể xem được tất cả các đối tượng trong sơ đồ. Hãy lưu ý rằng hai bảng không liên quan tới phần còn lại của sơ đồ: DimEntity và Địa lý.

  6. Hãy bấm chuột phải vào DimEntity và bấm Xóa bỏ. Bảng này là đối tượng trước đó từ cơ sở dữ liệu gốc và không cần trong mô hình.

  7. Phóng to bảng Địa lý để bạn có thể xem được tất cả các trường của bảng. Bạn có thể sử dụng con trượt để phóng to bảng.

  8. Hãy lưu ý rằng bảng Địa lý có cột GeographyKey. Cột này chứa các giá trị nhận dạng duy nhất mỗi hàng trong bảng Địa lý. Hãy cùng tìm hiểu xem các bảng khác trong mô hình có sử dụng khóa này hay không. Nếu các bảng đó có sử dụng khóa này, chúng ta có thể tạo một mối quan hệ để kết nối bảng Địa lý với phần còn lại của mô hình.

  9. Bấm Tìm.

  10. Trong Tìm Siêu Dữ liệu, hãy gõ chữ GeographyKey.

  11. Bấm Tìm Tiếp nhiều lần. Bạn sẽ thấy rằng GeographyKey hiển thị bảng Địa lý và bảng Cửa hàng.

  12. Hãy sắp đặt lại bảng Địa lý để bảng này ở bên cạnh bảng Cửa hàng.

  13. Kéo cột GeographyKey trong bảng Stores đến cột GeographyKey trong bảng Geography. Power Pivot sẽ vẽ một đường giữa hai cột, biểu thị mối quan hệ.

Trong tác vụ này, bạn đã tìm hiểu một kỹ thuật mới để thêm các bảng và tạo các mối quan hệ. Giờ đây bạn đã có một mô hình tích hợp hoàn toàn, với tất cả các bảng được liên kết và sẵn có đến PivotTable trong Trang tính 1.

Mẹo    Trong Dạng xem Sơ đồ, một vài sơ đồ bảng được mở rộng hoàn toàn, thể hiện các cột như ETLLoadID, LoadDate, và UpdateDate. Các trường cụ thể này là các đối tượng trước đó từ nhà kho dữ liệu Contoso gốc, được thêm vào để hỗ trợ trích xuất dữ liệu và các thao tác tải. Bạn không cần chúng trong mô hình của bạn. Để bỏ chúng, hãy tô sáng và bấm chuột phải vào trường, rồi bấm Xóa bỏ .

Tạo cột được tính

Trong Power Pivot, bạn có thể sử dụng Biểu thức Phân tích Dữ liệu (DAX) để thêm các phép tính. Trong tác vụ này, bạn sẽ tính toán tổng lợi nhuận và thêm cột được tính mà cột đó tham chiếu đến các giá trị dữ liệu từ các bảng khác. Sau đó, bạn sẽ xem cách sử dụng các cột được tham chiếu để đơn giản hóa mô hình của bạn.

  1. Trong cửa sổ Power Pivot , hãy chuyển về Dạng xem Dữ liệu.

  2. Đổi tên bảng Table_ProductCategories accdb thành một tên gọi thân thiện hơn. Bạn sẽ tham chiếu đến bảng này trong các bước sau đây và một tên gọi ngắn hơn sẽ giúp cho các phép tính dễ đọc hơn. Bấm chuột phải vào tên bảng, bấm Đổi tên, gõ chữ ProductCategories, rồi nhấn Enter.

  3. Chọn bảng FactSales.

  4. Bấm Thiết kế > Cột > Thêm.

  5. Trong thanh công thức ở phía trên bảng, hãy gõ công thức sau đây. Tính năng Tự động Hoàn tất giúp bạn nhập tên đủ điều kiện của các cột và bảng và liệt kê các hàm sẵn dùng. Bạn cũng có thể chỉ cần bấm vào cột và Power Pivot sẽ thêm tên cột vào công thức.

    = [SalesAmount] - [TotalCost] - [ReturnAmount]

  6. Khi bạn đã kết thúc việc xây dựng công thức, hãy nhấn Enter để chấp nhận công thức.

    Các giá trị sẽ được nhập cho tất cả các hàng trong cột được tính toán. Nếu bạn cuộn xuống cuối bảng, bạn sẽ thấy rằng các hàng có thể có các giá trị khác nhau đối với cột này, dựa trên dữ liệu ở mỗi hàng.

  7. Hãy đổi tên cột bằng cách bấm chuột phải vào CalculatedColumn1 và chọn Đổi tên Cột. Gõ Lợi nhuận , rồi nhấn Enter.

  8. Bây giờ hãy chọn bảng DimProduct.

  9. Bấm Thiết kế > Cột > Thêm.

  10. Trong thanh công thức ở phía trên bảng, hãy nhập công thức sau đây.

    = RELATED(ProductCategories[ProductCategoryName])

    Hàm RELATED trả về một giá trị từ một bảng liên quan. Trong trường hợp này, bảng ProductCategories chứa tên của các thể loại sản phẩm mà sẽ hữu ích nếu các thể loại sản phẩm đó có trong bảng DimProduct khi bạn xây dựng một cấu trúc phân cấp bao gồm thông tin về thể loại. Để biết thêm thông tin về hàm này, hãy xem Hàm RELATED (DAX).

  11. Khi bạn đã kết thúc việc xây dựng công thức, hãy nhấn Enter để chấp nhận công thức.

    Các giá trị sẽ được nhập cho tất cả các hàng trong cột được tính toán. Nếu bạn cuộn xuống cuối bảng, bạn sẽ thấy rằng lúc này mỗi hàng có một Tên Thể loại Sản phẩm.

  12. Hãy đổi tên cột bằng cách bấm chuột phải vào CalculatedColumn1 và chọn Đổi tên Cột. Gõ ProductCategory, rồi nhấn Enter.

  13. Bấm Thiết kế > Cột > Thêm.

  14. Trong thanh công thức ở phía trên bảng, hãy nhập công thức sau đây, rồi nhấn Enter để chấp nhận công thức.

    = RELATED(DimProductSubcategory[ProductSubcategoryName])

  15. Hãy đổi tên cột bằng cách bấm chuột phải vào CalculatedColumn1 và chọn Đổi tên Cột. Nhập ProductSubcategory, rồi nhấn Enter.

Tạo một cấu trúc phân cấp

Đa số các mô hình đều chứa dữ liệu vốn đã được phân cấp. Các ví dụ thường gặp bao gồm: dữ liệu lịch, dữ liệu địa lý và thể loại sản phẩm. Tạo cấu trúc phân cấp là việc làm hữu ích vì bạn có thể kéo một mục (cấu trúc phân cấp) đến một báo cáo thay vì phải tập hợp và sắp xếp các trường giống nhau hết lần này đến lần khác.

  1. Trong Power Pivot, hãy chuyển sang Dạng xem Sơ đồ. Bung rộng bảng DimDate để bạn có thể thấy tất cả các trường của bảng dễ dàng hơn.

  2. Nhấn giữ phím Ctrl và bấm vào các cột CalendarYear, CalendarQuarter và CalendarMonth (bạn sẽ cần phải cuộn đến cuối bảng).

  3. Với ba cột được chọn, hãy bấm chuột phải vào một trong các cột và bấm Tạo Cấu trúc phân cấp. Một nút cấu trúc phân cấp mẹ, Cấu trúc phân cấp 1, được tạo ở phía cuối bảng và các cột đã chọn được sao chép theo cấu trúc phân cấp như là các nút con.

  4. Nhập chữ Ngày làm tên của cấu trúc phân cấp mới của bạn.

  5. Thêm cột FullDateLabel vào cấu trúc phân cấp. Bấm chuột phải vào FullDateLabel và chọn Thêm vào Cấu trúc phân cấp. Chọn Ngày. Cột FullDateLabel chứa ngày đầy đủ, gồm cả năm, tháng và ngày. Hãy xác nhận rằng cột FullDateLabel xuất hiện cuối cùng trong cấu trúc phân cấp. Bây giờ bạn có một cấu trúc phân cấp nhiều cấp độ bao gồm ngày, quý, tháng và các ngày theo lịch cá nhân.

  6. Vẫn ở Dạng xem Sơ đồ, hãy trỏ tới bảng DimProduct, rồi bấm nút Tạo Cấu trúc phân cấp trong tiêu đề bảng. Một nút cấu trúc phân cấp mẹ trống sẽ xuất hiện ở cuối bảng.

  7. Hãy nhập Thể loại Sản phẩm làm tên của cấu trúc phân cấp mới của bạn.

  8. Để tạo các nút cấu trúc phân cấp con, hãy kéo ProductCategory và ProductSubcategory vào cấu trúc phân cấp.

  9. Bấm chuột phải vào ProductName và chọn Thêm vào Cấu trúc phân cấp. Chọn Thể loại Sản phẩm.

Giờ đây, khi bạn đã biết một vài cách khác nhau để tạo cấu trúc phân cấp, hãy sử dụng chúng trong PivotTable.

  1. Trở lại Excel.

  2. Trong Trang tính 1 (trang tính chứa PivotTable), hãy xóa bỏ các trường trong vùng Hàng.

  3. Thay thế các trường đó bằng cấu trúc phân cấp Thể loại Sản phẩm mới trong DimProduct.

  4. Tương tự, thay thế CalendarYear trong vùng Cột bằng cấu trúc phân cấp Ngày trong DimDate.

Bây giờ khi bạn khám phá dữ liệu, bạn sẽ dễ dàng thấy được lợi ích của việc sử dụng các cấu trúc phân cấp. Bạn có thể mở rộng và đóng riêng rẽ các vùng khác nhau của PivotTable, mang đến khả năng kiểm soát cao hơn đối với cách thức sử dụng không gian sẵn có. Hơn nữa, bằng cách thêm một cấu trúc phân cấp đơn vào cả Hàng và Cột, bạn có thể làm phong phú và tức thời truy sâu xuống mà không phải xếp chồng nhiều trường để đạt hiệu quả tương tự.

Ẩn các cột

Giờ đây khi bạn đã tạo cấu trúc phân cấp Thể loại Sản phẩm và đã đặt cấu trúc đó trong DimProduct, bạn không còn cần DimProductCategory hay DimProductSubcategory trong danh sách Trường PivotTable nữa. Trong tác vụ này, bạn sẽ tìm hiểu cách ẩn các bảng và các cột ngoại lai đang chiếm không gian trong trong danh sách Trường PivotTable. Bằng cách ẩn các bảng và cột, bạn cải thiện trải nghiệm báo cáo mà không ảnh hưởng đến mô hình cung cấp các mối quan hệ dữ liệu và các phép tính.

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Bạn có thể ẩn các cột riêng lẻ, một phạm vi cột hoặc toàn bộ bảng. Tên bảng và tên cột được tô xám để thể hiện rằng bảng hoặc cột đó được ẩn đối với các khách hàng báo cáo sử dụng mô hình. Các cột ẩn được tô xám trong mô hình để thể hiện trạng thái của chúng, nhưng vẫn nhìn thấy được trong Dạng xem Dữ liệu để bạn có thể tiếp tục làm việc với chúng.

  1. Trong Power Pivot, hãy đảm bảo rằng Dạng xem Dữ liệu được chọn.

  2. Trong các tab ở dưới cùng, hãy bấm chuột phải vào DimProductSubcategory và chọn Ẩn từ Công cụ Khách hàng.

  3. Lặp lại đối với ProductCategories.

  4. Mở DimProduct.

  5. Bấm chuột phải vào các cột sau đây và bấm Ẩn từ Công cụ Khách hàng:

    • ProductKey

    • ProductLabel

    • ProductSubcategory

  6. Chọn nhiều cột liền kề. Hãy bắt đầu với ClassID và tiếp tục đến ProductSubcategory ở phía cuối. Bấm chuột phải để ẩn chúng.

  7. Lặp lại đối với các bảng khác, xóa bỏ các ID, các khóa hoặc các thông tin khác mà bạn sẽ không sử dụng trong báo cáo này.

Trở về Excel tới Trang tính 1 với danh sách Trường PivotTable để xem sự khác biệt. Số lượng các bảng đã giảm xuống và DimProduct chỉ bao gồm các mục mà có nhiều khả năng là bạn sẽ sử dụng khi phân tích kinh doanh.

Tạo báo cáo Power View

Các báo cáo PivotTable không phải là kiểu báo cáo duy nhất được hưởng lợi từ Mô hình Dữ liệu. Bằng cách sử dụng cùng một mô hình mà bạn vừa dựng, bạn có thể thêm một trang tính Power View để thử một số bố cục mà Power View cung cấp.

  1. Trong Excel, bấm Chèn > Power View.

    Ghi chú    Nếu đây là lần đầu tiên bạn sử dụng Power View trên máy này, bạn sẽ được nhắc bật bổ trợ và cài đặt Silverlight trước tiên.

  2. Trong Trường Power View, hãy bấm vào mũi tên cạnh bảng FactSales, rồi bấm SalesAmount.

  3. Mở rộng bảng Địa lý và bấm RegionCountryName.

  4. Trong ruy-băng, bấm Bản đồ.

  5. Một báo cáo bản đồ xuất hiện. Kéo một góc để đổi kích cỡ bản đồ. Trên bản đồ, các vòng tròn màu xanh với kích cỡ khác nhau thể hiện hiệu quả kinh doanh ở các quốc gia hoặc khu vực khác nhau.

Tối ưu hóa báo cáo Power View

Thực hiện một vài thay đổi nhỏ đối với mô hình của bạn sẽ dẫn đến các phản hồi trực quan hơn khi thiết kế báo cáo Power View. Trong tác vụ này, bạn sẽ thêm URL trang web của một vài nhà sản xuất, rồi phân loại dữ liệu đó dưới dạng Web URL sao cho địa chỉ URL address hiện thị là một nối kết.

Bước đầu tiên, thêm URL vào sổ làm việc của bạn.

  1. Trong Excel, hãy mở một trang tính mới và sao chép các giá trị này:

ManufacturerURL

ManufacturerID

http://www.contoso.com

Contoso, LTD

http://www.adventure-works.com

Adventure Works

http://www.fabrikam.com

Fabrikam, Inc.

  1. Định dạng các ô dưới dạng bảng, rồi đặt tên URL bảng.

  2. Tạo một mối quan hệ giữa URL và bảng chứa tên nhà sản xuất, DimProduct:

    1. Bấm Dữ liệu > Mối quan hệ. Hộp thoại Tạo Mối quan hệ xuất hiện.

    2. Bấm Mới.

    3. Trong Bảng, chọn DimProduct.

    4. Trong Cột, chọn Nhà sản xuất.

    5. Trong Bảng Liên quan, chọn URL.

    6. Trong Cột Liên quan (Chính), chọn ManufacturerID.

Để so sánh các kết quả trước và sau, hãy bắt đầu một báo cáo Power View mới và thêm FactSales | SalesAmount, dimProduct | Nhà sản xuất và URL | ManufacturerURL vào báo cáo. Hãy lưu ý rằng URL thể hiện dưới dạng văn bản tĩnh.

Việc kết xuất một URL dưới dạng một siêu kết nối hiện hoạt sẽ yêu cầu phân loại. Để phân loại một cột, bạn hãy sử dụng Power Pivot.

  1. Trong Power Pivot, mở URL.

  2. Chọn ManufacturerURL.

  3. Bấm Nâng cao > Thuộc tính Báo cáo > Thể loại Dữ liệu: Chưa phân loại.

  4. Bấm mũi tên xuống.

  5. Chọn Web URL.

  6. Trong Excel, bấm Chèn > Power View.

  7. Trong Trường Power View, chọn FactSales | SalesAmount, dimProduct | Nhà sản xuất và URL | ManufacturerURL. Lần này, URL hiển thị dưới dạng siêu liên kết thực tế.

Các tối ưu hóa Power View khác bao gồm việc xác định một bộ trường mặc định cho mỗi bảng và thiết đặt các thuộc tính để xác định việc các hàng dữ liệu lặp lại sẽ được tổng hợp hoặc được liệt kê một cách độc lập. Hãy xem Cấu hình bộ trường mặc định cho các báo cáo Power View và Cấu hình các thuộc tính hành vi bảng cho các báo cáo Power View để biết thêm thông tin.

Tạo các trường được tính toán

Trong tác vụ thứ hai, Khám phá dữ liệu bằng cách sử dụng PivotTable, bạn đã bấm vào trường SalesAmount trong danh sách Trường PivotTable. Vì SalesAmount là cột dạng số, nên cột này được tự động đặt vào vùng Giá trị của PivotTable. Khi đó, tổng của SalesAmount sẵn sàng tính toán số lượng doanh thu đối với bất kỳ bộ lọc nào được áp dụng. Trong trường hợp này, lúc đầu không có bộ lọc nào, nhưng sau đó có CalendarYear, ProductSubcategoryName, và BrandName.

Điều bạn thực sự làm là tạo một trường tính toán ngầm, để dễ dàng hơn khi phân tích số lượng doanh thu từ bảng FactSales so với các trường khác, chẳng hạn như thể loại sản phẩm, khu vực và ngày. Các trường tính toán ngầm được Excel tạo ra khi bạn kéo một trường đến vùng Giá trị hoặc khi bạn bấm vào một trường dạng số, giống như bạn đã thao tác với trường SalesAmount. Các trường tính toán ngầm là các công thức sử dụng các hàm tổng hợp tiêu chuẩn như SUM, COUNT và AVERAGE, được tạo tự động cho bạn.

Ngoài ra còn có các kiểu trường được tính toán khác. Bạn có thể tạo các trường được tính toán rõ ràng trong Power Pivot. Không giống như trường được tính toán ngầm chỉ có thể được sử dụng trong PivotTable mà chúng đã được tạo ra, các trường được tính toán rõ ràng có thể được sử dụng ở bất kỳ PivotTable nào trong sổ làm việc, hoặc bằng bất kỳ báo cáo nào sử dụng Mô hình Dữ liệu như một nguồn dữ liệu. Với các trường được tính toán rõ ràng, được tạo trong Power Pivot, bạn có thể sử dụng tính năng Tự động Tính tổng để tự động tạo các trường được tính toán bằng cách sử dụng các phép tổng hợp tiêu chuẩn, hoặc bạn có thể tự tạo bằng cách sử dụng một công thức được tạo bằng Biểu thức Phân tích Dữ liệu (DAX).

Như bạn có thể hình dung, việc tạo các trường tính toán có thể giúp bạn phân tích dữ liệu của mình theo các cách bao quát và hiệu quả, vì vậy hãy bắt đầu tìm hiểu cách tạo chúng.

Tạo các trường được tính toán trong Power Pivot thật dễ dàng khi bạn sử dụng Tự động Tính tổng.

  1. Trong bảng FactSales, hãy bấm vào cột Lợi nhuận.

  2. Bấm Phép tính > Tự động Tính tổng. Hãy lưu ý rằng một trường tính toán mới có tên Tổng Lợi nhuận được tạo tự động trong ô trong Vùng Tính toán ngay phía dưới cột Lợi nhuận.

  3. Trong Excel, trong Trang tính 1, trong danh sách trường, trong FactSales, bấm Tổng Lợi nhuận.

Thế là xong! Đó là tất cả các bước để tạo trường được tính toán bằng cách sử dụng một phép tổng hợp tiêu chuẩn trong Power Pivot. Bạn có thể thấy, chỉ trong một vài phút, bạn đã tạo một trường được tính toán TỔNG Lợi nhuận và thêm trường đó vào PivotTable, giúp bạn dễ dàng hơn trong việc phân tích lợi nhuận phụ thuộc vào các bộ lọc được áp dụng. Trong trường hợp này, bạn có thể thấy Tổng Lợi nhuận được lọc theo các cấu trúc phân cấp Thể loại Sản phẩm và Ngày.

Nhưng nếu bạn cần phân tích chi tiết hơn, chẳng hạn như số lượng doanh thu đối với một kênh, sản phẩm hoặc thể loại cụ thể thì sao? Để làm điều đó, bạn sẽ cần tạo một trường tính toán khác để đếm số hàng, mỗi hàng cho một doanh số bán trong bảng FactSales, phụ thuộc vào các bộ lọc được áp dụng.

  1. Trong bảng FactSales, hãy bấm vào cột SalesKey.

  2. Trong Tính toán, hãy bấm mũi tên xuống trên Tự động Tính tổng > Đếm.

  3. Đổi tên trường tính toán mới bằng cách bấm chuột phải vào Đếm SalesKey trong vùng tính toán, rồi chọn Đổi tên. Gõ Đếm, rồi nhấn Enter.

  4. Trong Excel, trong Trang tính 1, trong danh sách trường, trong FactSales, hãy bấm Đếm.

Hãy lưu ý tới cột mới, cột Đếm được thêm vào PivotTable thể hiện số lượng doanh thu phụ thuộc vào các bộ lọc được áp dụng. Cũng giống như với trường tính toán Tổng Lợi nhuận, bạn thấy cột Đếm được lọc theo cấu trúc phân cấp Thể loại Sản phẩm và Ngày.

Hãy tạo một trường khác. Lần này, bạn sẽ tạo một trường tính toán để tính toán tỷ lệ phần trăm của tổng doanh thu đối với một ngữ cảnh hoặc bộ lọc cụ thể. Tuy nhiên, không giống như các trường tính toán trước đó mà bạn đã tạo ra bằng cách sử dụng Tự động Tính tổng, lần này bạn sẽ nhập công thức theo cách thủ công.

  1. Trong bảng FactSales, trong Vùng Tính toán, hãy bấm vào một ô trống. Mẹo: ô trên cùng bên trái là nơi rất tốt để bắt đầu đặt các trường tính toán của bạn. Làm như vậy sẽ giúp dễ dàng tìm kiếm các trường tính toán này. Bạn có thể di chuyển xung quanh bất kỳ trường tính toán nào trong Vùng Tính toán.

  2. Trong thanh công thức, hãy nhập và sử dụng IntelliSense để tạo công thức sau đây: Percentage of All Products:=[Count]/CALCULATE([Count], ALL(DimProduct))

  3. Nhấn ENTER để chấp nhận công thức.

  4. Trong Excel, trong Trang tính 1, trong danh sách trường, trong FactSales, bấm Phần trăm Tất cả Sản phẩm.

  5. Trong PivotTable, hãy chọn nhiều cột Phần trăm của Tất cả Sản phẩm.

  6. Trên tab Trang đầu , bấm Số > Phần trăm. Hãy dùng hai vị trí thập phân để định dạng mỗi cột mới.

Điều mà trường tính toán mới này làm là nó tính toán phần trăm tổng số doanh thu đối với một ngữ cảnh lọc nhất định. Trong trường hợp này, ngữ cảnh lọc của chúng ta vẫn là các cấu trúc phân cấp Thể loại Sản phẩm và Ngày. Ví dụ, bạn có thể thấy số lượng máy tính dưới dạng tỷ lệ phần trăm tổng sản phẩm bán ra đã tăng qua các năm.

Việc tạo công thức cho các cột được tính toán và các trường được tính toán sẽ khá dễ dàng đối với bạn nếu bạn đã quen với việc tạo công thức Excel. Dù bạn có thành thạo các công thức Excel hay không, thì một nơi tuyệt vời để tìm hiểu các thông tin cơ bản của công thức DAX là các bước trong các bài học Bắt đầu nhanh: Tìm hiểu những điều Cơ bản về DAX trong 30 Phút..

Lưu tài liệu của bạn

Hãy lưu sổ làm việc để bạn có thể sử dụng nó với các hướng dẫn khác hoặc để khám phá thêm.

Bước tiếp theo

Mặc dù bạn có thể dễ dàng nhập dữ liệu từ Excel nhưng sử dụng bổ trợ Power Pivot để nhập thường sẽ nhanh hơn và hiệu quả hơn. Bạn có thể lọc dữ liệu mà bạn đang nhập, loại trừ các cột mà bạn không cần. Bạn cũng có thể chọn sẽ dùng bộ dựng truy vấn hay dùng lệnh truy vấn để truy xuất dữ liệu. Bước tiếp theo, tìm hiểu về các phương pháp thay thế này: Lấy dữ liệu từ một nguồn cấp dữ liệu trong Power Pivot và Nhập Dữ liệu từ Analysis Services hoặc Power Pivot.

Báo cáo Power View được thiết kế để hoạt động với các Mô hình Dữ liệu tương tự như mô hình bạn mới tạo. Hãy đọc tiếp để tìm hiểu thêm về tính năng trực quan hóa dữ liệu phong phú mà Power View mang đến cho Excel: Khởi động Power View trong Excel 2013 và Power View: Khám phá, trực quan hóa và trình bày dữ liệu của bạn.

Hãy tìm cách cải thiện Mô hình Dữ liệu để có được các báo cáo Power View tốt hơn bằng cách làm theo Hướng dẫn này: Tối ưu hóa Mô hình Dữ liệu của bạn cho báo cáo Power View

Áp dụng Cho: Excel 2013



Thông tin này có hữu ích không?

Không

Chúng tôi có thể cải thiện như thế nào?

255 ký tự còn lại

Để bảo vệ sự riêng tư của bạn, vui lòng không đưa thông tin liên hệ trong phản hồi của bạn. Xem xét chính sách về quyền riêng tư.

Cảm ơn phản hồi của bạn!

Tài nguyên hỗ trợ

Thay đổi ngôn ngữ