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 chúng hiệu quả. Ngay cả những hàm có vẻ đơ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 nguyên nhân đáng kể nhất. Tôi 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ô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 bạn thực hiện thay đổi và điều này ảnh hưởng đến hiệu suất, thì các hàm volatile có thể là thủ phạm. Các hàm volatile buộc phải tính toán lại để duy trì kết quả hiện tại. 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 và giờ hiện tại và hữu ích cho các tình huống như tính toán 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 volatile 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 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 các hàm biến động và chúng làm chậm sổ làm việc, bạn có thể tắt tính năng 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ó thay đổi nhỏ xảy ra, nhưng nhược điểm là bạn rất dễ quên. F9 tính toán lại tất cả các bảng tính đang mở, còn Shift + F9 chỉ tính toán lại bảng tính đang hoạt động.
Công thức mảng cũ
Việ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 ra 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 đặt 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 đặt 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 các cột hỗ trợ, giúp phân bổ các phép tính của bạn 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ả năng tương thích không phải là vấn đề, bạn có thể chỉ cần sử dụng các hàm mảng động nếu có thể.
Công thức tra cứu
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 bảng tính Excel, cách nhanh nhất là sử dụng công thức tra cứu. Các hàm phổ biến nhất được sử dụng trong các 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 tên của nó với sự trợ giúp của VLOOKUP.
Vấn đề là các công thức tra cứu này có thể làm giảm hiệu suất nếu chúng phải tìm kiếm trên các 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 bảng tính. Quá trình này tốn thời gian và có thể gây áp lực lên Excel.
Trừ khi bạn sử dụng các hàm tra cứu quá mức, đâ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 tra cứu thay thế hiệu quả hơn (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 tra cứu 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 tra cứu sẽ hiệu quả hơn. Bạn cũng phải 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 mọi ô để tìm ra giá trị bạn cần.
Khai phá sức mạnh của một trang tính Excel nhanh chóng
Theo kinh nghiệm của tôi, ba lỗi này là thủ phạm lớn nhất. Bạn cũng nên chú ý đến các công thức lồng nhau, đặc biệt là những công thức chứa logic điều kiện phức tạp. Một lần nữa, điều này có thể không phải là vấn đề đối với Excel dành cho Microsoft 365, vì Excel sử dụng các chỉ mục được lưu trong bộ nhớ đệm để đẩy nhanh các lần tra cứu lặp lại.
Bên cạnh việc xem xét các công thức, hãy đảm bảo bạn cập nhật Excel, xóa mọi dữ liệu không cần thiết và gỡ bỏ các tiện ích bổ sung không sử dụng. Bất cứ điều gì bạn có thể làm để tăng tốc trang tính Excel của mình đều giúp giảm bớt một vấn đề phải giải quyết. Bạn có thể tập trung vào việc hoàn thành công việc thay vì lãng phí nhiều thời gian hơn vào việc tối ưu hóa.