Cách tìm hàng ô trùng lặp và xóa hàng loạt trong Excel

Trong quá trình sử dụng Execl, bạn có thể gặp phải các giá trị bị trùng lặp khiến bạn cảm thấy khó chịu. Vì vậy, có một số phương pháp để tìm và loại bỏ chúng. Tuy nhiên, trước khi thực hiện các bước này bạn nên tạo một bản sao lưu của bảng Excel trước khi xóa bản sai. Hãy cùng Tekzone tìm hiểu về các bước tìm hàng ô trùng lặp và xóa hàng loạt trong bài viết dưới dây nhé.

1. Sử dụng nút Xóa trùng lặp

Phương pháp nhanh nhất để tìm và xóa các bản sao trong Excel là sử dụng nút “Remove Duplicates” của Excel. Phương pháp này cho phép bạn tìm kiếm các bản sao dựa trên dữ liệu trong một hoặc nhiều cột. Nó loại bỏ toàn bộ hàng khi tìm thấy bản sao.

Nhấp vào bất kỳ ô nào có dữ liệu trên bảng tính của bạn. Đây không nhất thiết phải là ô, cột hoặc hàng mà bạn muốn xóa trùng lặp.

Chọn tab “Data” và nhấp vào nút “Remove Duplicates” trên thanh công cụ.

Kiểm tra (các) cột mà bạn muốn xóa trùng lặp. Tất cả các cột được kiểm tra theo mặc định. Ngoài ra, nếu cột của bạn không có tiêu đề, hãy bỏ chọn hộp “My data has headers box” để bao gồm hàng đầu tiên. Nhấp vào “OK” sau khi bạn đã chọn các cột mong muốn.

Excel cung cấp cho bạn một thông báo cho biết có bao nhiêu hàng đã bị xóa và còn lại bao nhiêu hàng. Phương pháp này loại bỏ toàn bộ hàng, không chỉ giá trị. Nhấn “OK” để chấp nhận kết quả và quay lại bảng tính của bạn.

Nếu bạn không thích kết quả, hãy nhấn Ctrl+ Zđể trả lại các giá trị đã xóa vào trang tính của bạn.

2. Tìm dữ liệu trùng lặp bằng Conditional Formatting

Nếu bạn muốn xem lại các bản sao trong Excel trước khi xóa bất kỳ thứ gì, hãy thử sử dụng định dạng có điều kiện. Điều này tìm thấy các bản sao trong Excel nhưng không xóa chúng.

Chọn (các) cột hoặc toàn bộ trang tính nơi bạn muốn tìm kiếm các giá trị trùng lặp.

Mở tab “Home” và nhấp vào nút “Conditional Formatting” 

Chọn “Highlight Cell Rules” và chọn “Duplicate Values.”

Chọn màu mà bạn muốn các giá trị trùng lặp xuất hiện. Bạn cũng có thể chuyển “Rule Type” từ “Duplicate Values” thành “Unique Values” nếu bạn muốn thấy các giá trị duy nhất được đánh dấu. Nhấp vào “Done” khi bạn hoàn tất.

Xem lại các ô được đánh dấu và xóa các ô trùng lặp mà bạn không muốn. Trong ví dụ của tôi, tôi chỉ muốn xóa các hàng trong đó mọi giá trị đều bị trùng lặp.

3. Loại bỏ các bản sao bằng Conditional Filter

Nếu bạn muốn loại bỏ các bản sao trong Excel dễ dàng hơn sau khi sử dụng format có điều kiện, hãy sử dụng bộ lọc Excel dựa trên màu của ô để chỉ hiển thị các giá trị trùng lặp hoặc duy nhất. Với tùy chọn này, bạn sẽ xóa toàn bộ (các) hàng theo cách thủ công hoặc chỉ các ô trùng lặp.

Thiết lập format có điều kiện dựa trên phần trước.

Mở tab  “Home”, chọn nút “Sort & Filter” và chọn “Filter”.

Mũi tên thả xuống sẽ xuất hiện trên tiêu đề của mỗi cột. Nhấp vào mũi tên và chọn “Filter by Color”.Chọn màu của các ô trùng lặp để chỉ xem những ô đó. Hoặc chọn “No fill” để chỉ chọn các giá trị duy nhất.

Xin lưu ý rằng không có bản sao nào (kể cả giá trị ban đầu) được hiển thị khi bạn chọn “No fill”. Nếu bạn vẫn muốn xem tất cả các giá trị, hãy chọn “Filter by Color”. Thao tác này sẽ đặt các ô có màu hoặc các ô không có màu ở đầu danh sách.

Ví dụ: bạn đang sắp xếp theo màu của các ô trùng lặp. Điều này cho phép bạn xem lại các bản sao và xóa bất kỳ hàng hoặc giá trị nào bạn không muốn giữ lại. Nếu bạn chọn chỉ hiển thị các giá trị duy nhất, bạn có thể sao chép hoặc di chuyển tất cả dữ liệu hiển thị vào một trang tính mới. Điều này sẽ chỉ để lại các bản sao phía sau.

Khi bạn hoàn tất việc chỉnh sửa dữ liệu, hãy nhấp lại vào mũi tên thả xuống và chọn “Clear Filter”.

Nếu bạn muốn xóa mũi tên thả xuống, hãy chọn “Sort & filter” từ thanh công cụ và chọn “Filter”.  Điều này sẽ xóa tất cả các bộ lọc.

4. Tìm bản sao trong Excel bằng Formula

Các công thức Excel có thể thực hiện hầu hết mọi tác vụ trên bảng tính bao gồm cả việc tìm các bản sao. Có một số công thức khác nhau mà bạn có thể sử dụng dựa trên những gì bạn muốn xem. Tất cả đều dựa trên các COUNT functions. Những công thức này không loại bỏ các bản sao. Bạn phải làm điều đó một cách thủ công khi chúng được tìm thấy. Cho dù bạn sử dụng công thức nào thì quy trình đều giống nhau:

Tạo một cột mới trên cùng một trang tính. Tôi dán nhãn cho mình là “Duplicates”

Chọn ô trống đầu tiên trong cột mới của bạn. Nhập công thức bạn muốn (xem các công thức khác nhau bên dưới các bước) vào thanh hàm/công thức hoặc trong chính ô đó.

Nếu bạn muốn mở rộng công thức sang các ô khác trong cột, hãy nhấp và giữ hình vuông nhỏ ở dưới cùng bên phải của ô chứa công thức. Kéo xuống dưới để điền bao nhiêu ô tùy thích.

Bây giờ bạn đã biết cách chèn công thức, đây là những lựa chọn tốt nhất để sử dụng:

• =COUNTIF(Range, Criteria) >1  – Phạm vi là cột của bạn và tiêu chí là ô trên cùng. Điều này hoạt động tốt để tìm các bản sao trong một cột. Ví dụ: để xem các bản sao chỉ dựa trên cột D, bạn nên sử dụng =COUNTIF(D:D, D2) >1. Kết quả “True” có nghĩa là trùng lặp, trong khi “False” có nghĩa là duy nhất.

• Nếu bạn không muốn tham chiếu ô thay đổi khi bạn kéo nó, hãy sử dụng tham chiếu ô tuyệt đối thay thế. Ví dụ: tôi muốn =COUNTIF($D$2:$D$105, $D2) >1kiểm tra toàn bộ cột D.
• Nếu bạn muốn hiển thị nội dung nào đó không phải True hoặc False, hãy đặt COUNTIF bên trong hàm IF: =IF(COUNTIF($D$2:$D$105, $D2) > 1, "Duplicate", " "). Điều này sẽ hiển thị “Duplicate” cho các bản sao và một ô trống cho các giá trị duy nhất. Bạn có thể dễ dàng sử dụng bất kỳ từ nào bạn muốn, chẳng hạn như  “Duplicate” và “Unique”.
• Nếu muốn so sánh nhiều cột cùng một lúc, bạn sẽ cần sử dụng tham chiếu ô tuyệt đối và câu lệnh IF, chẳng hạn như: =IF(COUNTIFS($D$2:$D$105,$D2,$E$2:$E$105,$E2,$F$2:$F$105,$F2) >1, "Duplicate", "Unique")Công thức này sẽ chỉ hiển thị “Duplicate” nếu các giá trị trong D, E và F giống nhau. Nếu bất kỳ mục nào trong số ba mục này là duy nhất liên tiếp thì “Unique” sẽ hiển thị thay thế.
• Nếu bạn chỉ muốn hiển thị các giá trị trùng lặp chứ không phải lần xuất hiện đầu tiên của giá trị, thay vào đó hãy thử cách này: Tùy =IF(COUNTIFS($D$2:$D2,$D2,$E$2:$E2,$E2,$F$2:$F2,$F2) >1, "Duplicate", "Unique")chọn này chỉ hiển thị “Duplicate” cho lần xuất hiện thứ hai trở lên.

5. Đếm số lượng bản sao bằng Formula

Bạn có thể sử dụng công thức trên mà không cần > 1văn bản để đếm số lượng trùng lặp trong một cột. Hai công thức sẽ trở thành =COUNTIF($D$2:$D$105, $D2)và =COUNTIF(D:D, D2). Nhập cột mới cho nó hiển thị số lần mỗi mục xuất hiện trong dữ liệu.

Giá trị “1” có nghĩa đó là giá trị duy nhất. Bất cứ điều gì khác bằng bản sao.

6. Loại bỏ các giá trị trùng lặp bằng Formula

Khi bạn đã tìm thấy các giá trị trùng lặp (Phần 4) hoặc số lượng trùng lặp (Phần 5), hãy sử dụng phương pháp lọc để loại bỏ các giá trị trùng lặp và giữ lại các giá trị duy nhất.

Bấm vào bất kỳ vị trí nào trong cột có giá trị hoặc số lượng trùng lặp của bạn. Chuyển đến “Home tab -> Sort & Filter -> Filter” để bật hộp thả xuống bộ lọc trên tiêu đề cột.

Nhấp vào mũi tên thả xuống trong cột trùng lặp của bạn.

Kiểm tra (các) giá trị bạn muốn giữ, bỏ chọn những giá trị bạn không muốn và nhấp vào “Apply”. Ẩn tất cả các bản sao bằng cách chỉ chọn “Unique,” “False,” hoặc bất kỳ nhãn nào của bạn cho các giá trị duy nhất dành cho phương pháp giá trị trùng lặp và “1” để Đếm các bản sao. Chỉ xem các bản sao bằng cách bỏ chọn số nhận dạng duy nhất.

Chọn các hàng hiển thị bằng cách nhấn Ctrl+ C. Nếu vì bất kỳ lý do gì, việc này chọn tất cả các hàng, kể cả các hàng bị ẩn, thay vào đó hãy sử dụng shortcutAlt+ ;.

Sao chép các giá trị duy nhất hoặc trùng lặp (tùy theo bạn chọn hiển thị) sang một trang tính khác. Xóa các giá trị đó khỏi trang tính gốc.

Chuyển đến “Sort & Filter -> Filter”. Nhấp vào “Filter” sẽ xóa bộ lọc và hiển thị tất cả các giá trị trùng lặp hoặc duy nhất còn lại.

Từ đây, duyệt qua các ô trùng lặp và xóa các ô hoặc hàng bạn không còn muốn nữa. Hãy nhớ rằng lần xuất hiện ban đầu được liệt kê cùng với các giá trị trùng lặp.

7. Xóa dữ liệu trùng lặp bằng Advanced Filters

Cho đến nay, chúng tôi chỉ sử dụng các bộ lọc cơ bản nhưng bạn cũng có thể tìm và xóa các bản sao trong Excel bằng các bộ lọc nâng cao.

Chọn cột bạn muốn lọc. Mở tab “Data” và nhấp vào “Advanced”.

Trong hộp”Advanced Filter”, chọn “Filter the list, in-place”. Điều này ẩn các bản sao trong cùng một tập dữ liệu. Sau đó, bạn có thể sao chép-dán thủ công các giá trị duy nhất vào một vị trí khác trong cùng một trang tính hoặc vào một trang tính riêng biệt.

Nếu bạn chưa chọn (các) cột của mình, hãy chọn chúng. Chúng sẽ tự động hiển thị trong trường “List range” Để trống “Criteria range”

Chọn hộp bên cạnh “Unique records only,” và nhấn “OK”.

Điều đó sẽ hiển thị các giá trị duy nhất trong dữ liệu của bạn. Sử dụng shortcut  Alt+ ;để chọn các hàng hiển thị, chỉ khi bạn muốn thực hiện bất kỳ hành động nào trên chúng, chẳng hạn như sao chép sang vị trí khác.

Ngược lại, hãy chọn “Copy to another location” trong hộp Advanced Filter nếu bạn muốn Excel tự động sao chép các giá trị duy nhất sang một vị trí khác trong cùng một trang tính.

Trước tiên hãy chọn “List range”. Bạn có thể giữ trống “Criteria range”

Nhấp một lần vào trường “Copy to” và chọn các hàng trên trang tính nơi bạn muốn sao chép dữ liệu duy nhất.

Đảm bảo hộp bên cạnh “Unique records only” được chọn.

Phương pháp này ẩn toàn bộ hàng trùng lặp, không chỉ các giá trị. Nó cũng ẩn sự xuất hiện ban đầu của giá trị trùng lặp, không chỉ các phiên bản trùng lặp. Bạn sẽ muốn xem các bản sao để trích xuất các giá trị ban đầu.

8. Loại bỏ các bản sao Excel bằng Power Query

Power Query có thể loại bỏ các giá trị trùng lặp trong Excel, như minh họa bên dưới. Phương pháp này loại bỏ toàn bộ hàng trùng lặp.

Mở tab “Data” và chọn “From Table/Range.”

Tất cả dữ liệu của bạn sẽ tự động được chọn. Nếu không, hãy nhập toàn bộ phạm vi của trang tính vào hộp “Create Table”. Nhấp vào “OK” khi bạn hoàn tất.

Phần mềm chỉnh sửa “Power Query” sẽ mở ra. Chọn các cột và nhấp chuột phải vào tiêu đề cột đã chọn. Chọn “Remove duplicates” từ menu. Thao tác này sẽ loại bỏ tất cả các bản sao trong trình soạn thảo Power Query. Trong công cụ này, lần xuất hiện ban đầu vẫn còn trong danh sách.

Nếu bạn muốn xóa các mục trùng lặp khỏi toàn bộ bảng, hãy nhấp vào nút “Table” ở góc trên cùng bên trái và chọn “Keep duplicates” để chỉ hiển thị các mục trùng lặp và xóa phần còn lại.

Điều này hiển thị tất cả các bản sao, bao gồm cả các mục gốc. Để xóa các mục bổ sung và chỉ để lại các mục gốc, hãy nhấp lại vào “Table” và chọn “Remove duplicates”.

Nhấp vào “Close and Load” ở trên cùng để mở bảng trong tab mới của cùng một sổ làm việc. Chỉ kết quả truy vấn của bạn mới được tải. Thao tác này không xóa bất cứ thứ gì khỏi trang tính gốc.

9. Sử dụng PivotTable

Sử dụng PivotTable để chỉ hiển thị các giá trị duy nhất trong dữ liệu của bạn, nhờ đó loại bỏ các mục nhập trùng lặp. Điều này thực tế không xóa bất kỳ hàng hoặc giá trị nào khỏi dữ liệu gốc của bạn; nó chỉ cho bạn thấy những giá trị duy nhất.

Mở tab “Insert” và chọn “PivotTable”. Chọn “From Table/Range”

Chọn bảng hoặc dải ô nơi bạn muốn ẩn các giá trị trùng lặp. Toàn bộ tập hợp giá trị của bạn phải được chọn theo mặc định. Nếu không, nhấn Ctrl+ Ađể chọn mọi thứ. Hoặc sử dụng chuột để chọn phạm vi tùy chỉnh.

Chọn xem nên đặt PivotTable trong cùng một trang tính hay trong một trang tính mới. Nếu đó là trang tính hiện có, hãy nhập hoặc chọn ô bạn muốn sử dụng trong hộp “Location”. Nhấp vào “OK.”

Trong thanh bên PivotTable, hãy chọn hoặc kéo các cột bạn muốn trích xuất các giá trị duy nhất vào phần “Rows”.

Bạn sẽ cần format PivotTable để hiển thị nó ở dạng bảng. Để làm điều đó, hãy chuyển đến tab “Design” và thực hiện các bước sau:

Chọn “Report Layout -> Show in Tabular Form”

Chọn “Subtotals -> Do not show subtotals.”

Chọn “Report Layout -> Repeat All Item Labels.”

Chọn “Grand Totals -> Off for Rows and Columns.”

Bạn sẽ nhận được một PivotTable với các giá trị duy nhất ở dạng bảng.

Với rất nhiều cách để tìm và loại bỏ các bản sao trong Excel, không có lý do gì phải thực hiện lại theo cách thủ công. Chọn phương pháp yêu thích của bạn và chạy với nó.