Cách Tìm Giá Trị Trùng Lặp Trong Excel

Trong trả lời này, Học Excel Online đang hướng dẫn các bạn cách tìm cực hiếm trùng nhau trong excel. Chúng ta sẽ gồm cái chú ý cận cảnh về các phương thức 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: Cách tìm giá trị trùng lặp trong excel

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

Ngoài ra, bạn 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ọng trách cụ thể. Những kỹ thuật này hoạt động trong toàn bộ các phiên phiên bản của Excel 2016, Excel 2013, Excel 2010 và thấp hơn.


Xem nhanh


Cách làm trông rất nổi bật các dữ liệu lặp vào Excel bằng cách sử dụng phép tắc built-in (với lần lặp đầu tiên)Làm 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 đầuCách làm trông rất nổi bật các hàng trùng lặp trong Excel bí quyết làm rất nổi bật các dữ liệu lặp vào Excel cùng với Duplicate Remover

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

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

1. Lựa chọn dữ liệu bạn có nhu cầu kiểm tra tài liệu lặp. Đó có thể là một cột, một mặt hàng hoặc một dải ô. 2. Bên trên tab Trang chủ, trong nhóm 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 thêm với định dạng Light Red Fill và Dark Red Text được lựa chọn theo mặc định. Để vận dụng định dạng khoác định, chỉ cần nhấp vào OK.

Ngoài vấn đề điền vào red color và format văn bản, một vài ít các định dạng được khẳng định trước gồm sẵn trong list thả xuống. Để làm khá nổi bật các tài 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 ở đầu cuối trong trình solo thả xuống) và lựa chọn màu điền với / hoặc color chữ theo ý thích.

*
Mẹo. Để đánh dấu các quý hiếm duy nhất, hãy lựa chọn 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 sao trong một cột hoặc vào một vài cột như bộc lộ trong hình ảnh chụp màn hình hiển thị sau:

*



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

Khi thực hiện quy tắc inbuilt của Excel để làm nổi bật giá trị lặp, xin lưu ý hai điều sau:

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

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

Để làm nổi bật các tài liệu lặp tính trường đoản cú lần xuất hiện thêm thứ 2 trở đi, hãy chọn các ô mà bạn muốn tô màu và tạo nên quy tắc dựa trên công thức theo cách này:

1. Bên trên tab Trang chủ, trong đội Styles, chọn Conditional Formatting > New rule > Use a formula to determine which cells lớn format. 2. Trong vỏ 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 những số ấy 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 quẹt đậm cùng / hoặc color chữ mà các bạn muốn. 4. Cuối cùng, nhận OK để lưu và vận dụng quy tắc.

*
tác dụng là, những ô coppy trừ những trường hợp đầu tiên sẽ được làm nổi bật 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 3, thứ 4 và tất cả các tài liệu lặp tiếp theo

Để xem các dữ liệu lặp bắt đầu với lần xuất hiệ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 cách làm như trong ví dụ trước, cùng với sự biệt lập duy độc nhất là sửa chữa thay thế > 1 nghỉ ngơi cuối bí quyết 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ứ ba và toàn bộ các ngôi trường hợp trùng lặp tiếp theo, tạo thành một quy tắc định dạng có điều kiện dựa trên cách làm này:

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

Để làm rất nổi bật dữ liệu lặp thiết bị 4 và tất cả các dữ 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 trông rất nổi bật một quý giá lặp nhất định bạn sử dụng toán tử bởi (=). Ví dụ: để chỉ những dữ liệu lặp lần thiết bị 2, bạn sẽ làm theo cách làm sau:

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

Làm nổi bật các tài liệu lặp trong nhiều cột bao gồm cả lần lộ diện đầu tiên

Nếu lần xuất hiện đầu tiên của dữ liệu bị lặp những lần cũng được coi là một bản sao thì cách dễ dàng nhất là xây dừng công thức tài liệu lặp vào Excel.

Hoặc, tạo thành 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ụ: để làm nổi bật các bản sao vào dải A2:C8, cách làm sẽ như sau:

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

Hãy để ý đến việc sử dụng những tham chiếu ô 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 trông rất nổi bật các bản sao trong tương đối nhiều cột mà không có lần mở ra lần đầu

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

Xem thêm: " Subpoena Là Gì - Subpoena In Vietnamese

Để 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 mở ra đầu tiên, các bạn sẽ phải tạo thành 2 phép tắc với các công thức sau:

Nguyên tắc 1. Áp dụng đến cột đầu tiên

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

Trong lấy ví dụ như này, bọn 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 quả là các mục đụng hàng không gồm lần xuất hiện thứ nhất được lưu lại sát cột phía trái của dải ô (chỉ có một mục bởi thế trong trường hợp của ví dụ này):

*

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

Để làm khá nổi bật các phiên bản sao trong số cột 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 chỉ định sẵn, cùng COUNTIF đang thực hiện các bước tương từ bỏ 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 và được đánh dấu:

*
Làm rất nổi bật các tài liệu lặp trong toàn bộ các cột với một luật lệ Một phương án khác hoàn toàn 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 khách hàng và kết 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 kia B2 là ô đầu có tài liệu trong cột thứ hai của dải đích.

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

– Đối với cột trước tiên (B), đk IF ko thể thỏa mãn nhu cầu được yêu thương cầu, bởi vậy chỉ sử dụng công dụng COUNTIF vật dụng hai, là công thức thông thường mà bọn họ đã thực hiện để tìm các bản sao mà không có 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 theo sau (C2:D8), điểm then chốt là vấn đề sử dụng những tham chiếu hoàn hảo và tuyệt vời nhất và tương đối một phương pháp thông minh trong nhì hàm COUNTIF. Để tạo nên mọi thứ dễ dàng nắm bắt hơn,các bạn có thể thấy công thức chuyển đổi như gắng nào lúc áp dụng cho những ô khác:

Bởi vì điều kiện IF luôn luôn luôn TRUE cho toàn bộ các cột không giống chứ không chỉ có có cột trước tiên (có nhiều hơn thế 1 cột), cách làm được triển khai theo biện pháp này:

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

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

– Để làm khá nổi bật các hàng trùng lặp trừ những lần xuất hiện đầu tiên: =COUNTIF($A$2:$A2, $A2)>1 – Để làm rất nổi bật các sản phẩm trùng lặp bao hàm cả lần lộ diện đầu tiên: =COUNTIF($A$2:$A$15, $A2)>1 trong những số đó 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ư các bạn thấy, việc áp dụng thông minh các tham chiếu ô tuyệt vời nhất và kha khá là điều tạo cho sự không giống biệt. Ảnh chụp màn hình dưới đây thể hiện tại cách hoạt động vui chơi 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 đã chia sẻ cách sơn màu toàn thể hàng dựa trên những giá trị lặp vào một cột thay thể. Nhưng lại nếu bạn muốn xem những hàng có mức giá trị giống nhau vào một vài cột? Hoặc, làm vậy nào để các bạn làm rất nổi bật các hàng đụng hàng tuyệt đối, các cái 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 chức năng COUNTIFS chất nhận được so sánh các ô theo rất 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ị tương tự nhau trong cột A với B, hãy sử dụng một trong những công thức sau:

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

Làm nổi bật các ô sao chép liên tiếp trong Excel

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

Để làm 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 tài liệu lặp liên tiếp bao gồm cả lần mở ra đầu tiên: =OR($A1=$A2, $A2=$A3) Ảnh chụp màn hình hiển thị sau minh họa giải pháp làm rất 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 mang đế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 trống rỗng và bạn không muốn những ô trống liên tiếp được làm nổi bật, hãy tiến hành những cải tiến sau cho những công thức:

Để làm khá nổi bật các ô xào luộc liên tiếp mà không tồn tại lần xuất hiện đầu tiên và vứt qua những ô trống: =AND($A2””, $A1=$A2) Để làm nổi bật các ô giống nhau với lần xuất hiện thứ nhất và bỏ qua các ô trống: = and ($ A2 “”, OR ($ A1 = $ A2, $ A2 = $ A3)) Như bạn thấy, bài toán làm trông rất 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ó đk vô cùng đối kháng giản, nên các bạn sẽ không gặp mặt phải bất cứ khó khăn nào. Ngoài ra, vẫn còn một biện pháp khác để gia công nổi bật những dữ liệu lặp trong Excel, để biết cách làm chính là gì, mời các bạn theo dõi phần tiếp theo của nội dung bài viết này.

biện pháp làm nổi bật các dữ liệu lặp trong Excel với Duplicate Remover

Tiện ích Duplicate Remover là chiến thuật tối ưu để xử lý tài liệu trùng lặp vào Excel. Nó có thể tìm, đánh dấu, chọn, sao chép hoặc dịch chuyển các ô xào nấu hoặc toàn thể các sản phẩm 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à lưu lại các dữ liệu lặp trong một bảng. – Duplicate Remover – thủ thuật từng bước một với những tùy chọn nâng cao để xác minh và đánh dấu các dữ liệu lặp hoặc quý hiếm duy nhất trong 1 bảng. – Compare 2 Tables – search và ghi lại các tài liệu lặp bằng cách so sánh hai cột hoặc nhị bảng riêng rẽ biệt. Sau khi thiết lập Duplicate Remover, các bạn sẽ tìm thấy những hình thức này trên tab tài liệu Ablebits trong đội Dedupe:

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

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

Bạn rất có thể tin hoặc không, dẫu vậy thực sự là chúng ta có thể nhận được công dụng mong mong muốn 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à tiện ích thông minh vẫn chọn cục bộ bảng. – cửa sổ hộp thoại Dedupe Table sẽ xuất hiện với tất cả các cột được chọn tự động, cùng tùy chọn màu sắc duplicates được chọn theo mang định. Vì chưng vậy, tất cả chúng ta phải làm cho là dìm OK. Mẹo. Nếu bạn muốn phát hiện các hàng giống nhau trong một hoặc nhiều cột, hãy vứt chọn toàn bộ các cột không tương quan và chỉ vướng lại (các) cột chính. Công dụng nhận được tựa như như sau:

Như các bạn thấy trong hình trên, mức sử dụng Dupe Table vẫn làm khá nổi bật các hàng trùng lặp nhưng không bao hàm trường phù hợp đầu tiên.

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

Làm trông rất nổi bật các tài 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 nâng cao

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

– Chọn bất kỳ ô nào trong bảng mà bạn muốn làm khá 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 trở thành gợi ý tạo ra một bản sao giữ của bảng của bạn. Nếu như bạn không đề nghị thì có thể bỏ lựa chọn hộp đó. Xác minh rằng bảng đã làm được chọn chính xác và nhận Next. 2. Lựa chọn kiểu dữ liệu mà bạn có nhu cầu tìm trong các những chọn lựa sau: – Duplicates except 1st occurrences – tài liệu lặp xung quanh lần lộ diện lần đầu – Duplicates with 1st occurrences – dữ liệu lặp cùng cả lần xuất hiện trước tiên – unique values – quý giá duy tốt nhất – chất lượng values and 1st duplicate occurrences – cực hiếm duy nhất và lần mở ra trùng lặp lần đầu tiên Đối với ví dụ này, bọn họ hãy tìm tài liệu lặp và cả lần xuất hiện đầu tiên: 3. Bây giờ, chọn những cột đề nghị kiểm tra những dữ liệu lặp. Ở đây nên làm khá nổi bật các toàn bộ các hàng, phải ta chọn tất cả 3 cột. Bên cạnh ra, add-in này có thêm tùy lựa chọn nếu bảng của chúng ta của title hoặc bạn muốn bỏ qua những ô trống. Cả nhì tùy lựa chọn này đông đảo được đặt theo mang định.

4. Cuối cùng, chọn thao tác làm việc cần triển khai trên tài liệu lặp. Chúng ta có một số tùy chọn như chọn, xóa, sao chép, di chuyển các tài liệu lặp hoặc thêm cột tinh thần để xác minh các tài liệu lặp đó. Vớ nhiên, việc lựa chọn color phông chữ cũng thuận lợi hơn hẳn, bạn có thể nhấp vào phần More Colors … cùng chọn bất cứ màu nào tất cả sẵn hoặc màu tùy chỉnh. Nhấn Finish rồi xem kết quả Hy vọng rằng, bài viết này đem lại nhiều thông tin có ích cho chúng ta và mong gặp mặt lại chúng ta trong những bài viết sau.

Để hoàn toàn có thể ứng dụng giỏi Excel vào vào công việc, họ không chỉ nắm rõ được các hàm cơ mà còn đề nghị sử dụng tốt cả các công ráng của Excel. Mọi 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 phương tiện thường thực hiện là Data validation, Conditional formatting, Pivot table…

Toàn cỗ những kỹ năng này các bạn đều 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: