應(yīng)用Excel“規(guī)劃求解”解決迭代問題

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

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

應(yīng)用Excel“規(guī)劃求解”解決迭代問題

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

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

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

規(guī)劃求解；天然氣；偏差因子；迭代計(jì)算

規(guī)劃求解是在一定的限制條件下，利用科學(xué)方法進(jìn)行運(yùn)算，使對(duì)前景的規(guī)劃達(dá)到最優(yōu)的方法，是現(xiàn)代管理科學(xué)的一種重要手段，是運(yùn)籌學(xué)的一個(gè)分支。

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

1 Excel“規(guī)劃求解”

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

2 應(yīng)用舉例

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

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

改寫式（2）得

將式（7）代入式（1），并構(gòu)造如下函數(shù)：

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

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

對(duì)式（8）進(jìn)行優(yōu)化求解的步驟如下（圖1）：

圖1 Excel優(yōu)化計(jì)算示意圖

1）在C2：C3輸入已知參數(shù)擬對(duì)比壓力Pr和擬對(duì)比溫度Tr。

2）應(yīng)用式（3）～（5）分別計(jì)算a、b和c，分別填入C6：C8中。

3）在C11中設(shè)置迭代優(yōu)化參數(shù) ρR的初值，本計(jì)算設(shè)為1。

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

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

6）點(diǎn)擊Excel的“規(guī)劃求解”按鈕，調(diào)用規(guī)劃求解參數(shù)設(shè)置對(duì)話框（圖2），設(shè)置目標(biāo)單元格，本實(shí)例的優(yōu)化目標(biāo)為函數(shù)f（ρR），因此目標(biāo)單元格為B15，并設(shè)置目標(biāo)優(yōu)化為最小值；設(shè)置可變單元格，本實(shí)例的優(yōu)化參數(shù)為 ρR，因此可變單元格為C11；其他一些設(shè)置可根據(jù)需要修改。

7）以上設(shè)置全部完成后，點(diǎn)擊圖1所示對(duì)話框右上方的“求解”按鈕即可完成優(yōu)化求解。

8）應(yīng)用式（1）或式（7）計(jì)算天然氣偏差因子的值。

圖2 規(guī)劃求解參數(shù)設(shè)置對(duì)話框

在本實(shí)例中，優(yōu)化后計(jì)算的天然氣偏差因子為0.989 9，與理論值完全相同。

3 結(jié)論

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

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

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

[1]董 飛.資源分配問題的動(dòng)態(tài)規(guī)劃求解方法[J].凱里學(xué)院學(xué)報(bào),2015,33(3)：19-21.

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

[3]孫 燾,夏 斐,劉洪波.基于動(dòng)態(tài)規(guī)劃求解時(shí)間序列DTW中心[J].計(jì)算機(jī)科學(xué),2015,42(12)：278-282.

[4]李承耕.一類決策樹不確定型庫存問題的動(dòng)態(tài)規(guī)劃求解[J].長江大學(xué)學(xué)報(bào)(自然科學(xué)版),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-），女，工程師，現(xiàn)主要從事天然氣開采與采氣工藝技術(shù)研究工作。

2017-07-11