Excel 仪表盘 - 交互式控件

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

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

Excel 交互式控件将在功能区中的“开发工具”选项卡中可用。

如果您在功能区中找不到“开发工具”选项卡，请执行以下操作：

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

单击“确定”。您将在功能区中找到“开发工具”选项卡。

仪表盘中的滚动条

任何仪表盘的一个特点是仪表盘中的每个组件都尽可能紧凑。假设您的结果如下所示：

如果您能像下面这样使用滚动条来呈现此表，则浏览数据会更容易。

您还可以使用滚动条在条形图中创建动态目标线。当您上下移动滚动条时，目标线会上下移动，并且那些与目标线相交的条形将被突出显示。

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

创建滚动条

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

插入滚动条。
- 单击功能区上的“开发工具”选项卡。
- 单击“控件”组中的“插入”。
- 在图标下拉列表中，单击“窗体控件”下的“滚动条”图标。

将光标移到 I 列，然后向下拖动以插入垂直滚动条。
调整滚动条的高度和宽度，并将其与表格对齐。

右键单击滚动条。
在下拉列表中单击“设置控件格式”。

出现“设置控件格式”对话框。

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

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

=OFFSET(Summary[@[S. No.]],$O$2,0)。

按 Enter 键。复制公式填充列中的单元格。

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

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

向下移动滚动条。

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

将滚动条拖动到底部。

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

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

创建动态交互式目标线

假设您想显示过去 6 个月的按销售区域划分的销售额。您还为每个月设定了目标。

您可以执行以下操作：

创建一个显示所有这些信息的柱状图。
在列上创建目标线。
使用滚动条使目标线具有交互性。
通过在数据中设置目标值来使目标线动态化。
突出显示达到目标的值。

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

选择数据。插入一个簇状柱状图。

在列上创建目标线

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

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

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

单击“确定”按钮。

更改簇状柱形的配色方案。将目标线更改为绿色虚线。

使用滚动条使目标线具有交互性

插入一个滚动条，将其放置在图表下方，并调整其大小使其跨越 1 月到 6 月。
在“设置控件格式”对话框中输入滚动条参数。

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

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

通过在数据中设置目标值来使目标线动态化

现在，您可以使目标线动态化。

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

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

您可以观察到，目标线会根据滚动条移动。

突出显示达到目标的值

这是最后一步。您想在任何时候都突出显示达到目标的值。

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

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

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

您可以观察到，列中的值 - 东部结果、北部结果、南部结果和西部结果会根据滚动条（即目标值）动态变化。大于或等于目标的值将显示，其他值只是 #N/A。

更改图表数据区域以包含数据表中新添加的列。
单击“更改图表类型”。
使目标系列为折线，其余为簇状柱形图。
对于新添加的数据系列，选择“次坐标轴”。
以东部、北部、南部和西部系列填充橙色，而东部结果、北部结果、南部结果和西部结果系列填充绿色来设置数据系列格式。
输入目标线的数

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

您可以清除次坐标轴，因为它不需要。当您移动滚动条时，目标线会移动，并且条形将相应地突出显示。目标线还将显示月份的标签。

Excel 选项（单选）按钮

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

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

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

创建一个显示所有这些信息的柱状图。
在列上创建目标线。
使用选项按钮使目标线具有交互性。
通过在数据中设置目标值来使目标线动态化。
突出显示达到目标的值。

步骤 1 和 2 与前面的情况相同。在完成第二步后，您将拥有以下图表。

使用选项按钮使目标线具有交互性

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

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

右键单击选项按钮。在下拉列表中单击“设置控件格式”选项。

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

单元格 F10 与选项按钮链接。垂直制作 5 个选项按钮的副本。

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

单击任何选项按钮。

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

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

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

通过在数据中设置目标值来使目标线动态化

现在，您可以使目标线动态化。

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

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

您可以观察到，目标线是根据选定的选项按钮显示的。

突出显示达到目标的值

这是最后一步。您想在任何时候都突出显示达到目标的值。

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

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

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

正如您所看到的，“东部结果”、“北部结果”、“南部结果”和“西部结果”列中的值会根据滚动条（即目标值）动态变化。大于或等于目标值的数值将显示，其他值则显示为 #N/A。

更改图表数据区域以包含数据表中新添加的列。
单击“更改图表类型”。
使目标系列为折线，其余为簇状柱形图。
对于新添加的数据系列，选择“次坐标轴”。
以东部、北部、南部和西部系列填充橙色，而东部结果、北部结果、南部结果和西部结果系列填充绿色来设置数据系列格式。

在目标线上添加一个动态数据标签，其值来自单元格 $G$12。
清除次坐标轴，因为它不需要。
在功能区上的“视图”选项卡下，取消选中“网格线”复选框。
在“设置坐标轴格式”选项中，将标签选项更改为“高”。这会将纵坐标轴标签移到右侧，使您的目标线数据标签更醒目。

您的图表包含动态目标线和选项按钮，已准备好添加到仪表板中。

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

Excel 复选框

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

您可以在图表中使用 Excel 复选框来选择读者想要查看的数据细节。例如，在上一节的示例中，您创建了一个柱状图，显示了四个区域（东、北、南、西）的数据。您可以使用复选框来选择要显示数据的区域。您可以一次选择任意数量的区域。

您可以从上一节的最后一步开始：

插入复选框。
- 单击功能区上的“开发工具”选项卡。
- 单击“控件”组中的“插入”。
- 单击图标下拉列表中“表单控件”下的“复选框”图标。

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

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

单击“确定”按钮。您可以观察到，如果您选中复选框，则链接单元格 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())。
填写表格中的其他行。

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

图表显示了所选区域中大于所选月份目标值的数据。

打印页面