Kiểm tra dữ liệu trùng trong excel

-

Trong hướng dẫn này, Học Excel Online đang hướng dẫn chúng ta cách tìm quý hiếm trùng nhau vào excel. Họ sẽ tất cả cái nhìn cận cảnh về các phương pháp khác nhau nhằm ẩn các ô trùng lặp, tổng thể các mặt hàng hoặc những dữ liệu lặp liên tiếp bằng phương pháp sử dụng định dạng tất cả điều kiện.

Bạn đang xem: Kiểm tra dữ liệu trùng trong excel

Cách nhanh nhất để tìm kiếm và lưu lại các dữ liệu lặp vào Excel là áp dụng định dạng có điều kiện. Ưu điểm lớn nhất của phương pháp này là nó không chỉ hiển thị cho mình các tài liệu lặp hiện có mà còn phạt hiện với tô màu các dữ liệu lặp mới khi bạn nhập, sửa đổi hoặc chèn dữ liệu.

Ngoài ra, chúng ta cũng học được từ bài viết này một số phương pháp để làm nổi bật dữ liệu lặp tùy nằm trong vào nhiệm vụ cụ thể. Các kỹ thuật này chuyển động trong toàn bộ các phiên bản của Excel 2016, Excel 2013, Excel 2010 cùng thấp hơn.


Xem nhanh


Cách làm rất nổi bật các dữ liệu lặp trong Excel bằng cách sử dụng luật lệ built-in (với lần lặp đầu tiên)Làm rất nổi bật các bản sao trong không ít cột mà không có lần xuất hiện thêm lần đầuCách làm rất nổi bật các hàng giống nhau trong Excel giải pháp làm khá nổi bật các dữ liệu lặp vào Excel cùng với Duplicate Remover

Cách làm rất nổi bật các dữ liệu lặp vào Excel bằng phương pháp sử dụng nguyên tắc built-in (với lần lặp đầu tiên)

Đối với những người mới bắt đầu, trong tất cả các phiên bạn dạng Excel, gồm một nguyên tắc được xác định trước để triển khai nổi bật những ô trùng lặp. Để áp dụng quy tắc này trong các bảng tính, hãy thực hiện theo quá trình sau:

1. Chọn dữ liệu bạn muốn kiểm tra dữ liệu lặp. Đó có thể là một cột, một mặt hàng hoặc một dải ô. 2. Trên tab Trang chủ, trong nhóm Styles, chọn Conditional Formatting > Highlight Cells Rules > Duplicate Values…

*
3. Hành lang cửa số hộp thoại Duplicate Values sẽ mở ra với định hình Light Red Fill và Dark Red Text được lựa chọn theo mang định. Để vận dụng định dạng mặc định, chỉ cần nhấp vào OK.

Ngoài bài toán điền vào red color và format văn bản, một vài ít những định dạng được xác định trước có sẵn trong list thả xuống. Để làm trông rất nổi bật các tài liệu lặp bằng phương pháp sử dụng một số màu khác, hãy nhấp vào Custom Format… (mục sau cùng trong trình solo thả xuống) và chọn màu điền cùng / hoặc color chữ theo ý thích.

*
Mẹo. Để đánh dấu các giá trị duy nhất, hãy lựa chọn Unique ở hộp thoại bên tay trái.

Sử dụng nguyên tắc inbuilt, bạn có thể làm khá nổi bật các bản sao vào một cột hoặc trong một vài ba cột như biểu lộ trong hình ảnh chụp screen sau:

*



Chú thích. Khi vận dụng quy tắc nhân phiên bản được sản xuất trong nhị hoặc nhiều cột, Excel không so sánh các giá trị trong các cột đó, nó chỉ đơn giản làm trông rất nổi bật tất cả những trường hòa hợp trùng lặp.

Khi thực hiện quy tắc inbuilt của Excel để triển khai nổi nhảy giá trị lặp, xin lưu ý hai điều sau:

– Nó chỉ hoạt động cho những ô riêng rẽ lẻ. Để khắc ghi các mặt hàng trùng lặp, bạn cần tạo những quy tắc của riêng mình dựa trên các giá trị vào một cột rõ ràng hoặc bằng phương pháp so sánh những giá trị vào một vài cột. – Nó làm mờ các ô trùng lặp tất cả lần xuất hiện đầu tiên của dữ liệu lặp đó. Để làm khá nổi bật tất cả các phiên bản sao trừ các trường hợp mở ra lần đầu tiên, hãy sinh sản một quy tắc định dạng có điều kiện dựa trên bí quyết này.

Cách làm nổi bật các dữ liệu lặp mà không tồn tại lần xuất hiện thêm đầu tiên

Để làm khá nổi bật các tài liệu lặp tính từ bỏ lần lộ diện thứ 2 trở đi, hãy chọn các ô mà bạn muốn tô màu sắc và tạo nên quy tắc dựa trên công thức theo phong cách này:

1. Bên trên tab Trang chủ, trong đội Styles, lựa chọn Conditional Formatting > New rule > Use a formula to lớn determine which cells to lớn format. 2. Trong hộp Format values where this formula is true, nhập một công thức tương tự như sau: =COUNTIF($A$2:$A2,$A2)>1 trong các số đó A2 là ô trên thuộc của dải ô được chọn. 3. Nhấp vào nút Format… và lựa chọn màu điền sứt đậm cùng / hoặc màu sắc chữ mà các bạn muốn. 4. Cuối cùng, nhấn OK nhằm lưu và áp dụng quy tắc.

*
tác dụng là, những ô xào luộc trừ các trường hợp đầu tiên sẽ được gia công nổi nhảy với màu sắc bạn chọn:
*

Cách hiển thị các dữ liệu lặp lộ diện lần vật dụng 3, đồ vật 4 và toàn bộ các tài liệu lặp tiếp theo

Để xem những dữ liệu lặp ban đầu với lần lộ diện thứ N, ta bắt buộc tạo một quy tắc định dạng có điều kiện dựa trên phương pháp như trong lấy một ví dụ trước, với sự biệt lập duy tốt nhất là sửa chữa thay thế > 1 sinh hoạt cuối phương pháp bằng một số trong những theo yêu cầu. Ví dụ:

Để làm rất nổi bật dữ liệu lặp thứ tía và toàn bộ các ngôi trường hợp đụng hàng tiếp theo, sinh sản một quy tắc format có đk dựa trên cách làm này:

=COUNTIF($A$2:$A2,$A2)>=3

Để làm khá nổi bật dữ liệu lặp sản phẩm công nghệ 4 và toàn bộ các tài liệu lặp tiếp theo, áp dụng công thức này:

=COUNTIF($A$2:$A2,$A2)>=4

Để làm nổi bật một quý giá lặp cố định bạn sử dụng toán tử bằng (=). Ví dụ: nhằm chỉ những dữ liệu lặp lần sản phẩm 2, các bạn sẽ làm theo bí quyết sau:

=COUNTIF($A$2:$A2,$A2)=2 cách làm nổi bật các tài liệu lặp trong một dải (nhiều cột) Khi bạn có nhu cầu kiểm tra các dữ liệu lặp trên các cột, không phải bằng phương pháp so sánh các cột với nhau, các bạn hãy sử dụng 1 trong những các chiến thuật sau.

Làm nổi bật các dữ liệu lặp trong tương đối nhiều cột bao gồm cả lần xuất hiện đầu tiên

Nếu lần xuất hiện trước tiên của dữ liệu bị lặp nhiều lần cũng được xem như là một bạn dạng sao thì cách dễ nhất là thành lập công thức dữ liệu lặp vào Excel.

Hoặc, chế tác một quy tắc format có điều kiện với phương pháp này:

COUNTIF(range, top_cell)>1 Ví dụ: để triển khai nổi bật các bạn dạng sao vào dải A2:C8, phương pháp sẽ như sau:

=COUNTIF($A$2:$C$8, A2)>1

Hãy chăm chú đến việc sử dụng những tham chiếu ô tuyệt đối cho dải ($A$2:$C$8), và các tham chiếu tương đối cho ô trên cùng (A2).

*

Học Excel nhanh cực kỳ hiệu quả

Làm rất nổi bật các bạn dạng sao trong nhiều cột mà không có lần xuất hiện thêm lần đầu

Các phương án cho trường hợp này sẽ có chút tinh vi hơn vày Excel không xuất bản quy tắc sẵn.

Xem thêm: Tải Game Bạo Loạn Đường Phố Về Máy Tính, Download Game Gta Vice City Full

Để làm nổi bật các mục trùng lặp trong một vài cột bỏ lỡ lần lộ diện đầu tiên, các bạn sẽ phải tạo nên 2 luật lệ với các công thức sau:

Nguyên tắc 1. Áp dụng mang lại cột đầu tiên

Ở đây các bạn sử dụng đúng mực cùng một công thức như bọn họ đã sử dụng để triển khai nổi bật những dữ liệu lặp mà không tồn tại lần xuất hiện trước tiên trong một cột.

Trong ví dụ như này, họ đang tạo thành một quy tắc mang đến A2:A8 với công thức:

=COUNTIF($A$2:$A2,$A2)>1

Kết trái là những mục trùng lặp không có lần xuất hiện đầu tiên được ghi lại sát cột phía trái của dải ô (chỉ tất cả một mục như vậy trong trường hòa hợp của lấy một ví dụ này):

*

Quy tắc 2. Áp dụng cho tất cả các cột tiếp theo

Để làm khá nổi bật các phiên bản sao trong các cột còn sót lại (B2: C8), áp dụng công thức sau:

=COUNTIF(A$2:$A$8,B2)+COUNTIF(B$2:B2,B2)>1

Trong phương pháp trên, tác dụng COUNTIF đếm những tài liệu lặp trong cột đầu tiên được hướng đẫn sẵn, và COUNTIF vẫn thực hiện quá trình tương từ với những cột tiếp theo.

Do đó, tất cả các mục lặp trừ lần xuất hiện thứ nhất của chúng được tra cứu thấy cùng được tấn công dấu:

*
Làm trông rất nổi bật các tài liệu lặp trong tất cả các cột cùng với một nguyên tắc Một giải pháp khác có thể sử dụng là thêm cột trống vào bên trái tập dữ liệu của doanh nghiệp và phối hợp các cách làm trên thành một công thức đơn nhất như sau:

=IF(COLUMNS($B2:B2)>1,COUNTIF(A$2:$B$8,B2),0)+COUNTIF(B$2:B2,B2)>1

Trong đó B2 là ô đầu có tài liệu trong cột thứ 2 của dải đích.

*
Để làm rõ hơn về công thức, hãy chia nhỏ ra làm 2 phần chính:

– Đối cùng với cột trước tiên (B), điều kiện IF ko thể thỏa mãn nhu cầu được yêu cầu, vì vậy chỉ sử dụng công dụng COUNTIF sản phẩm công nghệ hai, là công thức thông thường mà họ đã thực hiện để search các bạn dạng sao mà không có lần xuất hiện lần trước tiên trong một cột. – Đối với tất cả các cột tiếp theo sau (C2:D8), điểm chủ đạo là việc sử dụng các tham chiếu tuyệt vời và kha khá một phương pháp thông minh trong nhì hàm COUNTIF. Để khiến cho mọi thứ dễ nắm bắt hơn,các bạn có thể thấy công thức biến hóa như nạm nào lúc áp dụng cho các ô khác:

Bởi vì đk IF luôn luôn TRUE cho tất cả các cột khác chứ không chỉ có cột trước tiên (có nhiều hơn thế nữa 1 cột), phương pháp được thực hiện theo cách này:

– Đếm số lần mở ra của một mục nhất thiết (D5 trong ảnh chụp màn hình hiển thị ở trên) trong toàn bộ các cột ở bên trái cột vẫn cho: COUNTIF(B$2:$C$8,D5) – Đếm số lần lộ diện của một mục nhất mực trong cột cất mục đó, cho đến ô của mục đó: COUNTIF(D$2:D5,D5) – Cuối cùng, phương pháp cộng thêm tác dụng của cả nhị hàm COUNTIF. Nếu tổng số lớn hơn 1, nghĩa là giả dụ mục mở ra nhiều lần, quy tắc đã được áp dụng và mục này được đánh dấu. Đánh dấu toàn cục các hàng dựa vào giá trị lặp trong một cột ví như bảng của bạn có không ít cột, gồm thể các bạn sẽ muốn có tác dụng nổi bật tổng thể các sản phẩm dựa trên phiên bản ghi giống nhau trong một cột thế thể.

Như bạn đã biết, nguyên tắc được kiến tạo trong Excel cho các dữ liệu lặp chỉ vận động ở cấp độ ô. Cơ mà một quy tắc dựa trên công thức thiết lập cấu hình có thể vận động ở lever cao rộng thế. Điểm chủ đạo là chọn cục bộ các hàng, và sau đó tạo một nguyên tắc với một trong những công thức sau:

– Để làm khá nổi bật các hàng đụng hàng trừ những lần xuất hiện đầu tiên: =COUNTIF($A$2:$A2, $A2)>1 – Để làm nổi bật các sản phẩm trùng lặp bao gồm cả lần xuất hiện đầu tiên: =COUNTIF($A$2:$A$15, $A2)>1 trong những số ấy A2 là ô đầu tiên và A15 là ô được sử dụng sau cuối trong cột mà bạn muốn kiểm tra dữ liệu lặp. Như bạn thấy, việc sử dụng thông minh các tham chiếu ô tuyệt vời nhất và kha khá là điều tạo nên sự không giống biệt. Ảnh chụp màn hình sau đây thể hiện nay cách buổi giao lưu của cả nhị quy tắc:

Cách làm trông rất nổi bật các hàng trùng lặp trong Excel

Ví dụ trước đã share cách đánh màu cục bộ hàng dựa trên các giá trị lặp trong một cột chũm thể. Cơ mà nếu bạn muốn xem những hàng có giá trị giống nhau trong một vài cột? Hoặc, làm nỗ lực nào để bạn làm rất nổi bật các hàng đụng hàng tuyệt đối, những chiếc có giá trị hoàn toàn bằng nhau trong toàn bộ các cột?

Đối cùng với điều này, sử dụng tính năng COUNTIFS có thể chấp nhận được so sánh các ô theo nhiều tiêu chí. Ví dụ: để gia công nổi bật những hàng trùng lặp có mức giá trị giống như nhau vào cột A với B, hãy thực hiện một trong các công thức sau:

Để làm rất nổi bật các hàng giống nhau ngoại trừ các lần lộ diện đầu tiên: =COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)>1 Để làm trông rất nổi bật các hàng trùng lặp bao hàm cả lần xuất hiện thêm đầu tiên: =COUNTIFS($A$2:$A$15, $A2, $B$2:$B$15, $B2)>1 tuy nhiên, lấy một ví dụ trên chỉ mang tính trình làng cách làm cho chúng ta vì trong thực tế, bạn sẽ không chỉ triển khai việc làm nổi bật giá trị lặp chỉ vào 2 cột mà sẽ tiến hành ở giới hạn lớn hơn. Cùng với hàm COUNTIFS chúng ta cũng có thể xử lý cho tới 127 phạm vi / cặp tiêu chuẩn.

Làm khá nổi bật các ô coppy liên tiếp vào Excel

Đôi khi, bạn không yêu cầu phải ghi lại tất cả các dữ liệu lặp trong một cột mà chỉ hiển thị những ô xào nấu liên tục, tức là các ô tương ứng với nhau. Để làm cho điều này, nên lựa chọn ô có tài liệu (không bao gồm tiêu đề cột) và chế tác một quy tắc định dạng có đk với một trong số công thức sau:

Để làm rất nổi bật các tài liệu lặp thường xuyên mà không bao hàm lần mở ra đầu tiên: =$A1=$A2 Để làm khá nổi bật các dữ liệu lặp liên tiếp bao gồm cả lần lộ diện đầu tiên: =OR($A1=$A2, $A2=$A3) Ảnh chụp screen sau minh họa bí quyết làm khá nổi bật các văn phiên bản trùng lặp liên tiếp, nhưng những quy tắc này cũng hoàn toàn có thể áp dụng cho số cùng ngày trùng lặp:

Nếu bảng tính Excel của chúng ta có thể có những hàng rỗng và các bạn không muốn các ô trống liên tiếp được thiết kế nổi bật, hãy triển khai những đổi mới sau cho những công thức:

Để làm nổi bật các ô coppy liên tiếp mà không tồn tại lần xuất hiện trước tiên và quăng quật qua những ô trống: =AND($A2””, $A1=$A2) Để làm trông rất nổi bật các ô trùng lặp với lần xuất hiện trước tiên và bỏ qua những ô trống: = and ($ A2 “”, OR ($ A1 = $ A2, $ A2 = $ A3)) Như chúng ta thấy, câu hỏi làm trông rất nổi bật các dữ liệu trùng lặp trong Excel bằng phương pháp sử dụng định hình có đk vô cùng solo giản, nên bạn sẽ không gặp gỡ phải bất kể khó khăn nào. Ngoài ra, vẫn còn đấy một biện pháp khác để triển khai nổi bật những dữ liệu lặp vào Excel, để biết cách làm chính là gì, mời chúng ta theo dõi phần tiếp sau của nội dung bài viết này.

giải pháp làm khá nổi bật các tài liệu lặp trong Excel cùng với Duplicate Remover

Tiện ích Duplicate Remover là giải pháp tối ưu nhằm xử lý tài liệu trùng lặp trong Excel. Nó hoàn toàn có thể tìm, đánh dấu, chọn, xào nấu hoặc dịch chuyển các ô coppy hoặc toàn thể các hàng trùng lặp.

Duplicate Remover tăng thêm 3 tính năng lạ cho Excel:

– Dedupe Table – giúp ngay mau lẹ tìm và khắc ghi các tài liệu lặp trong một bảng. – Duplicate Remover – thủ thuật mỗi bước với các tùy chọn cải thiện để xác định và đánh dấu các dữ liệu lặp hoặc giá trị duy nhất trong một bảng. – Compare 2 Tables – tìm và đánh dấu các tài liệu lặp bằng cách so sánh nhì cột hoặc hai bảng riêng biệt biệt. Sau khi thiết đặt Duplicate Remover, bạn sẽ tìm thấy những phương pháp này trên tab tài liệu Ablebits trong nhóm Dedupe:

Làm khá nổi bật các tài liệu lặp vào Excel bởi một cú đúp chuột

Đối với lấy một ví dụ này, học tập Excel Online đã tạo ra bảng sau với hàng trăm hàng. Và mục đích là làm nổi bật các sản phẩm trùng lặp có mức giá trị đều nhau trong cả cha cột:

Bạn có thể tin hoặc không, tuy nhiên thực sự là bạn cũng có thể nhận được hiệu quả mong mong với chỉ 2 cú click chuột.

– Với bất kỳ ô làm sao trong bảng đã được chọn, nhấp vào nút Dedupe Table, và phầm mềm thông minh sẽ chọn toàn bộ bảng. – cửa sổ hộp thoại Dedupe Table sẽ xuất hiện với toàn bộ các cột được chọn tự động, cùng tùy chọn màu sắc duplicates được lựa chọn theo khoác định. Vị vậy, tất cả các bạn phải làm là dìm OK. Mẹo. Nếu bạn muốn phát hiện các hàng trùng lặp trong một hoặc các cột, hãy vứt chọn tất cả các cột không liên quan và chỉ còn lại (các) cột chính. Tác dụng nhận được tựa như như sau:

Như bạn thấy vào hình trên, khí cụ Dupe Table đã làm rất nổi bật các mặt hàng trùng lặp nhưng không bao hàm trường hợp đầu tiên.

Nếu bạn có nhu cầu làm nổi bật các tài liệu lặp, kể cả lần lộ diện đầu tiên, hoặc nếu bạn muốn tô màu sắc các bạn dạng ghi khác hoàn toàn chứ chưa phải là các bản ghi trùng lặp, hoặc nếu như bạn không thích màu đỏ mặc định, hãy áp dụng trình thủ thuật Duplicate Remover, ở kia có tất cả các tác dụng này với còn nhiều chưa dừng lại ở đó nữa .

Làm rất nổi bật các dữ liệu lặp vào Excel bằng phương pháp sử dụng trình phía dẫn từng bước nâng cao

So với lao lý Dedupe Table, mẹo nhỏ Duplicate Remover đòi hỏi nhiều rộng một vài cú nhấp chuột, nhưng lại sở hữu thêm một trong những tùy chọn vấp ngã sung.

– Chọn ngẫu nhiên ô làm sao trong bảng mà bạn muốn làm rất nổi bật các tài liệu lặp, và nhấp vào nút Duplicate Remover bên trên thanh thực đơn. Trình gợi ý sẽ chạy và tổng thể bảng sẽ tiến hành chọn. Add-in cũng biến thành gợi ý tạo nên một bản sao giữ của bảng của bạn. Nếu như bạn không phải thì hoàn toàn có thể bỏ chọn hộp đó. Xác minh rằng bảng đã làm được chọn chính xác và dìm Next. 2. Lựa chọn kiểu dữ liệu mà bạn muốn tìm trong những những gạn lọc sau: – Duplicates except 1st occurrences – dữ liệu lặp kế bên lần lộ diện lần đầu – Duplicates with 1st occurrences – dữ liệu lặp và cả lần xuất hiện đầu tiên – quality values – quý hiếm duy duy nhất – chất lượng values and 1st duplicate occurrences – quý giá duy nhất cùng lần lộ diện trùng lặp lần trước tiên Đối với lấy ví dụ này, bọn họ hãy tìm dữ liệu lặp và cả lần xuất hiện thêm đầu tiên: 3. Bây giờ, chọn những cột đề xuất kiểm tra những dữ liệu lặp. Ở đây nên làm trông rất nổi bật các toàn bộ các hàng, yêu cầu ta chọn tất cả 3 cột. Ngoại trừ ra, add-in này còn có thêm tùy lựa chọn nếu bảng của doanh nghiệp của tiêu đề hoặc bạn có nhu cầu bỏ qua các ô trống. Cả nhị tùy chọn này đều được đặt theo khoác định.

4. Cuối cùng, chọn thao tác làm việc cần thực hiện trên dữ liệu lặp. Các bạn có một trong những tùy lựa chọn như chọn, xóa, sao chép, dịch chuyển các dữ liệu lặp hoặc thêm cột tâm trạng để khẳng định các dữ liệu lặp đó. Vớ nhiên, việc lựa chọn màu sắc phông chữ cũng dễ dàng hơn hẳn, chúng ta cũng có thể nhấp vào phần More Colors … và chọn bất kể màu nào tất cả sẵn hoặc màu tùy chỉnh. Nhấn Finish rồi xem tác dụng Hy vọng rằng, nội dung bài viết này mang về nhiều thông tin hữu dụng cho các bạn và mong chạm mặt lại chúng ta trong những bài viết sau.

Để có thể ứng dụng tốt Excel vào trong công việc, bọn họ không chỉ nắm rõ được những hàm nhưng mà còn đề nghị sử dụng xuất sắc cả các công vắt của Excel. đều hàm nâng cao giúp áp dụng giỏi vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những cơ chế thường sử dụng là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kỹ năng và kiến thức này các bạn đều hoàn toàn có thể học được trong khóa đào tạo Excel cùng chuyên gia / giảng viên Nguyễn Đức Thanh: