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

Nhiều người lập tức cảm thấy mình như một tay mơ mỗi khi gặp lỗi DIV/0, N/A, VALUE!, và tất cả những lỗi Excel rắc rối khác nằm rải rác trên bảng tính. Có thể bạn không phải lúc nào cũng cảm thấy như vậy, nhưng chỉ cần nhìn thấy chúng thôi cũng khiến nhiều người vô cùng lo lắng về một vấn đề rất có thể chỉ là một sự cố nhỏ.

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

Bảng tính của bạn trông chuyên nghiệp hơn ngay lập tức, và sếp bạn sẽ không còn thắc mắc tại sao lại có thông báo lỗi khắp nơi nữa. Hơn nữa, bạn có thể trả về bất kỳ thông tin nào bạn muốn - ô trống, dấu gạch ngang, thông báo tùy chỉnh, hoặc thậm chí là các phép tính thay thế -nếu và khi xảy ra lỗi.

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

Đôi khi, tất cả dữ liệu của bạn bị nhồi nhét vào một ô duy nhất, được phân tách bằng dấu phẩy, dấu chấm phẩy, dấu chấm hoặc dấu ngắt dòng. Sau đó, việc phải trích xuất dữ liệu theo hàng và cột phù hợp theo cách thủ công thật là phiền phức.

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.

Lưu ý rằng bạn sẽ sử dụng một dấu phân cách (như dấu chấm phẩy) để tách các từ thành những cột, và một dấu khác (như dấu chấm) để chỉ ra vị trí bắt đầu của một hàng mới.

Do đó, hàm này có thể khá phức tạp. Nó thường đòi hỏi bạn phải lập kế hoạch cẩn thận cho các dấu phân cách và có thể kết hợp TEXTSPLIT với những hàm khác để đạt được bố cục mong muốn. Nhưng đối với việc trích xuất, TEXTSPLIT hoạt động rất tốt.

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.

Những bảng tính lộn xộn sẽ rất hỗn loạn cho đến khi bạn sắp xếp chúng gọn gàng. Nhưng với các hàm Excel phù hợp, bạn không cần phải mất hàng giờ sửa chữa thủ công. Vì vậy, lần tới khi mở một bảng tính khiến bạn phải nheo mắt, bạn sẽ biết chính xác phải làm gì.