Cho đến nay, chúng ta chỉ tập trung vào Pivot Table tạo từ một bảng dữ liệu đơn lẻ. Tính năng Data Model (Mô hình dữ liệu) mang来 thêm lợi ích cho Pivot Chart. Với Data Model, chúng ta có thể sử dụng nhiều bảng dữ liệu trong một Pivot Table duy nhất. Chúng ta phải tạo một hoặc nhiều "mối quan hệ bảng" (table relationships) để dữ liệu được liên kết với nhau. Data Model là tính năng mới giới thiệu trong Excel 2013. Trong bài này, chúng ta sẽ chỉ cho bạn 6 bước tạo Pivot Table Data Model trong Excel.
Các bước tạo Pivot Table Data Model trong Excel
Hình sau显示部分 của ba bảng trong cùng một workbook. Mỗi trang tính trong cùng workbook, chỉ hiển thị riêng cửa sổ. Ba trang tính là Orders (Đơn hàng), Customers (Khách hàng), và Regions (Khu vực). Mỗi trang tính có một bảng, và chúng ta cũng đặt tên 3 bảng này là Orders, Customers, Regions (để tổ chức). Bảng Orders chứa thông tin đơn hàng sản phẩm. Bảng Customers chứa thông tin khách hàng, bảng Regions chứa thông tin khu vực.
Nếu nhìn kỹ các bảng, bạn sẽ thấy Orders và Customers có chung cột CustomerID, và Customers với Regions có chung cột State (tải file làm việc để thấy rõ điểm chung). Chúng ta sẽ dùng các cột chung này để thiết lập mối quan hệ giữa các bảng.
Xem thêm: Tạo Pivot Table Excel thủ công
Mối quan hệ Một - Nhiều (One to Many Relationships)
Chú ý mối quan hệ giữa các bảng là "một-nhiều". Với mỗi hàng trong bảng Orders, có đúng một hàng tương ứng trong Customers, xác định bởi cột CustomerID. Tương tự, mỗi hàng trong Customers có đúng một hàng tương ứng trong Regions, xác định bởi cột State.
💡 Lưu ý: Pivot Table tạo bằng Data Model có một số hạn chế so với Pivot Table từ một bảng dữ liệu đơn lẻ. Hạn chế nổi bật nhất: không thể tạo nhóm (groups). Ngoài ra, không thể tạo calculated fields (trường tính toán) hoặc calculated items (mục tính toán).
Mục tiêu ví dụ này là tóm tắt doanh số theo bang (state), theo khu vực (region) và theo năm. Lưu ý thông tin doanh số và ngày ở bảng Orders, thông tin bang ở Customers, tên khu vực ở Regions. Do đó cần dùng cả 3 bảng để tạo Pivot Table mục tiêu.
Dưới đây là quy trình từng bước:
Bước 1: Chèn PivotTable
Bước 1: chèn Pivot Table từ tập dữ liệu. Chọn bất kỳ ô nào trong bảng Orders → chọn Insert ➪ PivotTable ➪ From Table/Range. Hộp thoại Create PivotTable xuất hiện.
- Chọn ô C5 trong sheet Orders.
- Tab Insert → PivotTable → chọn From Table/Range.
Bước 2: Thêm dữ liệu vào Data Model
Chọn dữ liệu muốn phân tích và vị trí đặt báo cáo PivotTable – hai tùy chọn này giữ nguyên. Chọn ô kiểm Add This Data to the Data Model (Thêm dữ liệu này vào Mô hình dữ liệu), nhấp OK.
- Hộp thoại bật lên.
- Chọn "Add this data to the Data Model".
- Nhấn OK.
- Nhìn khung tác vụ PivotTable Fields (bên phải trang tính mới), nó hơi khác vì lần này chọn làm việc với Data Model.
- Khung tác vụ có 2 tab: Active và All. Tab Active chỉ liệt kê bảng Orders, tab All liệt kê tất cả bảng trong workbook.
- Bạn có thể kéo bảng từ tab All sang tab Active. Để đưa Customers vào Active: kích hoạt tab All, click chuột phải Customers → chọn Show in Active Tab. Làm tương tự cho Regions.
- Bước này tùy chọn, sau chúng ta sẽ chọn trường từ tab All.
- Hình sau显示 tab Active của PivotTable Fields. Bảng Customers và Regions mở rộng hiện tên cột (tên trường). Chúng ta cũng thay đổi cấu hình khung tác vụ (layout). Để làm vậy, nhấp điều khiển Tools, từ menu thả xuống chọn Fields Section and Areas Section Side-by-Side.
Xem thêm: Cách tạo Pivot Tables cho phân tích dữ liệu có ý nghĩa!
Bước 3: Quản lý mối quan hệ (Managing Relationships)
Thiết lập mối quan hệ giữa các bảng. Chọn PivotTable Tools ➪ Analyze ➪ Calculations ➪ Relationships. Hộp thoại Manage Relationships xuất hiện.
- Chọn bất kỳ đâu trong Pivot Table mới.
- Tab PivotTable Analyze → chọn Relationships.
- Hộp thoại khác xuất hiện.
- Nhấp New.
Bài đọc tương tự
- Sao chép Pivot Table Excel!
- Cách sửa đổi Pivot Table Excel
- Định dạng Pivot Table Excel (Hướng dẫn hoàn chỉnh)
- 8 ví dụ Pivot Table Excel – Cách tạo PivotTable!
- Tạo Pivot Table tự động
Bước 4: Tạo mối quan hệ (Creating Relationship)
Trong Table drop-down chọn Orders, Column (Foreign) chọn CustomerID; Related Table chọn Customers, Related Column (Primary) chọn CustomerID.
- Hộp thoại khác bật lên.
- Chọn các mục sau:
- Table: Data Model Table: Orders
- Related Table: Worksheet Table: Customers
- Column (Foreign): CustomerID
- Related Column (Primary): CustomerID
- Nhấp OK → quay lại Manage Relationships.
- Nhấp New lại. Tạo quan hệ giữa Customers và Regions như hình.
- Table: Data Model Table: Customers
- Related Table: Worksheet Table: Regions
- Column (Foreign): State
- Related Column (Primary): State
- Nhấn OK.
- Hộp thoại Manage Relationships giờ显示 2 mối quan hệ.
💡 Lưu ý: Nếu quên thiết lập quan hệ bảng trước, Excel sẽ nhắc bạn làm khi bạn thử thêm trường vào Pivot Table từ bảng dữ liệu khác.
Xem thêm: Cách nhập dữ liệu vào PowerPivot & Tạo Pivot Table/Pivot Chart
Bước 5: Kéo thả trường PivotTable vào các vùng
Đã thiết lập quan hệ bảng. Kéo tên trường đến vùng thích hợp trong PivotTable Fields:
- Kéo trường Total vào vùng Values.
- Kéo trường Year vào vùng Columns.
- Kéo trường Region vào vùng Rows.
- Kéo trường StateName vào vùng Rows.
- Hình sau显示 phần của Pivot Table.
Bước 6: Thêm Slicers (Bộ lọc)
Bước cuối, thêm 2 slicers (MailList và Product) để lọc Pivot Table theo khách hàng trong danh sách thư và theo sản phẩm.
- Chọn bất kỳ đâu trong Pivot Table.
- Tab PivotTable Analyze → chọn Insert Slicers.
- Tab All → chọn:
- Customers: MailList.
- Orders: Product.
- Nhấn OK.
- Sau khi làm xong, Pivot Table cuối cùng trông như sau.
💡Mẹo: Bạn có thể chuyển Pivot Table sang công thức. Chọn bất kỳ ô nào trong Pivot Table → PivotTable Tools ➪ Analyze ➪ OLAP Tools ➪ Convert to Formulas. Pivot Table sẽ được thay thế bằng ô dùng công thức. Các công thức này tạo bởi hàm CUBEMEMBER và CUBEVALUE. Mặc dù phạm vi mới không còn là Pivot Table, công thức sẽ cập nhật khi dữ liệu thay đổi.
Xem thêm: Cách lọc Pivot Table Excel với Slicers!
Kết luận
Chúng tôi đã chỉ cho bạn 6 bước dễ dàng tạo Pivot Table Data Model trong Excel. Nếu có thắc mắc, hãy bình luận bên dưới. Happy Excelling :).
Bài viết liên quan
- Cách hiển thị giá trị 0 trong Pivot Table Excel: 2 mẹo chuyên nghiệp
- Reverse Pivot Tables – Unpivot Summary Data
- Cách tạo báo cáo Pivot Table trong Excel
- Excel Pivot Table Tutorials for Dummies Step by Step | Download PDF
- Cách tạo Timeline trong Excel để lọc Pivot Tables!