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

平淡

什么是迭代計算

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

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

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

應(yīng)用場景1：固定人員來訪的時間

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

1自定義時間格式

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

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

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

公式解釋：

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

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

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

如果要生成隨機數(shù)字，使用RANDBETWEEN函數(shù)即可。但是，如果直接在A2單元格中輸入公式，可以默認(rèn)會生成很多重復(fù)的數(shù)字，而且由于RANDBETWEEN是易失性函數(shù)，生成隨機數(shù)字后，若文檔中進行了任何數(shù)據(jù)的更改（如增加其他數(shù)據(jù)），生成的數(shù)字又會再次發(fā)生變化，使用起來極為不便（圖5）。此時，可以使用迭代生成不重復(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列中就可以看到生成的隨機數(shù)字了。不過，默認(rèn)仍然會有重復(fù)的數(shù)字（圖6）。

公式解釋：

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

2設(shè)置條件格式

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

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

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