3 công thức khiến bảng tính Excel của bạn liên tục chậm

Microsoft Excel có rất nhiều hàm tích hợp hữu ích, nhưng điều đó không có nghĩa là bạn nên sử dụng chúng một cách bừa bãi. Một trong những điều bạn có thể làm khi Excel bị chậm là kiểm tra một số công thức nhất định để đảm bảo bạn đang sử dụng các hàm này một cách hiệu quả. Ngay cả những hàm tưởng chừng đơn giản cũng có thể ảnh hưởng đáng kể đến hiệu suất và gây ra nhiều vấn đề đau đầu.

Mặc dù có nhiều nguyên nhân, nhưng các công thức sử dụng hàm volatile, hàm array và hàm lookup là những thủ phạm có tác động lớn nhất. Bài viết sẽ giải thích lý do tại sao chúng có thể làm chậm bảng tính của bạn, cũng như một số mẹo về cách khắc phục chúng để tối ưu hóa hiệu suất, hoặc khi nào nên tránh sử dụng chúng hoàn toàn.

Các công thức có hàm volatile

Có lý do khiến các chuyên gia hạn chế sử dụng chúng

Nếu bạn nhận thấy bảng tính Excel của mình tự động tính toán lại mỗi khi thực hiện thay đổi và điều đó ảnh hưởng đến hiệu suất, thì các hàm volatile có thể là nguyên nhân. Các hàm volatile buộc phải tính toán lại để cập nhật kết quả. Chúng thực hiện việc này ngay cả khi thay đổi không ảnh hưởng đến bất kỳ ô nào được tham chiếu hoặc kết quả đầu ra của chúng.

Một hàm volatile phổ biến là NOW, trả về ngày giờ hiện tại và hữu ích cho các tình huống như tính hạn chót, chèn dấu thời gian và cập nhật bảng thông tin theo thời gian thực. Một hàm khác là hàm INDIRECT, sử dụng chuỗi văn bản để xây dựng tham chiếu ô và hữu ích cho việc tham chiếu phạm vi động, cùng nhiều chức năng khác.

Mặc dù chúng có hiệu quả trong việc duy trì tính động, nhưng chỉ nên sử dụng khi cần thiết. Điều này đặc biệt đúng đối với các mô hình lớn với hàng chục trang tính hoặc hàng nghìn hàng. Tốt nhất nên sử dụng các phương án thay thế không biến động khi có thể (ví dụ, nhập ngày thủ công thay vì sử dụng hàm NOW).

Nếu bạn không thể tránh khỏi các hàm volatile và chúng làm chậm workbook, bạn có thể tắt tính toán lại tự động. Bằng cách đó, bạn có thể kích hoạt tính toán lại theo cách thủ công thay vì mỗi khi có một thay đổi nhỏ xảy ra, nhưng nhược điểm là bạn dễ quên. F9 sẽ tính toán lại tất cả các bảng tính đang mở, và Shift + F9 chỉ tính toán lại bảng tính đang hoạt động.

Các công thức mảng cũ

Xử lý công thức mảng không phải lúc nào cũng đơn giản

Bạn tạo công thức mảng bằng cách viết công thức và nhấn Ctrl + Shift + Enter thay vì chỉ nhấn Enter. Bạn sẽ biết mình đã tạo thành công nếu công thức được tự động đóng trong dấu ngoặc nhọn.

Công thức mảng đặc biệt hữu ích khi các phép tính của bạn liên quan đến nhiều giá trị, giúp bạn tiết kiệm bước tạo cột phụ. Chúng cũng có thể trả về nhiều kết quả.

Công thức mảng là một tính năng cũ, đặc biệt là khi Microsoft đã giới thiệu các hàm mảng động hiệu quả hơn trong Excel cho Microsoft 365. Tuy nhiên, chúng vẫn có những công dụng riêng, đặc biệt là khi bạn muốn đảm bảo khả năng tương thích với các phiên bản Excel cũ hơn (2019 trở về trước). Vì lý do này, chúng sẽ không biến mất trong tương lai gần.

Mặc dù mọi thứ được đóng gói gọn gàng trong một dòng khi bạn sử dụng công thức mảng, Excel sẽ xử lý công thức nhiều lần để đạt được kết quả. Điều này có thể tốn kém về mặt tính toán, đặc biệt là trong các bảng tính Excel lớn.

Để tránh sử dụng công thức mảng, bạn có thể cân nhắc sử dụng nhiều cột hỗ trợ, giúp phân bổ các phép tính trên nhiều ô. Nếu không thể tránh được, hãy cố gắng không sử dụng chúng với các hàm biến động. Nếu không gặp vấn đề về khả năng tương thích, bạn có thể chỉ cần sử dụng các hàm mảng động nếu có thể.

Các công thức lookup

Chúng tiện lợi nhưng có thể tốn kém

Nếu bạn cần tìm một giá trị cụ thể trong một trang tính Excel, cách nhanh nhất là sử dụng công thức lookup. Các hàm phổ biến nhất được sử dụng trong những công thức này là VLOOKUP và HLOOKUP, lần lượt tìm kiếm trên các cột và hàng. Ví dụ, bạn có thể dễ dàng tìm giá của một sản phẩm trong danh sách dọc bằng cách sử dụng tên của sản phẩm đó với sự trợ giúp của VLOOKUP.

Vấn đề là các công thức lookup này có thể làm giảm hiệu suất nếu chúng phải tìm kiếm trên những tập dữ liệu lớn, đặc biệt là khi dữ liệu được tìm kiếm nằm rải rác trên nhiều trang tính. Quá trình này tốn thời gian và có thể gây áp lực cho Excel.

Trừ khi bạn sử dụng các hàm lookup quá mức, nếu không thì đây có thể không phải là vấn đề lớn đối với phiên bản Microsoft 365 của Excel, vì nó tạo ra một chỉ mục tạm thời giúp tăng tốc các lần tra cứu lặp lại. Ngoài ra, nó có một hàm lookup thay thế hiệu quả (và linh hoạt hơn) có tên là XLOOKUP mà bạn có thể sử dụng thay thế.

Nếu bạn phải sử dụng công thức lookup trong các phiên bản Excel cũ hơn, việc sử dụng công thức trong cùng một trang tính với mảng lookup sẽ hiệu quả hơn. Bạn cũng nên giới hạn phạm vi tra cứu để chỉ bao gồm các ô cần thiết (ví dụ: B2:B100) - tránh tham chiếu đến toàn bộ cột (ví dụ: B:B). Ngoài ra, hãy sử dụng phép so khớp gần đúng thay vì phép so khớp chính xác, vì phép so khớp gần đúng không cần phải tìm kiếm trong mọi ô để tìm ra giá trị bạn cần.