Trong nhiều trường hợp, chúng ta có thể cần tạo Mối quan hệ trong Excel với các giá trị Trùng lặp vì các tập dữ liệu có một cột chung có thể sử dụng. Nhưng nếu cả hai bảng đều có giá trị Trùng lặp, quy trình trở nên khá khó khăn để thực thi. Nếu bạn tò mò muốn biết cách tạo nhiều trang tính từ danh sách giá trị ô, thì bài viết này có thể hữu ích cho bạn. Trong bài viết này, chúng ta thảo luận cách tạo mối quan hệ với giá trị ô trùng lặp trong Excel với giải thích chi tiết.
Tải sổ làm việc thực hành bên dưới.
2 Cách dễ dàng để tạo Mối quan hệ trong Excel với giá trị Trùng lặp
Chúng ta sẽ sử dụng tập dữ liệu sau để tạo mối quan hệ giữa hai bảng trong Excel với giá trị Trùng lặp. Cả hai tập dữ liệu đều có giá trị Trùng lặp. Đồng thời, cột chung là ID Sản phẩm. Mối quan hệ sẽ dựa trên cột chung này.
1. Nối bảng chung (Appending Common Table)
Vì chúng ta có giá trị Trùng lặp trong cả hai bảng, chúng ta không thể tạo Mối quan hệ giữa chúng trong Excel. Vì Mối quan hệ này sẽ yêu cầu mối quan hệ một-một và Excel hiện không hỗ trợ. Chúng ta sẽ nhập các bảng vào truy vấn năng lượng và nối chúng lại với nhau. Sau đó, chúng ta sẽ xóa các Trùng lặp từ bảng đã nối. Ở giai đoạn cuối, chúng ta tạo Mối quan hệ với bảng mới này với hai bảng gốc. Đây là cách chúng ta tạo Mối quan hệ trong Excel với giá trị Trùng lặp.
Các bước
- Chúng ta có hai bảng riêng biệt giữa đó chúng ta cần tạo Mối quan hệ, dựa trên cột chung ID Sản phẩm.
- Nhưng nói chung, để tạo Mối quan hệ giữa các bảng, chúng ta cần có ít nhất các giá trị duy nhất trong một trong các bảng.
- Nhưng ở đây chúng ta có giá trị Trùng lặp trong cột ID Sản phẩm của cả hai bảng.
- Để chứng minh tại sao chúng ta không thể tạo Mối quan hệ giữa các bảng đã trình bày, hãy làm theo các bước dưới đây nơi chúng ta sẽ cố gắng tạo mối quan hệ giữa hai bảng này.
- Để làm điều này, chúng ta cần trước tiên tạo một bảng, chọn phạm vi ô B4:C16, sau đó nhấp vào lệnh Bảng từ tab Chèn.
- Sau đó trong hộp thoại Tạo Bảng, xác nhận lại phạm vi bảng.
- Chọn hộp kiểm Bảng của tôi có tiêu đề.
- Để chứng minh tại sao chúng ta không thể tạo Mối quan hệ giữa các bảng đã trình bày, hãy làm theo các bước dưới đây nơi chúng ta sẽ cố gắng tạo mối quan hệ giữa hai bảng này.
- Để làm điều này, chúng ta cần trước tiên tạo một bảng, chọn phạm vi ô B4:C16, sau đó nhấp vào lệnh Bảng từ tab Chèn.
- Sau đó trong hộp thoại Tạo Bảng, xác nhận lại phạm vi bảng.
- Chọn hộp kiểm Bảng của tôi có tiêu đề.
- Sau khi đã tạo xong bảng, chúng ta sẽ tạo một bảng pivot từ hai bảng vừa tạo.
- Chọn bảng đầu tiên rồi chuyển đến tab Chèn > Bảng Pivot > Từ Bảng/Phạm vi.
- Trong hộp thoại tiếp theo, nhấp vào Trang tính mới.
- Và sau đó tích vào hộp kiểm Thêm dữ liệu này vào Mô hình dữ liệu.
- Nhấp OK sau đó.
- Sau đó chọn bảng thứ hai rồi chuyển đến tab Chèn > Bảng Pivot > Từ Bảng/Phạm vi.
- Trong hộp thoại tiếp theo, nhấp vào Trang tính mới.
- Và sau đó tích vào hộp kiểm Thêm dữ liệu này vào Mô hình dữ liệu.
- Nhấp OK sau đó.
- Trong trang tính mới, bạn sẽ chú ý rằng có menu Các trường Bảng Pivot.
- Bảng điều khiển bên bị chiếm bởi cả hai bảng. Để quan sát cả hai bảng, bạn cần nhấp vào phần Tất cả.
- Sau đó chúng ta kéo trường ID Sản phẩm vào khu vực Bộ lọc bên dưới.
- Hơn nữa, kéo Doanh thu và Lợi nhuận từ Bảng 2 và Chi phí và Số lượng từ Bảng 3.
- Bạn sẽ nhận thấy Doanh thu và Lợi nhuận hiển thị các giá trị chúng nên hiển thị. Nhưng cột Số lượng và Chi phí hiển thị cùng giá trị cho mỗi hàng. Điều này khá bất thường.
- Đó là vì không có mối quan hệ giữa các bảng.
- Để giải quyết vấn đề này, chúng ta cần tạo Mối quan hệ giữa chúng trước.
- Chúng ta đặt con trỏ tại bất kỳ điểm nào trong bảng. Bây giờ kích hoạt tính năng Power Pivot trong Excel và sau đó nhấp vào tab Power Pivot.
- Sau đó nhấp vào Thêm vào Mô hình dữ liệu.
- Trong mô hình dữ liệu, chúng ta có thể thấy cả hai bảng đã được tải.
- Chuyển sang chế độ Xem sơ đồ bằng cách nhấp vào Xem sơ đồ trong tab Trang chủ.
- Sau đó từ tab Thiết kế, nhấp vào Tạo Mối quan hệ.
- Trong hộp thoại Tạo Mối quan hệ, chọn Bảng 2 và Bảng 3 trong hộp thả xuống.
- Bên dưới, bạn sẽ nhận thấy có dấu hiệu cảnh báo.
- Tóm tắt của dấu hiệu cảnh báo đó là chúng ta có giá trị Trùng lặp trong cả hai cột, và chúng ta sẽ không thể tạo Mối quan hệ vì điều này sẽ yêu cầu loại Mối quan hệ nhiều-nhiều. Điều này không được hỗ trợ lúc này.
- Nhấp vào Hủy.
- Bây giờ chúng ta sẽ mở truy vấn năng lượng và từ đó chúng ta sẽ nối hai bảng riêng biệt.
- Sử dụng bảng đã nối đó, chúng ta sẽ tạo Mối quan hệ giúp chúng ta đạt được kết quả mong muốn.
- Chọn Bảng 2.
- Từ tab Dữ liệu, đi đến Lấy dữ liệu > Từ Nguồn khác > Từ Bảng/Phạm vi.
- Bạn sẽ nhận thấy có một cửa sổ mới.
- Và trong cửa sổ đó, chúng ta có Bảng 2 hiện đã được tải.
- Làm tương tự cho Bảng 3.
- Chuột phải vào Bảng 2, sau đó từ menu ngữ cảnh, nhấp vào Thêm như Truy vấn mới.
- Sẽ có một truy vấn mới được liệt kê bên dưới.
- Sau đó chuột phải vào Danh sách và từ menu ngữ cảnh, nhấp vào Chuyển thành Bảng.
- Trong cửa sổ tiếp theo, không làm gì cả và nhấp OK.
- Chuột phải vào Bảng 2, sau đó từ menu ngữ cảnh, nhấp vào Thêm như Truy vấn mới.
- Sẽ có một truy vấn mới được liệt kê bên dưới.
- Sau đó chuột phải vào Danh sách và từ menu ngữ cảnh, nhấp vào Chuyển thành Bảng.
- Trong cửa sổ tiếp theo, không làm gì cả và nhấp OK.
- Sau đó chúng ta có hai cột riêng biệt bây giờ được loại trừ khỏi bảng của chúng, trong các truy vấn khác nhau.
- Bây giờ chúng ta sẽ Nối ID Sản phẩm và ID Sản phẩm (2).
- Chọn bảng ID Sản phẩm sau đó nhấp vào Nối truy vấn từ nhóm Kết hợp.
- Sau đó từ menu thả xuống, nhấp vào Nối truy vấn.
- Ngay sau đó, cửa sổ dưới sẽ xuất hiện.
- Trong menu đó, nhấp vào ID Sản phẩm (2), từ tùy chọn Bảng để nối.
- Nhấp OK sau đó.
- ID Sản phẩm (2) bảng giờ đã nối với bảng ID Sản phẩm thành công.
- Trong bảng vừa nối, chuột phải vào bảng, và chọn Xóa Trùng lặp.
- Bây giờ tất cả Trùng lặp đã được xóa.
- Đóng bảng truy vấn năng lượng. Nhấp vào Giữ khi thoát.
- Các bảng mới tạo sẽ tải trong các trang tính riêng biệt.
- Chú ý trang tính tên ID Sản phẩm.
- Nhấp vào Thêm vào Mô hình dữ liệu, từ tab Power Pivot.
- Bảng mới hiện có sẵn tại Power Pivot.
- Trong tab Thiết kế, nhấp vào Tạo Mối quan hệ.
- Sau đó trong cửa sổ Tạo Mối quan hệ, chọn Bảng 2 và ID Sản phẩm.
- Nhấp OK sau đó.
- Trong xem sơ đồ, chúng ta có thể thấy rằng có Mối quan hệ được tạo giữa Bảng 2 và ID Sản phẩm.
- Sau đó trong cửa sổ Tạo Mối quan hệ, chọn Bảng 3 và ID Sản phẩm.
- Nhấp OK sau đó.
- Trong xem sơ đồ, chúng ta có thể thấy rằng có Mối quan hệ được tạo giữa Bảng 3 và ID Sản phẩm.
- Bây giờ chúng ta quay lại báo cáo bảng pivot gốc.
- Trong báo cáo đó, xóa tất cả các trường hiện có và nhập các trường như dưới đây.
- Ban đầu, thêm trường ID Sản phẩm vào Hàng.
- Sau đó kéo Doanh thu vào giá trị.
- Bạn có thể thấy các vấn đề tương tự như trước vẫn hiện diện.
- Đồng thời, bạn có thể chú ý có khu vực cảnh báo màu vàng, cho thấy sự hiện diện của Mối quan hệ giữa các bảng.
- Nhấp vào Tự động phát hiện.
- Sau khi nhấp Tự động phát hiện, hộp thoại dưới sẽ xuất hiện.
- Sẽ mất một chút thời gian để liên kết Mối quan hệ
- Sau đó, Mối quan hệ sẽ được tìm thấy và liên kết.
- Nhấp vào Đóng.
- Bây giờ kéo Doanh thu và lợi nhuận từ Bảng 2_2.
- Và kéo số lượng và chi phí từ Bảng 3_2.
- Nếu có bất kỳ cảnh báo màu vàng Mối quan hệ như đã nêu trước, nhấp Tự động phát hiện như trước.
- Bây giờ bạn có thể thấy bảng pivot đang hiển thị các giá trị phù hợp, theo Mối quan hệ chúng ta đã tạo trước đó.
2. Xóa giá trị Trùng lặp
Trong phương pháp này, chúng ta sẽ xóa giá trị ô Trùng lặp của cả hai bảng và sau đó chúng ta sẽ tạo mối quan hệ một-một giữa chúng. Việc loại bỏ các giá trị Trùng lặp sẽ được đạt được bằng cách sử dụng các lệnh Truy vấn năng lượng. Với các bảng hiện không có Trùng lặp, chúng ta giờ có thể tạo Mối quan hệ trong Excel với giá trị Trùng lặp.
Các bước
- Chúng ta có hai bảng riêng biệt giữa đó chúng ta cần tạo Mối quan hệ, dựa trên cột chung ID Sản phẩm.
- Nhưng nói chung, để tạo Mối quan hệ giữa các bảng, chúng ta cần có ít nhất các giá trị duy nhất trong một trong các bảng.
- Nhưng ở đây chúng ta có giá trị Trùng lặp trong cột ID Sản phẩm của cả hai bảng.
- Để làm điều này, chúng ta cần trước tiên tạo một bảng, chọn phạm vi ô B4:C16, sau đó nhấp vào lệnh Bảng từ tab Chèn.
- Sau đó trong hộp thoại Tạo Bảng, xác nhận lại phạm vi bảng.
- Chọn hộp kiểm Bảng của tôi có tiêu đề.
- Để làm điều này, chúng ta cần trước tiên tạo một bảng, chọn phạm vi ô B4:C16, sau đó nhấp vào lệnh Bảng từ tab Chèn.
- Sau đó trong hộp thoại Tạo Bảng, xác nhận lại phạm vi bảng.
- Chọn hộp kiểm Bảng của tôi có tiêu đề.
- Bây giờ chúng ta sẽ mở truy vấn năng lượng và từ đó chúng ta sẽ xóa giá trị Trùng lặp từ bảng.
- Chọn Bảng 1.
- Từ tab Dữ liệu, đi đến Lấy dữ liệu > Từ Nguồn khác > Từ Bảng/Phạm vi.
- Bạn sẽ nhận thấy có một cửa sổ mới mở ra.
- Và trong cửa sổ đó, chúng ta có Bảng 1 hiện đã được tải.
- Làm tương tự cho Bảng 2.
- Trong Bảng 1, chuột phải vào ID Sản phẩm, và chọn Xóa Trùng lặp.
- Bây giờ tất cả Trùng lặp đã được xóa.
- Làm tương tự cho Bảng 2.
- Trong Bảng 2, chuột phải vào bảng, và chọn Xóa Trùng lặp.
- Sau khi xóa Trùng lặp, bảng sẽ trông như bên dưới.
- Sau đó chúng ta nhấp vào Đóng & Tải vào từ tab Trang chủ.
- Bảng sẽ tải trong các trang tính khác nhau.
- Chúng ta sẽ thêm chúng vào bảng pivot
- Chọn Bảng 1 đã tải và sau đó đi đến tab Chèn > Bảng Pivot > Từ Bảng/Phạm vi.
- Trong hộp thoại tiếp theo, nhấp vào Trang tính hiện có, và vị trí của bảng.
- Và sau đó tích vào hộp kiểm Thêm dữ liệu này vào Mô hình dữ liệu.
- Nhấp OK sau đó.
- Chọn Bảng 2 đã tải và sau đó đi đến tab Chèn > Bảng Pivot > Từ Bảng/Phạm vi.
- Trong hộp thoại tiếp theo, nhấp vào Trang tính hiện có, và vị trí của bảng.
- Và sau đó tích vào hộp kiểm Thêm dữ liệu này vào Mô hình dữ liệu.
- Nhấp OK sau đó.
- Sau này, kéo ID Sản phẩm từ Bảng 1_2 vào Hàng.
- Hơn nữa, kéo Doanh thu và Lợi nhuận từ Bảng 1_2 và Chi phí và Số lượng từ Bảng 2_2 vào giá trị.
- Giá trị của cột chi phí và số lượng trong bảng pivot là bất thường. Chúng đang hiển thị cùng giá trị cho từng hàng.
- Để giải quyết vấn đề này, nhấp vào TẠO trên Hộp cảnh báo vàng.
- Trong cửa sổ Chỉnh sửa Mối quan hệ, chọn Bảng 2_2 và Bảng 1_2 trong menu thả xuống thứ nhất và thứ hai.
- Sau đó chọn ID Sản phẩm trong cả hai menu thả xuống Cột (Ngoại khóa) và Cột liên quan (Khóa chính).
- Nhấp OK sau đó.
- Sau khi nhấp OK, bạn sẽ nhận thấy Mối quan hệ giờ đã giải quyết vấn đề.
- Cột Chi phí và Lợi nhuận giờ hiển thị giá trị chính xác.
Lưu ý quan trọng
- Khi tạo Mối quan hệ giữa hai bảng trong phương pháp thứ hai, cột chung sẽ nằm trong trường Hàng nên từ bảng Cột liên quan (Khóa chính). Nếu không, nó sẽ tạo giá trị sai trong bảng pivot
- Tích vào ô Thêm dữ liệu vào mô hình dữ liệu là bắt buộc. Nếu không, sẽ không thể tạo kết nối giữa chúng.
Kết luận
Tóm lại, vấn đề về cách chúng ta có thể tạo Mối quan hệ trong Excel với giá trị Trùng lặp được giải答 ở đây theo 2 cách khác nhau.
Đối với vấn đề này, một sổ làm việc có sẵn để tải xuống nơi bạn có thể thực hành các phương pháp này.
Thoải mái đặt bất kỳ câu hỏi hoặc phản hồi nào qua phần bình luận. Bất kỳ đề xuất nào để cải thiện cộng đồng Exceldemy sẽ được trân trọng cao.