Bạn đang tìm kiếm một cách để sắp xếp lại bảng tính của mình và giảm kích thước tệp mà không cần sử dụng các công thức động phức tạp như OFFSET, INDEX hay TOCOL? Hàm TRIMRANGE trong Excel là một tính năng mạnh mẽ giúp tự động phát hiện các ô chứa dữ liệu và điều chỉnh phạm vi một cách linh hoạt theo sự mở rộng hoặc thu hẹp của dữ liệu. Đây là một công cụ tối ưu hóa bảng tính hiệu quả, đặc biệt cho những người dùng Excel thường xuyên làm việc với các tập dữ liệu lớn và muốn duy trì sự gọn gàng, tăng cường hiệu suất.
Bên cạnh hàm TRIMRANGE, Microsoft Excel cũng đã giới thiệu Toán tử Trim Ref, một phiên bản viết tắt đơn giản hơn của hàm TRIMRANGE. Tính năng này mang lại sự tiện lợi đáng kể khi bạn muốn làm gọn các tham chiếu một cách nhanh chóng, và chúng ta sẽ cùng tìm hiểu chi tiết về nó ở phần sau của bài viết này. Với TRIMRANGE và Trim Ref, việc quản lý dữ liệu trong Excel trở nên trực quan và hiệu quả hơn bao giờ hết, giúp người dùng tiết kiệm thời gian và tối ưu hóa trải nghiệm làm việc với bảng tính.
Cú Pháp Hàm TRIMRANGE Trong Excel
Hàm TRIMRANGE có ba đối số, cho phép bạn kiểm soát linh hoạt cách thức cắt tỉa phạm vi dữ liệu:
=TRIMRANGE(phạm_vi,cắt_hàng,cắt_cột)Trong đó:
- phạm_vi (bắt buộc): Là vùng dữ liệu bạn muốn cắt tỉa.
- cắt_hàng (tùy chọn): Xác định các hàng cần cắt.
- 0 = Không cắt hàng nào.
- 1 = Cắt các hàng trống ở đầu.
- 2 = Cắt các hàng trống ở cuối.
- 3 = Cắt cả hàng trống ở đầu và cuối.
- cắt_cột (tùy chọn): Xác định các cột cần cắt.
- 0 = Không cắt cột nào.
- 1 = Cắt các cột trống ở đầu.
- 2 = Cắt các cột trống ở cuối.
- 3 = Cắt cả cột trống ở đầu và cuối.
Nếu bạn bỏ qua đối số cắt_hàng và/hoặc cắt_cột, Excel sẽ mặc định cắt cả hàng trống ở đầu và cuối, cũng như cột trống ở đầu và cuối.
Tại thời điểm viết bài này (tháng 1 năm 2025), 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ỉ hỗ trợ cắt dữ liệu trước hoặc sau phạm vi dữ liệu thực tế. Để 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 hàng trống trong Excel.
Ví Dụ Thực Tế Về TRIMRANGE Trong Excel
Để hiểu rõ hơn về cách hàm TRIMRANGE hoạt động và những lợi ích nó mang lại, chúng ta hãy cùng xem xét qua một vài ví dụ cụ thể.
Tối Ưu Công Thức Tính Toán Với TRIMRANGE và Phép Trừ
Trong ví dụ này, chúng ta muốn tính toán lợi nhuận bằng cách trừ giá mua từ giá bán trong các cột B và C.
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, chuẩn bị cho ví dụ TRIMRANGE Excel.
Chúng ta biết rằng sẽ có thể thêm các hàng dữ liệu bổ sung vào cuối bảng trong tương lai, và mong muốn Excel tự động cập nhật các hàng này vào công thức trừ. Thông thường, bạn có thể gõ công thức như sau vào ô D2:
=(C2:C200)-(B2:B200)Công thức này sẽ “tràn” kết quả đến 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 phép tính trống. Hơn nữa, nếu bạn sử dụng tham chiếu toàn bộ cột, phép tính sẽ tràn xuống tận cuối bảng tính, dẫn đến hơn một triệu phép tính, làm tăng đáng kể kích thước tệp và làm chậm hiệu suất của bảng tính.
Bảng tính Excel minh họa công thức bị tràn (spilled calculation) xuống dưới các hàng dữ liệu thực tế, gây ra sự lộn xộn và giảm hiệu suất Excel.
Thay vào đó, chúng ta sẽ gõ công thức sau vào ô D2:
=TRIMRANGE(C2:C200)-TRIMRANGE(B2:B200)Công thức này sẽ giúp cắt bỏ hiệu quả các ô trống không cần thiết ở 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 của bạn luôn gọn gàng.
Kết quả công thức Excel sau khi sử dụng hàm TRIMRANGE, cho thấy phép tính được cắt gọn gàng đến cuối phạm vi dữ liệu, giúp bảng tính Excel trở nên ngăn nắp.
Trong ví dụ trên, chúng ta đã bỏ qua các đối số cắt_hàng và cắt_cột trong các tham chiếu TRIMRANGE, vì mặc định của Excel là cắt cả hàng và cột trống ở đầu và cuối hoạt động hiệu quả trong trường hợp này. Không có hàng hoặc cột trống ở đầu, và cũng không có dữ liệu bên phải cột D.
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.
Bảng tính Excel với hàm TRIMRANGE tự động cập nhật và tính toán khi thêm các hàng dữ liệu mới, thể hiện tính năng động của hàm trong việc quản lý dữ liệu.
Kết Hợp TRIMRANGE Với XLOOKUP Để Tránh Lỗi #N/A
Trong ví dụ thứ hai này, chúng ta có danh sách các cầu thủ bóng đá và sẽ sử dụng hàm XLOOKUP để xác định màu áo đấu cần cung cấp cho họ, dựa trên đội mà họ thuộc về.
Dữ liệu cầu thủ bóng đá trong Excel với cột áo đấu (Jersey) còn trống, kèm theo một bảng tham chiếu màu áo đội bóng, sẵn sàng cho việc sử dụng XLOOKUP và TRIMRANGE.
Chúng ta cũng biết rằng sẽ tuyển thêm năm cầu thủ nữa, vì vậy khi thêm công thức XLOOKUP, chúng ta cần mở rộng phạm vi đến hàng 22. Do đó, trong ô E2, bạn có thể gõ:
=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ề. Dấu $ được sử dụng để chỉ định đây là các tham chiếu ô tuyệt đối (cố định). Tuy nhiên, giống như ví dụ trước, cách này sẽ khiến năm hàng dữ liệu trống trở nên lộn xộn với lỗi #N/A. Hơn nữa, Excel phải làm việc nhiều hơn mức cần thiết, một vấn đề 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.
Bảng tính Excel hiển thị lỗi #N/A do hàm XLOOKUP tham chiếu các hàng trống, làm mất thẩm mỹ và hiệu quả khi chưa áp dụng TRIMRANGE.
Bạn cũng có thể sử dụng 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 nhiều so với phương pháp TRIMRANGE đơn giản để đạt được kết quả tương tự.
Vì vậy, trong ô E2, chúng ta sẽ gõ:
=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 đã tham chiếu phạm vi (B2:B22) trong hàm TRIMRANGE. Lưu ý rằng lần này chúng ta đã bao gồ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 bao gồm đối số thứ ba, vì phạm vi của chúng ta chỉ kéo 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, ô trong cột E sẽ tự động được điền.
Hàm XLOOKUP trong Excel hoạt động hiệu quả trên dữ liệu mới nhờ TRIMRANGE, tự động điền thông tin và tránh lỗi #N/A, tối ưu hóa quản lý dữ liệu.
Đơn Giản Hóa Công Thức Với Toán Tử Trim Ref
Thừa nhận rằng 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 cũng đã 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 nhúng TRIMRANGE vào trong 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 liệu bạn đang cắt hàng hay cột, vì chúng tự động cắt cả hai.
Sử dụng Toán tử Trim Ref liên quan đến việc 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 bỏ |
|---|---|
| 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ác ô trống ở đầu và cuối |
Do các Toán tử Trim Ref chỉ là một “dấu chấm” đơn lẻ trong công thức của bạn, chúng có thể khó phát hiện khi bạn đang 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ẽ gõ:
=XLOOKUP(B2:.B22,$G$2:$G$7,$H$2:$H$7)Lưu ý rằng 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à vì 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.
Minh họa cách sử dụng toán tử Trim Ref (dấu chấm sau dấu hai chấm) trong công thức Excel để tự động cắt bỏ các hàng trống cuối phạm vi dữ liệu, giúp công thức XLOOKUP gọn gàng hơn.
Giống như khi sử dụng hàm TRIMRANGE, chúng ta có thể thêm nhiều hàng dữ liệu vào cuối, và hàm XLOOKUP sẽ tự động áp dụng cho dữ liệu đó.
Khi Nào Nên Chọn TRIMRANGE và Trim Ref Thay Vì Bảng Có Cấu Trúc?
Bạn có thể lập luận một cách chính đáng 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 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 quan điểm này.
Tuy nhiên, các mảng tràn (spill arrays) và một số hàm LAMBDA không thể sử dụng trong các bảng có cấu trúc. Do đó, trong những tình huống này, việc sử dụng TRIMRANGE là một giải pháp thay thế tiện lợi mà bạn nên có trong bộ công cụ của mình. 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 bảng có cấu trúc (ví dụ: nếu bạn muốn định dạng dữ liệu của mình một cách độc đáo), và hàm TRIMRANGE 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òn 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 tối đa AI thông qua Copilot.
Kết Luận
Hàm TRIMRANGE và Toán tử Trim Ref là những bổ sung vô cùng giá trị cho bộ công cụ của người dùng Excel, đặc biệt là những ai thường xuyên phải đối mặt với các bảng tính cồng kềnh và công thức phức tạp. Chúng không chỉ giúp bạn giữ cho bảng tính gọn gàng, tăng cường hiệu suất xử lý dữ liệu mà còn đơn giản hóa việc quản lý các phạm vi động một cách thông minh và tự động. Bằng cách loại bỏ các ô trống không cần thiết ở đầu và cuối phạm vi dữ liệu, TRIMRANGE và Trim Ref tối ưu hóa không gian làm việc và giảm thiểu lỗi, mang lại trải nghiệm làm việc mượt mà và hiệu quả hơn.
Hãy bắt đầu áp dụng TRIMRANGE và các Toán tử Trim Ref vào công việc hàng ngày của bạn để thấy sự khác biệt. Nếu bạn có bất kỳ câu hỏi nào hoặc muốn chia sẻ kinh nghiệm của mình về việc quản lý dữ liệu hiệu quả trong Excel, đừng ngần ngại để lại bình luận bên dưới. Đừng quên khám phá thêm các thủ thuật Excel hữu ích khác trên Thời Báo Công Nghệ để nâng cao kỹ năng làm việc của bạn!
