Excel Solver的用法
电脑相关 2009-06-26, 22:13
Solver是Excel一个功能非常强大的插件(Add-Ins)，可用于工程上、经济学及其它一些学科中各种问题的优化求解，使用起来非常方便，Solver包括（但不限于）以下一些功能：
include of 用法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 Y
0 0
0.54 183
0.85 225
1.50 286
2.46 380
3.56 470
5.00 544
求L和K的值。
在Excel中随便假设一组L和K的值，比如都假设为1，以这组假设的值，求出一组Y’，然后再求出一组(X-Y)2的值，再将求出的这组(X-Y)2的值用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. Wi t h addi t ional 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 wi t h 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 i t is activated, you can find a new menu i t em 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:
Enter several pairs of x,y values into Columns A and B, and enter formulas  to Columns C, D, and E like the following.
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 automaticall y. 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 soluti on 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 th ing to a
chieve 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 i t 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]
∙

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。