图8－17
先进行倒数变换，令
则
具体实现是：先在D2
单元
初级会计实务单元训练题天津单元检测卷六年级下册数学单元教学设计框架单元教学设计的基本步骤主题单元教学设计
格中输入
公式
小学单位换算公式大全免费下载公式下载行测公式大全下载excel公式下载逻辑回归公式下载
：=1/C2，然后拖拽填充柄将该公式填充到D11 单元格即可。
利用回归工具建立上述回归模型，得如图8－18所示的结果。
图8－18
分析图8－18中的计算结果，可得如下回归模型：
预测。将1999年该商场商品零售额36.33亿元代入模型中，得1999年该商场商品流通费用率为：
故1999年该商场商品流通费用总额预测值为1.3606万元（36.33×3.74%）。
与线性回归分析类似，非线性回归分析也可以使用函数建立模型。对于非线性回归问题，Excel 2000提供的函数有LOGEST、GROWTH等。有关这些函数的具体使用方法，可通过Excel 2000的函数向导学习掌握。
通过本章内容的学习，应能够针对实际问题，选择有效的预测方法，建立模型，进行预测分析。若分析影响事物的主要因素比较困难或相关变量资料难以得到，则可以采用时间序列预测法（如移动平均法和指数平滑法）。若需要从事物变化的因果关系出发，寻找它与其他因素之间的内在联系，则可以采用回归分析预测法。对于移动平均法、指数平滑法和回归分析预测法，应能熟练地应用Excel 2000的分析工具建立相应模型、进行预测推算。尤其是对于所求出的回归模型，必须通过经济意义检验、统计检验和计量经济学检验。只有当所有检验都通过时，所做的回归模型才成立，才能用于预测。
第九章 规划分析
[本章提要]本章主要通过生产管理和经营决策中的最优配置问题，介绍Excel 2000的规划求解工具的应用。着重说明了规划求解工具的适应范围，求解步骤，结果分析以及限制条件的修改。
在生产管理和经营决策过程中，经常会遇到一些规划问题。例如生产的组织安排，产品的运输调度，作物的合理布局以及原料的恰当搭配等问题，其共同点就是如何合理地利用有限的人力、物力、财力等资源，得到最佳的经济效果，即达到产量最高、利润最大、成本最小、资源消耗最少等目标。这些问题中通常要涉及到众多的关联因素，复杂的数量关系，只凭经验进行简单估算显然是不行的。而线性规划、非线性规划和动态规划等方法正是研究和求解该类问题的有效数学方法。但是这些方法的求解大多十分繁琐复杂，常令人望而却步。而利用Excel 2000的规划求解工具，可以方便快捷地帮助我们得到各种规划问题的最佳解。
9.1 规划模型
规划问题可以涉及到众多的生产或经营领域的常见问题。例如生产的组织安排问题：如果要生产若干种不同的产品，每种产品需要在不同的设备上加工，每种产品在不同设备上需要加工的时间不同，每种产品所获得的利润也不同。要求在各种设备生产能力的限制下，如何安排生产可获得最大利润。又如运输的调度问题：如果某种产品的产地和销地有若干个，从各产地到各销地的运费不同。要求在满足各销地的需要量的情况下，如何调度可使得运费最小。再如作物的合理布局问题：不同的作物在不同性质的土壤上单位面积的产量是不同的。要求在现有种植面积和完成种植计划的前提下，如何因地制宜使得总产值最高。还有原料的恰当搭配问题：在食品、化工、冶金等企业，经常需要使用多种原料配置包含一定成份的产品，不同原料的价格不同，所含成份也不同。要求在满足产品成份要求的情况下，如何配方可使产品成本最小。
虽然规划问题种类繁多，但是其所要解决的问题可以分成两类：一类是确定了某个任务，研究如何使用最少的人力、物力和财力去完成它；另一类是已经有了一定数量的人力、物力和财力，研究如何使它们获得最大的收益。而从数学角度来看，规划问题都有下述共同特征：
决策变量：每个规划问题都有一组需要求解的未知数（ ），称作决策变量。这组决策变量的一组确定值就代表一个具体的规划
方案
气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载
。
约束条件：对于规划问题的决策变量通常都有一定的限制条件，称作约束条件。约束条件可以用与决策变量有关的不等式或等式来表示。
目标：每个问题都有一个明确的目标，如利润最大或成本最小。目标通常可用与决策变量有关的函数表示。
如果约束条件和目标函数都是线性函数，则称作线性规划；否则为非线性规划。如果要求决策变量的值为整数，则称为整数规划。规划求解问题的首要问题是将实际问题数学化、模型化。即将实际问题通过一组决策变量、一组用不等式或等式表示的约束条件以及目标函数来表示。这是求解规划问题的关键。然后即可应用Excel 2000的规划求解工具求解。
例如，某企业要指定下一年度的生产计划。按照
合同
劳动合同范本免费下载装修合同范本免费下载租赁合同免费下载房屋买卖合同下载劳务合同范本下载
规定，该企业第一季度到第四季度需分别向客户供货80、60、60和90台。该企业的季度最大生产能力为130台，生产费用为 （元），这里的 为季度生产的台数。该函数反映出生产规模越大，平均生产费用越低。若生产数量大于交货数量，多余部分可以下季度交货，但企业需支付每台16元的存储费用。所以生产规模过大，超过交货数量太多，将增加存储费用。那么如何安排各季度的产量，才能既满足供货合同，且使得企业的各种费用最小呢？
该问题是一个典型的非线性规划问题。下面首先将其模型化，即根据实际问题确定决策变量，设置约束条件和目标函数。
该问题的决策变量显然应为第一季、第二季、第三季和第四季的产量。设其分别为 。
该问题的约束条件为：
交货数量的约束：
生产能力的约束：
该问题的目标应是企业的费用最小。其中，费用包括生产费用 和可能发生的存储费用 之和，用公式表示则分别为：
则目标函数 为：
9.2 规划模型求解
建立好规划模型后，即可使用Excel 2000的规划求解工具求解了。由于在默认情况下，Excel 2000不加载规划求解工具。所以要应用规划求解工具，且Excel 2000的工具菜单中没有规划求解命令时，应先加载规划求解工具。其操作步骤如下：
单击工具菜单中的加载宏命令，这时将出现加载宏对话框。
在当前加载宏列表框中，选定规划求解的复选框，单击确定。
这以后的工具菜单中，将会出现规划求解命令。当需要进行规划求解操作时，直接执行该命令即可。如果不再需要进行规划求解操作时，可以按照类似的方法，通过加载宏命令，取消当前加载宏列表中规划求解的复选框。这样将会把规划求解命令从工具菜单中移去。
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 规划求解
显然，可选的方案很多。利用Excel 2000的规划求解工具可以迅速帮助找到最佳方案。其具体操作步骤如下：
单击工具菜单中的规划求解命令，这时将出现规划求解参数对话框，如图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
单击求解按钮。Excel 2000即开始进行计算，最后出现规划求解结果对话框，如图9－7所示。
图9－7
根据需要选择是保存规划求解结果还是恢复为原值；是否保存方案，是否生成运算结果报告、敏感度分析报告和限制范围报告。这里选择保存规划求解结果，并生成运算结果报告、敏感度分析报告和限制范围报告。最后的计算结果如图9－8所示。
图9－8
从计算结果可以看出，最佳生产方案是第一季到第四季分别生产130、10、60和90。其生产费用和存储费用分别为25296元和800元，总费用为26096元。该方案较原方案节省520元。
9.3 分析求解结果
通过查看规划求解工具生成的各种报告，可以进一步分析规划求解结果，并根据需要修改或重新设置规划求解参数。当规划求解失败时，还可以适当调整规划求解选项。
9.3.1 显示分析报告
Excel 2000的规划求解工具可以根据需要生成多个报告。图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所示。
根据需要重新设置最长运算时间、迭代次数、精度和允许误差等选项。然后单击确定，再重新求解。
通过本章内容的学习，应能够根据实际问题建立规划模型，再根据模型的各种参数正确建立工作表，应能熟练地应用Excel 2000的规划求解命令对规划模型求解，并能分析生成的各种运算结果报告。
图9－12
第十章 股市行情分析
[本章提要]本章主要通过股市行情分析问题，综合介绍Excel 2000的排序、筛选、图表、数据透视表、窗体以及宏等工具在研判股市行情和股票收益核算中的应用。也是对全书内容的复习。
股票证券市场是一个充满活力，同时又存在相当风险的资本市场。随着社会主义市场经济的不断发展，股票证券市场也日益规范。越来越多投资者愿意承担一定的风险投资股市，以获得更高的收益。与此同时，如何运用经验和智慧来降低和规避风险就成为投资者所特别关注的问题。其中采用各种技术分析方法，研判股票价格的变动规律和股票价格的逻辑变动方向，可以为股票投资决策提供重要的参考依据。而Excel 2000则是应用多种技术分析方法以及核算股市收益的有力工具。
10.1 排序与筛选
排序和筛选操作是一般数据库管理软件都具备的功能，Excel 2000的排序和筛选操作更加方便和直观。排序和筛选操作通过按指定的
标准
excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载
对数据进行组织，使得数据管理更加高效。
10.1.1 排序
在浏览股市行情时，有时可能要查找某个只知其名但不知其编码的股票的行情，这时如果股票如果是按股票名称排序的，就能比较快地找到。更多的时候还希望了解哪些股票涨幅或是跌幅最大，哪些股票成交量最大。这些要求都可以利用Excel 2000的排序命令方便地实现。以下通过图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 自动筛选
股票买卖操作前，通常需要按照一定的条件从几百个股票中找出感兴趣或是有潜力的股票。Excel 2000的自动筛选功能使得挑选股票的操作变的非常简单。
1. 排行榜
股市行情中经常需要显示成交量前5名、涨幅前5名以及跌幅前5名的股票。利用Excel 2000的自动筛选操作其基本步骤如下：
选定数据清单中的某个单元格为当前单元格。
单击数据菜单筛选命令中的自动筛选子命令。
该命令是一个选项开关，默认为关闭状态，当处于打开状态时，数据清单每列的标志旁边都会出现一个下拉箭头。如图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线图有多种形式，Excel 2000中提供了4种形式。分别是“盘高-盘低-收盘图”、“成交量-盘高-盘低-收盘图”、“开盘-盘高-盘低-收盘图”和“成交量-开盘-盘高-盘低-收盘图”。以下以第4种形式的K线图为例说明创建K线图的操作步骤：
在工作表中准备好股票的有关行情数据。注意，数据必须完整而且排列顺序应与图形要求的顺序一致，即按成交量、开盘、盘高、盘低和收盘的顺序排列。如图10－16所示。
图10－16
选中要分析的数据所在的单元格区域。这里选定B1:U6单元格区域。
单击常用工具栏上的图表向导工具按钮。
在弹出的图表向导-步骤4之1-图表类型对话框的图表类型中选股价图，在子图表类型中选成交量-开盘-盘高-盘低-收盘图。
按照图表向导的提示，一步步完成K线图的制作。完成的K线图如图10－17所示。
图10－17
从该K线图可以看出，由于成交量的数据与股票价格的数据差距较大，所以有两个纵坐标轴，分别标识成交量和股价。由于股价的范围大致在2～3元范围内，为了更清晰地反映股价的变动情况，将右侧的纵坐标轴的刻度作一些调整。同时将图表的颜色也略作调整。修饰后的K线图如图10－18所示。
该K线图以柱形图表示成交量的大小。用带上、下影线的矩形表示一天的股价变动情况。对于阳线（图上白色的矩形），矩形的底部表示开盘价，顶部表示收盘价，即矩形的长度表示了该日股价的上涨幅度。而对于阴线（图上黑色的矩形），矩形的顶部表示开盘价，底部表示收盘价，即矩形的长度表示了该日股价的下跌幅度。而上影线和下影线则分别表示最高价和最低价。所以K线图表示了较为全面的股价变动信息。
图10－18
K线图还清楚地反映了多空双方的强弱程度。例如较长的阳线反映了多方的力量较强，而没有上影线的阳线（收盘价等于最高价），如图中9月14日，9月29日，属于超强的涨势，通常表示未来仍然有上涨的空间。而上影线较长的阳线则反映了涨势较虚，如图中9月18日。类似的较长的阴线反映了空方的力量较强，而没有下影线的阴线（收盘价等于最低价）则属于超强的跌势，通常表示未来仍然有下跌的空间。而下影线较长的阴线则反映了跌势较虚。还有十字K线，如图中9月5日，9月15日，表示多空上方势均力敌，通常可通过上影线和下影线的长度判断多空双方的强弱。在高价圈或低价圈出现十字K线时，通常意味着反转变盘的迹象。
只是根据单日K线图研判股市走势未免过于简陋。由于主力、大户可以对单日或短期走势实施有力的控制，所以，应在单日K线图的基础上，通过2日或是多日的K线图的组合，再配合成交量的大小全面分析，这样才能够更全面、更准确地研判股市的未来走势和多空双方的强弱，特别是避免受到主力、大户作价的干扰影响。
10.2.2 移动平均线
移动平均是一种统计技术，移动平均线是将某段时间内股票价格的平均值画在坐标图上所形成的曲线。它受短期股票价格上升或下跌的影响较小，稳定性高，因而可以较为准确地研判股市的未来走势。根据时间长短，移动平均线可分为短期移动平均线、中期移动平均线和长期移动平均线。一般而言，10日以下的称为短期移动平均线，10日～20日的称为中期移动平均线，20日以上的称为长期移动平均线。短期移动平均线通常对股价的波动更为敏感，因此也称作快速移动平均线。相应地长期移动平均线则称为慢速移动平均线。
要绘移动平均线，首先需要计算移动平均数。移动平均数常见的有以下多种：
算术移动平均数：即一般所说的平均数，计算方法是将一组数相加，再除以数的个数。其计算公式如下：
加权移动平均数：算术移动平均数的计算将每个数对未来的影响同等看待，这是不太合理的。一般来说越近的数对未来的影响应该越大。所以加权移动平均数对影响力较大的近期数据赋予较高的加权，而对于影响力较小的远期数据赋予较低的加权。其计算公式如下：
指数平滑移动平均数：由于算术移动平均数和加权移动平均数的计算都需要计算大量的数据，较为繁杂费时，因此常使用指数平滑移动平均数递推计算。其计算公式如下：
其中的EMAt为待计算的指数平滑移动平均数，EMAt-1为前1日的指数平滑移动平均数，第1个指数平滑移动平均数可以使用算术移动平均数或是加权移动平均数。
下面使用算术移动平均数说明制作5日、10日和30日移动平均线的操作步骤。
在第5日股价收盘价的下面（F7单元格），输入计算5日算术平均数的公式：“=AVERAGE(B6:F6)”。
将该公式填充到G7:CT7单元格区域，计算出其他各天的5日算术移动平均数。
按照类似的方法在第10日股价收盘价的下面（K8单元格），输入计算10日算术平均数的公式，并填充到L8:CT8单元格区域，计算出各天的10日算术移动平均数。
在第30日股价收盘价的下面（AE9单元格），输入计算30日算术平均数的公式，并填充到AF9:CT9单元格区域，计算出各天的30日算术移动平均数。
选定要制作移动平均线的数据所在的单元格。为了观察方便，将第30日以前的数据列隐藏，然后选定1、7、8和9行。如图10－19所示。
图10－19
单击常用工具栏中的图表向导按钮，在弹出的图表向导-步骤4之1-图表类型对话框的图表类型和子图表类型中均选折线图。
按照图表向导的提示，一步步完成移动平均线图的制作。完成并经过一定修饰的移动平均线如图10－20所示。
图10－20
通过观察移动平均线的起落，可以明确地显示股票的真正基本趋势。对于股票的短期趋势，可以用10天～25天的移动平均线研判；对于较短的中期趋势，可以用26天～49天的移动平均线研判；对于中期趋势，可以用50天～100天的移动平均线研判。一般来说较少采用更长周期的移动平均线。另外，通过多重移动平均线还可以进一步研判较为合适的买卖股票时机，有关理论和方法请参考有关证券技术分析的书籍。
10.2.3 KD线
KD线是非常实用的研判股票市场行情的工具。它是建立在随机指标的基础上的图形分析方法。它融合了移动平均线的观点，同时也具有强弱指标（RSI）的基础，可以形成非常准确的买卖信号，是短线操作的利器。而且随机指标与移动平均线和强弱指标不同，它不仅考虑收盘价，还将最高价、最低价也考虑进去，因而更能体现股市的真正波动。
在计算K、D值之前，首先需要计算出未成熟随机值RSV（Row Stochastic Vlaue），然后再依此计算K、D值。计算n日的RSV、K、D的公式如下：
其中第1个K、值等于第1个未成熟随机值。
下面仍以上例说明制作9日KD线图的操作步骤。
在第9日股价收盘价的下面（J7单元格），输入计算9日RSV的公式：
“=(J6-MIN(B5:J5))/(MAX(B4:J4)-MIN(B5:J5))”。
将该公式填充到K7:CT7单元格区域，计算出其他各天的9日RSV。
在J8和J9单元格输入计算第1个K值和D值的公式：“=J7”。
在K8单元格输入计算第2个K值的计算公式：“=(2*J8+K7)/3”。并将该公式填充到L8:CT8单元格区域，计算出其他各天的K值。
类似地，在K9单元格输入计算第2个D值的计算公式：“=(2*J9+K8)/3”。并将该公式填充到L9:CT9单元格区域，计算出其他各天的D值。
选定要制作K、D线的数据所在的单元格。为了观察方便，将第9日以前的数据列隐藏，然后选定1、7和8行。如图10－21所示。
图10－21
单击常用工具栏中的图表向导按钮，在弹出的图表向导-步骤4之1-图表类型对话框的图表类型和子图表类型中均选折线图。
按照图表向导的提示，一步步完成K、D线图的制作。完成并经过一定修饰的KD线图如图10－22所示。
通过KD线图，可以为买卖股票时机提供很好的帮助。当K值从小于D值变成大于D值时，即K线从下方突破D线，形成K线在上D线在下时，即为买进时机。如图10月5日，该交叉点称作黄金交叉点。而当D值从小于K值变成大于K值时，即K线从上方穿越D线，形成D线在上K线在下时，即为卖出时机。如图12月23日，该交叉点称作死亡交叉点。此外，在配和支撑线、K线和成交量等分析，可以更好地把握买卖时机。
图10－22
10.3 股票收益计算器
在买卖股票的操作中，如何计算某个股票的收益有时是比较麻烦的，除了买卖股票的价格外，还要计算印花税、手续费、委托费等，如果再加上送股、配股和派息等，则更为复杂。利用Excel 2000制作一个股票收益计算器，可以使得上述工作大大简化。
10.3.1 公式的建立
首先新建一个工作表，输入有关的数据。其中委托费、成交费等项各股票交易所不完全相同，可以根据实际数据输入。建立好的股票收益计算器如图10－23所示。
图10－23
该工作表中最关键的是C8单元格中的内容，其内容是计算股票收益的公式：
＝F7*(1-B3-B4)-F3*(1+B3+B4)-F5+F6-(SIGN(F3)+SIGN(F7))*(B5+B6+B7)
其中B3和B4分别是印花税和手续费，F7*(1-B3-B4)为卖出股票的收益（已扣除印花税和手续费）；F3*(1+B3+B4)为买入股票的支出（含印花税和手续费）；F5为配股的支出；F6为派息的收益；最后一项(SIGN(F3)+SIGN(F7))*(B5+B6+B7)为委托费、成交费和通信费，其中SIGN函数为符号函数，当F3或F7大于0时值为1，等于0时值为0。
利用该计算器，只要输入买入和卖出股票的价格、数量，以及送、配股和派息数据，即可立刻计算出相应的收益。
10.3.2 窗体的应用
上例的计算器虽已可以自动完成股票收益的计算，但是还存在一些不足。例如当用户输入股票价格时，如果忘记输入小数点，将会得到一个不着边际的计算结果。为此我们利用Excel 2000提供的滚动条、单选钮等多种窗体控件，对该计算器加以改造，防止其出现明显的错误，并使其操作更为方便。首先单击视图菜单中的工具栏命令，选定窗体工具栏。则工作表上将出现窗体工具栏，如图10－24所示。
图10－24
先为买入股票数量等单元格添加滚动条控件（位于图10—24中第2行的第3个控件）。具体操作步骤如下：
在数量单元格后面插入一列。
单击窗体工具栏的滚动条控件。
在E3单元格上拖拽出一个矩形。
右击刚刚建立的滚动条控件；在弹出的快捷菜单中单击设置控件格式命令。将出现对象格式对话框。
根据需要设置有关参数，这里设置当前值为1000，最小值为100，最大值为10000，步长为10，页步长为100。并指定单元格链接为D3（当改变控件值时，D3单元格的值相应改变）。如图10－25所示。
类似地为送股、配股和卖出数量建立滚动条控件。
为股票价格等单元格添加滚动条控件。
建立股票价格的滚动条较股票数量的滚动条要复杂一些。因为滚动条变化的步长只能是整数，而价格可能需要按0.01元的步长变化。为此需要借助其它的单元格作为中间单元。例如指定G3单元格的滚动条控件与I3单元格链接，其值的变化范围为100～10000，步长为1，而在F3单元格中输入公式“＝I3/100”，即可实现当滚动条控件变化一个单位时，股票价格单元格F3能按0.01元的步长变化。类似地为配股价、派息和卖出价添加滚动条控件。
图10－25
因为股市上还有多种基金可以买卖，而基金买卖时是不上印花税的，为此再为计算器添加两个单选钮控件，使其计算股票时，印花税为0.50%，而计算基金时，印花税为0。具体操作步骤如下：
单击窗体工具栏中的单选钮控件（位于图10—24中第1行的第6个控件）。；
在工作表中的适当位置拖拽出一个矩形；
将建立的单选钮控件的名称改为“股票”；
右击该单选钮控件，然后在弹出的快捷菜单中单击设置控件格式命令；
在弹出的对象格式对话框中设置单元格链接等有关参数，这里设置其与I5单元格链接。
按照类似的方法在股票单选钮旁边在建立一个基金单选钮，并使其也与I5单元格链接。
这样当单击股票时，I5单元格的值为1，而单击基金时，I5单元格的值为2。然后还需要将单选钮的结果，即I5的结果与印花税单元格相连。为此，在J5和J6单元格分别输入0.005和0，而在印花税单元格B3输入公式“=INDEX(J5:J6, I5)”。这样，当选定股票时，I5单元格的值为1，相应的B3单元格的值将取J5:J6单元格区域的第一个值，即为0.005；而当选定基金时，I5单元格的值为2，相应的B3单元格的值则取J5:J6单元格区域的第二个值0。
各个控件添加完毕后，再适当地重新组织各单元格区域，并进行一些修饰，完成后的计算器如图10－26所示。
图10－26
该计算器比最初的要好用的得多。例如单击价格滚动条两端的滚动箭头，价格数据将会按0.01元的步长增加或减少；而单击价格滚动条（滚动块两侧），价格数据将会按0.10元的步长增加或减少；当需要快速增加或减少价格数据时，还可以直接拖拽滚动块。当计算的是基金时，只要单击基金单选钮，即可自动按基金的计算公式完成计算。
10.3.3 工作表的保护
这时的计算器虽已可以方便地使用了，但是还有一点美中不足，就是如果使用者直接在某个公式单元格中键入数据，将会使得精心设计的控件失灵。更有甚者，如果使用者在总收益单元格中键入数据，将使整个计算器失效。为了防止以上问题的发生，还需要为该计算器加上必要的保护。其具体操作步骤如下：
选定不需保护的单元格，例如买入、卖出、送配股的数量单元格，与买入、卖出、配股价格的滚动条链接的单元格；
单击格式菜单中的单元格命令，在保护选项卡下，清除锁定复选框；
单击工具菜单中保护命令的保护工作表子命令。将出现图10－27所示的保护工作表对话框。
图10－27
从中选择需要保护的项，如果需要，还可以输入密码，单击确定。
这样，工作表中除了刚才解除锁定的单元格都不能修改其内容，精心建立的公式和修饰的格式就都不会被破坏了。如果不按上述步骤操作，只是简单地执行保护工作表命令，则保护的工作表的所有单元格都不能改变，计算器就只能计算当前锁定的一种情况了。所以应将不是公式的且需要变化的单元格，都解除锁定，然后再对工作表设置保护。
到此为止，股票收益计算器全部制作完成。读者在此基础上还可以根据需要进行各种改进。例如送、配股可以只输入百分比，然后自动计算相应的股数。还可以设置累加器，将多次买卖的结果累计，最后计算总的收益等等。
10.4 收益核算
对于经常从事股票买卖的用户来说，由于频繁地买入或卖出各种股票，且买卖股票的数量、价格各不相同，要手工管理和分析核算还是比较繁琐的。而利用Excel 2000的数据透视表可以方便地进行股票收益的分析核算。
10.4.1 明细账的建立
首先应根据每次买卖股票的交割单建立一个明细账，它是将要建立数据透视表的基础数据。建立时应注意，买入、配股等的金额数据和卖出的数量数据应按负数输入。为了分析方便，将存入金额设置为股票代码“000000”。图10－28即为一个明细账的示例。
图10－28
10.4.2 汇总账的建立
要核算股票收益，需要根据每种股票买入、卖出、送股、配股、派息等各种情况分类汇总，这可以利用数据透视表方便地实现。具体操作步骤如下：
选定明细账工作表的数据清单中的任意单元格为当前单元格。
单击数据菜单中的数据透视表和图表报告命令，在弹出的数据透视表和数据透视图向导--步骤3之1对话框中选择Microsoft Excel数据清单或数据库。
在数据透视表和数据透视图向导--步骤3之2对话框中，Excel 2000通常会自动选定数据清单所在的整个区域，根据需要对其进行调整。
在数据透视表和数据透视图向导--步骤3之3对话框中，指定数据透视表显示位置选项为新建工作表。
这时将出现一空白的数据透视表和数据透视表工具栏。如图10－29所示。用户可根据需要进行汇总账的版式设计。
图10－29
因为是按股票进行分类，所以将股票字段拖至行字段处。
因为是根据摘要分别汇总，所以将摘要字段拖至列字段处。
因为需要分别统计股票的数量和金额，所以将数量和金额字段依次拖至数据项处。
初始时数据项的计算方法是计数，右击任意数据项，在弹出的快捷菜单中单击字段设置命令，在弹出的数据透视表字段对话框中将汇总方式改为求和。
建立好的数据透视表如图10－30所示。
图10－30
10.4.3 股票收益的核算
通过明细账建立的数据透视表实际上是一个按股票分类的汇总账，将同一种股票多次买卖以及送配情况的数据按照摘要分别汇总，可以清晰地反映各种股票以及总的收益情况。
每一行的总计项可以反映相应股票的汇总情况。例如“000568”股票，先后共买入400股，送240股，卖出640股，数量总计为0，而金额总计为2032。说明该股票现已全部卖出，盈利2032元。又如“000573”股票，先后共买入1000股，配299股，送400股，卖出为0，现股票数量总计为1699，而金额总计-11363元。
如果要核算某种股票的成本，可以在其旁边的单元格中输入计算公式。例如，要计算“000573”股票的平均成本，可以输入公式：“=J10/J9”，即可得到该股票现在的平均成本为6.69元。如果要考虑手续费等开销，可以按公式“=J10/J9*1.0085”计算。可得“6.74”，即只要以高于6.75元的价格卖出即可盈利。
通过最后两行的求和项，还可以直观地了解现在手中所持的股票数和账户中所余的资金总额。
当明细账工作表增加了新的数据，需要重建汇总账时，虽然不用重新建立数据透视表，但是也不能简单地使用数据透视表工具栏上的数据更新命令。应该用鼠标右击数据透视表的任一单元格，在弹出的快捷菜单中单击向导命令，然后在弹出的数据透视表和数据透视图向导--步骤3之3对话框中单击上一步。在数据透视表和数据透视图向导--步骤3之3对话框中重新指定数据区域，使其包含新增的数据区域，最后单击完成即可。
10.5 日常工作自动化
在日常工作中，有些操作，甚至可能是一些较为复杂的操作需要经常进行，为了有效地提高工作效率，减少差错，可以利用Excel 2000提供的宏使得上述操作自动完成。宏是Excel的重要组成部分。学好用好宏，可以更方便地操作Excel，更好地控制Excel，进一步深入发掘Excel的强大功能，全面提高应用Excel的水平。
10.5.1 宏的基本概念
所谓宏实际上是一种计算机程序语言。早期的宏是模仿用户界面的，实质上就是一些按键的组合，再加上一些类似于Basic语言中的INPUT、IF…THEN…ELSE等语句的宏命令。例如，宏命令“RNC”相当于用户键入R(区域“Range”)、N(名称“Name”)、C(创建“Create”)，执行的是创建区域名称的操作。这样的宏不仅难以阅读，而且也与图形用户界面不适应。以后逐渐开始使用独立于用户界面的宏语言。例如在Excel 4.0版中至少有三种不同的方法复制一个区域：使用[Ctrl]+[C]复合键；使用工具栏中的复制工具按钮；使用编辑菜单中的复制命令。所有这些用户操作序列都被翻译成单一的宏函数，=COPY( )。这样的宏的主要缺点是Excel宏只能用于Excel，而无法适应其它应用程序。从Excel 5.0开始使用VBA（Visual Basic for Application）作为宏语言。Excel 2000使用的是与Visual Basic 6.0兼容的面向整个Office 2000各应用程序的VBA。Visual Basic是Windows环境下开发应用程序的一种通用程序设计语言，功能强大，直观易用。而VBA是在Visual Basic程序设计语言的基础上，增加了对相应软件不同对象的控制功能。例如关于Excel工作簿、工作表、区域、数据透视表等对象的属性、事件和方法。
在Excel 2000中，宏都是由一个个过程构成。具体分为三类：Function过程、Sub过程和Property过程，也称作函数宏、命令宏和属性宏。其中Function过程用于创建自定义函数，Property过程主要用来创建和操作自定义属性，对这两种过程本章不进一步介绍。以下主要通过股票行情分析中的应用，介绍创建和应用命令宏的方法。
10.5.2 录制宏
所谓命令宏是指能独立完成一些特定操作的一段VBA程序。例如要创建一个命令宏，将单元格区域A2:E2的格式设置成货币样式，并清除工作表中的网格线，则相应的命令宏如下所示：
Sub Example( )
Range("A2:E2").Select
Selection.Style = "Currency"
ActiveWindow.DisplayGridlines = False
End Sub
该命令宏的第1句使用Range对象的Select方法，实际上是执行选定A1:F1单元格区域的操作。第2句修改Selection的Style属性，实际上是执行将选定对象的样式设置为货币样式的操作。最后一句修改ActiveWindow对象的DisplayGridlines属性，将其设置为False，实际上是取消当前活动窗口的表格线。
从上例可以看出用VBA创建命令宏的大致特点。显然要创建操作较为复杂的命令宏，需要熟悉Excel的各种对象，掌握VBA提供的各种语句、函数、方法和属性等内容，还需要具备一定的程序设计的能力。这对于一般用户，特别是对于非计算机专业的用户，是较为困难的。即使对于掌握了VBA的用户，逐字逐句地编写VBA，也是相当辛苦的工作。为此，Excel提供了记录宏的功能，可以录制用户执行的操作，自动生成有关的命令宏。例如，股票行情数据清单如本章开始处的图10－1所示。要创建有关建立股票排行榜的命令宏，其操作步骤如下：
首先单击视图菜单中的工具栏命令中的Visual Basic。Visual Basic工具栏出现在屏幕上。如图10－31所示。
图10－31
单击Visual Basic工具栏上的录制宏按钮 ，出现录制新宏对话框。
在宏名框中键入要录制的宏的名字，并根据需要输入说明的内容。本例在宏名框中输入“涨幅”替换默认的宏名；在说明框中键入“筛选涨幅为前5名的股票”替换默认的说明描述。如图10－32所示。单击确定。
图10－32
这时状态栏出现录制字样，并出现停止录制工具栏。此后所进行的操作，Excel 2000将自动记录下来，并将其转换成相应的命令宏。
执行一遍筛选涨幅前5名的操作。注意，为了保证宏无论在什么情况下都能正确的执行，操作的第1步应先选定股票数据清单所在的工作表，以及该工作表中股票数据所在的任意单元格。
单击停止录制按钮 。
这样就完成了录制宏的操作。要查看录制的宏的内容可单击Visual Basic工具栏上的Visual Basic编辑器按钮 。此时出现Visual Basic编辑器窗口。如图10－33所示。
图10－33
在Visual Basic编辑器中可以查看、编辑以及调试VBA宏。在其中的代码窗口中，可以看到刚才录制的操作所对应的宏语句。使用宏记录器录制的宏通常都是机械的，录制完后可以根据需要修改它们，使其更通用、更简洁。为了增加宏的可读性，还可以在宏语句后面添加有关的说明或
浙公网安备 33021202002483