Microsoft Excel

Microsoft Excel (Bài 31): Kiểm tra sai lệch dữ liệu và loại bỏ dữ liệu lặp trong Excel

Posted on by HKT

Trong bài viết này, sẽ hướng dẫn bạn cách so sánh 2 chuỗi chữ trong Excel trong các trường hợp không phân biệt được chữ hoa, chữ thường, tìm kiếm từ giống nhau. Bạn sẽ học được các công thức để so sánh 2 ô thông qua giá trị của ô, độ dài của đoạn văn bản, hoặc số lần xuất hiện của 1 ký tự đặc biệt, cũng như so sánh nhiều ô.

Khi sử dụng Excel để phân tích dữ liệu, độ chính xác là yếu tố quan trọng nhất. Thông tin sai lệch có thể dẫn đến chậm deadlines, phán đoán nhầm xu hướng, quyết định sai lầm, thâm hụt doanh thu.

Các công thức của Excel luôn luôn đúng, nhưng kết quả có thể sai do trong hệ thống có dữ liệu lỗi. Trong trường hợp này, biện pháp duy nhất là kiểm tra dữ liệu và độ chính xác. Nếu chỉ so sánh 2 ô thì làm theo phương pháp thủ công sẽ không có vấn đề gì, nhưng chỉ ra sự khác nhau giữa hàng trăm, hàng ngàn chuỗi chữ thì phương pháp này là không thể.

Bài viết sẽ giúp bạn tự động hoá công việc so sánh các ô chán ngắt và dễ mắc lỗi này, đồng thời hướng dẫn công thức hợp lý nhất trong từng trường hợp cụ thể.

SO SÁNH 2 Ô TRONG EXCEL:

Có 2 cách khác nhau để so sánh chuỗi trong Excel tuỳ vào việc bạn muốn tìm kiếm không phân biệt hay phân biệt chữ viết hoa, viết thường.

CÔNG THỨC SO SÁNH 2 Ô KHÔNG PHÂN BIỆT CHỮ VIẾT HOA, VIẾT THƯỜNG

Để so sánh 2 ô trong Excel không quan tâm đến chữ hoa, chữ thường, bạn dùng công thức như sau:

=A1=B1

A1, B1 là ô mà bạn đang so sánh. Kết quả của công thức là TRUE hoặc FALSE.

Nếu bạn muốn kết quả thể hiện sự giống hoặc khác nhau là những cụm từ của riêng bạn, thêm cụm đó vào công thức hàm IF như sau:

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

Như bạn thấy trong hình dưới đây, công thức có thể so sánh chuỗi chữ, ngày và số.

CÔNG THỨC SO SÁNH 2 Ô PHÂN BIỆT CHỮ VIẾT HOA, VIẾT THƯỜNG

Với một số trường hợp, yêu cầu không chỉ so sánh chữ trong 2 mà còn so sánh chữ hoa, chữ thường, thì việc so sánh có thể dùng hàm EXACT trong Excel.

EXACT (chuỗi văn bản 1, chuỗi văn bản 2)

Nếu chuỗi văn bản 1 và chuỗi văn bản 2 ở trong 2 ô mà bạn cần so sánh, giả sử ô A2 và B2 thì công thức như sau:

=EXACT(A2, B2)

Kết quả, bạn sẽ nhận được TRUE nếu chuỗi chữ khớp nhau hoàn toàn cả về mặt chữ hoa, chữ thường còn không sẽ là FALSE.

Nếu bạn muốn hàm EXACT cho ra kết quả khác TRUE hoặc FALSE thì dùng công thức IF và đánh cụm từ mà bạn muốn nếu kết quả đúng và sai:

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

Hình dưới miêu tả kết quả trong trường hợp so sánh phân biệt chữ hoa, chữ thường:

SO SÁNH NHIỀU Ô TRONG EXCEL:

Để so sánh nhiều hơn 2 ô trong 1 hàng, dùng công thức như ví dụ trên và kết hợp thêm AND. Chi tiết như sau:

CÔNG THỨC SO SÁNH NHIỀU Ô KHÔNG PHÂN BIỆT CHỮ HOA, CHỮ THƯỜNG

Tuỳ vào cách bạn muốn thể hiện kết quả như thế nào, dùng 1 trong số những công thức sau:

=AND(A2=B2, A2=C2)

Hoặc

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

Công thức AND trả về TRUE nếu tất cả các ô chứa giá trị giống nhau, FALSE nếu 1 ô chứa giá trị khác. Công thức IF giúp bạn viết kết quả bằng từ của riêng bạn, trong ví dụ này là “Equal” và “Not equal”.

Trong hình dưới, công thức đúng với mọi kiểu dữ liệu – chữ, số, ngày.

CÔNG THỨC SO SÁNH NHIỀU Ô PHÂN BIỆT CHỮ HOA, CHỮ THƯỜNG

Để so sánh nhiều chuỗi xem chúng có giống nhau hoàn toàn không, dùng 1 trong số các công thức sau:

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

Hoặc

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

Cũng giống ví dụ trước, công thức đầu tiên cho ra giá trị TRUE – FALSE, công thức thứ 2 cho ra kết quả mà bạn đặt tên riêng.

SO SÁNH DÃY Ô VỚI 1 Ô MẪU

Những ví dụ dưới sẽ hướng dẫn bạn cách xác định các ô trong 1 dãy có chứa đoạn văn bản giống như đoạn văn bản trong ô mẫu.

CÔNG THỨC SO SÁNH CÁC Ô VỚI ĐOẠN VĂN BẢN MẪU KHÔNG PHÂN BIỆT CHỮ HOA, CHỮ THƯỜNG

Nếu chữ hoa hay chữ thường không quan trọng, bạn có thể dùng công thức sau để so sánh các ô với mẫu:

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

Trong kiểm tra logic của hàm IF, bạn so sánh 2 số:

  • Số ô có trong 1 khoảng cụ thể (số hàng nhân với số cột)
  • Số ô có chứa giá trị giống như giá trị trong ô mẫu (kết quả được trả bởi hàng COUNTIF)

Giả sử đoạn văn bản mẫu có ô C2 và chuỗi để so sánh ở các ô ở khoảng A2:B6, công thức sẽ như sau:

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

Để kết quả hiển thị thân thiện với người dùng hơn, ví dụ như “All match” hoặc “Not all match” thay vì TRUE và FALSE, dùng hàm IF như chúng ta đã làm ở các ví dụ trước.

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

Như đã thấy ở hình trên, công thức đúng với chuỗi văn bản, ngày và số.

CÔNG THỨC SO SÁNH CÁC Ô VỚI ĐOẠN VĂN BẢN MẪU PHÂN BIỆT CHỮ HOA, CHỮ THƯỜNG

Nếu chữ hoa, chữ thường quan trọng, bạn có thể sử dụng công thức Mảng sau để so sánh các ô với mẫu:

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 khoảng ô từ A2:B6 mà đoạn văn bản mẫu trong ô C2, công thức sẽ thành như sau:

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

Không giống các công thức thông thường, công thức Mảng được thực hiện bằng cách nhấn cụm Ctrl + Shift + Enter. Nếu nhấn đúng, Excel sẽ để công thức Mảng trong dấu ngoặc móc như trong hình:

SO SÁNH 2 Ô THEO ĐỘ DÀI CHUỖI:

Đôi khi bạn cần kiểm tra xem độ dài chuỗi chữ trong mỗi hàng có bằng nhau không. Công thức cho yêu cầu này rất đơn giản. Đầu tiên, bạn lấy độ dài chuỗi chữ trong 2 ô bằng cách dùng hàm LEN, sau đó so sánh các con số.

Giả sử đoạn chữ cần so sánh ở ô A2 và B2, bạn có thể dùng 1 trong số các công thức sau:

=LEN(A2)=LEN(B2)

Hoặc

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

Như bạn đã biết, công thức thứ 1 trả kết quả là TRUE/FALSE, còn công thức thứ 2 là từ kết quả của riêng bạn.

Với hình trên, công thức đúng với đoạn văn bản, số.

Mẹo nhỏ:

Nếu 2 đoạn văn bản dường như giống nhau lại cho ra độ dài khác nhau, thì vấn đề có thể là do dấu cách ở trước hoặc sau ở 1 hoặc cả 2 ô. Trong trường hợp này, loại bỏ dấu cách thừa bằng cách sử dụng hàm TRIM.

SO SÁNH 2 Ô BẰNG SỐ LẦN XUẤT HIỆN CỦA 1 KÝ TỰ CỤ THỂ:

Đây là ví dụ cuối cùng trong bài hướng dẫn này, thể hiện cách làm cho 1 công việc cụ thể. Giả sử bạn có 2 cột chuỗi chữ có chứa 1 ký tự quan trọng. Mục tiêu của bạn là kiểm tra xem trong 2 cột ở mỗi hàng, số lần xuất hiện của ký tự đó có bằng nhau hay không.

Để hiểu rõ hơn, bạn hãy xem ví dụ sau. 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 (xem hình bên dưới). 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ể.

Để giải quyết vấn đề này, viết công thức với logic như sau.

  • Đầu tiên, thay thế mã phân biệt thành không dùng hàm SUBSTITUTE:

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

  • Sau đó, đếm xem bao nhiêu lần mã phân biệt xuất hiện trong mỗi ô. Cách làm: lấy độ dài chuỗi khi loại bỏ mã phân biệt, trừ mã khỏi tổng độ dài chuỗi. Phần này nên được viết riêng cho ô 1 và ô 2, ví dụ như:

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ố trên bằng cách đặt dấu bằng giữa 2 phần trên

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

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

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

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

Công thức trả TRUE nếu ô B2 và C2 chứa số lần xuất hiện của ký tự trong ô A2 bằng nhau, nếu không thì FALSE. Đổi thành kết quả có ích hơn với người dùng, bạn dùng hàm IF:

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

Như bạn đã thấy, công thức hoàn toàn phù hợp ngay cả khi có những trường hợp sau:

  • 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.

Các phương pháp loại bỏ dữ liệu lặp

Bài viết này, chúng tôi sẽ hướng dẫn các bạn làm thế nào để loại bỏ dữ liệu trùng lặp trong Excel 2007, Excel 2010, Excel 2013, Excel 2016. Có nhiều hơn một kĩ thuật để có thể loại bỏ được dữ liệu lặp trong 1 ô hay trong cả một dòng của Excel.

SỬ DỤNG CHỨC NĂNG REMOVE DUPLICATES CỦA EXCEL

Nếu bạn đang sử dụng một trong những phiên bản gần đây của Excel: Excel 2007, Excel 2010, Excel 2013 hoặc Excel 2016 thì bạn có thể dễ dàng loại bỏ dữ liệu bị lặp trong 1 ô, 1 dòng hoặc loại bỏ dữ liệu lặp trên 1 dòng dựa trên 1 vài cột. Cụ thể chúng ta theo những bước sau đây để thực hiện:

  1. Chú ý, công cụ Remove Duplicates trong Excel sẽ xoá những dữ liệu lặp lại, nên tốt nhất hãy làm 1 bản copy của dữ liệu đó trước khi thực hiện những bước tiếp theo.
  2. Đầu tiên, chọn toàn bộ vùng dữ liệu muốn loại bỏ sự lặp lại bằng cách để con trỏ chuột vào bất cứ ô nào trong vùng rồi sử dụng phím tắtCTRL + A.
  3. Trên thanh menu của Excel, chọn thẻ Data nhóm *Data Tools** rồi bấm nút Remove Duplicates
  1. Cửa sổ Remove Duplicates xuất hiện, tại đây bạn chọn cột cần loại bỏ dữ liệu trùng lặp. Nếu bảng của bạn có tiêu đề thì tick dấu chọn ở mục My data has headers
  2. Sau khi bấm OK bạn sẽ nhận được thông báo có bao nhiêu dữ liệu lặp và đã được loại bỏ, còn lại bao nhiêu dòng dữ liệu là duy nhất.

LOẠI BỎ DỮ LIỆU LẶP BẰNG CÁCH COPY DỮ LIỆU DUY NHẤT RA VÙNG KHÁC

Có một cách khác nữa để loại bỏ dữ liệu lặp là copy dữ liệu không bị trùng lặp ra một nơi khác. Cách làm như sau:

  1. Chọn toàn bộ bảng hoặc vùng cần thực hiện thao tác copy
  2. Trong Excel, chọn thẻ Data, trong nhóm Sort & Filter chọn Advanced
  3. Trong cửa sổ Advanced Filter, hãy thực hiện các bước sau đây:
  • Chọn ô Copy to another location
  • Mục List Range là dữ liệu đã được chọn ở bước số 1
  • Mục Copy to chọn nơi sẽ copy những dữ liệu không có sự trùng lặp
  • Đánh dấu chọn ở hộp kiểm Unique records only
  1. Cuối cùng, click chọn OK và dữ liệu không trùng lặp đã được copy ra 1 nơi khác

SỬ DỤNG CÔNG THỨC VÀ KẾT HỢP VỚI CHỨC NĂNG LỌC DỮ LIỆU

Một cách nữa để có thể loại bỏ, tách dữ liệu lặp lại ra khỏi dữ liệu thô đó là cách sử dụng cột phụ, dùng công thức để biết được dữ liệu nào lặp, dữ liệu nào không lặp như trong hình sau:

Ở đây, chúng ta sẽ kết hợp công thức COUNTIFS với câu điều kiện IF để kiểm tra dữ liệu lặp hay không dựa vào 3 cột A, B và C trong dữ liệu, nếu kết quả của hàm COUNTIFS lớn hơn 1 tức là dữ liệu đã lặp lại, ngược lại, dữ liệu là duy nhất.

Chú ý ở đây chúng ta phân biệt 2 trường hợp:

  1. Công thức để kiểm tra dữ liệu lặp không bao gồm lần đầu tiên dữ liệu xuất hiện:=IF(COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2, $C$2:$C2, $C2)>1, "Duplicate row", "Unique")
  2. Công thức để kiểm tra dữ liệu lặp có bao gồm lần đầu tiên dữ liệu xuất hiện:=IF(COUNTIFS($A$2:$A$10, $A2, $B$2:$B$10, $B2, $C$2:$C$10, $C2)>1, "Duplicate row", "Unique")

Sau khi đã hoàn thành cột phụ để phân biệt được dữ liệu duy nhất và dữ liệu lặp. Việc còn lại chúng ta phải làm là sử dụng chức năng Filter quen thuộc của Excel để có thể loại bỏ được dữ liệu bị lặp:

  1. Chọn toàn bộ dữ liệu bao gồm cả cột vừa tạo
  2. Trong thẻ Data, bấm nút Filter hình cái phễu.
  3. Bấm chọn mục Duplicate row rồi bấm OK
  4. Sau đó, chúng ta đi xoá những dòng dữ liệu bị trùng bằng cách, chọn những dòng còn lại sau quá trình lọc, nhấn chuột phải và bấm Delete Row từ menu chuột phải.

Một cách tương tự, bạn cũng có thể sử dụng cách này để xoá dữ liệu lặp lại ở một lần cụ thể nào đó, dù dữ liệu đó có lặp lần thứ 3, lần thứ 4, lần thứ 5 v.v.