应用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