首页  编辑  

数据和图表的动态分析

Tags: /计算机文档/Office/   Date Created:

数据和图表的动态分析

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

img_1551.bmp (150.9KB)
img_16563.bmp (246.5KB)
img_2059.bmp (20.0KB)
img_22157.bmp (210.7KB)
img_22243.bmp (270.9KB)
img_22282.bmp (251.4KB)
img_24347.bmp (307.2KB)
img_28782.bmp (307.2KB)
img_3083.bmp (272.9KB)
img_31893.bmp (139.0KB)
img_32048.bmp (272.9KB)
img_4724.bmp (413.8KB)
img_5623.bmp (251.4KB)
img_7382.bmp (96.9KB)
img_9407.bmp (251.4KB)