Unlimited WordPress themes, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Computer Skills
  2. Microsoft Excel
Computers

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

by
Difficulty:AdvancedLength:LongLanguages:

Vietnamese (Tiếng Việt) translation by Dai Phong (you can also view the original English article)

Khi bạn muốn tạo một PivotTable, bạn sẽ làm gì nếu bạn có dữ liệu trong các sheet khác nhau? Nếu bạn đang sử dụng Excel 2013, thì bạn có một phương pháp được tổ chức hợp lý để thực hiện 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 dữ liệu quan hệ theo cách cơ sở dữ liệu làm việ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ừ dữ 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.

Customer Info sheet
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.

Order Info sheet
Sheet Order Info

Nhấp vào sheet Payment Info và xem nó có chứa số đơn hàng, giá mỗi đơn hàng, phương thức thanh toán và đơn hàng được đặt bởi một khách hàng mới hay khách hàng cũ.

Payment Info sheet
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, 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.

creating a table from existing data
Chuyển đổi dữ liệu trên một bảng tính bằng cách chọn Insert > Table

Hộp thoại Create Table xác định chính xác khu vực của bảng. Hộp đánh dấu ở phía dưới cũng sẽ xác định rằng hàng đầu tiên của bảng là dành cho tiêu đề. (Nếu không, hãy chọn tùy chọn đó.)

Create Table dialog box
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.

applying a name to a table
Á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_InfoPayment_Info.

Bây giờ chúng ta đã 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).

inserting the PivotTable
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.

add data to the Data Model
Adding the data to the Data Model là thứ cho phép các kết nối hoạt động

Bạ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, 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.

creating a table relationship
Có ba bảng, từ đó tạo ra hai mối quan hệ

Điều này có nghĩa là các bảng Payment_Info và Customer_Info có liên 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 quá trình này để tạo ra một mối quan hệ kết nối Payment_Info với Order_Info, tương tự sử dụng trường Order #. Hộp Manage Relationships bây giờ sẽ giống như sau:

list of all relationships
Hộp thoại Manage Relationships sẽ hiển thị các mối quan hệ mà bạn tạo ra

Lư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 kết nối thông 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:

  • StateMonth vào ROWS
  • Product vào COLUMNS
  • $ Sale vào VALUES
  • Status vào FILTERS
final PivotTable
Kéo các trường từ mỗi bảng vào trong bảng PivotTable

Bây giờ bạ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 có thể chọn các trường từ nhiều sheet để tạo một PivotTable thống nhất. Hãy nhớ rằng các hàng của mỗi bảng cần liên quan đến nhau theo một cách nào đó. Bạn sẽ có cơ hội thành công cao nhất khi các bảng có một trường chung với các giá trị duy nhất.

Nếu bạn đang tìm kiếm các cách tối ưu để trình bày dữ liệu của mình, thì Envato Market có nhiều template Excel và PowerPoint, cũng như các script và ứng dụng để chuyển đổi dữ liệu Excel sang các định dạng thân thiện với web và ngược lại.

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.