5 hàm giúp dọn dẹp ngay lập tức bảng tính Excel lộn xộn

Một bảng tính Excel lộn xộn có thể là cơn ác mộng - khoảng trắng thừa, định dạng không nhất quán, văn bản lộn xộn khắp nơi. Nhưng với các hàm phù hợp, bạn có thể dọn dẹp nó chỉ trong vài giây và làm cho dữ liệu của mình thực sự hữu ích.

5. Hàm TRIM

Khoảng trắng thừa trên bảng tính có thể khiến hai mục nhập trông giống hệt nhau trở nên khác biệt, gây rối loạn việc xác thực dữ liệu và thậm chí làm sai lệch công thức của bạn. May mắn thay, hàm TRIM giải quyết tất cả những điều đó bằng cách loại bỏ tất cả khoảng trắng thừa khỏi văn bản (trừ các khoảng trắng đơn giữa các từ). Hàm này rất hữu ích để sắp xếp lại tên, địa chỉ, v.v..., đặc biệt là trong các trang tính bạn nhập từ những nguồn khác.

Giả sử ô A1 chứa Alice, với khoảng trắng thừa ở khắp mọi nơi. Sử dụng hàm TRIM sẽ trả về Alice không có khoảng trắng.

Thay vì tham chiếu đến một ô và mở rộng công thức, bạn cũng có thể tham chiếu đến tất cả dữ liệu cùng một lúc. Ví dụ, bạn có thể sử dụng công thức sau để xóa tất cả các khoảng trắng thừa.

=TRIM(A1:G5)​​​

4. Hàm IFERROR

IFERROR hiển thị một cái gì đó dễ nhìn và ít gây lo lắng hơn. Nó hoạt động với các gói đăng ký Office 2019 và Microsoft 365, vì vậy nó có thể truy cập rộng rãi. Hàm này đánh giá một công thức và trả về một giá trị được chỉ định nếu công thức đó tạo ra lỗi.

Ví dụ, thay vì sử dụng công thức trả về #NAME? khi không tìm thấy kết quả khớp, chẳng hạn như bên dưới:

=VLOOKUP(A1,Table1,2,FALSE)

Bạn có thể sử dụng công thức sau để trả về thông báo tùy chỉnh:

=IFERROR(VLOOKUP(A1,Table1,2,FALSE),"Not Found")

3. Hàm CLEAN

Khi nhập dữ liệu từ PDF, trang web và các hệ thống cũ hoặc thậm chí chuyển đổi file PDF thành bảng tính Excel, bạn thường mang theo một số ký tự vô hình. Những thứ như ngắt dòng và ký hiệu ẩn, mà bạn không thể nhìn thấy, có thể gắn liền và làm hỏng công thức của bạn.

Hàm CLEAN loại bỏ tất cả các ký tự không in được khỏi văn bản, để lại một phiên bản dễ đoán hơn và dễ sử dụng hơn cho công thức.

Bạn cũng có thể kết hợp hàm CLEAN với hàm TRIM. Nhiều chuyên gia Excel sử dụng hàm này làm tổ hợp phím tắt cho dữ liệu nhập:

=TRIM(CLEAN(text))

2. Hàm TEXTSPLIT

TEXTSPLIT, có sẵn trong Excel 365 và Excel 2021, hoạt động tương tự như trình hướng dẫn Text-to-Columns, nhưng ở dạng công thức, và rất hữu ích nếu bạn đang xử lý dữ liệu được nối. Hàm này có thể chia văn bản thành các cột hoặc xuống các hàng bằng cách sử dụng những dấu phân cách do bạn chỉ định.

Nếu ô A1 chứa Ada,Uche,ada.uche@email.com, thì việc sử dụng công thức bên dưới có thể chia văn bản thành 3 cột riêng biệt: Ada, Uche và ada.uche@email.com.

=TEXTSPLIT(A1,",")

Bạn thậm chí có thể chia theo hàng hoặc cột và xử lý nhiều dấu phân cách. Ví dụ, nếu ô A2 chứa Apple,Banana;Cherry, hãy sử dụng công thức dưới đây:

=TEXTSPLIT(A1, {",", ";"})

Công thức này sẽ chia văn bản thành Apple, Banana và Cherry.

Theo mặc định, TEXTSPLIT sẽ điền những giá trị này vào các cột. Nếu bạn muốn chúng được chia thành các hàng riêng biệt, hãy đặt đối số thứ tư, mặc định là FALSE hoặc bị bỏ qua, thành TRUE:

=TEXTSPLIT(A2, ",",";", , TRUE)

Kết quả sẽ là Apple trong ô I1, Banana trong ô J1, Cherry trong ô I2 và Dates trong ô J2.

1. Hàm TEXTJOIN

Sau khi tách, bạn có thể muốn ghép các phần lại với nhau. TEXTJOIN giúp bạn hợp nhất nhiều chuỗi văn bản với dấu phân cách tùy chọn và thậm chí cho phép bạn bỏ qua các khoảng trắng.

Giả sử bạn có tên trong cột A, tên đệm trong cột B (một số ô trống) và họ trong cột C. Bạn có thể sử dụng công thức sau để tạo tên đầy đủ mà không có khoảng trắng thừa từ các ô tên đệm trống.

=TEXTJOIN(" ",TRUE,A1,B1,C1)

Bạn sẽ nhận được tên đầy đủ như Cardi B hoặc Mary Jane Watson sau khi áp dụng công thức.

Đối số TRUE yêu cầu Excel bỏ qua các ô trống, nhờ đó bạn sẽ không gặp phải những khoảng trống khó coi hay dấu phẩy lơ lửng.