Nhằm giúp các bạn hình thành tư duy tốt trong việc tổ chức dữ liệu và lập báo cáo trên Excel, Học Excel Online xin giới thiệu với các bạn seri bài viết về “Kỹ năng tổ chức, quản lý dữ liệu và lập báo cáo trên Excel”. Bài viết của tác giả Dương Mạnh Quân – Giảng viên của Học Excel Online, dựa trên những kinh nghiệm giảng dạy trong thực tế của thầy liên quan tới nội dung này. Mời các bạn cùng tìm hiểu nhé.

Bài 5: Hướng dẫn cách xử lý lỗi dữ liệu dạng Text trong Excel

Dữ liệu dạng chuỗi ký tự (Text) là dạng thường gặp và cũng thường mắc lỗi nhất trong quá trình nhập dữ liệu trên Excel. Nguyên nhân chủ yếu của việc này có thể kể ra như:

• Thói quen xấu khi nhập xong hay thêm dấu cách thừa ở cuối ký tự. Về hiển thị chúng ta không thấy dấu cách thừa này, nhưng Excel đã hiểu việc có dấu cách hay không có dấu cách là khác nhau về nội dung của toàn bộ chuỗi ký tự trong ô.
• Không cẩn thận trong việc nhập dữ liệu khiến nhập sai chính tả
• Thiếu biện pháp kiểm soát quá trình nhập dữ liệu dẫn tới không biết nhập đúng hay sai, đặc biệt với những nội dung theo 1 danh sách cho trước.

Sau đây chúng ta cùng tìm hiểu về kỹ thuật xử lý lỗi dữ liệu dạng Text trong Excel, bao gồm: Tìm vị trí lỗi và kỹ thuật sửa lỗi

1. Các kỹ thuật tìm lỗi ký tự Text

a. Sử dụng AutoFilter

Tương tự như cách sử dụng AutoFilter tìm lỗi ký tự dạng Date, Number thì AutoFilter cũng hỗ trợ chúng ta trong việc tìm lỗi ký tự Text. Dựa theo quy luật hiển thị dữ liệu dạng Text trong AutoFilter như sau:

• Sắp xếp tự động theo thứ tự từ A đến Z
• Mỗi nội dung giống nhau sẽ chỉ xuất hiện 1 lần trên bộ lọc tự động.

Dựa trên 2 đặc điểm này chúng ta có thể thấy dữ liệu Text bị sai thường là:

• Lặp lại nhiều lần trên cùng 1 vị trí ký tự (không được gộp tự động)
• Không nằm đúng vị trí thứ tự ký tự

Ví dụ như sau:

Trong hình trên chúng ta thấy tên “Đào Thanh Thúy” xuất hiện 2 lần bởi trong nội dung này có xuất hiện dấu cách thừa

b. Sử dụng chức năng Conditional Formatting để so sánh nội dung trong bảng với 1 danh sách đã có

Giả sử ở đây chúng ta có 1 danh sách nhân viên tại cột G

Sử dụng Conditional Formatting để so sánh dữ liệu nhập vào ở cột B với dữ liệu ở cột G như sau:

Sử dụng hàm COUNTIF để đếm mỗi giá trị ở cột B (bắt đầu từ B2), so sánh trong vùng danh sách ở cột G (cố định vùng G2:G7)

Khi đó kết quả như sau:

Ở ô B6 không tìm thấy trong danh sách nên không được định dạng tô màu

Để làm ngược lại: Tên sai thì tô màu

Sửa công thức trong Conditional Formatting như sau:

=COUNTIF($G$2:$G$7,$B2)=0

Tức là: nếu không tìm thấy theo danh sách thì sẽ tô màu (Không tìm thấy tức là kết quả hàm COUNTIF = 0)

2. Các kỹ thuật sửa lỗi dữ liệu Text

Trước khi sửa lỗi dữ liệu, chúng ta cần tuân thủ 1 nguyên tắc: Copy dữ liệu rồi sửa trên bản Copy. Sau khi kiểm tra lại kết quả sửa đã hoàn thành thì mới Copy kết quả đúng trả về bảng dữ liệu.

Mục đích: Tránh việc mất dữ liệu gốc khi thao tác sửa chưa hoàn thiện sẽ không có căn cứ đối chiếu, so sánh.

a. Sử dụng Replace để loại bỏ ký tự thừa

Những ký tự thừa như các dấu ( . , / ‘ ; ) mà trong quá trình nhập dữ liệu do bất cẩn thêm vào thì chúng ta có thể xóa bỏ bằng cách:

• Find what: Nhập ký tự cần xóa bỏ
• Replace with: không nhập nội dung gì

Bấm Replace All

b. Sử dụng bộ lọc để lọc những lỗi phát hiện bởi Conditional Formatting

Sử dụng AutoFilter và tận dụng chức năng lọc theo màu để lọc những nội dung lỗi được phát hiện bởi chức năng Conditional Formatting đã trình bày ở trên: Sử dụng tính năng Lọc theo màu trong AutoFilter

Sau khi lọc ra những nội dung lỗi chúng ta có thể sửa trực tiếp bằng tay.

c. Sử dụng Remove Duplicate để tạo danh sách không trùng

Theo nguyên tắc Dữ liệu đúng thì không xuất hiện 2 lần trong danh sách của AutoFilter nên chúng ta sẽ thực hiện như sau:

• Bước 1: Sắp xếp danh sách cần sửa theo thứ tự từ A đến Z với công cụ Sort trong tab Data

• Bước 2: Lọc bỏ các giá trị trùng với công cụ Remove Duplicate trong tab Data

Sau mỗi 1 lần lọc bỏ giá trị trùng, chúng ta sẽ thu được 1 danh sách mới chỉ bao gồm các giá trị không trùng.

Nếu danh sách đó vẫn còn lỗi thì tiếp tục sửa > Sắp xếp > Loại bỏ giá trị trùng cho tới khi thu được danh sách không trùng và không có lỗi.

Khi hoàn thành bước này thì chúng ta kiểm tra lại trên danh sách gốc để đối chiếu với danh sách không trùng theo cách sử dụng Conditional formatting đối chiếu dữ liệu trong bảng với danh sách bên ngoài => Phát hiện vị trí lỗi => Lọc các vị trí lỗi và sửa.

Bài tiếp theo: Kỹ thuật loại bỏ giá trị trùng lặp trong danh sách

Ngoài ra Học Excel Online xin giới thiệu với các bạn “Khóa học Excel từ cơ bản tới nâng cao dành cho người đi làm“. Đây là 1 khóa học rất đầy đủ kiến thức và bổ trợ rất tuyệt vời cho bạn trong việc làm quen với Excel, sử dụng các công cụ, các hàm trong Excel để đáp ứng yêu cầu công việc tổ chức, quản lý dữ liệu và lập báo cáo trên Excel.