Bạn đang tìm kiếm một phương pháp để dọn dẹp bảng tính Excel của mình, giảm kích thước file mà không cần phải sử dụng các công thức phạm vi động phức tạp như OFFSET, INDEX hay TOCOL? Chào mừng bạn đến với kỷ nguyên mới của Excel với hàm TRIMRANGE và toán tử Trim Ref! Những công cụ mạnh mẽ này giúp tự động phát hiện các ô chứa dữ liệu của bạn và tự động mở rộng, thu hẹp phạm vi, đảm bảo bảng tính luôn gọn gàng và hiệu quả.
Hàm TRIMRANGE cho phép bạn kiểm soát chính xác việc cắt bỏ các hàng và cột trống thừa ở đầu hoặc cuối dữ liệu. Thậm chí, toán tử Trim Ref còn cung cấp một phiên bản viết tắt đơn giản hơn, giúp bạn dễ dàng áp dụng mà không cần phải lồng ghép hàm phức tạp. Hãy cùng trangtincongnghe.net khám phá cách TRIMRANGE và Trim Ref có thể thay đổi cách bạn làm việc với Excel, mang lại trải nghiệm mượt mà và tối ưu hơn, khẳng định vị thế dẫn đầu trong quản lý dữ liệu hiệu quả.
Cú Pháp Hàm TRIMRANGE trong Excel
Hàm TRIMRANGE được thiết kế với ba đối số, mang lại sự linh hoạt trong việc xác định cách bạn muốn cắt gọn phạm vi dữ liệu:
=TRIMRANGE(a,b,c)Trong đó:
- a (bắt buộc): Là phạm vi dữ liệu bạn muốn cắt gọn.
- b (tùy chọn): Xác định các hàng cần cắt.
0= không cắt hàng nào1= cắt các hàng trống ở đầu2= cắt các hàng trống ở cuối3= cắt cả hàng trố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 nào1= cắt các cột trống ở đầu2= cắt các cột trống ở cuối3= cắt cả cột trống ở đầu và cuối
Nếu bạn bỏ qua các đối số b và/hoặc c, Excel sẽ mặc định cắt cả hàng và/hoặc cột trống ở cả đầ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 các hàng hoặc cột trống nằm giữa các dữ liệu hiện có. Chức năng này chỉ giới hạn ở việc cắt gọn dữ liệu ở trước hoặc sau phạm vi dữ liệu của bạn. Để xử lý các hàng trống xen kẽ, bạn cần sử dụng các phương pháp khác để xóa chúng.
Ví Dụ 1: Sử Dụng TRIMRANGE Với Phép Tính
Trong ví dụ này, chúng ta muốn tính lợi nhuận bằng cách trừ giá mua (cột B) từ giá bán (cột C) cho mỗi mặt hàng.
Bảng dữ liệu Excel chưa định dạng với các ID mặt hàng, giá mua, giá bán và cột Lợi nhuận trống, sẵn sàng cho phép tính.
Chúng ta biết rằng có thể sẽ thêm các hàng dữ liệu mới vào cuối bảng trong tương lai, và mong muốn Excel tự động nhận diện chúng vào công thức tính toán. Một cách có thể làm là nhập công thức:
=(C2:C200)-(B2:B200)vào ô D2. Công thức này sẽ “tràn” (spill) kết quả tới 200 hàng đầu tiên. Tuy nhiên, cách làm này sẽ khiến bảng tính trở nên lộn xộn với rất nhiều ô tính toán trống rỗng. Tệ hơn nữa, nếu chúng ta sử dụng tham chiếu cả cột (ví dụ C:C), phép tính sẽ tràn xuống tận cuối bảng tính, gây ra hơn một triệu phép tính không cần thiết, 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.
Bảng tính Excel với kết quả phép tính tràn (spill) xuống các hàng trống bên dưới dữ liệu thực tế, gây lộn xộn.
Thay vào đó, chúng ta sẽ 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 gọn các ô dư thừa ở cuối dữ liệu, ngăn không cho Excel phải xử lý quá nhiều và giữ cho bảng tính luôn gọn gàng.
Bảng tính Excel với phép tính tràn đã được hàm TRIMRANGE tự động cắt gọn, chỉ hiển thị kết quả trên các hàng dữ liệu.
Trong công thức trên, chúng ta đã bỏ qua các đối số b và c của hàm TRIMRANGE. Lý do là Excel mặc định sẽ cắt gọn cả hàng và cột trống ở cả đầu và cuối, điều này hoàn toàn phù hợp trong trường hợp này vì không có hàng hay cột trống ở đầu và không có dữ liệu nào nằm bên phải cột D.
Giờ đây, 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 tính toán mà không cần bất kỳ điều chỉnh nào.
Bảng tính Excel tự động cập nhật kết quả phép tính khi thêm hàng dữ liệu mới nhờ hàm TRIMRANGE.
Ví Dụ 2: Sử Dụng TRIMRANGE Với XLOOKUP
Trong ví dụ thứ hai này, chúng ta có một danh sách cầu thủ bóng đá và sẽ sử dụng hàm XLOOKUP để xác định màu áo đấu cần cấp phát cho họ, tùy thuộc vào đội bóng mà họ thuộc về.
Bảng tính Excel chứa dữ liệu cầu thủ bóng đá với cột Áo đấu trống và bảng tham chiếu màu áo tương ứng.
Giả sử chúng ta sẽ tuyển thêm năm cầu thủ nữa, vì vậy khi nhập công thức XLOOKUP, chúng ta cần mở rộng phạm vi tham chiếu đến hàng 22. Do đó, trong ô E2, chúng ta có thể nhập:
=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ề. Chúng ta đã sử dụng ký hiệu $ để chỉ định các tham chiếu ô tuyệt đối (cố định). Tuy nhiên, tương tự như ví dụ trước, cách này sẽ khiến năm hàng dữ liệu trống cuối cùng hiển thị lỗi #N/A, trông rất thiếu chuyên nghiệp. Hơn nữa, Excel phải làm việc nhiều hơn mức cần thiết, điều này có thể ảnh hưởng đến hiệu suất nếu bạn có nhiều hàng trống trong công thức của mình.
Bảng tính Excel với công thức XLOOKUP trả về lỗi #N/A do tham chiếu các hàng dữ liệu trống.
Chúng ta cũng 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 những cách này phức tạp hơn rất nhiều để đạt được kết quả tương tự như phương pháp TRIMRANGE đơn giản hơn nhiều.
Vì vậy, trong ô E2, chúng ta sẽ nhập:
=XLOOKUP(TRIMRANGE(B2:B22,2),$G$2:$G$7,$H$2:$H$7)Đây là công thức tương tự như trên, nhưng chúng ta đã bao bọc phạm vi tìm kiếm (B2:B22) bằng hàm TRIMRANGE. Lưu ý rằng lần này, chúng ta đã thêm đối số thứ hai (“2”), chỉ dẫn Excel rằng chúng ta muốn cắt bỏ các hàng trống ở cuối. Tuy nhiên, chúng ta không cần đối số thứ ba vì phạm vi của chúng ta chỉ trải dài một cột.
Lần này, Excel đã cắt gọn kết quả XLOOKUP của chúng ta, và khi thêm nhiều hàng dữ liệu mới vào cuối, các ô trong cột E sẽ tự động điền mà không còn lỗi #N/A.
Bảng tính Excel thực hiện tính toán XLOOKUP trên các hàng dữ liệu mới tự động nhờ hàm TRIMRANGE, loại bỏ lỗi #N/A.
Sử Dụng Toán Tử Trim Ref
Thực tế là một số công thức chúng ta đã sử dụng trong các ví dụ trên khá phức tạp. Đó là lý do tại sao Microsoft đã giới thiệu các toán tử Trim Ref – một phiên bản viết tắt của hàm TRIMRANGE. Nói cách khác, các toán tử Trim Ref giúp bạn không cần phải lồng ghép TRIMRANGE vào các hàm khác. Hơn nữa, các toán tử Trim Ref không yêu cầu bạn chỉ định có đang cắt hàng hay cột hay không, vì chúng tự động cắt cả hai.
Việc sử dụng toán tử Trim Ref chỉ đơn giản là thêm một dấu chấm (.) ở một hoặc cả hai bên của dấu hai chấm trong công thức của bạn:
| Vị trí thêm dấu chấm | Các ô trống được cắt gọn |
|---|---|
| Sau dấu hai chấm | Các ô trống ở cuối |
| Trước dấu hai chấm | Các ô trống ở đầu |
| Cả hai bên dấu hai chấm | Cả ô trống ở cuối và ở đầu |
Lưu ý: Vì các toán tử Trim Ref chỉ là một “dấu chấm” đơn lẻ trong công thức, chúng có thể khó nhận ra khi bạn xem lại công việc của mình hoặc bảng tính của người khác. Đây là điều cần lưu ý nếu mọi thứ không hoạt động như bạn mong đợi!
Sử dụng cùng dữ liệu cầu thủ bóng đá như trong Ví dụ 2, trong ô E2, chúng ta sẽ nhập:
=XLOOKUP(B2:.B22,$G$2:$G$7,$H$2:$H$7)Lưu ý cách chúng ta đã thêm một dấu chấm sau dấu hai chấm trong B2:.B22. Điều này là do chúng ta muốn Excel cắt bỏ các ô trống ở cuối. Ảnh chụp màn hình dưới đây cho thấy kết quả tương tự, mặc dù chúng ta đã sử dụng một công thức đơn giản hơn nhiều mà không cần lồng hàm TRIMRANGE vào.
Ví dụ về việc sử dụng toán tử Trim Ref sau dấu hai chấm trong công thức Excel để cắt gọn các hàng trống thừa.
Tương tự như khi sử dụng hàm TRIMRANGE, bạn có thể thêm nhiều hàng dữ liệu vào cuối bảng và hàm XLOOKUP vẫn sẽ áp dụng cho dữ liệu đó một cách tự động và gọn gàng.
Tại Sao Nên Sử Dụng TRIMRANGE và Trim Ref Thay Vì Bảng Cấu Trúc (Structured Tables)?
Bạn có thể lập luận rằng thay vào đó, bạn 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 và sử dụng các tham chiếu có cấu trúc, chúng sẽ 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 tình huống, chúng tôi sẽ đồng ý với ý kiến này.
Tuy nhiên, các mảng tràn (spill arrays) và một số hàm LAMBDA không thể được sử dụng trong các bảng cấu trúc. Trong những tình huống này, việc sử dụng TRIMRANGE là một lựa chọn thay thế tiện lợi mà bạn nên biết. Ngoài ra, trong một số trường hợp, bạn có thể muốn đặt một phạm vi bên ngoài một bảng cấu trúc (ví dụ: nếu bạn muốn định dạng dữ liệu của mình theo 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 được gọn gàng.
Bên cạnh hàm TRIMRANGE và các toán tử Trim Ref của Excel, có nhiều cách khác để làm sạch dữ liệu trong Excel, chẳng hạn như sử dụng Power Query hoặc tận dụng trí tuệ nhân tạo thông qua Copilot.
Kết Luận
TRIMRANGE và các toán tử Trim Ref đại diện cho một bước tiến quan trọng trong việc quản lý dữ liệu hiệu quả và gọn gàng trong Microsoft Excel. Chúng giải quyết một vấn đề phổ biến mà nhiều người dùng Excel gặp phải: làm thế nào để xử lý các phạm vi dữ liệu động mà không làm ảnh hưởng đến hiệu suất hoặc tính thẩm mỹ của bảng tính. Bằng cách tự động cắt gọn các hàng và cột trống thừa, các tính năng này giúp giảm kích thước file, tăng tốc độ tính toán và đảm bảo rằng các công thức của bạn luôn hoạt động trên dữ liệu thực tế.
Đối với những chuyên gia công nghệ và người dùng Excel muốn nâng cao kỹ năng làm việc của mình, việc nắm vững TRIMRANGE và Trim Ref không chỉ là một lợi thế mà còn là một công cụ thiết yếu. Chúng cung cấp một giải pháp thanh lịch, đặc biệt khi các hàm mảng tràn không tương thích với bảng cấu trúc, hoặc khi bạn cần sự linh hoạt trong việc định dạng dữ liệu không có cấu trúc. Hãy áp dụng TRIMRANGE và Trim Ref vào quy trình làm việc hàng ngày của bạn để biến những bảng tính lộn xộn thành những công cụ phân tích sắc bén và hiệu quả. Chia sẻ trải nghiệm của bạn với chúng tôi tại trangtincongnghe.net và cùng nhau khai thác tối đa tiềm năng của Excel!
