数据和图表的动态分析

新楼盘开盘了，房地产公司的销售人员们开始忙碌地接待客户。销售之一的小马的销售业绩总是比别人好，于是公司准备让小马介绍销售经验。原来，别的业务员在接待客户时，都是用各种书面材料给客户看，遇到客户咨询有关购房面积和贷款问题时，就会用计算器帮客户计算。这种方式效率低，而客户的需要可能是各种各样的，难以提供更多选择方案。而小马则不同，她除了给客户看各种书面材料外，还用自己的笔记本电脑帮助客户计算购房面积和贷款。更方便的是，她在Excel中帮助客户进行动态的数据和图表分析，根据客户的情况，快速提供出多种可能的购房和贷款方案，让客户有多种选择余地，因此受到客户的欢迎。
在Excel中进行复杂的数据分析时，数据之间的关系错综复杂，可通过函数和公式得到各种分析结果。在进行优化分析时，往往希望得到最佳的结果。这时，通常用单变量求解和规划求解等方法进行分析，这些是最好的方法，并不难学，但很多用户不太熟悉。如果希望分析结果能够直观表现并进行对比，也可以采用用户熟知的一些方法（如公式和函数、图表等），结合控件的使用，灵活而直观地进行动态分析。
小马就是用控件建立了购房贷款的数据和图表动态分析方法。
贷款买房时，比较关心的问题主要包括：购房面积、每平米单价、总价、贷款比例、贷款年限、首付款和每月还款额等，如图1所示。

图1 购房贷款试算表
其中很多数据是可以通过其他数据计算得到的。例如，总价=购房面积×每平米单价，贷款金额=总价×贷款比例，首付款=总价－贷款金额。每月还款额可用PMT函数计算得出，PMT函数依据贷款利率、还款期数（次数）和贷款金额自动计算出每月还款额，并用负数表示还款支出（例如：=PMT(5.4%/12,12*20,689000)，其中，月利率为年利率5.4%除以12，还款期数为12个月乘以20年，贷款总金额为689000元）。
假如购房者正在考虑购买多大的面积，选择何处的地段和价位，并且每月偿还能力有限，则他希望能够直观地在图1中看到购房面积和每平米单价的数据变化对其他数据的影响。如果每月最高还贷能力为5000元，则可供他选择的房屋面积和每平米单价会有很多种组合。
在图1中逐个填写试算数据，会耽误很多时间。所以，可以考虑用控件快速完成试算数据的填写。
从菜单选择【视图】|【工具栏】|【窗体】命令，显示“窗体”工具栏，如图2所示。

图2 “窗体”工具栏
在“窗体”工具栏单击“滚动条”控件，并在如图1所示的表格第1行“购房面积”的右侧画出水平滚动条，如图3所示。用同样的方法在“每平米单价”右侧画出另一个水平滚动条。

图3 添加滚动条控件
要让水平滚动条可以工作，必须对其进行设置。在第1个水平滚动条上单击右键，选择【设置控件格式】命令，显示对话框，选择“控制”选项卡，如图4所示。

图4 第1个滚动条的设置
现在希望单击或拖动滚动条时，能自动改变购房面积的数值大小，则可以定位到“单元格链接”中，并单击如图1所示的表格中的购房面积数据所在的单元格（其当前数值为“130”，位于C4单元格），这样，“单元格链接”中自动填入“$C$4”的绝对地址。此设置表示，当滚动条变化时，会将对应的数值填入C4单元格。
还可以指定滚动条变化时的数据范围，可按购买面积的范围填写“最小值”和“最大值”。在“最小值”中输入“50”，表示购买面积最小为50平方米，在“最大值”中输入“200”，表示购买面积最大为200平方米。
设置“步长”为1，表示单击滚动条左右两侧的箭头时，数值自动减少或增加1。设置“页步长”为10，表示在滚动条中间的滑块左侧或右侧单击时，数值自动减少或增加10。
以上设置完毕后，可用鼠标单击或拖动滑块的位置，查看购房面积数据的自动变化。
用同样的方法可设置第2个滚动条的控件格式，如图5所示。“单元格链接”为“$C$5”，对应每平米单价的数据所在的单元格。“最小值”和“最大值”分别为“1000”和“15000”，表示每平米单价的范围为1000～15000元。“步长”和“页步长”分别为“50”和“500”，表示滚动条变化的最小单位是50元，按页变化的单位是500元。

图5 第2个滚动条控件的设置
经过上面的设置，就可以方便地调节2个滚动条，得到不同的数据组合，并随时监控每月还款额是否超出支付能力。
如果想让数据的显示更直观，还可以绘制图表。用气泡图可形象地表现购房面积、每平米单价和每月还款额之间的关系。
在工具栏单击【图表向导】按钮，显示图表向导对话框，如图6所示。选择图表类型为“气泡图”中的“三维气泡图”。

图6 选择图表类型为三维气泡图
单击【下一步】按钮，设置图表的数据区域，如图7所示。在“数据区域”中按【Delete】键清除其中的内容。
单击“系列”选项卡，指定图表中所用的数据，如图8所示。单击【添加】按钮，添加数据系列。在气泡图中，将以每平米单价作为X轴，以每月还款额作为Y轴，以购房面积作为气泡。因此，可在“X值”中，单击如图1所示中的每平米单价的数据所在的单元格$C$5；在“Y值”中，单击如图1所示中的每月还款额的数据所在的单元格$C$12；在“大小”中，单击如图1所示中的购房面积的数据所在的单元格$C$4。

图7 设置数据区域

图8 指定图表中所用的数据
单击【下一步】按钮，选择“数据标志”选项卡，如图9所示。选中“气泡尺寸”，将在图中标注出气泡尺寸（购房面积）。

图9 设置数据标志
单击【下一步】按钮，设置图表位置，如图10所示。采用默认设置，并单击【完成】，结束图表向导。

图10 采用默认图表位置
当前绘制出的图表从格式和内容上都还不完善，如图11所示，需要进一步调整。图表中的文字字号都比较大，可以选中图表，设置更小的字号。

图11 采用自动设置的坐标轴时的图表
图表中并未显示出气泡数据，是因为当前坐标轴采用自动设置的缘故。在X轴上单击右键（注意由于Y轴数据都是负值，所以X轴目前在图表顶端），选择【坐标轴格式】命令，显示对话框，如图12所示。选择“刻度”选项卡，设置“最小值”为1000，“最大值”为15000，这是每平米单价的范围。

图12 设置坐标轴刻度
单击“数字”选项卡，如图13所示。设置坐标轴的数字格式为“数值”类型，“小数位数”为0，并取消“使用千分位分隔符”。这样设置是为了减小X轴刻度标签的文字长度，美化图表的显示。

图13 设置坐标轴的数字格式
单击“图案”选项卡，如图14所示。在“刻度线标签”中选择“图内”，使标签文字显示在X轴的上方区域。

图14 设置刻度线标签位置
至此，X轴的设置结束。可按同样方法设置Y轴。参照图12设置Y轴的“最小值”为-6000，“最大值”为0，这是每月还款额的范围（略超过还款能力-5000，是为了更好地看到气泡的位置）。
所有坐标轴设置完毕，将得到如图15所示的气泡图。
现在，可以拖动滚动条，直观地在表格和图表中进行动态分析了，气泡的位置将随滚动条而改变。
通过此例，可以了解控件在数据和图表的动态分析中的作用。控件能使数据的变化更容易控制，帮助用户简洁明了地进行直观的数据分析。

图15 利用滚动条控件控制数据和图表的动态变化
_xyz