Trong quá trình làm việc với Excel, chắc chắn đã có lúc bạn tìm kiếm những lỗi sai của các hàm dò tìm VLOOKUP nâng cao, vất vả đi tìm lỗi khi tổng kết báo cáo dữ liệu với SUMIFS hoặc SUMPRODUCT. Cuối cùng, bạn phát hiện ra lý do cho việc công thức không hoạt động là những khoảng trắng trong dữ liệu. Trong bài này, H.E.O sẽ giúp bạn xử lý những dữ liệu có lẫn khoảng trắng này, đây là một trong những kĩ thuật Excel nâng cao khi làm việc với dữ liệu.

Các trường hợp kí tự dấu cách bị lẫn trong dữ liệu

Chúng ta sẽ bắt đầu từ 1 ví dụ đơn giản trước.

Qua ví dụ này, chúng ta phân biệt 3 trường hợp lẫn dấu cách ở trong dữ liệu

1. Trường hợp 1: dấu cách ở cuối nội dung (Trailing space) – ô A2
2. Trường hợp 2: dấu cách ở đầu nội dung (Leading space) – ô A5
3. Trường hợp 3: dấu cách ở giữa nội dung (in-between space) – ô C2

VBA101 – VBA cơ bản dành cho người mới bắt đầu

So sánh dữ liệu để phát hiện phát hiện dấu cách hoặc kí tự không thấy được

Cách đơn giản nhất để phát hiện và so sánh 2 ô dữ liệu A1, A2 có giống nhau hay không, các bạn sử dụng công thức sau:

=A1=A2

Kết quả của phép so sánh này là FALSE có nghĩa là dữ liệu 2 ô này không hoàn toàn giống nhau, tuy chúng trông giống nhau. Kiểm tra lại 1 lần nữa bằng hàm LEN chúng ta thấy được rằng =LEN(A1) cho kết quả là 6, nhưng =LEN(A2) cho kết quả là 7.

Khóa học excel nâng cao Hà Nội

Cách loại bỏ khoảng trắng, kí tự khoảng trắng trong dữ liệu

Trong Excel, có 1 hàm sẽ giúp chúng ta loại khoảng trắng ở cả 3 trường hợp: leading space, trailing space và in-between space, đó chính là hàm TRIM. Cách sử dụng hàm TRIM như sau:

Để loại bỏ khoảng trắng của dữ liệu trong ô A2, ở trong 1 ô bất kì, nhập công thức sau:

=TRIM(A2)

Xoá khoảng trắng rồi nhưng vẫn lỗi

Khi dữ liệu đến từ những hệ thống, những nguồn bên ngoài Excel, chúng ta hay gặp trường hợp, không chỉ những khoảng trắng, mà còn nhiều loại kí tự không nhìn thấy hoặc kí tự không in được (non-printing characters) được bị lẫn trong dữ liệu: kí tự xuống dòng, kí tự tab, v.v …

Hàm TRIM hoạt động rất tốt với những kí tự khoảng trắng, nhưng với những kí tự khác, hàm TRIM không có tác dụng. Nhằm làm sạch dữ liệu, Excel đã cung cấp cho chúng ta hàm CLEAN. Ví dụ về cách sự dụng hàm CLEAN như sau:

Trong ví dụ này, chúng ta kết hợp hàm CLEAN và hàm TRIM để có thể xử lý triệt để những khoảng trắng hay những kí tự không in được ra khỏi dữ liệu địa chỉ.

Kết hợp hàm CLEAN và TRIM rồi nhưng vẫn có lỗi

Sau khi kết hợp hàm CLEAN và hàm TRIM có thể bạn vẫn sẽ gặp lại những lỗi khó chịu như #NAME?, #REF. Chúng ta sẽ phải xử lý dữ liệu như sau:

=SUBSTITUTE(SUBSTITUTE(A2, CHAR(13),” “), CHAR(10), ” “)

Công thức trên giúp chúng ta thay thế 2 loại kí tự xuống dòng Carriage Return (Char(13)) và Line Feed (Char(10)) với kí tự khoảng trắng.

Copy dữ liệu từ Web vào Excel bị lỗi

Nếu bạn copy dữ liệu từ Web vào Excel và bị lỗi khoảng trắng, nhiều khả năng bạn đã gặp phải những khoảng trắng gọi là “non-breaking spaces” ( ). Để loại bỏ những kí tự khoảng trắng từ web này, chúng ta sử dụng công thức như sau:

=TRIM(SUBSTITUTE(A2, CHAR(160), ” “))

Công thức phía trên sẽ thay những kí tự khoảng trắng có mã 160 trong bảng mã ASCII bằng khoảng trắng, sau đó, chúng ta sẽ lại sử dụng hàm TRIM để thay thế những khoảng trắng bị thừa.

Nếu bảng tính của các bạn còn chứa những kí tự không in được, chúng ta sẽ sử dụng hàm CLEAN cùng với hàm TRIM và hàm SUBSTITUTE như sau để loại bỏ được tất cả những kí tự không nên có trong dữ liệu như sau

=TRIM(CLEAN((SUBSTITUTE(A2,CHAR(160),” “))))

Ảnh chụp sau đây sẽ giúp bạn hiểu được sự khác nhau khi kết hợp các hàm này

Như vậy, hi vọng với các kĩ thuật trên, các bạn đã có một bảng dữ liệu sạch, đẹp để có thể bắt đầu tạo những báo cáo đẹp nhất

Rất nhiều kiến thức phải không nào? Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: HocExcel.Online