Hướng dẫn sử dụng hàm so sánh trong Excel kiểm tra sai lệch dữ liệu

Khi phân tích dữ liệu bằng Excel, chúng ta luôn phải hạn chế tối đa sai lệch, đảm bảo độ chính xác của các phép tính. Trong bài viết này, các bạn hãy cùng Gitiho tìm hiểu cách sử dụng các hàm so sánh trong Excel để kiểm tra các sai lệch dữ liệu trong trường hợp có và không phân biệt chữ hoa và chữ thường.

Sử dụng hàm so sánh trong Excel với hai ô tính bất kì

Tùy thuộc vào nhu cầu kiểm tra dữ liệu, bạn có thể sử dụng hai cách dưới đây để tạo hàm so sánh trong Excel với dữ liệu văn bản chứa chữ hoa và chữ thường.

Hàm so sánh trong Excel không phân biệt chữ hoa và chữ thường

Công thức đơn giản nhất để so sánh dữ liệu trùng trong Excel áp dụng cho hai ô tính, với điều kiện không phân biệt chữ hoa và chữ thường là:

=A1=B1

Trong đó: A1 và B1 là hai ô bạn cần so sánh.

Công thức sẽ trả về giá trị TRUE nếu giá trị trong hai ô khớp nhau hoàn toàn. Ngược lại, bạn sẽ nhận được giá trị FALSE nếu trong hai ô có bất kì ký tự nào khác nhau.

Nếu bạn muốn Excel trả về các cụm từ khác theo mong muốn của mình thì thay vì sử dụng công thức trên, bạn hãy sử dụng công thức hàm IF. Giả sử chúng ta muốn nhận kết quả "Equal" nếu hai ô có giá trị khớp hoàn toàn hoặc "Not equal" nếu dữ liệu trong hai ô có sự khác biệt. Lúc này, chúng ta viết hàm so sánh trong Excel như sau:

=IF(A1=B1, "Equal", "Not equal")

Trong đó: 

• A1=B1 là điều kiện trả kết quả của hàm.
• Equal là kết quả nếu giá trị hai ô đáp ứng điều kiện.
• Not equal là kết quả nếu giá trị hai ô không đáp ứng điều kiện.

Các bạn hoàn toàn có thể thay các cụm từ "Equal" và "Not equal" theo ý muốn của mình. Công thức hàm Excel cơ bản của chúng ta có thể thực hiện so sánh chuỗi giá trị dạng chữ (text), ngày (date) và số (number).

Chúng ta hãy thử áp dụng hai công thức hàm so sánh trong Excel kể trên để kiểm tra liệu kết quả hai hàm có như nhau không nhé.

Như các bạn thấy, hai công thức này đều trả về kết quả là giá trị trong hai ô khớp nhau hoàn toàn. Do đó, các bạn có thể yên tâm sử dụng dữ liệu cho các công thức hàm tính toán của mình.

Nếu bạn mới sử dụng Excel và chưa nắm được các hàm và cách viết công thức để tính toán trong Excel, tham khảo ngay khóa học sau:

Hàm so sánh trong Excel có phân biệt chữ hoa và chữ thường

Cách thức hoạt động mặc định của Excel là không phân biệt chữ hoa và chữ thường. Do đó, nếu bạn cần so sánh dữ liệu trùng trong Excel chính xác đến định dạng chữ hoa và chữ thường, bạn cần sử dụng một hàm khác. Hàm so sánh trong Excel dành cho bạn chính là hàm EXACT với công thức như sau:

=EXACT(A2,B2)

Kết quả bạn nhận được từ Excel là TRUE nếu 2 ô có nội dung khớp nhau hoàn toàn tính cả chữ hoa và chữ thường. Ngược lại, nếu giá trị trong 2 ô có sự sai lệch thì kết quả trả về là FALSE.

Nếu bạn muốn phép so sánh dữ liệu trùng trong Excel này trả về kết quả khác TRUE và FALSE thì bạn hãy kết hợp hàm IF vào công thức hàm EXACT để xác định các cụm từ bạn muốn Excel trả về:

=IF(EXACT(A2 ,B2), “Exactly equal”, “Not equal”)

Tại đây, chúng ta sẽ thay kết quả TRUE bằng cụm từ "Exactly equal" và thay kết quả FALSE bằng cụm từ "Not equal". Khi nhập hàm vào ô C2 và áp dụng tính năng AutoFill cho trang tính, chúng ta sẽ nhận được kết quả như trong hình dưới đây.

Cả 2 hàm so sánh trong Excel đã phát huy tác dụng trong trường hợp này. Vì vậy, bạn hãy áp dụng một trong hai công thức này nếu cần so sánh dữ liệu trong 2 ô tính nhé.

Xem thêm: Cách dùng hàm VLOOKUP lọc trùng dữ liệu trong Excel nhanh chóng

Sử dụng hàm so sánh trong Excel với nhiều ô tính

Trong phần này, chúng ta sẽ tìm hiểu cách so sánh dữ liệu trùng trong Excel với nhiều hơn 2 ô tính. Có sự khác biệt nào trong công thức được sử dụng? Câu trả lời chính là sự kết hợp của hàm AND. Chúng ta sẽ tiếp tục tìm hiểu cách ứng dụng công thức hàm so sánh trong Excel với 2 trường hợp phân biệt chữ hoa và chữ thường như ở phần trên.

Hàm so sánh trong Excel không phân biệt chữ hoa và chữ thường

Công thức đơn giản nhất để bạn so sánh dữ liệu trùng trong Excel với phạm vi nhiều ô tính là công thức hàm AND cơ bản như sau:

=AND(A2=B2, A2=C2)

Tương tự như hàm EXACT, công thức trên sẽ trả về một trong hai giá trị TRUE và FALSE tương ứng với kết quả so sánh. Nếu bạn muốn thiết lập các giá trị mới, hãy sử dụng hàm IF kèm theo. Như vậy, công thức của chúng ta là:

=IF(AND(A2=B2, A2=C2), “Equal”, “Not equal”)

Kết quả áp dụng 2 loại công thức trên được thể hiện trong hình phía dưới.

Điểm đặc biệt của công thức hàm AND kết hợp hàm IF trong Excel là có thể áp dụng cho mọi định dạng dữ liệu: văn bản (text), số (number) và ngày tháng (date).

Hàm so sánh trong Excel có phân biệt chữ hoa và chữ thường

Tương tự như ở phần trên với công thức hàm EXACT, chúng ta sẽ kết hợp thêm hàm AND để tạo một công thức hàm so sánh trong Excel có phân biệt chữ hoa và chữ thường trong nhiều ô dữ liệu. Công thức mới như sau:

=AND(EXACT(A2,B2), EXACT(A2, C2))

Nếu bạn muốn thay đổi giá trị TRUE và FALSE trả về thì bạn chỉ cần lồng hàm IF ra ngoài công thức trên để được công thức với giá trị tự tạo, giả sử kết quả "Exactly equal" thay cho TRUE và kết quả "Not equal thay cho FALSE:

=IF(AND(EXACT(A2,B2), EXACT(A2, C2)),”Exactly equal”, “Not equal”)

Bạn sẽ nhận thấy cả 2 công thức này đều trả về kết quả khớp với nhau.

Xem thêm: Hướng dẫn sử dụng hàm so sánh trong Excel kiểm tra sai lệch dữ liệu

Sử dụng hàm so sánh trong Excel cho một dải ô với một ô mẫu

Nếu bạn cần so sánh một phạm vi dữ liệu trang tính với một giá trị mẫu đặt tại một ô cố định, bạn sẽ cần các công thức mới để so sánh dữ liệu trùng trong Excel hiệu quả.

Hàm so sánh trong Excel không phân biệt chữ hoa và chữ thường

Nếu bạn không quan tâm đến sự khác biệt chữ hoa và chữ thường trong phạm vi dữ liệu, công thức phù hợp sẽ là:

ROWS(dãy ô)*COLUMNS(dãy ô)=COUNTIF(dãy ô, ô mẫu)

Trong đó:

• Hàm ROWS xác định số hàng trong phạm vi dữ liệu cần so sánh.
• Hàm COLUMNS xác định số cột trong phạm vi dữ liệu cần so sánh.
• Hàm COUNTIF sẽ đếm tổng số ô chứa giá trị khớp với giá trị trong ô mẫu.

Giả sử với đoạn văn bản mẫu tại ô C2 và chuỗi ô so sánh nằm ở khoảng A2:B6, chúng ta sử dụng công thức sau:

=ROWS(A2:B6)*COLUMNS(A2:B6)=COUNTIF(A2:B6,C2)

Nếu bạn muốn thay đổi giá trị trả về, hãy sử dụng kèm theo công thức hàm IF như trong các phần trên.

=IF(ROWS(A2:B6)*COLUMNS(A2:B6)=COUNTIF(A2:B6,C2),”All match”, “Not all match”)

Với công thức này, Excel sẽ tiến hành đối chiếu tất cả giá trị trong dãy ô với nội dung ô mẫu và trả về "All match" nếu tất cả các ô trong dãy ô so sánh khớp với giá trị ô mẫu. Ngược lại, nếu có bất kì ô nào không khớp, kết quả hàm so sánh trong Excel trả về là "Not all match".

Công thức này có thể áp dụng cho tất cả các định dạng dữ liệu. Để tìm hiểu chi tiết về các kiểu dữ liệu trong hệ thống Excel, hãy tham khảo bài viết dưới đây.

Hàm so sánh trong Excel có phân biệt chữ hoa và chữ thường

Nếu bạn yêu cầu độ chính xác tuyệt đối của phép so sánh dữ liệu, bạn sẽ cần đến một công thức mảng phức tạp như sau: 

IF(ROWS(dãy ô)*COLUMNS(dãy ô)=SUM(–EXACT(ô mẫu, dãy ô)), “từ_kết_quả_nếu _trùng”, ” từ_kết_quả_nếu _không_trùng “)

Công thức này hoạt động như sau: Nếu phạm vi dải dữ liệu so sánh với giá trị tại ô mẫu khớp hoàn toàn đến cả chữ hoa và chữ thường, hàm IF sẽ trả về "từ kết quả nếu trùng". Ngược lại, chỉ cần một giá trị không khớp, kết quả nhận được sẽ là "từ kết quả nếu không trùng". Áp dụng công thức cho khoảng ô A2:B6 và ô mẫu C2, chúng ta có công thức cụ thể như dưới đây:

Áp dụng công thức cho khoảng ô A2:B6 và ô mẫu C2, chúng ta có công thức cụ thể như dưới đây:

=IF(ROWS(A2:B6)*COLUMNS(A2:B6)=SUM(–EXACT(C2, A2:B6)), “All match”, “Not all match”)

Lưu ý: Bạn cần nhấn tổ hợp Ctrl+Shift+Enter để chạy công thức mảng, cách nhấn Enter thông thường sẽ không hoạt động.

Nếu bạn thực hiện đúng thao tác, Excel sẽ hiển thị thêm cặp dấu ngoặc nhọn {} ra ngoài công thức đã nhập và trả về kết quả tương ứng với hình.

Xem thêm: Hướng dẫn cách vẽ biểu đồ so sánh trong Excel từ báo cáo trên Excel

Sử dụng hàm so sánh trong Excel với độ dài chuỗi tại hai ô

Giả sử bạn cần kiểm tra độ dài chuỗi tại mỗi hàng liệu có bằng nhau, hàm cần dùng đến lúc này là hàm LEN với công thức cơ bản như sau, áp dụng cho 2 ô A2 và B2:

=LEN(A2)=LEN(B2)

Công thức này sẽ so sánh độ dài chuỗi tại 2 ô và trả về kết quả TRUE nếu ô A2 có số ký tự đúng bằng ô B2. Ngược lại, nếu có sự chênh lệch về độ dài 2 ô thì hàm so sánh trong Excel sẽ trả về kết quả FALSE.

Bạn có thể sử dụng thêm hàm IF nếu muốn Excel trả về giá trị khác thay cho TRUE và FALSE:

=IF(LEN(A2)=LEN(B2), “Equal”, “Not equal”)

Nếu bạn áp dụng công thức đúng như trên mà Excel trả về kết quả FALSE cho 2 đoạn văn bản trong có khác biệt gì về mặt hình thức, rắc rối khả năng cao nằm ở các ký tự khoảng trắng trong ô. Với trường hợp này, bạn cần kiểm tra và loại bỏ các dấu cách thừa đó. Như vậy, công thức so sánh dữ liệu trùng trong Excel mới trả về kết quả mong đợi.

Sử dụng hàm so sánh trong Excel cho hai ô tính bằng một ký tự

Trong trường hợp cần đối chiếu 2 ô tính Excel bằng số lần xuất hiện của một ký tự cụ thể thì công thức hàm so sánh trong Excel như thế nào? Chúng ta sẽ tìm hiểu một ví dụ cụ thể nhé.

Giả sử bạn có 2 danh sách giao hàng (cột B) và nhận hàng (cột C). Mỗi hàng chứa các danh sách đơn hàng cho 1 mặt hàng cụ thể, mặt hàng được phân biệt bằng mã riêng và mã được liệt kê ở cột A . Bạn muốn chắc chắn rằng trong mỗi hàng, có chứa số lượng hàng giao và hàng nhận bằng nhau, với mã cụ thể.

Để xử lý công việc này, bạn cần thực hiện các thao tác sau:

1. Thay thế ký tự mã phân biệt bằng kí tự trống bằng hàm SUBSTITUTE.

SUBSTITUTE(A1, ký_hiệu_để_đếm,””)

2. Đếm số lần xuất hiện của ký tự mã phân biệt trong các ô bằng cách lấy độ dài chuỗi khi đã loại bỏ ký tự phân biệt trừ đi mã khỏi tổng độ dài chuỗi văn bản. Như vậy, bạn sẽ có hai công thức cho hai ô lần lượt như sau:

LEN(cell 1) – LEN(SUBSTITUTE(cell 1, character_to_count, “”))

và

LEN(cell 2) – LEN(SUBSTITUTE(cell 2, character_to_count, “”))

3. Bạn so sánh kết quả của hai công thức trên bằng cách đặt dấu "=" vào giữa:

LEN(cell 1) – LEN(SUBSTITUTE(cell 1, character_to_count, “”))=LEN(cell 2) – LEN(SUBSTITUTE(cell 2, character_to_count, “”))

Trong ví dụ, mã phân biệt nằm ở ô A2 và chuỗi so sánh đặt tại ô B2 và C2 nên công thức của chúng ta sẽ là:

=LEN(B2)-LEN(SUBSTITUTE(B2,$A2,””))=LEN(C2)-LEN(SUBSTITUTE(C2,$A2,””))

Công thức này sẽ trả về kết quả TRUE nếu dữ liệu ô B2 và ô C2 chứa cùng một số lần xuất hiện ký tự trong ô A2. Ngược lại, Excel trả về giá trị FALSE nếu số lần xuất hiện ký tự ô A2 khác nhau trong ô B2 và ô C2. 

Nếu bạn muốn đổi các giá trị kết quả để phù hợp với công việc của mình, hãy sử dụng thêm hàm IF:

=IF(LEN(B2)-LEN(SUBSTITUTE(B2, $A2,””))=LEN(C2)-LEN(SUBSTITUTE(C2, $A2,””)), “Equal”, “Not equal”)

Công thức này có thể được áp dụng ngay cả trong trường hợp:

• Ký tự được đếm (mã phân biệt) xuất hiện ở bất kì đâu trong chuỗi văn bản.
• Chuỗi văn bản chứa số lượng ký tự khác nhau, dấu câu khác nhau như “;”, “,” hoặc dấu cách.

Xem thêm: Cách so sánh hai bảng tính Excel để tìm sự khác biệt

Tổng kết

Như vậy, chúng ta đã tìm hiểu cách viết hàm so sánh trong Excel với các trường hợp cụ thể. Hy vọng các bạn có thể dễ dàng áp dụng kiến thức trong bài viết để tối ưu công việc của mình. Để học sâu hơn về Excel, các bạn hãy tham khảo các bài viết trên blog tin học văn phòng Gitiho và tham gia khóa học Tuyệt đỉnh Excel với chúng mình nhé.

Gitiho chúc các bạn thành công!