EXCEL动态权重（滚动条及图形控件教学示例）的实现

    首先，我们来分析一下这个excel文件吧（图1、图2）。在这个文件里提供了两个资产组合的期望收益和风险计算，基础数据表格在B5：D7之间，当调整D9或D12的滚动条时，相关系数、资产权重会发生改变，投资组合的期望收益与风险以及最小方差组合相关信息随之变动，图形中的曲线及点会发生相应的移动。

  

图1 投资组合风险与期望收益的动态权重演示

 

图2投资组合风险与期望收益的动态权重演示（数据列表）

    这样一个可视化的演示内容包含了公式、控件及图形三方面的应用，其中公式应用在投资组合的期望收益与风险的计算当中，控件提供了对相关系数和权重的变动控制，图形完成了可视化任务，其中图形数据主要来自图2中的部分。

    下面我们来仿照这个示例做一下自己的xls文件。

    我们先新建一个excel表格文件。在相应位置构建三个表格，输入数据及计算公式（图3）。其中相关系数旁的单元格C9内写入公式“=(D9-100)*0.01”，这里单元格D9用于控制相关系数变动量，其可控变动量应该在0到200之间，这样C9就可以在-1到+1之间进行变化了。为了控制这一变化，我们需要一个滚动条控件。我们可以在“工具栏”中增加显示“自定义”部件，选择“窗体”部件（图4）。从这个部件中，我们会看到多种控件（图5）。需要说明的是，这些控件称为“表单控件”，有一些和它们长得类似的控件在“自定义”中的“控件工具箱”里，属于“ActiveX 控件”，使用方法和它们是不一样的。在Excel2007及更高版本中，这些控件在“开发工具”中选择（图6）。

 

图3 增加自定义部分中的窗体1

 

图4 增加自定义部分中的窗体2

 

图5 Excel2003版中选择滚动条控件

 

图6 Excel2010版中选择滚动条控件

 

图7 设置滚动条格式

    在单元格D9上绘制一个滚动条控件后，右键点击控件后选择“设置控件格式”（图7），则看到这样的界面，选择“控制”表单，对其中各项进行设置（图8），这些设置项，我们在一年级的VB课程中都介绍过，在此略过。修改后，先测试一下吧，我们会发现相关系数可以变动了。与此相同的方法，我们添加了控制权重的滚动条。设置内容如图9所示。在这里，我们要求权重的变动范围是0到1之间，以0.01为单位进行变动。

    接下来，我们要为绘图做准备了。我们在单元格B49：H152的区域内，编制了不同权重配比条件下的期望收益和风险计算表。其中，D和E这两列分别输入了组合标准差和期望的计算公式。G和H使用IF公式给出了当前资产组合的投资回报值和最小风险条件下的投资组合回报值，这两组公式保证每个条件下仅有一个单元格内显示数据，这就是后面图形显示时除了曲线外，还有两个点的原因。为了完成上述计算，单元格H50对最小风险投资组合中资产A的权重进行了计算。

 

图8 相关系数滚动条设置内容

 

图9 权重滚动条设置内容

 

图10 添加图形

    接下来，我们要做的工作是添加图形（图10）。这个图形中应该有三组数据，分别用于显示组合趋势线、当前投资组合和最小风险投资组合。选择图形中的散点图，单击下一步。分别添加三个系列的数据如图11所示。

 

 

图11a添加数据序列1（曲线）

 

图11b 添加数据序列1（当前组合）

 

图11c添加数据序列3（最小风险组合）

（此序列中F应为H，操作失误，请谅解）

 

图12 设置标题和坐标说明

 

图13 设置网格线

 

图14 设置图例为不显示

 

图15 插入图表到sheet1中

    此时不要着急单击“完成”，而是单击“下一步”，对图形的标题、坐标轴、网格线等进行设定，再将图表作为对象插入sheet1中（图12-15）。

 

图16 插入图形后的表格

    这是我们看到的图形（图16）非常难看，不仅图线密集、坐标太粗，而且还有一些在纵坐标为0上的点，更为可气的是我们调整权重或者相关系数时，坐标系会发生变化。为了能精细显示图形且坐标系固定，需要将横纵坐标做出设置。分别单击横纵坐标。右键点击后弹出菜单，选择“坐标轴格式”。在“刻度”选项卡（图17）中取消最小值等自动设定，并进行指定。对于描述期望收益的纵轴更可以将最小值设定为最低期望收益率，且让纵轴与横轴相交位置不在原点。修改刻度后，可以选择图案选项卡（图18），修改坐标轴为虚线形式。

 

 

图17a修改横坐标刻度设置

 

图17b 修改纵坐标设置

 

图18 修改坐标轴图案为虚线

 

 

图19 修改图表类型

    这时我们得到的图形变成了图19中的样子。这时，系列1显示的点过于密集，观看不便，对于有密集恐惧症的同学简直就是一种折磨。我们选择系列1，右键单击后选择“图表类型”，将之修改为平滑线图（图20a）。系列2和系列3的显示依然不够美观，存在孤立点到横坐标的连线。我们可以选择系列2或系列3，右键单击后选择“图表类型”，将之修改为散点图（图20b）。另外，我们还可以修改坐标系中的网格线为虚线（图21）。

 

图20a系列1改为平滑线图

 

 

图20b 系列2和系列3改为纯散点图

 

图21 修改网格线格式为虚线

 

 

图22 最终完成的可视化动态权重投资组合期望收益与风险xls文件

    这时我们再看最终生成的文件，已经可以实现和课上示例同样的功能和相似的外观了。在这个文件制作过程中，我们运用了公式、控件和图形来实现可视化和交互性操作。其中最为重要的是在掌握这些功能的基础上，具有分析问题，并将问题转化为xls操作的能力，这就需要大家具备一定的系统分析知识和技巧。这种技巧，我们在VB课程和管理信息系统课程中都有所涉及，希望大家平时看到一个软件时，思考一下其界面背后的功能实现，提高系统分析能力。