Bạn có thường xuyên phải đối mặt với những bảng tính Excel lộn xộn, chứa đầy các hàng hoặc cột trống không mong muốn sau khi nhập liệu hay trích xuất dữ liệu? Việc dọn dẹp thủ công không chỉ tốn thời gian mà còn dễ gây ra lỗi. May mắn thay, Excel đã âm thầm giới thiệu một hàm mới đầy quyền năng: TRIMRANGE. Hàm này giải quyết triệt để nỗi đau chung của nhiều người dùng, đặc biệt là những ai thường xuyên làm việc với dữ liệu nhập hoặc được tạo bởi người dùng khác. TRIMRANGE hứa hẹn sẽ là một “vị cứu tinh”, giúp bạn làm sạch dữ liệu nhanh chóng và hiệu quả mà không cần đến các bước phức tạp hay script rắc rối.
TRIMRANGE trong Excel là gì?
Hãy tưởng tượng bạn vừa nhập một tập dữ liệu lớn vào Excel và nhận thấy có nhiều hàng và cột trống không cần thiết xung quanh phạm vi dữ liệu chính. Chúng làm cho bảng tính trở nên bừa bộn và bạn muốn loại bỏ chúng chỉ trong một thao tác duy nhất thay vì thực hiện thủ công từng cái. Đó chính là lúc hàm TRIMRANGE phát huy tác dụng. Hàm này cho phép bạn loại bỏ các hàng và cột trống dẫn đầu (leading) và cuối cùng (trailing) của một phạm vi, chỉ giữ lại phần dữ liệu liên quan.
Cú pháp của hàm TRIMRANGE như sau:
TRIMRANGE(range_to_trim, [rows_to_trim], [columns_to_trim])Trong đó:
range_to_trim: Là tham số bắt buộc, chỉ định phạm vi ô mà bạn muốn làm sạch.rows_to_trim: Là tham số tùy chọn, xác định cách thức loại bỏ các hàng trống. Các giá trị có thể là:- 0: Không loại bỏ hàng nào.
- 1: Loại bỏ các hàng trống dẫn đầu (ở phía trên cùng) của phạm vi.
- 2: Loại bỏ các hàng trống cuối cùng (ở phía dưới cùng) của phạm vi.
- 3 (mặc định): Loại bỏ cả hàng trống dẫn đầu và cuối cùng.
columns_to_trim: Là tham số tùy chọn, có các giá trị tương tự nhưrows_to_trim, nhưng áp dụng cho việc loại bỏ các cột trống dẫn đầu (phía bên trái) và cuối cùng (phía bên phải) của phạm vi.
Lưu ý quan trọng: Hàm TRIMRANGE chỉ loại bỏ các hàng và cột trống ở phần đầu hoặc cuối của phạm vi dữ liệu được chỉ định, chứ không loại bỏ các hàng và cột trống nằm xen kẽ bên trong phạm vi đó.
Hướng dẫn sử dụng hàm TRIMRANGE trong Excel
Sau khi đã hiểu rõ về khái niệm và cú pháp của hàm TRIMRANGE, hãy cùng xem xét một số trường hợp sử dụng thực tế để thấy rõ hiệu quả của nó.
Cách sử dụng TRIMRANGE cơ bản
Trong ví dụ này, chúng ta sẽ tạo một cột A động (dynamic range) lấy giá trị từ cột E, vốn chứa một danh sách tên. Khi nhập công thức =E:E vào ô A1 và nhấn Enter, cột A sẽ được điền các tên từ cột E. Tuy nhiên, vì chúng ta đang tham chiếu toàn bộ cột E, bao gồm cả các ô trống, nên cột A sẽ xuất hiện các giá trị 0 tại những vị trí ô trống tương ứng.
Dải động trong Excel chứa các giá trị 0 và ô trống ở cuối
Chúng ta có thể dễ dàng loại bỏ những số 0 thừa này bằng cách lồng phạm vi E:E vào hàm TRIMRANGE. Công thức sẽ trông như sau:
=TRIMRANGE(E:E)Khi bạn chỉ điền phạm vi làm tham số duy nhất trong hàm, Excel sẽ mặc định loại bỏ cả các ô trống dẫn đầu và cuối cùng trong cột A. Nếu bạn chỉ muốn loại bỏ các ô trống ở cuối cột, bạn có thể nhập công thức sau:
=TRIMRANGE(E:E, , 2)Ở đây, chúng ta để trống tham số thứ hai (áp dụng cho hàng) vì chúng ta không có hàng nào để cắt (hãy nhớ rằng đây là tham số tùy chọn). Kết quả là các ô trống ở cuối cột A sẽ biến mất, làm cho bảng tính Excel của bạn trông gọn gàng hơn rất nhiều. Hơn nữa, vì đây là một dải động, bạn có thể thêm hoặc xóa giá trị trong cột E và cột A sẽ tự động mở rộng hoặc thu hẹp mà không để lại bất kỳ số 0 hay ô trống nào.
Kết quả làm sạch dải động sau khi áp dụng hàm TRIMRANGE loại bỏ ô trống
Kết hợp TRIMRANGE với các hàm Excel khác (Ví dụ: XLOOKUP)
Bạn hoàn toàn có thể kết hợp hàm TRIMRANGE với các hàm Excel khác để đạt được những kết quả thú vị và phân tích sâu hơn. Thậm chí, việc này còn giúp bạn tránh được các lỗi thường gặp. Ví dụ, hàm XLOOKUP trong Excel có thể trả về lỗi #N/A nếu nó không tìm thấy giá trị mong muốn trong phạm vi tra cứu.
Dưới đây là một ví dụ về một bảng tính trông sẽ tệ thế nào khi XLOOKUP trả về lỗi:
Hàm XLOOKUP trong Excel trả về lỗi #N/A khi dải tìm kiếm có ô trống
Hiện tại, công thức XLOOKUP của chúng ta có thể trông như thế này:
=XLOOKUP(A2:A12, H2:H11, G2:G11)Để khắc phục lỗi này, chúng ta sẽ lồng phạm vi tra cứu (tham số đầu tiên) vào hàm TRIMRANGE. Đây là công thức sau khi điều chỉnh:
=XLOOKUP(TRIMRANGE(A2:A12), H2:H11, G2:G11)Như bạn có thể thấy, công thức này sẽ loại bỏ ngay lập tức những lỗi #N/A và bạn thậm chí có thể tiếp tục mở rộng phạm vi dữ liệu của mình mà không lo phát sinh lỗi.
Khắc phục lỗi XLOOKUP bằng cách lồng hàm TRIMRANGE để làm sạch dải tìm kiếm
Ứng dụng TRIMRANGE trong Xác thực Dữ liệu (Data Validation)
Vì hàm TRIMRANGE là một công cụ tuyệt vời để làm việc với dữ liệu sạch, nó có thể hữu ích ở nhiều nơi khác, chẳng hạn như trong tính năng Xác thực Dữ liệu (Data Validation).
Giả sử bạn đang tạo một danh sách thả xuống (dropdown list) mà bạn muốn mở rộng sau này bằng cách thêm các hàng trống để chứa giá trị trong tương lai. Tuy nhiên, điều này sẽ khiến danh sách thả xuống của bạn trông hơi kỳ cục vì nó sẽ có một khoảng trống thừa ở cuối.
Danh sách thả xuống trong Excel hiển thị khoảng trắng thừa do dải dữ liệu động
Bạn có thể loại bỏ khoảng trống thừa đó bằng cách đưa hàm TRIMRANGE vào trường Source của danh sách trong Data Validation.
Thiết lập Data Validation sử dụng hàm TRIMRANGE trong trường Source để tạo danh sách sạch
Danh sách thả xuống của bạn sẽ trông gọn gàng hơn và vẫn có thể mở rộng khi cần thiết.
Kết quả danh sách thả xuống đã loại bỏ khoảng trắng thừa nhờ ứng dụng TRIMRANGE
Đơn giản hóa TRIMRANGE với Cú pháp Chấm (Dot Operator)
Như bạn đã thấy, hàm TRIMRANGE vốn đã rất dễ sử dụng, nhưng bạn có thể làm cho nó đơn giản hơn nữa với Trim References (Trim Refs) hay còn gọi là Cú pháp Chấm (Dot Operator). Việc sử dụng chúng đơn giản như việc thêm một dấu chấm (dot operator) vào một hoặc cả hai phía của dấu hai chấm trong một phạm vi.
- Để loại bỏ cả ô dẫn đầu và ô cuối cùng trong cột, bạn có thể viết:
=E.:.E - Để loại bỏ chỉ các ô dẫn đầu trong cột:
=E.:E - Để loại bỏ chỉ các ô cuối cùng trong cột:
=E:.E
Ngay cả ví dụ về XLOOKUP mà chúng ta đã đề cập trước đó cũng có thể trông gọn gàng hơn nhiều với cú pháp này:
=XLOOKUP(A2.:.A12, H2:H11, G2:G11)Trước khi có hàm TRIMRANGE, người dùng thường phải sử dụng các cách giải quyết phức tạp hơn như hàm TOCOL và OFFSET để loại bỏ hàng/cột trống. Tuy nhiên, những hàm này không được thiết kế chuyên biệt cho việc đó và cần được sử dụng cẩn thận để tránh lỗi. Hàm TRIMRANGE, đặc biệt khi kết hợp với Cú pháp Chấm, mang đến một cách dễ dàng và linh hoạt hơn rất nhiều để dọn dẹp các bảng tính Excel lộn xộn, giúp tối ưu hóa quy trình làm việc của bạn.
Với hàm TRIMRANGE, việc làm sạch dữ liệu trong Excel chưa bao giờ dễ dàng và hiệu quả đến thế. Hãy thử áp dụng hàm này vào các bảng tính của bạn để trải nghiệm sự khác biệt. Đừng ngần ngại chia sẻ những mẹo và thủ thuật Excel yêu thích của bạn hoặc cách bạn đã sử dụng TRIMRANGE để cải thiện hiệu suất công việc trong phần bình luận bên dưới nhé!
