Trong hướng dẫn này, Học Excel Online đang hướng dẫn các bạn cách tìm giá trị trùng nhau vào excel. Bọn họ sẽ có 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, tổng thể các sản phẩm 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 gồm điều kiện.
Bạn đang xem: Tìm dữ liệu trùng trong excel
Cách sớm nhất có thể để tra cứu và ghi lại các dữ liệu lặp trong Excel là thực hiện định dạng tất cả điều kiện. Ưu điểm lớn số 1 của cách thức này là nó không chỉ là hiển thị cho bạn các tài liệu lặp hiện gồm mà cò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 nổi bật dữ liệu lặp tùy trực thuộc 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 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 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)Làm rất nổi bật các phiên bản sao trong vô số cột mà không tồn tại lần xuất hiện thêm lần đầuCách làm nổi bật các hàng đụng hàng trong Excel phương pháp làm khá nổi bật các tài 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 trong Excel bằng cách sử dụng nguyên 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 Excel, bao gồm một quy tắc được xác định trước để gia công nổi bật những ô trùng lặp. Để thực hiện quy tắc này trong số bảng tính, hãy thực hiện theo các bước sau:
1. Lựa chọn dữ liệu bạn có nhu cầu 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…
Ngoài câu hỏi điền vào màu đỏ và định dạng văn bản, một vài ít những định dạng được xác định trước gồm sẵn trong danh sách thả xuống. Để làm trông rất nổi bật các dữ 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 cuối trong trình đơn thả xuống) và lựa chọn màu điền và / hoặc màu sắc chữ theo ý thích.
Sử dụng quy tắc inbuilt, chúng ta cũng có thể làm nổi bật các bạn dạng sao vào một cột hoặc trong một vài cột như mô tả trong ả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 xây đắp trong hai hoặc các cột, Excel không so sánh những giá trị trong những cột đó, nó chỉ đơn giản dễ dàng làm khá nổi bật tất cả các trường phù 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 chú ý hai điều sau:
– Nó chỉ chuyển động cho những ô riêng lẻ. Để khắc ghi các mặt 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 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ị vào một vài cột. – Nó làm mờ các ô trùng lặp của cả lần xuất hiện thứ nhất của tài liệu lặp đó. Để làm trông rất nổi bật tất cả các phiên bản sao trừ các trường hợp xuất hiện lần đầu tiên, hãy chế tạo ra 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 tồn tại lần mở ra đầ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 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 phong cách này:
1. Bên trên tab Trang chủ, trong team Styles, chọn Conditional Formatting > New rule > Use a formula lớn determine which cells lớn format. 2. Trong hộp Format values where this formula is true, nhập một công thức giống như như sau: =COUNTIF($A$2:$A2,$A2)>1 trong những số đó 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 bôi đậm và / hoặc màu chữ mà bạn muốn. 4. Cuối cùng, nhận OK để lưu và vận dụng quy tắc.
Cách hiển thị những dữ liệu lặp xuất hiện lần sản phẩm công nghệ 3, thiết bị 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ước đầu với lần xuất hiện thêm thứ N, ta nên tạo một quy tắc định hình có điều kiện dựa trên phương pháp như trong lấy ví dụ trước, với sự biệt lập duy độc nhất vô nhị là sửa chữa thay thế > 1 ngơi nghỉ cuối cách làm bằng một số trong những theo yêu cầu. Ví dụ:
Để làm khá nổi bật dữ liệu lặp thứ cha và toàn bộ các ngôi trường hợp đụng hàng tiếp theo, tạo nên một quy tắc định hình có đk dựa trên phương pháp này:
=COUNTIF($A$2:$A2,$A2)>=3
Để làm trông rất nổi bật dữ liệu lặp sản phẩm 4 và toàn bộ các dữ liệu lặp tiếp theo, sử dụng công thức này:
=COUNTIF($A$2:$A2,$A2)>=4
Để làm khá nổi bật một cực hiếm lặp khăng khăng bạn sử dụng toán tử bằng (=). Ví dụ: nhằm chỉ các dữ liệu lặp lần vật dụng 2, bạn sẽ làm theo cách làm sau:
=COUNTIF($A$2:$A2,$A2)=2 biện pháp 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 muốn 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 với nhau, bạn hãy sử dụng 1 trong những các giải pháp sau.
Làm nổi bật các dữ liệu lặp trong vô số nhiều cột bao gồm cả lần mở ra đầu tiên
Nếu lần xuất hiện trước tiên của tài liệu bị lặp những lần cũng được xem như là một phiên bản sao thì cách dễ nhất là desgin công thức dữ liệu lặp vào Excel.
Hoặc, chế tạo một quy tắc định hình có đk với phương pháp này:
COUNTIF(range, top_cell)>1 Ví dụ: để gia công nổi nhảy các bạn dạng sao trong dải A2:C8, công thức sẽ như sau:
=COUNTIF($A$2:$C$8, A2)>1
Hãy chăm chú đến câu hỏi sử dụng những tham chiếu ô tuyệt đối hoàn hảo cho dải ($A$2:$C$8), và các tham chiếu tương đối cho ô trên thuộc (A2).
Học Excel nhanh cực kỳ hiệu quả
Làm nổi bật các phiên bản sao trong không ít cột mà không có lần lộ diện lần đầu
Các chiến thuật cho trường đúng theo này sẽ sở hữu được chút phức hợp hơn bởi Excel không xây dừng quy tắc sẵn.
Xem thêm: Tử Vi HôM Nay 10/6/2022 CủA 12 Cung HoàNg đạO
Để làm rất nổi bật các mục trùng lặp trong một số cột làm lơ lần mở ra đầu tiên, các bạn sẽ phải tạo ra 2 luật lệ với những 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 chuẩn cùng một phương pháp như bọn 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 trước tiên trong một cột.
Trong lấy ví dụ này, bọn họ đang tạo nên một quy tắc mang đến A2:A8 cùng với công thức:
=COUNTIF($A$2:$A2,$A2)>1
Kết trái 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ỉ bao gồm một mục như vậy trong trường đúng theo của lấy ví dụ như 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 bạn dạng sao trong số cột còn sót 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 phương pháp trên, tác dụng COUNTIF đếm những dữ liệu lặp trong cột đầu tiên được hướng dẫn và chỉ định sẵn, với 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 trước tiên của bọn chúng được tìm thấy với đượ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ó tài liệu trong cột thứ hai của dải đích.
– Đối cùng với cột thứ nhất (B), đk IF ko 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ì mà họ đã áp dụng để search các bạn dạng sao mà không có lần xuất hiện thêm lần thứ nhất trong một cột. – Đối với tất cả các cột tiếp sau (C2:D8), điểm chủ công là vấn đề sử dụng các tham chiếu hoàn hảo nhất và tương đối một bí quyết 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 chúng ta cũng có thể thấy công thức biến đổi như nắm nào lúc áp dụng cho những ô khác:
Bởi vì đk 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ột trước tiên (có nhiều hơn thế 1 cột), công thức đượ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 ở trên) trong tất cả các cột ở bên trái cột vẫn cho: COUNTIF(B$2:$C$8,D5) – Đếm số lần mở ra của một mục nhất quyết 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 kết quả của cả nhị hàm COUNTIF. Ví như tổng số lớn hơn 1, nghĩa là ví như mục lộ diện nhiều lần, quy tắc đang được áp dụng và mục này được đánh dấu. Đánh dấu cục bộ các hàng dựa vào giá trị lặp vào một cột nếu như bảng của bạn có tương đối nhiều cột, bao gồm thể các bạn sẽ muốn làm nổi bật toàn bộ các sản phẩm dựa trên bạn dạng ghi giống nhau trong một cột cố gắng thể.
Như chúng ta đã biết, nguyên tắc được xây cất trong Excel cho các dữ liệu lặp chỉ chuyển động ở cấp độ ô. Cơ mà một quy tắc dựa vào công thức thiết lập cấu hình có thể hoạt động ở lever cao rộng thế. Điểm cơ bản là chọn toàn bộ các hàng, và tiếp nối tạo một quy tắc với một trong số công thức sau:
– Để làm nổi bật các hàng giống nhau trừ các lần lộ diện đầu tiên: =COUNTIF($A$2:$A2, $A2)>1 – Để làm rất nổi bật các hàng trùng lặp bao gồm cả lần mở ra đầu tiên: =COUNTIF($A$2:$A$15, $A2)>1 trong các số ấy A2 là ô trước tiên và A15 là ô được sử dụng ở đầu cuối trong cột mà bạn có nhu cầu kiểm tra tài liệu lặp. Như bạn thấy, việc sử dụng thông minh những tham chiếu ô hoàn hảo nhất và kha khá là điều làm 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ả hai quy tắc:
Cách làm nổi bật các hàng trùng lặp trong Excel
Ví dụ trước đã share cách đánh màu toàn cục hàng dựa trên những giá trị lặp trong một cột cố kỉnh thể. Tuy vậy nếu bạn có nhu cầu xem những hàng có mức giá trị giống nhau trong một vài cột? Hoặc, làm cố kỉnh nào để các bạn làm nổi bật các hàng đụng hàng tuyệt đối, các cái có giá chỉ 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 chức năng COUNTIFS chất nhận được so sánh các ô theo không ít tiêu chí. Ví dụ: để triển khai nổi bật những hàng trùng lặp có giá trị như thể nhau trong cột A với B, hãy áp dụng một trong những công thức sau:
Để làm nổi bật các hàng đụng hàng 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 nổi bật các mặt 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 ví dụ như trên chỉ mang tính trình làng cách làm cho cho chúng ta 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ỉ vào 2 cột mà sẽ thực hiện ở 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 ô xào nấu liên tiếp trong Excel
Đôi khi, bạn không bắt buộc phải ghi lại tất cả những dữ liệu lặp trong một cột nhưng chỉ hiển thị những ô xào luộc liên tục, có nghĩa là các ô khớp ứ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 hàm tiêu đề cột) và tạo nên một quy tắc định dạng có điều kiện với một trong số công thức sau:
Để làm nổi bật các dữ liệu lặp liên tục mà không bao hàm lần mở ra đầu tiên: =$A1=$A2 Để làm trông rất nổi bật các tài 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 rất 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 mang lại 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à bạn không muốn các ô trống liên tiếp được làm nổi bật, hãy triển khai những đổi mới sau cho những 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 thứ nhất 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 thứ nhất và vứt qua các ô trống: = và ($ A2 “”, OR ($ A1 = $ A2, $ A2 = $ A3)) Như các bạn thấy, câu hỏi làm khá nổi bật các dữ liệu trùng lặp vào Excel bằng phương pháp sử dụng định dạng có điều kiện vô cùng đối chọi giản, nên bạn sẽ không gặp mặt phải bất kể khó khăn nào. Ngoại trừ ra, vẫn còn đó một biện pháp khác để gia công nổi bật các dữ liệu lặp vào Excel, để biết phương pháp làm chính là gì, mời các bạn theo dõi phần tiếp theo sau của nội dung bài viết này.
biện pháp làm trông rất nổi bật các tài liệu lặp trong Excel với Duplicate Remover
Tiện ích Duplicate Remover là chiến thuật tối ưu nhằm xử lý tài liệu trùng lặp trong Excel. Nó rất có thể tìm, đánh dấu, chọn, coppy hoặc dịch rời các ô xào luộc hoặc toàn thể các sản phẩm trùng lặp.
Duplicate Remover tạo thêm 3 tính năng mới cho Excel:
– Dedupe Table – góp ngay chớp nhoáng tìm và đánh dấu các dữ liệu lặp trong một bảng. – Duplicate Remover – thủ thuật từng bước với những tùy chọn nâng cao để xác định và lưu lại các tài liệu lặp hoặc giá trị duy nhất trong một bảng. – Compare 2 Tables – kiếm tìm và khắc ghi các dữ liệu lặp bằng phương pháp so sánh nhị cột hoặc nhị bảng riêng biệt biệt. Sau khi thiết đặt Duplicate Remover, các bạn sẽ tìm thấy những khí cụ này bên trên tab dữ liệu Ablebits trong đội Dedupe:
Làm nổi bật các tài liệu lặp vào Excel bằng 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 nghìn hàng. Và mục tiêu là làm rất nổi bật các mặt hàng 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, mà lại thực sự là chúng ta cũng có thể nhận được hiệu quả mong ao ước 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à ứng dụng thông minh đã chọn toàn bộ bảng. – cửa sổ hộp thoại Dedupe Table sẽ mở ra với toàn bộ các cột được lựa chọn tự động, và tùy chọn màu sắc duplicates được lựa chọn theo khoác định. Vày vậy, tất cả chúng ta phải làm 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 những cột, hãy bỏ chọn toàn bộ các cột không tương quan và chỉ còn lại (các) cột chính. Tác dụng nhận được giống như như sau:
Như bạn thấy vào hình trên, vẻ ngoài Dupe Table sẽ làm nổi bật các hàng trùng lặp mà lại không bao gồm trường thích 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, tất 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 phiên bản ghi biệt lập chứ chưa phải là các phiên bản ghi trùng lặp, hoặc nếu khách hàng không thích màu đỏ mặc định, hãy thực hiện trình mẹo nhỏ Duplicate Remover, ở kia có tất cả các tài năng này cùng còn nhiều không 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 cách sử dụng trình phía dẫn từng bước nâng cao
So với dụng cụ Dedupe Table, thủ pháp Duplicate Remover đòi hỏi nhiều hơn một vài cú nhấp chuột, nhưng lại sở hữu thêm một số trong những tùy chọn té sung.
– Chọn ngẫu nhiên ô nào trong bảng mà bạn muốn làm rất nổi bật các dữ liệu lặp, với nhấp vào nút Duplicate Remover 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 biến thành gợi ý tạo nên một phiên bản sao lưu giữ của bảng của bạn. Nếu khách hàng không buộc phải thì hoàn toàn có thể bỏ chọn hộp đó. Xác minh rằng bảng đã được chọn chính xác và dấn 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 xung quanh lần mở ra lần đầu – Duplicates with 1st occurrences – dữ 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 độc nhất vô nhị – quality values và 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 ví dụ này, họ hãy tìm tài liệu lặp và cả lần lộ diện đầu tiên: 3. Bây giờ, chọn các cột yêu cầu kiểm tra các dữ liệu lặp. Ở đây nên làm khá nổi bật các tất cả các hàng, nên ta chọn tất cả 3 cột. Ngoài ra, add-in này còn có thêm tùy lựa chọn nếu bảng của công ty của tiêu đề hoặc bạn muốn bỏ qua những ô trống. Cả nhị tùy chọn này các được để theo mang định.
4. Cuối cùng, chọn thao tác cần triển khai trên dữ liệu lặp. Chúng ta 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 tài liệu lặp hoặc thêm cột tâm trạng để xác minh các tài liệu lặp đó. Tất nhiên, câu hỏi lựa chọn màu sắc phông chữ cũng dễ ợt hơn hẳn, bạn cũng có thể nhấp vào phần More Colors … với chọn bất cứ màu nào có sẵn hoặc màu sắc tùy chỉnh. Thừa nhận Finish rồi xem kết quả Hy vọng rằng, bài viết này đem đến nhiều thông tin bổ í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.
Để hoàn toàn có thể ứng dụng giỏi Excel vào vào công việc, chúng ta không chỉ nắm rõ được các hàm nhưng còn cần sử dụng tốt cả những công chũm của Excel. Rất nhiề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 luật thường sử 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 hoàn toàn có thể học được trong khóa huấn luyện và đào tạo Excel cùng chuyên viên / giáo viên Nguyễn Đức Thanh:
