Excel Dashboards 简明教程

Excel Dashboards - Quick Guide

Excel Dashboards - Introduction

对于那些对仪表盘很陌生的人来说,最好先了解仪表盘。在本章中,你将了解到仪表盘的定义、它的名称由来、他们在 IT 领域变得流行的原因、关键指标、仪表盘的优势、仪表盘的类型、仪表盘数据及格式和仪表盘上的实时数据。

在信息技术中,仪表盘是一种易于阅读的、经常为单页的实时用户界面,以图形化方式展示组织或部门的关键绩效指标的当前状态(快照)和历史趋势,以便能够一目了然地做出即时明智的决策。

digital dashboard

仪表盘的名称源自 automobile dashboards 。在你的车盖下,可能有数百个会影响车辆性能的流程。你的仪表盘使用可视化来汇总这些事件,以便你能够安心地集中在安全驾驶上。以类似的方式,业务仪表盘被用来轻松查看和/或监控组织的性能。

digital dashboards 的理念源自 20 世纪 70 年代对决策支持系统研究。业务仪表盘最初开发于 20 世纪 80 年代,但由于数据刷新和处理方面的问题,它们被搁置了。20 世纪 90 年代,信息时代加速发展,数据仓库和在线分析处理(OLAP)使得仪表盘能够充分发挥功能。然而,直至关键绩效指标 (KPI) 的兴起和罗伯特·S·卡普兰和戴维·P·诺顿的平衡计分卡的引入,仪表盘的使用才变得流行起来。如今,仪表盘的使用已成为决策制定的一个重要组成部分。

big data

在当今的商业环境中,趋势是朝着大数据发展。管理和从所有数据中提取真正价值是现代商业成功的关键。设计精良的仪表盘是一个非凡的信息管理工具。

Dashboard – Definition

Stephen Few 将仪表盘定义为“在单个电脑屏幕上完全展示的、实现一个或多个目标所需的各类重要信息的视觉显示形式,这样,便能够一目了然地对其进行监控”。

从当前意义来看,仪表盘可以定义为一个数据可视化工具,它显示当前指标状态和关键绩效指标 (KPI),简化复杂数据集,以便用户能够一目了然地识别当前表现。

仪表盘在一块屏幕上合并并排列数字和指标。可以为一个特定角色量身定制仪表盘并显示一个部门或整个组织的指标。

仪表盘可以为一次性查看而静态显示,也可以动态显示屏幕后面数据变更的合并结果。还可以使仪表盘具有交互性,从而在一块屏幕上显示大数据的各个细分。

Key Metrics for Dashboard

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

此外,仪表盘的关键指标还取决于接收者(受众)的角色。例如,高管(CEO、CIO 等)、运营经理、销售主管、销售经理等。这是由于仪表盘的主要目标是实现数据可视化,以便制定决策。

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

Dashboard Benefits

仪表盘允许管理者监控组织中各个部门的贡献。为了监控组织的整体绩效,仪表盘允许您从组织的每个部门获取和报告特定数据点,提供了当前绩效的快照以及与之前绩效的对比。

仪表盘的好处包括以下内容−

  1. 绩效指标的视觉呈现。

  2. 识别和纠正负面趋势的能力。

  3. Measurement of efficiencies/inefficiencies.

  4. 生成显示新趋势的详细报告的能力。

  5. 基于收集的数据做出更明智决策的能力。

  6. 策略和组织目标的一致性。

  7. 所有系统的瞬时可见性。

  8. 快速识别数据异常值和相关性。

  9. 与运行多个报告相比,借助全面的数据可视化节省时间。

Types of Dashboards

仪表盘可根据其效用分类如下−

  1. Strategic Dashboards

  2. Analytical Dashboards

  3. Operational Dashboards

  4. Informational Dashboards

Strategic Dashboards

战略仪表盘支持组织中任何层级的管理者做出决策。它们提供数据的快照,显示业务的健康状况和机遇,关注于高水平的绩效指标和预测。

  1. 战略仪表盘需要定期和静态的数据快照(例如,每日、每周、每月、每季度和每年)。它们不必从一刻到下一刻持续变化,并且需要在指定的时间间隔进行更新。

  2. 它们只描述高层次数据,不一定给出细节。

  3. 在有大数据集的情况下,它们可以通过点击按钮进行交互以方便比较和不同的视图。但是,没有必要在这些仪表盘中提供更多交互式功能。

以下屏幕截图显示了管理人员仪表盘示例,显示目标和进度。

strategic

Analytical Dashboards

分析仪表盘包括更多上下文、比较和历史。它们关注分析所需的数据的各个方面。

分析仪表盘通常支持与数据交互,例如深入了解底层详细信息,因此应该具有交互性。

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

analytical

Operational Dashboards

操作仪表盘用于持续监测操作。它们通常与战略或分析仪表盘的设计不同,并且专注于监测不断变化且可能需要随时关注和响应的活动与事件。因此,操作仪表盘需要随时获得实时和更新的数据,因此应具有动态性。

操作仪表盘示例可以是 support-system dashboard ,显示要求主管对高优先级工单立即采取行动的服务工单的实时数据。

operational

Informational Dashboards

信息仪表盘只用于显示数字、事实和/或统计信息。它们可能是静态的,也可能是动态的,带有实时数据但不具有交互性。例如,机场的航班进港/离港信息仪表盘。

informational

Dashboard Data and Formats

仪表盘所需的数据取决于其类别。数据的前提是它应当与该类别相关、无错误、最新,且在需要时应当是实时的。数据可能来自各种不同的来源和格式(电子表格、文本文件、网页、组织数据库等)。

仪表盘上显示的结果必须真实、正确且恰当。这一点至关重要,因为仪表盘上的信息将导致决策、操作和/或推论。因此,除了显示的数据外,所选的显示媒体同样重要,因为它不应在数据描述中产生错误的印象。重点应放在能够明确展示结论的数据可视化的能力上。

Live Data on Dashboards

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

因此,仪表盘已成为从高级管理人员到普通用户的首选媒体。

Excel Features to Create Dashboards

你可以使用各种功能在 Excel 中创建仪表板,这些功能有助于突出数据可视化,这是任何仪表板的主要特征。你可以使用条件格式显示数据表以突出显示好成绩和差成绩,可以总结图表和数据透视表中的数据,可以添加交互式控件,并且可以定义和管理 KPI 等。

在本章中,你将了解在创建仪表板时派上用场的最重要的 Excel 功能。这些功能有助于你获得仪表板元素,这些元素可以简化复杂数据并提供当前状态或实时性能的视觉效果。

Excel Tables

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

Excel 表非常适合将数据导入到工作簿中,在该工作簿中,你希望创建仪表板。有几种方法可以通过建立与各种数据源的连接来将数据导入 Excel。这样就可以在源数据更新时刷新工作簿中的数据。

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

你可以对数据分析进行总结,并用作仪表板一部分的 Excel 表中描绘相同的结论。

analysis summary

Sparklines

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

sparkline

Conditional Formatting

条件格式是突出显示表中数据的一项重要资产。你可以定义规则,通过这些规则你可以更改色标、数据条和/或图标集。你可以使用 Excel 定义的规则,也可以根据适用于你的数据的适用性创建自己的规则。

conditional color scale
conditional data bars
conditional icon set

你将在第 Conditional Formatting for Data Visualization 章学习这些条件格式技术。

Excel Charts

Excel 图表是仪表板最广泛使用的数据可视化组件。你可以让受众以惊人的方式查看数据模式、比较和趋势,任何大小的数据集,并添加颜色和样式。

如果使用 Excel 2013,Excel 具有多种内置图表类型,如折线图、条形图、柱形图、散点图、气泡图、饼图、甜甜圈图、面积图、股票图、曲面图和雷达图。

column chart
line chart
stacked line
pie chart
bar chart
area chart
scatter chart
bubble chart
stock chart
surface contour chart
radar chart

在章节中,您将了解如何有效地使用这些图表及其图表元素。

除了上面提到的图表类型,还有一些其他用处很广的图表类型,它们在展示某些数据类型时很实用。这些图表包括瀑布图、带状图、甘特图、温度计图、直方图、帕累托图、漏斗图、箱线图和华夫饼图。

gauge chart
histogram chart
waterfall chart

您将在章节 - 高级 Excel 仪表盘图表中了解这些图表。

Excel Camera

创建图表后,您需要将它们放置在仪表盘中。如果您想使仪表盘具有动态性,即在源数据每次发生变化时刷新数据(大多数仪表盘都是这种情况),您需要在仪表盘中的图表与后端数据之间提供一个界面。您可以使用 Excel 的相机功能实现这一点。

Excel PivotTables

当您有较大的数据集并且您想以动态方式总结结果,从而显示分析结果的各个方面时,Excel 数据透视表可以方便地包含在您的仪表盘中。您可以使用 Excel 表格或数据模型中功能更强大的数据表来创建数据透视表。

这两种方法之间的主要区别在于:

Excel Tables

Data Tables

只能使用一个表格中的数据来创建数据透视表。

可以使用来自多个表格的数据来创建数据透视表,并定义表格之间的关系。

当表格中的行数增加时,内存处理和存储不会得到优化。

可以处理具有数千行数据的超大数据集,同时优化内存并减少文件大小。

如果您尝试使用多个 Excel 表格创建数据透视表,系统将提示您创建关系,并且与该关系相关的表格将被添加到数据模型中。

pivot table

您将在章节 - 中了解数据透视表。

如果您的工作簿的数据模型中有数据,您可以创建跨多个数据表格的数据的 Power Pivot 表格和 Power Pivot 图表。

power pivot

您将在章节 - 中了解这些内容。

Dynamic Dashboard Elements with Interactive Controls

您可以使用滚动条、单选按钮、复选框和动态标签等易于使用的控件让仪表盘元素变得交互式。您将在章节 - 中了解有关这些内容的更多信息。

Scrollbars

scroll bar

Radio Buttons

radio button

Checkboxes

checkboxes

Excel Power PivotTables and Power PivotCharts

Excel Power 透视表和 Power 透视图表通过在工作簿中构建内存优化的数据模型,有助于汇总来自多个资源的数据。数据模型中的数据表格可以运行数千个动态数据,从而可以毫不费力且快速地进行汇总。

您将在章节 - Excel Power 透视表和 Power 透视图表仪表盘中了解如何在仪表盘中使用 Power 透视表和 Power 透视图表。

Excel Data Model

data model

Excel Power PivotTable and Power PivotChart

pivot table chart

Excel Power View Reports

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

您将在章节 - Excel Power View 仪表盘报表中了解如何使用 Power View 作为仪表盘画布。

Power View Report

view report

Key Performance Indicators (KPIs)

关键绩效指标(KPI)是许多仪表盘中不可或缺的一部分。你可以在 Excel 中创建和管理 KPI。你将在章节中学习关于 KPI 的知识 - Key Performance Indicators in Excel Dashboards

Key Performance Indicators

key performance

Excel Dashboards - Conditional Formatting

Conditional Formatting for Data Visualization

如果你选择使用 Excel 来创建仪表盘,请尝试使用 Excel 表格,如果它们满足目标。对于条件格式和迷你图,Excel 表格是用于仪表盘的最佳且最简单的选择。

在 Excel 中,你可以使用条件格式进行数据可视化。例如,在包含过去季度按地区划分的销售数字的表格中,你可以突出显示排名前 5% 的值。

data visualization

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

你可以选择适合你的数据可视化的格式选项 - 数据条、颜色刻度或图标集。

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

Highlighting Cells

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

  1. 给定数值范围内的数字:大于、小于、介于和等于。

  2. 重复或唯一的值。

考虑你想要展示的以下结果摘要 -

highlighted cells

假设你要突出显示大于 1000000 的总金额值。

  1. 选择列 - 总金额。

  2. 单击“开始”选项卡下“样式”组中的条件格式。

  3. 单击下拉列表中的突出显示单元格规则。

  4. 单击出现的第二个下拉列表中的大于。

conditional formatting

将出现大于对话框。

  1. 在格式大于的单元格中:框中,指定条件为 1000000。

  2. 在框中,选择格式选项为浅绿色文本的绿色填充。

greater than dialog
  1. Click the OK button.

specified format

正如你所看到的,满足指定条件的值将以指定格式突出显示。

Top / Bottom Rules

你可以使用顶部/底部规则对满足下列任何标准的值分配格式 -

  1. Top 10 Items − 排名在 N 名内的单元格(其中 1 ⇐ N ⇐ 1000)。

  2. Top 10% − 排名在 n% 以内的单元格(其中 1 ⇐ n ⇐ 100)。

  3. Bottom 10 Items − 排名在 N 名以外的单元格(其中 1 ⇐ N ⇐ 1000)。

  4. Bottom 10% − 排名在 n% 以外的单元格(其中 1 ⇐ n ⇐ 100)。

  5. Above Average − 被选中范围内的均值以上的单元格。

  6. Below Average − 被选中范围内的均值以下的单元格。

假如你想突出列出前 5% 的总量值。

  1. 选择列 - 总金额。

  2. 单击“开始”选项卡下“样式”组中的条件格式。

  3. 在下拉列表中单击顶部/底部规则。

  4. 在出现的第二个下拉列表中,单击最前十百分比。

top bottom rules

将出现最前十百分比对话框。

  1. 在对排名在 TOP 中的单元格设置格式框中,指定条件为 5%。

  2. 在框中,选择格式选项为浅绿色文本的绿色填充。

top option
  1. 单击确定按钮。排名前 5% 的值将用指定的格式突出显示。

top bottom format

Data Bars

你可以使用彩色的数据条来查看相对其他值的值。数据条的长度代表值。更长的条代表更高的值,更短的条代表更低的值。你既可以使用单色数据条,也可以使用渐变数据条。

  1. 选择列 - 总金额。

  2. 单击“开始”选项卡下“样式”组中的条件格式。

  3. 在下拉列表中单击数据条。

  4. 在出现的第二个下拉列表中的渐变填充下单击蓝色数据条。

data bars

列中的值将以蓝色渐变填充条突出显示,显示小值、中间值和大值。

gradient fill bar
  1. 选择列 - 总金额。

  2. 单击“开始”选项卡下“样式”组中的条件格式。

  3. 在下拉列表中单击数据条。

  4. 在出现的第二个下拉列表中的纯色填充下单击橙色数据条。

orange bar

列中的值将根据条形高度以橙色条突出显示,显示小值、中间值和大值。

colored bar

假设您希望突出显示销售额与既定销售目标(例如 800000)的比较情况。

  1. 使用值 = [@[总金额]]-800000 创建一个列。

  2. Select the new column.

  3. 单击“开始”选项卡下“样式”组中的条件格式。

  4. 在下拉列表中单击数据条。

  5. 单击出现的第二个下拉列表中梯度填充下的绿色数据条。

green color bar

数据条将从每个单元格的中间开始,针对负值向左延伸,针对正值向右延伸。

positive negative values

您可能注意到,延伸到右侧的条形为绿色,表示正值;延伸到左侧的条形为红色,表示负值。

Color Scales

可以使用颜色刻度查看单元格中的值与列中其他单元格中的值的相对关系。颜色指示每个单元格值在这个范围中的位置。可以使用 3 色刻度或 2 色刻度。

  1. 选择列 - 总金额。

  2. 单击“开始”选项卡下“样式”组中的条件格式。

  3. 单击下拉列表中的颜色刻度。

  4. 单击第二个出现的下拉列表中的绿色-黄色-红色颜色刻度。

color scale

与突出显示单元格规则一样,颜色刻度使用单元格阴影来显示单元格值中的差异。您可以在预览中观察到,对于此数据集而言,阴影差异并不明显。

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

more rules

将出现新建格式规则对话框。

  1. 单击选择规则类型框中的基于其值设置所有单元格格式。

  2. 在编辑规则说明框中,在格式样式框中选择以下内容:选择 3 色比例。在中点,针对值 - 键入 75。

rule description

单击确定按钮。

shaded depicting

您可以看到,使用定义的颜色刻度,该值具有明显不同的阴影,描述了数据范围。

Icon Sets

可以使用图标集来显示数值差异。在 Excel 中,您有一系列图标集 -

Icon Set Type

Icon Sets

Directional

Shapes

Indicators

Ratings

您可能注意到,一个图标集包含 3 到 5 个符号。您可以定义条件,将图标与单元格范围中的值相关联。例如,对于较小的数字,使用一个红色的向下箭头;对于较大的数字,使用一个绿色的向上箭头;对于中间值,使用一个黄色的水平箭头。

  1. 选择列 - 总金额。

  2. 单击“开始”选项卡下“样式”组中的条件格式。

  3. 单击下拉列表中的图标集。

  4. 单击第二个出现的下拉列表中的方向组中的 3 个箭头(彩色)。

icon set

选择列中会基于值显示彩色箭头。

colored arrow

Using Custom Rules

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

  1. 选择列 - 总金额。

  2. 单击“开始”选项卡下“样式”组中的条件格式。

  3. 单击下拉列表中的“新建规则”。

custom rule

将出现新建格式规则对话框。

  1. 在“选择规则类型”框中,单击“使用公式确定要格式化的单元格”。

  2. 在“编辑规则描述”框中,执行以下操作:- 在框中输入公式 - 格式化公式为真的值。例如:= PercentRank.INC($E$3:$E$13,E3)>=0.7 单击“格式”按钮。选择格式。例如:字体 - 粗体和填充 - 橙色。单击“确定”。

  3. Check the Preview.

preview

如果预览没问题,单击“确定”。满足公式的数据集中值将使用你选择的格式突出显示。

data set

Managing Conditional Formatting Rules

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

单击“主页”选项卡中“样式”组中的 Conditional Formatting 。在下拉列表中,单击“管理规则”。

managing rules

将出现“条件格式规则管理器”对话框。你可以查看所有现有规则。可以添加新规则、删除规则,或编辑规则以修改它。

existing rule

Excel Dashboards - Excel Charts

如果您选择图表来以可视方式显示数据,Excel 图表可帮助您选取和更改不同的视图。Excel 提供了几种图表类型,使您能够使用图形表示任何数据集合,以便在仪表盘中用现有数据传达您想要传达的信息。

此外,还有一些复杂图表对某些特定目的很有用。Excel 2016 中提供了其中的一些图表。不过,它们也可以使用 Excel 2013 中的内置图表类型构建。

在本章中,您将了解 Excel 中的图表类型以及何时使用每种图表类型。请记住,在仪表盘中的一张图表中,您应仅传达一条信息。否则,可能会导致解释混淆。您可以调整图表大小,以便在仪表盘中容纳更多数量的图表,每张图表传达一条特定信息。

除了本章讨论的图表类型外,还有一些高级图表被广泛用于以视觉提示描述信息。您将在本章中学习高级图表类型及其用法 – Advanced Excel Charts for Dashboards

Types of Charts

如果您拥有 Excel 2013,您可以找到以下主要图表类型 −

Column Charts

stacked column
three d column

Line Charts

line chart
three d line

Pie Charts

pie d chart
two three d chart
pie pie chart

Doughnut Chart

doughnut

Bar Charts

clustered bar chart
stacked bar
three d bar

Area Charts

area stacked chart
three d area

XY (Scatter) Charts

scatter xy chart
scatter with lines

Bubble charts

bubble chart

Stock Charts

stock volume

Surface Charts

surface chart

Radar Charts

radar with markers

要了解这些图表,请参阅教程 − Excel Charts

Combo Charts

当您拥有混合类型的数据时,您可以使用组合图表显示它。图表可以仅具有“主要纵轴”或“主要纵轴”和“次要轴”的组合。您将在后面的章节中学习组合图表。

Selecting the Appropriate Chart Type

要在仪表盘中通过图表显示数据,首先要确定该图表的目的。一旦清楚地了解您要通过图表表示什么,您就可以选择最能描述您的信息的图表类型。

以下是选择图表类型的一些建议 −

  1. 如果您要比较数据值,可以选择条形图、饼图、折线图或散点图。

  2. 如果您要显示分布,可以使用柱形图、散点图或折线图。

  3. 如果你想显示趋势随时间的变化,可以使用折线图。

  4. 如果你想表示一个整体的各个部分,饼图可以是一个选择。但是,当你使用饼图时,请记住,通过饼图不同大小的切片只能有效地描绘两个到三个数据点,且这些数据点需要有非常不同的数据值。如果你尝试在饼图中描绘更多的数据点,则可能难以找出比较结果。

  5. 如果你有以下任何一种目的,可以使用散点图:你想显示大型数据集之间的相似性,而不是数据点之间的差异。你想比较许多数据点,而不考虑时间。你包含在散点图中的数据越多,你所能做出的比较就越好。

  6. Excel 中的推荐图表可帮助你找到适合你数据的图表类型。

在 Excel 中,你可以创建一个具有图表类型的图表,并稍后随时轻松修改它。

迷你图是放置在单个单元格中的微型图表,每个单元格都代表你选择中的一行数据。它们提供了一种查看趋势的快速方法。在 Excel 中,你可以有折线图迷你图、柱状迷你图或盈亏迷你图。

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

  1. 识别你希望添加迷你图的数据。

  2. 在数据右侧保留一列空白,并给该列命名。迷你图将放置在此列中。

  3. Select the data.

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

quick analysis
  1. 单击快速分析按钮。快速分析工具出现。

  2. 单击迷你图。出现图表选项。

chart option
  1. 单击折线。为所选数据中的每一行显示折线图。

sparkline chart
  1. 单击柱形。为所选数据中的每一行显示柱状图。

sparkline column

盈亏图不适用于此数据。考虑以下数据以了解盈亏图的外观。

win loss

Using Combo Charts for Comparisons

如果数据范围差异很大,你可以使用组合图将两种或更多种图表类型结合起来来比较不同类别的值。通过次要轴来描绘其他数据范围,将使图表更容易读取和快速掌握信息。

combo chart

Fine Tuning Charts Quickly

你可以使用图表右上角出现的三个按钮 、 和 快速微调图表。

  1. 使用图表元素,你可以向图表添加或删除轴、轴标题、图例、数据标签、网格线、误差线等。

  2. 使用图表样式,你可以通过设置图表样式和颜色来自定义图表的外观。

  3. 借助图表过滤器,您可以动态编辑图表上显示的数据点(值)和名称。

fine tuning
  1. 您可以选择/取消选择图表元素。

display selected chart
  1. 您可以设置网格线格式以显示深度轴。

gridlines
  1. 您可以设置图表样式。

chart style
  1. 您可以为图表选择配色方案。

color scheme
  1. 您可以动态选择要显示的值和名称,值是数据系列和类别,名称是数据系列(列)和类别(行)的名称。

value names

Using Aesthetic Data Labels

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

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

data labels

您可以使用各种选项(包括效果)设置数据标签的格式。

format data label

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

data label shapes
changed labels

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

resized labels

您可以包括数据点中的文本或其他任何文本,以使任何数据标签可以刷新,从而使其动态。

data label field

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

leader line

您可以通过移动数据标签将具有引导线的数据标签放置在距数据点的任何距离处。

adjust leader line

您可以设置引导线的格式以使其醒目。

leader line option

您可以选择以下任何选项,以根据数据和要突出显示的内容在图表上显示数据标签。

即使切换到不同类型的图表,数据标签仍会保留在原处,但是在设置任何图表元素(包括数据标签)的格式之前,请先确定图表类型。

Using Trendlines in Charts

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

trendline

Using Shapes in Charts

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

shapes in charts

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

formatted shape

Using Cylinders, Cones, and Pyramids

在 3D 柱形图中,默认情况下,您会有一个个框。

default chart

为了使您的图表在仪表板中更醒目,可以选择其他 3D 柱形形状,例如圆柱体、圆锥体、棱锥体等。您可以在“格式化数据系列”窗格中选择这些形状。

column shapes

Columns with Pyramid shape

pyramid shape

Columns with Cylinder shape

cylinder shape

Columns with Cone shape

cone shape

Using Pictures in Charts

您可以通过使用图片来代替柱形图,从而使您的数据演示更加突出。

pictures in charts

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

Excel Dashboards - Advanced Excel Charts

你应该意识到图形对于直观传达数据信息非常有用。除了 Excel 中提供的图形类型之外,还有很多受欢迎的应用图形。其中一些也包含在 Excel 2016 中。

如果你使用的是 Excel 2013 或更早版本,请参阅教程 - 高级 Excel 图形,以了解这些图形及其如何通过内置图形类型创建图形。

Types of Advanced Excel Charts

以下高级 Excel 图形类型将方便你包含在仪表板中:-

Waterfall Chart

瀑布图非常适合通过分解正面和负面贡献的累积影响来显示如何达到净值(例如净收入)。

waterfall

Band Chart

分段图适合以图形方式表示一段时间内的数据,将每个数据点限制在一个定义的时间段内。例如,来自不同地区的产品客户调查结果。

band chart

Gantt Chart

甘特图是一个图表,其中一系列水平线显示在特定时间段内完成的工作量,相对于这些时间段计划的工作量。

gantt chart

Thermometer Chart

当你必须表示目标值和实际值时,可以使用温度计图表明确显示这些值。

thermometer

Gauge Chart

仪表图显示最小值、最大值和当前值,描绘出你距离最大值有多远。

gauge excel chart

Bullet Chart

子弹图可用于将指标与一个或多个相关指标进行比较,并将指标与声明其质化状态的已定义定量范围(例如好、满意和差)关联起来。你还可以使用子弹图来显示 KPI。

bullet chart

Funnel Chart

漏斗图用于可视化数据在从一个阶段到另一个阶段时逐步减少的情况。例如,销售渠道。

funnel chart

Waffle Chart

华夫饼图是一个不错的选择,可显示完成的工作所占百分比、实现的目标与预期目标等的对比。

waffle chart

Heat Map

热力图是表中数据的可视化表示,用于突出显示重要数据点。

heat map

Step Chart

如果您必须显示以不规则间隔变化但变化之间保持恒定的变化,则阶梯图很有用。

step chart

Box and Whisker Chart

箱线图通常用于统计分析。例如,您可以使用箱线图来比较试验结果或竞赛考试结果。

box whisker chart

Histogram

直方图是数值数据分布的图形表示,广泛用于统计分析。

histogram

Pareto Chart

帕累托图是另一种广泛用于统计分析以帮助决策的图表。它代表帕累托分析,也称为 80/20 法则,这意味着 80% 的结果是由 20% 的原因造成的。

pareto chart

Displaying Quarterly Performance with Bullet Charts

假设您必须在仪表板上按季度显示销售团队的绩效。数据可以如下所示。

quaterly performance

您可以使用子弹图将此信息显示在仪表板上,如下所示−

bullet chart information

如您所见,它占用的空间较小,但传达了大量信息。

Displaying Profit % Region-Wise with Waffle Charts

假设您必须显示东部、北部、南部和西部各地区的利润百分比。

region wise

您可以像下面显示的那样,使用华夫饼图在仪表板上着重显示此信息。

comparison

此显示不仅描述了值,还提供了良好的比较。

Excel Dashboards - PivotTables

如果您将数据放入单个 Excel 表中,则可以使用 Excel 数据透视表以所需的方式汇总数据。数据透视表是一个非常强大的工具,您可以使用它来切分和整理数据。您可以使用一个紧凑的表格跟踪和分析数十万个数据点,该表格可以动态更改,以便您从数据的不同角度发现它们。这是一个简单、但功能强大的工具。

Excel 为您提供了一种更强大的方法,可以从多个表格、不同数据源和外部数据源创建数据透视表。它被命名为“数据透视表”,它使用其自己的数据库(称为数据模型)。您将在其他章节中了解数据透视表和其他 Excel 强大的工具,例如数据透视图表和 Power View 报表。

数据透视表、动力数据透视表、动力数据透视图表和 Power View 报表非常方便,可以在仪表板中显示来自大型数据集的汇总结果。在尝试使用这些强大工具之前,您可以在普通数据透视表上获得熟练度。

Creating a PivotTable

您可以创建数据透视表,方法是从数据范围或 Excel 表中创建。在这两种情况下,数据的首行应包含列标题。

您可以从一个空数据透视表开始,并从头开始构建它,或使用 Excel 推荐的数据透视表命令预览适合您数据的可能自定义数据透视表,并选择一个适合您目的的数据透视表。在任何一种情况下,您都可以立即修改数据透视表,以深入了解手头数据的不同方面。

考虑以下包含每个销售人员在每个地区和 1 月、2 月和 3 月的销售数据的数据范围 −

sales data

要从这个数据范围创建数据透视表,请执行以下操作 −

  1. 确保首行包含标题。您需要标题,因为它们将成为数据透视表中的字段名。

  2. 将数据范围命名为 SalesData_Range。

  3. 单击数据范围 − SalesData_Range。

  4. 单击功能区上的插入选项卡。

  5. 单击表格组中的数据透视表。

将出现创建数据透视表对话框。

pivottable

正如您在创建数据透视表对话框中所观察到的,在选择要分析的数据下,您可以从当前工作簿中选择表或范围,或使用外部数据源。因此,您可以使用相同的步骤,通过范围或表来创建数据透视表表单。

  1. 单击“选择表格或区域”。

  2. 在“表格/区域”框中,键入区域名称 − SalesData_范围。

  3. 在“选择您要放置数据透视表报告的位置”下单击“新工作表”。

您还可以看到您可以通过将此数据范围添加到数据模型来选择分析多个表格。数据模型是 Excel Power Pivot 数据库。

create pivottable
  1. 单击“确定”按钮。新的工作表将插入到您的工作簿中。新的工作表包含空白数据透视表。

  2. 命名工作表 − 范围-数据透视表。

new worksheet

如您所见,数据透视表字段列表显示在工作表的右侧,其中包含数据范围中各列的标题名称。此外,在功能区上,会出现数据透视表工具 - 分析和设计。

您需要根据要显示的数据选择数据透视表字段。通过将字段放置在适当的区域,您可以获得数据所需的布局。例如,要按销售人员按月份对订单金额进行汇总 − 一月、二月和三月,您可以执行以下操作 −

  1. 在“数据透视表字段”列表中单击“销售人员”字段,并将其拖动到“行”区域。

  2. 在“数据透视表字段”列表中单击“月份”字段,并将其拖动到“行”区域。

  3. 单击“订单金额”,并将其拖动到“∑ 值”区域。

pivottable fields

您的数据透视表已准备就绪。您只需将字段拖过各区域即可更改数据透视表的布局。您可以在数据透视表字段列表中选择/取消选择字段以选择要显示的数据。

Filtering Data in PivotTable

如果您需要关注数据透视表数据的一个子集,则可以基于一个或多个字段值的子集对数据透视表中的数据进行筛选。例如,在上述示例中,您可以基于范围字段筛选数据,以便仅显示所选区域的数据。

有几种方法可以在数据透视表中筛选数据 −

  1. Filtering using Report Filters.

  2. Filtering using Slicers.

  3. Filtering data manually.

  4. Filtering using Label Filters.

  5. Filtering using Value Filters.

  6. Filtering using Date Filters.

  7. 使用前 10 名筛选器进行筛选。

  8. Filtering using Timeline.

您将在本部分了解报表筛选器的使用,并在下一部分了解切片器的使用。有关其他筛选选项,请参阅 Excel 数据透视表教程。

您可以为某个字段分配筛选器,以便您可以根据该字段的值动态更改数据透视表。

  1. 将字段区域拖到“筛选器”区域。

  2. 将字段销售人员拖动到“行”区域。

  3. 将“月”字段拖动到“列”区域。

  4. 将“订单金额”字段拖动到“∑ 值”区域。

filtering data

带有“区域”标签的“筛选器”将显示在数据透视表上方(如果您在数据透视表上方没有留空行,则数据透视表将被下移以腾出筛选器的空间)。

filter region

您可以观察到,

  1. 销售人员值显示在行中。

  2. 月值显示在列中。

  3. 区域筛选器显示在顶部,默认选择为全部。

  4. 汇总值为订单金额之和。以销售人员为基础的订单金额之和显示在“总计”列中。以月份为基础的订单金额之和显示在“总计”行中。

  5. 单击区域筛选器中的箭头。

将显示带有区域字段值的列表。

region values
  1. 勾选“选择多个单位”复选框。所有值都会显示复选框。默认情况下,所有复选框都会被勾选。

  2. 取消勾选(全部)复选框。所有复选框都会被取消勾选。

  3. 勾选南部和西部复选框。

multiple items
  1. 单击“确定”按钮。将只汇总南部和西部区域的数据。

selected items

您可以观察到,在区域筛选器旁边单元格中显示了(多个单位),表示您选择了多个值。但是,从显示的报告中,并不知道有多少个值和/或哪些值。在这种情况下,使用切片器会是一个更好的筛选选项。

Using Slicers in PivotTable

使用切片器筛选有许多优势 −

  1. 您可以通过选择切片器的字段制作多个筛选器。

  2. 您可以可视化应用筛选器的字段(每个字段一个切片器)。

  3. 切片器会有表示它所代表的字段值的按钮。您可以单击切片器的按钮以选择/取消选择字段中的值。

  4. 你可以在筛选器中看到一个字段的哪些值被使用了(在切片器中,选中的按钮被高亮显示)。

  5. 你可以为多个数据透视表和/或数据透视图使用一个常见的切片器。

  6. 你可以隐藏/取消隐藏一个切片器。

为了了解切片器的用法,考虑以下数据透视表。

usage of slicers

假设你想要根据区域和月份字段过滤数据透视表。

  1. 点击功能区中 PIVOTTABLE TOOLS 下的 ANALYZE 选项卡。

  2. 点击 Filter 组中的 Insert Slicer。

插入切片器对话框出现。它包含了数据中的所有字段。

  1. 勾选区域和月份。

analyse pivottable
  1. 点击确定按钮。每个选定字段的切片器都显示为默认情况下选定的所有值。切片器工具出现在功能区上,用于处理切片器的设置,外观和感觉。

slicer tools

正如你所看到的,每个切片器都具有其所代表的字段的所有值,这些值以按钮的形式显示。默认情况下,字段的所有值都被选中,因此所有按钮都被高亮显示。

假设你只想为南方和西方区域以及二月和三月显示数据透视表。

  1. 在区域切片器中点击南方。只有南方会在区域切片器中被高亮显示。

  2. 按下 Ctrl 键并单击区域切片器中的西方。

  3. 在月份切片器中点击二月。

  4. 按住 Ctrl 键并单击月份切片器中的三月。在切片器中选定的值被高亮显示。数据透视表将根据选定的值进行汇总。

selected values

要从过滤中添加/删除字段的值,请按住 Ctrl 键并单击各个切片器中的那些按钮。

Power PivotTables & Power PivotCharts

当您的数据集较大时,可以使用 Excel Power Pivot,它可以处理数亿行数据。数据可以位于外部数据源中,而 Excel Power Pivot 会构建一个数据模型,该模型以内存优化模式工作。您可以执行计算、分析数据并得出报告,以得出结论和决策。报告可以是 Power 透视表或 Power 透视图表,也可以是二者的组合。

您可以将 Power Pivot 用作即席报告和分析解决方案。因此,对于具有 Excel 实践经验的人来说,有可能在几分钟内执行高端数据分析和决策,并且是仪表板中的一项宝贵资产。

Uses of Power Pivot

您可以将 Power Pivot 用于以下用途−

  1. 执行强大的数据分析并创建复杂的数据模型。

  2. 快速整合来自多个不同来源的大量数据。

  3. 执行信息分析并以交互方式共享见解。

  4. 创建关键绩效指标 (KPI)。

  5. To create Power PivotTables.

  6. To create Power PivotCharts.

Differences between PivotTable and Power PivotTable

Power 透视表在布局上类似于透视表,具有以下不同之处−

  1. 数据透视表基于 Excel 表格,而 Power PivotTable 基于数据模型中包含的数据表格。

  2. 数据透视表基于单个 Excel 表格或数据范围,而 Power PivotTable 可基于多个数据表格,前提是这些表格已添加到数据模型中。

  3. 数据透视表从 Excel 窗口创建,而 Power PivotTable 从 PowerPivot 窗口创建。

Creating a Power PivotTable

假设数据模型中有两个数据表格——“销售人员”和“销售额”。要从这两个数据表格创建 Power PivotTable,请执行以下操作:

  1. 在 PowerPivot 窗口的功能区上单击“主页”选项卡。

  2. 单击功能区上的“数据透视表”。

  3. 单击下拉列表中的“数据透视表”。

home pivot

将出现“创建数据透视表”对话框。单击“新建工作表”。

create pivot

单击“确定”按钮。Excel 窗口中会创建新工作表,并会显示一个空 Power PivotTable。

active tab

正如你所看到的,Power PivotTable 的布局与此数据透视表类似。

数据透视表字段列表会显示在工作表的右侧。在此处,你会发现与数据透视表之间存在的某些差异。Power PivotTable 字段列表有两个选项卡——“活动”和“全部”,这两个选项卡显示在标题下方且位于字段列表上方。“全部”选项卡被高亮显示。“全部”选项卡显示数据模型中的所有数据表格,而“活动”选项卡显示为当前 Power PivotTable 选择的所有数据表格。

  1. 单击“全部”下数据透视表字段列表中的表格名称。

将显示出带有多选框的相应字段。

  1. 每个表格名称将在左侧显示此符号。

  2. 如果将光标置于此符号之上,将显示该数据表格的数据源和模型表格名称。

data source
  1. 将“销售人员”从“销售人员”表格拖动到“行”区域。

  2. 单击“活动”选项卡。

字段“销售人员”显示在 Power PivotTable 中,并且表格“销售人员”显示在“活动”选项卡下。

  1. 单击“全部”选项卡。

  2. 在销售表中单击“月份”和“订单金额”。

  3. 单击“活动”选项卡。

销售和销售人员这两个表均显示在“活动”选项卡下。

sales salesperson
  1. 将“月份”拖到“列”区域。

  2. 将“区域”拖到“筛选器”区域。

drag filter area
  1. 单击区域筛选器框中“全部”旁边的箭头。

  2. 单击“选择多项”。

  3. 单击“北方”和“南方”。

region filter box
  1. 单击“确定”按钮。按升序对列标签进行排序。

explore data

可以通过动态方式修改 Power PivotTable 以浏览和报告数据。

Creating a Power PivotChart

Power PivotChart 是基于数据模型并在 Power Pivot 窗口中创建的透视表。虽然它具有一些与 Excel 透视表相似的功能,但其他功能使它变得更加强大。

假设你要基于以下数据模型创建 Power PivotChart。

table tools
  1. 在 Power Pivot 窗口功能区中单击“开始”选项卡。

  2. Click on PivotTable.

  3. 在下拉列表中单击“透视表”。

dropdown list

“创建透视表”对话框随即出现。单击“新建工作表”。

create pivot west
  1. 单击“确定”按钮。在 Excel 窗口的新工作表上创建一个空透视表。在本章中,当我们提到透视表时,我们指的是 Power PivotChart。

pivot chart

如你所见,数据模型中的所有表格会显示在“透视表字段”列表中。

  1. 单击“透视表字段”列表中的销售人员表格。

  2. 将字段“销售人员”和“区域”拖到“轴”区域。

透视表中将出现两个轴字段按钮,分别对应于两个选定字段。这些是轴字段按钮。字段按钮用于筛选透视表中显示的数据。

axis field
  1. 从 4 张表(East_Sales、North_Sales、South_Sales 和 West_Sales)中的每一张表将 TotalSalesAmount 拖动到 ∑ VALUES 区域。

legend value

正如您所看到的,工作表中出现以下内容:

  1. 在透视图表中,默认显示柱形图。

  2. 在图例区域,添加了 ∑ VALUES。

  3. 值显示在透视图表中的图例中,标题为值。

  4. 值字段按钮显示在透视图表上。

您可以移除图例和值字段按钮,以便让透视图表看起来更简洁。

  1. 单击透视图表右上角的按钮。

  2. 取消选择图表元素中的图例。

total sales amount
  1. 右键单击值字段按钮。

  2. 在下拉列表中单击在图表上隐藏值字段按钮。

图表上的值字段按钮将被隐藏。

hidden value field

请注意,字段按钮和/或图例的显示取决于透视图表的内容。您需要决定要显示的内容。

与动态数据透视表一样,动态数据透视图表字段列表也包含两个选项卡:活动和全部。此外,还有 4 个区域:

  1. AXIS (Categories)

  2. LEGEND (Series)

  3. ∑ VALUES

  4. FILTERS

正如您所看到的,图例中填充了 ∑ 值。此外,字段按钮被添加到透视图表中,以便于过滤显示的数据。您可以单击字段按钮上的箭头,选择/取消选择要在动态数据透视图表中显示的值。

Table and Chart Combinations

动态数据透视图为您提供了动态数据透视表和动态数据透视图的不同组合,以便探索数据、可视化数据和报告数据。

考虑我们在动态数据透视图中用于图示的以下数据模型:

table chart

您可以在动态数据透视图中使用以下表和图表组合。

  1. 图表和表格(水平) - 可以在同一工作表中创建一个动态数据透视图表和一个动态数据透视表,一个紧挨着另一个水平排列。

table horizontal

图表和数据透视表(垂直)——您可以在同一工作表的垂直方向上创建一个 Power PivotChart 和一个 Power PivotTable。

table vertical

单击 Power Pivot 窗口中功能区的“数据透视表”时出现的下拉列表中提供了这些组合和其他一些组合。

Hierarchies in Power Pivot

您可以在 Power Pivot 中使用层次结构进行计算,以及进行嵌套数据向上钻取和向下钻取。

考虑此章节中插图的以下数据模型。

table chart

您可以在数据模型的图表视图中创建层次结构,但只能基于一个数据表。

  1. 按如下顺序单击数据表“Medal”中的列——Sport、DisciplineID 和 Event。请记住,此顺序对于创建有意义的层次结构非常重要。

  2. Right-click on the selection.

  3. 单击下拉列表中的“创建层次结构”。

将使用三个选定字段作为子级的层次结构字段创建。

  1. 右键单击层次结构名称。

  2. 单击下拉列表中的“重命名”。

  3. 键入一个有意义的名称,比如 EventHierarchy。

event hierarchy

可以使用在数据模型中创建的层次结构创建 Power PivotTable。

  1. Create a Power PivotTable.

event hierarchy more fields

如您所见,在“数据透视表字段”列表中,EventHierarchy 显示为 Medal 表中的字段。Medal 表中的其他字段已折叠,并以“更多字段”显示。

  1. 单击 EventHierarchy 前面的箭头。

  2. 单击“更多字段”前面的箭头。

将显示 EventHierarchy 下的字段。Medal 表中的所有字段将显示在“更多字段”下。

event fields

按如下方式将字段添加到 Power PivotTable——

  1. 将 EventHierarchy 拖到 ROWS 区域。

  2. 将 Medal 拖动到 ∑ VALUES 区域。

sport field

如你所见,Power透视表中的Sport字段值前面带有+符号。显示了每个体育项目的奖牌数。

  1. 单击Aquatics前面的+符号。将显示Aquatics下的DisciplineID字段值。

  2. 单击出现的子级D22。将显示D22下的Event字段值。

aquatics

如你所见,给了Event的奖牌数,它在父级DisciplineID处相加,而它又进一步在父级Sport处相加。

Calculations Using Hierarchy in Power PivotTables

你可以使用Power透视表中的层次结构创建计算。例如,在EventsHierarchy中,你可以将子级处的奖牌数以父级处奖牌数的百分比的形式显示,如下所示:

  1. 右键单击某个Event的Medal value的Count。

  2. 在下拉列表中单击Value Field Settings。

value field setting

将出现Value Field Settings对话框。

  1. 单击Show Values As选项卡。

  2. 单击Show values ass框。

  3. 单击% of Parent Row Total。

show value
  1. Click the OK button.

diving

如你所见,子级显示为Parent Totals的百分比。你可以通过对父级的子级的百分比值求和来验证这一点。和为100%。

Drilling Up and Drilling Down a Hierarchy

你可以使用Quick Explore tool快速浏览Power透视表中层次结构中的级别。

  1. 单击Power透视表中Event字段的值。

  2. 单击Quick Explore tool-出现于所选值所在单元格的右下角。

quick explore

EXPLORE框带有Drill Up选项。这是因为从Event只能向上钻取,因为它下面没有子级。

drill up
  1. 单击Drill Up。Power透视表数据被向上钻取至Discipline级别。

discipline level
  1. 单击Quick Explore tool-出现于包含值的单元格的右下角。

EXPLORE框出现,显示Drill Up和Drill Down选项。这是因为从Discipline可以向上钻取至Sport或向下钻取至Event级别。

drill down

这样,您可以快速地在 Power PivotTable 中上下移动层级。

Using a Common Slicer

您可以插入切片器,并在 Power PivotTable 和 Power PivotChart 中共享它们。

  1. 在彼此相邻的水平方向上创建一个 Power PivotChart 和 Power PivotTable。

  2. Click on Power PivotChart.

  3. 将 Discipline 从 Disciplines 表拖到 AXIS 区域。

  4. 将 Medal 从 Medals 表拖到 ∑ VALUES 区域。

  5. Click on Power PivotTable.

  6. 将 Discipline 从 Disciplines 表拖到 ROWS 区域。

  7. 将 Medal 从 Medals 表拖到 ∑ VALUES 区域。

common slicer
  1. 单击功能区上“PIVOTTABLE TOOLS”中的“ANALYZE”选项卡。

  2. Click on Insert Slicer.

将显示“插入切片器”对话框。

  1. 单击 Medals 表中的 NOC_CountryRegion 和 Sport。

  2. Click on OK.

会显示两个切片器——NOC_CountryRegion 和 Sport。

  1. 按如下方式排列和调整它们的大小,以便恰当对齐在 Power PivotTable 旁边。

noc country region
  1. 单击 NOC_CountryRegion 切片器中的 USA。

  2. 单击 Sport 切片器中的 Aquatics。

Power PivotTable 将被筛选到选定的值。

sport slicer

正如您所看到的,Power PivotChart 未被筛选。要使用相同的筛选器筛选 Power PivotChart,您可以使用与 Power PivotTable 中所用相同的切片器。

  1. Click on NOC_CountryRegion Slicer.

  2. 单击功能区上“SLICER TOOLS”中的“OPTIONS”选项卡。

  3. 单击切片器组中的“Report Connections”。

将显示 NOC_CountryRegion 切片器的“Report Connections”对话框。

report connections

正如您所看到的,工作簿中的所有 Power PivotTable 和 Power PivotChart 都会列在该对话框中。

  1. 单击位于与选定的 Power PivotTable 相同的工作表中的 Power PivotChart。

  2. Click the OK button.

  3. Repeat for Sport Slicer.

sport report connection

Power PivotChart 也将过滤为在两个切片器中所选的值。

filtered slicer

接下来,可以在 Power PivotChart 和 Power PivotTable 中添加更多详细信息。

  1. 单击 Power PivotChart。

  2. 将性别拖到图例区域。

  3. 右键单击 Power PivotChart。

  4. 单击更改图表类型。

  5. 在更改图表类型对话框中选择堆积柱形图。

  6. 单击 Power PivotTable。

  7. 将事件拖到行区域。

  8. 单击功能区中 PIVOTTABLE 工具中的设计选项卡。

  9. Click on Report Layout.

  10. 从下拉列表中单击大纲形式。

outline form

Aesthetic Reports for Dashboards

您可以使用 Power PivotTables 和 Power PivotCharts 创建美学报告,并将它们包含在仪表板中。如您在前一部分所见,可以使用报表布局选项来选择报表的外观。例如,通过选择 - 显示为大纲形式和显示带状行选项,您将获得如下所示的报表。

aesthetic report

正如您所看到的,字段名称显示在行标签和列标签的位置,而报表看起来不言自明。

您可以在选择窗格中选择要在最终报表中显示的对象。例如,如果您不想显示已创建且使用的切片器,则可以通过在选择窗格中取消选中来隐藏它们。

Excel Dashboards - Power View Reports

Excel Power View 实现了交互式数据可视化,鼓励直观即席的数据探索。数据可视化是多功能且动态的,因此只需一个 Power View 报告就能轻松显示数据。

您可以在一个可视化切换到另一个可视化、钻取数据并显示数据的精要时,立即处理跨越数千行的大型数据集。

Power View 报表基于数据模型,该模型可称为 Power View 数据库,它可以优化内存,从而实现更快的计算和数据显示。一个典型的数据模型如下所示。

power view

在本章中,您将了解可整合到仪表板中的 Power View 报表的显着特性。

Power View Visualizations

Power View 提供了多种类型的数据可视化:

Table

表格可视化是最简单和默认的可视化。如果您想要创建任何其他可视化,则首先将创建表格,您需要通过“转换可视化”选项将其转换为所需的可视化。

table

Matrix

matrix

Card

card

Charts

Power View 在可视化中具有以下图表类型:

  1. Line Chart

  2. Bar Chart

  3. Column Chart

  4. Scatter Chart

  5. Bubble Chart

  6. Pie Chart

Line Chart

visualizing line

Bar Chart

visualizing bar chart

Column Chart

visualizing column chart

Scatter Chart and Bubble Chart

visualizing scatter bubble

Pie Chart

visualizing pie chart

Map

map

Map with Pie Charts

map with pie chart

Combination of Power View Visualizations

与 Excel 图表不同,Power View 可视化非常强大,因为它们可以作为组合进行显示,每个组合都描述和/或突出显示显著的结果。

例如,您可以在 Power View 中拥有三个可视化:

  1. Table visualization - 显示国家/地区、奖牌和奖牌数。

  2. Stacked Column chart visualization - 显示国家/地区、性别和奖牌数。

  3. Pie chart visualization - 显示奖牌、性别和奖牌数。

combinational charts

Interactive Nature of Charts in Power View Visualizations

假设您单击上述 Power View 中的饼形切片。您将观察到以下内容:

  1. 单击扇区将突出显示,扇区的其余部分变暗。

  2. 表格将仅显示与突出显示的扇区相对应的数据。

  3. 簇状圆柱将突出显示与突出显示的扇区相对应的数据,而图表其余部分变暗。

interactive nature

该功能可帮助您让受众查看大型数据集中的结果,以探索重要的数据点。

Slicers in Power View

您可以在 Power View 中使用通用切片器,以筛选 Power View 中所有可视化对象显示的数据。

例如,在以下 Power View 中,您有 2 个可视化对象 −

  1. 按国家/地区和奖牌叠放条形图显示奖牌数。

  2. 按运动和奖牌叠放柱形图显示奖牌数。

假设您有两个切片器——一个用于性别,一个用于季节,则这两个图表中的数据都将被筛选到切片器中选定的字段。

slicer

Tiles in Power View

在 Power View 中,磁贴可帮助您选取一个字段的数据点,并查看相应的值。磁贴可用于表格、矩阵、卡片、叠放条形图和地图可视化对象中。

Tiles in Table visualization

tiles in table

Tiles in Matrix visualization

tiles in matrix

Tiles in Card visualization

tiles in card

Tiles in Stacked Bar Chart visualization

tiles in stacked chart

Tiles in Map visualization

tiles in map

磁贴还可以与可视化对象的组合一起使用。

tiles with visualization

您还可以在此类可视化对象中使用图表交互性的特性。

interactive visualization

Power View Reports

您可以制作美观的 Power View 报表,并将其包含在您的信息中心中。

olympics

这可以通过选择一个合适的背景,选择字体、字体大小、颜色标度等等来实现。

Key Performance Indicators

关键绩效指标(KPI)是对已实现目标/目标/业务目标的定量衡量。在仪表板中,KPI 必然有一个位置来直观地显示个人/部门/组织目前所在的位置,与它应该在哪里的位置进行比较。

KPI 的示例包括以下内容 -

  1. 组织的销售部门可以使用 KPI 来衡量月度总利润相对于预测的总利润。

  2. 会计部门可以衡量月度支出相对于收入来评估成本。

  3. 人力资源部门可以衡量每季度的员工流失率。

  4. 商界专业人士经常使用汇编在业务记分卡中的 KPI 来快速准确地获取业务成功的历史概览或识别趋势。

仪表板可以公开或选择性地一直展示受监控的 KPI,因此被选作最佳的监控和报告工具。

Components of a KPI

KPI 主要包含三个组成部分——

  1. Base Value

  2. Target Value / Goal

  3. Status

虽然人们会对状态感兴趣,但基准值和目标值同样重要,因为 KPI 并不是一成不变的,而会随着时间的推移而变化。

在 Excel 中,基准值、目标值和状态的定义见以下部分。

Base Value

基准值由一个运算字段定义,运算结果为一个值。所述运算字段表示表中所在行中该项目的当前值。例如,销售总额、给定时期的利润等。

Target Value

目标值(或目标)由一个运算字段定义,运算结果为一个值,或者由一个绝对值定义。这是用于评估当前值的参照值。目标值可能是以下内容之一——

  1. 一个固定数字,是所有行都应达到的目标。例如,对所有销售人员的销售目标。

  2. 一个运算字段,针对每一行都有一个不同的目标。例如,预算(运算字段),组织中的部门划分。

Status Thresholds and Status

状态是值的视觉指标。Excel 提供了多种方法来显示状态,相对于目标值。

  1. 可以使用项目符号图表来显示 KPI。可以使用一列的阴影区域来说明状态阈值,并使用叠加在状态阈值上的另一列来表示状态。

  2. 还可以在 Power View 中定义和显示 KPI。

Defining KPIs in Excel

要定义 KPI,需要准备好以下内容——

  1. Base Value

  2. Target Value

  3. 状态阈值(例如,差、好、优秀)

例如,要定义一个 KPI 来监控销售业绩,需要执行以下操作——

  1. 确定包含销售总额运算值的那个或那些单元格。这是基准值。

  2. 定义可以是绝对或可变的目标值。

  3. 定义状态阈值,帮助您可视化状态。

Visualizing KPIs with Bullet Charts

你可以使用目标图可视化 KPI,其中将清晰描绘以下内容:

  1. The Target

  2. Status Thresholds

  3. The Value (Status)

visualizing bullet chart

Visualizing KPIs with Power View

你可以使用图标可视化在 Power View 中定义的 KPI。

visualizing power view

您还可以使用可包含在仪表板中的 KPI 在 Power View 中生成美观的报表。

sales performance

正如您所观察到的,在 Power View 中,您可以按如下方式描绘结果 −

  1. 使用图标对 KPI 状态进行表格可视化。

  2. 100% 堆积条形图可视化描述相对于目标实现的百分比。您还可以注意到,它清晰比较了所有销售人员的绩效。

  3. 卡片可视化描述销售人员的 KPI 状态以及他们所属的区域。您可以交互式滚动小块,来显示不同区域的结果,还可以按区域评估绩效。

Excel Dashboards - Build a Dashboard

在前面的章节中,您已了解到在设置仪表板时非常有用的各种 Excel 功能。在本章中,您将学习如何构建仪表板,即设置仪表板所需的步骤。您还将了解有关仪表板的注意事项。

由于任何仪表板都基于受众最感兴趣的具体意图,因此仪表板组件和仪表板布局因情况而异。

Initial Preparation

构建仪表板的第一步是初始准备。花些时间了解以下内容 -

  1. Why do you need the dashboard? - 此仪表板是针对特定任务(如显示项目状态)还是需要实现更广泛的目标(如衡量业务绩效)?了解构建仪表板的原因将指导您进行设计。

  2. What purpose the dashboard will serve? -您的仪表盘应仅突出显示有价值的数据。您应了解所需的数据。除此之外的任何内容都是不必要的。

  3. What is the source of data? -您应了解数据来源。它可以只是一个 Excel 工作表,也可以通过多种动态数据源与 Excel 工作簿建立数据连接。

  4. Who is the audience for the dashboard? -这是针对经理、执行人员、利益相关者、外部供应商还是普通受众?了解他们的要求和偏好,例如他们查看仪表盘的时间、他们期望的详细程度以及他们希望如何消化信息。例如,在选择图表类型时,了解受众可以帮助您决定是否必须显示值之间的关系,或者必须进行特定比较。

  5. Does the dashboard need to be static or dynamic? -仪表盘可以定期(例如,每周或每月)更新,还是需要不断更新,以简化后端发生的数据更改?此选择将改变您构建仪表盘的方式。

  6. Does the dashboard need to be just a display or is it to be interactive? -仪表盘可以具有只读访问权限,还是必须提供交互式控件/功能,以便允许某些人按需探索数据?这种选择也会改变您构建仪表盘的方式。

一旦获得这些问题的答案,就确定您需要和不需要的 Excel 功能。这是因为您的目标和专长是生成适合目的的有效仪表盘。

接下来,识别仪表盘的组件。这些可以是文本、表格、图表、交互式控件等。使用这些组件确定仪表盘布局。

在 PowerPoint 幻灯片上模拟您的 Excel 仪表盘。为每个组件绘制框以了解布局,并添加您想要包含的组件的快速草图。您还可以在一张纸上进行此操作。在您开始处理实际仪表盘之前,从管理层和/或主要受众获得此模型的批准。这将节省返工时间。但是,随着仪表盘的使用和您收到反馈,您很可能需要对仪表盘进行一些更改。但是,经过批准的仪表盘模型是您工作的良好开端。

Organize the Data Source for the Excel Dashboard

在 Excel 中构建仪表盘之前,您需要组织数据源。在 Excel 中,可以通过多种方式实现这一点 −

  1. 如果数据只是 Excel 表格,则从数据将更新到的工作簿中建立到您的工作簿的链接。

  2. 如果数据来自多个 Excel 表格,或者如果数据来自多个数据源,则在您的工作簿中构建数据模型是一个不错的选择。

您可以定期将数据导入工作簿,也可以建立数据连接以刷新数据,因为它得到更新,这取决于仪表盘是静态的还是动态的。

Set Up the Excel Dashboard Workbook

一旦您组织好数据,您需要构建您的工作簿。在工作簿中插入两到三个工作表 − 一个工作表用于您的仪表盘,一到两个工作表用于数据(数据或数据透视表/数据透视图或 Power View 报告,您可以隐藏它们)。这将帮助您组织和维护 Excel 工作簿。

Prepare the Data for the Excel Dashboard

基于您拥有的选择,即您对“初始准备”步骤中的问题的答案,为 Excel 仪表盘准备数据。数据可以是以下任何一项 −

  1. Results from data analysis

  2. Results from data exploration

  3. 计算输入数据所得的数据

  4. 来自数据透视表或 PowerPivot 表的数据汇总

Select the Dashboard Components

您已经了解了仪表盘中可以使用各种 Excel 功能。基于您对仪表盘的要求,为仪表盘组件选择以下任何 Excel 功能。

  1. Tables

  2. Sparklines

  3. Conditional Formatting.

  4. Charts

  5. Slicers

  6. Interactive Controls

  7. PivotTables

  8. PivotCharts

  9. PowerPivot Tables

  10. PowerPivot Charts

  11. Power View Reports

  12. KPIs

选择仪表盘组件将帮助您调整到您批准的仪表盘模型布局。

识别静态和动态组件以及要对 slicer 分组的组件(如果有)。

Identify Parts of the Dashboard for Highlighting

识别需要立即注意的仪表板部分,例如 % 已完成或当前状态。你可以为这些部分使用较大的字体、有冲击力的字体和字体颜色。

决定在仪表板中要加入多少颜色。可以根据仪表板受众来决定。如果仪表板是为高管和/或经理准备的,请选择能影响显示结果可视化的颜色。你可以添加仪表板背景颜色,使仪表板组件突出显示。你可以对相似的图表或相关结果使用相同的颜色代码。你也可以使用条件格式。

精心挑选仪表板部分进行突出显示,使仪表板发挥作用。

Build the Dashboard

这是创建 Excel 仪表板的关键且最后一步。此步骤包括利用 Excel 照相机高效地组装仪表板组件。你将在下一节中学习如何使用 Excel 照相机。

仪表板组件组装完毕后,给它进行最后润色 −

  1. 为仪表板添加标题。

  2. Incorporate a timestamp.

  3. 必要时,添加版权信息。

在接下来的一些章节中,你将学习如何根据一些示例使用这些步骤来创建仪表板。除了某些共性之外,仪表板并没有通用的规则或布局。一切都取决于你的要求。你的目标是制作一个有效的仪表板。

Using Excel Camera

Excel 照相机可以帮助你从工作表中截取快照,然后将它们放到不同的工作表中。例如,你可以在工作表上截取一个具有条件格式的表格,然后把它放到你的仪表板中。每当数据得到更新,仪表板将刷新以显示更改后的数据。

你可以按以下步骤将 Excel 照相机作为快速访问栏的一部分:

  1. 右键单击快速访问工具栏上的小箭头。

  2. 在“自定义快速访问工具栏”列表中,单击“更多命令”。

excel camera

“Excel 选项”对话框出现。

  1. 单击“快速访问工具栏”。

  2. 在“选择命令”下,选择“所有命令”。

  3. 在命令列表中单击“照相机”。

excel options
  1. 单击“添加 >>”按钮。照相机出现在右侧列表中。

add button
  1. 单击“确定”按钮。照相机图标会出现在工作簿快速访问工具栏中。

camera appears

你可以按以下步骤使用 Excel 照相机 −

  1. 选择要捕获的单元格范围。

  2. 单击快速访问工具栏上的相机。

单元格范围将显示虚线边框。

range cells
  1. 单击你要放置捕获区域的工作表上。它可以是你的仪表盘。

  2. 单击你要放置它所在的位置。

捕获区域出现在该点上。

captured region

无论何时你对原始数据进行更改,该更改都将反映在仪表盘中。

Date and Time Stamp on Excel Dashboard

你可以在仪表盘上加入日期或日期与时间戳,以显示数据上一次更新的时间。你可以使用 Excel 函数 TODAY() 和 NOW()。

若要加入日期戳,请输入 =TODAY(),在此单元格中你在数据工作表上放置日期戳。

date time stamp

无论何时工作簿更新,都将显示当前日期。

current date
  1. 确保你输入 TODAY() 函数的单元格格式化为你要显示的日期格式。

  2. 用相机捕获显示,并把它放在仪表盘上。

capture display

仪表盘上的日期将会反映工作簿上一次更新的日期。

你可以使用 NOW() 函数在仪表盘上加入日期和时间戳,方法类似。

  1. 输入 =NOW(),在此单元格中你在数据工作表上放置日期和时间戳。

date worksheet
  1. 确保你有合适的日期和时间格式。

  2. 用相机捕获显示,并把它放在仪表盘上。

日期和时间戳将加入仪表盘中,且会反映工作簿上一次更新的日期和时间。

Test, Sample, and Enhance the Dashboard

你需要测试仪表盘以确保它准确地显示数据。

  1. 在各种可能的情况下测试它。

  2. 测试确切的更新(静态或动态,目前是这种情况)。

  3. 测试交互式控件(如果有)。

  4. 测试外观和感觉。

您可能需要进行一些试运行以确保您的仪表盘符合您的要求。

下一步是让样本受众评估仪表盘,尤其是那些批准您的模型仪表盘的受众。由于他们将使用仪表盘,因此他们无疑会对仪表盘的使用和有效性提供意见。此反馈有助于您确保仪表盘的有效性。不要犹豫,征求反馈。

一旦获得反馈,就对反馈进行增强,如有必要,进行必要的更改。您的 Excel 仪表盘已准备就绪。

Share the Dashboard

您需要向目标受众提供 Excel 仪表盘。您可以通过多种方式进行此操作。

  1. 发送 Excel 仪表盘工作簿(您必须隐藏仪表盘工作表之外的工作表。您还可以保护工作簿)。

  2. 将 Excel 仪表盘工作簿保存在共享的网络驱动器上。

  3. Share the dashboard online.

如果 Excel 仪表盘是静态的,您可以通过邮件发送它,但如果是动态的或具有交互式控件,则它应该具有与后端数据的连接,因此需要在线共享。

您可以使用以下任何一种选项在线共享 Excel 仪表盘−

  1. Microsoft OneDrive。使用您的 Windows Live 帐户,您将获得 OneDrive 访问权限,您可以在其中发布和共享文档。

  2. New Microsoft Office Online.

  3. Microsoft SharePoint.

您还可以将 Excel 工作簿文件另存为 Acrobat Reader 文件 (.pdf) 并将其发布到 Web。但同样,此选项仅适用于静态仪表盘。

Tips for Effective Excel Dashboards

要使 Excel 仪表盘有效,您需要做某些事情并避免做另一些事情。以下是制作出色的 Excel 仪表盘的一些技巧:

  1. 简单明了。一个简单易懂的仪表盘比一个花哨的仪表盘更有效。请记住,需要强调的是数据。Glenna Shaw 认为,您需要在让仪表盘具有足够的吸引力以吸引受众注意力的同时,又不至于花哨到模湖显示的信息之间取得平衡。最好避免使用 3D 效果、渐变、额外的形状和不必要的工具。如果您能够使用条件格式或迷你图实现有力显示,则优先使用表格而非图表。

  2. 使用 Excel 条件格式。使用 Excel 条件格式提供了多种选择,可以根据表中的值自动更新。

  3. 选择适当的图表类型。请记住,使用图表类型没有通用的规则。有时,常规图表类型(如柱形图、条形图、甜甜圈图等)比出现的新型复杂图表更有力地传达信息。您可以使用 Excel 推荐图表命令来初步评估合适的图表类型。由于您可以使用 Excel 命令(更改图表类型)更改图表类型,因此您可以随意尝试,可视化显示并选择合适的图表。

  4. 使用交互式控件。使用交互式控件,如滚动条、选项(单选)按钮和复选框,可以帮助用户轻松有效地可视化数据的不同方面。

  5. 对大数据使用 Excel 数据模型。如果您有来自不同数据源的大型数据集,则最好使用 Excel 数据模型,它可以通过内存优化处理数千行数据,并可以将数据表分组并建立关系。

  6. 选择合适的颜色。颜色选择要谨慎。明智地使用颜色以提供足够的影响,但不可违背其目的。此外,如果受众可能包括色盲者,则避免使用红色和绿色。在这些情况下,虽然交通灯符号对正在显示的数据听起来有效,但它们不适合用于仪表板。改为使用灰度。

  7. 使用切片器。切片器比下拉列表更有效,因为它们具有视觉冲击力。您可以对图表、数据透视表、数据透视图进行分组以使用公共切片器。

  8. 将 Excel 仪表板组件组合在一起。您可以通过插入一个形状(例如矩形)并放置可以在该形状上分组的仪表板组件,为仪表板增添视觉趣味。例如,如果您正在使用公共切片器,您可以对所有共享该切片器的仪表板组件进行分组。

  9. 使用 Excel 数据层次结构。如果您的数据具有内在数据层次结构,请在数据模型中定义它们并使用它们对数据进行交互式向上钻取和向下钻取。

  10. 避免仪表板布局拥挤。请记住,显示过多的信息会让受众不知所措,并偏离实际目的。如果没有必要,请不要在仪表板中包含任何数据或图表。在测试仪表板时,这是一个重要的检查点。评估每个仪表板组件是否必要且足够。仪表板组件和布局应支持仪表板的单一目的。

Excel Dashboards - Examples

有几种可能的仪表板类型。除了某些可为或不可为之外,仪表板没有任何既定标准。您可以了解目的并且发挥您的想象力来选择仪表板的组件和布局。但是,您需要与仪表板的查看者保持一致,因此要照顾好他们的偏好才能让仪表板发挥效用。仪表板可以根据上下文和不断变化的需求随着时间的推移进行修改。

正如您在前面的章节中了解到的,仪表板的目的是有效地显示必要且足够的数据,并根据潜在受众的要求添加视觉冲击。仪表板及其组件的布局根据不同查看者的偏好在不同之处各不相同。

Example ─ Executive Dashboard

在本章中,您将了解示例性执行仪表板。同样,根据要求和偏好,此仪表板在不同公司之间可能会有所不同。

Key Metrics

执行仪表板通常关注业务绩效、收入、利润、新客户等等。因此,执行仪表板本质上显示 KPI。在这种情况下必要的视觉冲击是提供一目了然的结果,而不是太多细节,因为高管通常没有太多时间深入了解具体信息,除非绝对必要。

执行者可以是大公司中公司的负责人或特定部门的负责人。公司负责人可以是 CEO(首席执行官)或 MD(董事总经理)。执行者大多数情况下会对各部门的运营 KPI 和公司的整体绩效简介感兴趣。

Department-wise Operational KPIs

以下是部门及其运营 KPI 的一些示例 −

  1. Finance RevenueExpensesProfits

  2. Sales Region-wise performanceNew Customers

  3. Human Resources (HR) RecruitmentsAttrition

Excel Dashboard Structure

对于 KPI 的显示,大多数执行者仍然更喜欢仪表图而非目标图。在开始设计仪表板之前请确定偏好。

简单的 Excel 执行仪表板示例如下所示。

executive dashboard

Example - Project Management Dashboard

项目管理仪表板的目的是提供项目执行状态和要在一张快照中显示的突出项目功能。项目经理应使用此描绘的数据不仅用于项目监控,而且用于向高层管理人员和客户报告。

Key Metrics

项目管理中的关键指标如下 −

  1. Task completion status

  2. Risks status

  3. Issues status

  4. Project budget vs. actuals

Components for Project Overview

为了获得项目快照,以下主要组件将很有用 −

  1. 项目计划快照

  2. Tasks status by percentages

  3. 开放风险一览

Excel Dashboard Structure

示例 Excel 项目管理仪表板如下图所示。

project management

Example - Sales Management Dashboard

销售管理涉及按地区检查数据并按季度进行分析,以深入了解销售趋势和销售预测。这将有助于将销售业绩与竞争对手进行比较、评估销售团队、识别潜在的长处和短处以及规划未来。

Key Metrics

销售管理中涉及的关键指标如下 −

  1. 按地区和按月划分的总销售额

  2. 整个季度的销售趋势

  3. Sales forecasts

Components for Sales Management

要将上述指标表示在仪表板中,可以使用以下 Excel 功能 −

  1. 用于显示销售值和趋势(迷你图)的 Excel 表格。

  2. 带有簇状柱形图的销售额。

  3. 带有折线图和趋势线(线性)的销售趋势。

  4. 带有折线图和趋势线(线性预测)的销售预测。

Excel Dashboard Structure

示例销售管理仪表板如下所示 −

sales management

Example ─ Training Management Dashboard

培训管理通常有兴趣了解所花费的资金和培训覆盖范围,以便与竞争对手进行比较。此外,受训者提供的培训反馈将帮助管理层决定如何利用培训资源。

Key Metrics

培训管理中的关键指标如下 −

  1. Budget vs. Expenses.

  2. 培训时间 - 计划时间与实际时间。

  3. 培训覆盖范围 - 目标人数与实际培训人数。这可以表示为百分比。

  4. 培训反馈 – 对于进行的每一场培训,从受训人员处获取的平均反馈,以 1 - 5 等级标注(1 – 最低,5 – 最高)。

Components for Training Management Dashboard

您可以基于上述既定指标将以下 Excel 功能选择为仪表盘组件。

  1. 预算与开支 – 簇状条形图。

  2. 计划与实际培训时间 – 面积图。

  3. 培训覆盖率 – 作为与 100% 的目标相比的实际百分比的温度计图。

  4. 已进行培训的培训反馈 – 簇状柱形图。

Excel Dashboard Structure

示例培训管理 Excel 仪表盘如下所示 −

training

Example ─ Service Management / Support Dashboard

服务管理、支持或帮助台涉及接收服务单并尽快提供解决方案。因此,每天更新的仪表盘有助于提高服务管理,从而提升客户满意度。

Key Metrics

服务管理的关键指标如下 −

  1. 收到的单据数 – 按照支持人员划分的收到的单据数。

  2. 解决的单据数 – 按照支持人员划分的解决的单据数。

  3. 平均解决速度 – 按照支持人员划分 – 假定 8 小时工作。

  4. 解决百分比 – 按照支持人员划分的解决百分比。

  5. 收到的总单据数和解决的总单据数。

  6. Resolution %.

  7. 收到的平均满意度评分 – 按照支持人员划分的收到的平均满意度评分。

  8. Overall Satisfaction Score.

Components for Service Management Dashboard

可用于表示上述既定指标作为仪表盘组件的 Excel 功能如下 −

  1. 具有应用于 5 的条件格式的 1 到 5 的 Excel 表格。

  2. Bullet chart for 6.

  3. 7 的簇状条形图。

  4. Bullet chart for 8.

此外,您可以使用 Excel 函数 TODAY()包括与仪表盘中的数据对应的日期。

Excel Dashboard Structure

一个样例服务管理仪表盘可能如下图所示——

support dashboard

Dashboards - More Example

仪表盘可以用来显示任何需要关注的结果。你在前几章见过的示例仅仅是仪表盘被用于的各种应用中的精选。此外,仪表盘组件和相同用途的仪表盘布局也可以根据用户偏好而有所不同。因此,没有标准的仪表盘格式。

在本章中,你将对仪表盘被用来的一些更广泛的领域有一个简要了解。你可以用自己的想象力为你所拥有且需要提供的数据创建一个仪表盘。

Olympics Dashboard

你可以拥有一个显示奥运会数据数据分析结果的仪表盘。以下是用 Excel 数据模型和 Excel Power View 从 35000 多行数据创建的一个样例仪表盘。

olympics

Tourism Dashboard

一个关于某个特定网站的访问人数的样例旅游仪表盘可以如下图所示。

tourism

Hospital Management Dashboard

一个医院管理仪表盘是业务仪表盘的一种形式,其详细信息的级别由特定经理决定。以下给出在医院中使用的一个样例。

hospital management

Restaurant Dashboard

一个在餐厅中使用的一个样例仪表盘如下图所示。

restaurant dashboard

Sports Dashboard

体育场馆是仪表盘最常被使用的地方。每项体育运动都会有一个实时仪表盘,显示正在进行比赛的必要统计数据。以下给出样例仪表盘。

sports dashboard