巧用迭代Excel數據處理更高效

平淡

什么是迭代計算

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

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

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

應用場景1：固定人員來訪的時間

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

1自定義時間格式

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

2固定時間數據

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

公式解釋：

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

應用場景2：固定不重復的隨機數字

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

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

1公式設置

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

公式解釋：

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

2設置條件格式

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

3生成所需數據

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