Excel 仪表板 - 交互式控件 - Gingerdoc 姜知笔记

笔记首页 >> excel_dashboards >> Excel 仪表板 – 交互式控件

Excel 仪表板 – 交互式控件

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

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

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

开发者标签

如果在功能区上没有找到“开发人员”选项卡，请执行以下操作 –

单击 Excel 选项框中的自定义功能区。
在自定义功能区框中选择主选项卡。
选中 Main Tabs 列表中的 Developer 框。

开发人员选项卡功能区

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

仪表盘中的滚动条

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

袖珍的

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

滚动数据

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

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

创建滚动条

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

创建滚动条

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

插入滚动条

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

调整滚动条

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

格式控制

出现格式控制对话框。

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

格式控制对话框

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

= 偏移量（摘要 [@[S. No.]],$O$2,0）。

细胞链接

点击回车键。填写复制公式的列中的单元格。

列复制

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

公式复制

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

动态可滚动

向下移动滚动条。

移动滚动条

可以看到，单元格-滚动条单元格链接中的值发生了变化，表格中的数据就是根据这个值进行复制的。一次显示 12 行数据。

将滚动条拖到底部。

拖动滚动条

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

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

创建动态和交互式目标线

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

目标线

您可以执行以下操作 –

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

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

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

簇状列

创建跨列的目标线

将图表类型更改为组合。为目标系列选择图表类型为线，为系列的其余部分选择聚集列。

变化图表

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

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

编辑系列

单击确定按钮。

集群配色方案

更改聚集列的配色方案。将目标线更改为绿色虚线。

目标绿线

使目标线与滚动条交互

插入滚动条并将其放置在图表下方，并将其调整大小以跨越 1 月至 6 月。
在格式控制对话框中输入滚动条参数。

目标线互动

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

月目标

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

滚动条月份目标

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

现在，您可以设置动态目标线。

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

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

目标线设定

如您所见，目标线根据滚动条移动。

突出显示符合目标的值

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

在数据表的右侧添加列 – East-Results、North-Results、SouthResults 和 West-Results。
在单元格 H3 中，输入以下公式 –

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

突出显示

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

调整表格大小

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

更改图表数据范围以在数据表中包括新添加的列。
单击更改图表类型。
使目标系列为 Line，其余的 Clustered Column。
对于新添加的数据系列，选择 Secondary Axis。
以这样的方式格式化数据系列，即 East、North、South 和 West 系列填充颜色为橙色，而 East-Results、North-Results、South-Results 和 WestResults 系列填充颜色为绿色。
为目标行输入数据标签，并使用对动态数据表中月份值的单元格引用使其动态化。

动态表

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

动态目标线

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

目标线已移动

Excel 选项（单选）按钮

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

单选按钮选项

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

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

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

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

目标绿线

使目标线与选项按钮交互

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

互动选项

把它放在图表的右上角。

右上角

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

右键单击格式控件

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

格式对象

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

垂直选项

如您所见，所有选项按钮都具有相同的名称，称为字幕名称。但是，在 Excel 内部，这些选项按钮会有不同的名称，您可以在名称框中查看。此外，由于选项按钮 1 被设置为链接到单元格 F10，所有副本也指向同一个单元格。

单击任何选项按钮。

链接单元格

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

重命名选项

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

输入值

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

选择的选项

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

现在，您可以设置动态目标线。

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

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

选定的目标线

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

突出显示符合目标的值

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

在数据表的右侧添加列 – East-Results、North-Results、SouthResults 和 West-Results。
在单元格 H3 中，输入以下公式 –

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

突出显示

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

调整单元格大小

更改图表数据范围以在数据表中包括新添加的列。
单击更改图表类型。
使目标系列为 Line，其余的 Clustered Column。
对于新添加的数据系列，选择 Secondary Axis。
以这样的方式格式化数据系列，即 East、North、South 和 West 系列填充颜色为橙色，而 East-Results、North-Results、South-Results 和 WestResults 系列填充颜色为绿色。