Excel Dashboards 简明教程

Excel Dashboards - Build a Dashboard

在前面的章节中，您已了解到在设置仪表板时非常有用的各种 Excel 功能。在本章中，您将学习如何构建仪表板，即设置仪表板所需的步骤。您还将了解有关仪表板的注意事项。

由于任何仪表板都基于受众最感兴趣的具体意图，因此仪表板组件和仪表板布局因情况而异。

Initial Preparation

构建仪表板的第一步是初始准备。花些时间了解以下内容 -

  1. Why do you need the dashboard? - 此仪表板是针对特定任务（如显示项目状态）还是需要实现更广泛的目标（如衡量业务绩效）？了解构建仪表板的原因将指导您进行设计。

  2. What purpose the dashboard will serve? -您的仪表盘应仅突出显示有价值的数据。您应了解所需的数据。除此之外的任何内容都是不必要的。

  3. What is the source of data? -您应了解数据来源。它可以只是一个 Excel 工作表，也可以通过多种动态数据源与 Excel 工作簿建立数据连接。

  4. Who is the audience for the dashboard? -这是针对经理、执行人员、利益相关者、外部供应商还是普通受众？了解他们的要求和偏好，例如他们查看仪表盘的时间、他们期望的详细程度以及他们希望如何消化信息。例如，在选择图表类型时，了解受众可以帮助您决定是否必须显示值之间的关系，或者必须进行特定比较。

  5. Does the dashboard need to be static or dynamic? -仪表盘可以定期（例如，每周或每月）更新，还是需要不断更新，以简化后端发生的数据更改？此选择将改变您构建仪表盘的方式。

  6. Does the dashboard need to be just a display or is it to be interactive? -仪表盘可以具有只读访问权限，还是必须提供交互式控件/功能，以便允许某些人按需探索数据？这种选择也会改变您构建仪表盘的方式。

一旦获得这些问题的答案，就确定您需要和不需要的 Excel 功能。这是因为您的目标和专长是生成适合目的的有效仪表盘。

接下来，识别仪表盘的组件。这些可以是文本、表格、图表、交互式控件等。使用这些组件确定仪表盘布局。

在 PowerPoint 幻灯片上模拟您的 Excel 仪表盘。为每个组件绘制框以了解布局，并添加您想要包含的组件的快速草图。您还可以在一张纸上进行此操作。在您开始处理实际仪表盘之前，从管理层和/或主要受众获得此模型的批准。这将节省返工时间。但是，随着仪表盘的使用和您收到反馈，您很可能需要对仪表盘进行一些更改。但是，经过批准的仪表盘模型是您工作的良好开端。

Organize the Data Source for the Excel Dashboard

在 Excel 中构建仪表盘之前，您需要组织数据源。在 Excel 中，可以通过多种方式实现这一点 −

  1. 如果数据只是 Excel 表格，则从数据将更新到的工作簿中建立到您的工作簿的链接。

  2. 如果数据来自多个 Excel 表格，或者如果数据来自多个数据源，则在您的工作簿中构建数据模型是一个不错的选择。

您可以定期将数据导入工作簿，也可以建立数据连接以刷新数据，因为它得到更新，这取决于仪表盘是静态的还是动态的。

Set Up the Excel Dashboard Workbook

一旦您组织好数据，您需要构建您的工作簿。在工作簿中插入两到三个工作表 − 一个工作表用于您的仪表盘，一到两个工作表用于数据（数据或数据透视表/数据透视图或 Power View 报告，您可以隐藏它们）。这将帮助您组织和维护 Excel 工作簿。

Prepare the Data for the Excel Dashboard

基于您拥有的选择，即您对“初始准备”步骤中的问题的答案，为 Excel 仪表盘准备数据。数据可以是以下任何一项 −

  1. Results from data analysis

  2. Results from data exploration

  3. 计算输入数据所得的数据

  4. 来自数据透视表或 PowerPivot 表的数据汇总

Select the Dashboard Components

您已经了解了仪表盘中可以使用各种 Excel 功能。基于您对仪表盘的要求，为仪表盘组件选择以下任何 Excel 功能。

  1. Tables

  2. Sparklines

  3. Conditional Formatting.

  4. Charts

  5. Slicers

  6. Interactive Controls

  7. PivotTables

  8. PivotCharts

  9. PowerPivot Tables

  10. PowerPivot Charts

  11. Power View Reports

  12. KPIs

选择仪表盘组件将帮助您调整到您批准的仪表盘模型布局。

识别静态和动态组件以及要对 slicer 分组的组件（如果有）。

Identify Parts of the Dashboard for Highlighting

识别需要立即注意的仪表板部分，例如 % 已完成或当前状态。你可以为这些部分使用较大的字体、有冲击力的字体和字体颜色。

决定在仪表板中要加入多少颜色。可以根据仪表板受众来决定。如果仪表板是为高管和/或经理准备的，请选择能影响显示结果可视化的颜色。你可以添加仪表板背景颜色，使仪表板组件突出显示。你可以对相似的图表或相关结果使用相同的颜色代码。你也可以使用条件格式。

精心挑选仪表板部分进行突出显示，使仪表板发挥作用。

Build the Dashboard

这是创建 Excel 仪表板的关键且最后一步。此步骤包括利用 Excel 照相机高效地组装仪表板组件。你将在下一节中学习如何使用 Excel 照相机。

仪表板组件组装完毕后，给它进行最后润色 −

  1. 为仪表板添加标题。

  2. Incorporate a timestamp.

  3. 必要时，添加版权信息。

在接下来的一些章节中，你将学习如何根据一些示例使用这些步骤来创建仪表板。除了某些共性之外，仪表板并没有通用的规则或布局。一切都取决于你的要求。你的目标是制作一个有效的仪表板。

Using Excel Camera

Excel 照相机可以帮助你从工作表中截取快照，然后将它们放到不同的工作表中。例如，你可以在工作表上截取一个具有条件格式的表格，然后把它放到你的仪表板中。每当数据得到更新，仪表板将刷新以显示更改后的数据。

你可以按以下步骤将 Excel 照相机作为快速访问栏的一部分：

  1. 右键单击快速访问工具栏上的小箭头。

  2. 在“自定义快速访问工具栏”列表中，单击“更多命令”。

excel camera

“Excel 选项”对话框出现。

  1. 单击“快速访问工具栏”。

  2. 在“选择命令”下，选择“所有命令”。

  3. 在命令列表中单击“照相机”。

excel options

  1. 单击“添加 >>”按钮。照相机出现在右侧列表中。

add button

  1. 单击“确定”按钮。照相机图标会出现在工作簿快速访问工具栏中。

camera appears

你可以按以下步骤使用 Excel 照相机 −

  1. 选择要捕获的单元格范围。

  2. 单击快速访问工具栏上的相机。

单元格范围将显示虚线边框。

range cells

  1. 单击你要放置捕获区域的工作表上。它可以是你的仪表盘。

  2. 单击你要放置它所在的位置。

捕获区域出现在该点上。

captured region

无论何时你对原始数据进行更改，该更改都将反映在仪表盘中。

Date and Time Stamp on Excel Dashboard

你可以在仪表盘上加入日期或日期与时间戳，以显示数据上一次更新的时间。你可以使用 Excel 函数 TODAY() 和 NOW()。

若要加入日期戳，请输入 =TODAY()，在此单元格中你在数据工作表上放置日期戳。

date time stamp

无论何时工作簿更新，都将显示当前日期。

current date

  1. 确保你输入 TODAY() 函数的单元格格式化为你要显示的日期格式。

  2. 用相机捕获显示，并把它放在仪表盘上。

capture display

仪表盘上的日期将会反映工作簿上一次更新的日期。

你可以使用 NOW() 函数在仪表盘上加入日期和时间戳，方法类似。

  1. 输入 =NOW()，在此单元格中你在数据工作表上放置日期和时间戳。

date worksheet

  1. 确保你有合适的日期和时间格式。

  2. 用相机捕获显示，并把它放在仪表盘上。

日期和时间戳将加入仪表盘中，且会反映工作簿上一次更新的日期和时间。

Test, Sample, and Enhance the Dashboard

你需要测试仪表盘以确保它准确地显示数据。

  1. 在各种可能的情况下测试它。

  2. 测试确切的更新（静态或动态，目前是这种情况）。

  3. 测试交互式控件（如果有）。

  4. 测试外观和感觉。

您可能需要进行一些试运行以确保您的仪表盘符合您的要求。

下一步是让样本受众评估仪表盘，尤其是那些批准您的模型仪表盘的受众。由于他们将使用仪表盘，因此他们无疑会对仪表盘的使用和有效性提供意见。此反馈有助于您确保仪表盘的有效性。不要犹豫，征求反馈。

一旦获得反馈，就对反馈进行增强，如有必要，进行必要的更改。您的 Excel 仪表盘已准备就绪。

Share the Dashboard

您需要向目标受众提供 Excel 仪表盘。您可以通过多种方式进行此操作。

  1. 发送 Excel 仪表盘工作簿（您必须隐藏仪表盘工作表之外的工作表。您还可以保护工作簿）。

  2. 将 Excel 仪表盘工作簿保存在共享的网络驱动器上。

  3. Share the dashboard online.

如果 Excel 仪表盘是静态的，您可以通过邮件发送它，但如果是动态的或具有交互式控件，则它应该具有与后端数据的连接，因此需要在线共享。

您可以使用以下任何一种选项在线共享 Excel 仪表盘−

  1. Microsoft OneDrive。使用您的 Windows Live 帐户，您将获得 OneDrive 访问权限，您可以在其中发布和共享文档。

  2. New Microsoft Office Online.

  3. Microsoft SharePoint.

您还可以将 Excel 工作簿文件另存为 Acrobat Reader 文件 (.pdf) 并将其发布到 Web。但同样，此选项仅适用于静态仪表盘。

Tips for Effective Excel Dashboards

要使 Excel 仪表盘有效，您需要做某些事情并避免做另一些事情。以下是制作出色的 Excel 仪表盘的一些技巧：

  1. 简单明了。一个简单易懂的仪表盘比一个花哨的仪表盘更有效。请记住，需要强调的是数据。Glenna Shaw 认为，您需要在让仪表盘具有足够的吸引力以吸引受众注意力的同时，又不至于花哨到模湖显示的信息之间取得平衡。最好避免使用 3D 效果、渐变、额外的形状和不必要的工具。如果您能够使用条件格式或迷你图实现有力显示，则优先使用表格而非图表。

  2. 使用 Excel 条件格式。使用 Excel 条件格式提供了多种选择，可以根据表中的值自动更新。

  3. 选择适当的图表类型。请记住，使用图表类型没有通用的规则。有时，常规图表类型（如柱形图、条形图、甜甜圈图等）比出现的新型复杂图表更有力地传达信息。您可以使用 Excel 推荐图表命令来初步评估合适的图表类型。由于您可以使用 Excel 命令（更改图表类型）更改图表类型，因此您可以随意尝试，可视化显示并选择合适的图表。

  4. 使用交互式控件。使用交互式控件，如滚动条、选项（单选）按钮和复选框，可以帮助用户轻松有效地可视化数据的不同方面。

  5. 对大数据使用 Excel 数据模型。如果您有来自不同数据源的大型数据集，则最好使用 Excel 数据模型，它可以通过内存优化处理数千行数据，并可以将数据表分组并建立关系。

  6. 选择合适的颜色。颜色选择要谨慎。明智地使用颜色以提供足够的影响，但不可违背其目的。此外，如果受众可能包括色盲者，则避免使用红色和绿色。在这些情况下，虽然交通灯符号对正在显示的数据听起来有效，但它们不适合用于仪表板。改为使用灰度。

  7. 使用切片器。切片器比下拉列表更有效，因为它们具有视觉冲击力。您可以对图表、数据透视表、数据透视图进行分组以使用公共切片器。

  8. 将 Excel 仪表板组件组合在一起。您可以通过插入一个形状（例如矩形）并放置可以在该形状上分组的仪表板组件，为仪表板增添视觉趣味。例如，如果您正在使用公共切片器，您可以对所有共享该切片器的仪表板组件进行分组。

  9. 使用 Excel 数据层次结构。如果您的数据具有内在数据层次结构，请在数据模型中定义它们并使用它们对数据进行交互式向上钻取和向下钻取。

  10. 避免仪表板布局拥挤。请记住，显示过多的信息会让受众不知所措，并偏离实际目的。如果没有必要，请不要在仪表板中包含任何数据或图表。在测试仪表板时，这是一个重要的检查点。评估每个仪表板组件是否必要且足够。仪表板组件和布局应支持仪表板的单一目的。