设置主页 | 收藏本站 Excel学习网-公益型的EXCEL在线学习网站,助您轻松办公!

在Excel仪表盘中显示有用的年初至今方差图-excel表格

2019年10月29日 21:25 发布来源:Excel学习网

 

 
许多公司很难创建支出差异图表。实际上,许多公司都依赖于几乎没有用的图表。
 
这是太多公司使用的一张毫无价值的图表的示例。这是从年初至今比较一月到十月实际支出和预算支出的数据。
 

在Excel仪表盘中显示有用的年初至今方差图-excel表格


 


 


 

 
该图表的问题在于它隐藏了可操作的信息。它不会显示它。
 
该图表最明显的信息由向上倾斜的线表示,是年初至今(YTD)的预算和支出在一年中向上攀升。也就是说,该图表旨在强调数学确定性。
 
该图表试图传达的唯一有用信息是YTD方差的量,它是所示两条线之间的差。但是,由于使用了太多垂直空间来显示YTD总数,因此实际上不可能估计相对较小的方差。
 
要了解此图表如何有效地隐藏信息,请让自己担任经理检查图表的位置。如果您要就此支出表现向员工进行测验,您几乎没有事实可作为依据。员工将没有任何事实可以作为解释依据。 
 
相比之下,下表显示了相同的基础数据,但是格式传达了可操作的信息。
 
在Excel仪表盘中显示有用的年初至今方差图-excel表格
 
在这里,棕褐色的面积图显示了该年度的每月预算。黑线显示实际的每月支出。
 
该图表中的橄榄绿色列显示了年初至今的变化。与年初至今的支出和预算不同,差异不一定会增加。例如,在此图表中,一年前四个月的年初至今累计变化是有利的(小于零)。最近,年初至今的差异在8月和9月有所下降。
 
与第一个图表不同,此图表提供了足够的信息来提醒管理人员出现问题。它还为经理提供了足够的详细信息,以开始合理讨论支出差异的原因。
 
(要查看我们如何在Excel 2003中创建棕褐色和橄榄绿色的Excel图表,请阅读 在Excel 97-2003中显示任何颜色。)
 
创建图表
 
尽管这是一个相当标准的Excel图表,但它具有几个不同寻常的特征。
 
数据布局
 
此图显示了支持图表的工作表数据。启动“图表向导”时,将选择范围A1:D15,如图所示。为了使“图表向导”可以正确解释布局,请确保单元格A1为空。
 
图表范围包括第2行和第15行。这样做是为了提供显示柱状图整个宽度所需的水平空间,如上面的第一张图所示。
 
在Excel仪表盘中显示有用的年初至今方差图-excel表格
 
要成功显示灰色行,必须在三个灰色单元格中进行输入。
 
输入为这两个单元格显示的公式:

C2:= OFFSET(C2,1,0)
C15:= OFFSET(C15,-1,0) 
 
这两个公式在使用该公式的单元格下方或上方的单元格中返回值。通过使用 OFFSET 函数,您可以在灰色行之间插入新行,而无需更改公式。
 
为了说明为什么 在这些公式中使用OFFSET函数,假设单元格C15使用公式= C14。如果您在第14和15行之间插入一行,则单元格C16中的公式将不会引用新的单元格C15。相反,它仍将引用单元格C14。使用OFFSET可以解决此问题。
 
要进行的第三项输入是在单元格A2中输入撇号。这也称为单引号。此项使Excel在图表的第一个月显示为空白。没有该条目,Excel就会对月份如何与数据匹配感到困惑。
 
单元格A3包含日期值1/1/2004。此列中的其余单元格包含显示的等效日期。要格式化日期,请选择范围A2:A15;选择格式,单元格;在数字选项卡中,选择自定义;然后输入“ m yy”(不带引号)作为日期格式;然后选择确定。
 
在下面显示的单元格中输入以下公式,然后将其复制到如图所示的列中。
 
D3:= IF(B3 = 0,NA(),B3-C3 + D2)
 
对于未输入实际支出的月份,此公式将返回NA()。这将导致Excel在这些月份中不显示任何行。
 
图表布局
 
要创建图表,请选择上图中的区域A1:D15并启动图表向导。在“标准类型”选项卡中,选择“行”。然后选择左上方的图表子类型。单击下一步,然后再次单击下一步。
 
在“网格线”选项卡中,取消选择所有网格线。在图例选项卡中,取消选择显示图例。然后单击完成。
 
要关闭背景阴影,请在绘图区域上单击鼠标右键;选择格式绘图区域;选择一个无区域;然后选择确定。
 
在Excel仪表盘中显示有用的年初至今方差图-excel表格
 
要在图表底部显示月份和年份,请右键单击水平轴,选择“设置轴格式”,然后选择“模式”选项卡。在“刻度标记标签”选项框中,选择“低”选项。
 
仍在“格式轴”对话框中时,选择“比例”选项卡。可能已选中“值(Y)轴在类别之间交叉”复选框。确保未选中它。然后选择确定。
 
要将预算数据显示为面积图,请右键单击显示预算数据的行,然后选择“图表类型”。在“图表类型”列表框中,选择“区域”。选择左上方的图表子类型。然后选择确定。
 
要将YTD方差显示为柱形图,请右键单击显示此数据的行,然后选择“图表类型”。在“图表类型”列表框中,选择“列”。选择左上方的子类型。然后选择确定。
 
此图表中使用的日期格式特别适用于小型图表。在您的图表版本中,月份和年份可能位于同一行,而不是如上图所示。要创建包裹效果,只需缩小图表即可。如果您想要更大的图表,则可能希望使用其他日期格式。
原创文章如转载请注明:转载自Excel学习网 [ http://www.excelcn.com/]
需要保留本文链接地址:http://www.excelcn.com/jiqiao/10786.html
分享到:
网站地图 | XML地图 | 免责声明 | 关于我们 | Excel学习网:优秀的EXCEL在线学习和资源分享网站。
版权所有: CopyRight © 2013-2018 www.excelcn.com All Rights Reserved.
豫ICP备12002644号