-
迭代計算
鎖定
迭代計算次數指允許公式反覆計算的次數,在Excel中通常只針對循環引用生效.其他公式在循環引用狀態下不產生變化。
- 中文名
- 迭代計算
- 外文名
- Iterative calculation
- 應 用
- 計算機科學
迭代計算迭代特性
在計算機科學中,迭代是程序中對一組指令(或一定步驟)的重複。它既可以被用作通用的術語(與“重複”同義),也可以用來描述一種特定形式的具有可變狀態的重複。
而在第二種(更嚴格的)意義下,迭代描述了在指令式編程語言中使用的編程風格。與之形成對比的是遞歸,它更偏向於聲明式的風格。
迭代計算終止準則
由於數值迭代是逐步逼近最優點而獲得近似解的,它無限地接近於最優點卻又不是理論上的最優點,所以就需要考慮在什麼樣的條件下才終止迭代,獲得一個足夠精度的近似極小點,這一條件就是迭代計算的終止準則。
(1) 點距準則
當相鄰兩迭代點X(k)、X(k+1)之間的距離已達到充分小時,即小於或等於規定的某一很小正數ε時,迭代終止。一般用兩個迭代點向量差的模來表示,即
(2)函數下降量準則
當相鄰兩迭代點X(k)、X(k+1)的目標函數值的下降量已達到充分小時,迭代終止。一般用目標函數值下降量的絕對值來表示,即
或用目標函數值下降量的相對值來表示,即
(3)梯度準則
當目標函數在迭代點的梯度已達到充分小時,迭代終止。一般用梯度向量的模來表示,即
在優化設計中,一般只要滿足以上終止準則之一,則可認為設計點收斂於極值點。應該指出,有時為了防止當函數變化劇烈時,點距準則雖已滿足,求得的最優值f(X(k+1))與真正的最優值f(x*)仍相差較大;或當函數變化緩慢時,目標函數值下降量準則雖已得到滿足,但所求得的最優點X(k+1)與真正的最優點X*仍相距較遠,往往將前兩種終止準則結合起來使用,要求同時成立。至於梯度準則,僅用於需要計算目標函數梯度的最優化方法中。
[2]
迭代計算迭代計算設置
(1)打開存在循環引用的工作表,例如,此處打開“\源文件\ch20\設置循環引用.xlsx,如圖20.17所示。
(2)切換到“文件”選項卡,然後單擊“選項”命令,打開“Excel選項”對話框。
(4)單擊“確定"按鈕,完成迭代計算的設置操作,同時在工作表中顯示進行1000 次迭代計算的結果,如概述圖所示。
迭代計算迭代計算應用
迭代計算累加
利用迭代計算和循環引用可以實現單元格數值累加。例如,要求在A2錄入數據.C2累加A2 錄入的所有數據,D2累加A2的錄入次數.實現步驟如下:
(1)將迭代計算次數設置為1;
(2)在C2單元格錄入以下公式,如圖2左圖所示,表示在C2的值的基礎上追加A2的值。
=C2+A2
=IF(AND(A2=0,D2=0),0,IF(AND(A2=0,D2>O),D2,D2+1))
D2本身只需要“=D2+l”即可實現需求,只是為了防止一些意外情況而採用IF函數防錯。例如,D2錄入公式後會自動計算一次,不管A2單元格是否空白,這將造成多累加一次,而公式中第一個IF函數可以解決這個問題。第二個IF函數用於解決A2單元格錄入刪除數據時造成D2也累加一次的問題。
(4)在A2單元格錄入10,C2單元格的公式返回10,D2單元格的公式返回1,表示第一次累加,總數量為10,如圖2中圖所示。
(5)在A2單元格錄入40,C2單元格的公式返回50,D2單元格的公式返回2,表示第二次累加,總數量為50,如圖2右圖所示。
迭代計算自動添加不更新的時間
Excel有一個時間函數Now,它可以生成當前系統時間。函數運算結果在工作簿刷新時會隨時間變化而自動更新。例如,在單元格中錄入公式“=NoW()”,公式返回錄入公式的時間,包括年、月、日、時、分、秒。當按下【F9】鍵刷新工作簿時,時間會自動更新,變為刷新工作簿的時間。
[4]
如果需要函數一旦產生時間後,該時間值不再更新,那麼可以採用循環引用配合迭代計算來實現,步驟如下:
(1)在圖3所示的倉庫入庫表中的C2單元格錄入以下公式:
=IF(B2="","",IF(C2<>"",C2,TEXT(NOW<>,"hh:mm:ss")))
(2)在B2錄入數量50, C2將自動產生當前系統 時間,如圖4左圖所示.
或許有讀者會有疑問,直接在C列錄入當前時間不是也可以實現不更新的時間嗎?況且它還不涉及如此複雜的操作。其實使用公式有它獨特的作用——自動,即時間自動產生,不需要手工錄入,它除了減少輸入數據的時間外,還能確保時間準確,而且格式統一。
[4]
迭代計算解方程
解二元一次方程,迭代計算實在得心應手。
假設得知有以下兩個方程式,需求X與Y的解:
10X+5Y=200
3X=Y+25
思路與步驟如下:
(1)將以上兩個方程式轉換成“X=…”、 “Y=…”的形式。
X=(Y+25)/3
Y=(200-5X)/10
(2)將迭代計算次數設定為10000次。
(3)在C2單元格錄入以下公式:
=(C3+25)/3
(4)在C3單元格錄入以下公式:
=(200-5+C2)/10
由於迭代計算是一個推算過程,逐漸產生最接近真實結果的解,所以它得到的解有可能正確有可能錯誤。但誤差會相當小。在選項中設置的誤差值和迭代計算次數直接影響所得到的解的正確性,次數越大越接近真實的解,但是也耗費更長的時間。
[4]