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

 

许多公司很难创建支出差异图表。实际上，许多公司都依赖于几乎没有用的图表。

 

这是太多公司使用的一张毫无价值的图表的示例。这是从年初至今比较一月到十月实际支出和预算支出的数据。

 

 

 

 

 

该图表的问题在于它隐藏了可操作的信息。它不会显示它。

 

该图表最明显的信息由向上倾斜的线表示，是年初至今（YTD）的预算和支出在一年中向上攀升。也就是说，该图表旨在强调数学确定性。

 

该图表试图传达的唯一有用信息是YTD方差的量，它是所示两条线之间的差。但是，由于使用了太多垂直空间来显示YTD总数，因此实际上不可能估计相对较小的方差。

 

要了解此图表如何有效地隐藏信息，请让自己担任经理检查图表的位置。如果您要就此支出表现向员工进行测验，您几乎没有事实可作为依据。员工将没有任何事实可以作为解释依据。 

 

相比之下，下表显示了相同的基础数据，但是格式传达了可操作的信息。

 

 

在这里，棕褐色的面积图显示了该年度的每月预算。黑线显示实际的每月支出。

 

该图表中的橄榄绿色列显示了年初至今的变化。与年初至今的支出和预算不同，差异不一定会增加。例如，在此图表中，一年前四个月的年初至今累计变化是有利的（小于零）。最近，年初至今的差异在8月和9月有所下降。

 

与第一个图表不同，此图表提供了足够的信息来提醒管理人员出现问题。它还为经理提供了足够的详细信息，以开始合理讨论支出差异的原因。

 

（要查看我们如何在Excel 2003中创建棕褐色和橄榄绿色的Excel图表，请阅读 在Excel 97-2003中显示任何颜色。）

 

 

尽管这是一个相当标准的Excel图表，但它具有几个不同寻常的特征。

 

 

此图显示了支持图表的工作表数据。启动“图表向导”时，将选择范围A1：D15，如图所示。为了使“图表向导”可以正确解释布局，请确保单元格A1为空。

 

图表范围包括第2行和第15行。这样做是为了提供显示柱状图整个宽度所需的水平空间，如上面的第一张图所示。

 

 

要成功显示灰色行，必须在三个灰色单元格中进行输入。

 

 

这两个公式在使用该公式的单元格下方或上方的单元格中返回值。通过使用 OFFSET 函数，您可以在灰色行之间插入新行，而无需更改公式。

 

为了说明为什么 在这些公式中使用OFFSET函数，假设单元格C15使用公式= C14。如果您在第14和15行之间插入一行，则单元格C16中的公式将不会引用新的单元格C15。相反，它仍将引用单元格C14。使用OFFSET可以解决此问题。

 

要进行的第三项输入是在单元格A2中输入撇号。这也称为单引号。此项使Excel在图表的第一个月显示为空白。没有该条目，Excel就会对月份如何与数据匹配感到困惑。

 

单元格A3包含日期值1/1/2004。此列中的其余单元格包含显示的等效日期。要格式化日期，请选择范围A2：A15；选择格式，单元格；在数字选项卡中，选择自定义；然后输入“ m yy”（不带引号）作为日期格式；然后选择确定。

 

在下面显示的单元格中输入以下公式，然后将其复制到如图所示的列中。

 

 

对于未输入实际支出的月份，此公式将返回NA（）。这将导致Excel在这些月份中不显示任何行。

 

 

要创建图表，请选择上图中的区域A1：D15并启动图表向导。在“标准类型”选项卡中，选择“行”。然后选择左上方的图表子类型。单击下一步，然后再次单击下一步。

 

在“网格线”选项卡中，取消选择所有网格线。在图例选项卡中，取消选择显示图例。然后单击完成。

 

要关闭背景阴影，请在绘图区域上单击鼠标右键；选择格式绘图区域；选择一个无区域；然后选择确定。

 

 

要在图表底部显示月份和年份，请右键单击水平轴，选择“设置轴格式”，然后选择“模式”选项卡。在“刻度标记标签”选项框中，选择“低”选项。

 

仍在“格式轴”对话框中时，选择“比例”选项卡。可能已选中“值（Y）轴在类别之间交叉”复选框。确保未选中它。然后选择确定。

 

要将预算数据显示为面积图，请右键单击显示预算数据的行，然后选择“图表类型”。在“图表类型”列表框中，选择“区域”。选择左上方的图表子类型。然后选择确定。

 

要将YTD方差显示为柱形图，请右键单击显示此数据的行，然后选择“图表类型”。在“图表类型”列表框中，选择“列”。选择左上方的子类型。然后选择确定。

 

此图表中使用的日期格式特别适用于小型图表。在您的图表版本中，月份和年份可能位于同一行，而不是如上图所示。要创建包裹效果，只需缩小图表即可。如果您想要更大的图表，则可能希望使用其他日期格式。