Tô màu những ô trùng nhau trong excel

Trong khuyên bảo này, Học Excel Online vẫn hướng dẫn các bạn cách tìm quý hiếm trùng nhau trong 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, toàn cục các sản phẩm hoặc các 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: Tô màu những ô trùng nhau trong excel

Cách sớm nhất có thể để kiếm tìm và khắc ghi 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 chỉ hiển thị cho chính mình các tài liệu lặp hiện có mà còn vạc hiện với tô màu các 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, các bạn cũng học tập đượ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ách nhiệm cụ thể. Những kỹ thuật này chuyển động trong toàn bộ các 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 trong Excel bằng phương pháp 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ố nhiều cột mà không có lần xuất hiện lần đầuCách làm trông rất 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 nổi bật các tài liệu lặp vào Excel bằng phương pháp sử dụng quy 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 phiên bản Excel, có một nguyên tắc được khẳng định trước để triển khai nổi bật những ô trùng lặp. Để áp dụng 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. Trên tab Trang chủ, trong nhóm Styles, chọn Conditional Formatting > Highlight Cells Rules > Duplicate Values…

*
3. Cửa sổ hộp thoại Duplicate Values sẽ mở ra với định hình Light Red Fill & Dark Red Text được lựa chọn theo mặc định. Để áp dụng định dạng mang định, chỉ việc nhấp vào OK.

Ngoài câu hỏi điền vào red color và format văn bản, một vài ít những đị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 cuối cùng trong trình 1-1 thả xuống) và chọn màu điền cùng / hoặc color chữ theo ý thích.

*
Mẹo. Để khắc ghi các giá trị duy nhất, nên lựa chọn Unique ở hộp thoại bên tay trái.

Sử dụng luật lệ inbuilt, bạn có thể làm khá nổi bật các bản sao vào một cột hoặc trong một vài ba cột như diễn tả trong ảnh chụp màn hình sau:

*



Chú thích. Khi áp dụng quy tắc nhân bản được sản xuất trong nhị hoặc nhiều cột, Excel không so sánh những giá trị trong các cột đó, nó chỉ đơn giản và dễ dàng làm rất 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 để triển khai 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 rẽ lẻ. Để khắc ghi các mặt 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 những giá trị trong một cột cụ thể hoặc bằng cách so sánh những giá trị vào một vài cột. – Nó có tác dụng mờ các ô trùng lặp kể cả lần xuất hiện thứ nhất của dữ liệu lặp đó. Để làm khá nổi bật tất cả các bản sao trừ những trường hợp lộ diện lần đầu tiên, hãy tạo nên một quy tắc định hình có đk dựa trên phương pháp này.

Cách làm 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 trông rất nổi bật các dữ 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 lựa chọn các ô mà bạn có nhu cầu tô màu sắc và sản xuất quy tắc dựa vào công thức theo phong cách này:

1. Trên tab Trang chủ, trong đội Styles, lựa chọn Conditional Formatting > New rule > Use a formula khổng lồ determine which cells to format. 2. Trong hộp Format values where this formula is true, nhập một công thức tựa 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à lựa chọn màu điền trét đậm và / hoặc màu chữ mà các bạn muốn. 4. Cuối cùng, nhận OK để lưu và áp dụng quy tắc.

*
kết quả là, các ô coppy trừ những trường hợp thứ nhất sẽ được làm nổi bật với color bạn chọn:
*

Cách hiển thị những dữ liệu lặp xuất hiện thêm lần sản phẩm 3, máy 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ước đầu với lần mở ra thứ N, ta đề nghị tạo một quy tắc định hình có điều kiện dựa trên cách làm như trong lấy ví dụ như trước, cùng với sự khác hoàn toàn duy tuyệt nhất là thay thế sửa chữa > 1 làm việc cuối phương pháp bằng một trong những theo yêu cầu. Ví dụ:

Để làm rất nổi bật dữ liệu lặp thứ tía 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ó đk dựa trên bí quyết này:

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

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

=COUNTIF($A$2:$A2,$A2)=2 giải pháp làm 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 những dữ liệu lặp trên những cột, ko phải bằng phương pháp so sánh các cột cùng với nhau, các bạn hãy sử dụng 1 trong các các phương án sau.

Làm rất nổi bật các dữ liệu lặp trong vô số 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 đầu tiên 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à xây cất công thức dữ liệu lặp vào Excel.

Hoặc, tạo thành một quy tắc format có đk với bí quyết này:

COUNTIF(range, top_cell)>1 Ví dụ: để gia công nổi nhảy các phiên bản sao vào dải A2:C8, bí quyết sẽ như sau:

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

Hãy chú ý đến câu hỏi sử dụng các tham chiếu ô hoàn hảo nhất 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 khá nổi bật các bản sao trong nhiều cột mà không có lần xuất hiện lần đầu

Các giải pháp cho trường hòa hợp này sẽ có được chút phức tạp hơn vị Excel không xây dừng quy tắc sẵn.

Xem thêm: Gunny - Biệt Đội Thép Tập 44

Để làm khá nổi bật các mục trùng lặp trong một vài cột bỏ qua lần lộ diện đầu tiên, 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 đúng chuẩn cùng một cách làm như bọn họ đã sử dụng để gia công nổi bật những dữ liệu lặp mà không có lần xuất hiện đầu tiên trong một cột.

Trong lấy một ví dụ này, bọn họ đang tạo thành một quy tắc cho A2:A8 cùng 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 đầu tiên được ghi lại sát cột phía trái của dải ô (chỉ gồm một mục như vậy trong trường hợp của lấy một ví dụ 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 phiên bản sao trong số cột còn sót 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 tài liệu lặp trong cột đầu tiên được 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 thứ nhất của bọn chúng được tìm thấy cùng được tấn công dấu:

*
Làm trông 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 phép tắc Một giải pháp khác 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 công ty và phối hợp các bí quyết 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ó dữ liệu trong cột thứ hai của dải đích.

*
Để nắ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 đầu tiên (B), đk IF ko thể thỏa mãn nhu cầu được yêu cầu, bởi vì vậy chỉ sử dụng tác dụng COUNTIF lắp thêm hai, là công thức thường thì mà chúng ta đã thực hiện để kiếm tìm các bản sao mà không tồn tại lần lộ diện 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 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 giải pháp thông minh trong nhị hàm COUNTIF. Để tạo nên mọi thứ dễ nắm bắt hơn,các chúng ta có thể thấy công thức biến đổi như thế 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ó cột đầu tiên (có nhiều hơn thế 1 cột), phương pháp được thực hiện theo giải pháp này:

– Đếm số lần xuất hiện của một mục nhất định (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 xuất hiện của một mục khăng khăng trong cột chứa mục đó, cho tới ô của mục đó: COUNTIF(D$2:D5,D5) – Cuối cùng, công thức cộng thêm kết quả của cả hai hàm COUNTIF. Nếu như tổng số to hơn 1, nghĩa là giả dụ mục xuất hiện thêm nhiều lần, quy tắc đang đượ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 ví như bảng của bạn có tương đối nhiều cột, gồm thể bạn sẽ muốn làm nổi bật toàn thể các mặt hàng dựa trên bản ghi giống nhau trong một cột núm thể.

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

– Để làm rất nổi bật các hàng đụng hàng trừ những lần xuất hiện thêm đầu tiên: =COUNTIF($A$2:$A2, $A2)>1 – Để làm trông rất nổi bật các hàng trùng lặp bao hàm cả lần xuất hiện đầu tiên: =COUNTIF($A$2:$A$15, $A2)>1 trong đó A2 là ô trước tiên và A15 là ô được sử dụng cuối cùng trong cột mà bạn có nhu cầu kiểm tra tài liệu lặp. Như chúng ta thấy, việc thực hiện thông minh các tham chiếu ô hoàn hảo và tuyệt vời nhất và tương đối là điều tạo nên sự không giống 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 giống nhau trong Excel

Ví dụ trước đã share cách tô màu cục bộ hàng dựa trên những giá trị lặp trong một cột núm thể. Tuy nhiên nếu bạn có nhu cầu xem những hàng có mức giá trị kiểu như nhau trong một vài ba cột? Hoặc, làm cố gắng nào để bạn làm trông rất nổi bật các hàng giống nhau 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 tác dụng COUNTIFS có thể chấp nhận được so sánh những ô theo khá 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ị tương đương nhau trong cột A với B, hãy sử dụng một trong số công thức sau:

Để làm nổi bật các hàng trùng lặp 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 trông 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: =COUNTIFS($A$2:$A$15, $A2, $B$2:$B$15, $B2)>1 tuy nhiên, ví dụ trên chỉ với tính giới thiệu cách làm cho cho các bạn vì vào thực tế, bạn sẽ không chỉ thực hiện việc làm khá nổi bật giá trị lặp chỉ trong 2 cột mà lại sẽ tiến hành ở giới hạn lớn hơn. Với hàm COUNTIFS bạn cũng có thể xử lý cho 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 vào Excel

Đôi khi, chúng ta không buộc phải phải đánh dấu tất cả những dữ liệu lặp vào một cột nhưng mà chỉ hiển thị các ô xào luộc liên tục, tức là các ô tương xứng với nhau. Để có tác dụng điều này, hãy 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 định dạng có điều kiện với một trong những công thức sau:

Để làm khá 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 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 xuất hiện thêm đầu tiên: =OR($A1=$A2, $A2=$A3) Ảnh chụp screen 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 đến số với 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à chúng ta 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ách tân sau cho những công thức:

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

phương pháp làm trông rất nổi bật các dữ liệu lặp vào Excel cùng với Duplicate Remover

Tiện ích Duplicate Remover là phương án tối ưu để xử lý dữ liệu trùng lặp vào Excel. Nó hoàn toàn có thể tìm, tấn công dấu, chọn, sao chép hoặc dịch rời các ô coppy 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 lạ cho Excel:

– Dedupe Table – góp ngay nhanh chóng tìm và lưu lại các tài 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à 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à đánh dấu 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 rẽ biệt. Sau khi setup Duplicate Remover, bạn sẽ tìm thấy những khí cụ này trên tab tài liệu Ablebits trong team Dedupe:

Làm trông 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 Excel Online đã tạo nên bảng sau với hàng trăm hàng. Và mục đích là làm rất nổi bật các mặt hàng trùng lặp có mức giá trị đều nhau trong cả tía cột:

Bạn hoàn toàn có thể tin hoặc không, nhưng thực sự là chúng ta có thể nhận được hiệu quả mong muốn với chỉ 2 cú click chuột.

– Với ngẫu nhiên ô 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 vẫn chọn toàn thể bảng. – hành lang cửa số hộp thoại Dedupe Table sẽ xuất hiện với tất cả các cột được lựa chọn tự động, cùng tùy chọn màu sắc duplicates được chọn theo mặc định. Vì vậy, vớ cả chúng ta phải làm cho là thừa nhận 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 quăng quật chọn toàn bộ 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, chính sách Dupe Table vẫn làm khá nổi bật các mặt hàng trùng lặp mà không bao hàm trường hòa hợp đầu tiên.

Nếu bạn có nhu cầu làm nổi bật các tài liệu lặp, tất cả lần xuất hiện đầu tiên, hoặc nếu bạn muốn tô màu các phiên bản ghi khác biệt chứ chưa hẳn là các phiên bản ghi trùng lặp, hoặc nếu như 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, ở đó có toàn bộ các kĩ năng này với còn nhiều hơn thế nữa .

Làm khá nổi bật các dữ 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 giải pháp Dedupe Table, thủ pháp Duplicate Remover yên cầu nhiều rộng một vài ba cú nhấp chuột, nhưng lại sở hữu thêm một số trong những tùy chọn ngã 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, với nhấp vào nút Duplicate Remover trên thanh thực đơn. Trình giải đáp sẽ chạy và toàn thể bảng sẽ được chọn. Add-in cũng biến thành gợi ý tạo ra một phiên bản 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ó được chọn đúng chuẩn và thừa nhận Next. 2. Chọn kiểu tài liệu mà bạn muốn tìm trong số những chọn lựa sau: – Duplicates except 1st occurrences – dữ liệu lặp ko kể lần mở ra 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 – unique values – cực hiếm duy tuyệt nhất – quality values và 1st duplicate occurrences – quý hiếm 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 một ví dụ này, chúng ta hãy tìm tài liệu lặp và cả lần xuất hiện thêm đầ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 đề nghị làm trông rất nổi bật các toàn bộ các hàng, nên ta chọn tất cả 3 cột. Không tính ra, add-in này 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 lựa chọn này đông đảo được để theo mang định.

4. Cuối cùng, chọn thao tác cần thực hiện trên tài 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, di chuyển các tài liệu lặp hoặc thêm cột tâm trạng để xác định các dữ liệu lặp đó. Vớ nhiên, vấn đề lựa chọn màu sắc phông chữ cũng tiện lợi hơn hẳn, bạn 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 sắc tùy chỉnh. Dấn Finish rồi xem công dụng Hy vọng rằng, bài viết này mang lại nhiều thông tin bổ ích cho chúng ta và mong gặp gỡ lại các bạn trong những bài viết sau.

Để rất có thể ứng dụng tốt Excel vào vào công việc, bọn họ không chỉ nắm rõ được những hàm nhưng còn nên sử dụng tốt cả các công cụ của Excel. Hầu như hàm nâng cấp giúp áp dụng giỏi vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những cơ chế 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 hoàn toàn có thể học được trong khóa huấn luyện Excel cùng chuyên viên / giảng viên Nguyễn Đức Thanh:

Leave a Reply

Your email address will not be published. Required fields are marked *