5.5.2.1 利用组合框选择的动态图表
【例5-22】 如图5-70所示为一个利用组合框选择显示指定分公司销售数据的图表示例。该图表的一个组合框,用于选择要显示的产品名称。只要选择了某个产品,就会自动绘制该产品的销售数据图表。其绘制过程如下。
Step 1:设计辅助绘图数据区域,如图5-71所示。其中单元格G2保存组合框的返回值(可以先输入一个产品数目的数字,如3),而单元格区域H1:H8保存单元格G2数字查询出来的产品的数据。
Step 2:在单元格H1中输入公式“=INDEX(B1:E1,,$G$2)”,向下复制到单元格H8,得到某个产品的销售数据,这个数据区域将由组合框来控制显示,并以此数据区域来绘制图表。
图5-70 利用组合框显示指定产品的销售图表
Step 3:由于组合框中的项目来源必须是工作表的某列数据,因此在区域I1:I5中输入各个产品的名称列表。
Step 4:选择单元格区域A1:A8和H1:H8,绘制普通柱形图,并根据需要对图表进行修饰和美化,如图5-72所示。
图5-71 辅助绘图数据区域
图5-72 绘制基本图表
Step 5:单击【开发工具】/【控件】/【插入】→【表单控件】/【组合框】,在图表上适当位置插入组合框控件,如图5-73所示。
Step 6:选择该组合框并右击→【设置控件格式】/【控制】→在【单元格链接】文本框中输入“$G$2”→单击【确定】。这样就建立了组合框与工作表单元格区域I2:I5的数据,而组合框的返回值被保存到单元格G2中,如图5-74所示。
Step 7:根据需要,调整图表大小和组合框的位置。这样就得到了需要的图表。只要在组合框中选择某个产品,则自动在图表上显示该产品的数据。
5.5.2.2 用动态名称法绘制动态图表
由于利用辅助绘图区域数据绘制图表,故若其区域内查询公式被损坏了,那么动态图表也就绘制不出来,因而其安全性较差。如果定义一个动态的名称来代表辅助绘图区域的数据,而名称是不占用工作表的任何单元格的,所以图表的安全性得到很大提高。
图5-73 在图表中插入表单控件
图5-74 设置对象格式
【例5-23】 仍用例5-22的数据,利用动态名称绘制动态图表的步骤如下。
Step 1:定义表5-6所示的两个名称。注意此处单元格G2保存在图表上的组合框的返回值。因此,当单元格G2数字变化后,名称“产品”代表的数据区域就会发生变化。其操作过程为:选择【公式】→【名称管理器】/【定义名称】→在【名称】栏里输入“地区”,在引用位置栏里输入“=′5.19组合框′!$A$2:$A$8”。同样的步骤,在【名称】栏里输入“产品”,在引用位置栏里输入“=OFFSET(′5.19组合框′!$A$2,,′5.19组合框′!$G$2,7,1)”。
表5-6 定义的名称
Step 2:单击工作表任意空白单元格→【插入】→【图表】,选择某个图表类型→用鼠标右击插入的空白图表→【选择数据】→【打开数据源】对话框,如图5-75所示。
Step 3:单击【图例项(系列)】/【添加】→【编辑数据系列】/【系列值】→输入公式“=Excel绘图.xls!产品”。这里的系列名称不是必需的,因此可不理会,如图5-76所示。这里假定绘图是在工作表“5.20组合框”中进行的。单击【确定】之后,返回【选择数据源】对话框。
图5-75 准备为图表添加系列
图5-76 为图表添加名称数据系列
Step 4:单击【水平(分类)轴标签】/【编辑】→【轴标签】/【轴标签区域】文本框中输入公式“=Excel绘图.xls!地区”→单击【确认】即可,如图5-77所示。
Step 5:在图表上插入一个组合框表单控件,并建立与工作表单元格G2的链接,格式化并美化图表即可,如图5-78所示。
图5-77 为图表添加分类轴标签
图5-78 以动态绘制的图表
5.5.2.3 使用单选按钮绘制动态图表
单选按钮一次只能选择一个,这样就可以利用单选按钮在图表上显示指定项目的数据。
【例5-24】 以图5-70所示数据为例,并且定义了表5-6的名称。下面要使用单选按钮来控制动态图表,其效果如图5-79所示。
Step 1:首先,以定义的名称绘制基本的图表。
Step 2:然后在图表上插入4个单选按钮。
Step 3:用鼠标右击任意单选按钮→【设置控件格式】/【控制】→在【单元格链接】中引用单元格G2。这样就对4个单选按钮建立了与单元格G2的链接。
Step 4:以插入单选按钮的先后顺序将它们的标题分别改为“产品A”“产品B”“产品C”“产品D”。其操作过程为:右击按钮→【设置控件格式】→在【可控文字】更改按钮标题→点击【确定】。这样,就完成了利用单选按钮控制图表显示的动态图表(图5-80)。
图5-79 使用单选按钮控制的图表
图5-80 建立与单选按钮与G2的链接
5.5.2.4 使用复选框绘制动态图表
使用复选框来控制多个项目的显示,要比前面介绍的方法更为简单。例如:当图表的几个系列数据相差很大,或者是不同的单位时,可以使用复选框对某几个项目进行对比分析。
由于复选框的返回值是True或False,各个复选框的返回值必须保存到不同的单元格,因此在图表上插入多个复选框时,需要对每个复选框进行控制属性设置。
利用复选框控制图表的显示,其基本原理是根据复选框的返回值True或False进行判断。如果选中某个复选框,其返回值就是True,就在辅助绘图区域内输入该项目的数据,或者定义的名称引用是该项目的具体数据,那么图表上显示该项目的数据;如果没有选中某个复选框,就在辅助绘图数据区域内输入错误值#N/A,或者定义的名称引用是错误值#N/A,就不在图表上显示该项目数据。因为在默认情况下,图表是不绘制错误值的,这样就得到了需要的动态图表。下面以实例来说明复选框控制图表显示的原理与具体方法。
【例5-25】 如图5-81所示是利用复选框来控制数据显示的动态图表。在这个图表中可以选择都显示,也可以只显示某个项目。当数据都显示时,当月销售额的柱形很小,看起来很不直观,就可以不显示累计销售额数据,此时查看当月销售额和环比增长率就很清楚,如图5-81所示。这个图表共有4个数据系列,“每月销售额”绘制成柱形图,“累计销售额”绘制成面积图,“环比增长率”绘制成折线,它们都是由辅助绘图区的数据绘制而成。还有一个系列为零值,以便观察环比增长率数据。(www.xing528.com)
图5-81 用控件控制要显示的内容
辅助绘图区域如图5-82所示,单元格B21、C21和D21分别保存3个用于选择是否显示“当月销售额”的复选框、“累计销售额”复选框和“环比增长率”复选框的返回值,True表示显示,False表示不显示。
Step 1:在单元格B23中输入公式“=IF(B$21,B2,NA())”,向右向下复制。
Step 2:以辅助绘图区域A22:E34绘制组合图表,注意把“当月销售额”绘制成柱形图,“累计销售额”绘制成面积图,这两个数据绘制在主坐标轴上;“环比增长率”和“零值”绘制成折线图,它们绘制在次坐标轴上。
Step 3:为了便于观察环比增长率,将系列“零值”设置为不显示,但要添加一条趋势线,并使趋势线扩展至整个图表。最后美化图表。
Step 4:在图表的适当位置插入3个复选框,删除其标题文字,把图例拖放到这3个复选框的右侧,使它们看起来好像一个整体。
Step 5:选择这3个复选框,分别对它们建立与工作表单元格B21、C21、D21的链接,如图5-83所示。这样,就可以选择图表上的3个复选框,分别查看不同项目的数据了。
图5-82 辅助数据区域
图5-83 复选框与单元格的链接
5.5.2.5 使用微调项与文本框的动态图表
使用微调项来控制数据的变化,可以显示某个指标的细微变化引起相关指标变化程度,可用于市场模拟、盈亏平衡分析等。
【例5-26】 重庆龙脊山集团公司制造一种高质量运动鞋。公司最大生产能力为1500双,固定成本为37 800元,每双可变成本为36元/双,当前的销量为900双,平均销售价格为90元/双,公司管理层需要建立一个决策模型用于盈亏平衡分析,要求模型能满足以下要求:
①计算单位边际贡献及边际贡献率;
③计算盈亏平衡销量及盈亏平衡销售收入;
④提供反映公司的销售收入、总成本、利润等数据的成本-销量-利润的图形,通过图形动态反映出销量从100双按增量10双变化到1500双时利润的变化情况及“盈利”“亏损”“盈亏平衡”的决策信息;
⑤考虑到销售价格受市场影响可能有波动,用图形模型反映销售单价从80元按增量0.5变化到100元时,盈亏平衡销量和盈亏平衡销售收入的相应变化。
实现上述要求的操作过程如下。
Step 1:根据盈利平衡分析步骤,在Excel中建立盈亏平衡分析模型,计算出单位边际贡献、边际贡献率、销售收益、总成本、利润、盈亏平衡销售量及盈亏平衡销售收益,模型结构如图5-84所示。利用公式“目标销售量=(固定成本+目标利润)/(单价-单位变动成本)”计算目标销量,利用“目标销售收益=目标销售量×单价”计算目标销售收益。
在相关单元格中通过公式建立数据链接:在B9、B10、B11、B12、B13、B15单元格中输入的公式分别是“=B5-B6”“=B9/B5”“=B2*B5”“=B6*B2+B7”“=B11-B12”和“=B7/B9”。
图5-84 盈亏平衡分析模型
Step 2:采用模拟运算表准备作图数据。以销量为自变量,同时对销售收益、总成本和利润3个函数进行单变量模拟运算。如图5-84所示中单元格E3:H5所示。在E4:E5中输入自变量的各个值,因为本例是一个线性问题,所以只需要输入自变量销量的起始值0和终止值1500[3]。在单元格F3:H3分别引用B11、B12和B13中的销售收益、总成本和利润的计算公式。选取单元格区域E3:H3→【数据】/【数据工具】→【模拟分析】→【模拟运算表】→在【输入引用列的单元格】中选中单元格B2。此处模拟运算表的含义是用自变量E4和E5的值来代替B2中的值,分别计算对应的销售收益、总成本和利润的值,将结果放置于单元格区域F4:H5中。
Step 3:利用模拟运算表数据绘制图形。XY散点图常用于反映自变量数值的函数值图形,故本例用单元格E2:H2和E4:H4的数据绘制一个XY散点图形,然后编辑图形。选中E2:H2和E4:H4→【插入】/【图表】→【散点图】/【带平滑线的散点图】→右击图表→【选择数据】→【选择数据源】/【切换行列】。
Step 4:右击“水平(值)轴”→【设置坐标轴格式】→【坐标轴选项】→最小值和最大值分别设置为0和1500,【数字】选择为常规或数值但小数位数设为0。右击“垂直(值)轴”→【设置坐标轴格式】→【坐标轴选项】→最小值和最大值分别设置为-40000和140000,【数字】选择为常规或数值但小数位数设为0。
Step 5:在单元格H8中输入0,在单元格E8、F8和G8中分别输入公式“=E4+($H$8-$H$4)/($H$5-$H$4)*(E5-E4)”“=F4+($H$8-$H$4)/($H$5-$H$4)*(F5-F4)”和“=G4+($H$8-$H$4)/($H$5-$H$4)*(G5-G4)”,其计算结果如图5-84所示。
Step 6:在单元格A20中输入公式“="销量="&ROUND(B2,0)&"时,"&IF(B13>0,"盈利",IF(B13=0,"盈亏平衡","亏损"))”,在单元格A21中输入公式“="售价="&B5&"元,盈亏平衡销量="&ROUND(E11,0)”,公式中ROUND()函数是四舍五入函数,“&”是文本连接符号。
Step 7:建立盈亏平衡线数据区E11:F15。在单元格E11:E15中输入公式“=$B$15”,F11中输入数字“140000”(坐标轴最高值略高于最高销售收入凑整),F12中输入“=B5*E12”(盈亏平衡时的销售收入),F13中输入“=B7+E13*B6”(盈亏平衡时的总成本),F14中输入“=F12-F13”(盈亏平衡时的利润),F15中输入数字“-40000”(略小于一双也不销售时的亏损额的凑整)。
利用单元格E11:F15数据在图形上添加反映盈亏平衡销售量(单元格E11:E15都引用B15)的垂直参考线及销售收益、总成本与盈亏平衡销量的交点。
Step 8:同理,建立当前销量线数据区。利用单元格E17:F21数据在图形上添加反映当前销量(单元格E17:E21都引用B2)的垂直参考线及销售收益(单元格F18引用单元格B11)、总成本(单元格F20引用单元格B13)与当前销量的交点。当前销量值C2由小变大时,反映当前销量的垂直参考线由左向右移动,利润与当前销量的交点由负数变化成正数即由亏损变化成盈利,当销量超过盈亏平衡销售量后,销量越大利润值也越大。
根据公式的销售收益、总成本、利润等数据绘制本—量—利图形。通过图形动态反映销量从100双按增量10双变化到1500双时利润的情况及“盈利”“亏损”和“保本”的决策信息。
Step 9:选中E11:F15→【复制】→用鼠标选中图表区→【选择性粘贴】→【添加单元格为】选择“新建系列”,数值(Y)轴在“列”,“首列为分类X值”。同理选择E17:F21将之粘贴于图表之上,选择同上(图5-85)。
Step 10:再将绘图区略作调整,使之留下空白。然后,单击【插入】→【文本】/【文本框】→【横排文本框】→画一空白文本框→点击文本框边框→将光标定位于编辑栏→输入“=” →单击被链接单元格A20或A21→按【Enter】确认。
Step 11:再图表上添加控件按钮。单击【开发工具】/【控件】/【插入】→【表单控件】→【数值调节钮】(图5-86)→用鼠标右击画好的按钮→单元格链接为B2,最小值设为100,最大值设为1500,步长设为10。同理,再画一微调按钮,将单元格链接设为B5,最小值设为800,最大值设为1000,步长设为5。同时,在销售单价单元格B5里输入公式“=C5/10”(因为单价是从80变化到100元,步长为0.5,而微调的步长只能为整数)。
图5-85 【选择性粘贴】
图5-86 插入【数值调节钮】
Step 12:选择组合控件、文本框等对象,将控件、文本框、图表组合,则制成可调图形,操作结果如图5-87所示。
图5-87 盈亏平衡分析模型
5.5.2.6 使用功能按钮的动态图表
公司有诸多部门,当需要分析某个部门的财务费用或预算情况时可能需要为各部门建立图表。但显然,在一张工作表上建立太多的图表,既费时费力又会使桌面显得凌乱;如果制作圆环图,但如果部门太多,会因数据点过多而杂乱。建立动态图表就只需要一张图就可以解决问题。只需要点击工作表中该部门所在单元格,按F9即显示该部门的图表。
【例5-27】 以图5-88左侧资料为例。要为各部门建立动态的饼图,其创建过程如下。
图5-88 动态图表的制作
Step 1:创建动态数据区域,如图5-88中A12:E13。
Step 2:在单元格A13输入公式:
=INDIRECT(ADDRESS(CELL(“ROW”),COLUMN(A3)))
Step 3:将A13中的公式采用拖动的办法,向右填充一直到E13。这样就得到了A12:E13的动态区域。
Step4:选定A12:E13→【插入图表】→【饼图】→选择所需要的饼图类型。要查看某部门的费用的构成情况,只需要将光标点在A3:E10数据区某部门所在行,然后按F9键即可。
免责声明:以上内容源自网络,版权归原作者所有,如有侵犯您的原创版权请告知,我们将尽快删除相关内容。