Excel Dashboards 简明教程

Excel Dashboards - Interactive Controls

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

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

Excel 交互式控件将在功能区的 DEVELOPER 选项卡中可用。

developer tab

如果您在功能区上找不到 DEVELOPER 选项卡,请执行以下操作:

  1. 在 Excel 选项框中单击“自定义功能区”。

  2. 在“自定义功能区”框中选择“主选项卡”。

  3. 选中“主选项卡”列表中的“开发人员”框。

developer tab ribbon
  1. 单击“确定”。您将在功能区上找到 DEVELOPER 选项卡。

Scroll Bars in Dashboards

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

compact

如果您能使用滚动条(如下所示)显示此表,将更容易浏览数据。

scroll data

您还可以在带有滚动条的条形图中设置动态目标线。随着您向上或向下移动滚动条,目标线也会上下移动,并且那些穿过目标线的条形图将被突出显示。

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

Creating a Scrollbar

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

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

insert scrollbar
  1. 将光标移到列 I 并下拉以插入一个垂直滚动条。

  2. 调整滚动条的高度和宽度,使其与表格对齐。

adjust scrollbar
  1. 右键单击滚动条。

  2. 在下拉列表中,单击“格式化控件”。

format control

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

  1. 单击“控件”选项卡。

  2. 在出现的框中,键入以下内容:

format control dialog
  1. 单击“确定”按钮。滚动条已准备好使用。您已选择单元格 O2 作为滚动条的单元格链接,当您上下移动滚动条时,该单元格中数值将取 0 – 36 间的数值。接下来,您需要根据单元格 O2 中的值,创建数据表的副本,并设为引用。

  2. 在单元格 K3 中,键入以下内容:−= OFFSET(Summary[@[S. No.]],$O$2,0)。

cell link
  1. 按 Enter 键。使用该公式填充该列的单元格。

column copying
  1. 使用该公式填充其他列的单元格。

formula copying

您可以复制动态可滚动数据表,将其放到您的仪表板中了。

dynamic scrollable
  1. 向下移动滚动条。

move scrollbar

如您所见,- 滚动条单元格链接中的单元格值发生变化,并且数据表中的数据以此值为依据进行了复制。一次,会显示 12 行数据。

  1. 将滚动条拖动到最下方。

drag scrollbar

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

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

Creating a Dynamic and Interactive Target Line

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

targetline

您可以执行以下操作:

  1. 创建显示所有这些信息的柱状图。

  2. 在多列上创建目标线。

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

  4. 通过设置数据中的目标值使目标线动态。

  5. 突出显示满足目标的值。

Create a column chart showing all this information

选择数据。插入组合柱形图。

clustered column

Create a Target Line across the columns

将图表类型更改为组合。为目标序列选择折线图图表类型,为其余序列选择组合柱形图。

change chart

为目标线创建一个基本表。稍后你会使它变得动态。

base table

将目标线的系列值更改为上表中的目标列。

edit series

单击确定按钮。

clustered color scheme

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

target green line

Make the Target Line interactive with a scroll bar

  1. 插入滚动条,将其放置在图表下方,并将其大小调整为从 1 月到 6 月。

  2. 在“格式控制”对话框中输入滚动条参数。

targetline interactive
  1. 创建一个有两列的表格 - 月份和目标。

  2. 根据数据表和滚动条单元格链接输入值。

month target

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

scrollbar month target

Make the Target Line dynamic setting the target values in your data

现在,你准备使目标线动态化。

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

如你所知,单元格 G12 动态显示目标值。

targetline setting

如你所见,目标线根据滚动条移动。

Highlight values that are meeting the target

这是最后一步。此时的你想突出显示满足目标的值。

  1. 在数据表格的右侧添加列-东结果、北结果、南结果和西结果。

  2. 在单元格 H3 中,输入以下公式:− = IF (D3>=$G$12, D3, NA ())

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

resize table

您也可以观察到,列中的值-东结果、北结果、南结果和西结果会根据滚动条(即目标值)动态变化。大于或等于目标的值将显示,而其他值仅显示为#N/A。

  1. 更改图表数据范围以包含数据表中新添加的列。

  2. 单击更改图表类型。

  3. 使目标系列成为折线,其他系列成为簇状柱形图。

  4. 对于新添加的数据系列,选择辅助轴。

  5. 格式化数据系列,以便系列东、北、南和西具有填充色橙色,系列东结果、北结果、南结果和西结果具有填充色绿色。

  6. 为目标线输入数据标签,并使其通过引用动态数据表中的月份值来实现动态化。

dynamic table

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

dynamic targetline

您可以清除辅助轴,因为它不是必需的。当您移动滚动条时,目标线将移动,并且条将相应突出显示。目标线还将带有显示月份的标签。

targetline moved

Excel Option (Radio) Buttons

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

radio button option

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

您可以在图表中使用 Excel 选项按钮来选择读者希望查看的数据细节。例如,在上一个部分中的示例中,您创建了一个滚动条,以根据月份获得具有目标值的目标动态线。您可以使用选项按钮选择月份及目标值,并根据目标值建立目标线。以下是步骤:

  1. 创建显示所有这些信息的柱状图。

  2. 在多列上创建目标线。

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

  4. 通过设置数据中的目标值使目标线动态。

  5. 突出显示满足目标的值。

步骤 1 和 2 与前一个案例中相同。在完成第二步后,您将具有以下图表。

target green line

Make the Target Line interactive with Option Buttons

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

interactive option

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

right corner

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

right click format control

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

format object

单元格 F10 与“选项按钮”相链接。垂直复制“选项按钮”5次。

option vertically

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

单击任意“选项按钮”。

linked cell

如您所见,链接单元格中的数字更改为“选项按钮”的序号。将“选项按钮”重命名为“1 月”、“2 月”、“3 月”、“4 月”、“5 月”和“6 月”。

rename option

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

enter values

此表格显示基于所选“选项按钮”的月份和相应的目标值。

selected option

Make the Target Line dynamic setting the target values in your data

现在,你准备使目标线动态化。

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

如你所知,单元格 G12 动态显示目标值。

selcted targetline

如您所见,“目标值线”基于所选“选项按钮”显示。

Highlight values that are meeting the target

这是最后一步。此时的你想突出显示满足目标的值。

  1. 在数据表格的右侧添加列-东结果、北结果、南结果和西结果。

  2. 在单元格 H3 中,输入以下公式:− = IF (D3>=$G$12, D3, NA ())

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

resize cells

如您所见,列“东部-结果值”、“北部-结果值”、“南部-结果值”和“西部-结果值”中的值根据滚动条(即目标值)动态更改。大于或等于“目标值”的值会显示,其他值只是“#N/A”。

  1. 更改图表数据范围以包含数据表中新添加的列。

  2. 单击更改图表类型。

  3. 使目标系列成为折线,其他系列成为簇状柱形图。

  4. 对于新添加的数据系列,选择辅助轴。

  5. 格式化数据系列,以便系列东、北、南和西具有填充色橙色,系列东结果、北结果、南结果和西结果具有填充色绿色。

format data series
  1. 使用单元格 $G$12 中的值向“目标值线”添加动态数据标签。

  2. 清除辅助轴线,因为它不需要。

  3. 在功能区的“视图”选项卡下,取消选中“网格线”框。

  4. 在“格式化坐标轴”选项中将“标签”选项更改为“高”。这将把纵轴标签向右移动,使“目标值线”数据标签显眼。

包含动态“目标值线”和“选项按钮”的图表已可以包含在仪表盘中。

inclusion dashboard

当您选择一个“选项按钮”时,“目标值线”将根据所选月份的目标值显示,并且条形也会相应高亮显示。“目标值线”还将有一个数据标签,显示目标值。

target value

Excel Checkboxes

复选框通常用于从给定的一组选项中选择一个或多个选项。复选框总是由小方块描绘,选中时会有一个打勾标记。当您有一组复选框时,可以选择任意数量的复选框。例如,

checkboxes selected

您可以在图表中使用 Excel 复选框来选择读者希望查看的数据详情。例如,在上一个部分的示例中,您创建了一个柱状图,该柱状图显示了 4 个区域(东部、北部、南部和西部)的数据。您可以使用复选框来选择要显示其数据的区域。一次可以选择任意数量的区域。

您可以从上一个部分的最后一步开始 -

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

insert checkbox
  1. 把它放在图表左上角。

  2. 将复选框的名称更改为 East。

change name
  1. 右键单击复选框。在下拉列表中单击“格式控制”。

  2. 在格式控制对话框的“控制”选项卡下输入复选框参数。

checkbox parameter
  1. 单击“确定”按钮。你会观察到,如果你选中复选框,会在关联的单元格 C19 中显示 TRUE;如果你取消选中复选框,会显示 FALSE。

  2. 复制复选框并水平粘贴 3 次。

  3. 将名称更改为 North、South 和 West。

checkbox horizontal

如你所见,当你复制一个复选框时,关联的单元格对于所复制的复选框来说保持不变。但是,由于复选框可以进行多选,你需要让关联的单元格不同。

  1. 将 North、South 和 West 的关联单元格分别更改为 $C$20、$C$21 和 $C$22。

multiple selections

下一步是在图表中仅显示所选区域的数据。

  1. 按以下方式创建表结构 −

table structure
  1. 在单元格 C21 中输入 =IF($C$19,H3,NA())。

  2. 在单元格 D21 中输入 =IF($D$19,I3,NA())。

  3. 在单元格 E21 中输入 =IF($E$19,J3,NA())。

  4. 在单元格 F21 中输入 =IF($F$19,K3,NA())。

  5. 填写表格中的其他行。

other rows
  1. Add the Target column.

  2. 将图表数据更改为这个表格。

chart data

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

chart display