Giới thiệu các hàm tự tính toán lại (Volatile Function) trong Excel

Trong Excel có một số hàm được gọi là “volatile function”, có nghĩa là hàm sẽ tự động tính toán lại mỗi khi có sự thay đổi trong trang tính. Đối với dữ liệu nhỏ, việc xuất hiện những hàm này không ảnh hưởng gì. Tuy nhiên với sổ tính chứa những bộ dữ liệu lớn, việc sử dụng volatile function sẽ gây ảnh hưởng lớn – nhẹ: làm chậm file, nặng: treo file, gần như không thể sử dụng được. Vậy hàm tự động tính toán lại bao gồm những hàm nào? Hãy tìm hiểu ngay trong bài viết dưới đây:

Xem nhanh

Volatile Function

Một số hàm trong Excel hiển nhiên là hàm tự tính toán lại: RAND(), NOW(), TODAY()

1 số “ít hiển nhiên” hơn: OFFSET(), CELL(), INDIRECT(), INFO()

1 số tự tính toán trong phiên bản này, nhưng không phải trong phiên bản khác: INDEX() từ Excel 97 là một hàm non-volatile.

Những hàm tưởng là hàm tự tính toán lại nhưng thực chất là không: INDEX(), ROWS(), COLUMNS(), AREAS().

CELL(“filename”) là một hàm volatile dù cho Microsoft không công nhận.

Một cú pháp cụ thể trong hàm SUMIF cũng trả về kết quả một hàm tự động tính toán lại. Điều này xảy ra khi kích cỡ vùng điều kiện không giống với vùng tính tổng. Ví dụ: =SUMIF(A1:A4,”>0″,B1) là volatile, trong khi =SUMIF(A1:A4,”>0″,B1:B4) thì không, dù cho cả 2 đều sẽ tham chiếu đến vùng B1:B4.

Sử dụng volatile function trong công thức sẽ biến cả công thức đó thành volatile, dù cho có thể phần công thức đó không đáp ứng điều kiện. Ví dụ: =IF(1<2,99,NOW()) sẽ luôn trả về 99, còn phần hàm tự tính toán lại NOW() không bao giờ được gọi ra. Tuy nhiên ô chứa công thức với hàm IF này sẽ luôn được coi là volatile.

Trường hợp ngoại lệ:

Nếu ô A1 chứa công thức =NOW(), và ô B1 chứa công thức =IF(1<2,99,A1), thì B1 sẽ không được coi là một volatile.

Tham chiếu phụ thuộc hàm Volatile

Có 2 kiểu tham chiếu phụ thuộc: trực tiếp và gián tiếp.

Phụ thuộc trực tiếp (Direct dependents) sẽ luôn được tự động tính toán lại:

Nếu ô A1 chứa công thức =NOW() và A2 chứa =A1, A3 = A2… thì toàn bộ ô A2 và A3 sẽ luôn được tính toán lại theo A1.

Phụ thuộc gián tiếp (Indirect dependents) không phải lúc nào cũng tự tính toán lại:

Nếu ô A1 chứa công thức =NOW(), A2:A5 chứa số từ 2 tới 5

=INDEX(A1:A5, 1, 1) sẽ phụ thuộc trực tiếp và luôn tự tính toán lại.

=INDEX(A1:A5, 3, 1) phụ thuộc gián tiếp vào ô A1 và sẽ chỉ tính toán lại một khi giá trị ở các ô trong mảng INDEX vào thay đổi (chẳng hạn như thay đổi giá trị ô A5).

Lưu ý: INDEX sẽ tính toán lại khi có giá trị trong mảng nhập vào thay đổi. Bởi vậy, không nhất thiết phải thay đổi giá trị ô A3.

Conditional Formatting cũng là “volatile”

Bởi Conditional Formatting luôn cần được kiểm tra lại trong mỗi phép tính, bởi vậy những công thức được sử dụng trong đó là volatile. Đặc biệt, đây là một trường hợp tính toán lại một cách liên tục trong các trường hợp như sự thay đổi màu trong các ô, kể cả trong chế độ tính toán Manual, hay sự thay đổi được tạo nên bởi hàm trong VBA (mà sẽ không tác động tới việc tính toán) – Conditional formatting vẫn sẽ thực hiện công việc tính toán.

Những thao tác tác động tới việc tự tính toán lại

Autofilter

Bất cứ điều kiện nào được chọn bằng Autofilter sẽ đánh dấu tất cả những công thức trong vùng đó “chưa tính toán lại” dù cho bạn có sử dụng lại điều kiện trước, hoặc chưa có gì thay đổi trong vùng Autofilter. Việc này sẽ khiến Autofilter rất chậm về lâu dài.

Autofit/Điều chỉnh độ rộng hàng/cột

Nếu chế độ tính toán đang là Automatic, việc click hoặc click đúp vào lề giữa 2 cột hoặc hàng sẽ tác động tới tính toán lại. Tuy nhiên thay đổi độ rộng hàng/cột thủ công sẽ không tạo nên tác động nào.

Trong chế độ tính toán Manual, việc điều chỉnh này dù thủ công hay tự động đều không ảnh hưởng tới tự động tính toán lại.

Thêm/xóa/di chuyển hàng/cột/ô

Việc thêm/xóa/di chuyển hàng/cột/ô (sử dụng thao tác kéo thả, cut paste), đồng thời ô đó chứa 1 trong những điều kiện:

Được đặt tên
Chứa công thức tham chiếu tới địa chỉ khác/được tham chiếu từ địa chỉ khác

Sẽ khiến những ô đó được tính toán lại.

Thêm/Sửa/Xóa tên

Với việc đặt tên cho vùng (Name range), mọi hành động như thêm/sửa/xóa tên sẽ tác động tới việc tính toán lại.

Thay đổi tên/vị trí của trang tính

Đổi tên một trang tính hoặc di chuyển nó sẽ tác động tới việc tính toán lại trong chế độ Automatic.

Đối với chế độ Manual, chỉ khi có bất kỳ công thức trong trang tính khác tham chiếu tới trang tính đó mới tác động tới tính toán lại.

Xóa trang tính

Trong chế độ Automatic, việc xóa 1 trang tính sẽ tác động tới tính toán lại, tuy nhiên thêm trang tính mới thì không.

Ẩn/hiện hàng trong Excel 2003

Đối với riêng Excel 2003, việc ẩn/hiện hàng sẽ đánh dấu hàng được chọn “chưa tính toán lại”, trong chế độ automatic được hiểu rằng nó đã tác động tới tính toán lại. Tuy nhiên, đối với cột thì không.

Mở file .CSV

Mỗi khi bạn mở 1 file .CSV, dù cho qua VBA hoặc trực tiếp qua tính năng Open, việc tính toán lại trên toàn bộ các sổ tính đang mở sẽ được thực hiện. Việc tắt lệnh .EnableEvent không thể ngưng được tự tính toán. Để không tính toán lại, bạn cần sử dụng VBA để thay đổi Worksheet.EnableCalculation sang False cho từng trang trính.