Cho đến nay, chúng tôi tập trung hoàn toàn vào các bảng tổng hợp được tạo từ một bảng dữ liệu duy nhất trên trang web này. Mô hình dữ liệu tính năng này đã mang lại lợi thế bổ sung cho biểu đồ trục. Với Mô hình dữ liệu , chúng ta có thể sử dụng nhiều bảng dữ liệu trong một bảng tổng hợp. Chúng ta phải tạo một hoặc nhiều "mối quan hệ bảng" để dữ liệu có thể được liên kết với nhau. Mô hình Dữ liệu là một tính năng mới được giới thiệu trong Excel 2013. Trong bài viết này, chúng tôi sẽ hướng dẫn bạn 6 bước để tạo Mô hình Dữ liệu bảng tổng hợp trong Excel.
Quy trình từng bước để tạo mô hình dữ liệu bảng tổng hợp trong Excel
Hình sau cho thấy các phần của ba bảng nằm trong một sổ làm việc. Mỗi trang tính nằm trong cùng một sổ làm việc, chỉ được hiển thị trong một cửa sổ riêng biệt. Các trang tính là Đơn hàng , Khách hàng và Khu vực . Mỗi trang tính đều có một bảng và chúng tôi cũng đã đặt tên cho 3 bảng này là Đơn hàng , Khách hàng và Khu vực (để giữ cho nó có tổ chức). Đơn đặt hàng bảng chứa thông tin về đơn đặt hàng sản phẩm. Khách hàng bảng chứa thông tin về khách hàng của công ty và Khu vực bảng chứa thông tin khu vực.
Nếu bạn nhìn kỹ các bảng, bạn sẽ thấy rằng Đơn hàng và Khách hàng bảng có ID khách hàng chung và cột Khách hàng và Khu vực bảng có Trạng thái cột chung (tải xuống tệp làm việc để xem những điểm chung) . Chúng tôi sẽ sử dụng các cột chung này để tạo mối quan hệ giữa các bảng.
Đọc thêm: Tạo bảng tổng hợp Excel theo cách thủ công
Một đến nhiều mối quan hệ
Lưu ý rằng mối quan hệ giữa các bảng là “một-nhiều”. Đối với mọi hàng trong Đơn hàng , có chính xác một hàng tương ứng trong Khách hàng và hàng đó được xác định bởi ID khách hàng cột. Tương tự, đối với mọi hàng trong Khách hàng , có chính xác một hàng tương ứng trong Khu vực bảng và hàng đó được xác định bởi Trạng thái cột.
💡 Ghi chú: Bảng tổng hợp được tạo bằng Mô hình dữ liệu có một số hạn chế khi so sánh với bảng tổng hợp được tạo từ một bảng dữ liệu. Điều đáng chú ý nhất là:bạn không thể tạo nhóm . Ngoài ra, bạn không thể tạo các trường được tính toán hoặc các mục được tính toán.
Mục tiêu của chúng tôi trong ví dụ này là tổng kết doanh số bán hàng theo tiểu bang, theo khu vực và theo năm. Lưu ý rằng thông tin bán hàng và ngày có trong Đơn đặt hàng , thông tin trạng thái nằm trong Khách hàng bảng và tên khu vực nằm trong Khu vực bàn. Do đó, chúng tôi sẽ sử dụng tất cả ba bảng này để tạo bảng tổng hợp mục tiêu của chúng tôi.
Đây là quy trình từng bước mà chúng tôi đã sử dụng để tạo bảng tổng hợp:
Bước 1:Chèn PivotTable
Ở bước đầu tiên, chúng tôi sẽ chèn một bảng tổng hợp từ tập dữ liệu. Chọn bất kỳ ô nào trong Đơn hàng bảng và chọn Chèn ➪ PivotTable ➪ Từ Bảng / Phạm vi . Tạo PivotTable hộp thoại sẽ xuất hiện.
- Đầu tiên, chúng tôi đã chọn phạm vi ô C5 trong Đơn đặt hàng trang tính.
- Thứ hai, từ Chèn tab ➪ PivotTable ➪ chọn Từ Bảng / Phạm vi .
Bước 2:Thêm dữ liệu vào mô hình dữ liệu
Chọn dữ liệu bạn muốn phân tích và chọn vị trí bạn muốn đặt báo cáo PivotTable - hai tùy chọn này sẽ vẫn như cũ. Chọn Thêm dữ liệu này vào mô hình dữ liệu và nhấp vào OK .
- Vì vậy, một hộp thoại sẽ bật lên.
- Sau đó, chọn “ Thêm dữ liệu này vào Mô hình Dữ liệu ”.
- Sau đó, nhấn OK .
- Nếu bạn xem Trường PivotTable ngăn tác vụ (ở bên phải của trang tính mới được tạo), bạn sẽ thấy rằng nó hơi khác một chút vì lần này chúng tôi đã chọn làm việc với Mô hình dữ liệu .
- Ngăn Tác vụ chứa hai tab: Hoạt động và Tất cả . Hoạt động tab chỉ liệt kê Đơn hàng bảng và Tất cả tab liệt kê tất cả các bảng trong sổ làm việc.
- Bạn có thể chọn bất kỳ bảng nào trong mục Tất cả vào tab Hoạt động. Để đặt Khách hàng trong tab Hoạt động, kích hoạt Tất cả , nhấp chuột phải vào Khách hàng và chọn Hiển thị trong tab đang hoạt động từ các tùy chọn. Sau đó, thực hiện tương tự đối với Khu vực bảng.
- Đây hoàn toàn là tùy chọn, sau này chúng tôi sẽ chọn các trường từ Al tab l.
- Tiếp theo, hình sau cho thấy Đang hoạt động tab Trường PivotTable bảng công tác. Các bảng Khách hàng và Khu vực được mở rộng để hiển thị các tiêu đề cột (tên trường). Chúng tôi cũng đã thay đổi cấu hình của ngăn tác vụ (bố cục ngăn tác vụ). Để thực hiện thay đổi này, hãy nhấp vào Công cụ và từ trình đơn thả xuống, tôi đã chọn Phần trường và Phần khu vực cạnh nhau .
Đọc thêm: Cách tạo bảng tổng hợp để phân tích dữ liệu có ý nghĩa!
Bước 3:Quản lý các mối quan hệ
Bây giờ, chúng ta sẽ thiết lập mối quan hệ giữa các bảng. Chọn Công cụ PivotTable ➪ Phân tích ➪ Tính toán ➪ Mối quan hệ . Quản lý mối quan hệ hộp thoại sẽ xuất hiện.
- Sau đó, chọn bất kỳ vị trí nào bên trong bảng tổng hợp mới.
- Sau đó, từ Phân tích PivotTable , chọn Mối quan hệ .
- Do đó, một hộp thoại khác sẽ xuất hiện.
- Tiếp theo, nhấp vào Mới .
Bài đọc tương tự
- Sao chép Bảng Tổng hợp Excel!
- Cách sửa đổi một bảng tổng hợp Excel
- Định dạng Bảng Tổng hợp Excel (Hướng dẫn Cơ bản)
- 8 Ví dụ về Bảng Pivot trong Excel - Cách tạo PivotTable!
- Tạo Bảng Tổng hợp Tự động
Bước 4:Tạo mối quan hệ
Trong Bảng thả xuống, chọn Đơn hàng và trong Cột (Nước ngoài), chọn ID khách hàng ; trong Bảng liên quan , chọn Khách hàng và trong Cột Liên quan (Chính) , chọn ID khách hàng .
- Sau đó, một hộp thoại khác sẽ bật lên.
- Sau đó, chọn những điều sau:
- Bảng:Bảng Mô hình Dữ liệu:Đơn hàng
- Bảng Liên quan:Bảng Bảng:Khách hàng
- Cột (Nước ngoài):CustomerID
- Cột Liên quan (Chính):CustomerID
- Tiếp theo, nhấp vào OK và bạn sẽ được quay lại trang Quản lý mối quan hệ hộp thoại.
- Sau đó, nhấp vào Mới lại. Bây giờ chúng ta sẽ tạo mối quan hệ giữa Khách hàng bảng và Khu vực như hình sau.
- Bảng:Bảng Mô hình Dữ liệu:Khách hàng
- Bảng Liên quan:Bảng Bảng:Khu vực
- Cột (Nước ngoài):Trạng thái
- Cột Liên quan (Chính):Trạng thái
- Sau đó, nhấn OK .
- Vì vậy, Quản lý mối quan hệ bây giờ hộp thoại sẽ hiển thị hai mối quan hệ này.
💡 Ghi chú: Nếu bạn quên thiết lập trước các mối quan hệ bảng, Excel sẽ nhắc bạn làm như vậy khi bạn cố gắng thêm một trường vào bảng tổng hợp từ một bảng dữ liệu khác.
Đọc thêm: Cách nhập dữ liệu vào PowerPivot &tạo bảng Pivot / biểu đồ tổng hợp
Bước 5:Chèn các trường PivotTable vào các khu vực
Chúng tôi đã thiết lập một mối quan hệ bảng. Bây giờ, chỉ cần kéo tên trường vào các vùng thích hợp của Trường PivotTable ngăn tác vụ:
- Trước tiên, hãy kéo trường Tổng vào vùng Giá trị.
- Thứ hai, di chuyển trường Năm sang vùng Cột.
- Thứ ba, kéo trường Vùng vào vùng Hàng.
- Thứ tư, di chuyển trường StateName sang vùng Rows.
- Sau đó, hình sau hiển thị một phần của bảng tổng hợp.
Bước 6:Thêm lát cắt
Trong bước cuối cùng này, chúng tôi đã thêm hai bộ cắt ( Danh sách thư và Sản phẩm ) để cho phép lọc bảng tổng hợp theo những khách hàng có trong danh sách gửi thư và các sản phẩm phụ.
- Để bắt đầu, hãy chọn bất kỳ vị trí nào bên trong bảng tổng hợp.
- Sau đó, từ Phân tích PivotTable , chọn Chèn các lát cắt .
- Sau đó, từ Tất cả , chọn những thứ sau:
- Khách hàng:MaiList.
- Đơn hàng:Sản phẩm.
- Sau đó, nhấn OK .
- Cuối cùng, sau khi làm điều đó, bảng tổng hợp cuối cùng sẽ trông như thế này.
💡 Mẹo: Bạn có thể chuyển đổi bảng tổng hợp thành công thức. Để thực hiện việc này, hãy chọn bất kỳ ô nào trong bảng tổng hợp và chọn Công cụ PivotTable ➪ Phân tích ➪ Công cụ OLAP ➪ Chuyển đổi sang công thức . Bảng tổng hợp sẽ được thay thế bằng các ô sử dụng công thức. Các công thức này được tạo bằng CUBEMEMBER và CUBEVALUE chức năng. Mặc dù phạm vi dữ liệu mới sẽ không còn là bảng tổng hợp nữa, nhưng các công thức sẽ cập nhật khi dữ liệu thay đổi.
Đọc thêm: Cách lọc bảng tổng hợp Excel bằng các lát cắt!
Kết luận
Chúng tôi đã chỉ cho bạn 6 bước đơn giản để tạo Mô hình dữ liệu bảng tổng hợp trong Excel . Nếu bạn có bất kỳ câu hỏi nào, đừng ngại comment bên dưới. Chúc bạn xuất sắc :).
Các bài viết liên quan
- Cách Hiển thị Giá trị Bằng không trong Bảng Pivot Excel:2 Mẹo Chuyên nghiệp
- Bảng tổng hợp đảo ngược - Bỏ chia dữ liệu tóm tắt
- Cách tạo báo cáo bảng tổng hợp trong Excel
- Hướng dẫn từng bước trong Excel Pivot Table cho Dummies | Tải xuống PDF
- Cách tạo Dòng thời gian trong Excel để Lọc Bảng tổng hợp!