Làm sao để kiểm tra sai lệch dữ liệu trong bảng dữ liệu lớn trong Excel.

Bài viết này Gitiho.com sẽ hướng dẫn cho các bạn cách so sánh 2 chuỗi chữ gồm chữ hoa và chữ thường gây ra việc tìm kiếm giống nhau trong Excel.  Qua topic này các bạn sẽ biết được thêm một số công thức để so sánh 2 ô bằng cách phát hiện tần suất xuất hiện của một ký tự đặc biệt , so sánh độ dài của văn bản, so sánh nhiều ô và cả so sánh 2 ô thông qua giá  trị của ô.

Điều quan trọng khi sử dụng Excel là tính chính xác trong việc phân tích dữ liệu. Thâm hụt doanh thu, chậm deadline hay thậm chí là đưa ra nhậm định sai lầm là những hậu quả hoàn toàn có thể xảy đến khi người dùng Excel thiếu cẩn trọng trong công việc.

Trong trường hợp kết quả sai do dữ liệu lỗi, để dò xem sai sót bắt nguồn từ đâu người ta thường kiểm tra dữ liệu và độ chính xác. Chúng ta hoàn toàn dễ dàng vá thủ công nếu như số ô chỉ đếm trên đầu ngón tay, nhưng với những cơ quan, tập đoàn lớn có hàng trăm, hàng nghìn những chuỗi dữ liệu quan trọng thì cần phải có những cách thức tự động hoá hiệu quả hơn.

Sau đây Gitiho sẽ tổng hợp lại những công thức dành cho những trường hợp cụ thể để giúp các bạn giải quyết công việc hiệu quả và nhanh chóng hơn.

Cách 1: So sánh 2 ô theo số ký tự trong chuỗi. 

Cách so sánh này khá đơn giản bởi chỉ cần xem xem các dòng chữ trong mỗi ô có số ký tự bằng nhau không. Đầu tiên bạn sử dụng hàm LEN để đếm số ký tự trong ô và so sánh số ký tự.

Để so sánh ô A2 và B2 có bao nhiêu chữ, có 2 công thức thường thấy:

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

Công thức này có thể sử dụng cho cả một đoạn văn bản, một dãy số. Công thức thứ nhất sẽ ra TRUE/FALSE, công thức còn lại sẽ cho kết quả của riêng bạn.

*Tips: Chẳng hạn có hai đoạn văn có nội dung giống hệt nhau nhưng lại cho ra độ dài khác nhau thì có thể đang gặp phải vấn đề bị thừa dấu cách giữa các chữ trong văn bản. Hàm Trim sẽ rất hữu dụng để khắc phục vấn đề này.

Cách 2: So sánh ô nhờ tần suất xuất hiện của một ký tự cụ thể.

Để dễ hiểu hơn, chúng ta sẽ đặt trường hợp này vào một case cụ thể:  Bạn đang có 2 cột văn bản, trong đó cả 2 cột này đều chứa một ký tự liên quan đến nội dung quan trọng. Công việc của bạn là kiểm tra xem tần suất xuất hiện của ký tự này ở 2 cột mỗi hàng có giống nhau không.

Ví dụ: Bạn có hai list gồm "Shipped" (đang giao) và "Received" (đã nhận) lần lượt ở cột B và C. Mỗi một mặt hàng sẽ được cấp một mã riêng biệt ở cột A và nhiệm vụ của bạn là đưa là số lượng hàng đang giao và hàng đã nhận bằng nhau.

Bước 1: thay thế mã phân biệt thành không dùng hàm SUBTITUTE:

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

Lấy độ dài của chuỗi khi loại bỏ mã để phân biệt, trừ mã khỏi tổng độ dài chuỗi để phục vụ cho việc phân biệt tần suất xuất hiện của mã phân biệt trong mỗi ô.  Để thao tác, các bạn nên viết riêng thành 2 ô:

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

Và

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

Cuối cùng, để so sánh 2 con số đã đưa ra thì bạn chỉ cần đặt dấu "=" giữa 2 phần.

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

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

Ở đây, chúng ta đã đặt mã phân biệt thuộc ô A2 và ô B2, C2 để so sánh chuỗi chữ. Suy ra công thức. 

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

Hoàn thành công thức trên chúng ta sẽ được hai kết quả TRUE hoặc False. Nếu như là TRUE thì số kí tự của ba ô A2, B2 và C2 bằng nhau. Còn nếu ra FALSE thì ngược lại. Hoặc bạn có thể dùng hàm IF để đưa ra kết quả chính xác hơn. 

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

Công thức này có thể sử dụng cho những trường hợp đặc biệt khác như:

- Số đếm, ký tự đếm xuất hiện trong văn bản.

- Một văn bản có nhiều dấu câu, số hiệu, dấu cách hoặc ký tự phổ biến như ";" và ",".

Cách 3: So sánh nhiều số ô trong Excel.

Cách thức này được chia ra làm hai phương thức: so sánh nhiều ô có phân biệt chữ thường, chữ hoa và so sánh nhiều ô KHÔNG phân biệt chữ thường, chữ hoa.

So sánh có sự phân biệt giữa chữ thường và chữ hoa.

Với cách thức này, chúng ta sử dụng lệnh sau:

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

Công thức đầu tiên sẽ cho ra kết quả dạng TRUE/FALSE  và công thức còn lại sẽ cho ra kết quả phụ thuộc vào người dùng đặt tên.

So sánh KHÔNG có sự khác biệt chữ hoa và chữ thường.

Sẽ có hai công thức cơ bản giúp bạn thể hiện kết quả tuỳ vào yêu cầu của công việc, gồm:

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

Công thức bắt đầu bằng hàm AND sẽ phục vụ cho mục đích trả về kết quả TRUE/FALSE. Nếu là True có nghĩa là tất cả các ô có giá trị giống nhau, ngược lại, FALSE sẽ hiện ra nếu có một ô giá trị khác biệt.

Đối với công thức IF thì sẽ hiện kết quả là "EQUAL" và "Not Equal" phụ thuộc vào bạn viết quả theo tên riêng mà bạn đặt.

Vị dụ dưới đây đúng với mọi kiểu dữ liệu gồm chữ, số, ngày.

Cách 4: So sánh dãy ô với một ô mẫu có sẵn trước đó.

Những công thức dưới đây sẽ giúp bạn kiểm tra xem các ô có chứa một đoạn văn bản có giống với đoạn văn bản ở ô mẫu hay không.

Công thức giúp so sánh nội dung của các ô có giống với đoạn mẫu không bằng cách KHÔNG phân biệt chữ hoa, chữ thường.

Chẳng hạn kết quả không cần quan tâm đến chữ hoa hay chữ thường, chúng ta sử dụng hai công thức sau để so sánh với ô mẫu đã có trước:

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

Bằng cách kiểm tra logic của hàm IF, chúng ta cần so sánh 2 số: 

- Số hàng nhân với số cột sẽ cho ra số ô trong 1 khoảng cụ thể.

- Kết quả sẽ trả ra hàm COUNTIF nếu như số ô có chứa giá trị trong ô mẫu.

Ví dụ bạn cần so sánh các ô ở hai khoảng A2:B6 với văn bản mẫu tại ô C2, ta có công thức như sau:

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

Để giúp cho người sử dụng và theo dõi dễ hiểu hơn, thay vì ra kết quả "TRUE/FALSE", chúng ta sử dụng "All match" và "Not all match" bằng hàm IF:

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

Công thức này đúng cả với trường hợp dùng số, ngày, chuỗi văn bản.

Công thức giúp so sánh nội dung của các ô với văn bản mẫu có sự phân biệt giữa chữ hoa và chữ thường.

Chẳng hạn công việc bạn được giao cần phải phân biệt riêng cả chữ hoa lẫn chữ thường , người sử dụng có thể sử dụng công thức Mảng sau đây:

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 “)

Với đoạn văn mẫu trong ô A2 mà với khoảng ô từ A2:B6, ta sẽ có công thức như sau:

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

Lưu ý: công thức Mảng này được triển khai bằng tổ hợp phím Ctrl + Shift + Enter. Sau khi hoàn thành thao tác này, Excel sẽ để công thức Mảng trong dấu móc ngoặc như dưới đây.

Cách 5: So sánh 2 ô trong Excel.

Cũng tương tự như những cách thức phía trên, hai phương pháp dưới đây phụ thuộc vào việc bạn có cần lưu tâm đến chữ hoa hay chữ viết thường hay không.

So sánh 2 ô KHÔNG phân biệt viết hoa, viết thường.

Để áp dụng phương pháp này bạn dùng công thức như sau:

=A1=B1

A1 và B1 là ô bạn đang so sánh và kết quả cho ra sẽ là TRUE hoặc FALSE. 

Nếu bạn muốn hiển thị kết quả theo riêng ký tự của bạn thay vì TRUE/FALSE thì cần phải thêm cụm đó vào công thức hàm IF như sau: 

=IF(A1=B1, “Equal”, “Not equal”)

Công thức này cũng có khả năng sử dụng để so sánh chuỗi văn bản, ngày và số.

So sánh 2 ô có phân biệt cả viết hoa lẫn viết thường.

Chẳng hạn như chuỗi văn bản 1 và văn bản 2 cần đưa ra sự so sánh  (như ô A2 và B2 dưới đây) thì công thức bạn cần sẽ là:

=EXACT(A2, B2)

Kết quả sẽ ra TRUE nếu như chuỗi chữ trùng hợp nhau hết về cả chữ hoa lẫn chữ thường, còn không sẽ về FALSE.

Thay vì kết quả TRUE và FALSE thường thấy mà bạn muốn sử dụng hàm EXACT thì chúng ta cần phải sử dụng công thức IF và nhập cụm từ riêng mà bạn muốn nếu kết quả đúng và sai:

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

Hình minh hoạ dưới đây sẽ chỉ ra kết quả so sánh chữ viết hoa và chữ viết thường.

Trên đây Gitiho đã đưa ra những công thức giúp các bạn kiểm tra những dữ liệu trong những bảng dữ liệu khổng lồ bằng cách so sánh chuỗi trong Excel. Ngoài ra để sử dụng hiệu quả những hàm lệnh, hàm công cụ của Excel, các bạn nên tìm hiểu qua những bài viết như Hướng dẫn cách kết hợp hàm IF và hàm AND trong excel hay HƯỚNG DẪN CÁCH SỬ DỤNG HÀM IF VỚI NHIỀU ĐIỀU KIỆN: AND, OR, HÀM IF LỒNG NHAU VÀ HƠN THẾ.

Chúc các bạn hoàn thành công việc hiệu quả.