Tạo mối quan hệ giữa hai bảng trong Excel

Bạn đã bao giờ dùng hàm VLOOKUP để đưa cột từ một bảng sang một bảng khác? Giờ đây khi Excel có Mô hình Dữ liệu dựng sẵn, thì VLOOKUP đã trở nên lỗi thời. Bạn có thể tạo một mối quan hệ giữa hai bảng dữ liệu, dựa vào những dữ liệu khớp nhau trong từng bảng. Sau đó, bạn có thể tạo trang Power View, dựng PivotTable và các báo cáo khác với các trường từ mỗi bảng, ngay cả khi các bảng đó xuất phát từ các nguồn khác nhau. Ví dụ, nếu bạn có dữ liệu về doanh số bán hàng theo khách hàng, bạn có thể muốn nhập và liên kết dữ liệu hiển thị thời gian thông minh để phân tích các mẫu hình doanh số bán hàng theo năm và tháng.

Tất cả các bảng trong sổ làm việc đều được liệt kê trong các danh sách Trường PivotTable và Power View.

Trình duyệt của bạn không hỗ trợ video. Hãy cài đặt Microsoft Silverlight, Adobe Flash Player hoặc Internet Explorer 9.

Khi bạn nhập các bảng có liên quan từ một cơ sở dữ liệu có quan hệ, Excel thường có thể tạo những mối quan hệ này trong Mô hình Dữ liệu mà nó đang xây dựng ở hậu trường. Trong tất cả các trường hợp khác, bạn sẽ cần tạo mối quan hệ theo cách thủ công.

Hãy bảo đảm sổ làm việc có chứa tối thiểu hai bảng và mỗi bảng đều có một cột có thể được ánh xạ tới một cột trong bảng kia.
Thực hiện một trong các thao tác sau: Định dạng dữ liệu dưới dạng bảng hoặc Nhập dữ liệu ngoài dưới dạng bảng trong trang tính mới.
Đặt cho mỗi bảng một tên gọi có ý nghĩa: Trong Công cụ Bảng, bấm Thiết kế > Tên Bảng > nhập tên.
Xác nhận rằng cột ở một trong các bảng có các giá trị dữ liệu duy nhất và không có giá trị trùng lặp. Excel chỉ có thể tạo mối quan hệ nếu một cột có chứa các giá trị duy nhất.

Ví dụ, để liên kết doanh số bán hàng theo khách hàng với hiển thị thời gian thông minh, cả hai bảng phải chứa ngày tháng có cùng một định dạng (ví dụ: 1/1/2012) và tối thiểu một bảng (hiển thị thời gian thông minh) chỉ liệt kê mỗi ngày một lần trong cột đó.
Bấm Dữ liệu > Mối quan hệ.

Nếu Mối quan hệ bị mờ, thì đó là vì sổ làm việc của bạn chỉ chứa một bảng.

Trong hộp thoại Quản lý Mối quan hệ, hãy bấm Mới.
Trong hộp thoại Tạo mối Quan hệ, hãy bấm vào mũi tên cho Bảng và chọn bảng từ danh sách. Trong mối quan hệ một-nhiều, bảng này cần nằm ở phía nhiều. Với ví dụ về khách hàng và hiển thị thời gian thông minh, bạn sẽ chọn bảng doanh số bán hàng theo khách hàng trước tiên, vì nhiều doanh số bán hàng có khả năng xảy ra vào bất kỳ ngày nào đã cho.
Với Cột (Ngoại), hãy chọn cột có chứa dữ liệu có liên quan đến Cột Liên quan (Chính). Ví dụ, nếu bạn có cột ngày trong cả hai bảng, bạn sẽ chọn cột đó bây giờ.
Với Bảng Liên quan, hãy chọn bảng có ít nhất một cột dữ liệu có liên quan đến bảng bạn đã chọn cho Bảng.
Với Cột Liên quan (Chính), hãy chọn cột có giá trị duy nhất khớp với giá trị trong cột bạn đã chọn cho Cột.
Bấm OK.

Tìm hiểu thêm về mối quan hệ giữa các bảng trong Excel

Ghi chú về mối quan hệ
Ví dụ: Liên kết dữ liệu hiển thị thời gian thông minh với dữ liệu chuyến bay hàng không
“Có thể cần phải có mối quan hệ giữa các bảng”
- Bước 1: Xác định bảng nào cần được chỉ rõ trong mối quan hệ
- Bước 2: Tìm các cột có thể được dùng để tạo đường dẫn từ một bảng đến bảng tiếp theo

Ghi chú về mối quan hệ

Bạn sẽ biết mối quan hệ có tồn tại hay không khi bạn kéo các trường từ các bảng khác nhau vào danh sách Trường PivotTable. Nếu bạn không được nhắc tạo mối quan hệ, thì có nghĩa là Excel đã có thông tin mối quan hệ cần thiết để liên kết dữ liệu.
Việc tạo mối quan hệ cũng tương tự như việc dùng VLOOKUP: bạn cần các cột có chứa dữ liệu khớp để Excel có thể tham chiếu chéo các hàng trong một bảng với các hàng trong bảng khác. Trong ví dụ hiển thị thời gian thông minh, bảng Khách hàng sẽ cần có các giá trị ngày mà những giá trị này cũng tồn tại trong bảng hiển thị thời gian thông minh.
Trong mô hình dữ liệu, mối quan hệ bảng có thể là một-một (mỗi hành khách có một thẻ lên máy bay) hoặc một-nhiều (mỗi chuyến bay có nhiều hành khách) nhưng không thể là nhiều-nhiều. Mối quan hệ nhiều-nhiều gây ra lỗi phụ thuộc vòng tròn, chẳng hạn như “Đã phát hiện sự phụ thuộc vòng tròn”. Lỗi này sẽ xảy ra nếu bạn đặt kết nối trực tiếp giữa hai bảng có quan hệ nhiều-nhiều hoặc kết nối gián tiếp (một chuỗi các mối quan hệ bảng là quan hệ một-nhiều trong từng mối quan hệ nhưng là quan hệ nhiều-nhiều khi xem từ đầu này đến đầu kia. Đọc thêm về Mối quan hệ giữa các bảng trong Mô hình Dữ liệu.
Kiểu dữ liệu trong hai cột phải tương thích. Hãy xem Kiểu dữ liệu trong Mô hình Dữ liệu Excel để biết chi tiết.
Có thể có các cách khác trực quan hơn để tạo mối quan hệ, đặc biệt là khi bạn không biết chắc cần dùng cột nào. Hãy xem Tạo mối quan hệ trong Dạng xem Sơ đồ trong Power Pivot.

Ví dụ: Liên kết dữ liệu hiển thị thời gian thông minh với dữ liệu chuyến bay hàng không

Bạn có thể tìm hiểu về cả mối quan hệ bảng và hiển thị thời gian thông minh bằng cách dùng dữ liệu miễn phí trên Microsoft Azure Marketplace. Một vài bộ trong số các bộ dữ liệu này rất lớn, đòi hỏi phải có kết nối internet nhanh để hoàn thành tải xuống dữ liệu trong một khoảng thời gian hợp lý.

Bắt đầu Power Pivot trong bổ trợ Microsoft Excel và mở cửa sổ Power Pivot.
Bấm vào Nhận Dữ liệu Ngoài > Từ Dịch vụ Dữ liệu > Từ Microsoft Azure Marketplace. Trang đầu Microsoft Azure Marketplace mở ra trong Trình hướng dẫn Nhập Bảng.
Dưới Giá, hãy bấm Miễn phí.
Dưới Thể loại, hãy bấm Khoa học & Thống kê.
Tìm DateStream và bấm Đăng ký.
Nhập tài khoản Microsoft của bạn và bấm Đăng nhập. Xem trước dữ liệu sẽ xuất hiện trong cửa sổ.
Cuộn xuống dưới và bấm Chọn Truy vấn.
Bấm Tiếp.
Chọn BasicCalendarUS rồi bấm Kết thúc để nhập dữ liệu. Với kết nối internet nhanh, quá trình nhập sẽ mất khoảng một phút. Khi kết thúc, bạn sẽ thấy báo cáo trạng thái có 73.414 hàng được truyền. Bấm Đóng.
Bấm Nhận Dữ liệu Ngoài > Từ Dịch vụ Dữ liệu > Từ Microsoft Azure Marketplace để nhập bộ dữ liệu thứ hai.
Dưới Kiểu, hãy bấm Dữ liệu.
Dưới Giá, hãy bấm Miễn phí.
Tìm Các Chuyến bay Trễ của US Air Carrier và bấm Chọn.
Cuộn xuống dưới và bấm Chọn Truy vấn.
Bấm Tiếp.
Bấm Kết thúc để nhập dữ liệu. Với kết nối internet nhanh, quá trình nhập có thể mất 15 phút. Khi kết thúc, bạn sẽ thấy báo cáo trạng thái 2.427.284 hàng được truyền. Bấm Đóng. Bây giờ hẳn là bạn đã có hai bảng trong mô hình dữ liệu. Để liên kết các bảng này, chúng ta sẽ cần các cột tương thích trong mỗi bảng.
Lưu ý rằng DateKey trong BasicCalendarUS có định dạng 1/1/2012 12:00:00 SA. Bảng On_Time_Performance cũng có cột ngàygiờ, FlightDate, mà các giá trị trong cột này được xác định ở cùng một định dạng: 1/1/2012 12:00:00 SA. Hai cột chứa dữ liệu khớp nhau, có cùng một kiểu dữ liệu và tối thiểu một trong các cột (DateKey) chỉ chứa giá trị duy nhất. Trong một vài bước tiếp theo, bạn sẽ dùng các cột này để liên kết bảng.
Trong cửa sổ Power Pivot, hãy bấmPivotTable để tạo PivotTable trong trang tính mới hoặc trang tính hiện có.
Trong Danh sách Trường, hãy bung rộng On_Time_Performance và bấm ArrDelayMinutes để thêm vào vùng Giá trị. Trong PivotTable, bạn sẽ thấy tổng số thời gian các chuyến bay bị hoãn, được tính bằng phút.
Hãy bung rộng BasicCalendarUS và bấm MonthInCalendar để thêm nó vào vùng Hàng.
Lưu ý rằng bây giờ PivotTable liệt kê các tháng nhưng tổng cộng số phút giống nhau cho mọi tháng. Các giá trị lặp lại, giống hệt nhau cho thấy rằng cần có một mối quan hệ.
Trong Danh sách Trường, trong “Có thể cần mối quan hệ giữa các bảng”, hãy bấm Tạo.
Trong Bảng Liên quan, hãy chọn On_Time_Performance và trong Cột Liên quan (Chính) chọn FlightDate.
Trong Bảng, hãy chọn BasicCalendarUS và trong Cột (Ngoại) chọn DateKey. Bấm OK để tạo mối quan hệ.
Lưu ý rằng tổng số phút bị hủy của mỗi tháng bây giờ đã khác nhau.
Trong BasicCalendarUS và kéo YearKey đến vùng Hàng, ở trên MonthInCalendar.

Bây giờ bạn có thể phân chia các chuyến bay đến bị hoãn theo năm và tháng hoặc các giá trị khác trong lịch.

Mẹo: Theo mặc định, các tháng được liệt kê theo thứ tự bảng chữ cái. Dùng bổ trợ Power Pivot, bạn có thể thay đổi cách sắp xếp để các tháng xuất hiện theo thứ tự thời gian.

Hãy bảo đảm bảng BasicCalendarUS đang mở trong cửa sổ Power Pivot.
Trên bảng Trang đầu, hãy bấm Sắp xếp theo Cột.
Trong Sắp xếp, hãy chọn MonthInCalendar
Trong Theo, hãy chọn MonthOfYear.

Bây giờ PivotTable sắp xếp từng kết hợp tháng-năm (Tháng 10 năm 2011, Tháng 11 năm 2011) theo số tháng trong năm (10, 11). Thay đổi thứ tự sắp xếp thật dễ dàng vì nguồn cấp DateStream cung cấp tất cả các cột cần thiết để thực hiện thành công tình huống này. Nếu bạn đang dùng bảng hiển thị thời gian thông minh khác, bạn sẽ thực hiện bước khác.

“Có thể cần phải có mối quan hệ giữa các bảng”

Khi bạn thêm các trường vào PivotTable, bạn sẽ được thông báo liệu có cần mối quan hệ bảng để làm cho các trường bạn đã chọn trong PivotTable có ý nghĩa hay không.

Nút Tạo xuất hiện khi cần mối quan hệ

Mặc dù Excel có thể cho bạn biết khi nào cần mối quan hệ nhưng nó không thể cho bạn biết cần dùng bảng và cột nào hoặc liệu có thể có mối quan hệ bảng nữa hay không. Hãy thử làm theo các bước sau đây để có câu trả lời bạn cần.

Bước 1: Xác định bảng nào cần được chỉ rõ trong mối quan hệ

Nếu mô hình của bạn chỉ chứa một vài bảng, bạn có thể nhanh chóng thấy rõ cần dùng những bảng nào. Nhưng với các mô hình lớn hơn, bạn có thể dùng một số trợ giúp. Một phương pháp là dùng Dạng xem Sơ đồ trong Power Pivot bổ trợ. Dạng xem Sơ đồ cung cấp dạng biểu thị trực quan của tất cả các bảng trong Mô hình Dữ liệu. Dùng Dạng xem Sơ đồ, bạn có thể nhanh chóng xác định bảng nào tách biệt với phần còn lại của mô hình.

Dạng xem sơ đồ hiển thị các bảng đã bị ngắt kết nối

Lưu ý: Các mối quan hệ mơ hồ có thể được tạo ra, mà những mối quan hệ này không hợp lệ khi được dùng trong báo cáo PivotTable hoặc báo cáo Power View. Giả sử tất cả các bảng của bạn có liên quan theo một cách nào đó với các bảng khác trong mô hình nhưng khi bạn tìm cách kết hợp các trường từ các bảng khác nhau, bạn nhận được thông báo “Có thể cần mối quan hệ giữa các bảng". Nguyên nhân thường gặp nhất là vì bạn đã gặp phải mối quan hệ nhiều-đến-nhiều. Nếu bạn đi theo chuỗi các mối quan hệ bảng kết nối với bảng mà bạn muốn dùng, thì có thể bạn sẽ phát hiện ra rằng bạn có hai hoặc nhiều mối quan hệ bảng một-đến-nhiều. Không có giải pháp thay thế dễ dàng nào có hiệu quả trong mọi tình huống nhưng bạn có thể thử tạo các cột được tính toán để hợp nhất các cột bạn muốn dùng thành một bảng.

Bước 2: Tìm các cột có thể được dùng để tạo đường dẫn từ một bảng đến bảng tiếp theo

Sau khi bạn đã xác định ngắt kết nối bảng nào từ phần còn lại của mô hình, hãy xem lại các cột của bảng để xác định xem liệu một cột khác, ở vị trí khác trong mô hình đó, có chứa các dữ liệu khớp nhau hay không.

Ví dụ, giả sử bạn có mô hình có chứa doanh số bán sản phẩm theo vùng lãnh thổ và sau đó bạn nhập dữ liệu nhân khẩu học để tìm hiểu xem có mối tương quan nào giữa doanh số bán hàng và xu hướng nhân khẩu học trong từng vùng lãnh thổ hay không. Vì dữ liệu nhân khẩu học xuất phát từ nguồn dữ liệu khác, cho nên ban đầu các bảng dữ liệu này tách biệt so với phần còn lại của mô hình. Để tích hợp dữ liệu nhân khẩu học với phần còn lại của mô hình, bạn sẽ cần tìm cột trong một trong số các bảng nhân khẩu học tương ứng với bảng mà bạn đã dùng. Ví dụ, nếu dữ liệu nhân khẩu học được sắp xếp theo khu vực và dữ liệu doanh số bán hàng của bạn chỉ rõ việc bán hàng diễn ra ở khu vực nào, thì bạn có thể liên kết hai bộ dữ liệu bằng cách tìm cột chung, chẳng hạn như Tiểu bang, mã Zip hay Khu vực để thực hiện tra cứu.

Ngoài các giá trị khớp nhau, có một vài yêu cầu bổ sung đối với việc tạo mối quan hệ:

Các giá trị dữ liệu trong cột tra cứu phải là giá trị duy nhất. Nói cách khác, cột đó không được chứa các mục trùng lặp. Trong Mô hình Dữ liệu, chuỗi null và rỗng tương đương với chuỗi trống, đó là giá trị dữ liệu riêng biệt. Điều này có nghĩa là bạn không thể có nhiều giá trị null trong cột tra cứu.
Kiểu dữ liệu của cả cột nguồn và cột tra cứu phải tương thích. Để biết thêm thông tin về kiểu dữ liệu, hãy xem Kiểu dữ liệu trong Mô hình Dữ liệu.

Để tìm hiểu thêm về mối quan hệ bảng, hãy xem Mối quan hệ giữa các bảng trong Mô hình Dữ liệu.

Đầu Trang

Tạo mối quan hệ giữa hai bảng trong Excel

Tìm hiểu thêm về mối quan hệ giữa các bảng trong Excel

Ghi chú về mối quan hệ

Ví dụ: Liên kết dữ liệu hiển thị thời gian thông minh với dữ liệu chuyến bay hàng không

“Có thể cần phải có mối quan hệ giữa các bảng”

Bước 1: Xác định bảng nào cần được chỉ rõ trong mối quan hệ

Bước 2: Tìm các cột có thể được dùng để tạo đường dẫn từ một bảng đến bảng tiếp theo

Bạn cần thêm trợ giúp?

Bạn muốn xem các tùy chọn khác?

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

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