Trong chỉ dẫn này, Học Excel Online đang hướng dẫn chúng ta cách tìm cực hiếm trùng nhau trong excel. Chúng ta sẽ bao gồm cái quan sát cận cảnh về các phương thức khác nhau để ẩn những ô trùng lặp, toàn bộ 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 bao gồm điều kiện.
Bạn đang xem: Hàm tìm số trùng nhau trong excel
Cách sớm nhất để tìm và khắc ghi các dữ liệu lặp vào 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 những hiển thị cho mình các tài liệu lặp hiện gồm mà còn phân phát hiện cùng tô màu các dữ liệu lặp mới khi chúng ta nhập, sửa đổi hoặc chèn dữ liệu.
Ngoài ra, các bạn cũng học được từ nội dung bài viết này một số phương pháp để làm khá nổi bật dữ liệu lặp tùy ở trong vào nhiệm vụ cụ thể. Các kỹ thuật này vận động trong toàn bộ các phiên bạn dạng 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 trong 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 sao trong vô số nhiều cột mà không có lần lộ diện lần đầuCách làm nổi bật các hàng trùng lặp trong Excel biện pháp làm rất nổi bật các dữ liệu lặp vào Excel với Duplicate Remover
Cách làm nổi bật các tài 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)
Đố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 nguyên tắc được xác định trước để gia công nổi bật các ô trùng lặp. Để sử 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 tài liệu lặp. Đó rất có thể là một cộ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. 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 chọn theo mặc định. Để áp dụng định dạng khoác định, chỉ cần nhấp vào OK.Ngoài câu hỏi điền vào màu đỏ và định dạng văn bản, một trong những ít các định dạng được khẳng định trước bao 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 đối chọi thả xuống) và chọn màu điền với / hoặc màu sắc chữ theo ý thích.
Mẹo. Để đánh dấu các quý hiếm duy nhất, hãy lựa chọn Unique ở vỏ hộp thoại mặt tay trái.Sử dụng phép tắc inbuilt, chúng ta có thể làm trông rất nổi bật các bản sao vào một cột hoặc trong một vài cột như biểu hiện trong ả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 được thành lập trong nhị hoặc nhiều cột, Excel không so sánh những giá trị trong số cột đó, nó chỉ dễ dàng và đơn giản làm khá nổi bật tất cả những trường thích hợp trùng lặp.
Khi áp dụng quy tắc inbuilt của Excel để gia công nổi nhảy giá trị lặp, xin lưu ý hai điều sau:
– Nó chỉ chuyển động cho những ô riêng lẻ. Để lưu 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 các giá trị vào một cột cụ thể 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ờ các ô trùng lặp tất cả lần xuất hiện thứ nhất 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ừ những trường hợp xuất hiện lần đầu tiên, hãy chế tạo một quy tắc định dạng có đk dựa trên cách làm này.
Cách làm nổi bật các dữ liệu lặp mà không có lần mở ra đầu tiên
Để làm rất nổi bật các dữ liệu lặp tính từ bỏ lần lộ diện thứ 2 trở đi, nên chọn lựa các ô mà bạn có nhu cầu tô color và tạo ra 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 lớn 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 đó 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 thoa đậm và / hoặc color chữ mà các bạn muốn. 4. Cuối cùng, dấn OK nhằm lưu và áp dụng quy tắc.
công dụng là, các ô sao chép trừ các trường hợp đầu tiên sẽ được thiết kế nổi bật với color bạn chọn:Cách hiển thị những dữ liệu lặp lộ diện lần thứ 3, thiết bị 4 và tất cả các tài liệu lặp tiếp theo
Để xem những dữ liệu lặp bắt đầu với lần xuất hiện thứ N, ta đề nghị tạo một quy tắc format có đk dựa trên công thức như trong lấy ví dụ như trước, với sự biệt lập duy duy nhất là thay thế > 1 nghỉ ngơi cuối phương pháp bằng một số trong những theo yêu thương cầu. Ví dụ:
Để làm nổi bật dữ liệu lặp thứ tía và tất cả các ngôi trường hợp trùng lặp tiếp theo, chế tạo một quy tắc định hình có đk dựa trên công thức này:
=COUNTIF($A$2:$A2,$A2)>=3
Để làm trông rất nổi bật dữ liệu lặp lắp thêm 4 và tất cả 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 trông rất nổi bật một cực hiếm lặp nhất đị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 thứ 2, bạn sẽ làm theo phương pháp sau:
=COUNTIF($A$2:$A2,$A2)=2 phương pháp làm trông rất nổi bật các dữ 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 những cột, không phải bằng cách so sánh các cột cùng với nhau, bạn hãy sử dụng một trong các các phương án sau.
Làm khá nổi bật các dữ liệu lặp trong vô số cột bao gồm cả lần lộ diện đầu tiên
Nếu lần xuất hiện thứ nhất của tài liệu bị lặp những lần cũng được xem là một phiên bản sao thì cách dễ nhất là xây dừng công thức dữ liệu lặp vào Excel.
Hoặc, sinh sản một quy tắc format có đk với công thức này:
COUNTIF(range, top_cell)>1 Ví dụ: để làm nổi nhảy 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ú ý đến việc sử dụng các 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 tồn tại lần xuất hiện thêm lần đầu
Các phương án cho trường thích hợp này sẽ có được chút phức hợp hơn do Excel không thiết kế quy tắc sẵn.
Xem thêm: Báo Giá Máy Lọc Nước Karofi 8 Lõi Thông Minh Iro 2, Máy Lọc Nước Ro Karofi Ksi80
Để làm nổi bật các mục trùng lặp trong một vài cột làm lơ lần mở ra đầu tiên, các bạn sẽ phải tạo thành 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 chúng ta sử dụng đúng chuẩn cùng một bí quyết như chúng ta đã sử dụng để triển khai nổi bật các dữ liệu lặp mà không có lần xuất hiện thứ nhất trong một cột.
Trong lấy một ví dụ này, chúng ta đang tạo ra một quy tắc cho A2:A8 với công thức:
=COUNTIF($A$2:$A2,$A2)>1
Kết quả là những mục đụng hàng không có lần xuất hiện thứ nhất được đánh dấu sát cột bên trái của dải ô (chỉ gồm một mục do đó trong trường phù 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 bạn dạng sao trong những cột còn lại (B2: C8), sử dụng công thức sau:
=COUNTIF(A$2:$A$8,B2)+COUNTIF(B$2:B2,B2)>1
Trong cách làm trên, tác dụng COUNTIF đếm những dữ liệu lặp vào cột thứ nhất được chỉ định sẵn, với COUNTIF vẫn thực hiện quá trình tương trường đoản cú 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 tìm kiếm thấy và được tiến công 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 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 trái tập dữ liệu của người sử dụng và phối hợp các cách làm trên thành một công thức riêng lẻ 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.
Để làm rõ hơn về công thức, hãy chia ra làm 2 phần chính:– Đối với cột thứ nhất (B), điều kiện IF không thể đáp ứng nhu cầu được yêu cầu, vì chưng 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à họ đã sử dụng để kiếm tìm các phiên bản sao mà không tồn tại lần xuất hiện thêm 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 chủ đạo là câu hỏi sử dụng các tham chiếu hoàn hảo nhất và tương đối một biện pháp thông minh trong nhị hàm COUNTIF. Để tạo cho mọi thứ dễ dàng nắm bắt hơn,các bạn có thể thấy công thức thay đổi như nắm nào khi á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ông giống chứ không những có cột thứ nhất (có nhiều hơn nữa 1 cột), bí quyết được thực hiện theo bí quyết này:
– Đếm số lần mở ra của một mục khăng khăng (D5 trong ảnh chụp screen ở trên) trong toàn bộ các cột ở phía trái cột sẽ cho: COUNTIF(B$2:$C$8,D5) – Đếm số lần lộ diện của một mục nhất định 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 công dụng của cả nhị hàm COUNTIF. Ví như tổng số to hơn 1, nghĩa là ví như mục xuất hiện thêm nhiều lần, quy tắc đã được vận dụng và mục đó được đánh dấu. Đánh dấu cục bộ các hàng dựa trên giá trị lặp trong một cột trường hợp bảng của bạn có không ít cột, bao gồm thể các bạn sẽ muốn làm cho nổi bật toàn cục các mặt hàng dựa trên bạn dạng ghi trùng lặp trong một cột nuốm thể.
Như các bạn đã biết, phép tắc được sản xuất trong Excel cho những dữ liệu lặp chỉ vận động ở cấp độ ô. Dẫu vậy một quy tắc dựa vào công thức cấu hình thiết lập có thể vận động ở lever cao hơn thế. Điểm chủ chốt là chọn toàn cục các hàng, và kế tiếp 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 trùng lặp trừ các lần xuất hiện thêm đầ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 xuất hiện thêm đầu tiên: =COUNTIF($A$2:$A$15, $A2)>1 trong số ấy A2 là ô thứ nhất và A15 là ô được sử dụng sau cuối trong cột mà bạn có nhu cầu kiểm tra dữ liệu lặp. Như bạn thấy, việc áp dụng thông minh các tham chiếu ô tuyệt vời và hoàn hảo nhất và tương đối là điều làm cho sự khác 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ả hai 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 sơn màu tổng thể hàng dựa trên các giá trị lặp trong một cột nuốm thể. Tuy vậy nếu bạn có nhu cầu xem các hàng có mức giá trị tương đương nhau trong một vài ba cột? Hoặc, làm cụ nào để bạn làm rất nổi bật các hàng trùng lặp tuyệt đối, những cái có giá trị hoàn toàn bằng nhau trong tất cả các cột?
Đối cùng với điều này, sử dụng công dụng COUNTIFS được cho phép so sánh những ô theo rất nhiều tiêu chí. Ví dụ: để triển khai nổi bật các hàng trùng lặp có mức giá trị giống nhau vào cột A và 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 xuất hiện thêm đầ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 gồm cả lần xuất hiện đầ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ỉ với tính trình làng cách làm cho các bạn vì vào thực tế, bạn sẽ không chỉ triển khai việc làm khá nổi bật giá trị lặp chỉ trong 2 cột nhưng mà sẽ tiến hành ở số lượng giới hạn lớn hơn. Với hàm COUNTIFS bạn có thể xử lý tới 127 phạm vi / cặp tiêu chuẩn.
Làm khá nổi bật các ô xào nấu liên tiếp vào Excel
Đôi khi, chúng ta không đề nghị phải lưu lại tất cả các dữ liệu lặp trong một cột nhưng chỉ hiển thị các ô sao chép liên tục, có nghĩa là các ô khớp ứng với nhau. Để làm điều này, nên chọn ô có dữ liệu (không bao hàm tiêu đề cột) và chế tạo ra một quy tắc format có điều kiện với một trong số công thức sau:
Để làm khá nổi bật các tài liệu lặp thường xuyên mà không bao gồm lần lộ diện đầu tiên: =$A1=$A2 Để làm trông rất nổi bật các dữ 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 phương pháp làm nổi bật các văn bản trùng lặp liên tiếp, nhưng các quy tắc này cũng có thể áp dụng cho số và 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à bạn không muốn những ô trống liên tiếp được làm 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 rất nổi bật các ô xào luộc liên tiếp mà không có lần xuất hiện đầu tiên và bỏ 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 trước tiên và quăng quật qua các ô trống: = và ($ A2 “”, OR ($ A1 = $ A2, $ A2 = $ A3)) Như chúng ta thấy, vấn đề làm nổi bật các dữ liệu trùng lặp trong Excel bằng phương pháp sử dụng format có đk vô cùng solo giản, nên bạn sẽ không chạm chán phải bất cứ khó khăn nào. Quanh đó ra, vẫn còn đấy một bí quyết khác để gia công nổi bật các dữ liệu lặp trong Excel, để biết cách làm đó 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.
bí quyết làm trông rất nổi bật các tài liệu lặp vào Excel 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 vào Excel. Nó có thể tìm, đánh dấu, chọn, sao chép hoặc di chuyển các ô xào luộc 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 mới cho Excel:
– Dedupe Table – giúp ngay mau lẹ tìm và khắc ghi các dữ liệu lặp vào 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 để khẳng định và khắc ghi các dữ liệu lặp hoặc cực hiếm duy nhất trong 1 bảng. – Compare 2 Tables – tìm và lưu 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 biệt. Sau khi thiết đặt Duplicate Remover, các bạn sẽ tìm thấy những quy định này bên trên tab dữ liệu Ablebits trong team Dedupe:
Làm khá 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 ví dụ này, học tập Excel Online đã tạo thành bảng sau với hàng nghìn hàng. Và mục đích là làm nổi bật các mặt hàng trùng lặp có giá trị cân nhau trong cả bố cột:
Bạn có thể tin hoặc không, mà lại thực sự là chúng ta cũng có thể nhận được công dụng mong ao ước với chỉ 2 cú click chuột.
– Với bất kỳ ô nào trong bảng đã có được chọn, nhấp vào nút Dedupe Table, và app thông minh sẽ chọn toàn bộ bảng. – hành lang cửa số hộp thoại Dedupe Table sẽ mở ra với toàn bộ các cột được chọn tự động, với tùy chọn màu sắc duplicates được lựa chọn theo khoác định. Vày vậy, vớ cả các bạn phải làm cho là dìm OK. Mẹo. Nếu bạn có nhu cầu phát hiện những hàng giống nhau trong một hoặc nhiều cột, hãy vứt chọn tất cả 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ương tự như như sau:
Như bạn thấy vào hình trên, cách thức Dupe Table đang làm khá nổi bật các sản phẩm trùng lặp cơ mà không bao hàm trường phù hợp đầu tiên.
Nếu bạn muốn làm trông rất nổi bật các tài liệu lặp, của cả lần xuất hiện thêm đầu tiên, hoặc nếu bạn có nhu cầu tô color các phiên bản ghi khác hoàn toàn chứ chưa hẳn là các bạn dạng ghi trùng lặp, hoặc nếu khách hàng không thích red color mặc định, hãy thực hiện trình mẹo nhỏ Duplicate Remover, ở kia có tất cả các thiên tài này và còn nhiều hơn thế nữa nữa .
Làm rất nổi bật các dữ liệu lặp trong Excel bằng cách sử dụng trình phía dẫn từng bước nâng cao
So với cơ chế Dedupe Table, thủ pháp Duplicate Remover yên cầu nhiều hơn một vài ba cú nhấp chuột, nhưng lại có thêm một số tùy chọn vấp ngã sung.
– Chọn ngẫu nhiên ô nào trong bảng mà bạn muốn làm khá nổi bật các dữ liệu lặp, và nhấp vào nút Duplicate Remover trên thanh thực đơn. Trình giải đáp sẽ chạy và cục bộ bảng sẽ tiến hành chọn. Add-in cũng sẽ gợi ý tạo thành một phiên bản sao lưu lại của bảng của bạn. Nếu như bạn không buộc phải thì hoàn toàn có thể bỏ lựa chọn hộp đó. Xác minh rằng bảng đã được chọn đúng đắn và dìm Next. 2. Chọn kiểu dữ liệu mà bạn có nhu cầu tìm trong số những chọn lọc 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 – tài liệu lặp với cả lần xuất hiện trước tiên – chất lượng values – giá trị duy duy nhất – chất lượng values & 1st duplicate occurrences – quý hiếm duy nhất và lần xuất hiện thêm trùng lặp lần đầu tiên Đối với ví dụ như này, chúng ta hãy tìm tài liệu lặp cùng cả lần xuất hiện đầu tiên: 3. Bây giờ, chọn các cột bắt buộc kiểm tra các dữ liệu lặp. Ở đây buộc phải làm nổi bật các toàn bộ các hàng, cần ta chọn toàn bộ 3 cột. Quanh đó ra, add-in này có thêm tùy chọn nếu bảng của người sử dụng của title hoặc bạn muốn bỏ qua các ô trống. Cả hai tùy chọn này đều được đặt theo mang định.
4. Cuối cùng, chọn thao tác cần tiến hành trên dữ liệu lặp. Chúng ta có một số tùy lựa chọn như chọn, xóa, sao chép, dịch rời các tài liệu lặp hoặc thêm cột trạng thái để xác minh các tài liệu lặp đó. Tất 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. Dấn Finish rồi xem tác 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 các bạn và mong gặp mặt lại các bạn trong những nội dung bài viết sau.
Để có thể ứng dụng giỏi Excel vào vào công việc, họ không chỉ nắm rõ được những hàm nhưng mà còn phải sử dụng giỏi cả những công ráng của Excel. đều hàm cải thiện giúp áp dụng xuất sắc vào quá trình như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những chính sách 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 đào tạo Excel cùng chuyên viên / giảng viên Nguyễn Đức Thanh: