Công thức loại bỏ trùng lặp trong Excel là gì

Bạn có thể loại bỏ các bản sao trong Excel theo nhiều cách. Khi bạn sử dụng công cụ Remove Duplicates, Excel sẽ giữ phiên bản đầu tiên và các bản sao còn lại trong tập dữ liệu sẽ bị xóa. Việc xóa các hàng trùng lặp trong danh sách hoặc tập dữ liệu là điều phổ biến để dữ liệu có thể được sắp xếp, lọc và tóm tắt. Bạn sẽ cần quyết định những gì bạn cho là trùng lặp (dựa trên một hoặc nhiều trường hoặc cột)

Hãy giơ tay nếu bạn đã từng nhập dữ liệu vào Excel và ngay lập tức tự hỏi: "Đợi đã. Tôi đã nhập chưa?" Cho dù đó là lỗi nhập hoặc lỗi của con người, dữ liệu trùng lặp làm cho bảng tính của bạn kém hữu ích hơn

Làm cho bảng tính của bạn hoạt động cho bạn

Tự động hóa Microsoft Excel

Đây là cách tìm các bản sao trong Excel, để bạn có thể tự xóa chúng. Ngoài ra, tôi sẽ chỉ cho bạn hai cách để tìm và xóa các hàng trùng lặp chỉ trong một thao tác đơn giản.  

• Cách tìm các bản sao trong Excel

• Cách loại bỏ trùng lặp trong Excel

Cách tìm các bản sao trong Excel

Nếu bạn chỉ muốn tìm nội dung trùng lặp để có thể quyết định có xóa chúng hay không, cách tốt nhất là đánh dấu tất cả nội dung trùng lặp bằng định dạng có điều kiện

1. Chọn dữ liệu muốn kiểm tra thông tin trùng lặp. Sau đó, từ tab Trang chủ, chọn Định dạng có điều kiện > Đánh dấu quy tắc ô > Giá trị trùng lặp.   

   

2. Từ cửa sổ Định dạng có điều kiện xuất hiện, nhấp vào menu thả xuống bên dưới Định dạng bằng để chọn bảng màu bạn muốn sử dụng để đánh dấu các bản sao. Sau đó bấm Xong. (Mẹo. Chọn bảng phối màu có độ tương phản cao, chẳng hạn như tô màu đỏ nhạt, để cải thiện khả năng đọc. )

   

3. Bây giờ bạn có thể xem lại dữ liệu trùng lặp và quyết định xem bạn có cần xóa bất kỳ thông tin thừa nào không

   

Cách loại bỏ trùng lặp trong Excel

Nếu bạn muốn tìm và xóa các bản sao trong Excel mà không cần xem lại chúng theo cách thủ công trước, bạn có thể thực hiện việc này theo một trong hai cách sau

Sử dụng tính năng xóa trùng lặp trong Excel

1. Nhấp vào bất kỳ ô nào có chứa dữ liệu. Sau đó, chọn tab Data > Remove Duplicates.  

   

2. Từ cửa sổ Xóa trùng lặp xuất hiện, hãy chọn những cột bạn muốn đưa vào tìm kiếm dữ liệu dư thừa của mình. Nhấp vào OK. (Công cụ Remove Duplicates sẽ xóa vĩnh viễn dữ liệu trùng lặp, vì vậy bạn nên sao chép dữ liệu gốc sang trang tính khác để sao lưu. )

   

3. Excel sẽ cho bạn biết có bao nhiêu giá trị trùng lặp đã bị xóa.  

   

Sử dụng hàm UNIQUE trong Excel

Trong Excel, hàm UNIQUE trả về các giá trị duy nhất (i. e. , dữ liệu không trùng lặp) từ danh sách hoặc phạm vi. Thông báo trước duy nhất là điều này sẽ không thay thế dữ liệu hiện tại của bạn. Nó tồn tại trong cùng một bảng tính với nguồn dữ liệu ban đầu.  

Để nhận danh sách giá trị duy nhất, hãy chọn một cột trống trong bảng tính của bạn. Sau đó, nhập hàm ĐỘC ĐÁO bằng phạm vi ô bạn muốn quét để tìm các giá trị trùng lặp, chỉ để lại các giá trị duy nhất. Ví dụ,

đọc liên quan

• Cách xóa trùng lặp trong Google Sheets

• Cách sửa các lỗi thường gặp trong Excel

• 5 cách để tự động hóa Microsoft Excel

Bài viết này ban đầu được xuất bản vào tháng 6 năm 2019 bởi Justin Pot. Bản cập nhật gần đây nhất là vào tháng 10 năm 2022

Một trong những hoạt động quan trọng nhất và được sử dụng rộng rãi trong Excel là loại bỏ các giá trị trùng lặp khỏi tập dữ liệu. Hôm nay tôi sẽ chỉ cho bạn 6 cách dễ dàng sử dụng công thức để tự động loại bỏ các giá trị trùng lặp trong Excel

Mục lục ẩn

Tải sách bài tập thực hành

6 phương pháp dễ dàng sử dụng công thức để tự động loại bỏ các bản sao

1. Sử dụng hàm ĐỘC ĐÁO Excel để loại bỏ các bản sao

Loại bỏ hoàn toàn các giá trị xuất hiện nhiều lần

Giữ một bản sao của các giá trị xuất hiện nhiều lần

2. Kết hợp các hàm CONCATENATE, FILTER và COUNTIF để xóa bản sao

3. Hợp nhất các hàm IFERROR, INDEX, SMALL, CONCATENATE, IF & COUNTIF

4. Sử dụng hàm VLOOKUP của Excel để tự động xóa các bản sao

5. Tích hợp các hàm VLOOKUP, IF và ISERROR

6. Hàm UniteTEXTJOIN và COUNTIF

Cách xóa các bản sao bằng công cụ Remove Duplicates

phần thực hành

Phần kết luận

Những bài viết liên quan

Tải sách bài tập thực hành

Bạn có thể tải file Excel sau về và thực hành khi đọc bài viết này

Công thức để tự động loại bỏ các bản sao. xlsx

6 phương pháp dễ dàng sử dụng công thức để tự động loại bỏ các bản sao

Trong tập dữ liệu sau, bạn có thể thấy rằng chúng ta có các cột Tên, Điểm và Điểm của Học sinh. Tại đây, bạn có thể dễ dàng nhận thấy tên của một số học sinh đã được lặp đi lặp lại cùng với điểm số và điểm số của các em. Tiếp theo, sử dụng tập dữ liệu này, chúng ta sẽ xem qua 6 phương pháp dễ dàng sử dụng công thức để tự động loại bỏ các bản sao trong Excel. Ở đây, chúng tôi đã sử dụng Excel 365. Bạn có thể sử dụng bất kỳ phiên bản Excel nào có sẵn

1. Sử dụng hàm ĐỘC ĐÁO Excel để loại bỏ các bản sao

Trong phương pháp này, chúng tôi sẽ sử dụng hàm UNIQUE để tự động loại bỏ các bản sao trong Excel bằng công thức. Ở đây, cần lưu ý một điều là hàm ĐỘC ĐÁO chỉ khả dụng trong Excel 365 và các phiên bản Excel cao hơn

Bạn có thể xóa các giá trị trùng lặp khỏi tập dữ liệu theo hai cách

• Xóa hoàn toàn các giá trị xuất hiện nhiều lần
• Giữ một bản sao của các giá trị xuất hiện nhiều lần

Sử dụng chức năng ĐỘC ĐÁO, bạn có thể loại bỏ các bản sao theo cả hai cách

Loại bỏ hoàn toàn các giá trị xuất hiện nhiều lần

Tại đây, chúng tôi sẽ xóa hoàn toàn các giá trị trùng lặp khỏi tập dữ liệu của mình

• Trước hết, chúng ta sẽ gõ công thức sau vào ô G5

=UNIQUE(B5:D15,FALSE, TRUE)

Phân tích công thức

• DUY NHẤT(B5. D15,FALSE, TRUE) → hàm UNIQUE tìm một số dữ liệu duy nhất trong một mảng
• B5. D15 → chỉ ra một mảng
• FALSE → biểu thị by_col
• TRUE → chỉ ra rằng các bản sao sẽ bị xóa hoàn toàn

• Sau đó, nhấn ENTER
• Tại đây, ngay khi bạn nhấn ENTER, các giá trị duy nhất sẽ được hiển thị
• Kết quả là bạn có thể thấy các giá trị duy nhất trong các ô G5. I11

ghi chú

• Ba tên học sinh trùng nhau. David Moyes, Angela Hopkins và Brad Milford
• Trong số đó, David Moyes và Brad Milford đã bị loại bỏ hoàn toàn
• Angela Hopkins không bị xóa vì điểm và điểm của hai Angela Hopkins không giống nhau. Điều đó có nghĩa là họ là hai sinh viên khác nhau

Giữ một bản sao của các giá trị xuất hiện nhiều lần

Tại đây, chúng tôi sẽ xóa hoàn toàn các giá trị trùng lặp khỏi tập dữ liệu của mình

• Ở nơi đầu tiên, Để giữ một bản sao của các giá trị xuất hiện nhiều lần, chúng tôi sẽ nhập công thức sau vào ô G5

=UNIQUE(B5:D15,FALSE,FALSE)

• Sau đó nhấn ENTER
• Do đó, bạn có thể thấy kết quả trong các ô G5. tôi13
• Ở đây chúng tôi đã giữ một bản sao của tất cả các tên trùng lặp, ngoại trừ Angela Hopkins
• Cả Angela Hopkins đã bị giữ lại vì họ là hai sinh viên khác nhau

Nội dung liên quan. Cách xóa trùng lặp và giữ giá trị đầu tiên trong Excel

2. Kết hợp các hàm CONCATENATE, FILTER và COUNTIF để xóa bản sao

Trong phương pháp này, chúng tôi sẽ sử dụng kết hợp các hàm FILTER, CONCATENATE và COUNTIF để loại bỏ các giá trị trùng lặp trong Excel bằng công thức

bước

• Trước hết, chúng ta sẽ lấy một cột mới và chèn công thức sau vào ô E5

=CONCATENATE(B5:B15,C5:C15,D5:D15)

Ở đây, hàm CONCATENATE tính tổng ba cột thành một

• Sau đó, nhấn ENTER
• Do đó, bạn có thể thấy cột mới được sắp xếp tự động với các giá trị được hợp nhất

• Sau đó, chúng ta sẽ chuyển sang một cột mới khác và chèn công thức sau vào ô G5

=FILTER(B5:B15,COUNTIF($E$5:$E$15,$E$5:$E$15)=1)

Phân tích công thức

• COUNTIF($E$5. $E$15,$E$5. $E$15)=1 → hàm COUNTIF đếm số ô đáp ứng tiêu chí
• BỘ LỌC(B5. B15,COUNTIF($E$5. $E$15,$E$5. $E$15)=1) → hàm FILTER lọc một dải dữ liệu dựa trên các tiêu chí đã xác định

• Sau đó, nhấn ENTER
• Do đó, bạn có thể thấy các giá trị duy nhất trong ô G5. G11
• Hơn nữa, chúng tôi sẽ kéo Fill Handle về phía phải cho đến tổng số cột của bạn (3 trong ví dụ này)

• Do đó, bạn sẽ nhận được toàn bộ tập dữ liệu mà không có giá trị trùng lặp

Ghi chú

• Trong phương pháp này, bạn có thể xóa tất cả các giá trị xuất hiện nhiều lần
• Nhưng bạn không thể giữ một bản sao của các giá trị trùng lặp như đã đề cập trong phương pháp trước đó

Nội dung liên quan. Cách xóa các bản sao dựa trên tiêu chí trong Excel (4 phương pháp)

bài đọc tương tự

• Cách xóa các hàng trùng lặp trong bảng Excel
• Xóa hàng trùng dựa trên 2 cột trong Excel [4 cách]
• Excel VBA. Loại bỏ các bản sao khỏi một mảng (2 ví dụ)
• Cách xóa các bản sao trong Bảng tính Excel (7 phương pháp)
• Sửa chữa. Excel Loại bỏ các bản sao không hoạt động (3 giải pháp)

3. Hợp nhất các hàm IFERROR, INDEX, SMALL, CONCATENATE, IF & COUNTIF

Hai phương pháp trước chỉ dành cho những người sử dụng các phiên bản Excel mới. Phương pháp này hữu ích khi bạn có phiên bản excel cũ hơn và muốn tự động loại bỏ các bản trùng lặp bằng công thức

Ở đây, chúng ta sẽ sử dụng kết hợp các hàm IFERROR, INDEX, SMALL, CONCATENATE, IF và COUNTIF

bước

• Ban đầu, chúng tôi sẽ lấy một cột mới và chèn công thức này vào ô E5

=CONCATENATE(B5:B15,C5:C15,D5:D15)

Phân tích công thức

• NỐI TIẾP(B5. B15,C5. C15,D5. D15) Hàm CONCATENATE hợp nhất ba cột thành một cột duy nhất
• Đây B5. B15, C5. C15 và D5. D15 là ba cột trong tập dữ liệu của tôi. Bạn sử dụng cái của bạn

• Sau đó, vì nó là Công thức mảng nên chúng ta sẽ nhấn CTRL+SHIFT+ENTER trừ khi bạn đang ở trong Office 365
• Do đó, bạn có thể thấy toàn bộ cột với các giá trị được hợp nhất

• Tiếp theo, để loại bỏ các giá trị trùng lặp, chúng ta sẽ gõ công thức sau vào ô G5

=IFERROR(INDEX(B5:D15,SMALL(IF(COUNTIF(E5:E15,E5:E15)=1,ROW(E5:E15)-ROWS(E2:E4),""),ROW(E5:E15)-ROWS(E2:E4)),{1,2,3}),"")

Phân tích công thức

• Hàm ROWS tìm ra số hàng trong một mảng
• Hàm IF thực hiện so sánh logic giữa một giá trị đã cho và giá trị mà chúng ta mong đợi
• Hàm COUNTIF đếm một số ô dựa trên tiêu chí
• Hàm INDEX tìm dữ liệu từ một dải dữ liệu
• Hàm SMALL đưa ra đầu ra của giá trị nhỏ thứ k trong một mảng
• Hàm IFERROR trả về ô trống khi công thức có lỗi

• Tại thời điểm này, nhấn ENTER
• Do đó, bạn có thể thấy rằng các ô G5. I11 không chứa bất kỳ giá trị trùng lặp nào
• Do đó, bạn có thể loại bỏ các bản sao trong Excel

Ghi chú

• Trong phương pháp này, bạn cũng có thể xóa tất cả các giá trị xuất hiện nhiều lần
• Nhưng bạn không thể giữ một bản sao của các giá trị trùng lặp như đã đề cập trong phương pháp trước đó

4. Sử dụng hàm VLOOKUP của Excel để tự động xóa các bản sao

Ở phương pháp này chúng ta sẽ sử dụng hàm VLOOKUP để tự động loại bỏ trùng lặp bằng công thức trong Excel. Ở đây, chúng tôi đã sửa đổi tập dữ liệu cho mục đích này. Do đó, bạn có thể thấy cột Danh sách sách-1 và Danh sách sách-2. Bạn có thể dễ dàng nhận thấy các giá trị trùng lặp trong Danh sách sách-1. Tiếp theo, chúng tôi sẽ sử dụng VLOOKUP để loại bỏ các bản sao

bước

• Đầu tiên, tạo một cột khác bên cạnh Giá trị đặt tên Danh sách sách-2
• Sau đây, chúng ta sẽ áp dụng hàm VLOOKUP trong cột Giá trị

• Sau đó, chọn ô D5 và viết công thức sau

=VLOOKUP(C5,$B$5:$B$14,1,FALSE)

Phân tích công thức

• VLOOKUP(C5,$B$5. $B$14,1,FALSE) → hàm VLOOKUP tìm kiếm một giá trị trong một mảng
• Lookup_value là C5
• Table_array là $B$5. $B$14
• Col_index_num là 1
• [range_lookup] là FALSE vì chúng tôi muốn đối sánh chính xác
  • Đầu ra. Chén vàng
• Giải trình. Chiếc Bát Vàng là giá trị trùng lặp

• Tiếp theo, nhấn ENTER
• Hơn nữa, chúng ta sẽ kéo công thức xuống bằng công cụ Fill Handle

• Do đó, bạn có thể thấy toàn bộ cột Giá trị
• Nếu có bất kỳ giá trị duy nhất nào trong hai cột đó, hàm sẽ trả về lỗi #N/A

• Do đó, chúng tôi đã nhận được các giá trị trùng lặp và duy nhất của mình và bây giờ chúng tôi phải lọc chúng
• Để làm điều đó, hãy chọn các tiêu đề của tập dữ liệu >> chuyển đến tab Dữ liệu
• Sau đó, từ nhóm Sort & Filter > chọn Filter

• Bây giờ chúng ta có biểu tượng Bộ lọc trong mọi cột của tập dữ liệu
• Tiếp theo, nhấp vào biểu tượng thả xuống trong cột Giá trị và từ tùy chọn sắp xếp, hãy kiểm tra #N/A và nhấp vào OK

• Cuối cùng, chúng tôi đã loại bỏ thành công các bản sao khỏi tập dữ liệu

5. Tích hợp các hàm VLOOKUP, IF và ISERROR

Trong phương pháp này, chúng tôi sẽ sử dụng kết hợp các hàm VLOOKUP, IF và ISERROR để loại bỏ các bản sao tự động trong Excel

bước

• Đầu tiên, chúng tôi sẽ sử dụng cùng một tập dữ liệu từ các phương pháp trước đó
• Sau đó, chúng ta sẽ gõ công thức sau vào ô D5

________số 8

Phân tích công thức

• VLOOKUP(C5,$B$5. $B$14,1,FALSE) → công thức này sẽ tìm ra kết quả khớp chính xác của giá trị ô C5 trong phạm vi ô $B$5. $B$14. Ở đây, Lookup_value là C5, Table_array là $B$5. $B$14. Col_index_num là 1 và [range_lookup] là (FALSE) vì chúng tôi muốn khớp chính xác
• NẾU(ISERROR(VLOOKUP(C5,$B$5. $B$14,1,FALSE)),”Duy nhất”,”Duplicate”) → Nếu ​​giá trị là true, công thức sẽ trả về “Duy nhất”. Nếu giá trị là false, công thức sẽ trả về "Duplicate''
  • Đầu ra. D5
• Giải trình. D5 trả về Trùng lặp vì công thức phát hiện một giá trị trùng lặp

• Sau đó nhấn ENTER
• Do đó, bạn có thể thấy kết quả trong ô D5
• Hơn nữa, chúng ta sẽ kéo công thức xuống bằng công cụ Fill Handle

• Do đó, bạn có thể thấy toàn bộ cột Giá trị
• Tiếp theo, chúng tôi sẽ loại bỏ các Bản sao bằng công cụ Bộ lọc
• Để làm điều đó, hãy chọn các tiêu đề của tập dữ liệu >> chuyển đến tab Dữ liệu
• Sau đó, từ nhóm Sắp xếp & Lọc, chọn Bộ lọc

• Do đó, bạn có thể thấy các tiêu đề cột có biểu tượng Bộ lọc
• Hơn nữa, chúng tôi sẽ nhấp vào biểu tượng thả xuống của cột Giá trị
• Ở đây, chúng tôi sẽ bỏ chọn các Bản sao vì chúng tôi muốn xóa chúng
• Hơn nữa, bấm OK

• Do đó, bạn có thể thấy rằng chúng tôi chỉ có các giá trị duy nhất trong tập dữ liệu

6. Hàm UniteTEXTJOIN và COUNTIF

Trong phương pháp này, chúng tôi sẽ sử dụng các hàm TEXTJOIN và COUNTIF để loại bỏ các bản sao tự động trong Excel. Ở đây, để làm như vậy, chúng tôi đã thêm các cột Kết hợp và Đếm trong tập dữ liệu của mình. Chúng ta sẽ sử dụng hàm TEXTJOIN để tìm các giá trị kết hợp trong cột Kết hợp. Sau đó, chúng ta sẽ sử dụng hàm COUNTIF để tìm số trùng trong cột Count

bước

• Đầu tiên, chúng ta sẽ gõ công thức sau vào ô D5

=TEXTJOIN("", FALSE,B5:C5)

Phân tích công thức

• TEXTJOIN(“”, FALSE,B5. C5) → hàm TEXTJOIN thêm văn bản từ nhiều phạm vi
  • Đầu ra. Frank Orwell99
• Giải trình. Frank Orwell99 là văn bản kết hợp của các ô B5 và C5

• Tại thời điểm này, nhấn ENTER
• Do đó, bạn có thể thấy kết quả trong ô D5
• Hơn nữa, chúng ta sẽ kéo công thức xuống bằng công cụ Fill Handle

• Do đó, bạn có thể xem toàn bộ cột Kết hợp

• Tiếp theo, chúng ta sẽ gõ công thức sau vào ô E5

=UNIQUE(B5:D15,FALSE, TRUE)0

Hàm COUNTIF đếm số ô dựa trên tiêu chí

• Ngoài ra, nhấn ENTER
• Cùng với đó, chúng ta sẽ kéo công thức xuống bằng công cụ Fill Handle

• Do đó, bạn có thể thấy cột Đếm đầy đủ
• Tại đây, bạn có thể thấy trong cột Count, giá trị là 1 cho giá trị duy nhất và giá trị là 2 cho giá trị trùng lặp
• Hơn nữa, chúng tôi sẽ loại bỏ các bản sao bằng cách loại bỏ các ô có 2 trong cột Đếm
• Để làm như vậy, hãy chọn tiêu đề của tập dữ liệu >> chuyển đến tab Dữ liệu
• Sau đó, từ nhóm Sắp xếp & Lọc, chọn Bộ lọc

• Do đó, bạn có thể thấy các tiêu đề cột của tập dữ liệu có biểu tượng bộ lọc
• Ngoài ra,  chúng ta sẽ nhấp vào biểu tượng thả xuống của cột Đếm
• Sau đó, chúng ta sẽ bỏ đánh dấu 2 >> nhấn OK

• Do đó, bạn có thể thấy rằng tập dữ liệu không có giá trị trùng lặp

Cách xóa các bản sao bằng công cụ Remove Duplicates

Cho đến phần cuối cùng, chúng ta đã thấy tất cả các phương pháp phù hợp để loại bỏ các bản sao bằng các công thức khác nhau. Nếu muốn, bạn cũng có thể xóa các giá trị trùng lặp khỏi tập dữ liệu của mình bằng các công cụ tích hợp sẵn của Excel. Ở đây, chúng tôi sẽ sử dụng công cụ Remove Duplicate để loại bỏ các bản sao tự động trong Excel

bước

• Trước hết, Chọn toàn bộ tập dữ liệu
• Sau đó, chuyển đến tab Dữ liệu
• Hơn nữa, từ nhóm Data Tools >> chọn Remove Duplicates

• Lúc này sẽ xuất hiện hộp thoại Remove Duplicates
• Tại đây, chúng ta sẽ tích chọn tất cả các cột >> nhấn OK

• Do đó, bạn sẽ thấy hộp thoại xác nhận xuất hiện
• Sau đó, nhấp vào OK

• Do đó, bạn có thể thấy rằng chúng tôi đã xóa Tên sinh viên trùng lặp

 Lưu ý. Trong phương pháp này, một bản sao của hàng trùng lặp sẽ vẫn còn. Bạn không thể xóa hoàn toàn các hàng trùng lặp.

Đọc thêm. Cách xóa các bản sao khỏi cột trong Excel (3 phương pháp)

phần thực hành

Bạn có thể tải xuống tệp Excel ở trên và thực hành các phương pháp đã giải thích

Phần kết luận

Ở đây, chúng tôi chỉ cho bạn 6 phương pháp dễ dàng để các công thức tự động loại bỏ các bản sao trong Excel. Cảm ơn bạn đã đọc bài viết này. Chúng tôi hy vọng nó hữu ích. Nếu bạn có bất kỳ câu hỏi nào, vui lòng cho chúng tôi biết trong phần bình luận. Bạn có thể truy cập trang web của chúng tôi Exceldemy để biết thêm các bài viết liên quan

Công thức Excel để loại bỏ trùng lặp là gì?

Phím tắt để loại bỏ các bản sao trong Excel là gì?