PivotTable Nâng cao: Kết hợp Dữ liệu từ nhiều Sheet

Khi bạn muốn tạo một PivotTable, bạn sẽ làm gì nếu bạn có tài liệu trong những sheet khác nhau ? Nếu bạn đang sử dụng Excel 2013, thì bạn có một giải pháp được tổ chức triển khai hài hòa và hợp lý để triển khai việc này. Có một kỹ thuật được gọi là Mô hình Dữ liệu và nó sử dụng tài liệu quan hệ theo cách cơ sở tài liệu thao tác .
Trong bài này, tôi sẽ hướng dẫn cho bạn mọi thứ mà bạn cần để tạo một PivotTable trong Excel 2013 từ tài liệu trong nhiều sheet, sử dụng Mô hình Dữ liệu .

Video Hướng dẫn

Bạn có thể làm theo hướng dẫn này bằng cách sử dụng tập tin Excel của riêng bạn. Hoặc nếu bạn thích, hãy tải xuống tập tin zip kèm theo hướng dẫn này, nó có chứa một bảng tính mẫu có tên gọi là Pivot Consolidate.xlsx.

Kiểm tra Dữ liệu

Bảng tính này có ba sheet: Customer Info, Order Info và Payment Info.

Nhấp vào sheet Customer Info và xem nó có chứa số đơn hàng và tên và trạng thái của khách hàng.

Sheet Customer Info

Nhấp vào bảng Order Info và xem nó có chứa số đơn hàng, cũng như các trường ngày tháng, các sản phẩm được đặt, và các sản phẩm có phải là hữu cơ hay không.

Sheet Order InfoNhấp vào sheet Payment Info và xem nó có chứa số đơn hàng, giá mỗi đơn hàng, phương pháp giao dịch thanh toán và đơn hàng được đặt bởi một người mua mới hay người mua cũ .
Sheet Payment Info

Bằng cách kết nối tất cả các sheet này trong bảng tác vụ của PivotTable, chúng ta có thể chọn dữ liệu từ mỗi sheet. Vì số đơn hàng tồn tại trong cả ba sheet, nên chúng sẽ trở thành các điểm kết nối. Cơ sở dữ liệu gọi đây là một primary key (khoá chính). Lưu ý rằng không phải lúc nào cũng cần thiết phải có một khóa chính, nhưng nó làm giảm khả năng xảy ra lỗi.

Tạo bảng

Trước khi tạo ra PivotTable, tất cả chúng ta hãy tạo ra một bảng từ mỗi sheet .

Quay trở lại Bảng Khách hàng, sau đó nhấp vào bất kỳ nơi nào trong vùng dữ liệu. Chuyển đến tab Insert trên thanh ribbon, sau đó nhấp vào biểu tượng Table.

Chuyển đổi dữ liệu trên một bảng tính bằng cách chọn Insert > TableHộp thoại Create Table xác lập đúng chuẩn khu vực của bảng. Hộp lưu lại ở phía dưới cũng sẽ xác lập rằng hàng tiên phong của bảng là dành cho tiêu đề. ( Nếu không, hãy chọn tùy chọn đó. )
Hộp thoại Create Table sẽ lấy chính xác vùng dữ liệu

Nhấp OK, và bây giờ bạn có một bảng với các nút lọc dữ liệu. Bạn có thể nhấp vào bên trong nó để bỏ chọn, nếu bạn muốn xem nó rõ hơn (chỉ cần không bấm bên ngoài bảng). Thanh ribbon cũng hiển thị tab Design cho bảng. Ở phía bên trái của dải ribbon, hộp Table Name hiển thị tên tạm thời của Table 1. Xóa nó, và gọi nó là Customer_Info (sử dụng một dấu gạch dưới thay vì một khoảng trắng). Sau đó nhấn Enter.

Áp dụng một cái tên cho mỗi bảng

Lặp lại quy trình này trên sheet Order Info và Payment Info. Đặt tên các bảng tương ứng là Order_Info và Payment_Info.

Bây giờ tất cả chúng ta đã chuẩn bị sẵn sàng để thêm PivotTable .

Chèn PivotTable

Trên sheet Payment Info, đảm bảo con trỏ ở đâu đó trong bảng. Quay trở lại tab Insert trên ribbon, và nhấp vào biểu tượng PivotTable (đó là biểu tượng đầu tiên).

Với con trỏ bên trong một trong các bảng, chọn Insert > PivotTable

Hộp thoại xuất hiện sẽ xác định bảng phù hợp và chọn PivotTable sẽ nằm trong một bảng tính mới. Ở phía dưới cùng, nhấp vào hộp đánh dấu Add this data to the Data Model. Sau đó bấm OK.

Adding the data to the Data Model là thứ cho phép các kết nối hoạt độngBạn sẽ có một PivotTable trên một sheet mới, sẽ có một cửa sổ tác vụ ở phía bên phải màn hình hiển thị, và thanh ribbon sẽ hiển thị tab Analyze .

Cửa sổ tác vụ hiển thị bảng và các trường chỉ cho sheet hiện tại, do đó hãy nhấp vào ALL để thấy tất cả các bảng mà bạn đã tạo. Nhưng trước khi có thể sử dụng chúng, chúng ta phải kết nối chúng lại với nhau, và điều đó có nghĩa là tạo ra các mối quan hệ. Nhấp vào nút Relationships trên thanh ribbon.

Thiết lập Quan hệ Bảng

Nhấp vào nút đó sẽ hiển thị hộp thoại Manage Relationships. Nhấp vào nút New và hiển thị hộp thoại Create Relationships. Chúng ta sẽ tạo ra hai mối quan hệ bằng cách sử dụng trường Order # làm yếu tố kết nối.

Từ danh sách sổ xuống, chọn Payment_Info cho table, và bên cạnh nó, chọn Order # từ trình đơn thả xuống Column. Trên hàng thứ hai, chọn Customer_Info từ danh sách sổ xuống Related Table và bên cạnh nó, chọn Order # từ danh sách Related Column.

Có ba bảng, từ đó tạo ra hai mối quan hệĐiều này có nghĩa là những bảng Payment_Info và Customer_Info có tương quan khi chúng có số đơn hàng khớp nhau .

Nhấp vào OK và chúng ta thấy rằng mối quan hệ được liệt kê trong hộp Manage Relationships.

Lặp lại quy trình này để tạo ra một mối quan hệ liên kết Payment_Info với Order_Info, tựa như sử dụng trường Order #. Hộp Manage Relationships giờ đây sẽ giống như sau :
Hộp thoại Manage Relationships sẽ hiển thị các mối quan hệ mà bạn tạo raLưu ý rằng không cần phải tạo mối quan hệ giữa bảng Order_Info và Customer_Info, vì chúng được tự động hóa liên kết trải qua bảng Payment_Info .

Nhấp vào nút Close ở dưới cùng của cái hộp. Bây giờ cuối cùng chúng ta có thể kéo các trường vào bảng PivotTable.

Chèn các Trường vào Bảng PivotTable

Trong phần ALL của bảng tác vụ, nhấp vào các mũi tên nhỏ để thấy cả ba bảng, để bạn thấy các trường của chúng. Kéo các trường vào các vùng PivotTable như sau:

• State và Month vào ROWS
• Product vào COLUMNS
• $ Sale vào VALUES
• Status vào FILTERS

Kéo các trường từ mỗi bảng vào trong bảng PivotTableBây giờ bạn hoàn toàn có thể sử dụng và sửa đổi điều này giống như bất kể PivotTable khác .

Phần tóm tắt

Sử dụng tính năng Mô hình Dữ liệu Đối tượng mới trong Excel 2013, bạn hoàn toàn có thể chọn những trường từ nhiều sheet để tạo một PivotTable thống nhất. Hãy nhớ rằng những hàng của mỗi bảng cần tương quan đến nhau theo một cách nào đó. Bạn sẽ có thời cơ thành công xuất sắc cao nhất khi những bảng có một trường chung với những giá trị duy nhất .
Nếu bạn đang tìm kiếm những cách tối ưu để trình diễn tài liệu của mình, thì Envato Market có nhiều template Excel và PowerPoint, cũng như những script và ứng dụng để quy đổi tài liệu Excel sang những định dạng thân thiện với web và ngược lại .