應用Excel“規劃求解”解決迭代問題

肖群英，杜 超，王 靜，張雅寧

中國石油長慶油田蘇里格南作業分公司 （陜西 西安 710018）

應用Excel“規劃求解”解決迭代問題

肖群英，杜 超，王 靜，張雅寧

中國石油長慶油田蘇里格南作業分公司 （陜西 西安 710018）

在工程計算中存在大量的非線性迭代的求解問題，過去必須應用相關軟件才能解決。通過對Excel“規劃求解”工具的研究，將該工具應用于天然氣偏差因子的計算迭代問題中，得到了滿意的效果,可推廣應用于各類迭代問題中。

規劃求解；天然氣；偏差因子；迭代計算

規劃求解是在一定的限制條件下，利用科學方法進行運算，使對前景的規劃達到最優的方法，是現代管理科學的一種重要手段，是運籌學的一個分支。

Excel“規劃求解”工具是一個解決最優化問題極好的工具，目前已應用于(非)線性規劃、非線性回歸等各種工程問題[1-4]。盡管Excel“規劃求解”工具在各行各業得到廣泛應用，但大多數是求解線性規劃問題，迭代問題的求解未見任何報道。通過對Excel“規劃求解”工具進行研究，建立了應用Excel“規劃求解”工具解決迭代問題的新方法。實例應用結果表明，應用Excel“規劃求解”工具求解迭代問題，可以得到十分滿意的結果。

1 Excel“規劃求解”

Excel“規劃求解”是一組命令的組成部分，這些命令有時也稱作假設分析。假設分析：該過程通過更改單元格中的值來查看這些更改對工作表中公式結果的影響。

2 應用舉例

下面以計算天然氣偏差因子的Dranchuk-Abou-Kassem(1975)法為例，說明應用Excel的“規劃求解”工具解決迭代求解問題。

Dranchuk-Abou-Kassem(1975)法是目前計算天然氣偏差因子精度最高的方法之一，在各類天然氣藏得到相當廣泛的應用，并已成為天然氣偏差因子計算的標準方法。但該方法難以應用公式直接求解，以前必須借助相關軟件才能實現求解。

改寫式（2）得

將式（7）代入式（1），并構造如下函數：

公式（8）是一非線性隱式方程，通常應用牛頓-拉弗森迭代法求解。

式（9）的迭代求解使天然氣偏差因子的計算過程復雜化，應用普通計算難以完成。為此，通過對Excel“規劃優化”的分析研究，建立了應用“規劃求解”工具進行天然氣偏差因子迭代計算的方法。

對式（8）進行優化求解的步驟如下（圖1）：

圖1 Excel優化計算示意圖

1）在C2：C3輸入已知參數擬對比壓力Pr和擬對比溫度Tr。

2）應用式（3）～（5）分別計算a、b和c，分別填入C6：C8中。

3）在C11中設置迭代優化參數 ρR的初值，本計算設為1。

4）由于式（6）所示的參數d是 ρR的函數，只能在設置了ρR的初值后再計算，填入A15中。

5）應用式(8)計算f(ρR)的值，填入B15中。

6）點擊Excel的“規劃求解”按鈕，調用規劃求解參數設置對話框（圖2），設置目標單元格，本實例的優化目標為函數f（ρR），因此目標單元格為B15，并設置目標優化為最小值；設置可變單元格，本實例的優化參數為 ρR，因此可變單元格為C11；其他一些設置可根據需要修改。

7）以上設置全部完成后，點擊圖1所示對話框右上方的“求解”按鈕即可完成優化求解。

8）應用式（1）或式（7）計算天然氣偏差因子的值。

圖2 規劃求解參數設置對話框

在本實例中，優化后計算的天然氣偏差因子為0.989 9，與理論值完全相同。

3 結論

1）Excel“規劃求解”工具是一個非常有用的工具，可應用于解決工程中大量的非線性問題。

2）Excel“規劃求解”工具在迭代問題中的應用，得到了非常滿意的結果，該方法可推廣應用于其他各類迭代問題中。

3）Excel“規劃求解”的應用，將使眾多的科研人員從非線性問題的求解方法研究中解脫出來，從事其他方面的研究。

[1]董 飛.資源分配問題的動態規劃求解方法[J].凱里學院學報,2015,33(3)：19-21.

[2]顧運筠.Excel規劃求解的兩類應用[J].計算機應用與軟件,2005,22(1)：137-139.

[3]孫 燾,夏 斐,劉洪波.基于動態規劃求解時間序列DTW中心[J].計算機科學,2015,42(12)：278-282.

[4]李承耕.一類決策樹不確定型庫存問題的動態規劃求解[J].長江大學學報(自然科學版),2009,6(2)：134-136.

[5]Dranchuk P M and Abou-Kassem J H.Calculations of Z-Factors for natural gases using equations of state[J].Journal of Canadian Petroleum Technology,1975,14(3)：34-36.

There are a lot of nonlinear iterative solution problems in engineering calculation.In the past,relevant software has to be used to solve them.The Excel programming solver is applied to the calculation of the deviation factor of natural gas,and satisfactory results are obtained.It can be extended to various iterative problems.

planning solution;natural gas;deviation factor;iterative calculation

肖群英（1965-），女，工程師，現主要從事天然氣開采與采氣工藝技術研究工作。

2017-07-11