数据和图表的动态分析

新楼盘开盘了，房地产公司的销售人员们开始忙碌地接待客户。销售之一的小马的销售业绩总是比别人好，于是公司准备让小马介绍销售经验。原来，别的业务员在接待客户时，都是用各种书面材料给客户看，遇到客户咨询有关购房面积和贷款问题时，就会用计算器帮客户计算。这种方式效率低，而客户的需要可能是各种各样的，难以提供更多选择方案。而小马则不同，她除了给客户看各种书面材料外，还用自己的笔记本电脑帮助客户计算购房面积和贷款。更方便的是，她在 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 利用滚动条控件控制数据和图表的动态变化