Kiểm Tra Dữ Liệu Trùng Nhau Trong Excel

Trong lý giải này, Học Excel Online đã hướng dẫn các bạn cách tìm giá trị trùng nhau vào excel. Chúng ta sẽ tất cả cái quan sát cận cảnh về các phương pháp khác nhau để ẩn các ô trùng lặp, cục bộ các 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 bao gồm điều kiện.

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

Cách sớm nhất để tra cứu và khắc ghi các tài liệu lặp trong Excel là sử dụng định dạng gồm điều kiện. Ưu điểm lớn số 1 của phương thức này là nó không chỉ hiển thị cho bạn các tài liệu lặp hiện bao gồm mà còn vạc hiện cùng tô màu những dữ liệu lặp mới khi bạn nhập, chỉnh sửa 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 thuộc vào trách nhiệm cụ thể. Những kỹ thuật này vận động trong toàn bộ các phiên bản của Excel 2016, Excel 2013, Excel 2010 với thấp hơn.

Xem nhanh

Cách làm nổi bật các dữ liệu lặp vào Excel bằng cách sử dụng quy tắc built-in (với lần lặp đầu tiên)Làm nổi bật các bản sao trong nhiều cột mà không có lần mở ra lần đầuCách làm nổi bật các hàng đụng hàng trong Excel bí quyết làm trông rất nổi bật các dữ liệu lặp vào Excel với Duplicate Remover

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

Đối với người mới bắt đầu, trong toàn bộ các phiên bản Excel, tất cả một phép tắc được khẳng định trước để triển khai nổi bật các ô trùng lặp. Để thực hiện quy tắc này trong các bảng tính, hãy triển khai theo các bước sau:

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

3. Hành lang cửa số hộp thoại Duplicate Values sẽ xuất hiện với định dạng Light Red Fill & Dark Red Text được lựa chọn theo mặc định. Để áp dụng định dạng mặc định, chỉ việc nhấp vào OK.

Ngoài câu hỏi điền vào red color và format văn bản, một trong những ít những định dạng được khẳng định trước bao gồm sẵn trong danh sách thả xuống. Để làm khá nổi bật các dữ liệu lặp bằng cách sử dụng một số trong những màu khác, hãy nhấp vào Custom Format… (mục cuối cùng trong trình đơn thả xuống) và lựa chọn màu điền và / hoặc color chữ theo ý thích.

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

Sử dụng luật lệ inbuilt, chúng ta có thể làm khá nổi bật các bạn dạng sao trong một cột hoặc vào một vài ba cột như diễn tả trong hình ảnh chụp màn hình hiển thị sau:

Chú thích. Khi vận dụng quy tắc nhân bạn dạng được thiết kế trong nhì hoặc những cột, Excel không so sánh các giá trị trong những cột đó, nó chỉ dễ dàng làm rất nổi bật tất cả các trường phù 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 chú ý hai điều sau:

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

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

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

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

kết quả là, các ô sao chép trừ những trường hợp đầu tiên sẽ được thiết kế nổi nhảy với màu sắc bạn chọn:

Cách hiển thị những dữ liệu lặp xuất hiện thêm lần thứ 3, trang bị 4 và toàn bộ các tài liệu lặp tiếp theo

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

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

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

Để làm trông rất nổi bật dữ liệu lặp máy 4 và toàn bộ các tài liệu lặp tiếp theo, thực hiện công thức này:

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

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

=COUNTIF($A$2:$A2,$A2)=2 bí quyết làm khá nổi bật các dữ 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 nhiều cột, không phải bằng phương pháp so sánh những cột cùng với nhau, các bạn hãy sử dụng một trong các các chiến thuật sau.

Làm khá nổi bật các dữ liệu lặp trong nhiều cột bao hàm cả lần xuất hiện thêm đầu tiên

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

Hoặc, sản xuất một quy tắc định dạng có đk với công thức này:

COUNTIF(range, top_cell)>1 Ví dụ: để gia công nổi nhảy các phiên bản sao trong dải A2:C8, phương pháp sẽ như sau:

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

Hãy chú ý đến bài toán sử dụng các tham chiếu ô hoàn hảo và tuyệt vời nhất cho dải ($A$2:$C$8), và những 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 phiên bản sao trong không ít cột mà không tồn tại lần lộ diện lần đầu

Các chiến thuật cho trường đúng theo này sẽ có được chút tinh vi hơn vì Excel không xuất bản quy tắc sẵn.

Xem thêm: Lời Bài Hát Em Chưa 18 Lou Hoàng, Kaity Nguyễn

Để làm khá nổi bật các mục đụng hàng trong một trong những cột làm 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 đến cột đầu tiên

Ở đây các bạn sử dụng đúng chuẩn cùng một công thức như chúng ta đã sử dụng để làm nổi bật những dữ liệu lặp mà không tồn tại lần xuất hiện thứ nhất trong một cột.

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

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

Kết quả là các mục giống nhau không có lần xuất hiện đầu tiên được đánh dấu sát cột phía trái của dải ô (chỉ gồm một mục như vậy trong trường vừa lòng của lấy ví dụ này):

Quy tắc 2. Áp dụng cho toàn bộ các cột tiếp theo

Để làm trông rất nổi bật các phiên bản sao trong các cột còn sót lại (B2: C8), thực hiện công thức sau:

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

Trong công thức trên, chức năng COUNTIF đếm những dữ liệu lặp vào cột trước tiên được hướng dẫn và chỉ định sẵn, với COUNTIF sẽ thực hiện công việc 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 trước tiên của chúng được search thấy với được tiến công dấu:

Làm khá 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 quy tắc Một chiến thuật khác hoàn toàn có thể sử dụng là thêm cột trống vào phía bên trái tập dữ liệu của doanh nghiệp và kết hợp các cách làm trên thành một công thức đơn lẻ 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ó dữ 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 ra làm 2 phần chính:

– Đối cùng với cột đầu tiên (B), đk IF không thể thỏa mãn nhu cầu được yêu thương cầu, bởi vì vậy chỉ sử dụng tác dụng COUNTIF sản phẩm công nghệ hai, là công thức thông thường mà chúng ta đã thực hiện để tìm các phiên bản sao mà không tồn tại lần lộ diện lần trước tiên trong một cột. – Đối với toàn bộ các cột tiếp sau (C2:D8), điểm căn bản là vấn đề sử dụng những tham chiếu hoàn hảo và tương đối một cách thông minh trong nhị hàm COUNTIF. Để làm cho mọi thứ dễ hiểu hơn,các chúng ta có thể thấy công thức biến hóa như nỗ lực nào lúc áp dụng cho các ô khác:

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

– Đếm số lần xuất hiện thêm của một mục nhất quyế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 sẽ cho: COUNTIF(B$2:$C$8,D5) – Đếm số lần xuất hiện thêm của một mục nhất mực trong cột chứa mục đó, cho đến ô của mục đó: COUNTIF(D$2:D5,D5) – Cuối cùng, công thức cộng thêm kết quả của cả nhị hàm COUNTIF. Trường hợp tổng số to hơn 1, nghĩa là trường hợp mục xuất hiện nhiều lần, quy tắc đã được vận dụng và mục đó được đánh dấu. Đánh dấu toàn cục các hàng dựa trên giá trị lặp trong một cột nếu như bảng của bạn có nhiều cột, có thể các bạn sẽ muốn làm cho nổi bật cục bộ các mặt hàng dựa trên bạn dạng ghi trùng lặp trong một cột cầm thể.

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

– Để làm nổi bật các hàng giống nhau trừ các lần mở ra đầu tiên: =COUNTIF($A$2:$A2, $A2)>1 – Để làm rất nổi bật các mặt hàng trùng lặp bao hà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 có nhu cầu kiểm tra tài liệu lặp. Như chúng ta thấy, việc thực hiện thông minh các tham chiếu ô hoàn hảo nhất và tương đối là điều tạo nên sự không giống biệt. Ảnh chụp màn hình dưới đây thể hiện nay cách buổi giao lưu của cả nhị quy tắc:

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

Ví dụ trước đã chia sẻ cách đánh màu cục bộ hàng dựa trên các giá trị lặp vào một cột rứa thể. Tuy thế nếu bạn có nhu cầu xem những hàng có mức giá trị như là nhau trong một vài ba cột? Hoặc, làm cầm nào để các bạn làm nổi bật các hàng đụng hàng tuyệt đối, các chiếc có giá chỉ trị trọn vẹ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ác dụng COUNTIFS chất nhận được so sánh các ô theo khá nhiều tiêu chí. Ví dụ: để làm nổi bật các hàng trùng lặp có giá trị tương đương nhau trong cột A cùng B, hãy áp dụng một trong số công thức sau:

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

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

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

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

Nếu bảng tính Excel của chúng ta có thể có các hàng rỗng và 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 cải tiến sau cho các công thức:

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

cách 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à phương án tối ưu nhằm xử lý tài liệu trùng lặp trong Excel. Nó rất có thể tìm, tiến công dấu, chọn, sao chép hoặc dịch chuyển các ô xào luộc hoặc tổng thể các sản phẩm trùng lặp.

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

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

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

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

Bạn hoàn toàn có thể tin hoặc không, cơ mà thực sự là chúng ta có thể nhận được hiệu quả mong muốn với chỉ 2 cú click chuột.

– Với bất kỳ ô như thế nào trong bảng đã làm được chọn, nhấp vào nút Dedupe Table, và ứng dụng thông minh đã chọn toàn cục bảng. – hành lang cửa số hộp thoại Dedupe Table sẽ lộ diện với toàn bộ các cột được lựa chọn tự động, cùng tùy chọn màu sắc duplicates được lựa chọn theo mặc định. Do vậy, vớ cả chúng ta phải có tác dụng là thừa nhận OK. Mẹo. Nếu bạn muốn phát hiện những hàng đụng hàng trong một hoặc những cột, hãy vứt chọn tất cả các cột không liên quan và chỉ giữ lại (các) cột chính. Hiệu quả nhận được tựa như như sau:

Như chúng ta thấy vào hình trên, giải pháp Dupe Table sẽ làm nổi bật các hàng trùng lặp mà không bao gồm trường đúng theo đầu tiên.

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

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

So với cơ chế Dedupe Table, thủ thuật Duplicate Remover đòi hỏi nhiều hơn một vài ba cú nhấp chuột, nhưng lại sở hữu thêm một số tùy chọn ngã sung.

– Chọn bất kỳ ô làm sao trong bảng mà bạn có nhu cầu làm trông rất nổi bật các dữ liệu lặp, cùng nhấp vào nút Duplicate Remover bên trên thanh thực đơn. Trình trả lời sẽ chạy và toàn thể bảng sẽ được chọn. Add-in cũng sẽ gợi ý tạo một bạn dạng sao lưu lại của bảng của bạn. Nếu như khách hàng không cần thì có thể bỏ lựa chọn hộp đó. Xác minh rằng bảng đã làm được chọn đúng đắn và dấn Next. 2. Chọn kiểu dữ liệu mà bạn muốn tìm trong số những chọn lựa sau: – Duplicates except 1st occurrences – dữ liệu lặp ngoài lần mở ra lần đầu – Duplicates with 1st occurrences – tài liệu lặp với cả lần xuất hiện trước tiên – quality values – quý hiếm duy tốt nhất – quality values & 1st duplicate occurrences – cực hiếm duy nhất cùng lần mở ra trùng lặp lần đầu 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 mở ra đầu tiên: 3. Bây giờ, chọn các cột phải kiểm tra những dữ liệu lặp. Ở đây phải làm nổi bật các toàn bộ các hàng, đề nghị ta chọn tất cả 3 cột. Ngoài ra, add-in này còn có thêm tùy chọn nếu bảng của người tiêu dùng của tiêu đề hoặc bạn có nhu cầu bỏ qua các ô trống. Cả hai tùy lựa chọn này hầu hết được để 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. Chúng ta có một vài tùy chọn như chọn, xóa, sao chép, di chuyển các dữ liệu lặp hoặc thêm cột trạng thái để khẳng định các tài liệu lặp đó. Vớ nhiên, việc lựa chọn color phông chữ cũng dễ dãi hơn hẳn, bạn cũng có thể nhấp vào phần More Colors … cùng chọn bất kể màu nào bao gồm sẵn hoặc color tùy chỉnh. Nhấn Finish rồi xem công dụng Hy vọng rằng, bài viết này mang lại nhiều thông tin hữu dụng cho chúng ta và mong gặp gỡ lại chúng ta trong những nội dung bài viết sau.

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

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