Nội dung
Bài viết này bạn sẽ mất khoảng 10 phút để đọc. Trong 5 phút tiếp theo, bạn có thể dễ dàng so sánh hai cột trong Excel và tìm xem có trùng lặp trong chúng hay không, xóa chúng hoặc đánh dấu chúng bằng màu sắc. Vì vậy, thời gian đã đến!
Excel là một ứng dụng rất mạnh mẽ và thực sự thú vị để tạo và xử lý một lượng lớn dữ liệu. Nếu bạn có một số sổ làm việc có dữ liệu (hoặc chỉ một bảng lớn), thì bạn có thể muốn so sánh 2 cột, tìm các giá trị trùng lặp và sau đó làm điều gì đó với chúng, chẳng hạn như xóa, đánh dấu hoặc xóa nội dung. Các cột có thể nằm trong cùng một bảng, liền nhau hoặc không liền nhau, có thể nằm trên 2 trang tính khác nhau hoặc thậm chí trong các cuốn sách khác nhau.
Hãy tưởng tượng chúng ta có 2 cột với tên mọi người - 5 tên mỗi cột A và 3 tên trong một cột B. Bạn cần so sánh tên trong hai cột này và tìm các tên trùng lặp. Như bạn hiểu, đây là dữ liệu hư cấu, chỉ được lấy làm ví dụ. Trong các bảng thực, chúng ta đang xử lý hàng nghìn hoặc thậm chí hàng chục nghìn bản ghi.
Lựa chọn A: cả hai cột đều nằm trên cùng một trang tính. Ví dụ, một cột A và cột B.
Lựa chọn B: Các cột nằm trên các trang tính khác nhau. Ví dụ, một cột A trên trang tính Sheet2 và cột A trên trang tính Sheet3.
Excel 2013, 2010 và 2007 có một công cụ tích hợp Loại bỏ các bản sao (Loại bỏ các bản sao) nhưng nó bất lực trong tình huống này vì nó không thể so sánh dữ liệu trong 2 cột. Hơn nữa, nó chỉ có thể loại bỏ các bản sao. Không có tùy chọn nào khác như tô sáng hoặc thay đổi màu sắc. Và chỉ!
Tiếp theo, tôi sẽ chỉ cho bạn những cách khả thi để so sánh hai cột trong Excel, điều này sẽ cho phép bạn tìm và loại bỏ các bản ghi trùng lặp.
So sánh 2 cột trong Excel và tìm các mục nhập trùng lặp bằng công thức
Tùy chọn A: cả hai cột đều nằm trên cùng một trang tính
- Trong ô trống đầu tiên (trong ví dụ của chúng tôi, đây là ô C1), chúng tôi viết công thức sau:
=IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")
Trong công thức của chúng tôi A1 đây là ô đầu tiên của cột đầu tiên chúng ta sẽ so sánh. $ B $ 1 и $ B $ 10000 đây là địa chỉ của ô đầu tiên và ô cuối cùng của cột thứ hai, chúng tôi sẽ thực hiện phép so sánh. Lưu ý các tham chiếu tuyệt đối - các chữ cái cột và số hàng được đặt trước dấu đô la ($). Tôi sử dụng tham chiếu tuyệt đối để địa chỉ ô được giữ nguyên khi sao chép công thức.
Nếu bạn muốn tìm các bản sao trong một cột B, thay đổi các tham chiếu để công thức trông giống như sau:
=IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")Thay vì "Chỉ có"Và"Bản sao»Bạn có thể viết nhãn của riêng mình, ví dụ:«Không tìm thấy"Và"Tìm thấy", Hoặc chỉ để lại"Bản sao'và nhập một ký tự khoảng trắng thay vì giá trị thứ hai. Trong trường hợp thứ hai, các ô không tìm thấy bản sao sẽ vẫn trống và tôi tin rằng cách trình bày dữ liệu này thuận tiện nhất cho việc phân tích thêm.
- Bây giờ, hãy sao chép công thức của chúng ta vào tất cả các ô trong cột C, xuống hàng dưới cùng, chứa dữ liệu trong cột A. Để thực hiện việc này, hãy di chuyển con trỏ chuột đến góc dưới bên phải của ô C1, con trỏ sẽ có dạng hình chữ thập màu đen, như trong hình dưới đây:Nhấp và giữ nút chuột trái và kéo đường viền của khung xuống, đánh dấu tất cả các ô mà bạn muốn chèn công thức. Khi tất cả các ô bắt buộc được chọn, hãy thả nút chuột:
Nhấp và giữ nút chuột trái và kéo đường viền của khung xuống, đánh dấu tất cả các ô mà bạn muốn chèn công thức. Khi tất cả các ô bắt buộc được chọn, hãy thả nút chuột:
Mẹo: Trong các bảng lớn, việc sao chép công thức sẽ nhanh hơn nếu bạn sử dụng phím tắt. Đánh dấu một ô C1 và báo chí Ctrl + C (để sao chép công thức vào khay nhớ tạm), sau đó nhấp vào Ctrl + Shift + Kết thúc (để chọn tất cả các ô không trống trong cột C) và cuối cùng nhấn Ctrl + V (để chèn công thức vào tất cả các ô đã chọn).
- Tuyệt vời, bây giờ tất cả các giá trị trùng lặp đều được đánh dấu là “Bản sao":
Tùy chọn B: hai cột nằm trên các trang tính khác nhau (trong các sổ làm việc khác nhau)
- Trong ô đầu tiên của cột trống đầu tiên trên trang tính Sheet2 (trong trường hợp của chúng tôi là cột B) nhập công thức sau:
=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")Đây Sheet3 là tên của trang tính có cột thứ 2, và $ A $ 1: $ A $ 10000 là các địa chỉ ô từ đầu tiên đến cuối cùng trong cột thứ 1 này.
- Sao chép công thức vào tất cả các ô trong một cột B (giống như phương án A).
- Chúng tôi nhận được kết quả này:
Xử lý các bản sao được tìm thấy
Tuyệt vời, chúng tôi đã tìm thấy các mục nhập trong cột đầu tiên cũng có mặt trong cột thứ hai. Bây giờ chúng ta cần làm gì đó với chúng. Việc duyệt qua tất cả các bản ghi trùng lặp trong một bảng theo cách thủ công là khá kém hiệu quả và mất quá nhiều thời gian. Có nhiều cách tốt hơn.
Chỉ hiển thị các hàng trùng lặp trong cột A
Nếu các cột của bạn không có tiêu đề, thì bạn cần thêm chúng. Để thực hiện việc này, hãy đặt con trỏ vào số đại diện cho dòng đầu tiên và nó sẽ biến thành một mũi tên màu đen, như thể hiện trong hình bên dưới:
Nhấp chuột phải và chọn từ menu ngữ cảnh chèn (Chèn):
Đặt tên cho các cột, ví dụ: “Họ tên"Và"Bản sao?»Sau đó, mở tab Ngày (Dữ liệu) và nhấn Lọc (Lọc):
Sau đó, nhấp vào mũi tên nhỏ màu xám bên cạnh “Bản sao?«Để mở menu bộ lọc; bỏ chọn tất cả các mục trong danh sách này ngoại trừ Bản sao, và hãy nhấn OK.
Đó là tất cả, bây giờ bạn chỉ thấy những phần tử của cột А, được sao chép trong cột В. Chỉ có hai ô như vậy trong bảng đào tạo của chúng tôi, nhưng, như bạn hiểu, trong thực tế sẽ có nhiều ô khác trong số đó.
Để hiển thị lại tất cả các hàng của cột А, nhấp vào biểu tượng bộ lọc trong cột В, bây giờ trông giống như một cái phễu với một mũi tên nhỏ và chọn Chọn tất cả (Chọn tất cả). Hoặc bạn có thể làm tương tự thông qua Ribbon bằng cách nhấp vào Ngày (Dữ liệu)> Chọn & Lọc (Sắp xếp & Lọc)> Trong sáng (Rõ ràng) như được hiển thị trong ảnh chụp màn hình bên dưới:
Thay đổi màu hoặc đánh dấu các bản sao được tìm thấy
Nếu ghi chú “Bản sao”Là không đủ cho mục đích của bạn và bạn muốn đánh dấu các ô trùng lặp bằng màu phông chữ, màu tô khác hoặc một số phương pháp khác…
Trong trường hợp này, hãy lọc các ô trùng lặp như được hiển thị ở trên, chọn tất cả các ô đã lọc và nhấp vào Ctrl + 1để mở hộp thoại Format Cells (định dạng ô). Ví dụ, hãy thay đổi màu tô của các ô trong các hàng có các ô trùng lặp thành màu vàng sáng. Tất nhiên, bạn có thể thay đổi màu tô bằng công cụ Điền (Tô màu) tab Trang Chủ (Trang chủ) nhưng lợi thế của hộp thoại Format Cells (Định dạng ô) trong đó bạn có thể định cấu hình tất cả các tùy chọn định dạng cùng một lúc.
Bây giờ bạn chắc chắn sẽ không bỏ lỡ bất kỳ ô nào có trùng lặp:
Xóa các giá trị trùng lặp khỏi cột đầu tiên
Lọc bảng để chỉ các ô có giá trị trùng lặp được hiển thị và chọn các ô đó.
Nếu 2 cột bạn đang so sánh nằm trên các trang tính khác nhaunghĩa là, trong các bảng khác nhau, hãy nhấp chuột phải vào phạm vi đã chọn và chọn Xóa hàng (Xóa dòng):
Ấn Bản OKkhi Excel yêu cầu bạn xác nhận rằng bạn thực sự muốn xóa toàn bộ hàng trang tính và sau đó xóa bộ lọc. Như bạn có thể thấy, chỉ các hàng có giá trị duy nhất còn lại:
Nếu 2 cột nằm trên cùng một trang tính, gần nhau (liền kề) hoặc không gần nhau (không liền kề), thì quá trình loại bỏ các bản sao sẽ phức tạp hơn một chút. Chúng tôi không thể xóa toàn bộ hàng có giá trị trùng lặp, vì điều này cũng sẽ xóa các ô khỏi cột thứ hai. Vì vậy, chỉ để lại các mục nhập duy nhất trong một cột А, làm cái này:
- Lọc bảng để chỉ hiển thị các giá trị trùng lặp và chọn các ô đó. Nhấp chuột phải vào chúng và chọn từ menu ngữ cảnh Xóa nội dung (xóa nội dung).
- Làm sạch bộ lọc.
- Chọn tất cả các ô trong một cột А, bắt đầu từ ô A1 tất cả các cách xuống dưới cùng chứa dữ liệu.
- Nhấn vào Ngày (Dữ liệu) và nhấn Sắp xếp A đến Z (Sắp xếp từ A đến Z). Trong hộp thoại mở ra, hãy chọn Tiếp tục với lựa chọn hiện tại (Sắp xếp trong vùng lựa chọn được chỉ định) và nhấp vào nút Đen (Sắp xếp):
- Xóa cột có công thức, bạn sẽ không cần nó nữa, từ giờ bạn chỉ có các giá trị duy nhất.
- Vậy là xong, bây giờ là chuyên mục А chỉ chứa dữ liệu duy nhất không có trong cột В:
Như bạn có thể thấy, việc loại bỏ các bản sao từ hai cột trong Excel bằng công thức không khó lắm.