Trong giải đáp này, Học Excel Online đang hướng dẫn chúng ta cách tìm giá trị trùng nhau trong excel. Họ sẽ bao gồm cái nhìn cận cảnh về các phương thức khác nhau để ẩn những ô trùng lặp, toàn cục các hàng hoặc những dữ liệu lặp liên tiếp bằng cách sử dụng định dạng gồm điều kiện.
Bạn đang xem: Tìm trùng lặp trong excel
Cách sớm nhất có thể để kiếm tìm và khắc ghi các tài liệu lặp vào Excel là áp dụng định dạng có đ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 chính mình các dữ liệu lặp hiện tất cả mà còn vạc hiện và tô màu các 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, chúng ta cũng học tập được từ bài viết này một số cách để làm rất nổi bật dữ liệu lặp tùy nằm trong vào nhiệm vụ cụ thể. Những kỹ thuật này chuyển động trong toàn bộ các phiên bạn dạng của Excel 2016, Excel 2013, Excel 2010 cùng 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 phương pháp sử dụng phép tắc built-in (với lần lặp đầu tiên)Làm khá nổi bật các bản sao trong tương đối nhiều cột mà không tồn tại lần lộ diện lần đầuCách làm khá nổi bật các hàng giống nhau trong Excel cách làm trông rất nổi bật các dữ liệu lặp trong Excel cùng với Duplicate Remover
Cách làm rất nổi bật các tài 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)
Đối với người mới bắt đầu, trong toàn bộ các phiên phiên bản Excel, tất cả một quy tắc được khẳng định trước để triển khai nổi bật các ô trùng lặp. Để sử dụng 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 muốn kiểm tra tài liệu lặp. Đó rất 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, chọn Conditional Formatting > Highlight Cells Rules > Duplicate Values…
Ngoài việc điền vào red color và định dạng văn bản, một trong những ít những định dạng được xác minh trước bao gồm sẵn trong danh sách 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ố màu khác, hãy nhấp vào Custom Format… (mục ở đầu cuối trong trình đơn thả xuống) và chọn màu điền với / hoặc màu sắc chữ theo ý thích.
Sử dụng phép tắc inbuilt, chúng ta cũng có thể làm khá nổi bật các bạn dạng sao vào một cột hoặc trong một vài ba cột như miêu tả trong ảnh chụp màn hình sau:
Chú thích. Khi vận dụng quy tắc nhân phiên bản được xây cất trong hai hoặc nhiều cột, Excel không so sánh các giá trị trong số cột đó, nó chỉ đơn giản dễ dàng làm nổi bật tất cả các trường thích hợp trùng lặp.
Khi sử dụng 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ỉ hoạt động cho những ô riêng biệt lẻ. Để đánh dấu 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ị trong một cột rõ ràng hoặc bằng phương pháp so sánh những giá trị trong một vài ba 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 nổi bật tất cả các bạn dạng sao trừ những trường hợp xuất hiện lần đầu tiên, hãy tạo ra một quy tắc định hình có đk dựa trên bí quyết này.
Cách làm khá nổi bật các tài liệu lặp mà không có lần lộ diện đầu tiên
Để làm nổi bật các dữ liệu lặp tính từ lần xuất hiện thứ 2 trở đi, nên lựa chọn các ô mà bạn muốn tô màu và chế tạo ra quy tắc dựa vào công thức theo cách này:
1. 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 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 số ấy A2 là ô trên thuộc của dải ô được chọn. 3. Nhấp vào nút Format… và chọn màu điền quẹt đậm cùng / hoặc color chữ mà chúng ta muốn. 4. Cuối cùng, nhận OK nhằm lưu và áp dụng quy tắc.
Cách hiển thị các dữ liệu lặp lộ diện lần máy 3, sản phẩm công nghệ 4 và toàn bộ các dữ 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 buộc phải 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 lấy một ví dụ trước, cùng với sự biệt lập duy duy nhất là sửa chữa thay thế > 1 sinh sống cuối cách làm bằng một trong những theo yêu cầu. Ví dụ:
Để làm 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, sinh sản 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 khá nổi bật dữ liệu lặp máy 4 và tất cả các tài liệu lặp tiếp theo, sử dụng công thức này:
=COUNTIF($A$2:$A2,$A2)>=4
Để làm trông rất nổi bật một cực hiếm lặp nhất mực bạn thực hiện toán tử bởi (=). Ví dụ: nhằm 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 cách làm trông rất nổi bật các tài liệu lặp vào 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 các cột, không phải bằng cách so sánh các cột với nhau, các bạn hãy sử dụng một trong những các chiến thuật sau.
Làm nổi bật các dữ liệu lặp trong không ít cột bao gồm cả lần xuất hiện đầu tiên
Nếu lần xuất hiện thứ nhất của tài liệu bị lặp các lần cũng được xem như là một bạn dạng sao thì cách dễ nhất là phát hành công thức dữ liệu lặp vào Excel.
Hoặc, tạo 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ụ: để triển khai nổi bật các phiên bản sao trong dải A2:C8, công thức sẽ như sau:
=COUNTIF($A$2:$C$8, A2)>1
Hãy chú ý đến việc sử dụng các tham chiếu ô tuyệt vời 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 vô số cột mà không có lần lộ diện lần đầu
Các phương án cho trường phù hợp này sẽ sở hữu được chút phức hợp hơn vị Excel không sản xuất quy tắc sẵn.
Xem thêm: Mách Bạn Cách Vệ Sinh Đồng Hồ Dây Da Đồng Hồ Đeo Tay, Mách Bạn Cách Vệ Sinh Dây Da Đồng Hồ Cực Chuẩn
Để làm nổi bật các mục trùng lặp trong một trong những cột bỏ lỡ lần mở ra đầu tiên, các bạn sẽ phải tạo ra 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 bạn sử dụng chính xác cùng một phương pháp như chúng ta đã sử dụng để gia công nổi bật các dữ liệu lặp mà không có lần xuất hiện trước tiên trong một cột.
Trong lấy ví dụ này, bọn họ đang tạo ra 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à những mục giống nhau không tất cả lần xuất hiện trước 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 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 rất nổi bật các phiên bản sao trong số cột còn 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 bí quyết trên, chức năng COUNTIF đếm những dữ liệu lặp vào cột thứ nhất được chỉ định sẵn, cùng COUNTIF vẫn thực hiện quá trình tương tự với các cột tiếp theo.
Do đó, toàn bộ các mục lặp trừ lần xuất hiện trước tiên của chúng được tìm kiếm thấy cùng được đánh dấu:
=IF(COLUMNS($B2:B2)>1,COUNTIF(A$2:$B$8,B2),0)+COUNTIF(B$2:B2,B2)>1
Trong kia B2 là ô đầu có dữ liệu trong cột thứ hai của dải đích.
– Đối cùng với cột đầu tiên (B), điều kiện IF ko thể thỏa mãn nhu cầu được yêu cầu, vì chưng vậy chỉ sử dụng tác dụng COUNTIF thứ hai, là công thức thông thường mà bọn họ đã thực hiện để kiếm tìm các phiên bản sao mà không có lần mở ra lần đầu tiên trong một cột. – Đối với toàn bộ các cột tiếp theo (C2:D8), điểm cơ bản là câu hỏi sử dụng những tham chiếu tuyệt đối và kha khá một biện pháp thông minh trong hai hàm COUNTIF. Để tạo nên mọi thứ dễ dàng nắm bắt hơn,các bạn cũng có thể thấy công thức biến đổi như chũm nào khi áp dụng cho các ô khác:
Bởi vì điều kiện IF luôn luôn luôn TRUE cho tất cả các cột khác chứ không chỉ là có cột trước tiên (có nhiều hơn thế 1 cột), bí quyết được thực hiện theo giải pháp này:
– Đếm số lần xuất hiện thêm của một mục một mực (D5 trong ảnh chụp màn hình hiển thị ở trên) trong toàn bộ các cột ở phía bên trái cột đã 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, cách làm cộng thêm hiệu quả của cả nhì hàm COUNTIF. Giả dụ tổng số to hơn 1, nghĩa là giả dụ mục xuất hiện 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 thể 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ó tương đối nhiều cột, có thể bạn sẽ muốn làm cho nổi bật toàn bộ các hàng dựa trên phiên bản ghi giống nhau trong một cột nuốm thể.
Như các bạn đã biết, phép tắc được xây dừng trong Excel cho các dữ liệu lặp chỉ chuyển động ở cấp độ ô. Nhưng mà một quy tắc dựa vào công thức tùy chỉnh có thể hoạt động ở cấp độ cao hơn thế. Điểm chủ yếu là chọn toàn thể các hàng, và sau đó tạo một nguyên tắc với một trong số công thức sau:
– Để làm khá nổi bật các hàng giống nhau trừ những lần mở ra đầu tiên: =COUNTIF($A$2:$A2, $A2)>1 – Để làm nổi bật các mặt hàng trùng lặp bao gồm cả lần lộ diện đầu tiên: =COUNTIF($A$2:$A$15, $A2)>1 trong các số đó A2 là ô thứ nhất và A15 là ô được sử dụng ở đầu 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 thực hiện thông minh những tham chiếu ô hoàn hảo nhất và kha khá là điều khiến cho sự không giống biệt. Ảnh chụp màn hình tiếp sau đây thể hiện nay cách hoạt động của cả nhị quy tắc:
Cách làm trông rất nổi bật các hàng giống nhau trong Excel
Ví dụ trước đã share cách đánh màu toàn bộ hàng dựa trên các giá trị lặp trong một cột ráng thể. Dẫu vậy nếu bạn có nhu cầu xem các hàng có mức giá trị kiểu như nhau trong một vài cột? Hoặc, làm gắng nào để các bạn làm nổi bật các hàng trùng lặp tuyệt đối, các cái có giá bán trị hoàn toàn bằng nhau trong tất cả các cột?
Đối với điều này, sử dụng tính năng COUNTIFS cho phép 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ị như thể nhau trong cột A với B, hãy thực hiện một trong các công thức sau:
Để làm 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 mặt hàng trùng lặp bao gồm cả lần lộ diện đầu tiên: =COUNTIFS($A$2:$A$15, $A2, $B$2:$B$15, $B2)>1 mặc dù nhiên, lấy ví dụ như trên chỉ có tính giới thiệu cách làm cho 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ỉ trong 2 cột mà sẽ thực hiện ở số lượng giới hạn lớn hơn. 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 trong Excel
Đôi khi, các bạn không cần phải khắc ghi tất cả những dữ liệu lặp trong một cột mà chỉ hiển thị những ô sao chép liên tục, tức là các ô tương ứng với nhau. Để làm điều này, hãy chọn ô có dữ liệu (không bao hàm tiêu đề cột) và chế tác một quy tắc format có điều kiện với một trong số công thức sau:
Để làm trông rất nổi bật các dữ liệu lặp tiếp tục mà không bao hàm lần mở ra đầu tiên: =$A1=$A2 Để làm rất nổi bật các tài liệu lặp liên tiếp bao hàm cả lần xuất hiện đầu tiên: =OR($A1=$A2, $A2=$A3) Ảnh chụp màn hình hiển thị sau minh họa biện pháp làm khá nổi bật các văn bạn dạng trùng lặp liên tiếp, nhưng các quy tắc này cũng có thể áp dụng đến số với ngày trùng lặp:
Nếu bảng tính Excel của chúng ta cũng có thể có những hàng trống rỗng và chúng ta không muốn những ô trống liên tiếp được thiết kế nổi bật, hãy thực hiện những đổi mới sau cho những công thức:
Để làm khá nổi bật các ô xào nấu liên tiếp mà không có lần xuất hiện thứ nhất và bỏ qua những ô trống: =AND($A2””, $A1=$A2) Để làm nổi bật các ô trùng lặp với lần xuất hiện đầu tiên và vứt qua các ô trống: = và ($ A2 “”, OR ($ A1 = $ A2, $ A2 = $ A3)) Như các bạn thấy, vấn đề làm rất nổi bật các tài liệu trùng lặp trong Excel bằng cách sử dụng định hình có điều kiện vô cùng đơn giản, nên các bạn sẽ không chạm chán phải bất kể khó khăn nào. Kế bên ra, vẫn tồn tại một cách khác để làm nổi bật các dữ liệu lặp vào Excel, để biết phương pháp làm sẽ 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.
cách làm nổi bật các tài liệu lặp vào Excel cùng với Duplicate Remover
Tiện ích Duplicate Remover là giải pháp tối ưu để xử lý tài liệu trùng lặp trong Excel. Nó hoàn toàn có thể tìm, tiến công dấu, chọn, coppy hoặc dịch rời các ô coppy hoặc tổng thể các mặt hàng trùng lặp.
Duplicate Remover tăng thêm 3 tính năng được cải thiện cho Excel:
– Dedupe Table – góp ngay mau chóng 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 những tùy chọn nâng cấp để xác minh và đánh dấu các dữ liệu lặp hoặc cực hiếm duy nhất trong 1 bảng. – Compare 2 Tables – kiếm tìm và lưu lại các tài liệu lặp bằng phương pháp so sánh hai cột hoặc nhị 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ép tắc này bên trên tab dữ liệu Ablebits trong đội Dedupe:
Làm rất nổi bật các dữ liệu lặp trong Excel bởi một cú lưu ban 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 trăm hàng. Và mục tiêu là làm rất nổi bật các hàng trùng lặp có giá trị đều nhau trong cả ba cột:
Bạn rất có thể tin hoặc không, cơ mà thực sự là bạn có thể nhận được kết quả mong muốn với chỉ 2 cú click chuột.
– Với bất kỳ ô như thế nào trong bảng đã có chọn, nhấp vào nút Dedupe Table, và phầm mềm thông minh đã chọn toàn bộ bảng. – hành lang cửa số hộp thoại Dedupe Table sẽ xuất hiện với toàn bộ các cột được lựa chọn tự động, với tùy chọn color duplicates được chọn theo khoác định. Do vậy, tất cả các bạn phải làm là dấ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 nhiều cột, hãy quăng quật chọn tất cả các cột không liên 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ư bạn thấy trong hình trên, quy định Dupe Table vẫn làm nổi bật các hàng trùng lặp cơ mà không bao hàm trường đúng theo đầu tiên.
Nếu bạn muốn làm nổi bật các tài liệu lặp, bao gồm cả lần lộ diện đầu tiên, hoặc nếu bạn muốn tô color các bản ghi khác biệt chứ không hẳn 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ủ pháp Duplicate Remover, ở kia có toàn bộ các chức năng này với còn nhiều hơn thế nữa .
Làm trông rất nổi bật các tài liệu lặp vào Excel bằng phương pháp sử dụng trình phía dẫn mỗi bước nâng cao
So với vẻ ngoài 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 bổ sung.
– Chọn bất kỳ ô làm sao trong bảng mà bạn có nhu cầu 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 lý giải sẽ chạy và cục bộ bảng sẽ tiến hành chọn. Add-in cũng trở thành gợi ý tạo nên một bạn dạng sao lưu giữ của bảng của bạn. Nếu như bạn không đề nghị thì rất có thể bỏ chọn hộp đó. Xác minh rằng bảng đã được chọn đúng chuẩn và dấn Next. 2. Lựa chọn kiểu dữ liệu mà bạn muốn tìm trong số những lựa chọn sau: – Duplicates except 1st occurrences – tài liệu lặp ngoài lần lộ diện lần đầu – Duplicates with 1st occurrences – tài liệu lặp với cả lần xuất hiện thứ nhất – chất lượng values – cực hiếm duy nhất – unique values và 1st duplicate occurrences – giá trị 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ụ như này, bọn họ hãy tìm dữ liệu lặp cùng cả lần mở ra đầu tiên: 3. Bây giờ, chọn các cột phải kiểm tra các dữ liệu lặp. Ở đây yêu cầu làm nổi bật các tất cả các hàng, đề nghị ta chọn toàn bộ 3 cột. Ngoại trừ ra, add-in này 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 chọn này mọi được đặt theo mặc định.
4. Cuối cùng, chọn làm việc cần triển khai trên tài liệu lặp. Các bạn có một số trong những tùy 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 tinh thần để khẳng định các tài liệu lặp đó. Tất nhiên, vấn đề lựa chọn color phông chữ cũng tiện lợi hơn hẳn, bạn cũng có thể nhấp vào phần More Colors … và chọn bất kể màu nào bao gồm sẵn hoặc color tùy chỉnh. Dìm 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 có ích cho các bạn và mong gặp lại chúng ta trong những nội dung bài viết sau.
Để rất có thể ứng dụng giỏi Excel vào trong công việc, họ không chỉ nắm rõ được những hàm mà còn cần sử dụng giỏi cả những công thế của Excel. Mọi hàm cải thiện giúp áp dụng xuất sắc vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những hình thức thường sử dụng là Data validation, Conditional formatting, Pivot table…
Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa huấn luyện Excel cùng chuyên gia / giảng viên Nguyễn Đức Thanh:
