ExcelSolver的用法

2024年2月8日发(作者：)

Excel Solver的用法

电脑相关 2009-06-26, 22:13

Solver是Excel一个功能非常强大的插件(Add-Ins)，可用于工程上、经济学及其它一些学科中各种问题的优化求解，使用起来非常方便，Solver包括（但不限于）以下一些功能：

1、线性规划

2、非线性规划

3、线性回归，多元线性回归可以用Origin求解，也可以用Excel的linest函数或分析工具求解。

4、非线性回归

5、求函数在某区间内的极值

注意：Solver插件可以用于解决上面这些问题，并不是说上面这些问题Solver一定可以解决，而且有时候Solver给出的结果也不一定是最优的。

Solver安装方法：

Solver是Excel自带的插件，不需要单独下载安装。但Excel默认是不启用Solver的，启用方法：在"工具"菜单中点击“插件”，在Solver Add-In前面的方框中打勾，然后点OK，Excel会自动加载Solver，一旦启用成功，以后Sovler就会在"工具"菜单中显示。

Solver求解非线性回归问题的方法：

假设X和Y满足这样一个关系：Y=L(1-10-KX)，实验测得一组X和Y的值如下：

X

0

0.54

0.85

1.50

2.46

3.56

5.00

Y

0

183

225

286

380

470

544

求L和K的值。

在Excel中随便假设一组L和K的值，比如都假设为1，以这组假设的值，求出22一组Y’，然后再求出一组(X-Y)的值，再将求出的这组(X-Y)的值用Sum函数全部加起来(下面的图中，全部加起来结果在$G$22这个单元格中)。

然后点击“工具”菜单中的Solver，将Set Target Cell设为$G$22这个单元格，将By Changing Cells设为$F$8:$F9这两个单元格，即改变L和K的值，Equal

To选中Min这项，其他的选项不用理会，如下图：

然后点右上角的Solver，$F$8:$F9就会改变，改变之后的值即为优化的L和K值。

Use Excel Solver to solve non-linear

problem – solution of general

neural network

Excel is a powerful application. With additional Add-ons, it can be used to solve very complicated problem.

Recently, one of my friends ask me how to solve neural network problem in Excel. Sure it can do the job.

I will start with solving a very simple non-linear problem by using Excel Solver and extend the method to

solve neural network problem.

There are a number of ways to solve non-linear problem, such as NLIN in SAS, nmle in R, Solver in Excel,

Optimization package in MATLAB, and self-developed program adn so on. In Excel, it is Solver, an Excel

Add-on, that can help to solve the problem. Excel does not have Solver installed at default installation. If

the Excel on your machine does not have Solver add-on, you can use Tools->Add-Ins (Figure 1) to

activate this component. Once it is activated, you can find a new menu item called Solver shows up in

Tools menu. Click that, Solver dialog window will appear. You can find details about how to use Excel

Solver on the Internet.

Figure 1. Excel Add-ins dialog

Now we have the tool to work with. First of all, we can start with seeking solution of a very simple

non-linear problem, a system of polunomial equations. Suppose we have the following 3 order

polynomial equation:

y = a*x^3 + b * x^2 + c

Enter several pairs of x,y values into Columns A and B, and enter formulas to Columns C, D, and E like

the following.

x y predicted Diff Diff^2

1 3 =a_1*A2^3+b_1*A2^2+c_1 =c2-b2 =d2^2

2 38 =a_1*A1^3+b_1*A1^2+c_1 =c3-b3 =d3^2

23 1882 =a_1*A1^3+b_1*A1^2+c_1 =c4-b4 =d4^2

Enter string values to A6-A9, such ad “A_1″, “B_1″, “C_1″ snf “SSE”, and 1 to B6-B8, formula

(=sum(E2:E4)) to B9. You can download the sample Excel sheet polyeqsys. Now select A6:B9 as a range,

and click Insert -> Name -> Create to create named variables. Click “Ok” button when the “Create Name”

dialog shows up. Then you can select B9 and click Tools -> Solver to open the Solver dialog window. The

Set target cell box is filled with $B$9 automatically. This is what we want. Select “Min” in “Equal to” row.

Then click By changing cells box and select B6-B8. The Solver dialog window looks like Figure 2. Next step

is to click “Solve” button on the right top corner. After a few seconds, you will get solution in B6-B8. You

can notice the value in B9 become a small number.

Figure 2. Excel Solver dialog window

Now you learn how to use Excel Solver to solve simple non-linear problem. The strategy we use in the

simple problem can be applied to solve neural network problem directly. The most important thing to

achieve our goal is to design the neural network instead of training it. The following provides a simple

feedforward neural network to mimic the output of a sine function.

In Excel spreadsheet, we use sine function to generate a series of value pairs. The feedforward neural

network includes three layers: input, hidden, and output. Input layer only include input values of the sine

function. The hidden layer includes 5 neurons and each of the neurons includes a transigmoid

transforming function. The output layer includes one neuron which takes the outputs of the five neurons

in the hidden layer and transforms them through a linear function. Linear function is used as the pure

input functions in the hidden and output layers. Figure 3 shows the general layout of the feedforward

neural network.

Figure 3. The topology of a simple feedforward neural network model

All formulas should be entered into Excel spreadsheet just like what we do in the first example. Once we

complete this step, just setup the Excel Solver and run it to train the neural network. All parameters used

in the neural network can be obtained once the training process is completed. Details about how to

implement the neural network model in Excel and the trained network parameters can be found in the

Excel document – nnexample.

I hope you enjoying the reading and learning. Please post your comments if you have questions, new

ideas, and suggestions.

Do not forget to read the related posts:







[PDF]

Simple way to utilize Excel Solver

Use VBA script to control Solver

Solving nonlinear problem in Excel: Using downhill simplex method

