Hướng dẫn find duplicates between two sheets in excel - tìm các bản sao giữa hai trang tính trong excel

Trong Microsoft Excel, khi bạn phải tìm kiếm các bản sao hoặc các giá trị phù hợp trên nhiều bảng tính, bạn sẽ tìm thấy rất nhiều công thức phù hợp để đáp ứng mục tiêu. Sau khi tìm thấy các trận đấu hoặc bản sao, bạn cũng có thể làm nổi bật các ô tương ứng với các màu cụ thể hoặc với các phông chữ văn bản khác nhau. Trong bài viết này, bạn sẽ tìm thấy những phương pháp đó để làm nổi bật các bản sao trên nhiều tờ với các ví dụ và minh họa thích hợp.

Tải xuống Sổ làm việc thực hành

Bạn có thể tải xuống sổ làm việc Excel mà chúng tôi đã sử dụng để chuẩn bị bài viết này.

3 cách tiếp cận phù hợp để làm nổi bật các bản sao trên nhiều bảng tính trong Excel

Để làm nổi bật các bản sao hoặc phù hợp trên nhiều bảng tính, chúng ta phải chọn tùy chọn định dạng có điều kiện. Sau khi thiết lập một công thức quy tắc mới để tìm bản sao trên nhiều bảng tính, chúng tôi phải chọn định dạng ô với màu sắc hoặc thiết kế văn bản. Do đó, các ô tương ứng với các giá trị trùng lặp trong bảng tính đã chọn sẽ được tô sáng với các định dạng được xác định.Conditional Formatting option. After setting up a new rule formula to find the duplicates over multiple worksheets, we have to select the cell format with colors or text designs. Thus the corresponding cells with the duplicate values in the selected worksheet will be highlighted with the defined formats.

1. Sử dụng chức năng Countif để làm nổi bật các trận đấu trên bảng tính Excel

Hình ảnh sau đại diện cho một bảng tính có tên Sheet1. Nó chứa hai cột hiển thị một số ID thứ tự ở bên trái và bên phải hiển thị các ID đang được vận chuyển.Sheet1. It contains two columns showing some order IDs on the left and the right one shows the IDs that are in transit.

Trong bảng tính thứ hai có tên Sheet2, hai cột còn lại đang nằm với danh sách các ID thứ tự đã được phân phối ở bên trái và ngày giao hàng tương ứng ở bên phải.Sheet2, the other two columns are lying with a list of the order IDs that have been delivered already on the left and the corresponding delivery dates on the right.

Bây giờ chúng tôi sẽ tìm kiếm tất cả các bản sao của ID đặt hàng trên bảng1 và sheet2. ID thứ tự phù hợp trong Sheet1 sẽ được tô sáng với một màu được chỉ định sau đó. Vì vậy, hãy để Lôi đi qua các quy trình sau đây để đáp ứng các mục tiêu của chúng tôi.Sheet1 and Sheet2. The matched order IDs in Sheet1 will be highlighted with a specified color then. So, let’s go through the following procedures now to meet our objectives.

📌 Bước 1: Step 1:

Từ Sheet1, chọn phạm vi của các ô trong đó các giá trị trùng lặp sẽ được tô sáng.Sheet1, select the range of cells where the duplicate values will be highlighted.

Theo Ribbon, chọn lệnh quy tắc mới từ việc thả xuống định dạng có điều kiện.Home ribbon, select the New Rule command from the Conditional Formatting drop-down.

Một hộp thoại có tên ‘Quy tắc định dạng mới sẽ xuất hiện.‘New Formatting Rule’ will appear.

📌 Bước 2: Step 2:

Từ các tùy chọn loại quy tắc, chọn Sử dụng công thức để xác định trong các ô để định dạng.Rule Type options, select ‘Use a formula to determine within cells to format’.

Trong hộp công thức, gõ:

=COUNTIF(Sheet2!$B$5:$B$14, Sheet1!B5)

Nhấn định dạng.Format.

📌 Bước 3: Step 3:

Trong cửa sổ Tế bào định dạng, chọn một màu để làm nổi bật các bản sao.Format Cells window, select a color for highlighting the duplicates.

Nhấn OK.OK.

📌 Bước 4: Step 4:

Bạn sẽ tìm thấy bản xem trước của ô được định dạng với văn bản trong hộp thoại Quy tắc định dạng mới.New Formatting Rule dialog box.

Nhấn OK.OK.

📌 Bước 4:Sheet1, you’ll see the highlighted cells with the order IDs that are also present in Sheet2.

Bạn sẽ tìm thấy bản xem trước của ô được định dạng với văn bản trong hộp thoại Quy tắc định dạng mới.COUNTIF function here to define the criteria for highlighting the cells in Sheet1. The COUNTIF function looks for each order ID of Sheet1 in Sheet2 and returns the occurrence of each duplicate for the corresponding order ID. When we input this formula in the Rule Description box of the New Formatting Rule window, the application of the Conditional Formatting will look for the cells of the specified range in Sheet1 where the formula returns non-zero values only and thereby highlight the corresponding cells only.

Cuối cùng trong Sheet1, bạn sẽ thấy các ô được tô sáng với ID thứ tự cũng có trong Sheet2.

Chúng tôi đã sử dụng chức năng Countif ở đây để xác định các tiêu chí để làm nổi bật các ô trong Sheet1. Hàm Countif tìm kiếm từng ID đơn hàng của Sheet1 trong Sheet2 và trả về sự xuất hiện của từng bản sao cho ID thứ tự tương ứng. Khi chúng ta nhập công thức này vào hộp mô tả quy tắc của cửa sổ quy tắc định dạng mới, việc áp dụng định dạng có điều kiện sẽ tìm kiếm các ô của phạm vi được chỉ định trong trang1 trong đó công thức chỉ trả về các giá trị khác không và do đó chỉ làm nổi bật các ô tương ứng .Sheet2. In Sheet1, the corresponding order ID will be highlighted with another color or cell format.

📌 Bước 1: Step 1:

Từ Sheet1, chọn phạm vi của các ô trong đó các giá trị trùng lặp sẽ được tô sáng.Sheet1, select the range of cells for the order IDs again.

Theo Ribbon, chọn lệnh quy tắc mới từ việc thả xuống định dạng có điều kiện.Home ribbon, choose the Manage Rules command from the Conditional Formatting drop-down.

Một hộp thoại có tên ‘Quy tắc định dạng mới sẽ xuất hiện.Conditional Formatting Rules Manager will appear.

📌 Bước 2: Step 2:

Từ các tùy chọn loại quy tắc, chọn Sử dụng công thức để xác định trong các ô để định dạng.‘Duplicate Rule’. This will create a duplicate of your previously defined rule.

Trong hộp công thức, gõ:Edit Rule and the Edit Formatting Rule window will show up.

📌 Bước 3: Step 3:

Trong cửa sổ Tế bào định dạng, chọn một màu để làm nổi bật các bản sao.Rule Description, enable editing and add “>1” only at the end of the formula.

Nhấn OK.Format option.

📌 Bước 4: Step 4:

Bạn sẽ tìm thấy bản xem trước của ô được định dạng với văn bản trong hộp thoại Quy tắc định dạng mới.

Nhấn OK.OK.

📌 Bước 4: Step 5:

Bạn sẽ tìm thấy bản xem trước của ô được định dạng với văn bản trong hộp thoại Quy tắc định dạng mới.OK again.

Cuối cùng trong Sheet1, bạn sẽ thấy các ô được tô sáng với ID thứ tự cũng có trong Sheet2. Step 6:

Chúng tôi đã sử dụng chức năng Countif ở đây để xác định các tiêu chí để làm nổi bật các ô trong Sheet1. Hàm Countif tìm kiếm từng ID đơn hàng của Sheet1 trong Sheet2 và trả về sự xuất hiện của từng bản sao cho ID thứ tự tương ứng. Khi chúng ta nhập công thức này vào hộp mô tả quy tắc của cửa sổ quy tắc định dạng mới, việc áp dụng định dạng có điều kiện sẽ tìm kiếm các ô của phạm vi được chỉ định trong trang1 trong đó công thức chỉ trả về các giá trị khác không và do đó chỉ làm nổi bật các ô tương ứng .Conditional Formatting Rules Manager dialog box, the second rule is embedded now.

Làm nổi bật nhiều bản sao trên hai bảng tínhOK for the last time and you’re done.

Bây giờ, hãy để giả sử, chúng tôi có nhiều bản sao cho ID đặt hàng trong Sheet2. Trong Sheet1, ID thứ tự tương ứng sẽ được tô sáng với định dạng màu hoặc ô khác.Cell B13 highlighted with another color since this cell contains an order ID that is present multiple times in Sheet2.

Trong Sheet1, chọn phạm vi của ô cho ID thứ tự một lần nữa.

Theo Ribbon Ribbon, chọn lệnh Quản lý quy tắc từ việc thả xuống định dạng có điều kiện. How to Highlight Duplicates in Excel

Một hộp thoại có tên Trình quản lý quy tắc định dạng có điều kiện sẽ xuất hiện.

Chúng tôi cũng có thể kết hợp các chức năng isnumber và khớp để tìm các bản sao hoặc phù hợp trên hai bảng tính Excel. Hàm khớp trả về vị trí tương đối của một mục trong một mảng khớp với một giá trị được chỉ định theo thứ tự được chỉ định. Và hàm isNumber kiểm tra xem một giá trị có phải là số hay không.ISNUMBER and MATCH functions to find the duplicates or matches across two Excel worksheets. The MATCH function returns the relative position of an item in an array that matches a specified value in a specified order. And the ISNUMBER function checks whether a value is a number or not.

Vì vậy, công thức cần thiết trong hộp mô tả quy tắc ở đây sẽ là:Rule Description box here will be:

=ISNUMBER(MATCH(B5, Sheet2!$B$5:$B$14,0))

Chúng tôi sẽ nhận được kết quả sau đây như được tìm thấy trong phương pháp trước đó.

Công thức này hoạt động như thế nào trong định dạng có điều kiện? How Does This Formula Work in Conditional Formatting?

• Hàm khớp tìm kiếm các kết quả phù hợp của ID thứ tự từ hai bảng tính và trả về số hàng của ID thứ tự tương ứng trong Sheet1. Nếu hàm không tìm thấy khớp, nó sẽ trả về giá trị lỗi.MATCH function looks for the matches of the order IDs from two worksheets and returns the row number of the corresponding order ID in Sheet1. If the function does not find a match, it returns an error value.
• Hàm isNumber chỉ tìm kiếm các giá trị số và bỏ qua các giá trị lỗi được tìm thấy bởi hàm khớp. Do đó, hàm trả về đúng cho dữ liệu số và sai cho các giá trị lỗi.ISNUMBER function looks for the numeric values only and ignores the error values found by the MATCH function. Thus the function returns TRUE for the numeric data and FALSE for the error values.
• Cuối cùng, định dạng có điều kiện làm nổi bật các trận đấu dựa trên giá trị boolean chỉ đúng.Conditional Formatting highlights the matches based on the boolean value ‘TRUE’ only.

3. Áp dụng chức năng Vlookup để làm nổi bật các hàng trùng lặp trên nhiều bảng tính

Bây giờ, chúng tôi sẽ chèn chức năng VLOOKUP trong quy tắc định dạng mới. Hàm Vlookup tìm kiếm một giá trị trong cột ngoài cùng bên trái của bảng và sau đó trả về một giá trị trong cùng một hàng từ cột được chỉ định.VLOOKUP function in the New Formatting Rule. The VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row from the specified column.

Công thức cần thiết với chức năng VLOOKUP trong hộp quy tắc sẽ là:VLOOKUP function in the Rule Box will be:

=VLOOKUP(B5,Sheet2!B5:C14,,FALSE)

Và hình ảnh sau đây cho thấy các ô được tô sáng trong đó ứng dụng của chức năng VLookUP đã trả về các đầu ra hợp lệ.VLOOKUP function has returned valid outputs.

Kết luận từ

Tôi hy vọng tất cả các phương pháp đơn giản này được đề cập ở trên bây giờ sẽ giúp bạn áp dụng chúng trong bảng tính Excel của bạn khi cần thiết. Nếu bạn có bất kỳ câu hỏi hoặc phản hồi, xin vui lòng cho tôi biết trong phần bình luận. Hoặc bạn có thể kiểm tra các bài viết khác của chúng tôi liên quan đến các chức năng Excel trên trang web này.

• Cách làm nổi bật các hàng trùng lặp theo Excel (3 cách)
• Làm nổi bật các bản sao trong hai cột trong Excel