Bạn thường xuyên mất nhiều thời gian để làm sạch dữ liệu trong Excel? Những hàng hay cột trống thừa có thể khiến bảng tính trở nên lộn xộn và khó quản lý, đặc biệt khi bạn làm việc với dữ liệu được nhập khẩu hoặc do người dùng điền vào. Trước đây, việc này đòi hỏi nhiều bước thủ công, đôi khi phải dùng đến các script phức tạp. Tuy nhiên, với sự xuất hiện của hàm TRIMRANGE mới trong Excel, nỗi lo này sẽ được giải quyết một cách hiệu quả và đơn giản đáng kinh ngạc. Đây thực sự là một cứu cánh nếu bạn thường xuyên xử lý các bộ dữ liệu “bẩn” và cần một giải pháp tức thời, tự động.
Hàm TRIMRANGE trong Excel là gì?
Hãy tưởng tượng bạn vừa nhập một bộ dữ liệu vào Excel và nhận thấy có nhiều hàng và cột trống ở đầu hoặc cuối phạm vi dữ liệu. Chúng làm cho bảng tính trở nên lộn xộn và bạn muốn loại bỏ chúng chỉ trong một thao tác thay vì thực hiện thủ công từng cái một. Đó chính là lúc hàm TRIMRANGE phát huy tác dụng, cho phép bạn xóa các hàng và cột trống thừa ở đầu (leading) và cuối (trailing) phạm vi, chỉ để lại phần dữ liệu liên quan.
Dưới đây là cú pháp của hàm TRIMRANGE:
TRIMRANGE(range_to_trim, [rows_to_trim], [columns_to_trim])Tham số range_to_trim là phạm vi bạn muốn làm sạch. Tham số rows_to_trim có các giá trị khả dụng như sau:
- 0: Không loại bỏ hàng nào.
- 1: Loại bỏ các hàng trống ở đầu.
- 2: Loại bỏ các hàng trống ở cuối.
- 3 (mặc định): Loại bỏ cả hàng trống ở đầu và cuối.
Tham số columns_to_trim có cùng các giá trị (chúng chỉ áp dụng cho cột thay vì hàng).
Lưu ý quan trọng: Hàm TRIMRANGE chỉ loại bỏ các hàng và cột trống thừa ở đầu hoặc cuối phạm vi, chứ không loại bỏ các hàng hay cột trống nằm bên trong phạm vi dữ liệu.
Hướng dẫn sử dụng hàm TRIMRANGE trong Excel
Sau khi đã hiểu rõ hàm TRIMRANGE là gì và cách thức hoạt động của nó, hãy cùng xem xét một số trường hợp sử dụng thực tế.
Cách dùng cơ bản của hàm TRIMRANGE
Để minh họa, tôi sẽ làm cho cột A lấy giá trị từ cột E, vốn chứa một danh sách tên. Tôi nhập công thức sau vào ô A1:
=E:ESau khi nhấn Enter, cột A sẽ được điền đầy đủ các tên từ cột E. Tuy nhiên, vì chúng ta tham chiếu toàn bộ cột E, bao gồm cả các ô trống, cột A sẽ hiển thị các số 0 ở những vị trí có ô trống.
Hình ảnh cột dữ liệu động trong Excel với các số 0 thừa ở cuối chưa được làm sạch
Chúng ta có thể dễ dàng loại bỏ những số 0 này bằng cách bao quanh phạm vi bằng hàm TRIMRANGE. Dưới đây là cách thực hiện:
=TRIMRANGE(E:E)Vì chúng ta đã nhập phạm vi là tham số duy nhất trong hàm, nó sẽ tự động loại bỏ cả số 0 thừa ở đầu và cuối trong cột A. Nếu chúng ta muốn chỉ loại bỏ các ô trống thừa ở cuối cột, chúng ta có thể nhập công thức sau:
=TRIMRANGE(E:E, , 2)Tôi đã bỏ trống tham số ở giữa vì chúng ta không có hàng để loại bỏ (hãy nhớ rằng tham số này là tùy chọn). Giờ đây, các ô trống thừa ở cuối sẽ biến mất khỏi cột A, giúp bảng tính Excel trông sạch sẽ hơn rất nhiều. Hơn nữa, vì đây là một phạm vi động, bạn có thể thêm và xóa giá trị trong cột E, và nó sẽ tự động mở rộng hoặc thu hẹp mà không để lại bất kỳ số 0 nào.
Cột dữ liệu động trong Excel đã được làm sạch số 0 thừa bằng hàm TRIMRANGE
Kết hợp TRIMRANGE với các hàm Excel khác
Bạ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ả và phân tích thú vị. Thậm chí, bạn có thể sử dụng nó để tránh 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.
Dưới đây là ví dụ về một bảng tính trông sẽ tệ như thế nào khi hàm XLOOKUP trả về lỗi:
Bảng tính Excel hiển thị lỗi #N/A từ hàm XLOOKUP khi dữ liệu chưa được xử lý
Hiện tại, công thức XLOOKUP trông như thế này:
=XLOOKUP(A2:A12, H2:H11, G2:G11)Để khắc phục điều này, chúng ta hãy bao quanh phạm vi tra cứu (tham số đầu tiên) bằng hàm TRIMRANGE. Công thức sẽ trông như sau:
=XLOOKUP(TRIMRANGE(A2:A12), H2:H11, G2:G11)Như bạn có thể thấy, điều này sẽ loại bỏ ngay lập tức những lỗi đó, và bạn vẫn có thể tiếp tục mở rộng phạm vi một cách linh hoạt.
Hàm XLOOKUP đã loại bỏ lỗi #N/A hiệu quả bằng cách kết hợp với hàm TRIMRANGE trong Excel
Sử dụng TRIMRANGE trong Data Validation
Vì hàm TRIMRANGE là một cách tuyệt vời để làm việc với dữ liệu sạch, nó có thể rất hữu ích ở những nơi khác, chẳng hạn như trong tính năng Data Validation (Xác thực dữ liệu).
Giả sử bạn đang tạo một danh sách thả xuống mà bạn muốn mở rộng sau này. Bạn sẽ tạo một phạm vi động bằng cách bao gồm thêm các hàng trống để thêm giá trị trong tương lai. Nhưng điều này sẽ làm cho 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 có khoảng trống thừa ở cuối chưa được tối ưu
Bạn có thể loại bỏ khoảng trống đó bằng cách thêm hàm TRIMRANGE vào trường Source (Nguồn) của danh sách.
Thiết lập Data Validation trong Excel sử dụng hàm TRIMRANGE để làm sạch nguồn dữ liệu
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.
Danh sách thả xuống trong Excel đã loại bỏ khoảng trống thừa sau khi áp dụng hàm TRIMRANGE
Đơn giản hóa hàm TRIMRANGE với Dot Operator
Như bạn đã thấy, hàm TRIMRANGE vốn đã rất dễ sử dụng, nhưng bạn còn có thể làm cho nó đơn giản hơn nữa với Trim References (Tham chiếu cắt gọn) hay còn gọi là Dot Operator. Cách sử dụng chúng đơn giản như việc thêm một toán tử dấu chấm (.) vào một hoặc cả hai phía của dấu hai chấm trong phạm vi.
Điều này sẽ loại bỏ cả các ô trống ở đầu và cuối trong cột:
=E.:.EĐiều này sẽ loại bỏ các ô trống ở đầu trong cột:
=E.:EĐiều này sẽ loại bỏ các ô trống ở cuối trong cột:
=E:.ENgay cả ví dụ tôi đã đưa ra trước đó với XLOOKUP cũng có thể trông gọn gàng hơn nhiều, như sau:
=XLOOKUP(A2.:.A12, H2:H11, G2:G11)Nếu không có hàm TRIMRANGE, người dùng thường phải dùng đến các giải pháp thay thế như hàm TOCOL và OFFSET. Tuy nhiên, vì những hàm này không được thiết kế riêng cho việc loại bỏ hàng trống, bạn cần sử dụng chúng một cách cẩn thận để tránh phát sinh lỗi. Hàm TRIMRANGE mang đến một cách tiếp cận dễ dàng và linh hoạt hơn rất nhiều để dọn dẹp bảng tính Excel, đặc biệt với sự bổ sung của Trim Refs.
