Excel 仪表板 - 快速指南

Excel 仪表板 - 简介

对于仪表板新手来说，最好首先了解仪表板。在本章中，您将了解仪表板的定义、仪表板名称的由来、仪表板如何在 IT 领域流行、关键指标、仪表板的优点、仪表板类型、仪表板数据和格式以及仪表板上的实时数据。

在信息技术中，仪表板是一种易于阅读的、通常是单页的实时用户界面，以图形方式显示组织或部门关键绩效指标的当前状态（快照）和历史趋势，以实现即时且明智的决策一目了然。

仪表板的名字来源于汽车仪表板。在您的车辆的引擎盖下，可能有数百个过程会影响您的车辆的性能。您的仪表板使用可视化方式总结这些事件，以便您安心地专注于安全操作您的车辆。以类似的方式，业务仪表板用于轻松查看和/或监控组织的绩效。

数字仪表板的想法源于 20 世纪 70 年代对决策支持系统的研究。业务仪表板最早开发于20世纪80年代，但由于数据刷新和处理方面的问题而被束之高阁。20 世纪 90 年代，信息时代的步伐加快，数据仓库和在线分析处理 (OLAP) 使仪表板能够充分发挥作用。然而，直到关键绩效指标 (KPI) 兴起以及 Robert S. Kaplan 和 David P. Norton 的平衡计分卡引入后，仪表板的使用才开始流行。如今，仪表板的使用已成为决策的重要组成部分。

在当今的商业环境中，趋势是大数据。管理所有数据并从中提取真正价值是现代企业成功的关键。精心设计的仪表板是卓越的信息管理工具。

仪表板 – 定义

Stephen Few 将仪表板定义为“实现一个或多个目标所需的最重要信息的视觉显示，完全适合单个计算机屏幕，因此可以一目了然地进行监控”。

目前，仪表板可以定义为一种数据可视化工具，它显示指标和关键绩效指标（KPI）的当前状态，简化复杂的数据集，使用户对当前绩效一目了然。

仪表板在单个屏幕上整合和排列数字和指标。它们可以针对特定角色进行定制，并显示部门或组织的整体指标。

仪表板可以是静态的一次性视图，也可以是动态的，显示屏幕后面数据更改的综合结果。它们还可以进行交互，以在单个屏幕上显示大数据的各个部分。

仪表板的关键指标

仪表板的核心在于监控所需的关键指标。因此，根据仪表板是针对整个组织还是针对销售、财务、人力资源、生产等部门，显示所需的关键指标会有所不同。

此外，仪表板的关键指标还取决于接收者（受众）的角色。例如，高管（CEO、CIO 等）、运营经理、销售主管、销售经理等。这是因为仪表板的主要目标是实现数据可视化以进行决策。

仪表板的成功通常取决于选择用于监控的指标。例如，关键绩效指标、平衡计分卡和销售绩效数据可能是业务仪表板中合适的内容。

仪表板的好处

仪表板允许管理者监控组织中各个部门的贡献。为了监控组织的整体绩效，仪表板允许您捕获和报告组织中每个部门的特定数据点，提供当前绩效的快照以及与早期绩效的比较。

仪表板的好处包括以下几点 -

绩效指标的视觉呈现。
能够识别和纠正负面趋势。
效率/低效率的测量。
能够生成显示新趋势的详细报告。
能够根据收集的数据做出更明智的决策。
战略和组织目标的一致性。
所有系统的即时可见性。
快速识别数据异常值和相关性。
与运行多个报告相比，全面的数据可视化可以节省时间。

仪表板的类型

仪表板可以根据其实用程序进行分类，如下所示 -

战略仪表板
分析仪表板
操作仪表板
信息仪表板

战略仪表板

战略仪表板支持组织中任何级别的经理进行决策。它们提供数据快照，显示业务的健康状况和机会，重点关注绩效和预测的高水平衡量标准。

战略仪表板需要定期和静态的数据快照（例如每日、每周、每月、每季度和每年）。它们不需要从一个时刻到下一个时刻不断变化，并且需要在指定的时间间隔进行更新。
它们仅描绘高级数据，不一定提供细节。
它们可以是交互式的，以便在单击按钮即可在大型数据集的情况下进行比较和不同的视图。但是，没有必要在这些仪表板中提供更多交互功能。

以下屏幕截图显示了执行仪表板的示例，其中显示了目标和进度。

分析仪表板

分析仪表板包括更多上下文、比较和历史记录。他们专注于分析所需数据的各个方面。

分析仪表板通常支持与数据的交互，例如深入了解底层细节，因此应该是交互式的。

分析仪表板的示例包括财务管理仪表板和销售管理仪表板。

操作仪表板

操作仪表板用于持续监控操作。它们的设计通常与战略或分析仪表板不同，侧重于监控不断变化的活动和事件，并且可能需要立即关注和响应。因此，操作仪表板需要随时提供实时且最新的数据，因此应该是动态的。

操作仪表板的一个示例可以是支持系统仪表板，显示服务票据的实时数据，需要主管立即对高优先级票据采取行动。

信息仪表板

信息仪表板仅用于显示数字、事实和/或统计数据。它们可以是静态的，也可以是带有实时数据的动态的，但不能交互。例如，机场的航班到达/出发信息仪表板。

仪表板数据和格式

仪表板所需的数据取决于其类别。数据的前提是，如果需要的话，它应该是相关的、无错误的、最新的和实时的。数据可能来自各种不同的来源和格式（电子表格、文本文件、网页、组织数据库等）。

仪表板上显示的结果必须真实、正确且恰当。这一点至关重要，因为仪表板上的信息将导致决策、行动和/或推断。因此，与显示的数据一样，选择用于显示的介质也同样重要，因为它不应在数据描绘中给人留下错误的印象。重点应该放在数据可视化的能力上，以明确地得出结论。

仪表板上的实时数据

正如本章前面所讨论的，数据仓库和在线分析处理 (OLAP) 使得使用实时数据即时刷新动态仪表板成为可能。它还使设计仪表板的人员独立于组织的 IT 部门来获取数据。

因此，仪表板已成为从高层管理人员到普通用户最受追捧的媒介。

用于创建仪表板的 Excel 功能

您可以使用各种功能在 Excel 中创建仪表板，这些功能可帮助您突出数据可视化，这是任何仪表板的主要特征。您可以使用条件格式在表格中显示数据以突出显示好结果和坏结果，可以在图表和数据透视表中汇总数据，可以添加交互式控件，还可以定义和管理 KPI 等。

在本章中，您将了解创建仪表板时派上用场的最重要的 Excel 功能。这些功能可帮助您获得仪表板元素，这些元素可简化复杂的数据并实时提供对当前状态或性能的视觉影响。

Excel 表格

任何仪表板最重要的组成部分是其数据。数据可以来自单个源或多个源。数据可能有限或可能跨越多行。

Excel 表格非常适合将数据导入到要在其中创建仪表板的工作簿中。通过建立与各种源的连接，可以通过多种方式将数据导入 Excel。这样，只要源数据更新，就可以刷新工作簿中的数据。

您可以命名 Excel 表并使用这些名称来引用仪表板中的数据。这比使用单元格引用引用数据范围更容易。这些 Excel 表是包含原始数据的工作表。

您可以得出数据分析的摘要，并在 Excel 表格中进行描述，该表格可以作为仪表板的一部分包含在内。

迷你图

您可以在 Excel 表格中使用迷你图来显示一段时间内的趋势。迷你图是可以放置在单个单元格中的迷你图表。您可以使用折线图、柱形图或盈亏图来描绘基于您的数据的趋势。

条件格式

条件格式是突出显示表中数据的重要资产。您可以定义改变色标、数据条和/或图标集的规则。您可以使用 Excel 定义的规则，也可以根据数据的适用性创建自己的规则。

您将在“数据可视化的条件格式”一章中学习这些条件格式技术。

Excel 图表

Excel 图表是仪表板中使用最广泛的数据可视化组件。您可以让观众查看任何大小的数据集中的数据模式、比较和趋势，并显着添加颜色和样式。

如果您有 Excel 2013，Excel 具有多种内置图表类型，例如折线图、条形图、柱形图、散点图、气泡图、饼图、圆环图、面积图、股票图、曲面图和雷达图。

您将在“仪表板的 Excel 图表”一章中了解如何在仪表板中有效地使用这些图表和图表元素。

除了上述图表类型之外，还有其他广泛使用的图表类型可以方便地表示某些数据类型。这些是瀑布图、带状图、甘特图、温度计图、直方图、帕累托图、漏斗图、盒须图和华夫饼图。

您将在“仪表板的高级 Excel 图表”一章中了解这些图表。

Excel相机

创建图表后，您需要将它们放置在仪表板中。如果您想让仪表板动态化，每次源数据更改时都会刷新数据（大多数仪表板都是这种情况），您需要在仪表板中的图表和后端数据之间提供一个接口。您可以使用 Excel 的相机功能来实现此目的。

Excel 数据透视表

当您拥有大型数据集并且希望动态汇总结果以显示分析结果的各个方面时，Excel 数据透视表可以方便地包含在您的仪表板中。您可以使用 Excel 表或更强大的数据模型中的数据表来创建数据透视表。

两种方法之间的主要区别是 -

Excel 表格	数据表
仅来自一张表的数据可用于创建数据透视表。	来自多个表的数据可用于创建数据透视表，定义表之间的关系。
当表中的数量增加时。行数，内存处理和存储不会乐观。	可以通过内存优化和减小文件大小来处理包含数千行数据的庞大数据集。

如果您尝试创建包含多个 Excel 表的数据透视表，系统将提示您创建关系，并且具有该关系的表将添加到数据模型中。

您将在“仪表板的 Excel 数据透视表”一章中了解数据透视表。

如果工作簿的数据模型中有数据，则可以创建跨越多个数据表的数据透视表和 Power 数据透视图。

您将在本章中了解这些内容 - Excel Power PivotTables 和 Power PivotCharts for Dashboards。

具有交互式控件的动态仪表板元素

您可以使仪表板元素与易于使用的控件（例如滚动条、单选按钮、复选框和动态标签）进行交互。您将在“Excel 仪表板中的交互式控件”一章中了解有关这些的更多信息。

滚动条

单选按钮

复选框

Excel 功率数据透视表和功率数据透视图

Excel Power PivotTables 和 Power PivotCharts 通过在工作簿中构建内存优化的数据模型，有助于汇总来自多个资源的数据。数据模型中的数据表可以运行数千个动态数据，从而以更少的精力和时间进行汇总。

您将在章节 - 用于仪表板的 Excel Power PivotTables 和 Power PivotCharts 中了解 Power PivotTables 和 Power PivotCharts 在仪表板中的用法。

Excel数据模型

Excel Power PivotTable 和 Power PivotChart

Excel Power View 报告

Excel Power View 报表提供大型数据集的交互式数据可视化，展现数据模型的强大功能和动态 Power View 可视化的交互性质。

您将在“仪表板的 Excel Power View 报表”一章中了解如何使用 Power View 作为仪表板画布。

电力视图报告

关键绩效指标 (KPI)

关键绩效指标 (KPI) 是许多仪表板不可或缺的一部分。您可以在 Excel 中创建和管理 KPI。您将在“Excel 仪表板中的关键绩效指标”一章中了解 KPI 。

关键绩效指标

Excel 仪表板 - 条件格式

数据可视化的条件格式

如果您选择 Excel 创建仪表板，请尝试使用 Excel 表格（如果它们符合目的）。借助条件格式和迷你图，Excel 表格是仪表板的最佳且简单的选择。

在 Excel 中，您可以使用条件格式进行数据可视化。例如，在包含上一季度按地区销售数据的表中，您可以突出显示前 5% 的值。

您可以通过指定规则来指定任意数量的格式化条件。您可以从突出显示单元格规则或顶部/底部规则中选择符合您条件的 Excel 内置规则。您还可以定义自己的规则。

您可以选择适合您的数据可视化的格式选项 - 数据条、色阶或图标集。

在本章中，您将学习条件格式规则、格式选项以及添加/管理规则。

突出显示单元格

您可以使用突出显示单元格规则为包含满足以下任何条件的数据的单元格分配格式 -

给定数值范围内的数字：大于、小于、介于和等于。
重复或唯一的值。

考虑以下您想要呈现的结果摘要 -

假设您要突出显示超过 1000000 的总金额值。

选择列 – 总金额。
单击“主页”选项卡下“样式”组中的“条件格式”。
单击下拉列表中的突出显示单元格规则。
单击出现的第二个下拉列表中的“大于”。

出现大于对话框。

在“设置大于的单元格格式：”框中，将条件指定为 1000000。
在框中，选择格式选项为绿色填充和深绿色文本。

单击“确定”按钮。

正如您所观察到的，满足指定条件的值会以指定的格式突出显示。

顶部/底部规则

您可以使用顶部/底部规则为满足以下任何条件的值分配格式 -

Top 10 Items - 排名前 N 的单元格，其中 1 <= N <= 1000。
Top 10% - 排名前 n% 的单元格，其中 1 <= n <= 100。
Bottom 10 Items - 排名最后 N 的单元格，其中 1 <= N <= 1000。
Bottom 10% - 排名最后 n% 的单元格，其中 1 <= n <= 100。
高于平均水平- 选定范围内高于平均水平的单元格。
低于平均水平- 所选范围内低于平均水平的单元格。

假设您要突出显示前 5% 的总金额值。

选择列 – 总金额。
单击“主页”选项卡下“样式”组中的“条件格式”。
单击下拉列表中的顶部/底部规则。
单击出现的第二个下拉列表中的前 10%。

出现前 10% 对话框。

在“设置排名靠前的单元格格式：”框中，将条件指定为 5%。
在框中，选择格式选项为绿色填充和深绿色文本。

单击“确定”按钮。前 5% 的值将以指定的格式突出显示。

数据栏

您可以使用彩色数据条来查看相对于其他值的值。数据栏的长度代表值。较长的条形代表较高的值，较短的条形代表较低的值。您可以为数据栏使用纯色或渐变色。

选择列 – 总金额。
单击“主页”选项卡下“样式”组中的“条件格式”。
单击下拉列表中的数据栏。
单击出现的第二个下拉列表中“渐变填充”下的蓝色数据栏。

列中的值将突出显示，并带有蓝色渐变填充条，显示小值、中值和大值。

选择列 – 总金额。
单击“主页”选项卡下“样式”组中的“条件格式”。
单击下拉列表中的数据栏。
在出现的第二个下拉列表中单击“实体填充”下的“橙色数据栏”。

列中的值将突出显示，按条形高度和橙色条显示小值、中值和大值。

假设您想要突出显示与销售目标（例如 800000）相比的销售额。

创建一个值为 [@[Total Amount]]-800000 的列。
选择新列。
单击“主页”选项卡下“样式”组中的“条件格式”。
单击下拉列表中的数据栏。
单击出现的第二个下拉列表中“渐变填充”下的绿色数据栏。

数据条将从每个单元格的中间开始，并在负值时向左延伸，在正值时向右延伸。

正如您所观察到的，向右延伸的条形图为绿色，表示正值，向左延伸的条形图为红色，表示负值。

色阶

您可以使用色阶来查看单元格中的值相对于列中其他单元格中的值的情况。颜色指示每个单元格值落在该范围内的位置。您可以使用 3 色标尺或 2 色标尺。

选择列 – 总金额。
单击“主页”选项卡下“样式”组中的“条件格式”。
单击下拉列表中的色阶。
单击出现的第二个下拉列表中的绿-黄-红色阶。

与突出显示单元格规则的情况一样，色标使用单元格阴影来显示单元格值的差异。正如您在预览中观察到的，该数据集的阴影差异并不明显。

单击第二个下拉列表中的更多规则。

出现新格式规则对话框。

单击“根据选择规则类型”框中的值设置所有单元格的格式。
在编辑规则描述框中，选择以下内容 -
- 在“格式样式”框中选择“3 色标度”。
- 在中点下，对于值 – 输入 75。

单击“确定”按钮。

正如您所观察到的，使用定义的色标，值以明显的阴影表示数据范围。

图标集

您可以使用图标集来可视化数字差异。在 Excel 中，您有一系列图标集 -

图标集类型	图标集
定向
形状
指标
评级

正如您所观察到的，图标集由三到五个符号组成。您可以定义将图标与单元格区域中的值关联的条件。例如，红色向下箭头表示小数字，绿色向上箭头表示大数字，黄色水平箭头表示中间值。

选择列 – 总金额。
单击“主页”选项卡下“样式”组中的“条件格式”。
单击下拉列表中的图标集。
单击出现的第二个下拉列表中方向组中的 3 个箭头（彩色）。

根据值，彩色箭头出现在选定的列中。

使用自定义规则

您可以定义自己的规则并格式化满足特定条件的一系列单元格。

选择列 – 总金额。
单击“主页”选项卡下“样式”组中的“条件格式”。
单击下拉列表中的“新建规则”。

出现新格式规则对话框。

在“选择规则类型”框中单击“使用公式确定要设置格式的单元格”。
在编辑规则描述框中，执行以下操作 -
- 在框中键入公式 - 设置该公式成立的值的格式。例如， = PercentRank.INC($E$3:$E$13,E3)>=0.7
- 单击格式按钮。
- 选择格式。例如，字体 - 粗体和填充 - 橙色。
- 单击“确定”。
检查预览。

如果预览正常，请单击“确定”。数据集中满足公式的值将以您选择的格式突出显示。

管理条件格式规则

您可以使用“条件格式规则管理器”对话框来管理条件格式规则。

单击“开始”选项卡下“样式”组中的“条件格式” 。单击下拉列表中的管理规则。

将出现“条件格式规则管理器”对话框。您可以查看所有现有规则。您可以添加新规则、删除规则和/或编辑规则以对其进行修改。

Excel 仪表板 - Excel 图表

如果您选择图表来直观地显示数据，Excel 图表可以帮助您选择和更改不同的视图。Excel 提供了多种图表类型，使您能够通过仪表板中手头的数据以任何数据集的图形表示来表达您想要传达的消息。

此外，还有一些复杂的图表可用于某些特定目的。其中一些在 Excel 2016 中可用。但是，它们也可以从 Excel 2013 中的内置图表类型构建。

在本章中，您将了解 Excel 中的图表类型以及何时使用每种图表类型。请记住，在仪表板的一张图表中，您应该仅传达一条消息。否则，可能会造成解释上的混乱。您可以调整图表的大小，以便可以在仪表板中容纳更多数量的图表，每个图表都传达一条特定的消息。

除了本章讨论的图表类型之外，还有某些高级图表广泛用于通过视觉提示来描述信息。您将在“仪表板的高级 Excel 图表”一章中了解高级图表类型及其用法。

图表类型

如果您有 Excel 2013，您可以找到以下主要图表类型 -

柱形图

折线图

饼状图

圆环图

条形图

面积图

XY（散点）图表

气泡图

股票图表

表面图

雷达图

要了解这些图表，请参阅教程 - Excel 图表。

组合图表

当您有混合类型的数据时，您可以使用组合（组合）图表来显示它。图表可以仅具有主垂直轴，也可以具有主垂直轴和辅助轴的组合。您将在后面的部分中了解组合图。

选择适当的图表类型

要在仪表板中通过图表显示数据，首先要确定图表的用途。一旦明确了图表要表达的内容，您就可以选择最能描述您的信息的图表类型。

以下是有关选择图表类型的一些建议 -

如果要比较数据值，可以选择条形图、饼图、折线图或散点图。
如果要显示分布情况，可以使用柱形图、散点图或折线图。
如果您想显示一段时间内的趋势，可以使用折线图。
如果您想表示整体的各个部分，可以选择饼图。但是，当您使用饼图时，请记住，使用不同大小的饼图切片只能有效地描绘具有不同数据值的两到三个不同数据点。如果您尝试在饼图中描绘更多数量的数据点，则可能很难进行比较。
如果有以下任何目的，您可以使用散点图 -
- 您想要显示大量数据之间的相似性，而不是数据点之间的差异。
- 您想要比较许多数据点而不考虑时间。散点图中包含的数据越多，进行比较的效果就越好。
Excel 中的推荐图表可帮助您找到适合您的数据的图表类型。

在 Excel 中，您可以创建具有图表类型的图表，并在以后随时轻松修改它。

在表格中使用迷你图显示趋势

迷你图是放置在单个单元格中的微小图表，每个单元格代表您选择的一行数据。它们提供了一种快速查看趋势的方法。在 Excel 中，您可以使用线迷你图、柱迷你图或盈/亏迷你图。

您可以使用快速分析工具将迷你图快速添加到表中。

确定要为其添加迷你图的数据。
在数据右侧保留一个空列并命名该列。迷你图将放置在此列中。
选择数据。

快速分析工具按钮出现在所选数据的右下角。

单击“快速分析”按钮。出现快速分析工具。
单击“火花线”。出现图表选项。

单击线路。将为所选数据中的每一行显示折线图。

单击“列”。将为所选数据中的每一行显示柱形图。

赢/输图表不适合此数据。请考虑以下数据以了解赢/输图表的外观。

使用组合图进行比较

如果数据范围变化很大，您可以使用组合图组合两种或多种图表类型来比较不同类别的数据值。使用辅助轴来描述其他数据范围，图表将更容易快速阅读和掌握信息。

快速微调图表

您可以使用图表右上角旁边的三个按钮快速微调图表。

使用图表元素，您可以在图表中添加或删除轴、轴标题、图例、数据标签、网格线、误差线等。
使用图表样式，您可以通过设置图表样式和颜色的格式来自定义图表的外观。
使用图表过滤器，您可以动态编辑正在显示的图表上可见的数据点（值）和名称。

您可以选择/取消选择图表元素。

您可以设置网格线格式以显示深度轴。

您可以设置图表样式。

您可以为图表选择配色方案。

您可以动态选择要显示的值和名称。
- 值是数据系列和类别。
- 名称是数据系列（列）和类别（行）的名称。

使用美学数据标签

您可以拥有美观且有意义的数据标签。

您可以将数据标签放置在数据点的任何位置。

您可以使用各种选项格式化数据标签，包括效果。

您可以将数据标签更改为任何形状。

数据标签可以有不同的尺寸。您可以调整每个数据标签的大小，以便其中的文本可见。

您可以包含来自数据点的文本或任何数据标签的任何其他文本，以使它们可刷新并因此动态。

您可以使用引导线将数据标签连接到其数据点。

您可以通过移动数据点来将带有指引线的数据标签放置在距数据点任意距离的位置。

您可以设置引导线格式以使其显眼。

您可以选择这些选项中的任何一个，以根据您的数据和要突出显示的内容在图表上显示数据标签。

即使您切换到不同类型的图表，数据标签也会保留在原位。但是，请在格式化任何图表元素（包括数据标签）之前确定图表类型。

在图表中使用趋势线

您可以使用趋势线在图表中描述结果的预测。

在图表中使用形状

您可以在图表中插入不同类型的形状。插入形状后，您可以使用“编辑文本”向其中添加文本。您可以通过更改形状和/或编辑点来编辑形状。

您可以更改形状的样式、选择形状填充颜色、设置形状轮廓格式并向形状添加视觉效果。

使用圆柱体、圆锥体和棱锥体

在 3-D 柱形图中，默认情况下会有方框。

为了使图表在仪表板中更加显眼，您可以选择其他 3D 柱形，例如圆柱体、圆锥体、Pyramid等。您可以在“设置数据系列格式”窗格中选择这些形状。

Pyramid形状的柱子

圆柱体形状的柱子

圆锥形柱

在图表中使用图片

您可以通过使用图片代替列来更加强调数据呈现。

Excel 仪表板 - 交互式控件

如果您要在仪表板上显示更多数据，而单个屏幕无法容纳这些数据，则可以选择使用作为 Excel Visual Basic 一部分提供的 Excel 控件。最常用的控件是滚动条、单选按钮和复选框。通过将这些合并到仪表板中，您可以使其具有交互性，并允许用户通过可能的选择来查看数据的不同方面。

您可以在仪表板中提供交互式控件，例如滚动条、复选框和单选按钮，以方便接收者动态查看显示为结果的数据的不同方面。您可以与收件人一起决定仪表板的特定布局，然后使用相同的布局。Excel 交互式控件易于使用，不需要任何 Excel 专业知识。

Excel 交互式控件将在功能区上的“开发人员”选项卡中提供。

如果您在功能区上找不到“开发者”选项卡，请执行以下操作 -

单击“Excel 选项”框中的“自定义功能区”。
在“自定义功能区”框中选择“主选项卡”。
检查主选项卡列表中的开发人员框。

单击“确定”。您将在功能区上找到“开发人员”选项卡。

仪表板中的滚动条

任何仪表板的特征之一是仪表板中的每个组件都尽可能紧凑。假设您的结果如下 -

如果您可以使用如下所示的滚动条来呈现此表，则浏览数据会更容易。

您还可以在带有滚动条的条形图中拥有动态目标线。当您上下移动滚动条时，目标线也会上下移动，并且那些与目标线交叉的条将突出显示。

在以下部分中，您将了解如何创建滚动条以及如何创建链接到滚动条的动态目标线。您还将学习如何在滚动条中显示动态标签。

创建滚动条

要为表格创建滚动条，请首先将列标题复制到工作表上的空白区域，如下所示。

插入滚动条。
- 单击功能区上的“开发人员”选项卡。
- 单击“控件”组中的“插入”。
- 单击图标下拉列表中“表单控件”下的“滚动条”图标。

将光标移至 I 列，向下拉插入垂直滚动条。
调整滚动条的高度和宽度，使其与表格对齐。

右键单击滚动条。
单击下拉列表中的格式控制。

出现格式控制对话框。

单击“控制”选项卡。
在出现的框中键入以下内容。

单击“确定”按钮。滚动条已准备好使用。您已选择单元格 O2 作为滚动条的单元格链接，当您上下移动滚动条时，该单元格的值为 0 – 36。接下来，您必须使用基于单元格 O2 中的值的引用来创建表中数据的副本。
在单元格 K3 中，输入以下内容 -

= OFFSET(摘要[@[编号]],$O$2,0)。

按 Enter 按钮。填写复制公式的列中的单元格。

填写复制公式的其他列中的单元格。

您的动态可滚动表格已准备好复制到仪表板。

向下移动滚动条。

可以观察到，单元格-滚动条单元格链接中的值发生变化，并且表格中的数据根据该值进行复制。一次显示12行数据。

将滚动条拖至底部。

最后 12 行数据显示为当前值为 36（如单元格 O2 中所示），36 是您在“表单控制”对话框中设置的最大值。

您可以根据您的需求更改动态表格的相对位置、更改一次显示的行数、单元格链接到滚动条等。正如您在上面看到的，这些需要在“格式控制”对话框中进行设置。

创建动态且交互式的目标线

假设您要显示过去 6 个月内按地区划分的销售情况。您还为每个月设定了目标。

您可以执行以下操作 -

创建一个柱形图来显示所有这些信息。
创建跨列的目标线。
使目标线与滚动条交互。
使目标线动态设置数据中的目标值。
突出显示满足目标的值。

创建一个显示所有这些信息的柱形图

选择数据。插入聚集柱形图。

创建跨列的目标线

将图表类型更改为组合。对于目标系列，选择图表类型为“折线”；对于系列的其余部分，选择“簇状柱形图”。

为目标线创建基表。稍后您将使其动态化。

将目标行的数据系列值更改为上表中的目标列。

单击“确定”按钮。

更改簇列的颜色方案。将目标线更改为绿色虚线。

使目标线与滚动条交互

插入滚动条并将其放置在图表下方，并将其大小设置为从一月到六月。
在“格式控制”对话框中输入滚动条参数。

创建一个包含两列的表 - 月份和目标。
根据数据表和滚动条单元格链接输入值。

该表根据滚动条位置显示月份和相应的目标。

使目标线动态设置数据中的目标值

现在，您已准备好让您的目标线变得动态。

通过在所有行中键入 = $G$12 来更改为目标行创建的基表中的目标列值。

如您所知，单元格 G12 动态显示目标值。

正如您所观察到的，目标线根据滚动条移动。

突出显示符合目标的值

这是最后一步。您希望突出显示在任何时间点满足目标的价值观。

将列添加到数据表的右侧 - 东结果、北结果、南结果和西结果。
在单元格 H3 中，输入以下公式 -

= IF(D3 >= $G$12,D3,NA())

将公式复制到表中的其他单元格。调整表格大小。

正如您所观察到的，列中的值 - East-Results、North-Results、SouthResults 和 West-Results 根据滚动条（即目标值）动态变化。显示大于或等于目标的值，其他值只是#N/A。

更改图表数据范围以包括数据表中新添加的列。
单击更改图表类型。
使目标系列为线，其余为簇状列。
对于新添加的数据系列，选择次要轴。
设置数据系列的格式，使系列 East、North、South 和 West 的填充颜色为橙色，系列 East-Results、North-Results、South-Results 和 WestResults 的填充颜色为绿色。
输入目标行的数据标签，并通过对动态数据表中月份值的单元格引用使其动态化。

带有动态目标线的图表已准备好包含在仪表板中。

您可以清除辅助轴，因为它不是必需的。当您移动滚动条时，目标线也会移动，条形图也会相应地突出显示。目标线还将有一个显示月份的标签。

Excel 选项（单选）按钮

单选按钮通常用于从给定的一组选项中选择一个选项。它始终由一个小圆圈表示，选择时其中会有一个点。当您有一组单选按钮时，您只能选择其中一个。

在 Excel 中，单选按钮称为选项按钮。

您可以使用图表中的 Excel 选项按钮来选择读者想要查看的数据细节。例如，在上一节的示例中，您创建了一个滚动条来获取具有基于月份的目标值的动态目标线。您可以使用选项按钮选择月份，从而选择目标值，并使目标线基于目标值。以下是步骤 -

创建一个柱形图来显示所有这些信息。
创建跨列的目标线。
使目标线与选项按钮交互。
使目标线动态设置数据中的目标值。
突出显示满足目标的值。

步骤 1 和 2 与之前的情况相同。在第二步结束时，您将得到以下图表。

使目标线与选项按钮交互

插入选项按钮。
- 单击功能区上的“开发人员”选项卡。
- 单击“控件”组中的“插入”。
- 单击图标下拉列表中“表单控件”下的“选项按钮”图标。

将其放置在图表的右上角。

右键单击选项按钮。单击下拉列表中的格式控制选项。

在“设置对象格式”对话框的“控制”选项卡下输入选项按钮参数。

单元格 F10 链接到选项按钮。垂直复制 5 个选项按钮。

正如您所观察到的，所有选项按钮都具有相同的名称，称为标题名称。但是，在 Excel 内部，这些选项按钮有不同的名称，您可以在“名称”框中查看。此外，由于选项按钮 1 设置为链接到单元格 F10，因此所有副本也引用同一单元格。

单击任意选项按钮。

正如您所观察到的，链接单元格中的数字更改为选项按钮的序列号。将选项按钮重命名为一月、二月、三月、四月、五月和六月。

创建一个包含两列的表 - 月份和目标。根据数据表和滚动条单元格链接输入值。

该表根据所选选项按钮显示月份和相应的目标。

使目标线动态设置数据中的目标值

现在，您已准备好让您的目标线变得动态。

通过在所有行中键入 = $G$12 来更改为目标行创建的基表中的目标列值。

如您所知，单元格 G12 动态显示目标值。

正如您所观察到的，目标线是根据所选的选项按钮显示的。

突出显示符合目标的值

这是最后一步。您希望突出显示在任何时间点满足目标的价值观。

将列添加到数据表的右侧 - 东结果、北结果、南结果和西结果。
在单元格 H3 中，输入以下公式 -

= IF(D3 >= $G$12,D3,NA())

将公式复制到表中的其他单元格。调整表格大小。

更改图表数据范围以包括数据表中新添加的列。
单击更改图表类型。
使目标系列为线，其余为簇状列。
对于新添加的数据系列，选择次要轴。
设置数据系列的格式，使系列 East、North、South 和 West 的填充颜色为橙色，系列 East-Results、North-Results、South-Results 和 WestResults 的填充颜色为绿色。