EXCEL应用教材6.docx

EXCEL应用教材6.docx

《EXCEL应用教材6.docx》由会员分享，可在线阅读，更多相关《EXCEL应用教材6.docx（38页珍藏版）》请在冰豆网上搜索。

EXCEL应用教材6

图8－17

   先进行倒数变换，令

  则

  具体实现是：

先在D2单元格中输入公式：

=1/C2，然后拖拽填充柄将该公式填充到D11单元格即可。

  利用回归工具建立上述回归模型，得如图8－18所示的结果。

图8－18

   分析图8－18中的计算结果，可得如下回归模型：

  预测。

将1999年该商场商品零售额36.33亿元代入模型中，得1999年该商场商品流通费用率为：

  故1999年该商场商品流通费用总额预测值为1.3606万元（36.33×3.74%）。

  与线性回归分析类似，非线性回归分析也可以使用函数建立模型。

对于非线性回归问题，Excel2000提供的函数有LOGEST、GROWTH等。

有关这些函数的具体使用方法，可通过Excel2000的函数向导学习掌握。

  通过本章内容的学习，应能够针对实际问题，选择有效的预测方法，建立模型，进行预测分析。

若分析影响事物的主要因素比较困难或相关变量资料难以得到，则可以采用时间序列预测法（如移动平均法和指数平滑法）。

若需要从事物变化的因果关系出发，寻找它与其他因素之间的内在联系，则可以采用回归分析预测法。

对于移动平均法、指数平滑法和回归分析预测法，应能熟练地应用Excel2000的分析工具建立相应模型、进行预测推算。

尤其是对于所求出的回归模型，必须通过经济意义检验、统计检验和计量经济学检验。

只有当所有检验都通过时，所做的回归模型才成立，才能用于预测。

第九章 规划分析

  [本章提要]本章主要通过生产管理和经营决策中的最优配置问题，介绍Excel2000的规划求解工具的应用。

着重说明了规划求解工具的适应范围，求解步骤，结果分析以及限制条件的修改。

  在生产管理和经营决策过程中，经常会遇到一些规划问题。

例如生产的组织安排，产品的运输调度，作物的合理布局以及原料的恰当搭配等问题，其共同点就是如何合理地利用有限的人力、物力、财力等资源，得到最佳的经济效果，即达到产量最高、利润最大、成本最小、资源消耗最少等目标。

这些问题中通常要涉及到众多的关联因素，复杂的数量关系，只凭经验进行简单估算显然是不行的。

而线性规划、非线性规划和动态规划等方法正是研究和求解该类问题的有效数学方法。

但是这些方法的求解大多十分繁琐复杂，常令人望而却步。

而利用Excel2000的规划求解工具，可以方便快捷地帮助我们得到各种规划问题的最佳解。

9.1 规划模型

  规划问题可以涉及到众多的生产或经营领域的常见问题。

例如生产的组织安排问题：

如果要生产若干种不同的产品，每种产品需要在不同的设备上加工，每种产品在不同设备上需要加工的时间不同，每种产品所获得的利润也不同。

要求在各种设备生产能力的限制下，如何安排生产可获得最大利润。

又如运输的调度问题：

如果某种产品的产地和销地有若干个，从各产地到各销地的运费不同。

要求在满足各销地的需要量的情况下，如何调度可使得运费最小。

再如作物的合理布局问题：

不同的作物在不同性质的土壤上单位面积的产量是不同的。

要求在现有种植面积和完成种植计划的前提下，如何因地制宜使得总产值最高。

还有原料的恰当搭配问题：

在食品、化工、冶金等企业，经常需要使用多种原料配置包含一定成份的产品，不同原料的价格不同，所含成份也不同。

要求在满足产品成份要求的情况下，如何配方可使产品成本最小。

  虽然规划问题种类繁多，但是其所要解决的问题可以分成两类：

一类是确定了某个任务，研究如何使用最少的人力、物力和财力去完成它；另一类是已经有了一定数量的人力、物力和财力，研究如何使它们获得最大的收益。

而从数学角度来看，规划问题都有下述共同特征：

  决策变量：

每个规划问题都有一组需要求解的未知数（

），称作决策变量。

这组决策变量的一组确定值就代表一个具体的规划方案。

  约束条件：

对于规划问题的决策变量通常都有一定的限制条件，称作约束条件。

约束条件可以用与决策变量有关的不等式或等式来表示。

  目标：

每个问题都有一个明确的目标，如利润最大或成本最小。

目标通常可用与决策变量有关的函数表示。

  如果约束条件和目标函数都是线性函数，则称作线性规划；否则为非线性规划。

如果要求决策变量的值为整数，则称为整数规划。

规划求解问题的首要问题是将实际问题数学化、模型化。

即将实际问题通过一组决策变量、一组用不等式或等式表示的约束条件以及目标函数来表示。

这是求解规划问题的关键。

然后即可应用Excel2000的规划求解工具求解。

  例如，某企业要指定下一年度的生产计划。

按照合同规定，该企业第一季度到第四季度需分别向客户供货80、60、60和90台。

该企业的季度最大生产能力为130台，生产费用为

（元），这里的

为季度生产的台数。

该函数反映出生产规模越大，平均生产费用越低。

若生产数量大于交货数量，多余部分可以下季度交货，但企业需支付每台16元的存储费用。

所以生产规模过大，超过交货数量太多，将增加存储费用。

那么如何安排各季度的产量，才能既满足供货合同，且使得企业的各种费用最小呢？

  该问题是一个典型的非线性规划问题。

下面首先将其模型化，即根据实际问题确定决策变量，设置约束条件和目标函数。

  该问题的决策变量显然应为第一季、第二季、第三季和第四季的产量。

设其分别为

。

  该问题的约束条件为：

  交货数量的约束：

  生产能力的约束：

  该问题的目标应是企业的费用最小。

其中，费用包括生产费用

和可能发生的存储费用

之和，用公式表示则分别为：

  则目标函数

为：

9.2 规划模型求解

  建立好规划模型后，即可使用Excel2000的规划求解工具求解了。

由于在默认情况下，Excel2000不加载规划求解工具。

所以要应用规划求解工具，且Excel2000的工具菜单中没有规划求解命令时，应先加载规划求解工具。

其操作步骤如下：

  单击工具菜单中的加载宏命令，这时将出现加载宏对话框。

  在当前加载宏列表框中，选定规划求解的复选框，单击确定。

  这以后的工具菜单中，将会出现规划求解命令。

当需要进行规划求解操作时，直接执行该命令即可。

如果不再需要进行规划求解操作时，可以按照类似的方法，通过加载宏命令，取消当前加载宏列表中规划求解的复选框。

这样将会把规划求解命令从工具菜单中移去。

9.2.1 建立工作表

  规划求解的第一步，是将规划模型的有关数据输入到工作表中。

其具体步骤如下：

  在B5、B6、B7和B8单元格分别输入第一季到第四季的应交货数量。

  设在C5、C6、C7和C8单元格分别存放第一季到第四季的生产数量。

先设置其初始值与应交货数量相同。

可以直接将B5:

B8单元格区域的内容复制到C5:

C8单元格区域。

  在D5单元格建立计算第一季生产费用的公式：

“=80+98*C5-0.12*C5^2”，并将其填充到D6、D7和D8单元格区域。

计算出其它季度的生产费用。

  在E5单元格建立计算第一季存储数量的公式：

“=C5-B5”，即应等于第一季的生产数量减去第一季的应交货数量。

  在E6单元格建立计算第二季存储数量的公式：

“=E5+C6-B6”，即应等于第一季的存储数量加上第二季的生产数量减去第二季的应交货数量。

并将其填充到E7和E8单元格区域。

计算出第三季和第四季的存储数量。

  在F5单元格建立计算第一季存储费用的公式：

“=16*E5”，并将其填充到F6、F7和F8单元格区域。

计算出其它季度的存储费用。

  在G5:

G8单元格区域输入生产能力限制。

  在H5单元格建立计算第一季可交货数量的公式：

“=C5”，即应等于第一季的生产数量。

  在H6单元格建立计算第二季可交货数量的公式：

“=E5+C6”，即应等于第一季的存储数量加上第二季的生产数量。

并将其填充到H7和H8单元格区域。

计算出第三季和第四季的可交货数量。

  在B9:

F9单元格区域输入计算上述单元格的合计的公式。

  在B2单元格输入计算目标函数的公式：

“=D9+F9”。

即等于生产费用和存储费用的总和。

  建立好的工作表如图9－1所示。

图9－1

  从图9－1可以看出，按照交货数量安排生产计划时，目标函数，即总的费用为26136元。

下面考查一下其它的生产计划方案。

  先考虑均衡生产方式，即按80、70、70和70的数量安排生产计划，计算结果如图9－2所示。

图9－2

这时的生产费用和存储费用分别为26208元和480元，总费用为26688元。

即效益不如图9－1的方案。

  通过生产函数可知，生产规模越大，单位生产费用越低。

故考查按120、40、40和90的数量安排生产计划，计算结果如图9－3所示。

图9－3

  该方案的生产费用和存储费用分别为25656元和960元，总费用为26616元。

即效益介于图9－1和图9－2方案之间。

9.2.2 规划求解

  显然，可选的方案很多。

利用Excel2000的规划求解工具可以迅速帮助找到最佳方案。

其具体操作步骤如下：

  单击工具菜单中的规划求解命令，这时将出现规划求解参数对话框，如图9－4所示。

图9－4

  设置目标函数。

指定设置目标单元格为目标函数所在的单元格$B$2，并选定最小值单选钮。

  设置决策变量。

指定可变单元格为决策变量所在的单元格区域$C$5:

$C$8。

  设置约束条件。

单击添加按钮，这时将出现添加约束对话框。

如图9－5所示。

在单元格引用位置中指定决策变量第一季生产数量所在单元格的地址$C$5，选择“>=”关系运算符，在约束值中键入第一季应交货数量所在的单元格地址$B$5，单击添加按钮，即添加了一个约束条件：

“$C$5>=$B$5”。

第一季的生产数量应大于或等于第一季的应交货数量。

图9－5

  按照上述步骤逐个添加下表中的各约束条件。

添加完毕后，单击确定按钮。

这时的规划求解参数对话框如图9－6所示。

约束条件

说明

$C$5<=$G$5

第一季的生产数量应小于或等于第一季的生产能力。

$C$6<=$G$6

第二季的生产数量应小于或等于第二季的生产能力。

$C$7<=$G$7

第三季的生产数量应小于或等于第三季的生产能力。

$C$8<=$G$8

第四季的生产数量应小于或等于第四季的生产能力。

$H$6>=$B$6

第二季的可交货数量应大于或等于第二季的应交货数量。

$H$7>=$B$7

第三季的可交货数量应大于或等于第三季的应交货数量。

$H$8=$B$8

第四季的可交货数量应等于第四季的应交货数量。

图9－6

  单击求解按钮。

Excel2000即开始进行计算，最后出现规划求解结果对话框，如图9－7所示。

图9－7

  根据需要选择是保存规划求解结果还是恢复为原值；是否保存方案，是否生成运算结果报告、敏感度分析报告和限制范围报告。

这里选择保存规划求解结果，并生成运算结果报告、敏感度分析报告和限制范围报告。

最后的计算结果如图9－8所示。

图9－8

  从计算结果可以看出，最佳生产方案是第一季到第四季分别生产130、10、60和90。

其生产费用和存储费用分别为25296元和800元，总费用为26096元。

该方案较原方案节省520元。

9.3 分析求解结果

  通过查看规划求解工具生成的各种报告，可以进一步分析规划求解结果，并根据需要修改或重新设置规划求解参数。

当规划求解失败时，还可以适当调整规划求解选项。

9.3.1 显示分析报告

  Excel2000的规划求解工具可以根据需要生成多个报告。

图9－9其中的是运算结果报告。

  从报告中目标单元格和可变单元格的初值和终值可以清楚地看出最佳方案与原方案的差异。

通过约束单元格的状态可以进一步了解规划求解的细节。

在有关决策变量的约束条件中，约束“$C$5<=$G$5”，即第一季的生产数量小于或等于第一季的生产能力的约束条件已达到限制值。

这一点通过图9－10的敏感性报告可以更清楚地反映出来。

  从图中可以看出，决策变量$C$5，第一季生产数量的递减梯度为-12.80。

这说明第一季生产数量增加一个单位，将使得目标函数约降低13。

9.3.2 修改规划求解参数

  当规划模型有所变动时，可以方便地修改有关参数后，再重新计算即可。

  例如从上面的结果可以看出，如果扩大企业的生产能力，有可能进一步降低生产费用。

假设经过采取有关措施，企业的每季度生产能力由原来的130台增加到150台。

这时只需简单地将G5:

G8单元格的内容改为150，然后单击工具菜单中的规划求解命令，在弹出的规划求解参数对话框中直接单击求解命令即可。

计算结果如图9－11所示。

图9－9

图9－10

图9－11

  从图中可以看到，目标函数的值进一步降低到25944元。

  有时还根据需要根据模型的变化修改约束条件。

例如上例，严格地说约束条件还应该加上

。

否则可能会出现150、-10、60和90这样不合逻辑的解。

添加上述约束条件的操作步骤如下：

  单击工具菜单中的规划求解命令。

  在弹出的规划求解参数对话框中单击添加命令。

  在弹出的添加约束对话框中的单元格引用位置中指定C4:

C8单元格区域，在运算符列表框中选“>=”，在约束值框中输入“0”。

单击确定即可完成添加约束条件的操作。

  如果对规划模型的参数修改内容较多，或是需要计算另一个规划模型时，可以在规划求解参数对话框中直接单击全部重设命令。

然后重新设置规划求解的目标、可变单元格和约束条件。

9.3.3 修改规划求解选项

  如果规划模型设置的约束条件矛盾，或是在限制条件下无可行解，系统将会给出规划求解失败的信息。

规划求解失败也有可能是当前设置的最大求解时间太短，最大求解次数太少或是精度过高等原因引起。

对此可以修改规划求解选项。

其操作步骤是：

  单击工具菜单中的规划求解命令。

  在弹出的规划求解参数对话框中单击选项按钮。

这时将弹出规划求解选项对话框。

如图9－12所示。

  根据需要重新设置最长运算时间、迭代次数、精度和允许误差等选项。

然后单击确定，再重新求解。

  通过本章内容的学习，应能够根据实际问题建立规划模型，再根据模型的各种参数正确建立工作表，应能熟练地应用Excel2000的规划求解命令对规划模型求解，并能分析生成的各种运算结果报告。

图9－12

第十章 股市行情分析

  [本章提要]本章主要通过股市行情分析问题，综合介绍Excel2000的排序、筛选、图表、数据透视表、窗体以及宏等工具在研判股市行情和股票收益核算中的应用。

也是对全书内容的复习。

  股票证券市场是一个充满活力，同时又存在相当风险的资本市场。

随着社会主义市场经济的不断发展，股票证券市场也日益规范。

越来越多投资者愿意承担一定的风险投资股市，以获得更高的收益。

与此同时，如何运用经验和智慧来降低和规避风险就成为投资者所特别关注的问题。

其中采用各种技术分析方法，研判股票价格的变动规律和股票价格的逻辑变动方向，可以为股票投资决策提供重要的参考依据。

而Excel2000则是应用多种技术分析方法以及核算股市收益的有力工具。

10.1 排序与筛选

  排序和筛选操作是一般数据库管理软件都具备的功能，Excel2000的排序和筛选操作更加方便和直观。

排序和筛选操作通过按指定的标准对数据进行组织，使得数据管理更加高效。

10.1.1 排序

  在浏览股市行情时，有时可能要查找某个只知其名但不知其编码的股票的行情，这时如果股票如果是按股票名称排序的，就能比较快地找到。

更多的时候还希望了解哪些股票涨幅或是跌幅最大，哪些股票成交量最大。

这些要求都可以利用Excel2000的排序命令方便地实现。

以下通过图10－1所示的上证股票某天的股票行情数据清单说明排序的操作。

  现假设需要按照涨幅对股票数据排序，其操作步骤如下：

  选定排序字段的标志所在的单元格，这里选定H1单元格。

注意，不要选定H列标，否则将只对H列排序。

  单击常用工具栏中的降序工具按钮。

因为是按涨幅排序，即从大到小排序，所以应选择降序排序。

如果是按照跌幅排序，则应选择按升序排序。

  排序后工作表如图10－2所示。

图10－1

图10－2

  排序操作虽然简单，但不同满足复杂的排序要求。

例如对于人事数据清单，可能要求按单位排序，同一单位的按性别排序，同一单位且同一性别的按工资排序等。

这时排序工具按钮就无法实现了。

当按单位排完序后，再按性别排序时将打乱原来按单位排好的顺序。

这时需要使用数据菜单中的排序命令实现。

例如上例，如果要求涨幅相同的股票按成交量排序，则具体操作步骤如下。

  选定数据清单中任意单元格为当前单元格。

  单击数据菜单中的排序命令。

这时将弹出排序对话框。

  指定主要关键字为跌涨幅，排序方式为递减；次要关键字为成交量，排序方式亦为递减。

这时的排序对话框如图10－3所示。

图10－3

  从排序对话框可以看出，每次最多可以按3个字段实施复合排序。

如果要按3个以上字段实施复合排序，可以连续两次执行排序命令。

但是应注意，应将相对主要的关键字放在第2次排序过程中。

排序后部分结果如图10－4所示。

图10－4

10.1.2 自动筛选

  股票买卖操作前，通常需要按照一定的条件从几百个股票中找出感兴趣或是有潜力的股票。

Excel2000的自动筛选功能使得挑选股票的操作变的非常简单。

1.排行榜

  股市行情中经常需要显示成交量前5名、涨幅前5名以及跌幅前5名的股票。

利用Excel2000的自动筛选操作其基本步骤如下：

  选定数据清单中的某个单元格为当前单元格。

  单击数据菜单筛选命令中的自动筛选子命令。

  该命令是一个选项开关，默认为关闭状态，当处于打开状态时，数据清单每列的标志旁边都会出现一个下拉箭头。

如图10－5所示。

图10－5

  单击成交量字段的下拉箭头。

这时将显示该字段值的列表，以及全部、前10个和自定义等选项。

如图10－5所示。

图10－6

  可以从中选择一个值作为筛选数据清单的条件，这里选择前10个，将出现自动筛选前10个对话框。

如图10－7所示。

  根据需要可以设置是筛选最大还是最小，筛选前几个等。

这里设置筛选最大的前5个。

筛选结果如图10－8所示。

图10－7

图10－8

  这时工作表中满足条件的记录所在的行号和设置了筛选条件的筛选箭头都是蓝色的，以提醒操操作者注意当前显示的是筛选的结果以及对哪些字段进行了筛选。

  类似地，还可以对跌涨幅字段按最大和最小选项设置筛选条件，筛选出涨幅和跌幅前5名的股票。

注意，如果是对同一个工作表1个以上字段设置筛选条件，将筛选出同时满足所设置的所有筛选条件的记录。

因此要筛选出涨幅前5名时，应先将成交量字段的筛选条件设置为全部，即取消成交量字段的筛选条件。

2.自定义条件

  对于较为复杂的筛选条件，可以使用自动筛选中的自定义选项。

例如要筛选股票名称中以“股份”二字结尾的股票，其操作步骤如下：

  单击股票名称字段的筛选箭头，选择筛选列表中的自定义选项。

这时弹出自定义自动筛选方式对话框如图10－9所示。

图10－9

  在左侧的运算符下拉框中选止于运算符，在右侧的框中输入“股份”。

也可使用等于运算符，然后利用“?

”和“*”通配符实现该筛选条件。

  筛选结果如图10－10所示。

图10－10

  再比如要筛选成交量大于200,000，且涨幅在3％～5％之间的股票，其操作步骤如下：

  单击成交量字段的筛选箭头，选择筛选列表中的自定义选项。

  在自定义自动筛选方式对话框中左侧的运算符下拉框中选大于运算符，在右侧的框中输入“200000”。

  单击跌涨幅字段的筛选箭头，选择筛选列表中的自定义选项。

  在自定义自动筛选方式对话框中左侧的运算符下拉框中选大于或等于运算符，在右侧的框中输入“3”。

  在下一个运算符下拉框中选小于或等于运算符，在右侧的框中输入“5”。

  两个条件之间的关系选择与。

设置完的自定义自动筛选方式对话框如图10－11所示。

图10－11

  最后筛选结果如图10－12所示。

图10－12

10.1.3 高级筛选

  对于更为复杂的筛选，自动筛选就无能为力了。

例如。

自动筛选可以对多个字段同时设置筛选条件，但是各字段筛选条件之间的关系只能是“与”的关系。

又如，自定义选项虽然功能较强，但是最多只能应用两个运算符。

所以对于更为复杂的筛选必须使用高级筛选实现。

高级筛选操作的关键是条件区域的设置。

例如要筛选跌涨幅3％～5％之间且成交量大于300,000，或是跌涨幅-2％～-1%之间且成交量大于300,000的股票，则条件区域应选择工作表的某个区域（通常位于数据清单下方）按图10－13设置。

图10－13

  条件区域的第一行是要设置条件的字段名，可以有重复，可以是多个。

下面则是有关的条件。

每个条件由关系运算符和相应的参数构成。

同一行的条件相互间的关系是“与”的关系，不同行的条件之间的关系是“或”的关系。

所以该条件实际上是：

  （跌涨幅>=3与跌涨幅<=5与成交量>=300000）或

  （跌涨幅>=-2与跌涨幅<=-1与成交量>=30000）

  正是所需的筛选条件。

设置好条件区域以后，高级筛选的操作步骤如下：

  选定数据清单中的任意单元格为当前单元格。

  单击数据菜单筛选命令中的高级筛选命令。

  在弹出的高级筛选对话框中，根据需要选择显示筛选结果的方式，并分别指定数据清单和条件区域所在的单元格区域。

如图10－14所示。

  最后的筛选结果如图10－15所示。

图10－14

图10－15

10.2 图表分析

  各种图形是对股票市场进行技术分析的基本工具。

通过股票技术分析图形可以清楚地反映一段时期内股价的升跌、变化以及发展规律，大致判断未来的股市行情。

以下重点介绍K线图、移动平均线和KD线等常用技术分析图形。

10.2.1 K线图

  K线图是研判股市行情的基本图形，它细腻敏感，信息全面，能较好地反映多空双方的强弱状态和股价的波动，是股票投资技术分析的基本工具。

  K线图有多种形式，Exce