Cách nhanh chóng xác định lỗi dữ liệu trong bảng Excel kích thước lớn

Bài viết này do Siêu Marketing biên soạn sẽ chỉ bạn cách thức so khớp hai dãy chữ trong Excel mà không cần quan tâm đến chữ hoa hay chữ thường, làm thế nào để tìm ra từ trùng lắp. Bạn sẽ tiếp cận với những công thức giúp so khớp hai ô theo giá trị, đo lường độ dài của nội dung văn bản hay số lượng ký tự đặc biệt xuất hiện và cách để so sánh nhiều ô cùng lúc.

Khi phân tích dữ liệu bằng Excel, sự chính xác luôn là yếu tố quan trọng nhất. Thông tin không chính xác có thể gây ra việc trễ hạn, sai lệch trong việc nhận định xu hướng, quyết định không đúng đắn và thiếu hụt thu nhập.

Công thức Excel thì luôn chính xác nhưng kết quả có thể nhiễu lỗi nếu dữ liệu nhập vào không đúng. Trong tình huống này, việc kiểm tra tính chính xác của dữ liệu là phương án duy nhất. So sánh hai ô đơn lẻ có thể thực hiện thủ công mà không gặp vấn đề gì, nhưng nếu bạn phải phân biệt sự khác biệt giữa các chuỗi hàng trăm hay hàng nghìn thì sẽ không thể dùng phương pháp thủ công.

Qua bài viết, bạn sẽ học cách tự động hóa công việc mà nếu thực hiện thủ công sẽ nhàm chán và dễ gặp lỗi, đồng thời bạn sẽ được giới thiệu những công thức tốt nhất cho từng trường hợp cụ thể.

PHƯƠNG PHÁP SO SÁNH HAI Ô TRONG EXCEL:

Phụ thuộc vào mong muốn của bạn là so sánh không để ý đến chữ in hoa hay in thường, có hai phương thức khác nhau để so sánh chuỗi trong Excel.

DÙNG CÔNG THỨC ĐỂ SO SÁNH HAI Ô MÀ KHÔNG QUAN TÂM ĐẾN CHỮ IN HOA, IN THƯỜNG

Phương pháp so sánh hai ô mà không căn cứ vào chữ in hoa hoặc in thường trong Excel được thực hiện bằng công thức:

=A1=B1    

Trong đó A1, B1 là hai ô bạn muốn so sánh. Kết quả trả về từ công thức này có thể là TRUE hoặc FALSE.

Nếu bạn muốn hiển thị kết quả dưới dạng các cụm từ tùy chỉnh cho trường hợp giống và khác nhau, bạn có thể sử dụng công thức trong hàm IF như sau:

=IF(A1=B1, “Giống nhau”, “Không giống nhau”)

Hình ảnh dưới đây thể hiện việc so sánh chuỗi chữ, ngày tháng và số liệu thực hiện như thế nào.

1 - so sánh chuỗi trong excel
1 – so sánh chuỗi trong excel

DÙNG CÔNG THỨC ĐỂ SO SÁNH HAI Ô VỚI PHÂN BIỆT CHỮ IN HOA VÀ IN THƯỜNG

Tại vài tình huống bạn muốn so sánh chữ in hoa và in thường trong hai ô, có thể áp dụng hàm EXACT trong Excel.

EXACT(text1, text2)

Nếu hai chuỗi văn bản cần so sánh nằm trong hai ô, giả sử là A2 và B2, công thức sẽ như sau:

=EXACT(A2, B2)

Kết quả sẽ cho bạn biết hai chuỗi chữ có hoàn toàn giống nhau về mọi mặt bao gồm cả chữ hoa và chữ thường là TRUE, ngược lại sẽ là FALSE.

Để hàm EXACT trả về kết quả không phải TRUE hoặc FALSE, bạn có thể dùng công thức IF và nhập vào cụm từ bạn mong muốn cho trường hợp đúng và sai:

=IF(EXACT(A2, B2), “Hoàn toàn giống”, “Không giống nhau”)

Hình minh họa dưới cung cấp ví dụ về kết quả khi so sánh chuỗi có phân biệt chữ in hoa và in thường:

2 - so sánh chuỗi trong excel
2 – so sánh chuỗi trong excel

PHƯƠNG PHÁP SO SÁNH NHIỀU Ô CÙNG MỘT LÚC TRONG EXCEL:

Đối với việc cần so sánh nhiều hơn hai ô nằm trong cùng một hàng, bạn có thể áp dụng công thức sau:

Mô tả cụ thể về việc sử dụng hàm AND cùng với nhau như sau:

PHƯƠNG PHÁP SO SÁNH NHIỀU Ô TRONG EXCEL KHÔNG QUAN TRỌNG CHỮ HOA HAY CHỮ THƯỜNG

Để minh họa kết quả theo cách mong muốn của bạn, hãy chọn 1 trong những công thức dưới đây:

=AND(A2=B2, A2=C2)

Hoặc

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

Hàm AND sẽ trả về kết quả TRUE khi mọi ô có cùng giá trị và FALSE nếu có bất kỳ sự khác biệt nào. Trong khi đó, hàm IF cho phép bạn gán nhãn cho kết quả, như trong ví dụ là “Equal” hoặc “Not equal”.

Xem hình bên dưới, bạn sẽ thấy công thức này áp dụng cho cả dữ liệu là chữ, số và ngày.

3 - so sánh chuỗi trong excel
3 – so sánh chuỗi trong excel

PHƯƠNG PHÁP SO SÁNH CÁC Ô CÓ PHÂN BIỆT CHỮ HOA VÀ CHỮ THƯỜNG

Nếu bạn cần xác định các chuỗi có hoàn toàn giống nhau hay không, bạn có thể áp dụng công thức sau đây:

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

Hoặc

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

Như trong ví dụ đã được trình bày, công thức thứ nhất sẽ đưa ra TRUE hoặc FALSE, và công thức thứ hai cho phép bạn đặt tên cho kết quả.

4 - so sánh chuỗi trong excel
4 – so sánh chuỗi trong excel

KIỂM TRA SỰ GIỐNG NHAU GIỮA DÃY Ô VÀ MỘT MẪU Ô CỤ THỂ

Để tìm hiểu cách kiểm tra xem các ô trong một dãy có nội dung giống với ô mẫu hay không, hãy tham khảo các ví dụ sau.

CÔNG THỨC SO SÁNH GIỮA CÁC Ô VỚI MẪU VĂN BẢN KHÔNG QUAN TRỌNG CHỮ IN HOA HAY CHỮ THƯỜNG

Khi không phân biệt chữ hoa chữ thường, bạn có thể sử dụng công thức dưới đây để so sánh:

ROWS(khoảng ô)*COLUMNS(khoảng ô)=COUNTIF(khoảng ô, ô mẫu)

Trong khi sử dụng hàm IF, bạn sẽ so sánh hai con số:

  • Tính tổng số các ô trong một vùng cụ thể (số hàng nhân với số cột)
  • Đếm số ô có chứa giá trị tương đồng với ô mẫu (giá trị do hàm COUNTIF trả về)

Ví dụ, nếu ta có ô mẫu C2 và muốn so sánh với dãy ô từ A2 đến B6, công thức sẽ ra như sau:

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

Nếu bạn muốn hiển thị kết quả một cách thân thiện hơn, chẳng hạn “All match” hoặc “Not all match”, thay vì TRUE và FALSE, hãy dùng hàm IF như trong những ví dụ trước.

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

5 - so sánh chuỗi trong excel
5 – so sánh chuỗi trong excel

Hình trên cho thấy công thức này phù hợp để so sánh các dữ liệu bao gồm chữ cái, số lẫn ngày tháng.

CÔNG THỨC SO SÁNH GIỮA CÁC Ô VỚI VĂN BẢN MẪU CÓ PHÂN BIỆT CHỮ IN HOA VÀ CHỮ THƯỜNG

Nếu sự khác biệt giữa chữ hoa và chữ thường là đáng kể, bạn có thể sử dụng công thức mảng dưới đây để so sánh các ô với mẫu:

IF(ROWS(khoảng ô)*COLUMNS(khoảng ô)=COUNTIF(khoảng ô, ô mẫu)

Đối với phần ô từ A2 đến B6 cùng với mẫu văn bản ở ô C2, công thức sẽ được biểu diễn như sau:

=IF(ROWS(A2:B6)*COLUMNS(A2:B6)=SUM(–EXACT(C2, A2:B6)), “Khớp hết”, “Không khớp hết”)

Để thực hiện công thức Mảng không giống như công thức thông thường, bạn cần bấm tổ hợp phím Ctrl + Shift + Enter. Khi thực hiện đúng, Excel sẽ đặt công thức Mảng vào trong cặp dấu ngoặc nhọn như ví dụ dưới đây:

6 - so sánh chuỗi trong excel
6 – so sánh chuỗi trong excel

KIỂM TRA ĐỘ DÀI CHUỖI GIỮA HAI Ô:

Đôi khi, việc kiểm tra xem hai ô có cùng độ dài chuỗi hay không là cần thiết. Công thức sử dụng đơn giản, chỉ cần đo độ dài chuỗi chữ thông qua hàm LEN rồi sau đó so sánh.

Với trường hợp cần so sánh chuỗi ở ô A2 và B2, bạn có thể áp dụng một trong hai công thức sau:

=LEN(A2)=LEN(B2)

Hoặc bạn có thể sử dụng:

=IF(LEN(A2)=LEN(B2), “Bằng nhau”, “Không bằng nhau”)

Công thức đầu tiên sẽ cho kết quả là TRUE hoặc FALSE, còn công thức thứ hai sẽ được định nghĩa kết quả bởi bạn.

7 - so sánh chuỗi trong excel
7 – so sánh chuỗi trong excel

Bức ảnh trên minh họa công thức áp dụng đúng cho văn bản và số liệu.

Lời khuyên nhỏ:

Nếu bạn thấy hai đoạn văn bản có vẻ giống hệt nhau nhưng lại cho kết quả độ dài khác nhau, có thể do dấu cách bị thừa ở đầu hoặc cuối một trong hai ô hoặc cả hai. Hãy sử dụng hàm TRIM để xóa bỏ những dấu cách này.

SO SÁNH SỐ LẦN XUẤT HIỆN CỦA MỘT KÝ TỰ CỤ THỂ TRONG HAI Ô:

Trong ví dụ cuối cùng của bài viết này, chúng ta sẽ xem xét việc kiểm tra sự khớp nhau về số lần xuất hiện của một ký tự quan trọng trong hai cột với mỗi dòng.

Xét ví dụ dưới đây, giả sử bạn có hai danh sách, một cho giao hàng (cột B) và một cho nhận hàng (cột C), trong đó mỗi dòng chứa danh sách đơn hàng cho một mặt hàng được đánh dấu mã riêng biệt ở cột A (như hình minh họa bên dưới). Bạn muốn đảm bảo rằng với mỗi hàng, số lượng hàng giao và nhận khớp nhau cho mã hàng đã chọn.

Bạn có thể giải quyết vấn đề này bằng cách sử dụng logic công thức như sau:

  • Đầu tiên, loại bỏ mã đánh dấu ra khỏi chuỗi bằng cách dùng hàm SUBSTITUTE:

SUBSTITUTE(ô A1, ký_tự_cần_đếm,””)

  • Tiếp theo, tính số lần xuất hiện của mã bằng cách lấy độ dài chuỗi sau khi đã loại bỏ mã đó và trừ đi từ độ dài tổng của chuỗi. Công thức này cần được viết ra cho cả ô 1 lẫn ô 2, ví dụ:

LEN(ô 1) – LEN(SUBSTITUTE(ô 1, ký_tự_cần_đếm, “”))

LEN(ô 2) – LEN(SUBSTITUTE(ô 2, ký_tự_cần_đếm, “”))

  • Cuối cùng, để so sánh hai giá trị trên, bạn chỉ cần đặt dấu bằng giữa hai công thức trên:

LEN(ô 1) – LEN(SUBSTITUTE(ô 1, ký_tự_cần_đếm, “”))=

LEN(ô 2) – LEN(SUBSTITUTE(ô 2, ký_tự_cần_đếm, “”))

Ví dụ trên minh họa cách thức tiến hành trong tình huống cụ thể.

Ở ô A2, bạn nhập mã cần phân biệt, và tại ô B2 và C2, bạn nhập chuỗi cần được so sánh. Do đó, công thức ta sẽ sử dụng là:

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

Công thức sẽ trả về giá trị TRUE nếu số lần xuất hiện của ký tự ở ô A2 trong ô B2 và C2 là như nhau; ngược lại sẽ là FALSE. Để hiển thị kết quả thân thiện hơn với người dùng, hãy sử dụng hàm IF:

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

8 - so sánh chuỗi trong excel
8 – so sánh chuỗi trong excel

Công thức này vẫn hoạt động hiệu quả ngay cả khi:

  • Ký tự cần đếm (mã phân biệt) nằm ở bất cứ vị trí nào trong văn bản.
  • Văn bản chứa nhiều loại ký tự, dấu câu như “;”, “,” hoặc khoảng trắng.

Đến đây, sieumarketing.com đã giới thiệu đến các bạn cách để so sánh chuỗi trong Excel. Xin cảm ơn các bạn đã theo dõi và chúc mọi người áp dụng thành công!

Thêm vào đó, để ứng dụng Excel một cách hiệu quả vào công việc bạn cũng cần phải thành thạo các hàm và công cụ khác trong Excel:

Một số hàm thông dụng bao gồm:

  • SUMIF, SUMIFS cho việc tính tổng theo điều kiện đơn hoặc nhiều điều kiện
  • COUNTIF, COUNTIFS dùng để thống kê, đếm theo một hoặc nhiều tiêu chí
  • Hàm xử lý dữ liệu định dạng chuỗi, ngày tháng, số…
  • Hàm tìm kiếm và tham chiếu Index+Match, hàm SUMPRODUCT…

Và một số công cụ khác thường xuyên được sử dụng như:

  • Conditional formatting để tạo định dạng dựa trên các điều kiện
  • Data Validation cho việc thiết lập điều kiện nhập liệu
  • Cách tạo và sử dụng Name trong công thức
  • Tạo báo cáo sử dụng Pivot Table…

Thật nhiều kiến thức cần nắm vững đúng không? Tất cả các kiến thức này bạn có thể học được trong khóa học SM90 – Excel từ cơ bản đến chuyên gia do Siêu Marketing cung cấp. Khóa học này sẽ giúp bạn có một hê thống kiến thức toàn diện, chi tiết. Quan trọng hơn, khóa học không giới hạn thời gian học, do đó bạn có thể học mọi lúc và dễ dàng tra cứu lại kiến thức mình cần. Hiện tại có một ưu đãi đặc biệt cho những người đăng ký khóa học. Để biết thêm chi tiết, xem tại: sieumarketing.com

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop