创建Excel仪表板(用示例和模板说明)

当涉及到跟踪KPI、比较数据点和获取有助于管理层做出决策的数据备份视图时,Excel仪表板可能是一个了不起的工具。

在本教程中,您将学习如何创建Excel仪表板、创建仪表板时应遵循的最佳实践、可在Excel中使用的功能和工具、不惜一切代价避免的事项以及推荐的培训材料。

Excel仪表板示例和模板

什么是Excel仪表板?它与报表有何不同?

让我们首先了解什么是Excel仪表板。

Excel仪表板是一个页面(主要是,但并不总是必要的),它可以帮助经理和业务领导人跟踪关键的kpi或指标,并根据这些指标做出决策。它包含由数据支持的图表/表格/视图。

仪表板通常称为报表,但并非所有报表都是仪表板。

区别在于:

  • 报告只能在一个地方收集和显示数据。例如,如果一个经理想知道上一个时期的销售增长情况以及哪个地区最赚钱,那么一份报告就无法回答这个问题。它只需报告所有相关的销售数据。然后使用这些报告创建有助于决策的仪表板(Excel或PowerPoint)。
  • 另一方面,仪表盘将立即回答一些重要问题,如哪些地区的表现更好,管理层应关注哪些产品。这些仪表板可以是静态的,也可以是交互式的(用户可以在其中进行选择和更改视图,数据将动态更新)。

现在我们已经了解了什么是仪表板,让我们深入学习如何在Excel中创建仪表板。

如何创建Excel面板?

创建Excel仪表板是一个多步骤的过程,在创建它时需要记住一些关键的事情。

即使在启动Excel之前,您也需要明确仪表板的目标。

例如,如果您要创建一个KPI仪表板来跟踪公司的财务KPI,则您的目标是显示当前期间与过去期间的比较。

类似地,如果您要为人力资源部门创建一个仪表板来跟踪员工培训,那么目标将是显示有多少员工接受过培训,以及有多少员工需要接受培训才能达到目标。

在开始创建Excel仪表板之前要做的事情

很多人一拿到数据就开始在仪表盘上工作。

在大多数情况下,由于客户/利益相关者的目标没有得到满足,它们给他们带来了在仪表盘上返工的痛苦。

以下是开始构建Excel仪表板之前必须回答的一些问题:

问:仪表盘的用途是什么?

一旦获得数据(甚至在获得数据之前),首先要做的就是弄清楚利益相关者想要什么。明确仪表板需要用于什么目的。

是只跟踪一次KPI,还是定期跟踪?它需要跟踪整个公司或部门的关键绩效指标吗?。提出正确的问题可以帮助您了解需要哪些数据以及如何设计仪表板。

问:数据来源是什么?

始终知道数据的来源和格式。在我的一个项目中,数据是以西班牙语的PDF文件提供的。这完全改变了范围,我们的大部分时间都花在了手动挑选数据上。以下是您应该问的问题:谁拥有这些数据?您将以什么格式获取数据?数据多久更新一次?

问:谁将使用这个Excel面板?

经理可能只对您的仪表板提供的见解感兴趣,但是,他的团队中的一些数据分析师可能需要更详细的视图。根据使用仪表板的用户,您需要对数据和最终输出进行结构化。

问:Excel面板需要多久更新一次?

如果您的仪表板每周或每月更新一次,那么最好创建一个即插即用模型(您只需复制粘贴数据,它就会自动更新)。如果这只是一次性的练习,你可以省去一些自动化而手动完成。

问:客户/利益相关者使用什么版本的Office?

最好不要假设客户/干系人拥有最新版本的msoffice。我曾经创建了一个仪表板,但却发现我的干系人使用的是Excel 2003,这导致了一些返工,因为IFERROR函数在Excel 2003版本中不起作用(我在创建仪表板时广泛使用了该版本)。

在Excel中获取数据

一旦你对你需要创建的东西有了一个很好的想法,接下来的步骤就是把你的手放在数据上并把它放到Excel中。

当你的客户在Excel中给你数据时,你的生活很轻松,然而,如果不是这样,你需要找出一种有效的方法来在Excel中获取数据。

如果提供了CSV文件或文本文件,则可以在Excel中轻松转换这些文件。如果您有权访问存储数据的数据库,则可以创建连接并间接更新它。

一旦你有了这些数据,你就需要清理和标准化它。

例如,你可能需要摆脱前导空格、尾随空格或双空格,查找并删除重复项,删除空格和错误,等等。

在某些情况下,您甚至可能需要重新构造数据(例如说您需要创建数据透视表).

这些步骤将取决于项目以及数据在Excel中的外观。

概述仪表板的结构

一旦在Excel中有了这些数据,您就可以确切地知道在Excel仪表板中可以使用什么和不能使用什么。

在这个阶段,一个好主意是用Excel仪表板的概要与您的利益相关者进行交流。

作为一种最佳实践,我在PowerPoint中创建了一个简单的提纲和附加注释。此步骤的目的是确保您的干系人了解他/她可以期望使用可用数据使用哪种仪表板。

这也很有帮助,因为利益相关者可能会提出一些改变,为他增加更多的价值。

下面是我为其中一个KPI仪表板创建的示例大纲:

创建excel仪表板大纲示例

一旦你制定了大纲,就可以开始创建Excel仪表板了。

作为最佳实践,将Excel工作簿分为三部分(这些是我用相同名称创建的工作表):

  • 数据–这可以是一个或多个包含原始数据的工作表。
  • 计算–这是你进行所有计算的地方。同样,您可能有一张或多张表用于计算。
  • 仪表板–这是有仪表板的表。在大多数情况下,它是一个单页视图,显示由数据支持的分析/见解。

创建Excel面板-3个选项卡

Excel表-高效Excel仪表板的秘诀

我对原始数据做的第一件事是将其转换为Excel表格.

Excel表提供了许多在创建Excel仪表板时至关重要的优点。

要将表格数据转换为Excel表格,请选择数据并转到“插入”选项卡,然后单击表格图标。

创建excel仪表板excel表

以下是将Excel表用于仪表板的好处:

  • 当您将表格数据集转换为Excel表格时,您不必担心数据在以后的阶段会发生更改。即使得到了额外的数据,也可以简单地将其添加到表中,而不用担心公式会出错。这在我创建即插即用仪表板时非常有用。
  • 对于Excel表,可以使用列的名称而不是引用。例如,如果是C2:C1000,则可以使用“Sales”。

仪表板的重要Exce188亚洲金博宝appl函数

只需使用Excel公式.

当您从下拉列表中进行选择、使用滚动条或选中复选框时,会根据结果更新公式,并在仪表板中为您提供更新的数据/视图。

下面是我在Excel仪表板上使用的五大Ex188亚洲金博宝appcel函数:

  • SUMPRODUCT函数:这是我在创建交互式Excel面板时最喜欢的函数。它允许我在有很多变量的情况下进行复杂的计算。例如,假设我有一个销售仪表盘,我想知道代表Bob在东部地区第三季度的销售情况。我可以简单地创建一个产品公式。
  • 索引/匹配功能:我非常支持使用索引和匹配公式的组合在Excel仪表板中查找数据。您也可以使用VLOOKUP函数,但我发现索引/匹配是更好的选择。
  • IFEROR函数:在对原始数据进行计算时,通常会出现错误。我广泛使用IFERROR来隐藏仪表板中的错误(在原始数据中也有很多次)。
  • 文本函数:如果要创建动态标题,则需要使用文本函数。
  • /函数:当我需要复制一个公式,其中一个参数需要在单元格的右下方向递增时,我经常使用这些函数。

在Excel仪表板中使用公式时要记住的事项:

  • 避免使用挥发性配方奶粉(例如抵消,全国妇女组织,今天,等等……)。这些会减慢你的工作进度。
  • 删除不必要的公式。如果计算表中有其他公式,请在完成仪表板时删除这些公式。
  • 使用helper列,因为它可以帮助您避免长数组公式。

交互式工具,使您的Excel仪表板真棒

有许多交互式工具可用于使Excel仪表板动态且用户友好。

以下是我经常使用的:

  • 滚动条:使用滚动条以保存您的工作簿不动产。例如,如果有100行数据,则可以在仪表板中使用只有10行的滚动条。如果用户希望查看整个数据集,可以使用滚动条。
  • 复选框:复选框允许您进行选择并更新仪表板。例如,假设我有一个培训仪表盘,而我是公司的首席执行官,我希望查看整个公司仪表盘。但如果我是销售主管,我只想看看我所在部门的业绩。在这种情况下,您可以为公司的不同部门创建带有复选框的仪表板。
  • 下拉列表:A118bet网址多少 允许用户进行独占选择。您可以使用这些下拉选择来更新仪表板。例如,如果按部门显示数据,则可以在下拉列表中显示部门名称。

使用Excel图表使Excel仪表板中的数据可视化

图表不仅使您的Excel仪表板具有视觉吸引力,而且使其易于使用和解释。

以下是在Excel仪表板中使用图表时的一些提示:

  • 选择正确的图表:Excel为您提供了许多图表选项,您需要使用正确的图表。例如,如果必须显示趋势,则需要使用折线图,但如果要突出显示实际值,则条形图/柱形图可能是正确的选择。很多专家建议不要使用饼图,我建议你谨慎行事。如果你的听众习惯于看饼图,你不妨用这些。
  • 使用组合图:我强烈建议使用组合图因为这允许用户比较价值观并得出有意义的见解。例如,您可以将销售数字显示为柱状图,将增长显示为折线图。

  • 使用动态图表:如果要允许用户进行选择并希望图表随其更新,请使用动态图表。现在,动态图表只不过是一个常规图表,当您做出选择时,它的数据会在后端更新。

创建excel仪表板动态图表

  • 使用迷你图使数据更有意义如果你的仪表板/报告中有很多数据,你可以考虑使用火花线使其可视化。迷你图是驻留在单元格中的小图表,可以使用数据集创建。当您希望显示一段时间内的趋势,同时节省仪表板上的空间时,这些工具非常有用。你可以了解更多此处显示Excel迷你图.
  • 使用对比色突出显示数据:这是一个通用的图表提示,您应该在其中突出显示图表中的数据,以便易于理解。例如,如果有销售数据,可以用红色突出显示销售值最低的年份。

创建excel仪表板对比色

你可以浏览我的一些书1188金宝搏亚洲 .

另外,如果你想在Excel图表方面更高级,我建议你访问Excel图表专家的博客乔恩·帕尔蒂埃.

Excel仪表板的注意事项

让我们先从“不要”开始!

以下是我建议您在创建Excel仪表板时避免使用的一些方法。同样,这些会根据您的项目和涉众而有所不同,但在大多数情况下都是有效的。

  • 不要把你的仪表盘弄得乱七八糟:仅仅因为你有数据和图表并不意味着它应该放在你的仪表盘里。记住,仪表盘的目的是帮助识别问题或帮助决策。因此,保持它的相关性,并删除所有不属于那里。我经常问自己,有些东西是不是很好,是绝对必须的。然后我就把所有的好东西都拿走了。
  • 不要使用易变公式:因为它会减慢计算速度。
  • 不要在工作簿中保留额外的数据:如果需要这些数据,请创建仪表板的副本并将其作为备份。

现在让我们看看一些应该做的(或最佳实践)

  • 为图表/部分编号:仪表板不仅仅是一组随机的图表和数据点。相反,这是一个故事,一件事导致另一个。你需要确保你的听众按照正确的顺序遵循这些步骤,因此最好对这些步骤进行编号。虽然你可以在现场演示时指导他们,但当有人在后期使用你的仪表板或从仪表板上打印出来时,这是一个很大的帮助。
  • 限制仪表板区域中的移动:隐藏所有行/列以确保用户不会意外地滚动离开。
  • 冻结重要行/列:使用冻结窗格当您希望某些行/列在仪表板上始终可见时。
  • 使形状/图表保持不变:确保当有人隐藏/调整单元格大小时,形状/图表或交互控件不会隐藏或调整大小。您也可以使用Excel照相机该工具用于拍摄图表/表格的快照并将其放置在Excel仪表板中(这些图像是动态的,并且在后端图表/表格更新时更新)。
  • 提供用户指南:如果您有一个复杂的仪表板,最好创建一个单独的工作表并突出显示步骤。它将帮助人们使用你的仪表板,即使你不在那里。
  • 使用组合图表节省空间:使用组合图表(例如项目符号图表,温度计图表,和实际与目标图表)以节省工作表空间。
  • 使用符号和条件格式:使用符号(如上/下箭头或复选标记/交叉标记)和条件格式为仪表板添加一层分析(但也不要过度)。

想在Excel中创建专业仪表盘吗?查看在线Excel仪表板课程在这里,我向您展示了创建世界级Excel仪表板的所有内容。

仪表板示例

下面是一些很酷的Excel仪表板示例,您可以下载并使用它们。

Excel KPI仪表板

Excel中的KPI仪表板

您可以使用此仪表板跟踪各个公司的KPI,然后使用项目符号图深入了解各个公司的绩效。

单击此处下载此KPI仪表板。

如果您有兴趣学习如何创建此KPI仪表板-单击此处.

呼叫中心性能仪表板

Excel中的呼叫中心性能仪表板

您可以使用此仪表板跟踪呼叫中心的关键KPI。从这个仪表板,您可以学习如何创建组合图表,如何突出显示图表中的特定数据点,如何使用单选按钮进行排序,等等。

单击此处下载仪表板。

您可以阅读有关此仪表板的更多信息在这里.

在Excel仪表板中可视化的EPL季节

Excel中的EPL仪表板

在此仪表板中,您将学习如何在Excel仪表板中使用VBA。在此仪表板中,当您双击左侧的单元格时,游戏的详细信息将更新。它还使用VBA显示帮助菜单,以指导用户使用此仪表板。

单击此处下载EPL仪表板。

您可以阅读有关此仪表板的更多信息在这里.

推荐仪表盘培训

如果您想学习如何在Excel中创建世界一流的专业仪表盘,请查看我的免费在线Excel仪表板课程.