今天介紹一個技巧，不用VBA，就可以自動記錄輸入的時間。這個技巧用到了Excel中的迭代計算 01
數(shù)據(jù)和效果
假設(shè)有一個表格，記錄了每一份潛在客戶的信息，我們需要記錄客戶是否完成繳費，
如果有客戶完成了繳費，我們會在“費用”列中對應(yīng)單元格手工輸入金額，并且在“續(xù)費時間”列自動記錄繳費的時間（假定，我們錄入金額的時間就是客戶完成 繳費的時間）。
如果客戶繳費比較集中，就會導(dǎo)致比較忙亂，所以，我們希望輸入金額后，能夠在續(xù)費時間列中自動記錄當(dāng)前時間，就像下面的動圖所顯示的效果一樣：
02
方法分析
使用VBA可以很容易完成這個工作。不過對于大部分同學(xué)來說，并不能自如的使用VBA，因此可以暫時排除這個方案。
所以，我們考慮使用函數(shù)。
記錄當(dāng)前的時間有一個函數(shù)：NOW()。這個函數(shù)的作用就是返回當(dāng)前的時間：
我們可以在費用列中輸入公式：=IF(D3<>"",NOW(),"")
這里我們使用了一個IF函數(shù)來判斷：
如果D3不等于空（完成繳費），就使用NOW()函數(shù)返回當(dāng)前時間
如果D3等于空（未繳費），就返回空
這樣，只要我們在D3單元格中輸入金額，就會自動記錄當(dāng)前時間。
這里要提醒一下，缺省情況下，這個公式所在的單元格只會顯示時間，如果希望顯示日期和時間，請將該單元格的格式設(shè)置為自定義格式：
這樣，看似解決了問題。但是這個方法實際上并不能完成我們的需求：
如果你后續(xù)修改了其他的費用單元格（增加或者刪除），所有人的續(xù)費時間都會顯示為相同的時間值：
所以，此路不通！
03
一個很多人不了解甚至害怕的特性：循環(huán)引用和迭代計算
這里我們需要利用Excel的一個特別功能：迭代計算
大部分同學(xué)對這個特性很陌生。其實，有相當(dāng)多的同學(xué)應(yīng)該在不了解的情況下接觸過特性了。例如，下面這個對話框，有些同學(xué)一定遇見過：
當(dāng)你寫了一個公式，回車確認輸入后，也可能會彈出這個對話框。
這個對話框是說，你的單元格存在“循環(huán)引用”。什么是循環(huán)引用呢？簡單說，就是一個單元格引用了自身。比如在A2中輸入公式：=A2。當(dāng)然，你一般不會犯這種錯誤，所以最簡單的循環(huán)引用的場景是經(jīng)過兩次公式引用到自己：
在A2單元格中輸入公式：=B2，然后在B2單元格中輸入公式：=A2，這樣從A2的角度看就是：A2--->B2---A2，就必然會出現(xiàn)這個對話框。
真正的循環(huán)引用有可能很復(fù)雜，比如B引用A，C引用B，D引用C，E引用D，然后A引用D，此時引用鏈?zhǔn)沁@樣的：A--->B--->C--->D--->E---A。這個引用鏈越長，你就越難從公式中發(fā)現(xiàn)誰引用了誰，何況有些公式還很復(fù)雜。此時，需要用到一個工具：錯誤檢查。
在公式選項卡中，可以找到這個工具：
Excel會將循環(huán)引用的鏈條用藍色箭頭線標(biāo)識出來。
那么，發(fā)生了循環(huán)引用怎么辦？
循環(huán)引用往往是個錯誤，是我們手誤造成的。一旦發(fā)生，需要仔細檢查公式，然后修改。如果公式比較長，需要花比較長的時間進行檢查，還不一定檢查的出來。有時，可以將所有相關(guān)公式刪除掉，然后重新輸入公式。
但是，有的時候，我們需要這個循環(huán)引用。這時，我們實際上需要的是迭代計算。
所謂迭代計算，是說我們用同樣的規(guī)則，連續(xù)執(zhí)行N次。舉個例子來說：
假設(shè)我們把B2當(dāng)作一個未知數(shù)，計算規(guī)則（即公式）是：=B2+1
執(zhí)行一百次（N=100）
初始值B2：=0（因為此時B2是空的）
第1次：0+1=1
第2次：1+1=2
第3次：2+1=3
第4次：3+1=4
......
第N次：N-1+1=N
我們來看一個實際的例子：
假設(shè)在B3單元格中輸入公式：=B2+1，
結(jié)果只會是1，因為B2單元格沒有變化，沒有任何迭代。要想迭代，我們需要將公式計算結(jié)果返回到B2中，然后才能往下多次計算。
因此，我們需要在B2中輸入公式：=B2+1
此時，B2結(jié)果為0。這是因為此時發(fā)生了循環(huán)引用（B2--->B2），所以根本沒有計算。
要想實現(xiàn)這個迭代過程，你需要在Excel中激活迭代計算。
點擊文件選項卡，然后點擊選項：
在選項對話框中，左邊選擇“公式”，右邊勾選“啟用迭代計算”：
注意，缺省的迭代次數(shù)是100，精度是0.0001。這兩個值決定迭代什么時候終止。
所有的迭代必須有終止條件，否則Excel就會陷入死循環(huán)。這里有兩個終止條件：一個是迭代次數(shù)，缺省情況下，最多迭代100次。精度是另一個終止條件，當(dāng)本次的計算結(jié)果跟上一次的計算結(jié)果相差小于等于精度時，迭代就會終止。
你可以試試將迭代次數(shù)設(shè)一個很大的數(shù)試試（比如100萬）??。
點擊確定后，返回Excel：
此時，B2單元格的值變成了100。
這是因為，B2中的公式：=B2+1發(fā)生了100次迭代計算。
04
自動記錄時間
現(xiàn)在我們利用這個特性，實現(xiàn)自動記錄輸入輸入時間。
在續(xù)費時間列中，輸入公式：=IF(D3="","",IF(E3<>"",E3,NOW()))
這個公式仍然是通過最外面IF公式判斷D3，根據(jù)D3的值，決定是顯示時間還是空值。這個最外面的IF很簡單，重點是內(nèi)層的IF公式：IF(E3<>"",E3,NOW())
這個公式根據(jù)E3進行判斷：
如果E3不為空值，返回E3。注意這時已經(jīng)發(fā)生了迭代，只不過根據(jù)精度條件，一次就終止了）
如果E3空值，返回NOW()，即當(dāng)前時間
這樣，比如D3中輸入費用時，會發(fā)生以下情況：
如果E3原來為空，那么就會返回當(dāng)前時間。這是一個客戶完成繳費后正常發(fā)生的情形
此時E列其他單元格有兩種情況，要么為空，要么已經(jīng)記錄了時間。如果已經(jīng)記錄了時間，根據(jù)內(nèi)層的IF公式，這個值不變。如果沒有記錄時間，說明對應(yīng)的費用列是空的，也就不會有值。所以不會發(fā)生所以時間都變?yōu)楫?dāng)前時間的情況。
本文介紹的這個技巧是一個非常有用的技巧。因為有了迭代計算，使我們不用編程就可以完成很多原來必須依靠編程或者復(fù)雜的表格設(shè)計才能實現(xiàn)的結(jié)果。
這里的公式實際上很簡單。不過是這個迭代計算需要花點心思去理解一下。一旦你理解了這個技巧，在很多場景中都可以利用它簡化表格設(shè)計，甚至實現(xiàn)原來實現(xiàn)不了的功能。后面我會為大家介紹使用這個技巧的其他的應(yīng)用場景。
趕緊用起來吧！
聯(lián)系客服
