Trong nhiều trường hợp, chúng tôi có thể cần tạo Mối quan hệ trong Excel với Bản sao vì tập dữ liệu có một cột chung có thể được sử dụng. Nhưng nếu bằng cách nào đó cả hai bảng đều có Trùng lặp thì quá trình này trở nên khá khó thực hiện. Nếu bạn tò mò muốn biết cách tạo nhiều trang tính từ danh sách các 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 tôi thảo luận về cách bạn có thể tạo mối quan hệ với các giá trị ô trùng lặp trong Excel với giải thích cặn kẽ.
Tải xuống sách bài tập thực hành này bên dưới.
2 cách dễ dàng để tạo mối quan hệ trong Excel với Bản sao Giá trị
Chúng tôi 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 Bản sao các giá trị. Cả hai tập dữ liệu đều có Trùng lặp các giá trị. Đồ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. Bảng Chung kết thúc
Như chúng tôi có Bản sao trong cả hai bảng, chúng tôi không thể tạo Mối quan hệ trong số đó trong Excel. Kể từ Mối quan hệ này sẽ yêu cầu một Mối quan hệ một-một và Excel không hỗ trợ nó ngay bây giờ. Chúng tôi sẽ nhập các bảng trên truy vấn nguồn và nối chúng lại với nhau. Sau đó, chúng tôi sẽ xóa Bản sao s từ bảng được nối thêm. Trong giai đoạn cuối cùng, chúng tôi tạo Mối quan hệ với bảng mới này với hai bảng ban đầu. Đây là cách chúng tôi sẽ tạo Mối quan hệ trong Excel với Bản sao giá trị.
Các bước
- Chúng tôi có hai bảng riêng biệt giữa chúng tôi 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 giá trị duy nhất ở một trong các bảng.
- Nhưng ở đây chúng tôi có Bản sao các giá trị trong cả Id sản phẩm của bảng cột.
- Để giải thích lý do tại sao chúng tôi không thể tạo Mối quan hệ trong bảng được trình bày, hãy làm theo các bước dưới đây, nơi chúng tôi sẽ cố gắng tạo mối quan hệ giữa hai bảng này.
- Đối với điều này, trước tiên chúng ta cần tạo một bảng trước, chọn phạm vi ô B4:C16 rồi nhấp vào Bảng lệnh từ Chèn tab.
- Sau đó, trong Tạo bảng hộp thoại, xác nhận lại phạm vi bảng.
- Kiểm tra Bảng của tôi có tiêu đề hộp kiểm.
- Để giải thích lý do tại sao chúng tôi không thể tạo Mối quan hệ trong bảng được trình bày, hãy làm theo các bước dưới đây, nơi chúng tôi sẽ cố gắng tạo mối quan hệ giữa hai bảng này.
- Đối với điều này, trước tiên chúng ta cần tạo một bảng trước, chọn phạm vi ô B4:C16 rồi nhấp vào Bảng lệnh từ Chèn tab.
- Sau đó, trong Tạo bảng hộp thoại, xác nhận lại phạm vi bảng.
- Kiểm tra Bảng của tôi có tiêu đề hộp kiểm.
- Sau khi hoàn tất việc tạo bảng, chúng tôi sẽ tạo một bảng tổng hợp trong số hai bảng đã tạo gần đây.
- Chọn bảng đầu tiên, sau đó đi tới Chèn tab> PivotTable > 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 đó đánh dấu vào ô Thêm dữ liệu này vào Mô hình Dữ liệu hộp kiểm.
- Nhấp vào OK sau cái này.
- Sau đó, chọn bảng thứ hai rồi đi tới Chèn tab> PivotTable > 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 đó đánh dấu vào ô Thêm dữ liệu này vào Mô hình Dữ liệu hộp kiểm.
- Nhấp vào OK sau cái này.
- Trong trang tính mới, bạn sẽ nhận thấy rằng có một Trường PivotTable menu.
- 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 nút Tất cả phần.
- Sau đó, chúng tôi kéo Id sản phẩm trường bên dưới Bộ lọc khu vực.
- Hơn nữa, kéo Doanh thu và Lợi nhuận từ Table2 và Chi phí và Số lượng từ Table3 .
- Bạn sẽ nhận thấy rằng Doanh thu và Lợi nhuận đang hiển thị các giá trị mà chúng được cho là hiển thị. Nhưng Số lượng và Chi phí cột đang hiển thị các giá trị giống nhau cho mỗi hàng. Điều này khá bất thường.
- Đó là bởi vì không có bất kỳ mối quan hệ nào giữa các bảng.
- Để giải quyết vấn đề này, chúng tôi cần tạo Mối quan hệ trong số đó đầu tiên.
- Chúng tôi đặt con trỏ vào bất kỳ điểm nào trong bảng. Bây giờ, hãy bật tính năng Power Pivot trong Excel và sau đó nhấp vào Power Pivot tab.
- Sau đó, nhấp vào nút Thêm vào mô hình dữ liệu .
- Trong mô hình dữ liệu, chúng ta có thể thấy rằng cả hai bảng đều đã được tải.
- Chuyển sang Sơ đồ xem bằng cách nhấp vào Chế độ xem sơ đồ trong Trang chủ tab.
- Sau đó từ Thiết kế , nhấp vào Tạo mối quan hệ .
- Trong phần Tạo mối quan hệ hộp thoại, chọn Table2 và Table3 trong hộp thả xuống.
- Dưới đây, bạn sẽ nhận thấy rằng có một 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 tôi có Bản sao trong cả hai cột và chúng tôi sẽ không thể tạo Mối quan hệ vì điều này sẽ yêu cầu nhiều đến nhiều loại Mối quan hệ . Tính năng này không được hỗ trợ tại thời điểm này.
- Nhấp vào Huỷ .
- Bây giờ chúng ta sẽ mở một truy vấn nguồn và từ đó chúng tôi sẽ nối hai bảng riêng biệt.
- Sử dụng bảng được nối thêm đó, chúng tôi sẽ tạo Mối quan hệ điều đó sẽ giúp chúng tôi đạt được kết quả mong muốn.
- Chọn Table2 .
- Từ Dữ liệu , chuyển đến Lấy dữ liệu > Từ các nguồn khác > Bảng / Dải biểu mẫu .
- Bạn sẽ nhận thấy rằng có một cửa sổ mới.
- Và trong cửa sổ đó, chúng tôi có Table2 hiện đã được tải.
- Làm điều tương tự đối với Table3 .
- Nhấp chuột phải vào Table2 và sau đó từ trình đơn ngữ cảnh, nhấp vào Thêm làm truy vấn mới .
- Sẽ có một truy vấn mới được liệt kê bên dưới.
- Sau đó, nhấp chuột phải vào Danh sách và từ trình đơn ngữ cảnh, hãy nhấp vào Đến bảng .
- Trong cửa sổ sau, không làm gì cả và nhấp vào OK .
- Nhấp chuột phải vào Table2 và sau đó từ trình đơn ngữ cảnh, nhấp vào Thêm làm truy vấn mới .
- Sẽ có một truy vấn mới được liệt kê bên dưới.
- Sau đó, nhấp chuột phải vào Danh sách và từ trình đơn ngữ cảnh, hãy nhấp vào Đến bảng .
- Trong cửa sổ sau, không làm gì cả và nhấp vào OK .
- Sau đó, chúng tôi nhận được hai cột riêng biệt hiện bị 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 thêm Id sản phẩm và Id sản phẩm (2) .
- Chọn bảng Id Sản phẩm và sau đó nhấp vào Nối truy vấn từ Kết hợp nhóm.
- Sau đó, từ trình đơn thả xuống, hãy nhấp vào Nối truy vấn .
- Ngay sau đó, cửa sổ bên dưới sẽ xuất hiện.
- Trong trình đơn đó, nhấp vào Id Sản phẩm (2) , từ Bảng để nối thêm tùy chọn.
- Nhấp vào OK sau cái này.
- Id Sản phẩm (2) bảng hiện gắn với Id sản phẩm bảng thành công.
- Trong bảng mới được thêm vào, nhấp chuột phải vào bảng và chọn Xóa các Bản trùng lặp.
- Bây giờ là tất cả Bản sao hiện đã bị xóa.
- Đóng truy vấn nguồn bàn. Nhấp vào Giữ trên đường đi.
- Các bảng mới được tạo sẽ tải trong các trang tính riêng biệt.
- Chú ý đến Id sản phẩm bảng tính đã đặt tên.
- Nhấp vào Thêm vào Mô hình Dữ liệu , từ Power Pivot tab.
- Bảng mới hiện có sẵn tại Trụ điện .
- Trong Thiết kế , nhấp vào Tạo mối quan hệ .
- Sau đó, trong phần Tạo mối quan hệ cửa sổ, chọn Table2 và Id sản phẩm .
- Nhấp vào OK sau cái này.
- Trong chế độ xem sơ đồ, chúng ta có thể thấy rằng có Mối quan hệ được tạo giữa Table2 và Id sản phẩm .
- Sau đó, trong phần Tạo mối quan hệ cửa sổ, chọn Table3 và Id sản phẩm .
- Nhấp vào OK sau cái này.
- Trong chế độ xem sơ đồ, chúng ta có thể thấy rằng có Mối quan hệ được tạo giữa Table3 và Id sản phẩm .
- Bây giờ chúng ta quay lại báo cáo bảng tổng hợp ban đầu.
- Trong báo cáo đó, hãy xóa tất cả các trường hiện có và nhập các trường được hiển thị bên dưới.
- Ngay từ đầu, hãy thêm Id sản phẩm trong trường Hàng .
- Sau đó, kéo Doanh thu đến các giá trị.
- Bạn có thể thấy các vấn đề tương tự như trước đây vẫn xảy ra.
- Đồng thời, bạn có thể nhận thấy khu vực thông báo màu vàng cho biết 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 vào Tự động phát hiện, hộp thoại bên dưới sẽ xuất hiện.
- Sẽ mất một khoảng 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ờ hãy kéo Doanh thu và kiếm lợi nhuận từ Table2_2 .
- Và kéo số lượng và chi phí từ Table3_2 .
- Nếu có bất kỳ Mối quan hệ nào cảnh báo màu vàng như đã nêu trước đây, nhấp vào Tự động phát hiện như trước đây.
- Bây giờ, bạn có thể thấy rằng bảng tổng hợp hiện đang hiển thị các giá trị một cách thích hợp, theo Mối quan hệ chúng tôi đã tạo trước đó.
2. Xóa Bản sao Giá trị
Trong phương pháp này, chúng tôi sẽ xóa Bản sao giá trị ô của cả hai bảng và sau đó chúng tôi sẽ tạo mối quan hệ một-một trong số họ. Việc loại bỏ Bản sao này giá trị sẽ đạt được bằng cách sử dụng Truy vấn nguồn các lệnh. Với các bảng hiện không bị trùng lặp s, bây giờ chúng tôi có thể tạo Mối quan hệ trong Excel với Bản sao giá trị.
Các bước
- Chúng tôi có hai bảng riêng biệt giữa chúng tôi 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 giá trị duy nhất ở một trong các bảng.
- Nhưng ở đây chúng tôi có Bản sao các giá trị trong cả Id sản phẩm của bảng cột.
- Đối với điều này, trước tiên chúng ta cần tạo một bảng trước, chọn phạm vi ô B4:C16 rồi nhấp vào Bảng lệnh từ Chèn tab.
- Sau đó, trong Tạo bảng hộp thoại, xác nhận lại phạm vi bảng.
- Kiểm tra Bảng của tôi có tiêu đề hộp kiểm.
- Đối với điều này, trước tiên chúng ta cần tạo một bảng trước, chọn phạm vi ô B4:C16 rồi nhấp vào Bảng lệnh từ Chèn tab.
- Sau đó, trong Tạo bảng hộp thoại, xác nhận lại phạm vi bảng.
- Kiểm tra Bảng của tôi có tiêu đề hộp kiểm.
- Bây giờ chúng ta sẽ mở một truy vấn nguồn và từ đó chúng tôi sẽ xóa Bản sao giá trị từ bảng.
- Chọn Bảng1 .
- Từ Dữ liệu , chuyển đến Lấy dữ liệu > Từ các nguồn khác > Bảng / Dải biểu mẫu .
- Bạn sẽ nhận thấy rằng có một cửa sổ mới được mở ra.
- Và trong cửa sổ đó, chúng tôi có Table1 hiện đã được tải.
- Làm điều tương tự đối với Table2 .
- Trong Table1 , nhấp chuột phải vào Id sản phẩm và chọn Xóa bản sao.
- Bây giờ là tất cả Bản sao hiện đã bị xóa.
- Làm tương tự cho Table2 .
- Trong Table2 , nhấp chuột phải vào bảng và chọn Xóa bản sao.
- Sau khi xóa Bản sao , bảng sẽ giống như bảng bên dưới.
- Sau đó, chúng tôi nhấp vào nút Đóng &Tải tới từ Trang chủ tab.
- Bảng sẽ tải ở các trang tính khác nhau.
- Chúng tôi sẽ thêm chúng vào bảng tổng hợp
- Chọn Table1 đã tải và sau đó đi tới Chèn tab> PivotTable > 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 đó đánh dấu vào ô Thêm dữ liệu này vào Mô hình Dữ liệu hộp kiểm.
- Nhấp vào OK sau cái này.
- Chọn Table2 đã tải và sau đó đi tới Chèn tab> PivotTable > 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 đó đánh dấu vào ô Thêm dữ liệu này vào Mô hình Dữ liệu hộp kiểm.
- Nhấp vào OK sau cái này.
- Sau đó, hãy kéo Id sản phẩm từ Table1_2 vào Hàng .
- Hơn nữa, kéo Doanh thu và Lợi nhuận từ Table1_2 và Chi phí và Số lượng từ Table2_2 đến các giá trị.
- Giá trị của các cột chi phí và số lượng trong bảng tổng hợp là bất thường. Chúng đang hiển thị các giá trị giống nhau cho mỗi và mọi hàng.
- Để giải quyết vấn đề này, hãy nhấp vào TẠO trên hộp Cảnh báo màu vàng .
- Trong Chỉnh sửa mối quan hệ cửa sổ, chọn Table2_2 và Bảng 1_2 trong menu thả xuống đầu tiên và thứ hai.
- Sau đó, chọn Id sản phẩm trong cả Cột (Nước ngoài) và Cột có Liên quan (Chính) menu thả xuống.
- Nhấp vào OK sau cái này.
- Sau khi nhấp vào OK , bạn sẽ nhận thấy rằng Mối quan hệ hiện đã giải quyết được vấn đề.
- Chi phí và Lợi nhuận các cột hiện đang hiển thị các giá trị chính xác.
Những điều cần nhớ
- Trong 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 phải từ Cột có Liên quan (Chính) bàn. Nếu không, nó sẽ tạo ra các giá trị sai trong bảng tổng hợp
- Nhấp vào Thêm dữ liệu vào mô hình dữ liệu là điều bắt buộc. Nếu không, sẽ không thể tạo mối liên hệ giữa họ.
Kết luận
Tóm lại, vấn đề làm thế nào chúng ta có thể tạo Mối quan hệ trong Excel với Bản sao giá trị được trả lời ở đây theo 2 cách khác nhau.
Đối với sự cố này, có sẵn một sổ làm việc để tải xuống nơi bạn có thể thực hành các phương pháp này.
Vui lòng đặt bất kỳ câu hỏi hoặc phản hồi thông qua phần bình luận. Bất kỳ đề xuất nào để cải tiến Exceldemy cộng đồng sẽ được đánh giá cao