Hướng dẫn so sánh hai cột trong Excel 1 cách thông minh và lấy các kết quả phù hợp hoặc khác biệt, đánh dấu chúng hoặc thậm chí kéo dữ liệu.
Khi bạn đang làm việc với dữ liệu trong Excel, bạn có thể cần so sánh các cột để tìm ra điểm giống và khác nhau giữa các dữ liệu. So sánh các cột rất hữu ích cho việc tổ chức và phân tích dữ liệu. So sánh thủ công dữ liệu từ hai cột có thể là một công việc tốn thời gian và mệt mỏi, vì vậy bạn có thể sử dụng các công thức Excel khác nhau để khớp các cột.
Excel có một số phương pháp và hàm để so sánh các cột và tìm dữ liệu khớp và không khớp. Bạn có thể sử dụng các toán tử logic – VLOOKUP, MATCH, AND, INDEX, IF, COUNTIF, ISERROR, IFERROR – hoặc các quy tắc Format có điều kiện để so sánh và khớp dữ liệu. Trong bài viết này, chúng tôi sẽ thảo luận về các phương pháp khác nhau để so sánh các cột trong Excel cho phù hợp và khác biệt.
So sánh hai cột theo hàng cho phù hợp hoặc khác biệt
Cách đơn giản nhất để so sánh hai cột trong Excel là so sánh từng hàng, từng dòng đơn giản. Phương pháp này kiểm tra xem giá trị trong một cột có khớp với giá trị trong một cột khác trong cùng một hàng hay không. Nó sẽ chỉ so sánh các giá trị trong cùng một hàng, không phải toàn bộ tập dữ liệu. Có nhiều loại công thức khác nhau mà bạn có thể sử dụng để so sánh hai cột theo từng hàng – sử dụng toán tử so sánh đơn giản, hàm IF và hàm EXACT.
So sánh các cột bằng toán tử bằng
Cách dễ nhất để so sánh từng hàng dữ liệu của hai cột để tìm kết quả khớp là sử dụng toán tử so sánh. Với ‘Equals to’ (=), bạn có thể so sánh các ô trong hai cột để khớp và nhận kết quả là true hoặc false.
Ví dụ 1:
Ví dụ: chúng tôi sẽ so sánh hai cột (Bill Due và Bill Paid trong ảnh chụp màn hình bên dưới) để xem chúng có khớp nhau hay không. Để làm điều đó, chúng tôi sẽ sử dụng công thức đơn giản dưới đây:
=B2=C2Giá trị trong B2 khớp với giá trị trong C2, do đó, công thức trả về TRUE. Đầu tiên, hãy nhập công thức vào ô D2 và sau đó sao chép công thức xuống các ô khác bằng cách kéo chốt điền để so sánh các cột B và C theo từng hàng. Ô điều khiển điền là một hình vuông nhỏ màu xanh lá cây ở góc dưới cùng bên phải của ô đã chọn.
Khi bạn kéo chốt điền từ ô D2 đến D12, con trỏ sẽ thay đổi thành một dấu cộng màu đen.
Khi bạn áp dụng công thức qua các ô D2 đến D12, nó sẽ so sánh các giá trị theo từng hàng và bạn sẽ thấy một số hàng khớp trong khi những hàng khác thì không. Ví dụ: giá trị từ ô B4 không khớp với ô C4 liền kề của nó, do đó giá trị trong ô D4 là FALSE.
Ví dụ 2:
Chúng ta đã thấy cách công thức trên xử lý số, nhưng nó cũng có thể so sánh ngày, giờ và chuỗi văn bản tốt như nhau. Hãy để chúng tôi xem cách công thức so sánh các cột với các giá trị văn bản.
=A2=B2Công thức tìm kiếm kết quả khớp chính xác giữa hai cột và sẽ không bỏ sót dù chỉ một ký tự khoảng trắng. Sau đó, nó trả về TRUE nếu điều kiện được đáp ứng hoặc nếu không trả về FALSE. Địa chỉ thanh toán trong ô A2 khớp với địa chỉ giao hàng trong ô B2, kết quả là chúng tôi nhận được TRUE. Ngoài ra, địa chỉ trong ô A5 không khớp với địa chỉ trong ô B5 – ký tự cuối cùng khác trong ô B5. Do đó, nó trả về FALSE.
So sánh các cột bằng cách sử dụng hàm IF
Một cách khác để chúng ta có thể so sánh hai cột theo từng hàng là sử dụng hàm IF. Hàm IF kiểm tra xem một điều kiện hoặc tiêu chí có được đáp ứng hay không và trả về một giá trị được chỉ định nếu điều kiện là TRUE hoặc một giá trị khác nếu điều kiện là FALSE. Mặc dù phương pháp này tương tự như phương pháp trên, nhưng chúng ta có thể sử dụng nó để nhận được nhiều kết quả mô tả hơn là chỉ TRUE hoặc FALSE.
Ví dụ: chúng tôi có thể sử dụng công thức dưới đây để so sánh hai cột và nếu có sự trùng khớp, chúng tôi có thể nhận được kết quả “Paid” hoặc “Not Paid” nếu không có kết quả phù hợp:
=IF(B2=C2,"Paid","Not Paid")Trong công thức trên, hàm IF kiểm tra xem giá trị trong B2 có bằng giá trị trong C2 hay không và nếu điều kiện là Đúng, hàm IF sẽ trả về văn bản “Paid”. Nếu điều kiện là Sai, nó sẽ trả về “Không phải trả tiền”. Số tiền đến hạn của Hóa đơn ở B2 và Số tiền đã trả của Hóa đơn ở C2 là giống nhau, vì vậy nó trả về “Đã thanh toán” ở D2. Nhưng số tiền trong B5 và C5 không khớp, vì vậy công thức trả về “Không phải trả tiền” trong D5.
Chỉ dành cho Đối sánh:
Trong trường hợp bạn chỉ muốn tìm các kết quả phù hợp trong hai cột, bạn có thể sử dụng công thức dưới đây:
=IF(B2=C2,"Paid","")Công thức trên kiểm tra xem giá trị trong cột B có bằng các giá trị trong cột C, theo từng hàng. Nếu điều kiện là đúng, chúng ta sẽ nhận được chuỗi văn bản “Trả phí” và nếu điều kiện là sai, chúng ta sẽ không nhận được gì (chuỗi trống).
Chỉ dành cho sự khác biệt:
Để tìm các ô có các giá trị khác nhau trong cùng một hàng, hãy thử công thức dưới đây:
=IF(B4<>C4,"Not Paid","")Công thức trên kiểm tra nếu các giá trị trong cột B không bằng các giá trị trong cột C, theo từng hàng. Nếu điều kiện là đúng, chúng ta sẽ nhận được chuỗi văn bản “Không phải trả phí” và nếu điều kiện là sai, chúng ta sẽ không nhận được gì (chuỗi trống).
Lưu ý: Các công thức bằng với công thức và hàm IF không phân biệt chữ hoa chữ thường, có nghĩa là chúng sẽ bỏ qua các trường hợp khi so sánh các giá trị văn bản.
So sánh hai cột để đối sánh phân biệt chữ hoa chữ thường trong cùng một hàng bằng cách sử dụng hàm EXACT
Các công thức trên bỏ qua các trường hợp khi so sánh các giá trị văn bản. Nếu bạn muốn so sánh phân biệt chữ hoa chữ thường, bạn cần sử dụng hàm EXACT. Hàm EXACT trong Excel được sử dụng để so sánh hai chuỗi văn bản và trả về TRUE nếu cả hai giá trị đều giống nhau và FALSE nếu ngược lại. Bạn có thể sử dụng EXACT một mình hoặc với hàm IF (trong trường hợp bạn muốn nhận kết quả mô tả thay vì chỉ TRUE hoặc FALSE.
Ví dụ: hãy so sánh danh sách tên công ty từ các cơ sở dữ liệu khác nhau và xem chúng có khớp chính xác hay không bằng cách sử dụng hàm EXACT đơn giản:
=EXACT(A2,B2)Công thức trên kiểm tra xem các chuỗi văn bản từ A2 và B2 có phải là một đối sánh chính xác có phân biệt chữ hoa chữ thường hay không. Sau đó, nó trả về FALSE vì từ “St” trong A2 là chữ thường trong khi ở B2, nó được viết hoa.
Trong trường hợp bạn muốn nhận kết quả mô tả, bạn cần sử dụng hàm IF với hàm EXACT:
=IF(EXACT(A3,B3),"Match", "Check Database")Trong công thức trên, hàm EXACT kiểm tra xem các giá trị trong ô A3 và B3 có phải là các kết quả khớp chính xác có phân biệt chữ hoa chữ thường hay không. Tuy nhiên, từ đầu tiên ‘ANGELO’ được viết hoa bằng B2 khác với tên công ty trong A2, do đó, hàm EXACT trả về FALSE. Do đó, hàm IF trả về chuỗi văn bản “Check Database” cho đầu ra FALSE.
Trong hàng 5, các giá trị ô A5 và B5 là các kết quả khớp phân biệt chữ hoa chữ thường, vì vậy hàm IF nhận kết quả TRUE từ hàm EXACT và trả về “MATCH” ở vị trí của nó.
So sánh hai cột nếu lớn hơn hoặc nhỏ hơn
Đôi khi, bạn có thể muốn so sánh các cột và kiểm tra xem giá trị trong một cột lớn hơn hay nhỏ hơn các cột khác. Ví dụ: nếu bạn có hai cột ngày tháng và bạn muốn so sánh ngày nào sau đó nằm trong cùng một hàng (có lẽ để so sánh ngày hết hạn của sản phẩm), bạn có thể sử dụng một phép toán logic đơn giản để tìm hiểu.
Để biết sản phẩm đã hết hạn hay chưa, hãy so sánh hai cột nếu cột C lớn hơn cột B:
=IF(C2>B2,"Yes","No")Công thức trên kiểm tra xem giá trị trong ô C2 có lớn hơn ô B2 hay không. Nếu nó là TRUE, thì hàm IF trả về giá trị “Có”, ngược lại là “Không”.
So sánh nhiều cột theo hàng cho phù hợp
Chúng tôi đã thấy cách so sánh hai cột theo từng hàng nhưng bạn cũng có thể so sánh nhiều cột cho các kết quả phù hợp trong cùng một hàng. Có hai cách để bạn có thể so sánh nhiều cột – tìm kết quả phù hợp trong tất cả các ô trong cùng một hàng hoặc tìm kết quả phù hợp trong hai ô bất kỳ trong cùng một hàng.
Tìm các kết quả phù hợp trong tất cả các ô trong cùng một hàng
Phương pháp 1: Nếu bạn có tập dữ liệu có nhiều hơn hai cột (nhiều cột) và bạn muốn tìm các hàng có cùng giá trị trong tất cả các cột. Bạn có thể thực hiện việc này với các hàm IF và AND:
=IF(AND(A3=B3,A3=C3),"All Match","")Hàm AND kiểm tra nhiều điều kiện cùng một lúc (A3 = B3 và A3 = C3) và chỉ trả về TRUE nếu tất cả các đối số của nó đánh giá là TRUE. Hàm AND sẽ trả về FALSE ngay cả khi một trong các đối số đánh giá là FALSE. Bạn có thể thêm nhiều điều kiện trong hàm AND bằng cách thêm dấu phẩy vào giữa mỗi điều kiện.
Như bạn có thể thấy bên dưới, hàm AND sẽ trả về true nếu tất cả các ô có cùng giá trị trong cùng một hàng. Sau đó, hàm IF sẽ trả về văn bản “All Match” nếu hàm AND trả về TRUE.
Phương pháp 2: Nếu tập dữ liệu của bạn có nhiều cột, bạn có thể sử dụng hàm COUNTIF để thu gọn công thức của mình:
=IF(COUNTIF($A3:$D3, $A3)=4, "All match", "")Trong đó 4 đại diện cho số cột bạn đang so sánh trong công thức. Hàm COUNTIF được sử dụng để đếm các số đáp ứng một tiêu chí cụ thể.
Công thức COUNTIF kiểm tra xem hàng có các giá trị giống nhau trong tất cả các ô (A3: D3) hay không và trả về tổng số kết quả phù hợp. Và nếu tất cả các cột khớp trong cùng một hàng (kết quả của hàm COUNTIF) bằng số cột, bạn sẽ nhận được chuỗi văn bản có nội dung “Tất cả đều khớp”.
Tìm các kết quả phù hợp trong bất kỳ hai ô nào trong cùng một hàng
Phương pháp 1: Giả sử bạn có nhiều (3) cột và bạn muốn tìm các kết quả phù hợp trong bất kỳ cột nào trong số hai cột trong cùng một hàng, bạn có thể thực hiện điều đó với sự trợ giúp của các hàm IF và OR. Để làm điều này, chúng ta có thể sử dụng công thức dưới đây:
=IF(OR(A3=B3, B3=C3, A3=C3), "Match", "")Trong công thức trên, hàm OR so sánh từng cột với các cột khác và nếu bất kỳ cột nào trong số hai hoặc nhiều cột có cùng giá trị khớp trong cùng một hàng, nó sẽ trả về TRUE. Hàm IF sẽ trả về văn bản ‘Khớp’ khi nó nhận được TRUE từ hàm OR.
Phương pháp 2: Nếu bạn có quá nhiều cột để so sánh, công thức OR ở trên có thể quá lớn và phức tạp. Để tránh điều này, bạn có thể thêm một số hàm COUNTIF:
=IF(COUNTIF(B3:D3,A3)+COUNTIF(C3:D3,B3)+(C3=D3)=0,"Unique","Match")Ở đây, hàm COUNTIF đầu tiên kiểm tra và đếm có bao nhiêu ô (cột) có cùng giá trị với cột đầu tiên (A3) và hàm COUNTIF thứ hai kiểm tra xem có bao nhiêu cột có cùng giá trị với cột thứ hai, v.v. Sau đó, tất cả các kết quả của hàm COUNTIF được cộng lại. Vì vậy, nếu tổng số cuối cùng bằng 0, công thức sẽ trả về chuỗi văn bản ‘Duy nhất’. Nếu số đếm khác 0, chúng tôi sẽ nhận được kết quả là ‘Khớp’.
So sánh và đánh dấu các cột phù hợp / không khớp
Nếu bạn muốn so sánh hai cột và đánh dấu các hàng có dữ liệu trùng khớp hoặc dữ liệu không khớp thay vì hiển thị kết quả trong một cột riêng biệt, bạn có thể sử dụng format có điều kiện trong Excel. Format có điều kiện là một tính năng trong Excel có thể làm nổi bật dữ liệu dựa trên một bộ quy tắc. Với format có điều kiện, bạn có thể xác định trực quan các giá trị phù hợp hoặc các giá trị khác nhau trong hai cột.
So sánh hai cột và đánh dấu dữ liệu phù hợp trong cùng một hàng (Cạnh nhau)
Nếu bạn muốn so sánh hai cột và đánh dấu dữ liệu giống hệt nhau trong các hàng giống nhau, hãy làm theo các bước sau:
Đầu tiên, chọn các ô bạn muốn so sánh và đánh dấu. Bạn có thể chọn một cột đơn hoặc nhiều cột nếu bạn muốn đánh dấu toàn bộ các hàng.
Trong tab ‘Trang chủ’, nhấp vào trình đơn ‘Format có Điều kiện’ trong nhóm Kiểu và chọn tùy chọn ‘Quy tắc Mới …’ từ trình đơn.
Thao tác này sẽ mở hộp thoại Quy tắc Format Mới. Trong cửa sổ hộp thoại đó, hãy chọn loại quy tắc ‘Sử dụng công thức để xác định ô cần format’.
Sau đó, nhập công thức sau vào trường ‘Format giá trị nơi công thức này là đúng:’:
=$A1=$B1Như bạn có thể thấy, đây là một công thức đơn giản ‘bằng với’ để kiểm tra xem giá trị trong ô A1 có bằng B1 hay không. Nhưng chúng tôi đã thêm dấu ‘$’ trước nhãn cột A và B để khóa các cột thành tham chiếu tuyệt đối. Vì vậy, chỉ số hàng tự động thay đổi cho mỗi hàng khi công thức được áp dụng.
Tiếp theo, nhấp vào nút ‘Format’ để tùy chỉnh giao diện bạn muốn cho các hàng được đánh dấu.
Trong cửa sổ hộp thoại Format ô, bạn có thể thay đổi kích thước phông chữ, màu phông chữ, đường viền ô, format số, v.v. Để đánh dấu các hàng phù hợp với các màu nền khác nhau, hãy chuyển sang tab Tô màu và chọn màu từ phần Màu nền. Bạn cũng có thể thay đổi kiểu mẫu và màu mẫu của các ô được đánh dấu. Khi bạn đã chọn xong format, hãy nhấp vào nút ‘OK’.
Một lần nữa, nhấp vào ‘OK’ trong hộp thoại Quy tắc Format Mới để áp dụng format
Các ô có giá trị phù hợp trong cả hai cột A và B sẽ được đánh dấu như hình dưới đây.
Nếu bạn có ít dữ liệu khớp hơn dữ liệu không khớp trong bảng, bạn có thể lật điều kiện để làm nổi bật sự khác biệt dữ liệu giữa hai cột.
Ví dụ: chúng ta có thể sử dụng một trong các quy tắc format có điều kiện dưới đây để làm nổi bật sự khác biệt giữa cột A và B:
=$A1<>$B1hoặc
=$A1=$B1=FALSEĐầu tiên, chọn tập dữ liệu và mở cửa sổ Quy tắc format mới, như chúng tôi đã chỉ cho bạn ở trên, sau đó chọn loại quy tắc ‘Sử dụng công thức để xác định ô cần format’. Sau đó, nhập một trong các quy tắc trên và nhấp vào nút ‘Format’.
Tiếp theo, chọn format bạn muốn áp dụng và nhấp vào ‘OK’. Và nhấp vào ‘OK’ một lần nữa để áp dụng format.
So sánh hai cột và đánh dấu các giá trị trùng lặp
Nếu bạn muốn so sánh hai cột và đánh dấu các giá trị hiện có trong cả hai cột ngay cả khi chúng không ở cùng một hàng, bạn có thể sử dụng quy tắc Format có điều kiện đặt trước hoặc quy tắc format tùy chỉnh.
Ví dụ: chúng tôi có hai danh sách trái cây từ các cửa hàng khác nhau và chúng tôi muốn làm nổi bật các loại trái cây có sẵn ở cả hai cửa hàng. Đây là cách bạn có thể làm điều đó:
Đầu tiên, chọn các cột bạn muốn so sánh và nhấp vào menu ‘Format có Điều kiện’ từ nhóm Kiểu.
Sau đó, di con trỏ vào tùy chọn ‘Đánh dấu ô quy tắc’ từ trình đơn thả xuống và chọn tùy chọn ‘Giá trị trùng lặp’.
Trong hộp thoại Giá trị trùng lặp, chọn ‘Nhân bản’ từ menu thả xuống bên trái.
Sau đó, chọn format từ menu thả xuống bên phải và nhấp vào ‘OK’.
Các mục tồn tại trên cả hai cột sẽ được đánh dấu.
Ngoài ra, bạn cũng có thể sử dụng các quy tắc format tùy chỉnh để đánh dấu các giá trị trùng lặp trong hai cột.
Để làm điều đó, trước tiên, hãy chọn cột A và nhấp vào tùy chọn ‘Format có điều kiện’ từ ruy-băng. Sau đó, chọn tùy chọn ‘Quy tắc mới’ từ menu.
Sau đó, chọn loại quy tắc ‘Sử dụng công thức để xác định ô cần format’ và nhập quy tắc bên dưới để đánh dấu các kết quả phù hợp trong cột A:
=COUNTIF($B$2:$B$12, $A2)>0Sau đó, nhấp vào nút ‘Format’ để chọn format bạn muốn áp dụng và áp dụng những format đó.
Nhấp vào ‘Ok’ để áp dụng format cho cột A.
Tiếp theo, chọn cột B và nhấp vào tùy chọn ‘Format có điều kiện’ từ ruy-băng. Sau đó, chọn tùy chọn ‘Quy tắc mới’ từ menu.
Trong cửa sổ Quy tắc Format Mới, hãy chọn loại quy tắc ‘Sử dụng công thức để xác định ô cần format’ và nhập thông tin bên dưới để đánh dấu các bản sao trong cột B:
=COUNTIF($A$2:$A$12, $B2)>0Sau khi nhập công thức, hãy nhấp vào nút ‘Format’ và chỉ định format cho các ô tô sáng.
Sau khi chọn format, hãy nhấp vào ‘OK’ để áp dụng nó.
Bây giờ, các giá trị trùng lặp trong cả hai cột đã được đánh dấu.
So sánh hai cột và làm nổi bật các giá trị duy nhất
Phương pháp này hoàn toàn ngược lại với phương pháp trên. Nếu bạn muốn so sánh hai cột và chỉ đánh dấu các giá trị duy nhất trong cả hai cột không khớp, bạn cũng có thể sử dụng format có điều kiện cho việc này.
Đầu tiên, chọn các cột bạn muốn so sánh, chuyển đến tab ‘Trang chủ’, sau đó nhấp vào trình đơn ‘Format có Điều kiện’ trong nhóm Kiểu.
Sau đó, di chuột qua các tùy chọn ‘Đánh dấu ô quy tắc’ và chọn ‘Giá trị trùng lặp’.
Trong menu thả có nội dung Trùng lặp, hãy chọn ‘Duy nhất’ và sau đó chọn format xác định trước cho dữ liệu không khớp. Sau đó, nhấp vào ‘OK’.
Bây giờ, các giá trị duy nhất hoặc không khớp từ cả hai cột được đánh dấu.
Ngoài ra, bạn cũng có thể sử dụng các quy tắc format tùy chỉnh để đánh dấu các giá trị duy nhất trong hai cột.
Để làm điều đó, trước tiên, hãy chọn cột A và nhấp vào tùy chọn ‘Format có điều kiện’ từ ruy-băng. Sau đó, chọn tùy chọn ‘Quy tắc mới’ từ menu.
Sau đó, chọn loại quy tắc ‘Sử dụng công thức để xác định ô cần format’ và nhập quy tắc bên dưới để đánh dấu các kết quả phù hợp trong cột A:
=COUNTIF($B$2:$B$12, $A2)=0Sau đó, nhấp vào nút ‘Format’ để chọn format.
Nhấp vào ‘Ok’ để áp dụng format cho cột A.
Tiếp theo, chọn cột B và nhấp vào tùy chọn ‘Format có điều kiện’ từ ruy-băng. Sau đó, chọn tùy chọn ‘Quy tắc mới’ từ menu.
Trong cửa sổ Quy tắc Format Mới, hãy chọn loại quy tắc ‘Sử dụng công thức để xác định ô cần format’ và nhập thông tin bên dưới để đánh dấu các bản sao trong cột B:
=COUNTIF($A$2:$A$12, $B2)=0Sau khi nhập công thức, hãy nhấp vào nút ‘Format’ và chỉ định format cho các ô tô sáng. Sau đó, nhấp vào ‘OK’ để áp dụng nó.
Bây giờ, các giá trị duy nhất trong cả hai cột đã được đánh dấu.
So sánh nhiều cột và đánh dấu các hàng phù hợp
Chúng tôi đã biết cách so sánh hai cột và đánh dấu các đối sánh hàng nhưng nếu bạn có nhiều cột cần được so sánh, bạn cũng có thể làm điều đó với sự trợ giúp của format có điều kiện. Với format có điều kiện, chúng ta có thể so sánh một số cột, từng hàng và đánh dấu các kết quả phù hợp.
Ví dụ: chúng tôi có danh sách trái cây từ ba cửa hàng khác nhau và chúng tôi muốn đánh dấu các hàng có các mặt hàng giống nhau trong cả ba cột. Để làm điều đó, hãy làm theo các bước sau:
Đầu tiên, chọn các cột để so sánh (A2: D12). Sau đó, nhấp vào trình đơn ‘Format có Điều kiện’ và chọn tùy chọn ‘Quy tắc Mới ..’.
Để so sánh nhiều cột, hãy tạo quy tắc format có điều kiện mới bằng hàm AND hoặc COUNTIF:
=AND($A2=$B2, $A2=$C2, $A2=$D2)Trong đó các cột A, B và C được khóa thành tham chiếu tuyệt đối bằng cách sử dụng ký hiệu $ trong khi số hàng (2) được để làm tham chiếu tương đối. Vì vậy, công thức có thể tự động thay đổi để so sánh các giá trị theo từng hàng. Khi công thức trên được áp dụng cho bảng, nó sẽ so sánh hàng đầu tiên của bảng. Sau đó, công thức tự động điều chỉnh thành = AND ($ A3 = $ B3, $ A3 = $ C3, $ A3 = $ D3), v.v. Chỉ số hàng thay đổi vì chúng là tham chiếu tương đối và các chữ cái của cột được giữ nguyên vì chúng là tham chiếu tuyệt đối.
Mỗi giá trị ô trong hàng được so sánh với giá trị của cột đầu tiên. Khi tất cả các điều kiện được thỏa mãn, hàm AND trả về giá trị TRUE. Nếu kết quả của quy tắc format có điều kiện là TRUE, thì hàng tương ứng sẽ được đánh dấu với format được chỉ định.
Trong cửa sổ Quy tắc Format Mới, hãy chọn ‘Sử dụng công thức để xác định ô cần format’ và nhập công thức trên vào trường văn bản ‘Format giá trị nơi công thức này đúng:’. Sau đó, nhấp vào ‘Format’ để chỉ định format.
Sau khi chọn format, hãy nhấp vào ‘Ok’ để áp dụng format có điều kiện.
Bây giờ, các hàng có cùng giá trị trong nhiều cột được đánh dấu.
Trong trường hợp bạn có nhiều cột để so sánh, bạn cũng có thể sử dụng hàm COUNTIF để tạo quy tắc format có điều kiện:
=COUNTIF($A2:$D2, $A2)=4Trong đó A2 sẽ được so sánh với mọi ô trong hàng đầu tiên (A2: D2) và 4 là số cột để so sánh. Công thức kiểm tra xem A2 có khớp với các ô khác trong hàng hay không. Nếu hàng có các giá trị giống nhau trong cả bốn cột, thì hàm COUNTIF sẽ trả về 4. Nếu kết quả của hàm COUNTIF bằng số cột (4), quy tắc format có điều kiện sẽ dẫn đến TRUE và hàng tương ứng sẽ được tô sáng.
Quy tắc format có điều kiện ở trên sẽ tự động điều chỉnh để so sánh từng hàng trong bảng.
Để bắt đầu, hãy chọn các cột để so sánh, nhấp vào trình đơn ‘Format có Điều kiện’ và chọn ‘Quy tắc Mới…’
Tiếp theo, chọn loại quy tắc ‘Sử dụng công thức để xác định ô cần format’ và nhập công thức trên vào trường văn bản bên dưới. Sau đó, chỉ định format cho các điểm nổi bật và nhấp vào ‘OK’.
Bây giờ, các hàng có cùng giá trị trong nhiều cột được đánh dấu.
Bạn nên biết rằng công thức AND và COUNTIF có thể được sử dụng để so sánh hơn 4 cột và đánh dấu các hàng có cùng giá trị.
So sánh nhiều cột và đánh dấu sự khác biệt của hàng
Nếu bạn muốn so sánh nhiều cột và đánh dấu các giá trị khác nhau (dữ liệu không khớp) trong mỗi hàng riêng lẻ, bạn có thể sử dụng tính năng ‘Chuyển đến Đặc biệt’ trong Excel.
Để thực hiện việc này, hãy chọn các cột bạn muốn so sánh.
Bây giờ, bạn cần chỉ định cột so sánh. Giá trị ô từ các cột đã chọn khác từ cùng một hàng sẽ được so sánh với cột so sánh để làm nổi bật sự khác biệt của ô. Khi bạn chọn một dải ô, ô trên cùng của dải ô là ô hiện hoạt. Trong hình trên, ô hiện hoạt có màu trắng trong khi các ô khác được đánh dấu màu xám. Ở đây, ô hiện hoạt là A3, do đó cột so sánh là A.
Để thay đổi cột so sánh, hãy nhấn Tabphím để di chuyển ô hiện hoạt từ trái sang phải hoặc nhấn Enterphím để di chuyển từ trên xuống dưới.
Sau đó, nhấp vào nút menu ‘Tìm & Chọn’ từ nhóm ‘Chỉnh sửa’ của tab ‘Trang chủ’ và chọn ‘Chuyển đến Đặc biệt…’.
Trong hộp thoại Đi đến Đặc biệt, chọn ‘Sự khác biệt hàng’ và nhấp vào nút ‘OK’.
Như bạn có thể thấy, tất cả các giá trị ô khác nhau trong cột so sánh trong mỗi hàng sẽ được đánh dấu / chọn.
Để tô màu các ô đã chọn, hãy nhấp vào nút ‘Tô màu’ trên ruy-băng và chọn một màu từ bảng màu.
So sánh hai cột bằng cách sử dụng hàm VLOOKUP và trích xuất dữ liệu phù hợp
Đôi khi, bạn có thể không chỉ muốn so sánh các mục trong danh sách này với danh sách khác mà còn kéo dữ liệu phù hợp. Khi so sánh các cột, có hai loại đối sánh bạn có thể sử dụng – đối sánh một phần hoặc đối sánh chính xác. Điều này có thể được thực hiện với hàm VLOOKUP hoặc INDEX MATCH.
Hàm Vlookup được sử dụng để tìm kiếm một giá trị cụ thể trong một cột và trả về một giá trị tương ứng từ một cột khác trong cùng một hàng.
Cú pháp của hàm VLOOKUP:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])Hàm này bao gồm 4 tham số hoặc đối số:
- lookup_value: Giá trị này chỉ định giá trị mà bạn đang tìm kiếm trong cột đầu tiên của mảng bảng nhất định. Giá trị Tra cứu phải luôn ở cột ngoài cùng bên trái của bảng tìm kiếm.
- table_array: Đây là bảng (phạm vi ô) mà bạn muốn tra cứu một giá trị. Bảng này (bảng tìm kiếm) có thể nằm trong cùng một trang tính hoặc trang tính khác hoặc thậm chí là một sổ làm việc khác.
- col_index_num: Điều này chỉ định số cột của mảng bảng có giá trị bạn muốn trích xuất.
- [range_lookup]: Tham số này chỉ định xem bạn muốn trích xuất kết hợp chính xác hay kết hợp gần đúng. Giá trị là TRUE hoặc FALSE, hãy nhập ‘FALSE’ nếu bạn muốn giá trị chính xác hoặc nhập ‘TRUE’ nếu bạn đồng ý với giá trị gần đúng.
Kết hợp chuẩn xác
Giả sử chúng ta có hai bảng với danh sách các mục. Trong phần thứ hai, chúng tôi có một danh sách các mặt hàng và giá của chúng cần được điền. Để làm điều đó, chúng ta cần so sánh cột A với cột D và trích xuất giá cho các mặt hàng phù hợp.
Chúng ta có thể sử dụng hàm VLOOKUP để so sánh hai cột và tìm nạp dữ liệu phù hợp:
= VLOOKUP (D2, $ A $ 2: $ B $ 13,2, FALSE)
Đầu tiên, nhập công thức vào ô E2 và sau đó sao chép công thức xuống cột bằng cách kéo chốt điền.
Trong đó D2 là giá trị cần tìm trong cột đầu tiên của bảng tra cứu. $ A $ 2: $ B $ 13 đại diện cho bảng tra cứu nơi giá trị sẽ được tìm kiếm và giá trị tương ứng sẽ được kéo. Ở đây phạm vi được khóa thành tham chiếu tuyệt đối để ngăn tham chiếu ô thay đổi khi công thức được sao chép xuống.
‘2’ trong công thức là số cột của bảng tra cứu với giá trị bạn muốn trích xuất. Tham số FALSE được sử dụng để tìm kết quả khớp chính xác của D2.
Công thức trên sẽ tìm kiếm giá trị trong cột A2: B13 (cột A) ở cột đầu tiên của dải ô D2. Đối sánh chính xác của D2 được tìm thấy trong hàng 5 của cột A, vì vậy giá trị tương ứng được trích xuất từ cột B (cột 2) và trả về trong E2. Khi công thức được sao chép xuống cột E, chỉ giá trị lookup_value tự động điều chỉnh thành D3, D4, v.v. để tìm kiếm từng giá trị của cột D trong phạm vi A2: B13.
So sánh các cột và kéo dữ liệu đối sánh bằng cách sử dụng hàm IFERROR hoặc IFNA
Trong trường hợp không tìm thấy lookup_value trong bảng tra cứu hoặc giá trị look_up không phải là bản sao chính xác của các giá trị trong bảng look_up, bạn sẽ gặp lỗi # N / A.
Trong ví dụ dưới đây, không tìm thấy giá trị tra cứu (D3 và D5) trong cột A và loại đối sánh là FALSE (chính xác), do đó, công thức trả về lỗi # N / A.
Điều này có thể xảy ra ngay cả khi có thêm khoảng trắng, thiếu khoảng trắng hoặc lỗi đánh máy trong giá trị look_up. Trong những trường hợp như vậy, bạn có thể thay đổi match_type thành TRUE, điều này sẽ cho phép công thức bỏ qua các lỗi nhỏ và tìm kiếm giá trị khớp gần đúng.
Nếu không tìm thấy giá trị tra cứu trong bảng, bạn có thể sử dụng hàm IFNA hoặc IFERROR để tránh lỗi # N / A.
=IFNA(VLOOKUP(D2,$A$2:$B$13,2,FALSE),"")
Công thức này hoạt động giống như công thức VLOOKUP trước đó, ngoại trừ IFNA thay thế thông báo lỗi bằng một ô trống. Bạn cũng có thể yêu cầu công thức trả về một văn bản thay vì một ô trống.
Ngoài ra, bạn cũng có thể sử dụng hàm IFERROR để loại bỏ thông báo lỗi và trả về một chuỗi văn bản được chỉ định. Để làm điều đó, hãy nhập công thức dưới đây:
=IFERROR(VLOOKUP(D2,$A$2:$B$13,2,FALSE),"Not Available")Nhập công thức trên vào ô E2 và sao chép nó xuống cột. Nếu hàm VLOOKUP trả về lỗi # N / A, thì hàm IFERROR sẽ thay thế nó bằng thông báo “Không có sẵn” như hình dưới đây.
So sánh hai cột và tìm một phần đối sánh bằng cách sử dụng Ký tự đại diện
Trong trường hợp có sự khác biệt nhỏ về tên trong hai cột, tham số TRUE trong hàm VLOOKUP sẽ không bao hàm nó. Ví dụ: nếu một cột có giá trị là “Google” và cột kia có “Google LLC”, thì công thức VLOOKUP ở trên sẽ không thể khớp với các cột. Tuy nhiên, bạn vẫn có thể sử dụng hàm VLOOKUP để khớp một phần các cột bằng cách thêm ký tự đại diện vào công thức.
Hàm Vlookup cho phép bạn tìm một phần đối sánh trên một giá trị được chỉ định bằng cách sử dụng các ký tự đại diện. Nếu bạn muốn xác định một giá trị có chứa giá trị tra cứu ở bất kỳ vị trí nào, hãy thêm dấu và (&) để nối giá trị tra cứu với ký tự đại diện (*). Sử dụng dấu hiệu ‘$’ để tạo tham chiếu ô tuyệt đối và thêm ký tự đại diện ‘*’ trước hoặc sau giá trị tra cứu.
Trong ví dụ dưới đây, chúng tôi chỉ có một phần của giá trị tra cứu (Fan) trong ô D3. Vì vậy, để thực hiện đối sánh từng phần trên các ký tự đã cho, hãy nối ký tự đại diện ‘*’ trước và sau tham chiếu ô.
=VLOOKUP("*"&D3&"*",$A$2:$B$13,2,FALSE)Trong công thức trên, D2 đã được đặt trong toán tử ‘&’ và dấu hoa thị “*” để bù cho ký tự bị thiếu trước và sau giá trị tra cứu. Nếu Danh sách 2 không có toàn bộ tên của các mục, các ký tự dấu hoa thị sẽ bù cho các ký tự bị thiếu và lấy giá trị từ các cột được khớp một phần.
Ví dụ, trong ô D3 chúng ta chỉ có mục có tên là ‘Quạt’ nhưng trong cột A chúng ta có ‘Quạt bàn’. Nhưng dấu hoa thị ‘*’ trước D3 bù đắp cho ‘Bảng’ bị thiếu trước giá trị tra cứu. Vì vậy, hàm VLOOKUP trả về giá trị tương ứng ’31 .68′ từ cột B.
So sánh hai cột bằng cách sử dụng hàm MATCH
Nếu bạn muốn trả lại vị trí của giá trị phù hợp trong cột thay vì chính giá trị đó, bạn có thể sử dụng hàm MATCH.
Hàm MATCH là một hàm tích hợp sẵn trong Excel và được sử dụng chủ yếu để định vị vị trí tương đối của giá trị tra cứu trong một cột hoặc một hàng.
Cú pháp của hàm MATCH:
=MATCH(lookup_value,lookup_array,[match_type})Ở đâu:
lookup_value – Giá trị bạn muốn tra cứu trong một phạm vi ô hoặc một mảng được chỉ định. Nó có thể là giá trị số, giá trị văn bản, giá trị lôgic hoặc tham chiếu ô có giá trị.
lookup_array – Mảng ô mà bạn đang tìm kiếm một giá trị. Nó phải là một cột hoặc một hàng.
match_type – Đây là một tham số tùy chọn có thể được đặt thành 0,1 hoặc -1 và mặc định là 1.
- 0 tìm kiếm một kết quả phù hợp chính xác và khi không tìm thấy nó, sẽ trả về một lỗi.
- -1 tìm kiếm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value khi mảng tra cứu theo thứ tự tăng dần.
- 1 tìm kiếm giá trị lớn nhất nhỏ hơn hoặc bằng giá trị look_up khi mảng tra cứu theo thứ tự giảm dần.
So sánh hai cột và tìm vị trí của một kết hợp chính xác
Giả sử, chúng ta có các bảng sau đây mà chúng ta muốn tìm vị trí của mỗi giá trị trong cột D trong cột A.
=MATCH(D2,$A$2:$A$13,0)Công thức tìm kiếm từng giá trị của danh sách 2 trong danh sách 1 và trả về vị trí của từng giá trị.
Hiển thị dữ liệu trùng lặp hoặc trùng khớp bằng cách sử dụng chức năng MATCH
Có thể sử dụng kết hợp các hàm MATCH, ISERROR và IF để so sánh và hiển thị các cột trùng lặp.
Ví dụ: chúng ta có thể sử dụng công thức dưới đây để so sánh hai cột và hiển thị các bản sao trong cột đầu tiên:
=IF(ISERROR(MATCH(A2,$B$2:$B$10,0)),"",A2)Ở đây, hàm ISERROR được kết hợp với hàm IF để tìm lỗi và hiển thị chuỗi văn bản hoặc khoảng trống.
Hàm MATCH tìm kiếm và trả về vị trí của A2 (trong phạm vi B2: B10) là 5. Vì nó không phải là lỗi nên hàm ISERROR trả về giá trị FALSE và hàm IF trả về giá trị của A2. Trong một trường hợp khác, hàm MATCH trong C6 trả về lỗi # N / A vì giá trị của A6 không được tìm thấy trong phạm vi B2: B10. Do đó, hàm ISERROR trả về TRUE và sau đó, hàm IF trả về giá trị trống.
Hiển thị dữ liệu duy nhất bằng cách sử dụng chức năng MATCH
Nếu bạn muốn so sánh hai cột và hiển thị các giá trị duy nhất trong mỗi cột, bạn cũng có thể thực hiện điều đó với công thức tương tự ở trên bằng cách chỉ cần hoán đổi 2 đối số cuối cùng của hàm IF.
Để hiển thị các giá trị duy nhất trong cột đầu tiên, hãy nhập công thức dưới đây:
=IF(ISERROR(MATCH(A2,$B$2:$B$10,0)),A2,"")Hàm MATCH tìm kiếm và trả về vị trí của A2 (trong phạm vi B2: B10) là 5. Vì kết quả không phải là lỗi nên hàm ISERROR trả về FALSE và hàm IF trả về khoảng trống.
Hàm MATCH trong C4 trả về lỗi # N / A vì không tìm thấy giá trị của A4 trong phạm vi B2: B10. Do đó, hàm ISERROR trả về TRUE và sau đó, hàm IF trả về giá trị A4.
Để hiển thị các giá trị duy nhất trong cột thứ hai, hãy nhập công thức dưới đây:
=IF(ISERROR(MATCH(B2,$A$2:$A$10,0)),B2,"")Hàm MATCH xem xét và trả về vị trí của B2 (trong phạm vi A2: A10) là 5. Vì kết quả không phải là lỗi nên hàm ISERROR trả về FALSE và hàm IF trả về khoảng trống.
Hàm MATCH trong C4 trả về lỗi # N / A vì không tìm thấy giá trị của B4 trong phạm vi B2: B10. Do đó, hàm ISERROR trả về TRUE và sau đó, hàm IF trả về giá trị B4.
So sánh hai cột bằng cách sử dụng hàm INDEX và MATCH
Hàm MATCH có thể được kết hợp với hàm INDEX để so sánh và đối sánh hai cột. So với hàm VLOOKUP, INDEX MATCH là một công thức linh hoạt và mạnh mẽ có thể so sánh hai cột và cũng kéo dữ liệu khớp.
Hàm INDEX được sử dụng để truy xuất một giá trị tại một vị trí cụ thể trong bảng hoặc một dải ô. Hàm MATCH trả về vị trí tương đối của một giá trị trong một cột hoặc một hàng. Khi được kết hợp, MATCH tìm số hàng hoặc cột (vị trí) của một giá trị cụ thể và hàm INDEX lấy ra một giá trị dựa trên số hàng và cột đó.
Cú pháp của hàm INDEX:
=INDEX(array,row_num,[col_num],)- mảng – Mảng ô mà bạn đang tìm kiếm một giá trị.
- row_num – Nó đại diện cho hàng trong mảng mà từ đó trả về một giá trị. Nếu row_num bị bỏ qua, thì column_num là bắt buộc.
- column_num – Nó đại diện cho cột trong mảng mà từ đó trả về một giá trị. Nếu column_num bị bỏ qua, thì row_num là bắt buộc.
Thí dụ:
Để so sánh hai cột A và D và tìm nạp giá (giá trị phù hợp) cho cột D bằng cách sử dụng INDEX và MATCH:
=INDEX($B$2:$B$13,MATCH(D2,$A$2:$A$13,0))Nhập công thức vào ô E2 và sao chép nó xuống phạm vi E3: E7. Bây giờ, hãy xem công thức hoạt động như thế nào:
Hàm INDEX cần một số hàng và cột để truy xuất một giá trị. Trong công thức trên, hàm MATCH lồng nhau tìm số hàng (vị trí) của giá trị D2. Sau đó, chúng tôi cung cấp số hàng (5) đó cho hàm INDEX với phạm vi B2: B13. Chúng tôi đã chỉ định ‘0’ làm đối số cuối cùng để bỏ qua số cột vì chúng tôi chỉ đang xem xét một cột trong mảng của chúng tôi, cột B ($ B $ 2: $ B $ 13).
Cuối cùng, hàm INDEX trả về giá trị thứ 5 trong mảng B2: B13, là 24,14.
Như bạn có thể thấy, chúng tôi đã gặp lỗi # N / A trong ô E5 vì giá trị ô D5 không có sẵn trong cột A. Để tránh những lỗi như vậy, bạn có thể bọc công thức bằng hàm IFERROR.
=IFERROR(INDEX($B$2:$B$13,MATCH(D2,$A$2:$A$13,0)),"")
Sử dụng ký tự đại diện
Trong trường hợp có sự khác biệt nhỏ về tên trong hai cột mà chúng tôi đang so sánh, bạn có thể khớp một phần các cột bằng cách thêm ký tự đại diện vào công thức.
Các ký tự đại diện chỉ có thể được sử dụng trong hàm MATCH khi match_type được đặt thành ‘0’ và giá trị tra cứu là một chuỗi văn bản. Có những ký tự đại diện bạn có thể sử dụng trong hàm MATCH: dấu hoa thị (*) và dấu chấm hỏi (?).
- Dấu hỏi (?) Được sử dụng để so khớp bất kỳ ký tự hoặc chữ cái đơn lẻ nào với chuỗi văn bản.
- Dấu hoa thị (*) được sử dụng để so khớp bất kỳ số ký tự nào với chuỗi.
Như bạn có thể thấy bên dưới, tên trong Danh sách 2 không đầy đủ như trong Danh sách 1, vì vậy việc sử dụng các ký tự đại diện có thể bù đắp cho các ký tự bị thiếu.
=INDEX($B$2:$B$13,MATCH("*"&D2&"*",$A$2:$A$13,0))Trong công thức trên, D2 đã được đặt trong toán tử ‘&’ và dấu hoa thị “*” để bù cho ký tự bị thiếu trước và sau giá trị tra cứu. Nếu danh sách 2 không có toàn bộ tên của các mục, các ký tự dấu hoa thị sẽ bù cho các ký tự bị thiếu và trích xuất giá trị từ các cột được khớp một phần.
So sánh hai cột và tìm các kết quả phù hợp và khác biệt bằng cách sử dụng VBA Macro
Trong trường hợp bạn cần so sánh và đối sánh các cột thường xuyên hoặc lặp đi lặp lại, bạn có thể tạo Macro VBA để tự động hóa các tác vụ đó. Bạn có thể sử dụng mã VBA để tạo các hàm tùy chỉnh do người dùng tạo để thực hiện các tác vụ và tính toán. Đây là cách bạn có thể làm điều đó:
So sánh hai cột theo hàng và đánh dấu sự khác biệt bằng cách sử dụng mã VBA
VBA Macro là cách nhanh nhất và hiệu quả nhất để so sánh hai cột trong Excel. Nếu bạn muốn so sánh hai cột và làm nổi bật sự khác biệt giữa chúng, hãy làm theo hướng dẫn:
Đầu tiên, mở sổ làm việc có chứa hai cột bạn muốn so sánh.
Sau đó, chuyển đến tab ‘Nhà phát triển’ và nhấp vào tùy chọn ‘Visual Basic’ từ dải băng hoặc nhấn Alt+ F11shortcut để mở Microsoft Visual Basic cho Ứng dụng.
Thao tác này sẽ mở Microsoft Visual Basic for Applications trong một cửa sổ riêng biệt. Trong cửa sổ VBA, nhấp vào menu ‘Chèn’ và chọn tùy chọn ‘Mô-đun’. Ngoài ra, bạn có thể chỉ cần nhấp chuột phải vào ‘Đối tượng Microsoft Excel’ trong thanh điều hướng ở bên trái, nhấp vào ‘Chèn’, sau đó chọn ‘Mô-đun’ từ menu phụ.
Bây giờ, sao chép và dán tập lệnh VBA sau vào cửa sổ mô-đun mới:
Sub HighlightColumnDifferences()
Dim Rg As Range
Dim Ws As Worksheet
Dim FI As Integer
On Error Resume Next
SRC:
Set Rg = Application.InputBox("Select Two Columns:", "Excel", , , , , , 8)
If Rg Is Nothing Then Exit Sub
If Rg.Columns.Count <> 2 Then
MsgBox "Please Select Two Columns"
GoTo SRC
End If
Set Ws = Rg.Worksheet
For FI = 1 To Rg.Rows.Count
If Not StrComp(Rg.Cells(FI, 1), Rg.Cells(FI, 2), vbBinaryCompare) = 0 Then
Ws.Range(Rg.Cells(FI, 1), Rg.Cells(FI, 2)).Interior.ColorIndex = 6 'you can change the color index as you like.
End If
Next FI
End Sub
Đoạn mã trên cho phép bạn so sánh hai cột theo từng hàng và làm nổi bật sự khác biệt giữa chúng.
Sau khi dán tập lệnh, hãy nhấp vào ‘File’ và chọn ‘Lưu XXXX (tên file)’ để lưu mô-đun này dưới dạng macro.
Tập lệnh VB cần được lưu trong một loại file hỗ trợ macro. Khi bạn nhấp vào ‘Lưu’, bạn sẽ thấy một hộp nhắc hỏi xem bạn muốn lưu file này trong file không có macro hay loại file hỗ trợ macro.
Nhấp vào ‘Không’ để chọn loại file hỗ trợ macro.
Trong cửa sổ Lưu dưới dạng, hãy chọn format ‘Sổ làm việc Hỗ trợ Macro Excel (* .xlsm)’ từ trình đơn thả xuống ‘Lưu dưới dạng’.
Sau đó, nhấp vào nút ‘Lưu’ để lưu macro VBA với sổ làm việc.
Bây giờ, bạn có thể chạy macro để so sánh các cột.
Quay lại trang tính Excel của bạn, sau đó đi đến tab ‘Nhà phát triển’ trong ‘Ruy-băng’ và chọn ‘Macro’ hoặc nhấn ALT+ F8.
Một hộp thoại có tên Macro sẽ mở ra. Dưới tên Macro, bạn sẽ thấy macro mà bạn đã tạo. Chọn macro ‘HighlightColumnDifference’ và nhấp vào ‘Chạy’.
Bây giờ, bạn sẽ thấy một hộp thoại để chỉ định hai cột. Chỉ cần chọn các cột bạn muốn so sánh và nhấp vào ‘OK’.
Sự khác biệt giữa hai cột sẽ được đánh dấu bằng màu nền mà bạn đã chỉ định trong mã. Mã VBA này so sánh các cột có phân biệt chữ hoa chữ thường và làm nổi bật sự khác biệt.
So sánh hai cột và đánh dấu dữ liệu phù hợp (hoặc trùng lặp) bằng cách sử dụng mã VBA
Nếu bạn muốn so sánh hai cột và sau đó đánh dấu các kết quả phù hợp hoặc trùng lặp trong cột thứ hai, bạn có thể sử dụng mã dưới đây.
Mở bảng tính và nhấn Alt+ F11để mở cửa sổ Microsoft Visual Basic for Applications. Sau đó, đi tới ‘Chèn’> ‘Mô-đun’ trong cửa sổ Microsoft Visual Basic for Applications.
Tiếp theo, sao chép-dán mã macro bên dưới vào tập lệnh Mô-đun trống mới:
Sub CompareTwoRanges()
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range
SRg:
Set xRgC1 = Application.InputBox("Select the column you want compare according to", "Excel", , , , , , 8)
If xRgC1 Is Nothing Then Exit Sub
If xRgC1.Columns.Count <> 1 Then
MsgBox "Please select a single column"
GoTo SRg
End If
SsRg:
Set xRgC2 = Application.InputBox("Select the column you want to highlight duplicates in:", "Excel", , , , , , 8)
If xRgC2 Is Nothing Then Exit Sub
If xRgC2.Columns.Count <> 1 Then
MsgBox "Please select a single column"
GoTo SsRg
End If
For Each xRgF1 In xRgC1
For Each xRgF2 In xRgC2
If xRgF1.Value = xRgF2.Value Then
xRgF2.Interior.ColorIndex = 38 '(you can change the color index as you need)
End If
Next
Next
End Sub
Sau khi dán mã, hãy lưu file dưới dạng Sổ làm việc được Bật Macro với format ‘* .xlsm’ như chúng tôi đã chỉ cho bạn ở trên. Sau đó, đóng mô-đun và cửa sổ Microsoft Visual Basic for Applications.
Để chạy macro VBA, hãy chuyển sang tab ‘Nhà phát triển’ và nhấp vào ‘Macro’ từ nhóm Mã.
Trong cửa sổ hộp thoại Macro, chọn ‘CompareTwoRanges’ và nhấp vào ‘Run’.
Khi bạn nhìn thấy hộp thoại bật lên đầu tiên, hãy chọn cột mà bạn muốn so sánh các giá trị trùng lặp và nhấp vào ‘OK’.
Trong hộp thoại thứ hai, chọn cột mà bạn muốn đánh dấu các giá trị trùng lặp và nhấp vào ‘OK’.
Như bạn có thể thấy bên dưới, cột thứ hai được so sánh với cột đầu tiên và các bản sao được đánh dấu trong cột thứ hai với màu nền. Mã VBA này so sánh các cột với các kết quả phù hợp phân biệt chữ hoa chữ thường.
So sánh hai cột và trích xuất dữ liệu phù hợp bằng cách sử dụng mã VBA
Trong trường hợp bạn muốn so sánh hai cột theo từng hàng và kéo các giá trị phù hợp (trùng lặp) sang một cột khác, bạn có thể sử dụng mã macro bên dưới.
Mở một mô-đun trống trong cửa sổ Microsoft Visual Basic for Applications như chúng tôi đã chỉ cho bạn. Sao chép và dán tập lệnh dưới đây vào mô-đun trống mới:
Sub PullMatches()
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range
Dim xIntSR, xIntER, xIntSC, xIntEC As Integer
On Error Resume Next
SRg:
Set xRgC1 = Application.InputBox("Select first column:", "Excel", , , , , , 8)
If xRgC1 Is Nothing Then Exit Sub
If xRgC1.Columns.Count <> 1 Then
MsgBox "Please select single column"
GoTo SRg
End If
SsRg:
Set xRgC2 = Application.InputBox("Select the second column:", "Excel", , , , , , 8)
If xRgC2 Is Nothing Then Exit Sub
If xRgC2.Columns.Count <> 1 Then
MsgBox "Please select single column"
GoTo SsRg
End If
Set xWs = xRg.Worksheet
For FI = 1 To xRg.Rows.Count
If Not StrComp(xRg.Cells(FI, 1), xRg.Cells(FI, 2), vbBinaryCompare) = 0 Then
Ws.Range(xRg.Cells(FI, 1), Rg.Cells(FI, 2)).Interior.ColorIndex = 8 'you can change the color index as you like.
End If
Next FI
End Sub
Sau khi dán mã, hãy lưu file và đóng cửa sổ Microsoft Visual Basic for Applications. Sau đó, mở cửa sổ hộp thoại Marco, chọn macro ‘PullMatches’ và nhấp vào ‘Chạy’.
Đầu tiên, chọn cột đầu tiên (bên trái) bạn muốn so sánh và nhấp vào ‘OK’.
Trong hộp thoại thứ hai, chọn cột thứ hai bạn muốn so sánh và nhấp vào ‘OK’.
Kết quả khớp giữa hai cột sẽ được kéo và hiển thị tự động ở cột bên phải của hai cột bạn đã chọn.
So sánh hai cột và trích xuất dữ liệu duy nhất bằng cách sử dụng mã VBA
Nếu bạn muốn so sánh hai cột và kéo các giá trị duy nhất, đây là mã VBA bên dưới có thể giúp bạn.
Mở mô-đun trống trong cửa sổ Microsoft Visual Basic for Applications và sao chép-dán tập lệnh bên dưới vào mô-đun trống mới:
Sub PullUniques()
Dim xRg, xRgC1, xRgC2, xFRg1, xFRg2 As Range
Dim xIntR, xIntSR, xIntER, xIntSC, xIntEC As Integer
Dim xWs As Worksheet
On Error Resume Next
SRg:
Set xRg = Application.InputBox("Select two columns:", "Excel", , , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Columns.Count <> 2 Then
MsgBox "Please select two columns as a range"
GoTo SRg
End If
Set xWs = xRg.Worksheet
xIntSC = xRg.Column
xIntEC = xRg.Columns.Count + xIntSC - 1
xIntSR = xRg.Row
xIntER = xRg.Rows.Count + xIntSR - 1
Set xRg = xRg.Columns
Set xRgC1 = xWs.Range(xWs.Cells(xIntSR, xIntSC), xWs.Cells(xIntER, xIntSC))
Set xRgC2 = xWs.Range(xWs.Cells(xIntSR, xIntEC), xWs.Cells(xIntER, xIntEC))
xIntR = 1
For Each xFRg In xRgC1
If WorksheetFunction.CountIf(xRgC2, xFRg.Value) = 0 Then
xWs.Cells(xIntER, xIntEC).Offset(xIntR) = xFRg
xIntR = xIntR + 1
End If
Next
xIntR = 1
For Each xFRg In xRgC2
If WorksheetFunction.CountIf(xRgC1, xFRg) = 0 Then
xWs.Cells(xIntER, xIntSC).Offset(xIntR) = xFRg
xIntR = xIntR + 1
End If
Next
End Sub
Sau đó, lưu file và đóng cửa sổ Microsoft Visual Basic for Applications.
Sau đó, mở cửa sổ hộp thoại Marco, chọn macro ‘PullUniques’ và nhấp vào ‘Chạy’.
Trong cửa sổ bật lên, chọn hai cột so sánh và nhấp vào ‘OK’.
Macro so sánh các cột không có phân biệt chữ hoa chữ thường và liệt kê các giá trị duy nhất từ hai cột.
Đó là nó. Bây giờ, bạn đã biết mọi thứ về so sánh các cột trong Excel. Bạn có thể lựa chọn phương pháp phù hợp với mình nhất.
