Cách Mà Excel Tự Tính Toán Lại (hay Vì Sao Mà File Của Bạn Lại Chậm ...

Trong bài viết trước về các hàm tự động tính toán lại (Volatile Function), chúng ta đã hiểu được thế nào là một “Volatile”, những đối tượng nào thuộc về “Volatile” và cách mà một Volatile Function tự tính toán lại. Với bài viết này ta sẽ đi sâu hơn về cách mà Excel tự tính toán lại cũng như một lí do mà file của bạn trở nên chậm.

Những phương thức khiến Excel tự tính toán lại

Người dùng Excel có thể tác động, khiến Excel tự tính toán lại theo nhiều cách, chẳng hạn như:

• Điền dữ liệu mới vào ô (trong chế độ tính toán tự động Automatic calculation).
• Chèn/xóa hàng/cột
• Lưu file sau khi đã bật chế độ Recalculate before save 

  

• Sử dụng Autofilter
• Thay đổi độ rộng hàng/cột tự dộng bằng cách click hoặc click đúp vào phần chia giữa hàng/cột trong chế độ Automatic calculation 
• Thêm/sửa/xóa Name Range
• Đổi tên worksheet
• Thay đổi vị trí worksheet
• Ẩn/hiện hàng (không có cột)

Sự phụ thuộc, “Dirty Cells” và “Recalculated Cells”

Có thể diễn giải việc tính toán trên Excel qua 3 giai đoạn sau: 1. Xây dựng hệ thống “cây phụ thuộc”

2. Xây dựng chuỗi tính toán

3. Sự tính toán chi tiết trong từng ô

“Cây phụ thuộc” cho Excel biết rằng, vùng nào sẽ phụ thuộc vào vùng nào hay nói cách khác, ô nào sẽ được sử dụng để tính toán cho ô nào. Từ hệ thống này, Excel sẽ xây dựng nên chuỗi tính toán. Chuỗi tính toán liệt kê ra tất cả những ô chứa công thức cần được tính toán. Excel sẽ sửa lại chu trình tính toán nếu nhận ra có bất kỳ công thức nào chưa được tính. Với trường hợp này, những ô đã được tính toán sẽ được đưa xuống dưới chuỗi, để ưu tiên những ô còn lại lên trên. Bởi lý do này, tốc độ tính toán có thể được cải thiện trong 1 trang tính mà đã được chạy qua vài chu kỳ trước đó.

Khi có một thay đổi bất kỳ diễn ra đối với sổ tính, chẳng hạn như một công thức mới được thêm vào, Excel sẽ tái cấu trúc cây phụ thuộc và chuỗi tính toán. Khi đó, Excel sẽ đánh dấu tất cả những ô phụ thuộc vào dữ liệu mới “cần được tính toán lại”. Những ô được đánh dấu trong trường hợp này gọi là Dirty. Tất cả những ô phụ thuộc trực tiếp hay gián tiếp đều sẽ được đánh dấu dirty. Chẳng hạn nếu B1 phụ thuộc A1, C1 phụ thuộc B1 thì khi A1 thay đổi, cả B1 và C1 đều dirty.

Nếu 1 ô phụ thuộc vào chính nó (một cách trực tiếp hoặc gián tiếp), Excel sẽ nhận dạng đó là một tham chiếu vòng (circular reference) và cảnh báo cho người sử dụng. Trong trường hợp việc tham chiếu đó là cần thiết, bạn có thể tính toán bằng cách thay đổi trong hộp thoại Options > Formulas 

Sau khi đã đánh dấu Dirty, sau quá trình tự tính toán lại, Excel sẽ tự tính toán lại giá trị trong mỗi ô dirty theo thứ tự trong chuỗi tính toán. Như ở ví dụ trên, ô B1 sẽ được tự tính toán trước, sau đó đến ô C1. Việc tính toán này sẽ được thực hiện ngay trong chế độ Automatic calculation, hoặc sau đó với chế độ Manual.

Từ phiên bản Excel 2002, trong VBA đã hỗ trợ 1 phương thức có tên là Range.Dirty, với mục đích bắt vùng được đánh dấu phải tính toán lại và thường được sử dụng cùng Range.Calculate.

Các trường hợp IF(), CHOOSE(), AND(), OR()

IF và CHOOSE sẽ chỉ thực hiện duy nhất 1 điều kiện. Hành động này được gọi là short-circuiting (vòng ngắn):

Nếu bạn tạo ra các UDF (user defined function – hàm người dùng tự định nghĩa) Một, Hai và Ba, mỗi thứ chứa một lệnh debug.print hoặc msgbox, bạn có thể kiểm tra như sau:

=IF(True, Một(), Hai()) sẽ chỉ thực hiện lệnh của hàm Một, không thực hiện Hai.

=CHOOSE(2, Một(), Hai(), Ba()) sẽ chỉ thực hiện Hai.

Hàm AND và OR thì luôn luôn duyệt qua và tính toán tất cả các lựa chọn.

 File chậm, vì sao?

Trong trường hợp File chậm, bạn hãy để ý xem liệu trong trang tính của mình có quá nhiều Volatile Function không? Nếu có, những ô phụ thuộc vào hàm đó có nhiều không? Kiểm tra chuỗi tính toán của Excel và bạn sẽ tìm ra nguyên nhân

Ngoài ra, hãy tham khảo ngay các nguyên nhân khác cũng như “Bí kíp giảm cân” cho file Excel của bạn:

Nguyên nhân làm File Excel trở nên nặng và chậm

Cách sửa file Excel nặng chậm & gỡ mật khẩu bảo vệ