
- Excel 仪表板教程
- Excel 仪表板 - 主页
- 介绍
- Excel 功能创建仪表板
- 条件格式
- Excel 图表
- 互动控制
- 高级 Excel 图表
- Excel 数据透视表
- 电源数据透视表和数据透视图
- 电源视图报告
- 关键绩效指标
- 构建仪表板
- 例子
- Excel 仪表板有用的资源
- Excel 仪表板 - 快速指南
- Excel 仪表板 - 资源
- Excel 仪表板 - 讨论
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-Results、North-Results、SouthResults 和 West-Results 根据滚动条(即目标值)动态变化。显示大于或等于目标的值,其他值只是#N/A。
更改图表数据范围以包括数据表中新添加的列。
单击更改图表类型。
使目标系列为线,其余为簇状列。
对于新添加的数据系列,选择次要轴。
设置数据系列的格式,使系列 East、North、South 和 West 的填充颜色为橙色,系列 East-Results、North-Results、South-Results 和 WestResults 的填充颜色为绿色。

使用单元格 $G$12 中的值将动态数据标签添加到目标行。
清除辅助轴,因为不需要。
在功能区的“查看”选项卡下,取消选中“网格线”框。
在“格式轴”选项中将“标签”选项更改为“高”。这会将垂直轴标签向右移动,使您的目标线数据标签显眼。
带有动态目标线和选项按钮的图表已准备好包含在仪表板中。

当您选择选项按钮时,目标线将根据所选月份的目标值显示,并且条形图将相应突出显示。目标线还将有一个显示目标值的数据标签。

Excel 复选框
复选框通常用于从一组给定选项中选择一个或多个选项。复选框始终由小方块表示,选中时会有一个勾号。当您有一组复选框时,可以选择任意数量的复选框。例如,

您可以使用图表中的 Excel 复选框来选择读者想要查看的数据细节。例如,在上一节的示例中,您创建了显示 4 个区域(东部、北部、南部和西部)的数据的柱形图。您可以使用复选框来选择显示数据的区域。您可以一次选择任意数量的区域。
您可以从上一节的最后一步开始 -
插入一个复选框。
单击功能区上的“开发人员”选项卡。
单击“控件”组中的“插入”。
单击图标下拉列表中“表单控件”下的“复选框”图标。

将其放置在图表的左上角。
将复选框的名称更改为 East。

右键单击该复选框。单击下拉列表中的格式控制。
在“格式控制”对话框的“控制”选项卡下输入复选框参数。

单击“确定”按钮。您可以观察到,在链接的单元格 C19 中,如果选中该复选框,将显示 TRUE;如果取消选中该复选框,将显示 FALSE。
复制复选框并水平粘贴 3 次。
将名称更改为北、南和西。

正如您所观察到的,当您复制复选框时,复制的复选框的链接单元格也保持不变。但是,由于复选框可以有多个选择,因此您需要使链接的单元格不同。
将北、南和西的链接单元格分别更改为 $C$20、$C$21 和 $C$22。

下一步是在图表中仅包含所选区域的数据。
创建表结构如下 -

- 在单元格 C21 中输入 = IF($C$19,H3,NA())。
- 在单元格 D21 中输入 = IF($D$19,I3,NA())。
- 在单元格 E21 中输入 = IF($E$19,J3,NA())。
- 在单元格 F21 中键入 = IF($F$19,K3,NA())。
- 填写表中的其他行。

添加目标列。
将图表数据更改为此表。

图表显示所选区域的数据大于为所选月份设置的目标值。
