巧用迭代Excel數(shù)據(jù)處理更高效

平淡

什么是迭代計(jì)算

Excel的迭代計(jì)算是指將計(jì)算結(jié)果代回原變量進(jìn)行重復(fù)計(jì)算，直到滿足特定的數(shù)值條件為止。舉個(gè)簡(jiǎn)單的例子，在A1單元格中輸入“1”，在B1單元格中輸入公式“=a1+b1”，由于在B1單元格的公式中引用了其自身進(jìn)行迭代計(jì)算，Excel會(huì)彈出循環(huán)引用的提示（圖1）。

為了允許迭代，需要依次點(diǎn)擊“文件→選項(xiàng)”，在打開(kāi)的選項(xiàng)設(shè)置窗口中切換到“公式”選項(xiàng)卡，在右側(cè)的窗格中勾選“啟用迭代計(jì)算”，并將“最多迭代次數(shù)”設(shè)置為2（圖2）。

這樣，圖1中共進(jìn)行了2次迭代運(yùn)算（第1次：1+0，第2次：1+1+0），所以結(jié)果為2。由此可見(jiàn)，通過(guò)開(kāi)啟迭代計(jì)算，可以將原來(lái)循環(huán)引用的數(shù)據(jù)固定為指定的數(shù)值。下面介紹迭代計(jì)算的實(shí)際應(yīng)用。

應(yīng)用場(chǎng)景1：固定人員來(lái)訪的時(shí)間

為了便于管理來(lái)訪人員，很多公司都會(huì)要求來(lái)訪者在前臺(tái)進(jìn)行登記。借助迭代計(jì)算可以實(shí)現(xiàn)來(lái)訪時(shí)間的自動(dòng)錄

1自定義時(shí)間格式

打開(kāi)來(lái)訪人員登記文檔，選中B列并右擊，依次選擇“設(shè)置單元格格式→自定義”，在右側(cè)窗格的“類型”下輸入“ y y y y / m / dh：mm”，即將該列數(shù)據(jù)的顯示格式指定為“年月日 時(shí)：分”樣式（圖3）。

2固定時(shí)間數(shù)據(jù)

定位到B 2單元格并輸入公式“=IFS（A 2=""，""，B2=""，NOW（），B2<>""，B2）”，然后下拉填充。這樣只要在A列中輸入來(lái)訪者的姓名，那么在B列對(duì)應(yīng)的單元格中就會(huì)自動(dòng)錄入A列中輸入姓名時(shí)對(duì)應(yīng)的時(shí)刻（圖4）。

公式解釋：

這里使用IFS函數(shù)對(duì)B2單元格的值進(jìn)行多條件判斷，如在A 2單元格中輸入“張三”，此時(shí)由于A 2單元格不為空，所以第一條件值為Fal se，不執(zhí)行;接著執(zhí)行第二條件，此時(shí)在B 2 單元格中顯示NOW函數(shù)的數(shù)值，因?yàn)锽2單元格也不為空，所以值同樣為Fal se，不執(zhí)行;繼續(xù)執(zhí)行第三條件，因?yàn)榇藭r(shí)B2單元格中顯示NOW函數(shù)的數(shù)值（即不為空），所以此時(shí)的值為T(mén)r ue，執(zhí)行迭代計(jì)算后顯示A2單元格中輸入數(shù)據(jù)時(shí)的時(shí)間值。它和直接在B2單元格中輸入NOW公式不同，后者的數(shù)值會(huì)隨當(dāng)前時(shí)刻同步變化，通過(guò)迭代計(jì)算后則可以固定不變了。

應(yīng)用場(chǎng)景2：固定不重復(fù)的隨機(jī)數(shù)字

在執(zhí)行一些隨機(jī)性的抽查工作時(shí)，我們經(jīng)常需要生成一些不重復(fù)的隨機(jī)數(shù)字。比如公司組織的生產(chǎn)安全知識(shí)比賽（總共100道題），現(xiàn)在需要在1∽100之間生成30個(gè)不重復(fù)的隨機(jī)數(shù)字，然后讓員工隨機(jī)選擇作答。

如果要生成隨機(jī)數(shù)字，使用RANDBETWEEN函數(shù)即可。但是，如果直接在A2單元格中輸入公式，可以默認(rèn)會(huì)生成很多重復(fù)的數(shù)字，而且由于RANDBETWEEN是易失性函數(shù)，生成隨機(jī)數(shù)字后，若文檔中進(jìn)行了任何數(shù)據(jù)的更改（如增加其他數(shù)據(jù)），生成的數(shù)字又會(huì)再次發(fā)生變化，使用起來(lái)極為不便（圖5）。此時(shí)，可以使用迭代生成不重復(fù)且可以固定使用的數(shù)字。

1公式設(shè)置

定位到A 2單元格并輸入公式“=IF（SUM（$B$2：$B$31）<>30，RANDBETWEEN（1，100），A2）”，定位到B2單元格并輸入公式“=COUNTIF（$A$2：$A$31，A2）”，然后選中A2：B2數(shù)據(jù)區(qū)域并下拉填充公式到A31：B31數(shù)據(jù)區(qū)域，這樣在A列中就可以看到生成的隨機(jī)數(shù)字了。不過(guò)，默認(rèn)仍然會(huì)有重復(fù)的數(shù)字（圖6）。

公式解釋：

使用I F函數(shù)對(duì)B 2 ： B 3 1數(shù)據(jù)區(qū)域的和進(jìn)行判斷，如果不等于3 0（即B列顯示重復(fù)數(shù)字有>1），那么就使用R ANDBETWEEN函數(shù)再生成隨機(jī)數(shù)字，否則直接顯示A2單元格中的數(shù)字。這里同樣利用迭代計(jì)算，可以實(shí)現(xiàn)A列只顯示不重復(fù)的數(shù)字。

2設(shè)置條件格式

為了方便查看重復(fù)數(shù)據(jù)，選中A列并依次點(diǎn)擊“開(kāi)始→ 條件格式→ 突出顯示單元格規(guī)則→重復(fù)值→選擇默認(rèn)設(shè)置”，這樣重復(fù)的數(shù)字就會(huì)被自動(dòng)填充為淺紅色標(biāo)注，B列會(huì)顯示重復(fù)次數(shù)（圖7）。

3生成所需數(shù)據(jù)

打開(kāi)圖2所示的窗口，將“最多迭代次數(shù)”設(shè)置為1000，接著返回圖6所示的窗口，按下F9鍵再次加載運(yùn)算，可以看到每一次按下F9鍵，隨機(jī)數(shù)字會(huì)隨之發(fā)生變化。一般只要按F9鍵2～3次，此時(shí)A列就不出現(xiàn)紅色重復(fù)數(shù)字了。此時(shí)無(wú)論再按多少次F9鍵都不會(huì)再發(fā)生變化。最后按提示將A列的數(shù)據(jù)復(fù)制后粘貼為數(shù)值使用即可（圖8）。