|
今天我們把主題聚焦在一類特殊函數(shù)造成的問題上——volatile function。 這一類函數(shù)有人把他們翻譯成“易變函數(shù)”，其實(shí)挺貼切的。 本系列其他文章： Excel表格為什么那么慢已經(jīng)應(yīng)該如何解決（一）概述 Excel表格為什么那么慢已經(jīng)應(yīng)該如何解決（二）常見求和公式效率分析 Excel表格為什么那么慢已經(jīng)應(yīng)該如何解決（三）查找公式的效率分析 一個(gè)Excel文件中，很可能會(huì)包含大量的公式，在一般的情況下，Excel可以保持一個(gè)過得去的性能（當(dāng)然，在有些情況下，你必須仔細(xì)設(shè)計(jì)這些公式，我們?cè)凇癊xcel表格為什么那么慢已經(jīng)應(yīng)該如何解決”系列前面的文章中介紹過一些場(chǎng)景）。Excel之所以可以做到這一點(diǎn)，是因?yàn)?，?duì)于大部分函數(shù)，Excel并不會(huì)在每一次表格數(shù)據(jù)發(fā)生變化的時(shí)候都去計(jì)算一遍，只有當(dāng)該公式引用（直接或間接）的單元格發(fā)生變化的時(shí)候，Excel采取計(jì)算該公式。 采用這個(gè)方法，Excel就可以保證每次只計(jì)算很小比例的公式，降低了計(jì)算量。 但是，如果你的公式中使用了“易變函數(shù)”，每次Excel有變化時(shí)（比如輸入數(shù)據(jù)），這些公式都會(huì)被重新計(jì)算，這樣就會(huì)增加Excel的計(jì)算時(shí)間。如果有很多這樣的公式，Excel的使用體驗(yàn)就會(huì)很差。 例如，Rand和Randbetween是兩個(gè)易變函數(shù)。在下面的數(shù)據(jù)中，B3:E10000使用這兩個(gè)函數(shù)填充： 同時(shí)，在另一個(gè)表的B5:B33使用Sumif函數(shù)計(jì)算條件求和，引用的區(qū)域就是上圖的B:E列： 正常情況下，如果我們修改別的單元格的值，按說不會(huì)引起公式的計(jì)算。但是，下面的動(dòng)圖告訴我們，每次我們修改其他單元格，就會(huì)導(dǎo)致重算： 在當(dāng)前普遍使用的Excel各個(gè)版本中，易變函數(shù)有下面這些：
這個(gè)清單不是很長。但是有一些經(jīng)常使用的函數(shù)（例如，前面6個(gè)函數(shù)，尤其是前面四個(gè)函數(shù)）。 INFO和CELL不常用，而且依賴于參數(shù)。我們會(huì)在以后適當(dāng)?shù)臅r(shí)候?yàn)榇蠹医榻B這兩個(gè)函數(shù)的用法。 這里比較奇怪的是SUMIF函數(shù)。在我們的印象里這個(gè)函數(shù)不應(yīng)該是易變函數(shù)。但是在某些情況下，它會(huì)變成易變函數(shù)，我們?cè)诒疚暮竺鏁?huì)為大家介紹。 另外，這個(gè)清單還得添加一個(gè)成員
出乎所有人的意料。不管你在條件格式里使用什么樣的函數(shù)，條件格式都可以看作是易變函數(shù)。也就是說，條件格式里的函數(shù)總是會(huì)重新計(jì)算。 如果是你自己做的表格，你根據(jù)上面的清單對(duì)比自然就知道有沒有易變函數(shù)了。 如果你忘了，或者是別人做的表格，這里有一個(gè)簡(jiǎn)單的方法可以判斷是否可能有易變函數(shù)： 打開Excel文件，點(diǎn)擊保存按鈕。然后按F9，然后馬上點(diǎn)擊關(guān)閉按鈕，如果Excel提示你需要保存，那么很可能這個(gè)文件里有易變函數(shù)。
并不是所有操作都會(huì)導(dǎo)致易變函數(shù)重新計(jì)算。下面是一些會(huì)引起易變函數(shù)及相關(guān)單元格重新計(jì)算的操作：
需要強(qiáng)調(diào)的是，易變函數(shù)本身的計(jì)算速度其實(shí)是很快的（大部分情況下），它們對(duì)速度的影響主要是會(huì)導(dǎo)致跟它們相關(guān)的所有單元格都會(huì)重新計(jì)算，影響的是每次操作Excel的響應(yīng)速度。 很多人會(huì)采用一個(gè)顯而易見的操作：將計(jì)算選項(xiàng)從“自動(dòng)”改為“手動(dòng)”：
這樣做可以立竿見影的改進(jìn)Excel的響應(yīng)時(shí)間。 但是，我必須要強(qiáng)調(diào)的是這是一個(gè)我絕對(duì)不會(huì)推薦的做法。實(shí)際上，我強(qiáng)烈建議： 一定要把計(jì)算選項(xiàng)設(shè)為“自動(dòng)” 之所以這么建議，是因?yàn)閮蓚€(gè)原因：首先，你很可能在Excel中將某些報(bào)表作為中間結(jié)果，這樣在你的某些數(shù)據(jù)修改了的情況下，由于是手動(dòng)計(jì)算模式，這些中間報(bào)表并沒有更新，從而導(dǎo)致你后續(xù)的其他分析錯(cuò)誤。其次，你很可能要將這個(gè)文件分享給其他人，而其他人并不知道這個(gè)計(jì)算選項(xiàng)是手動(dòng)的，這樣她就會(huì)得到錯(cuò)誤的表格數(shù)據(jù)。 對(duì)于大部分的易變函數(shù)來說，都有減少負(fù)面影響的方法。不過需要針對(duì)不同的函數(shù)采用不同的方法，篇幅過長，我們放到后續(xù)的文章中介紹。今天我們只介紹一下SUMIF的問題。
一般情況下，SUMIF不是一個(gè)易變函數(shù)，只有它引用單元格發(fā)生變化時(shí)，才會(huì)重新計(jì)算。但是，下面的SUMIF公式是一個(gè)易變函數(shù)： =SUMIF(Sheet1!$B$3:$D$10000,Index!$H$2,Sheet1!$E$3:$E$10000) 仔細(xì)觀察，就會(huì)發(fā)現(xiàn)這個(gè)公式跟我們通常所用的公式是不一樣的，它引用了兩個(gè)區(qū)域，但是這兩個(gè)區(qū)域的大小是不一樣的，一個(gè)是從B到D，共3列，一個(gè)是只有E一列。 當(dāng)SUMIF中各個(gè)參數(shù)區(qū)域引用的單元格個(gè)數(shù)不一致時(shí)，這個(gè)公式就會(huì)變成易變函數(shù)。 解決方法其實(shí)也很簡(jiǎn)單，將這個(gè)公式改寫成各區(qū)域大小一致就可以了： =SUMIF(Sheet1!$B$3:$D$10000,Index!$H$2,Sheet1!$E$3:$G$10000)
關(guān)注本公眾號(hào)，點(diǎn)擊底部菜單“聯(lián)系客服”，與客服取得聯(lián)系，索取“計(jì)算性能分析—volatile”案例文件
|
|
|
