Giải thích cơ chế Excel tự động tính toán lại các phép tính (hoặc tại sao tệp tin của bạn lại mất thời gian mở lâu đến vậy?)

Bài viết trước đã giúp chúng ta hiểu về các hàm cập nhật tự động (Volatile Function), nhận biết được hàm nào là “Volatile” và cách thức mà hàm Volatile được tính toán. Bài viết hôm nay sẽ đào sâu hơn vào cơ chế tự động cập nhật của Excel, đồng thời giải thích một trong những nguyên nhân khiến tệp Excel của bạn chạy chậm lại.

Các hành động kích hoạt chức năng tự động tính toán của Excel

Các tác động từ người dùng có thể buộc Excel phải thực hiện tính toán lại bằng cách áp dụng các phương thức như:

  • Nhập dữ liệu mới vào các ô (trong chế độ tính toán tự động Automatic calculation).
  • Thêm hoặc bỏ đi hàng/cột
  • Ghi lại tệp sau khi kích hoạt chế độ Recalculate before save
    File > Options > Formulas…

     

  • Sử dụng tính năng Autofilter
  • Thực hiện điều chỉnh kích thước hàng/cột bằng việc nhấn hoặc nhấp đúp vào phần chia hàng/cột trong chế độ Automatic calculation
  • Thêm, chỉnh sửa hoặc xóa Name Range
  • Rename một worksheet
  • Chuyển đổi vị trí của worksheet
  • Ẩn hoặc hiển thị hàng (không bao gồm cột)

Mối phụ thuộc, “Dirty Cells” và “Recalculated Cells”

Quá trình tính toán của Excel được thực hiện qua ba bước sau:
1. Xây dựng “hệ thống cây phụ thuộc”

2. Phát triển chuỗi các phép tính toán

3. Tính toán chi tiết trên từng ô cụ thể

Cây phụ thuộc” là công cụ giúp Excel nhận diện ô nào cần dựa vào thông tin từ ô nào để thực hiện tính toán. Từ đây, Excel sẽ lên kế hoạch cho chuỗi các phép tính, nêu rõ những ô chứa công thức cần được xem xét lại. Excel sẽ chỉnh sửa lại loạt công thức nếu phát hiện ra có công thức nào đó bỏ sót. Trong tình huống này, những ô đã được xử lý sẽ được đưa xuống phía dưới của chuỗi, nhằm mục đích đặt các ô chưa xử lý lên hàng đầu. Nhờ cơ chế này, hiệu suất của việc tính toán có thể được nâng cao trong một worksheet đã trải qua nhiều lần duyệt lại trước đó.

Bất kỳ thay đổi nào đối với bảng tính, như việc thêm một công thức mới, đều khiến Excel phải tái cấu trúc lại cây phụ thuộc và chuỗi tính toán. Excel sẽ đánh dấu tất cả các ô phụ thuộc vào dữ liệu mới là “cần được xem xét lại”. Ô được đánh dấu trong tình huống này được gọi là Dirty. Tất cả các ô liên quan trực tiếp hay gián tiếp đều sẽ nhận dấu dirty. Ví dụ, nếu B1 phụ thuộc vào A1 và C1 phụ thuộc vào B1, thì khi A1 thay đổi, cả B1 và C1 đều trở nên dirty.

Khi một ô có mối liên kết trở lại chính nó (trực tiếp hoặc gián tiếp), Excel sẽ phát hiện ra rằng đó là một “circular reference” và sẽ cảnh báo người dùng. Trong trường hợp bạn cần thực hiện tham chiếu vòng làm một phần của phép tính, bạn có thể thay đổi cài đặt trong hộp thoại Options > Formulas

Sau khi đánh dấu là Dirty, quá trình tự động cập nhật lại sẽ được kích hoạt và Excel sẽ tiến hành tính toán lại giá trị cho từng ô dirty theo thứ tự được xác định trong chuỗi tính toán. Theo ví dụ trước, ô B1 sẽ được xử lý trước, tiếp theo là ô C1. Việc này được thực hiện sau quá trình tự động cập nhật lại.

Trong chế độ Automatic calculation, hoặc chế độ Manual, tính toán có thể được thực hiện ngay lập tức hoặc được hoãn lại.

Kể từ Excel 2002, VBA đã được tích hợp phương thức Range.Dirty để yêu cầu các khu vực chọn tính toán lại. Phương thức này thường được dùng chung với Range.Calculate.

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

Đối với IFCHOOSE, chỉ có một điều kiện duy nhất được thực hiện, quá trình này được gọi là short-circuiting (vòng ngắn):

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

=IF(True, Một(), Hai()) sẽ chỉ chạy lệnh trong hàm Một và bỏ qua Hai.

=CHOOSE(2, Một(), Hai(), Ba()) sẽ chạy mình hàm Hai.

Về phần hàm ANDOR, chúng luôn kiểm tra và tính toán tất cả các điều kiện có trong hàm.

Tại sao File lại chạy chậm?

Nếu File của bạn chạy chậm, hãy kiểm tra xem bảng tính của bạn có chứa nhiều Volatile Function không và có bao nhiêu ô phụ thuộc vào những hàm này. Khảo sát chuỗi tính toán của Excel để tìm ra nguyên nhân chậm trễ.


Đừng quên kiểm tra các nguyên nhân khác cũng như các “Bí kíp giảm cân” cho file Excel của bạn:

Nguyên nhân khiến File Excel nặng và chậm

Cách làm nhẹ file Excel chậm và xóa bỏ mật khẩu bảo vệ

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop