Các chi phí ẩn của việc tham chiếu toàn cột trong Excel: Tìm hiểu 3 phương pháp thay thế để tối ưu hiệu suất của sổ làm việc của bạn

Các tham chiếu toàn cột trong Excel là nguyên nhân gây giảm hiệu suất một cách âm thầm, thường buộc chương trình phải xử lý một phạm vi hơn một triệu hàng. Do đó, chúng có thể làm chậm đáng kể hiệu suất của sổ làm việc của bạn. Vì vậy, hãy ngừng sử dụng các tham chiếu A:A và thay vào đó, tạo các phạm vi động.

Chi phí ẩn của thuế hàng triệu dòng

Tham chiếu toàn cột (A:A) có thể buộc Excel quản lý 1.048.576 hàng, đặc biệt khi sử dụng các phép tính kiểu mảng hoặc các hàm đánh giá từng ô. Hãy lấy bộ dữ liệu 50 hàng này làm ví dụ. Trong ô D1, bạn muốn sử dụng UNIQUE để liệt kê tất cả những người được ghi trong cột B.

Để đảm bảo công thức lấy được bất kỳ dữ liệu mới nào được thêm vào các hàng 51, 52 và tiếp theo, bạn có thể sử dụng tham chiếu toàn cột:

=UNIQUE(B:B)

Công thức này xem xét tất cả các ô trong cột B—bao gồm cả các hàng trống bên dưới phạm vi đang hoạt động, được biểu thị bằng số không lẻ.

Kiểm tra số không: Chứng minh gánh nặng

Nếu bạn muốn thấy chính xác những gì bạn đang buộc Excel phải xử lý khi nhập một tham chiếu cột toàn bộ, hãy gõ vào ô D1:

=B:B

Khi bạn cuộn xuống, bạn sẽ thấy hơn một triệu số không đuôi, đại diện cho các ô trống trong cột B từ hàng 51 trở xuống.

Trong khi một số hàm bỏ qua những khoảng trống này, những hàm khác lại coi chúng là dữ liệu hợp lệ, trả về giá trị không làm rối kết quả của bạn và chỉ ra rằng công cụ buộc phải xử lý khoảng trống này.

Dưới đây là ba công cụ có sẵn mà bạn có thể sử dụng để ngăn chặn hành vi này.

Giải pháp 1: Chuyển dữ liệu của bạn thành bảng Excel

Cách hiệu quả nhất để tránh thuế triệu dòng là sử dụng bảng Excel—các đối tượng động thay đổi hoàn toàn cách Excel tính toán dữ liệu của bạn.

Các bảng thay thế phạm vi lớn của các tham chiếu toàn cột bằng các ranh giới rõ ràng. Nếu dữ liệu của bạn có 50 hàng, đối tượng bảng sẽ chính xác dài 50 hàng, vì vậy khi một công thức tham chiếu bảng, engine tính toán của Excel sẽ giới hạn phạm vi tính toán chỉ trong container đó.

Đầu tiên, hãy đảm bảo rằng bộ dữ liệu của bạn có tiêu đề cột. Tiếp theo, chọn bất kỳ ô nào trong phạm vi, nhấn Ctrl+T, và trong hộp thoại Tạo Bảng, hãy chắc chắn rằng ô "Bảng của tôi có tiêu đề" được chọn. Sau đó, bấm "OK."

Bảng sử dụng các tham chiếu có cấu trúc, điều này, ngoài việc dễ đọc hơn, còn đóng vai trò như một khóa đối với các kích thước dữ liệu của bạn. Vì vậy, để liệt kê tất cả các tên duy nhất được sử dụng trong cột B, bạn cần thay đổi công thức của mình từ quét cột không giới hạn sang tìm kiếm có mục tiêu trong bảng:

=UNIQUE(Table1[Manager])

Hãy chú ý rằng số không ở cuối kết quả đã biến mất vì công thức chỉ tham chiếu dữ liệu nằm trong phạm vi của bảng.

Phương pháp này tốt hơn việc tham chiếu toàn cột vì hai lý do:

1. Tính toán không chất thải: Vì bảng có kết thúc xác định, Excel không cần xử lý khoảng trống phía dưới dữ liệu.
2. Phạm vi tự quản lý: Khi bạn thêm dữ liệu vào hàng 51, ranh giới của bảng sẽ mở rộng thêm một hàng, và công thức của bạn vẫn khớp với phạm vi mà không cần điều chỉnh thủ công.

Giải pháp 2: Sử dụng hàm TRIMRANGE

Nếu bạn không muốn chuyển dữ liệu Excel của mình thành một bảng chính thức, hàm TRIMRANGE là lựa chọn tốt tiếp theo. Nó cho phép bạn sử dụng tham chiếu toàn cột như trước đây, nhưng yêu cầu Excel cắt phạm vi chỉ bao gồm các ô thực sự chứa dữ liệu.

TRIMRANGE có ba đối số:

=TRIMRANGE(range,[trim_rows],[trim_cols])

ở đâu :

• range (bắt buộc) là phạm vi cần được cắt bớt.
• trim_rows (tùy chọn) cho Excel biết có nên loại bỏ các hàng trống ở đầu (1), các hàng trống ở cuối (2), hay cả hai (3).
• trim_cols (tùy chọn) cho Excel biết có nên loại bỏ các cột trống đầu (1), cột trống cuối (2), hay cả hai (3).

Để áp dụng logic này vào bộ dữ liệu của bạn, hãy gõ:

=UNIQUE(TRIMRANGE(B:B,2))

Ở đây, "2" buộc công thức cắt bỏ các ô trống ở cuối. Vì chỉ có một cột được tham chiếu trong công thức, nên không cần phải thêm đối số trim_cols.

Đây là lý do tại sao đây là một giải pháp hợp lý cho các tham chiếu toàn cột:

1. Tham chiếu linh hoạt nhưng gọn gàng: Bạn có được sự linh hoạt của tham chiếu toàn cột — nó vẫn tìm các dữ liệu mới mà bạn thêm sau này. Tuy nhiên, bộ xử lý tính toán chỉ thực hiện trên 50 hàng dữ liệu đang hoạt động.
2. Độ linh hoạt của lưới: Bạn sẽ có hiệu suất như bảng mà không cần thay đổi định dạng hiển thị của bảng tính

Giải pháp 3: Sử dụng toán tử tham chiếu trim

Toán tử tham chiếu trim của Excel mang lại chính xác các lợi ích giống như hàm TRIMRANGE, nhưng gõ ít hơn.

Bằng cách thêm dấu chấm (.) vào bất kỳ bên nào của dấu hai chấm trong phạm vi của bạn, bạn thông báo cho Excel loại bỏ các ô trống từ hướng đó:

Vậy, với cùng danh sách như trước, hãy gõ:

=UNIQUE(B:.B)

Khoảng thời gian sau dấu hai chấm cho Excel biết dừng lại ở hàng cuối cùng chứa dữ liệu — trong trường hợp này là hàng 50.

Đây là một phương pháp tốt vì:

1. Công thức tối giản: Bạn nhận được lợi ích hiệu suất cao mà không cần thay đổi định dạng hiển thị của bảng tính hoặc bao các phạm vi bằng các hàm bổ sung.
2. Kiểm soát chính xác: Bạn có thể dễ dàng chọn và thay đổi chính xác những khoảng trống nào cần bỏ qua bằng cách đặt các dấu chấm ở vị trí thích hợp so với dấu hai chấm.
3. Kết quả dữ liệu sạch: Phương pháp này ngăn các chức năng hiện đại nhận các số 0 đuôi, đảm bảo kết quả của bạn vẫn chính xác và không có dữ liệu ma.

Bên cạnh việc tối ưu công thức của bạn, hãy ghi nhớ những thói quen này để giữ cho sổ làm việc Excel của bạn hiệu quả:

• Hạn chế định dạng quá mức: Không bao giờ áp dụng viền hoặc màu sắc cho cả cột.
• Mẹo Ctrl+End: Sử dụng phím tắt này để tìm nơi Excel nghĩ dữ liệu của bạn kết thúc. Nếu nó nhảy đến hàng 1.000.000, bạn có các ô ma.
• Đặt lại lưới: Nếu bạn có các ô ma, hãy chọn các hàng trống, nhấp chuột phải và chọn "Xóa" (đừng chỉ nhấn Backspace). Sau đó, lưu ngay file làm việc của bạn để đặt lại ô cuối cùng và giảm dung lượng file.