Cách Loại Bỏ Hàng Trống Trong Excel Tự Động Với Hàm TRIMRANGE Microsoft 365

File Excel của bạn mở trong 45 giây thay vì 3 giây. Công thức tính toán trên 1 triệu hàng trống mà không cần thiết. RAM tiêu tốn 2GB cho một bảng tính đơn giản. Nguyên nhân là tham chiếu toàn bộ cột mà không loại bỏ hàng trống.

Hàm TRIMRANGE trong Excel 365 tự động loại bỏ các hàng và cột trống từ biên ngoài của vùng dữ liệu, giúp file nhẹ hơn và tính toán nhanh hơn. Thay vì xóa thủ công hoặc dùng các hàm phức tạp như OFFSET, bạn chỉ cần một dòng công thức.

Mục Lục

Vấn đề với tham chiếu toàn bộ cột

Khi tham chiếu toàn bộ cột như A:A, Excel phải xử lý hơn 1 triệu hàng mỗi lần tính toán. Ngay cả khi chỉ có 100 hàng dữ liệu thực tế, Excel vẫn quét qua 1.048.576 hàng để kiểm tra giá trị.

Công thức như =LEN(A:A) sẽ trả về kết quả cho toàn bộ cột. Điều này tạo ra 1 triệu kết quả số 0 cho các hàng trống, làm tăng kích thước file và giảm hiệu suất đáng kể.

Hậu quả cụ thể:

Kích thước file tăng từ 2MB lên 50MB
Thời gian mở file chậm hơn 15 lần
Công thức mảng động tràn xuống hàng triệu hàng không cần thiết
RAM sử dụng tăng gấp đôi đến gấp ba

Các giá trị 0 hoặc khoảng trống không chỉ trông lộn xộn mà còn làm chậm file và tăng nguy cơ lỗi khi phân tích dữ liệu.

Cách sử dụng TRIMRANGE cơ bản

Cú pháp của TRIMRANGE đơn giản – chỉ cần cung cấp vùng dữ liệu và hàm sẽ tự động loại bỏ các hàng và cột trống.

Cú pháp:

=TRIMRANGE(vùng_dữ_liệu, [chế_độ_hàng], [chế_độ_cột])

Tham số:

vùng_dữ_liệu (bắt buộc): Vùng hoặc mảng ô cần làm sạch
chế_độ_hàng (tùy chọn): Cách loại bỏ hàng trống
chế_độ_cột (tùy chọn): Cách loại bỏ cột trống

Cả hai tham số chế_độ_hàng và chế_độ_cột đều có các tùy chọn sau: 0 = không loại bỏ, 1 = loại bỏ hàng đầu, 2 = loại bỏ hàng cuối, 3 = loại bỏ cả đầu và cuối (mặc định).

Ví dụ đơn giản:

=LEN(TRIMRANGE(A:A))

Công thức này tính độ dài văn bản trong cột A nhưng chỉ cho các hàng có dữ liệu. Không còn 1 triệu kết quả số 0 nữa.

Loại bỏ hàng trống theo từng hướng

Đôi khi bạn cần kiểm soát chính xác hàng nào sẽ bị loại bỏ. TRIMRANGE cho phép chỉ định loại bỏ hàng trống ở đầu, cuối hoặc cả hai.

XEM THÊM: Cách Sử Dụng Hàm CHOOSE Trong Excel Để Chọn Giá Trị Tự Động

Loại bỏ chỉ hàng trống ở cuối:

=TRIMRANGE(A1:A100, 2)

Công thức này giữ nguyên các hàng trống ở đầu nếu có, chỉ xóa các hàng trống từ hàng cuối cùng có dữ liệu trở xuống. Hữu ích khi cấu trúc dữ liệu yêu cầu khoảng trống ở đầu.

Loại bỏ chỉ hàng trống ở đầu:

=TRIMRANGE(A1:A100, 1)

Cách này loại bỏ các hàng trống trước hàng đầu tiên có dữ liệu, giữ nguyên các hàng trống ở cuối.

Loại bỏ cả hai hướng:

=TRIMRANGE(A1:A100)
hoặc
=TRIMRANGE(A1:A100, 3)

Đây là chế độ mặc định và phổ biến nhất. Nếu bỏ qua các tham số tùy chọn, Excel sẽ tự động loại bỏ cả hàng trống đầu và cuối.

Toán tử dấu chấm – cách viết ngắn gọn hơn

Toán tử Trim Reference là cú pháp rút gọn để gọi chức năng loại bỏ hàng trống mà không cần viết đầy đủ hàm TRIMRANGE. Chỉ cần thêm dấu chấm (.) trước hoặc sau dấu hai chấm (:) trong tham chiếu vùng.

Ba cách sử dụng toán tử dấu chấm:

Loại bỏ hàng trống ở cuối:

=LEN(A:.A)

Loại bỏ hàng trống ở đầu:

=LEN(A.:A)

Loại bỏ cả hai hướng:

=LEN(A.:.A)

Toán tử dấu chấm tự động loại bỏ cả hàng và cột trống mà không cần chỉ định riêng cho từng loại. Cú pháp ngắn gọn hơn nhưng ít rõ ràng hơn với người không quen.

So sánh hai cách viết:

=XLOOKUP(C2, TRIMRANGE(A:A, 2), TRIMRANGE(B:B, 2))
=XLOOKUP(C2, A:.A, B:.B)

Cả hai cho kết quả giống nhau. Cách thứ hai ngắn gọn nhưng cách thứ nhất rõ ràng hơn về ý định.

Kết hợp TRIMRANGE với các hàm khác

TRIMRANGE phát huy sức mạnh khi kết hợp với các hàm Excel khác, đặc biệt là các hàm mảng động và hàm tra cứu.

Với XLOOKUP: Hàm XLOOKUP có thể trả về lỗi #N/A nếu không tìm thấy giá trị trong vùng chứa các hàng trống. Bọc vùng tra cứu trong TRIMRANGE giúp loại bỏ các lỗi này.

=XLOOKUP(D2, TRIMRANGE(A:A), TRIMRANGE(B:B))

Công thức này tra cứu giá trị trong D2 trong cột A và trả về giá trị tương ứng từ cột B, bỏ qua tất cả các hàng trống.

Với hàm tính toán:

=AVERAGE(TRIMRANGE(B2:.B100))

TRIMRANGE cung cấp chỉ các giá trị hợp lệ vào hàm AVERAGE, ngăn chặn lỗi hoặc kết quả sai lệch do các ô trống hoặc công thức trống.

Với công thức mảng động:

=TRIMRANGE(D2:D200) - TRIMRANGE(C2:C200)

Công thức này tính chênh lệch giữa hai cột và chỉ hiển thị kết quả cho các hàng có dữ liệu. Khi thêm hàng mới, kết quả tự động mở rộng.

Tạo danh sách thả xuống động

Khi tạo danh sách thả xuống dự định mở rộng sau này, việc bao gồm các hàng trống khiến danh sách trông kỳ lạ. TRIMRANGE trong trường Source giúp danh sách sạch hơn và vẫn tự động mở rộng.

XEM THÊM: Cách Dùng Hàm TRANSPOSE Chuyển Cột Thành Hàng Trong 30 Giây

Các bước tạo:

Chọn ô hoặc vùng cần danh sách thả xuống
Dữ liệu > Xác thực dữ liệu
Trong Cho phép, chọn Danh sách
Trong Nguồn, nhập công thức:

=TRIMRANGE(A:A)

Danh sách thả xuống bây giờ chỉ hiển thị các giá trị thực tế, không có mục trống. Khi thêm giá trị mới vào cột A, danh sách tự động cập nhật.

Lợi ích:

Không có mục trống trong danh sách
Tự động cập nhật khi thêm dữ liệu
Không cần điều chỉnh vùng thủ công
Giao diện chuyên nghiệp hơn

Cải thiện hiệu suất với tham chiếu cột đầy đủ

Tham chiếu cột đầy đủ thường bị tránh vì có thể gây vấn đề hiệu suất với một số hàm. Tuy nhiên, với toán tử dấu chấm, chúng trở nên hiệu quả hơn nhiều vì tham chiếu cột đầy đủ có thể được giới hạn chỉ phần có giá trị.

Trước khi có TRIMRANGE: Tránh =SUM(A:A) vì Excel phải quét 1 triệu hàng. Thay vào đó dùng =SUM(A1:A100) và phải cập nhật thủ công khi dữ liệu tăng.

Với TRIMRANGE:

=SUM(TRIMRANGE(A:A))
hoặc
=SUM(A.:.A)

Excel tự động mở rộng hàm xuống khi thêm giá trị mới vào cột A, và TRIMRANGE loại bỏ các ô trống không cần thiết.

Kết quả đo được:

File 200 hàng dữ liệu: Giảm từ 15MB xuống 2MB
Thời gian tính toán: Từ 8 giây xuống dưới 1 giây
RAM sử dụng: Giảm 60-70%

Hạn chế cần lưu ý

TRIMRANGE không cho phép loại bỏ các hàng hoặc cột trống giữa dữ liệu hiện có. Chỉ có thể loại bỏ trước hoặc sau dữ liệu.

TRIMRANGE không làm gì với:

Hàng trống giữa các hàng có dữ liệu
Cột trống giữa các cột có dữ liệu
Ô chứa công thức trả về chuỗi rỗng (“”)

Để xử lý các hàng trống ở giữa, cần sử dụng phương pháp khác như Tìm và Chọn > Đi đến Đặc biệt > Ô trống.

TRIMRANGE và toán tử dấu chấm hiện không hỗ trợ tham chiếu vùng qua nhiều sheet (tham chiếu 3D). Công thức như =SUM(TRIMRANGE(Sheet1:Sheet3!A:A)) sẽ báo lỗi.

Các tình huống không áp dụng:

Dữ liệu trong Excel Tables (Tables đã tự động mở rộng)
Tham chiếu 3D qua nhiều sheets
Vùng với định dạng có điều kiện phức tạp

Yêu cầu phiên bản

TRIMRANGE là hàm mới chỉ có trong Excel 365. Hàm này không hoạt động trong các phiên bản cũ hơn như Excel 2021, Excel 2019 hoặc Excel 2016.

Kiểm tra phiên bản:

File > Tài khoản
Xem phần Về Excel
Cần phiên bản “Microsoft 365” hoặc “Excel for Microsoft 365”

Nếu sử dụng phiên bản Office cũ, cần nâng cấp lên Microsoft 365 để dùng TRIMRANGE. Các phương pháp thay thế cho phiên bản cũ bao gồm OFFSET, INDEX hoặc Excel Tables, nhưng đều phức tạp và kém linh hoạt hơn.

XEM THÊM: Tôi Giảm Thời Gian Làm Báo Cáo Excel Từ 2 Giờ Xuống 10 Phút Nhờ Hàm OFFSET

So sánh với phương pháp truyền thống

Trước TRIMRANGE, người dùng phải dùng các công thức phức tạp với OFFSET hoặc INDEX để tạo vùng động.

Phương pháp cũ với OFFSET:

=OFFSET($A$1, 0, 0, COUNTA($A:$A), 1)

OFFSET là hàm volatile tự động tính toán lại với mỗi thay đổi trong workbook, gây vấn đề hiệu suất. Công thức cũng khó hiểu và dễ lỗi.

Phương pháp mới với TRIMRANGE:

=TRIMRANGE(A:A)

Công thức ngắn gọn, rõ ràng và không gây vấn đề hiệu suất. Không cần tính toán phức tạp để xác định hàng cuối.

Excel Tables: Cách tốt nhất vẫn là sử dụng Excel Tables khi có thể (Ctrl + T). Tables tự động mở rộng, có tham chiếu có cấu trúc và nhiều lợi ích khác. Tuy nhiên, một số tính năng Excel như công thức tràn mảng hoặc một số hàm LAMBDA không thể sử dụng trong Tables. Đây là lúc TRIMRANGE trở nên cần thiết.

Mẹo sử dụng hiệu quả

Đặt tên cho vùng TRIMRANGE: Thay vì viết lại công thức TRIMRANGE nhiều lần, tạo vùng có tên:

Công thức > Trình quản lý tên > Mới
Tên: DanhSachSanPham
Tham chiếu đến: =TRIMRANGE(Sheet1!$A:$A)
Sử dụng =XLOOKUP(D2, DanhSachSanPham, ...)

Kết hợp nhiều điều kiện:

=FILTER(TRIMRANGE(A:C), TRIMRANGE(B:B) > 1000)

Lọc dữ liệu từ 3 cột đầu tiên chỉ cho các hàng có giá trị cột B lớn hơn 1000, tự động bỏ qua hàng trống.

Sử dụng trong biểu đồ: Tạo biểu đồ với vùng dữ liệu động mà không cần cập nhật thủ công. Thay vì chọn A1:A20, dùng vùng có tên với TRIMRANGE. Biểu đồ tự động cập nhật khi thêm dữ liệu.

Khi nào nên dùng TRIMRANGE

TRIMRANGE phù hợp nhất cho các tình huống sau:

Dữ liệu thay đổi thường xuyên: Báo cáo hàng ngày, danh sách sản phẩm, dữ liệu nhập từ hệ thống khác. Thay vì điều chỉnh vùng tham chiếu thủ công, TRIMRANGE tự động thích ứng.

Công thức mảng động: TRIMRANGE đặc biệt hữu ích khi tạo công thức mảng động hoặc tối ưu hàm lambda để cải thiện hiệu suất. Giúp giới hạn phạm vi xử lý chỉ còn dữ liệu thực tế.

File lớn cần tối ưu: File Excel chậm, mở lâu, tiêu tốn RAM nhiều. TRIMRANGE giúp giảm số lượng ô cần tính toán, cải thiện hiệu suất đáng kể.

Tránh Tables: Một số workflow không phù hợp với Excel Tables nhưng vẫn cần vùng động. TRIMRANGE là giải pháp thay thế tốt.

Tương lai của quản lý dữ liệu động

Microsoft công bố TRIMRANGE vào tháng 8 năm 2024 như một hàm preview. Hàm này thể hiện hướng đi mới của Excel trong việc xử lý dữ liệu động và tối ưu hiệu suất.

Các tính năng tương tự đang được phát triển bao gồm khả năng xử lý tốt hơn với công thức mảng, hỗ trợ tham chiếu 3D và tích hợp sâu hơn với Power Query. TRIMRANGE là bước đầu tiên trong việc hiện đại hóa cách Excel quản lý vùng dữ liệu động.

Nếu đang dùng Excel 365, bắt đầu với TRIMRANGE cho các báo cáo thường xuyên thay đổi. Công thức đơn giản =TRIMRANGE(A:A) thay thế hàng trăm dòng code VBA hoặc công thức phức tạp với OFFSET. File nhẹ hơn, tính toán nhanh hơn và bảo trì dễ dàng hơn nhiều.