Cách tìm các giá trị giống nhau trong một cột Excel. Tìm và loại bỏ các bản sao trong Microsoft Excel

Hãy xem xét ĐẾN Cách tìm và đánh dấu các giá trị giống nhau trong Excel. Định dạng có điều kiện sẽ giúp chúng tôi. Định dạng có điều kiện là gì và cách sử dụng, xem bài viết “Định dạng có điều kiện trong Excel”.Bạn có thể chọn các giá trị trùng lặp trong Excel cả trong toàn bộ bảng và trong một phạm vi cụ thể (hàng, cột). Một chức năng" Lọc trong Excel " sẽ giúp ẩn chúng nếu cần thiết. Hãy xem xét một số cách.
Cách đầu tiên.
Cách tìm các giá trị giống nhau trong Excel.
Ví dụ: số, họ, v.v. Cách thực hiện xem bài viết “Cách chọn ô trong Excel”.
Cách thứ hai.
Cách đánh dấu các giá trị trùng lặp trong Excel. Trong bảng này chúng ta cần nhấn mạnh năm sinh 1960. Chọn cột “Năm sinh”.Trên tab “Trang chủ”, trong phần “Phong cách”, nhấp vào nút “Định dạng có điều kiện”. Sau đó, trong phần “Quy tắc ô đã chọn”, chọn “Giá trị trùng lặp”.
Trong hộp thoại xuất hiện, chọn những gì chúng ta cần làm nổi bật: giá trị trùng lặp hoặc duy nhất. Chọn màu tô ô hoặc màu phông chữ.
Để biết thêm chi tiết, hãy xem bài viết “Đánh dấu ngày, ngày trong tuần trong Excel được cung cấp”.
Nhấp vào "OK". Trong cột D, tất cả các năm đều được tô sáng - 1960.

Trong định dạng có điều kiện, bạn cũng có thể chọn chức năng “Chứa văn bản” trong phần “Quy tắc cho các ô đã chọn”. Viết văn bản này (ví dụ: họ, số, v.v.) và tất cả các ô có văn bản này sẽ được tô sáng màu. Chúng tôi viết họ “Ivanov”. Có nhiều cách khác để tìm các giá trị giống hệt nhau trong Excel và đánh dấu chúng không chỉ bằng màu sắc mà còn bằng từ, số và dấu hiệu. Bạn có thể định cấu hình bảng để các bản sao không chỉ được đánh dấu mà còn được tính. Bạn có thể chọn nghĩa lặp lại từ từ đầu tiên hoặc bạn có thể chọn nghĩa trùng lặp từ từ thứ hai trở đi. Đọc về tất cả những điều này và nhiều hơn nữa trong bài viết "

Hãy tận dụng định dạng có điều kiện. Chúng tôi đã thảo luận về chủ đề này trong bài viết và bây giờ chúng tôi sẽ áp dụng nó để giải quyết một vấn đề khác.

Tìm bản ghi trùng lặp trong Excel 2007

Hãy chọn cột mà chúng tôi sẽ tìm kiếm các bản sao (trong ví dụ của chúng tôi, đây là cột có số danh mục) và trên tab chính, chúng tôi tìm kiếm nút “Định dạng có điều kiện”. Hơn nữa, từng điểm một, như trong hình.

Trong cửa sổ mới, tất cả những gì chúng ta phải làm là đồng ý với bảng màu được đề xuất (hoặc chọn một bảng màu khác) và nhấp vào “OK”.


Bây giờ các giá trị lặp lại của chúng ta có màu đỏ. Nhưng chúng nằm rải rác khắp bàn và điều này thật bất tiện. Bạn cần sắp xếp các dòng để ghép chúng lại với nhau. Xin lưu ý rằng trong bảng trên có cột “Mã hàng” chứa số dòng. Nếu chưa có thì bạn nên tạo một cái để sau này chúng ta khôi phục lại thứ tự ban đầu của dữ liệu trong bảng.
Chọn toàn bộ bảng, chuyển đến tab “Dữ liệu” và nhấp vào nút “Sắp xếp”. Trong cửa sổ mới chúng ta cần thiết lập thứ tự sắp xếp. Chúng tôi đặt các giá trị chúng tôi cần và thêm cấp độ tiếp theo. Chúng ta cần sắp xếp các hàng trước tiên theo màu ô và sau đó theo giá trị ô để các hàng trùng lặp nằm cạnh nhau.


Chúng tôi xử lý các bản sao được tìm thấy. Trong trường hợp này, các dòng trùng lặp có thể bị xóa.


Lưu ý rằng khi loại bỏ các ô trùng lặp, các ô màu đỏ sẽ trở lại màu trắng.
Sau khi loại bỏ các ô màu, hãy chọn lại toàn bộ bảng và sắp xếp nó theo cột “Số/n”. Sau đó, tất cả những gì còn lại là sửa lại số đã sai do dòng bị xóa.

Cách thực hiện việc này trong Excel 2003

Ở đây sẽ phức tạp hơn một chút - bạn sẽ phải sử dụng hàm logic “COUNTIF()”.
Nhập ô có giá trị đầu tiên, trong đó bạn sẽ tìm kiếm các giá trị trùng lặp.

• Định dạng.
• Định dạng có điều kiện.

Trong trường đầu tiên, chọn "Công thức" và nhập công thức "=COUNTIF(C;RC)>1". Chỉ cần đừng quên chuyển đổi bố cục kịp thời - “COUNTIF” được nhập trong bố cục tiếng Nga và “(C;RC)>1” được nhập trong bố cục tiếng Anh.


Chọn màu bằng cách nhấp vào nút “Định dạng” trên tab “Xem”.
Bây giờ chúng ta cần sao chép định dạng này vào toàn bộ cột.

• Biên tập.
• Sao chép.

Chọn toàn bộ cột có dữ liệu đang được kiểm tra.

• Biên tập.
• Chèn đặc biệt.

Chọn “Định dạng”, “OK” và định dạng có điều kiện sẽ được sao chép vào toàn bộ cột.
Chinh phục Excel và hẹn gặp lại!

Tìm bản sao trong Excel là một trong những nhiệm vụ phổ biến nhất đối với bất kỳ nhân viên văn phòng nào. Có một số cách khác nhau để giải quyết điều này. Nhưng bạn có thể tìm thấy các bản sao trong Excel và đánh dấu chúng bằng màu sắc nhanh như thế nào? Để trả lời câu hỏi thường gặp này, chúng ta hãy xem một ví dụ cụ thể.

Làm thế nào để tìm các giá trị trùng lặp trong Excel?

Giả sử chúng ta đang đăng ký các đơn đặt hàng mà công ty nhận được qua fax và e-mail. Một tình huống có thể phát sinh là cùng một đơn đặt hàng được nhận thông qua hai kênh thông tin đến. Nếu bạn đăng ký cùng một đơn hàng hai lần, một số vấn đề nhất định có thể phát sinh đối với công ty. Dưới đây chúng tôi sẽ xem xét một giải pháp sử dụng định dạng có điều kiện.

Để tránh trùng lặp đơn hàng, bạn có thể sử dụng định dạng có điều kiện để tìm nhanh các giá trị trùng lặp trong một cột Excel.

Ví dụ về nhật ký đơn hàng hàng ngày:

Để kiểm tra xem nhật ký đơn hàng có trùng lặp hay không, chúng tôi sẽ phân tích theo tên khách hàng - cột B:

Như bạn có thể thấy trong hình với định dạng có điều kiện, chúng tôi có thể triển khai tìm kiếm trùng lặp trong Excel một cách dễ dàng và nhanh chóng và phát hiện dữ liệu ô trùng lặp cho bảng lịch sử đơn hàng.

Ví dụ về hàm COUNTIF và đánh dấu các giá trị trùng lặp

Nguyên tắc của công thức tìm bản sao bằng định dạng có điều kiện rất đơn giản. Công thức chứa hàm =COUNTIF(). Hàm này cũng có thể được sử dụng khi tìm kiếm các giá trị giống nhau trong một phạm vi ô. Đối số đầu tiên trong hàm là phạm vi dữ liệu cần xem. Trong đối số thứ hai, chúng tôi chỉ ra những gì chúng tôi đang tìm kiếm. Đối số đầu tiên của chúng ta có tham chiếu tuyệt đối vì nó phải bất biến. Và đối số thứ hai, ngược lại, phải thay đổi thành địa chỉ của từng ô của phạm vi được xem, do đó nó có liên kết tương đối.

Cách nhanh nhất và dễ dàng nhất: tìm các bản sao trong ô.

Sau hàm có toán tử so sánh số giá trị tìm được trong một phạm vi với số 1. Tức là nếu có nhiều hơn một giá trị thì công thức trả về giá trị TRUE và định dạng có điều kiện được áp dụng cho ô hiện tại .

Nếu bạn làm việc với lượng lớn thông tin trong Excel và thường xuyên thêm nó, chẳng hạn như dữ liệu về học sinh hoặc nhân viên công ty, thì các giá trị trùng lặp có thể xuất hiện trong các bảng đó, hay nói cách khác là trùng lặp.

Trong bài viết này chúng ta sẽ xem xét cách tìm, chọn, xóa và đếm số giá trị trùng lặp trong Excel.

Cách tìm và đánh dấu

Bạn có thể tìm và đánh dấu các bản sao trong tài liệu bằng cách sử dụng định dạng có điều kiện trong Excel. Chọn toàn bộ phạm vi dữ liệu trong bảng mong muốn. Trên tab “Trang chủ”, nhấp vào nút "Định dạng có điều kiện", chọn từ trình đơn "Quy tắc đánh dấu ô" – "Giá trị trùng lặp".

Trong cửa sổ tiếp theo, chọn từ danh sách thả xuống "lặp đi lặp lại" và màu cho ô và văn bản để vẽ các bản sao được tìm thấy. Sau đó nhấp vào "OK" và chương trình sẽ tìm kiếm các bản sao.

Trong ví dụ, Excel đã đánh dấu tất cả thông tin tương tự bằng màu hồng. Như bạn có thể thấy, dữ liệu không được so sánh theo từng hàng mà các ô giống hệt nhau trong các cột sẽ được đánh dấu. Do đó, ô “Sasha V.” được tô sáng. . Có thể có một số học sinh như vậy nhưng có họ khác nhau.

Làm sao để đếm

Nếu bạn cần tìm và đếm số lượng giá trị lặp lại trong Excel, chúng tôi sẽ tạo bảng tổng hợp Excel cho việc này. Thêm cột “Mã” vào cột ban đầu và điền “1”: đặt 1, 1 vào hai ô đầu tiên, chọn chúng và kéo chúng xuống. Khi tìm thấy các hàng trùng lặp, giá trị trong cột Mã sẽ tăng lên một đơn vị mỗi lần.

Chọn mọi thứ cùng với các tiêu đề, chuyển đến tab “Chèn” và nhấn nút "Bảng xoay".

Để tìm hiểu thêm về cách làm việc với bảng trụ trong Excel, hãy đọc bài viết theo liên kết.

Trong cửa sổ tiếp theo, các ô của phạm vi đã được chỉ định; hãy sử dụng điểm đánh dấu để đánh dấu “sang trang tính mới” và nhấp vào “OK”.

Ở bên phải, kéo ba tiêu đề đầu tiên vào khu vực "Tiêu đề dòng" và kéo trường “Mã” vào khu vực “Giá trị”.

Kết quả là chúng ta sẽ có được một bảng tổng hợp không trùng lặp và trong trường “Mã” sẽ có các số tương ứng với các giá trị lặp lại trong bảng nguồn - số lần hàng này được lặp lại trong đó.

Để thuận tiện, hãy bôi đen tất cả các giá trị trong cột “Số tiền theo mã trường” và sắp xếp chúng theo thứ tự giảm dần.

Tôi nghĩ bây giờ bạn có thể tìm, chọn, xóa và thậm chí đếm số lượng bản sao trong Excel cho tất cả các hàng của bảng hoặc chỉ cho các cột đã chọn.

Có lẽ ai làm việc với dữ liệu trong Excel đều gặp phải câu hỏi làm sao để so sánh 2 cột trong Excel để tìm ra điểm giống và khác nhau. Có nhiều hướng khác nhau để làm điều đó. Chúng ta hãy xem xét kỹ hơn về từng người trong số họ.

Cách so sánh hai cột trong Excel theo hàng

Khi so sánh hai cột dữ liệu, thường cần phải so sánh dữ liệu ở từng hàng riêng lẻ để tìm sự trùng khớp hoặc khác biệt. Chúng ta có thể thực hiện phân tích như vậy bằng cách sử dụng hàm. Hãy xem cách thức hoạt động của nó bằng cách sử dụng các ví dụ bên dưới.

Ví dụ 1: Cách so sánh hai cột tìm sự trùng khớp và khác biệt trong cùng một hàng

Để so sánh dữ liệu trong mỗi hàng của hai cột trong Excel, chúng ta hãy viết một công thức đơn giản. Công thức phải được chèn vào từng hàng trong cột liền kề, bên cạnh bảng chứa dữ liệu chính. Sau khi tạo công thức cho hàng đầu tiên của bảng, chúng ta có thể kéo giãn/sao chép công thức sang các hàng còn lại.

Để kiểm tra xem hai cột của cùng một hàng có chứa cùng một dữ liệu hay không, chúng ta cần công thức:

=IF(A2=B2; “Trùng khớp”; “”)

Công thức xác định sự khác biệt giữa dữ liệu của hai cột trong một hàng sẽ như sau:

=IF(A2<>B2; "Không khớp"; “”)

Chúng ta có thể thực hiện kiểm tra sự trùng khớp và khác biệt giữa hai cột trên một dòng trong một công thức:

=IF(A2=B2; “Khớp”; “Không khớp”)

=IF(A2<>B2; "Không khớp"; "Phù hợp")

Một kết quả tính toán mẫu có thể trông như thế này:

Để so sánh dữ liệu trong hai cột của cùng một hàng theo cách phân biệt chữ hoa chữ thường, hãy sử dụng công thức:

=IF(MATCH(A2,B2), “Trùng khớp”, “Duy nhất”)

Cách so sánh nhiều cột cho kết quả trùng khớp trong một hàng Excel

Excel cho phép bạn so sánh dữ liệu ở nhiều cột của cùng một hàng bằng các tiêu chí sau:

• Tìm các hàng có cùng giá trị trong tất cả các cột của bảng;
• Tìm các hàng có cùng giá trị trong hai cột bất kỳ của bảng;

Ví dụ 1. Cách tìm kết quả khớp trong một hàng trong nhiều cột của bảng

Hãy tưởng tượng rằng bảng của chúng ta bao gồm một số cột dữ liệu. Nhiệm vụ của chúng ta là tìm những hàng có giá trị khớp với tất cả các cột. Excel và các hàm sẽ giúp chúng ta điều này. Công thức xác định trận đấu sẽ như sau:

=IF(AND(A2=B2,A2=C2); “Trùng hợp”; “ “)

Nếu bảng của chúng ta có nhiều cột thì việc sử dụng hàm kết hợp với:

=IF(COUNTIF($A2:$C2,$A2)=3;”Khớp”;” “)

Trong công thức, “5” cho biết số cột của bảng mà chúng ta đã tạo công thức. Nếu bảng của bạn có nhiều hoặc ít cột thì giá trị này phải bằng số cột.

Ví dụ 2: Cách tìm kết quả trùng khớp của cùng một hàng trong hai cột bất kỳ của bảng

Hãy tưởng tượng rằng nhiệm vụ của chúng ta là xác định từ một bảng có dữ liệu ở một số cột những hàng có dữ liệu giống nhau hoặc được lặp lại trong ít nhất hai cột. Các hàm và sẽ giúp chúng ta điều này. Hãy viết công thức cho một bảng gồm ba cột dữ liệu:

=IF(OR(A2=B2,B2=C2,A2=C2);”Trùng khớp”;” “)

Trong trường hợp có quá nhiều cột trong bảng của chúng ta, công thức có hàm của chúng ta sẽ rất lớn, vì trong các tham số của nó, chúng ta cần chỉ định tiêu chí khớp giữa mỗi cột của bảng. Trong trường hợp này, một cách dễ dàng hơn là sử dụng .

=IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Chuỗi duy nhất”; “Chuỗi không phải duy nhất”)

=IF(COUNTIF($B:$B,$A5)=0; “Không có kết quả trùng khớp ở cột B”; “Không có kết quả trùng khớp ở cột B”)

Công thức này kiểm tra các giá trị trong cột B để xem chúng có khớp với các ô trong cột A hay không.

Nếu bảng của bạn bao gồm một số hàng cố định, bạn có thể chỉ định một phạm vi rõ ràng trong công thức (ví dụ: $B2:$B10). Điều này sẽ tăng tốc công thức.

Cách so sánh hai cột trong Excel để tìm kết quả trùng khớp và đánh dấu chúng bằng màu

Khi tìm kiếm sự trùng khớp giữa hai cột trong Excel, chúng ta có thể cần trực quan hóa sự trùng khớp hoặc khác biệt được tìm thấy trong dữ liệu, chẳng hạn như sử dụng đánh dấu màu. Cách dễ nhất để làm nổi bật sự trùng khớp và khác biệt về màu sắc là sử dụng Định dạng có điều kiện trong Excel. Hãy xem cách thực hiện việc này bằng các ví dụ dưới đây.

Tìm và đánh dấu các kết quả khớp màu trong nhiều cột trong Excel

Trong trường hợp chúng ta cần tìm kết quả khớp trong một số cột, thì để làm được điều này, chúng ta cần:

• Chọn các cột có dữ liệu mà bạn muốn tính toán kết quả trùng khớp;
• Trên tab “Trang chủ” trên Thanh công cụ, nhấp vào mục menu “Định dạng có điều kiện” -> “Quy tắc đánh dấu ô” -> “Giá trị lặp lại”;
• Trong hộp thoại bật lên, chọn “Lặp lại” trong danh sách thả xuống bên trái và trong danh sách thả xuống bên phải, chọn giá trị lặp lại màu nào sẽ được đánh dấu. Nhấp vào nút “OK”:
• Sau đó, các kết quả trùng khớp sẽ được đánh dấu trong cột đã chọn:

Tìm và đánh dấu các hàng khớp trong Excel

Tìm các ô khớp với dữ liệu trong hai hoặc nhiều cột và tìm kiếm kết quả khớp của toàn bộ hàng có dữ liệu là các khái niệm khác nhau. Xin lưu ý hai bảng dưới đây:

Các bảng trên chứa cùng một dữ liệu. Sự khác biệt của chúng là trong ví dụ bên trái, chúng tôi đang tìm kiếm các ô phù hợp và ở bên phải, chúng tôi tìm thấy toàn bộ các dòng lặp lại có dữ liệu.

Hãy xem cách tìm các hàng khớp trong bảng:

• Ở bên phải của bảng có dữ liệu, chúng ta sẽ tạo một cột phụ, trong đó, đối diện với mỗi hàng có dữ liệu, chúng ta sẽ đặt một công thức kết hợp tất cả các giá trị của hàng trong bảng vào một ô:

=A2&B2&C2&D2

Ở cột phụ bạn sẽ thấy dữ liệu bảng tổng hợp:

Bây giờ, để xác định các hàng khớp trong bảng, hãy thực hiện các bước sau:

• Chọn vùng có dữ liệu ở cột phụ (trong ví dụ của chúng ta đây là dãy ô E2:E15 );
• Trên tab “Trang chủ” trên Thanh công cụ, nhấp vào mục menu “Định dạng có điều kiện” -> “Quy tắc đánh dấu ô” -> “Giá trị lặp lại”;
• Trong hộp thoại bật lên, chọn “Lặp lại” trong danh sách thả xuống bên trái và trong danh sách thả xuống bên phải, chọn màu mà các giá trị lặp lại sẽ được đánh dấu. Nhấp vào nút “OK”:
• Sau đó, các dòng trùng lặp sẽ được đánh dấu trong cột đã chọn: