Trong thế giới làm việc với dữ liệu số, Excel luôn là công cụ không thể thiếu. Tuy nhiên, khi các bảng tính trở nên phức tạp với hàng ngàn, thậm chí hàng triệu ô dữ liệu, việc quản lý và duy trì hiệu suất có thể trở thành một thách thức lớn. Các công thức động truyền thống như OFFSET, INDEX hay TOCOL thường khá phức tạp và đôi khi gây ra sự lộn xộn, hay tình trạng công thức “spill” (tràn) xuống các ô trống không cần thiết, làm tăng kích thước file và giảm tốc độ xử lý.
Để giải quyết những vấn đề này, Microsoft đã giới thiệu hàm TRIMRANGE và các toán tử Trim Ref – những công cụ mạnh mẽ giúp tự động điều chỉnh phạm vi dữ liệu, loại bỏ các ô trống thừa thãi và giữ cho bảng tính của bạn luôn gọn gàng, hiệu quả. TRIMRANGE có khả năng tự động nhận diện vùng dữ liệu bạn đang sử dụng và co giãn linh hoạt theo đó, trong khi Trim Ref Operators cung cấp một cú pháp viết tắt đơn giản hơn để đạt được cùng mục đích. Bài viết này sẽ đi sâu vào cách sử dụng và những lợi ích mà TRIMRANGE và Trim Ref Operators mang lại cho công việc của bạn.
Hàm TRIMRANGE trong Excel: Cú pháp và Cách Hoạt Động
Hàm TRIMRANGE được thiết kế để “cắt” bỏ các hàng hoặc cột trống ở đầu và cuối một phạm vi dữ liệu, giúp bạn làm việc hiệu quả hơn mà không cần phải thủ công điều chỉnh phạm vi.
Cú pháp của hàm TRIMRANGE như sau:
=TRIMRANGE(a,b,c)Trong đó:
a(bắt buộc): Là phạm vi dữ liệu mà bạn muốn cắt.b(tùy chọn): Xác định các hàng cần cắt (0 = không cắt hàng, 1 = cắt hàng đầu, 2 = cắt hàng cuối, 3 = cắt cả hàng đầu và cuối).c(tùy chọn): Xác định các cột cần cắt (0 = không cắt cột, 1 = cắt cột đầu, 2 = cắt cột cuối, 3 = cắt cả cột đầu và cuối).
Nếu bạn bỏ qua đối số b và/hoặc c, Excel sẽ mặc định cắt cả hàng đầu và cuối, và/hoặc cột đầu và cuối.
Lưu ý quan trọng: Tại thời điểm hiện tại (tháng 1 năm 2025), hàm TRIMRANGE không cho phép bạn cắt bỏ các hàng hoặc cột trống nằm giữa các dữ liệu đã có. Hàm này chỉ có tác dụng cắt bỏ dữ liệu trước hoặc sau phạm vi dữ liệu thực tế của bạn. Để xóa các hàng trống giữa các dữ liệu, bạn cần sử dụng các phương pháp khác trong Excel.
Ví dụ Thực Tế 1: Tối Ưu Phép Tính Trừ với TRIMRANGE
Hãy xem xét một ví dụ thực tế. Giả sử bạn có một bảng dữ liệu chứa mã sản phẩm, giá mua và giá bán, và bạn muốn tính lợi nhuận bằng cách trừ giá mua khỏi giá bán.
Bảng dữ liệu Excel thô với các mã sản phẩm, giá mua, giá bán và cột Lợi nhuận trống, sẵn sàng áp dụng TRIMRANGE để tính toán.
Bạn biết rằng mình sẽ bổ sung thêm các hàng dữ liệu mới vào cuối bảng trong tương lai, và bạn muốn Excel tự động cập nhật phép tính này. Nếu bạn chỉ đơn giản gõ = (C2:C200)-(B2:B200) vào ô D2, công thức sẽ “spill” (tràn) kết quả xuống 200 hàng. Điều này dẫn đến một bảng tính lộn xộn với rất nhiều ô tính toán trống. Thậm chí, nếu bạn sử dụng tham chiếu cả cột (ví dụ: C:C-B:B), phép tính sẽ tràn xuống hơn một triệu ô, làm tăng đáng kể kích thước file và làm chậm hiệu suất của bảng tính.
Công thức Excel bị tràn (spill) xuống các hàng trống bên dưới dữ liệu thực tế, gây lãng phí tài nguyên và làm bảng tính thiếu gọn gàng.
Thay vào đó, bạn có thể sử dụng hàm TRIMRANGE để giải quyết vấn đề này. Hãy nhập công thức sau vào ô D2:
=TRIMRANGE(C2:C200)-TRIMRANGE(B2:B200)Công thức này sẽ tự động cắt bỏ các ô trống thừa ở cuối phạm vi dữ liệu, ngăn Excel phải thực hiện các phép tính không cần thiết và giữ cho bảng tính của bạn luôn gọn gàng. Trong ví dụ này, chúng ta đã bỏ qua các đối số b và c vì mặc định của TRIMRANGE (cắt bỏ cả hàng và cột đầu cuối) hoạt động tốt, do không có hàng hoặc cột trống ở đầu và không có dữ liệu bên phải cột D.
Bảng tính Excel với công thức đã được TRIMRANGE cắt gọn, loại bỏ các ô tính toán trống thừa ở cuối, giúp bảng tính gọn gàng hơn.
Khi bạn thêm các hàng dữ liệu mới vào cuối bảng, các ô tương ứng trong cột Lợi nhuận sẽ tự động được tính toán.
TRIMRANGE giúp công thức Excel tự động mở rộng và tính toán cho các hàng dữ liệu mới được thêm vào, đảm bảo tính động và hiệu quả.
Ví dụ Thực Tế 2: Kết Hợp TRIMRANGE Với Hàm XLOOKUP
Trong ví dụ thứ hai này, chúng ta có một danh sách các cầu thủ bóng đá và muốn sử dụng hàm XLOOKUP để xác định màu áo đấu phù hợp với từng đội.
Bảng dữ liệu cầu thủ bóng đá trong Excel với cột màu áo đấu trống và bảng tham chiếu màu áo tương ứng với từng đội.
Bạn dự kiến sẽ thêm khoảng năm cầu thủ nữa vào danh sách. Nếu bạn nhập công thức XLOOKUP vào ô E2 với phạm vi mở rộng đến hàng 22:
=XLOOKUP(B2:B22,$G$2:$G$7,$H$2:$H$7)Trong đó, B2:B22 là phạm vi tìm kiếm, $G$2:$G$7 là mảng tra cứu, và $H$2:$H$7 là mảng trả về (đã sử dụng tham chiếu tuyệt đối $ để cố định). Tương tự như ví dụ trước, các hàng trống sẽ hiển thị lỗi #N/A, khiến bảng tính trông kém chuyên nghiệp và buộc Excel phải xử lý nhiều hơn mức cần thiết.
Kết quả hàm XLOOKUP trong Excel hiển thị lỗi #N/A ở các hàng trống, cho thấy sự thiếu tối ưu khi không sử dụng TRIMRANGE.
Để tránh tình trạng này, bạn có thể kết hợp XLOOKUP với TRIMRANGE. Mặc dù có thể sử dụng các hàm như OFFSET, INDEX hoặc TOCOL để tạo các công thức phạm vi động, nhưng chúng thường phức tạp hơn nhiều so với việc sử dụng TRIMRANGE.
Vậy, trong ô E2, bạn chỉ cần gõ:
=XLOOKUP(TRIMRANGE(B2:B22,2),$G$2:$G$7,$H$2:$H$7)Công thức này tương tự như trên, nhưng phạm vi tìm kiếm (B2:B22) được lồng trong hàm TRIMRANGE. Ở đây, chúng ta đã thêm đối số thứ hai là “2”, chỉ định rằng chúng ta muốn cắt bỏ các hàng trống ở cuối phạm vi (trailing blank rows). Đối số thứ ba không cần thiết vì phạm vi của chúng ta chỉ trải dài một cột.
Kết quả là Excel sẽ cắt gọn kết quả của XLOOKUP, và khi bạn thêm các hàng dữ liệu mới vào cuối danh sách, các ô trong cột E sẽ tự động điền thông tin.
Hàm XLOOKUP kết hợp với TRIMRANGE trong Excel tự động tính toán và hiển thị kết quả chính xác cho dữ liệu mới, tối ưu hóa bảng tính.
Hiểu và Sử Dụng Trim Ref Operators: Phiên Bản Rút Gọn của TRIMRANGE
Thừa nhận rằng, một số công thức sử dụng TRIMRANGE có thể hơi phức tạp, đặc biệt khi lồng ghép vào các hàm khác. Đó là lý do tại sao Microsoft đã giới thiệu Trim Ref Operators – một phiên bản viết tắt, đơn giản hơn của hàm TRIMRANGE. Với Trim Ref Operators, bạn không cần phải nhúng TRIMRANGE vào các hàm khác, và chúng tự động cắt bỏ cả hàng và cột trống mà không cần bạn chỉ định cụ thể.
Sử dụng Trim Ref Operator rất đơn giản: bạn chỉ cần thêm một dấu chấm (.) vào một hoặc cả hai phía của dấu hai chấm trong công thức của mình:
| Vị trí thêm dấu chấm | Loại khoảng trống được cắt bỏ |
|---|---|
| Sau dấu hai chấm | Khoảng trống cuối (Trailing blanks) |
| Trước dấu hai chấm | Khoảng trống đầu (Leading blanks) |
| Cả hai phía dấu hai chấm | Khoảng trống cuối và đầu (Trailing and leading blanks) |
Một lưu ý quan trọng là Trim Ref Operators chỉ là một dấu chấm nhỏ trong công thức, nên chúng có thể khó nhận thấy khi bạn kiểm tra lại công việc hoặc xem bảng tính của người khác. Hãy lưu ý điều này nếu công thức của bạn không hoạt động như mong đợi!
Quay lại với ví dụ dữ liệu cầu thủ bóng đá ở trên, trong ô E2, bạn có thể gõ:
=XLOOKUP(B2:.B22,$G$2:$G$7,$H$2:$H$7)Hãy để ý dấu chấm sau dấu hai chấm trong B2:.B22. Điều này báo cho Excel biết rằng bạn muốn cắt bỏ các hàng trống cuối. Kết quả vẫn tương tự như khi sử dụng hàm TRIMRANGE đầy đủ, nhưng với một công thức đơn giản và trực quan hơn nhiều.
Ví dụ sử dụng toán tử Trim Ref trong Excel để loại bỏ các hàng trống thừa ở cuối công thức XLOOKUP, giữ bảng tính gọn gàng.
Tương tự như khi sử dụng hàm TRIMRANGE, bạn có thể tự tin thêm nhiều hàng dữ liệu mới vào cuối bảng, và hàm XLOOKUP sẽ tự động áp dụng cho dữ liệu đó một cách chính xác.
Tại Sao Nên Dùng TRIMRANGE và Trim Ref Thay Vì Bảng Cấu Trúc (Structured Tables)?
Bạn có thể thắc mắc tại sao không nên trình bày dữ liệu của mình trong các bảng Excel có định dạng sẵn (Structured Tables) và sử dụng các tham chiếu có cấu trúc (structured references), vì chúng cũng tự động mở rộng để bao gồm dữ liệu mới và bất kỳ phép tính liên quan nào bạn tạo. Trong hầu hết các trường hợp, đây thực sự là một lựa chọn tuyệt vời.
Tuy nhiên, có những tình huống cụ thể mà các mảng tràn (Spill Arrays) và một số hàm LAMBDA không thể hoạt động trong các bảng cấu trúc. Trong những trường hợp này, việc sử dụng TRIMRANGE và Trim Ref Operators trở thành một giải pháp thay thế cực kỳ hữu ích. Hơn nữa, đôi khi bạn có thể muốn đặt một phạm vi dữ liệu nằm ngoài bảng cấu trúc (ví dụ: để định dạng dữ liệu một cách độc đáo), và hàm TRIMRANGE sẽ giúp bạn giữ cho dữ liệu không có cấu trúc này luôn gọn gàng và dễ quản lý.
Bên cạnh hàm TRIMRANGE và các toán tử Trim Ref của Excel, còn có nhiều cách khác để làm sạch và tối ưu hóa dữ liệu của bạn trong Excel, chẳng hạn như sử dụng Power Query mạnh mẽ hoặc tận dụng tối đa sức mạnh của AI thông qua Copilot.
Với TRIMRANGE và Trim Ref Operators, bạn có trong tay những công cụ hiệu quả để giải quyết các vấn đề liên quan đến phạm vi dữ liệu động, từ đó nâng cao hiệu suất làm việc và duy trì sự chuyên nghiệp cho các bảng tính Excel của mình. Hãy bắt đầu áp dụng chúng để biến những bảng tính lộn xộn trở nên gọn gàng và hiệu quả hơn!
