易失函數相關知識

06-06

在所有EXCEL的三百多個函數中，有一些函數很特殊，稱之為「易失函數」。

在一個工作表中使用了這類函數以後，每輸入（或刪除）一個數據，整個工作表就要重算一次。甚至當我們打開一個工作簿，不作任何操作，直接關閉，也會彈出一個對話框，詢問「是否保存更改」，這是易失函數使然。看見這種情況，我們就可以斷定：這個工作簿使用了易失函數。

要了解易失函數，首先我們要知道，EXCEL是按什麼次序計算的。在一個工作表中寫了很多一個套一個的公式，EXCEL是如何決定計算次序的呢？

在打開工作表時，EXCEL掃描所有的公式，理清其相互關係，在內存中創建一個「關係鏈」。比如我們在C1的公式中引用B1:B20，而B1又依賴於A1的數據，B10依賴於A10的數據，那麼我們就說A1,A10,B1:B20及C1一起組成了一個關係鏈，計算順序當然就是A1,A10 => B1:B20 => C1，所有這些單元格，只要有一個改動了數據，整個關係鏈就要重算一次。

但注意這一點：關係鏈之外的公式不會重算。這是EXCEL的「聰明」的計算，這一點很重要。正是由於這一點，EXCEL的整體運算速度才得以保證。

但易失函數卻偏偏不「遵循」這個規矩。

——只要工作表中有一個單元格變化了，所有的包含易失函數的公式就會全部重算，不管改動的那個單元格是不是在關係鏈上。

——甚至，象我們前面說到的，只要打開和關閉工作表，都會引發全部重算。

為了減少這種易失函數造成的速度影響，我們可以：

1、設置重算方式為「手工重算」（菜單：工具－選項－重新計算－點選「手工重算」，並勾選「保存前自動重算」）

這樣,改動數據,所有的公式都不會進行計算了。需要看結果時，再按下F9。

【但如果表中使用了宏表函數，有些情況按F9也不予重算，這時要按ctrl+alt+shift+F9，進行「全部重算」】

【有些自定義函數也需按ctrl+alt+shift+F9才能重算】

2、在公式中儘可能少用易失函數。

但很多易失函數不可能完全不用，這時尤其要注意減小它的引用範圍和使用範圍。

那麼，哪些是易失函數？

對於OFFICE XP版和OFFICE 2003版，下面這些是易失函數：

OFFSET，INDIRECT，TODAY，NOW，RAND，CELL，INFO

對於較低版本，INDEX也曾經是易失函數。（最近有人指出：INDEX在最新版本里也是個「半易失函數」）

【附1】

我們都知道易失函數會引發重算。但重算範圍到底有多大？哪些公式參與了重算？我一直不大清楚。

最近在為工作製作一個管理系統的過程中，碰見了這個問題，由於數據量比較大，公式較多，又在不少公式中難以避免地使用了易失函數，導致速度問題凸顯。

為弄清易失函數對速度的影響，我作了一系列的測試，發現一些以前沒注意過的現象，提出來供大家討論。 1、一般都認為：易失函數會引發工作表的全部重算——過去我理解這句話的意思是：整個工作表的全部公式都重算一遍。但在我的測試中，所表現出的特性不是這樣。測試方法：設置一個大數據量的工作表，再作幾千行範圍的數組公式（如C1:C5000=SUMPRODUCT(A1:A5000*B1:B5000)）【其中範圍5000行可根據自己計算機的配置自行決定，不要太大，以免時間過長，但也不能太小，否則不容易測量時間】第一步：先運行數組公式，記下運行時間T1；第二步：再寫一個易失函數公式，如：D1=NOW()，D2=RAND()之類，運行它（按F9）,運行時間：T2（非常小）顯然兩次運行時間有天壤之別。這就是說，含有易失函數公式，不會導致非易失函數公式重算。 2、含有易失函數公式的重算會不會導致其關係鏈上的所有公式重算？測試方法：在第一次測試的基礎上，增加一個公式：E1=OFFSET(C1,,,5000)這樣E1和C1:C500就組成了一個關係鏈。E1的運行會不會導致C1:C5000重算？運行一下，得到時間T3，也是非常小，遠遠小於T1。這就是說，易失函數的重算也不會導致其關係鏈上的非易失函數公式重算 3、從上面的測試來看，易失函數的重算，指的僅僅是易失函數公式本身的重算，與所有的不含易失函數的公式無關。那麼我們可以說「易失函數會引發工作表的易失函數公式全部重算」嗎？還不能。再作一個測試：<br>第一步，把上面的C1:C5000=SUMPRODUCT(A1:A5000*B1:B5000)改為：C1:C5000=SUMPRODUCT(A1:A5000*B1:B5000)*NOW()，這樣我們就有了5000個含易失函數的公式。運行一下，看看時間(T4)【要想運行它，隨便在一個空單元格輸入一個數據，回車就開始運行】這個時間和T1差不多。切換到下一個空的工作表中去。在一個單元格隨便輸入一個數據，它也會引發重算！ ——這就是說，易失函數引發的重算不是工作表級別的，而是工作簿級別的！ 4、下一個測試：把上面作的這個工作簿存為BOOK1，再將它拷貝成另一個文件BOOK2，同時打開兩個文件。隨便在一個工作表中輸入一個數據，記下運行時間T5，顯然T5=2*T4 ——這就是說，易失函數引發了兩個打開的工作簿都在重算！儘管兩個表根本沒有關聯。（如果打開更多的工作簿，會發現所有工作簿都在同時重算。） 5,再作進一步的測試：關閉BOOK2，新建一個BOOK3,現在我們知道BOOK1有幾千個易失函數公式，運行時間是T4，而BOOK3是空表。在BOOK3里輸入一個數據，我們會看到他們也在重算，重算的時間=T4

改變BOOK3和BOOK1的打開順序，測試結果不變。上面只說到了易失函數，實際上對易失性操作的測試結果也完全相同。根據上面的測試，我們是不是應該這樣來表述易失函數引發的重算：

易失函數（包括易失性操作）會引發所有打開工作簿里含易失函數的公式全部重算，但不含易失函數的公式不會參與重算(註：這裡說的「公式」，應該包括定義名稱里的公式)

【附2】

在代碼編輯窗口中，單擊菜單欄「插入」 →「模塊」，粘貼以下代碼：

Option Explicit

Option Base 1

Option Compare Text

Public jCalcSeq As Long

Public Function CalcSeqCountRef(theRange As Range) As Variant

jCalcSeq = jCalcSeq + 1

CalcSeqCountRef = jCalcSeq + theRange - theRange

End Function

Sub resetcounter()

jCalcSeq = 0

End Sub

每按下一次F9鍵，易失函數的計算次數計數器將會發生變化，而非易失函數就不會變化，如下圖所示：