CÁCH HIGHLIGHT DỮ LIỆU TRÙNG LẶP trong BẢNG TÍNH EXCEL
Khi mà chúng ta tự nhập dữ liệu thì nhiều khi sẽ có những dữ liệu bị trùng lặp, bị lặp lại trong một cột, một mặt hàng hoặc 1 vùng dữ liệu. Dưới đây là quá trình thực hiện nay highlight dữ liệu bị trùng lặp trong Excel
Bước 1: lựa chọn cột, mặt hàng hoặc vùng dữ liệu bạn nên xem đa số giá trị bị trùng lặp.
Bạn đang xem: Cách tô màu những ô cùng giá trị trong excel
Bước 2: trên Thanh luật Excel lựa chọn Home.
Bước 3: tại Styles chọn Conditional Formatting.
Bước 4: lựa chọn Highlight Cells Rules và chọn Duplicate Values....
Bước 5: lựa chọn định dạng bạn ao ước muốn.
Bước 6: sau khi chọn dứt định dạng nhấp lựa chọn OK.
Trong lý giả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 vào excel. Bọn họ sẽ bao gồm cái quan sát cận cảnh về các cách thức khác nhau nhằm ẩn những ô trùng lặp, toàn thể các sản phẩm 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.
Cách nhanh nhất có thể để tìm và lưu lại các dữ liệu lặp vào Excel là thực hiện định dạng có đ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 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 những dữ liệu lặp mới khi bạn nhập, sửa đổi hoặc chèn dữ liệu.
Ngoài ra, chúng ta cũng học được từ nội dung bài viết này một số cách để làm khá nổi bật dữ liệu lặp tùy trực thuộc vào trọng trách 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à thấp hơn.
Xem nhanh
ToggleCách làm nổi bật các dữ liệu lặp trong Excel bằng cách sử dụng luật lệ built-in (với lần lặp đầu tiên)Làm trông rất nổi bật các bạn dạng sao trong vô số cột mà không có lần xuất hiện thêm lần đầu
Cách làm khá nổi bật các hàng giống nhau trong Excel bí quyết làm trông rất nổi bật các tài liệu lặp trong Excel 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 phương pháp sử dụng phép tắc built-in (với lần lặp đầu tiên)
Đối với những người mới bắt đầu, trong toàn bộ các phiên bạn dạng Excel, gồm một quy tắc được xác đị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 thực hiện theo công việc sau:1. Lựa chọn dữ liệu bạn có nhu cầu kiểm tra dữ liệu lặp. Đó hoàn toàn có thể là một cột, một sản phẩm 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 số trong những ít các định dạng được xác minh trước có sẵn trong list thả xuống. Để làm rất nổi bật các dữ liệu lặp bằng cách sử dụng một vài màu khác, hãy nhấp vào Custom Format… (mục cuối cùng 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.
Xem thêm: Bí ẩn nguyên nhân giá vàng trong nước tăng, nguyên do giá vàng trong nước tăng kỷ lục
Sử dụng quy tắc 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ư 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 xây dựng trong hai hoặc các cột, Excel ko so sánh các giá trị trong số cột đó, nó chỉ đơn giản 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 để gia công nổi bật giá trị lặp, xin chú ý hai điều sau:
– Nó chỉ vận động cho những ô riêng biệt lẻ. Để đánh dấu 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ị 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 ba cột. – Nó làm cho mờ các ô trùng lặp bao gồm cả lần xuất hiện trước tiên của dữ liệu lặp đó. Để làm rất nổi bật tất cả các phiên bản sao trừ các trường hợp lộ diệ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 phương pháp 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 trông rất nổi bật các tài 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 có nhu cầu tô color và tạo ra quy tắc dựa vào công thức theo cách này:
1. Bên trên tab Trang chủ, trong team Styles, lựa chọn Conditional Formatting > New rule > Use a formula khổng lồ determine which cells khổng lồ 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ố đó 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 bôi đậm và / hoặc màu sắc chữ mà chúng ta muốn. 4. Cuối cùng, dìm OK để lưu và áp dụng quy tắc.
Cách hiển thị các dữ liệu lặp xuất hiện lần thứ 3, đồ vật 4 và toàn bộ các dữ 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 buộc phải tạo một quy tắc định dạng có đk dựa trên công thức như trong lấy ví dụ như trước, với sự khác biệt duy độc nhất là sửa chữa > 1 ngơi nghỉ cuối công thức bằng một vài theo yêu thương cầu. Ví dụ:
Để làm nổi bật dữ liệu lặp thứ ba và tất cả các ngôi trường hợp đụng hàng tiếp theo, chế tác một quy tắc định dạng 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 thiết bị 4 và toàn bộ 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 nổi bật một cực hiếm lặp nhất thiết bạn sử dụng toán tử bởi (=). Ví dụ: để chỉ những dữ liệu lặp lần sản phẩm công nghệ 2, bạn sẽ làm theo bí quyết sau:
=COUNTIF($A$2:$A2,$A2)=2 biện pháp làm 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 có nhu cầu 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 những cột cùng với nhau, bạn hãy sử dụng một trong các các giải pháp 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 những lần cũng được xem như là một bạn dạng sao thì cách dễ nhất là thành lập công thức tài liệu lặp trong Excel.
Hoặc, chế tạo một quy tắc định dạng có điều kiện với bí quyết này:
COUNTIF(range, top_cell)>1 Ví dụ: để gia công 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 để ý đến câu hỏi sử dụng những tham chiếu ô tuyệt vời cho dải ($A$2:$C$8), và những tham chiếu kha khá cho ô trên thuộc (A2).
Học Excel nhanh cực kỳ hiệu quả
Làm rất nổi bật các bản sao trong vô số nhiều cột mà không có lần xuất hiện lần đầu
Các phương án cho trường thích hợp này sẽ có được chút tinh vi hơn vị Excel không desgin quy tắc sẵn.
Để làm trông rất nổi bật các mục đụng hàng trong một vài cột bỏ qua mất lần xuất hiện đầu tiên, bạn sẽ phải tạo nên 2 phép tắc với các công thức sau:
Nguyên tắc 1. Áp dụng mang lại cột đầu tiên
Ở đây bạn sử dụng đúng đắn cùng một bí quyết như họ đã sử dụng để làm 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 ví dụ này, bọn họ đang tạo thành một quy tắc mang lại 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 tất cả lần xuất hiện trước tiên được ghi lại sát cột bên trái của dải ô (chỉ gồm một mục như vậy trong trường hợp của ví dụ này):
Quy tắc 2. Áp dụng cho tất cả các cột tiếp theo
Để làm rất nổi bật các bạn dạng sao trong các 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 phương pháp trên, tính năng COUNTIF đếm những tài liệu lặp trong cột trước tiên được chỉ định và hướng dẫn sẵn, với COUNTIF vẫn thực hiện công việc tương tự với những cột tiếp theo.
Do đó, toàn bộ các mục lặp trừ lần xuất hiện đầu tiên của chúng được tra cứu 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 đó B2 là ô đầu có tài liệu trong cột thứ 2 của dải đích.
– Đối với cột thứ nhất (B), điều kiện IF ko thể đáp ứng được yêu cầu, bởi vì vậy chỉ sử dụng tác dụng COUNTIF sản phẩm hai, là công thức thường thì mà chúng ta đã thực hiện để search các bạn dạng sao mà không có lần lộ diện lần trước tiên trong một cột. – Đối với tất cả các cột tiếp theo sau (C2:D8), điểm chủ quản là việc sử dụng các tham chiếu tuyệt vời và hoàn hảo nhất và kha khá một cách 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ó thể thấy công thức thay đổi như cố gắng nào lúc áp dụng cho các ô khác:
Bởi vì điều kiện IF luôn luôn TRUE cho toàn bộ các cột khác chứ không chỉ có cột trước tiên (có nhiều hơn nữa 1 cột), công thức được thực hiện theo bí quyết 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 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 lộ diện của một mục nhất thiết trong cột đựng mục đó, cho đến ô của mục đó: COUNTIF(D$2:D5,D5) – Cuối cùng, phương pháp cộng thêm công dụng của cả hai hàm COUNTIF. Giả dụ tổng số lớn hơn 1, nghĩa là ví như mục mở ra nhiều lần, quy tắc vẫn đượ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 vào giá trị lặp trong một cột giả dụ bảng của bạn có khá nhiều cột, có thể bạn sẽ muốn làm cho nổi bật toàn thể các mặt hàng dựa trên bạn dạng ghi giống nhau trong một cột chũm thể.
Như bạn đã biết, nguyên tắc được thi công trong Excel cho các dữ liệu lặp chỉ hoạt động ở lever ô. Nhưng lại một quy tắc dựa trên công thức tùy chỉnh có thể chuyển động ở cấp độ cao rộng thế. Điểm then chốt là chọn toàn bộ các hàng, và tiếp đến tạo một nguyên tắc với một trong những công thức sau:
– Để làm trông rất nổi bật các hàng trùng lặp 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 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à ô đầu tiên và A15 là ô được sử dụng sau 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 á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 khiến cho sự khác biệt. Ảnh chụp màn hình sau đây thể hiện tại cách hoạt động của cả nhì quy tắc:
Cách làm rất 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 các giá trị lặp vào một cột nạm thể. Nhưng lại nếu bạn muốn xem những hàng có giá trị tương đương nhau trong một vài cột? Hoặc, làm nỗ lực nào để bạn làm trông rất nổi bật các hàng đụng hàng tuyệt đối, những cái có giá bán trị hoàn toàn bằng nhau trong toàn bộ các cột?
Đối với điều này, sử dụng tác dụng COUNTIFS được cho phép so sánh những ô theo khá 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ư là nhau trong cột A và B, hãy sử dụng một trong các công thức sau:
Để làm trông rất nổi bật các hàng đụng hàng ngoại trừ những lần xuất hiện đầu tiên: =COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)>1 Để làm khá nổi bật các mặt hàng trùng lặp bao gồm cả lần mở ra đầ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ỉ với tính reviews 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 nổi bật giá trị lặp chỉ vào 2 cột nhưng sẽ triển khai ở giới hạn lớn hơn. Với hàm COUNTIFS bạn 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 luộc liên tiếp vào Excel
Đôi khi, các bạn không đề nghị phải đánh dấu tất cả những dữ liệu lặp trong một cột nhưng mà chỉ hiển thị các ô sao chép liên tục, tức là các ô tương xứ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à tạo một quy tắc định dạng có điều kiện 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 nổi bật các dữ 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 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 rất có thể áp dụng đến số cùng ngày trùng lặp:
Nếu bảng tính Excel của bạn có thể có các hàng trống rỗng và chúng ta không muốn những ô trống liên tiếp được gia công nổi bật, hãy tiến hành những cách tâ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 tồn tại lần xuất hiện đầu tiên và vứt qua các ô trống: =AND($A2””, $A1=$A2) Để làm rất nổi bật các ô giống nhau với lần xuất hiện thứ nhất và vứt qua các ô trống: = and ($ A2 “”, OR ($ A1 = $ A2, $ A2 = $ A3)) Như các 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ó đk vô cùng đối kháng giản, nên các bạn sẽ không gặp phải bất kể khó khăn nào. Ko kể ra, vẫn còn một phương pháp khác để làm nổi bật các dữ liệu lặp trong Excel, để biết phương pháp làm đó 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.
cách làm trông rất nổi bật các dữ 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 trong Excel. Nó có thể tìm, tấn công dấu, chọn, xào luộc hoặc dịch chuyển các ô sao chép hoặc cục bộ 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 nhanh chóng tìm và lưu lại các dữ 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 cao để xác định 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 – tra cứu và khắc ghi các dữ 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 setup Duplicate Remover, bạn sẽ tìm thấy những phương tiện này bên trên tab tài liệu Ablebits trong nhóm Dedupe:
Làm rất 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 lấy ví dụ như này, học tập Excel Online đã tạo thành bảng sau với hàng trăm ngàn hàng. Và mục đích là làm rất nổi bật các hàng trùng lặp có mức giá trị bằng nhau trong cả ba cột:
Bạn có thể tin hoặc không, mà lại thực sự là chúng ta có thể nhận được công dụng mong ý muốn với chỉ 2 cú click chuột.
– Với ngẫu nhiên ô làm sao trong bảng đã được chọn, nhấp vào nút Dedupe Table, và app thông minh vẫn chọn toàn bộ bảng. – cửa sổ hộp thoại Dedupe Table sẽ xuất hiện thêm với toàn bộ các cột được lựa chọn tự động, với tùy chọn màu sắc duplicates được chọn theo mang định. Vày vậy, vớ cả các bạn phải có tác dụng là thừa nhận OK. Mẹo. Nếu bạn có nhu cầu phát hiện những hàng trùng lặp trong một hoặc nhiều cột, hãy quăng quật 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. Hiệu quả nhận được giống như như sau:
Như chúng ta thấy vào hình trên, biện pháp Dupe Table đang làm rất nổi bật các sản phẩm trùng lặp nhưng mà không bao hàm trường vừa lòng đầu tiên.
Nếu bạn muốn làm nổi bật các dữ liệu lặp, kể cả lần lộ diện đầu tiên, hoặc nếu bạn có nhu cầu tô màu sắc các bản ghi khác hoàn toàn 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 áp dụng trình mẹo nhỏ Duplicate Remover, ở đó có toàn bộ các tài năng này với còn nhiều hơn thế nữa .
Làm nổi bật các tài liệu lặp trong Excel bằng cách sử dụng trình phía dẫn mỗi bước nâng cao
So với pháp luật Dedupe Table, mẹo nhỏ Duplicate Remover yên cầu nhiều hơn một vài cú nhấp chuột, nhưng lại có thêm một vài tùy chọn té sung.
– Chọn ngẫu nhiên ô làm sao trong bảng mà bạn có nhu cầu làm rất nổi bật các tài liệu lặp, và nhấp vào nút Duplicate Remover bên trên thanh thực đơn. Trình hướng dẫn 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 ý chế tạo một phiên bản sao lưu lại của bảng của bạn. Nếu khách hàng không buộc phải thì có thể bỏ chọn hộp đó. Xác minh rằng bảng đã làm được chọn đúng mự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 số những gạn lọc sau: – Duplicates except 1st occurrences – tài liệu lặp bên cạnh lần xuất hiện thêm lần đầu – Duplicates with 1st occurrences – tài liệu lặp cùng cả lần xuất hiện thứ nhất – quality values – cực hiếm duy duy nhất – chất lượng values & 1st duplicate occurrences – quý hiếm duy nhất và lần lộ diện trùng lặp lần đầu tiên Đối với lấy ví dụ này, họ hãy tìm dữ liệu lặp và cả lần lộ diện đầu tiên: 3. Bây giờ, chọn những cột đề nghị kiểm tra các dữ liệu lặp. Ở đây phải làm khá 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òn có thêm tùy lựa chọn nếu bảng của người tiêu dùng của tiêu đề hoặc bạn muốn bỏ qua những ô trống. Cả hai tùy lựa chọn này số đông được đặt theo mặc định.
4. Cuối cùng, chọn làm việc cần tiến hành trên dữ liệu lặp. Các bạn có một số tùy lựa 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 tâm lý để xác minh các tài liệu lặp đó. Vớ nhiên, việc lựa chọn màu sắc 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 … với chọn bất kể màu nào tất cả sẵn hoặc màu tùy chỉnh. Nhấn Finish rồi xem tác dụng Hy vọng rằng, bài viết này đem đến nhiều thông tin hữu dụng cho các bạn và mong chạm mặt lại các bạn trong những 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 mà còn đề xuất sử dụng tốt cả các công ráng của Excel. Hầu hết hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những luật thường thực hiện 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 học Excel cùng chuyên viên / giáo viên Nguyễn Đức Thanh:
