Cách so sánh nhị cột dữ liệu trong Excel để xóa, highlight, sơn màu dữ liệu trùng lặp là một trong những câu hỏi vẫn được rất nhiều bạn thân thương cũng như gửi câu hỏi về đến chúng tôi. Với bài viết này, Blog học excel cơ bản online đang hướng dẫn các bạn cách so sánh hai cột Excel trùng lặp cùng xóa hoặc highlight những phần đụng hàng được kiếm tìm thấy.
Bạn đang xem: So sánh giá trị 2 cột trong excel
Excel là 1 trong những ứng dụng rất bạo dạn và thực sự tuyệt đối để tạo thành và xử lý những mảng tài liệu lớn. Bây giờ, bạn có tương đối nhiều bảng tính và dữ liệu, hoặc có thể chỉ là một trong bảng tính siêu lớn, bạn muốn so sánh 2 cột để tìm các giá trị trùng gắn và thao tác làm việc trên đa số phần lặp được tìm kiếm thấy, ví dụ xóa hàng bị trùng, tô color phần trùng hoặc xóa câu chữ của ô bị trùng. Nhì cột này có thể nằm vào một bảng, liền kề hoặc ko liền kề, hoặc chúng có thể nằm trong 2 trang tính không giống nhau, thậm chí là là nhì bảng tính không giống nhau.
Giả sử chúng ta có 2 cột tên fan – 5 tên trong cột A và 3 thương hiệu trong cột B. Bạn muốn so sánh tài liệu giữa nhị cột này để tìm các tên bị trùng lắp. Đây chỉ cần ví dụ. Trong các bảng tính thực, tài liệu để đối chiếu thường gồm hàng ngàn, hàng chục ngàn mục.
Trường hợp A: Cả nhì cột phần nhiều trong một trang tính, cùng một bảng dữ liệu: Cột A và cột B.
Trường thích hợp B: hai cột được đặt lên trên hai trang tính không giống nhau: cột A vào Sheet2 và cột A trong Sheet3.
Xem nhanh
So sánh hai cột nhằm tìm quý giá trùng bằng công thức:
Trường hợp 1: cả nhì cột và một danh sách trong ô trống đầu tiên, ô C1, nhập công thức: =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),”Unique”,”Duplicate”)
Trong công thức, A1 là ô trước tiên của cột trước tiên mà họ muốn thực hiện để so sánh. $B$1 và $B$10000 là showroom của ô đầu tiên và ở đầu cuối của cột thứ hai mà bạn muốn so sánh. Hãy chú ý cố định tham chiếu ô – ký hiệu đô la ($) trước những ký trường đoản cú cột và số hàng. Tôi thực hiện tham chiếu tuyệt vời nhất nhằm duy trì nguyên địa chỉ cửa hàng ô khi coppy công thức.
Nếu bạn muốn tìm các giá trị trùng trong cột B, hoán thay tên cột như sau: =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),”Unique”,”Duplicate”)
Thay bởi “Unique”/”Duplicate” (duy nhất/trùng lắp), chúng ta cũng có thể tự đặt nhãn cho khách hàng như “Not found”/”Found” (Tìm thấy/Không kiếm tìm thấy) hoặc “Duplicate” và gõ “” thay vị “Unique”. Trong biện pháp thứ hai, các bạn sẽ có một ô trống xung quanh ô nhưng mà giá trị trùng đính không được tìm kiếm thấy.
bây chừ hãy xào nấu công thức vào toàn bộ các ô của cột C cho tới hàng cuối cùng có chứa tài liệu trong cột A. Để làm cho điều này, đặt bé trỏ vào góc dưới bên đề xuất của ô С1, con trỏ sẽ chuyển thành một dấu thập phân black như vào hình dưới đây:
Nhấn giữ chuột trái, kéo mặt đường viền ô xuống nhằm chọn toàn bộ các ô mà bạn muốn sao chép phương pháp vào. Khi toàn bộ các ô đã được chọn, thả con chuột trái:
Chú ý. Trong bảng lớn, có một cách nhanh rộng để coppy công thức là áp dụng phím tắt. Nhấp vào ô C1 để chọn nó cùng nhấn Ctrl + C (để xào luộc công thức vào clipboard), tiếp nối nhấn Ctrl + Shift + end (để chọn toàn bộ những ô chứa dữ liệu trong cột C), cuối cùng, nhấn Ctrl + V (để dán phương pháp vào tất cả ô tính sẽ chọn).
toàn bộ các ô chứa giá trị trùng phần nhiều được ghi lại là “Duplicate”.
Trường vừa lòng B: nhì cột nằm trên hai trang tính (bảng tính) khác nhau.
trong ô trước tiên của cột trống thứ nhất trong Sheet2 (cột B), nhập công thức: =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),””,”Duplicate”)Sheet3! là tên trang tính gồm chứa cột lắp thêm 2, $A$1:$A$10000 là địa chỉ của ô tính đầu tiên và ô tính sau cùng trong cột thứ 2.
giống như như cách trong trường hợp A. Chúng ta có tác dụng sau đây:
Xử lý những giá trị trùng đính được search thấy:
Chúng ta vẫn tìm thấy phần lớn giá trị trong cột thứ nhất (cột A) cũng trùng với cột lắp thêm hai (cột B). Hiện nay chúng ta yêu cầu làm vài trang bị với chúng.
Thật không kết quả và tốn thời hạn khi search kiếm trên toàn thể bảng và xem xét từng cực hiếm trùng gắn một biện pháp thủ công. Có rất nhiều cách thực hiện ưu việt hơn.
Chỉ hiển thị hàng bị trùng vào cột A:Nếu cột của doanh nghiệp không tất cả tiêu đề, bạn phải thêm vào cho chúng. Để triển khai điều này, đặt con trỏ loài chuột vào số lượng thứ từ bỏ đầu sản phẩm (số 1), con chuột đã biển đổi thành mũi thương hiệu màu black như trong hình dưới đây:
Nhấp yêu cầu vào hàng được lựa chọn và chọn Insert từ list tùy chọn:
Đặt tên đến cột, ví dụ “Name”, “Duplicate?”… kế tiếp mở thẻ Data và nhấp chọn Filter:
Sau khi bấm vào vào mũi tên bé dại màu xám quanh đó “Duplicate?” nhằm mở danh sách tùy chọn, vứt chọn tất cả các mục quanh đó Duplicate trong list đó, nhận OK:
Như vậy, bạn chỉ thấy phần đông ô tính vào cột A tất cả chứa giá trị trùng đính với cột B. Bao gồm 3 ô tính vì thế trong trang tính ví dụ.
Để hiển thị tất cả các sản phẩm trong cột A lần nữa, nhấp vào biểu tượng filter trong cột B và lựa chọn Select All. Hoặc, chúng ta cũng có thể vào thẻ Data > Select & Filter > Clear như trong hình dưới đây:
Tô color hoặc highlight rất nhiều giá trị trùng kiếm tìm thấy:
Ngoài đính nhãn “Duplicate”, chúng ta cũng có thể đánh dấu các giá trị trùng bằng một cách khác ví như định dạng màu chữ hoặc bôi color nó.Lọc những giá trị giống nhau như phân tích và lý giải ở trên, chọn các ô tính đang lọc và nhấn Ctrl + F1 để mở hộp thoại Format Cells. Ví dụ: hãy biến hóa màu nền của những hàng bị trùng quý phái màu rubi tươi. Vớ nhiên, chúng ta có thể thay thay đổi màu sắc nền của các ô này bằng cách sử dụng tùy chọn Fill màu sắc trên thẻ Home, tuy nhiên ưu núm của vỏ hộp thoại Format Cells là nó cho phép bạn thực hiện toàn bộ các thay đổi định dạng cùng lúc.
Bây giờ, các bạn sẽ không quăng quật sót ngẫu nhiên ô cất giá trị trùng nào.
Loại vứt giá trị đụng hàng từ cột đầu tiên:
Lọc bảng dữ liệu để hiển thị các giá trị trùng lặp, chọn toàn bộ những ô đó.
Nếu nhị cột được đối chiếu nằm trên nhì trang tính không giống nhau, cụ thể là trong nhì bảng dữ liệu riêng biệt, nhấp yêu cầu vào vùng dữ liệu được chọn và lựa chọn Delete Row từ list tùy chọn.
Khi Excel yêu mong bạn chứng thực bạn gồm thật sự ước ao xóa toàn thể hàng này không, nhấn OK với bỏ bộ lọc. Như vậy, chỉ đông đảo hàng chứa các giá trị duy nhất được hiển thị:
Nếu nhị cột ở trên cùng một trang tính, hai cột liền kề hoặc không liền kề, vấn đề xóa đa số giá trị trùng đính thêm sẽ phức tạp hơn. Chúng ta không thể xóa tổng thể hàng đựng chúng cũng chính vì như vậy đã xóa đa số ô tương xứng trong cột 2. Vì vậy, nhằm hiển thị gần như giá trị độc nhất vô nhị trong cột A, thực hiện theo các bước sau:
lọc bảng để những giá trị trùng lặp được hiển thị với chọn toàn bộ những ô đó. Nhấp yêu cầu vào vùng được chọn và lựa chọn Clear Contents.
Excelcông cầm thông minh độc nhất vô nhị mà nhân viên văn phòng thường tuyệt gọi, nguyên nhân lại vậy, bởi vì sự logic và các tính năng chăm nghiệp, cung cấp người dùng từ việc khó tốt nhất đến đơn giản và dễ dàng tùy theo yêu mong công việc. Bình thường khi bạn có nhu cầu so sánh gì đó bạn hay so sánh bằng tay bằng đôi mắt thường, nhưng số lượng phải so sánh vô số và unique so sánh phải chi tiết nhất thì 1 trong những tính năng của Excel sẽ giúp bạn. Nhưng mà cáchso sánh 2 cột trong Excelthế làm sao để chuẩn chỉnh nhất thì bạn hãy tham khảo dưới đây.
1. Khi nào cần so sánh dữ liệu 2 cột
- Khi nói đến việc đối chiếu thì chắc hẳn rằng là yêu cầu có ít nhất là 2 đối tượng để đối chiếu, điều cơ bản của việc đối chiếu là tìm ra sự giống và khác nhau.
- Trong quy trình học Excel thì việc so sánh dữ liệu 2 cột trong Excelphải đưa ra sự giống và khác cho mục tiêu của các bước cần làm.
2. Cách đối chiếu dữ liệu 2 cột trong excel
- Excel cung cấp tính năng so sánh dữ liệu 2 cột bằng vô số phương pháp khác nhau, nhưng chuẩn chỉnh nhất và dễ ợt nhất cho tất cả những người sử dụng bởi hàm COUNTIF. Đây là hàm những thống kê được áp dụng nhiều duy nhất khi thực hiện thủ thuật đối chiếu giá trị 2cộttrong Excel.
2.1. áp dụng hàm COUNTIF nhằm sosánh dữ liệu 2 cột
- Hàm COUNTIFđược dùng để làm đếm những ô thỏa mãn nhu cầu những đk nào đó được đưa ra trong phạm vi điều kiện khoanh vùng. Khi thực hiện so sánh nhị cột dữ liệu sẽ cần sử dụng hàmso sánh 2 cột vào Excel.
- Đây cũng là một trong trong số những công thức hàm cơ phiên bản của khối hệ thống hàm bên trên trang tính Excel, với tác dụng đếm ô theo yêu cầu trong phạm vi nhất định đã được định ra sẵn, ngoài ra được dùng với công dụng chủ yếu hèn là để đối chiếu dữ liệu trùng 2 cột trong excel để tìm ra sự tương đương giống và khác nhau.
- Ưu điểm của hàm COUNTIF: Hàm này sẽ không phân biệt chữ hoa, chữ thường, so sánh được 2 cột tài liệu chính xác.
- điểm yếu củahàm COUNTIF: quá trình thực hiện nay phức tạp.
- Cú pháp của hàm:=COUNTIF(Range;Criteria)
Trong đó:
+ Range:Vùng chứa dữ liệu cần đếm (tham số bắt buộc).
+ Criteria:Điều kiện dùng để làm đếm tài liệu (tham số bắt buộc).
- Cách tiến hành như sau:
- bước 1:Truy cập trang tính Excel cùng mở 2 dữ liệu cần so sánh
Truy cập trang tính Excel và mở 2 dữ liệu cần so sánh
- cách 2:Chọn bảng hàng cột trước tiên khoanh vùng chọn > biến đổi tên bất kỳ viết liền không dấu, rồi Enter, tương tự với cột thứ hai cũng vậy.
Cách đối chiếu 2 cột tài liệu trong excel
- bước 3:Thao tác tiếp sau ở giữa không gian của hàng so sánh đầu tiên nhập theo bí quyết hàm =COUNTIF(list2,A5), sau đó Enter và thực hiện thao tác ban đầu so sánh theo bí quyết từ hàng đầu tiên.
Cách đối chiếu 2 cột dữ liệu trong excel
Như vậy sau khoản thời gian thực hiện so sánh cho ra kết quả:
Cách so sánh 2 cột dữ liệu trong excel
- bước 4:Vào phần name box để chọn theo kí hiệu tam giác lựa chọn list1 > vào mục Styles > chọn Conditional Formatting > lựa chọn New ruler
Cách đối chiếu 2 cột tài liệu trong excel
- hiện nay lên cửa ngõ sổ New formatting Rule, click vào dòng xoáy cuối cùng
Cách so sánh 2 cột dữ liệu trong excel
- bước 5:Tab mới mở ra, nhập công thức của hàm COUNTIF > Format > lựa chọn trong bảng màu sắc một màu sắc > Formatđể hoàn thành.
.=> bạn sẽ thấy được xuất hiện trong bảng tính ở cột 1 những những quý hiếm được trét theo màu bạn chọn, đó là các giá trị không có ở vào cột 2.
Cách đối chiếu 2 cột tài liệu trong excel
Thực hiện làm việc tương trường đoản cú với cột 2, trên screen sẽ hiện phần lớn giá trị ko được giống nhau trên trang tính là đều giá trị được bôi màu sắc trong cả hai cột, bởi thế việc điều tra và kiểm tra đối chiếu dữ liệu 2 cột Excel của người sử dụng đã hoàn thành.
2.2. áp dụng hàm EXACT để đối chiếu dữ liệu 2 cột vào Excel
- Hàm EXACT để đối chiếu hai cột trong
Excel.
- Ưu điểm của hàm EXACT: Dễ sử dụng dành cho người mới xúc tiếp và có tác dụng quen với Excel.
- Nhượcđiểm của hàm EXACT: Hàm này biệt lập chữ hoa và chữ thường, trong khi hàm này chỉ so sánh được dữ liệu trên một hàng ngang.
- Cú pháp hàm:=EXACT(Text1;Text2)
Trong đó:
- Text1:Dữ liệu lắp thêm 1 yêu cầu so sánh.
- Text2:Dữ liệu thứ hai cần so sánh.
- Cách triển khai như sau:
- cách 1: Quan gần kề ví dụ sau và đối chiếu 2 cột như hình.
Hàm EXACT để so sánh dữ liệu - Hình 1
- cách 2: Nhập công thức=Exact($A$2:$A$7;$B$2:$B$7) vào ô buộc phải so sánh.
Trong đó:
- $A$2:$A$7:Dữ liệu của cột 1 yêu cầu so sánh
- $B$2:$B$7:Dữ liệu của cột 2 đề xuất so sánh.
Hàm EXACT để so sánh dữ liệu - Hình 2
- cách 3: Nhấn Enter để nhấn kết quả: True - giống như nhau, False - không giống nhau.
Hàm EXACT để đối chiếu dữ liệu - Hình 3
- bước 4: thừa nhận giữ ô vuông ỏ góc nhỏ dại ô vừa bắt đầu nhập công thức. Chúng ta kéo xuống nhằm nhận kết quả ở phần lớn ô còn lại.
Hàm EXACT để đối chiếu dữ liệu - Hình 4
2.3 thực hiện hàm VLOOKUP nhằm so sáng quý giá 2 cột
Việc sử dụng hàm
VLOOKUPso sánh 2 cột vào Excel nhằm timg ra các giá trị trùng nhau mau lẹ và công dụng nhất.
Công thức:
=VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup)
Trong đó:
- Lookup_value: giá trị cần dò tìm
- Table_array: Bảng số lượng giới hạn dò tìm.
- Col_index_num: Số sản phẩm tự từ trái qua buộc phải của cột lấy tài liệu trong bảng đề xuất dò tìm
- Range_lookup: tìm kiếm kiếm chính xác hay kiếm tìm kiếm tương so với bảng giới hạn
- Range_lookup = 1 (TRUE): dò search tương đối.
- Range_lookup = 0 (FALSE): dò tìm bao gồm xác.
Bạn có thể xem cách thực hiện công thức qua ví dụ sau: Tìm giá trị trùng nhau ngơi nghỉ cột 1 so với cột 2
=VLOOKUP(=VLOOKUP(E4;$C$4:$C$8;1;0)
Hàm Vlookup
Những ô có kết quả trùng lặp sẽ lộ diện giá trị trùng lặp, còn còn nếu như không sẽ có hình tượng lỗi #N/A.
Trên đó là những thao tác đơn giản và về tối ưu tuyệt nhất để thực hiện so sánh dữ liệu 2 cột trong Excel, bạn đọc hãy vừa mày mò vừa thực hành thực tế và áp dụng ngay để nhanh chóngtối ưu thời gian thao tác của mình.
Xem thêm:
Để nâng cấp kiến thức cũngkỹ năng thực hànhmời các bạn đọc tham khảo các khóatin học tập văn phòng trên trang emcanbaove.edu.vn của công ty chúng tôi nhé.