Excel数据分析-数据可视化

您可以在 Excel 中以多种方式显示数据分析报告。但是,如果您的数据分析结果可以可视化为突出显示数据中值得注意的点的图表,则您的受众可以快速掌握您想要在数据中投影的内容。它还对您的演示风格产生了很好的影响。

在本章中,您将了解如何在图表上使用Excel图表和Excel格式设置功能,使您能够重点显示数据分析结果。

使用图表可视化数据

在 Excel 中,图表用于对任何数据集进行图形表示。图表是数据的可视化表示形式,其中数据由条形图中的条形图或折线图中的折线等符号表示。Excel 为您提供了许多图表类型,您可以选择一种适合您的数据的图表类型,也可以使用“Excel 推荐的图表”选项查看针对您的数据自定义的图表并选择其中一种。

有关图表类型的详细信息,请参阅教程 Excel 图表。

在本章中,您将了解可用于 Excel 图表的不同技术,以更有效地突出显示数据分析结果。

创建组合图

假设您拥有从不同地区获得的 2015-2016 财年的目标和实际利润。

创建组合图

我们将为这些结果创建一个簇状柱形图。

簇状柱形图

正如您所观察到的,很难在此图表中快速可视化目标与实际之间的比较。它不会对您的结果产生真正的影响。

区分两种类型的数据以比较值的更好方法是使用组合图。在Excel 2013和上述版本中,您可以使用组合图来实现相同的目的。

对目标值使用垂直列,对实际值使用带标记的线条。

  • 单击功能区上图表工具选项卡下的设计选项卡。
  • 单击类型组中的更改图表类型。将出现“更改图表类型”对话框。
使用垂直列
  • 单击组合。

  • 将“实际”系列的“图表类型”更改为“折线型标记”。预览将显示在“自定义组合”下。

  • 单击“确定”。

更改图表类型

将显示您的自定义组合图。

自定义组合图

正如您在图表中观察到的那样,目标值位于列中,实际值沿线标记。数据可视化变得更好,因为它还可以向您显示结果的趋势。

但是,当两个数据值的数据范围差异很大时,这种类型的表示形式效果不佳。

使用辅助轴创建组合图

假设您拥有从不同地区获得的 2015-2016 财年已发货产品单位数量和实际利润的数据。

组合图

如果您使用与以前相同的组合图,您将获得以下内容 -

组合图

在图表中,“单位数”的数据不可见,因为数据范围变化很大。

在这种情况下,您可以创建带有辅助轴的组合图,以便主轴显示一个范围,辅助轴显示另一个范围。

  • 单击插入选项卡。
  • 单击图表组中的组合。
  • 单击下拉列表中的创建自定义组合图。
自定义组合图

将出现“插入图表”对话框,并突出显示“组合”。

对于图表类型,选择 −


  • 系列编号的标记线单位数目



  • 序列实际利润的簇状柱



  • 选中系列号右侧的辅助轴框。,然后单击确定。


图表的预览将显示在“自定义组合”下。

自定义组合

您的组合图将显示为辅助轴。

带辅助轴的组合图

您可以在主轴上观察“实际利润”的值和“否”的值。次轴上的单位数。

上图中的一个重要观察结果是第 3 季度,其中没有。售出的单位更多,但实际利润较少。这可能归因于为增加销售额而发生的促销成本。情况在第 4 季度有所改善,销售额略有下降,实际利润大幅增加。

区分序列和类别轴

假设您要预测 2013-2016 年的实际利润。

区分序列和类别轴

为此数据创建聚集列。

簇状柱形图

如您所观察的,数据可视化效果无效,因为未显示年份。您可以通过将年份更改为类别来克服此问题。

删除数据区域中的标题年份。

删除标题年份

现在,年份被视为一个类别,而不是一个系列。您的图表如下所示 -

年份作为类别

图表元素和图表样式

图表元素为图表提供了更多描述,从而有助于更有意义地可视化数据。

  • 点击图表

图表右上角旁边出现三个按钮 -

  • 图表元素图表元素
  • 图表样式图表样式
  • 图表过滤器图表过滤器

有关这些的详细说明,请参阅 Excel 图表教程。

  • 单击图表元素。
  • 单击“数据标签”。
点击数据标签
  • 单击图表样式
  • 选择适合您的数据的样式和颜色。
选择样式和颜色

您可以使用趋势线以图形方式显示数据中的趋势。您可以将图表中的趋势线扩展到实际数据之外,以预测未来值。

趋势线

数据标签

Excel 2013 及更高版本为您提供了显示数据标签的各种选项。您可以选择一个数据标签,根据需要设置其格式,然后使用“克隆当前标签”将格式复制到图表中的其余数据标签。

图表中的数据标签可以具有不同的形状和大小的效果。

还可以将单元格的内容显示为带有“插入数据标签字段的数据标签”的一部分。

数据标签

快速布局

您可以使用快速布局通过选择预定义的布局选项之一来快速更改图表的整体布局。

  • 单击图表。
  • 单击图表工具下的设计选项卡。
  • 单击“快速布局”。

将显示不同的可能布局。当您继续使用布局选项时,图表布局将更改为该特定选项。

快速布局

选择您喜欢的布局。图表将以所选布局显示。

在柱形图中使用图片

您可以通过使用图片代替列来创建对数据表示的更多强调。

  • 单击柱形图上的柱形图。

  • 在“格式化数据系列”中,单击“填充”。

  • 选择图片。

  • 在“插入图片来源”下,提供文件名或剪贴板(如果之前已复制图像)。

使用图片

您选择的图片将代替图表中的列显示。

地点图片

波段图

您可能需要展示来自不同地区的产品的客户调查结果。波段图适用于此目的。波段图是添加了阴影区域的折线图,用于显示数据组的上限和下边界。

假设您来自东部和西部地区的客户调查结果,按月计算为 -

波段图

在这里,在数据中< 50% 为低,50% - 80% 为中,> 80% 为高。

使用波段图,您可以按如下方式显示调查结果 -

显示调查结果

根据数据创建折线图。

创建折线图

将图表类型更改为 −

  • 东系列和西系列与标记对齐。
  • 低、中、高系列到堆积柱。
更改图表类型

您的图表如下所示。

图表
  • 单击其中一列。
  • 在“设置数据系列格式”中将间隙宽度更改为 0%。
设置数据系列格式

您将获得波段而不是列。

乐队

为了使图表更美观 -

  • 添加图表标题。
  • 调整垂直轴范围。
  • 将波段的颜色更改为绿-黄-红。
  • 向波段添加标注。

最终结果是波段图,其中包含定义的边界和跨波段表示的调查结果。从图表中可以迅速清楚地看出,虽然西部地区的调查结果令人满意,但东部地区的调查结果在最后一个季度有所下降,需要引起注意。

波段图

温度计图表

当您必须表示目标值和实际值时,可以轻松地在Excel中创建温度计图表,突出显示这些值。

使用温度计图表,您可以按如下方式显示数据 -

温度计图表

按如下所示排列数据 -

排列您的数据
  • 选择数据。
  • 创建簇状柱形图。
簇状图

正如您所观察到的,右侧列是目标。

  • 单击图表中的列。
  • 单击功能区上的切换行/列。
目标
  • 右键单击目标列。
  • 单击格式化数据系列。
  • 单击辅助轴。
辅助轴

当您观察到主轴和辅助轴具有不同的范围时。

  • 右键单击主轴。
  • 在“设置轴格式”选项的“边界”下,键入 0 作为最小值,键入 1 作为最大值。
  • 对辅助轴重复相同的操作。
主轴和次轴具有不同的范围

主轴和辅助轴都将设置为 0% - 100%。目标列隐藏实际列。

  • 右键单击可见列(目标)
  • 在“设置数据系列格式”中,选择
    • 没有填充
    • 边框实线
    • 蓝色代表颜色
目标列
  • 在“图表元素”中,取消选择
    • 轴→主水平
    • 轴→次垂直
    • 网格线
    • 图表标题
  • 在图表中,右键单击主垂直轴
  • 在“格式轴”选项中,单击刻度线
  • 对于“主要类型”,选择“内部”
格式轴
  • 右键单击图表区。
  • 在“设置图表区格式”选项中,选择
    • 没有填充
    • 没有边界线
图表区

调整图表区的大小,以获得温度计的形状。

温度计形状

您得到了温度计图表,显示了实际值与目标值的关系。您可以通过一些格式使此温度计图表更令人印象深刻。

  • 插入一个矩形形状,在图表中叠加蓝色矩形部分。
  • 在“设置形状格式”选项中,选择 −
    • 填充的渐变填充
    • 线性类型表示
    • 1800 代表角度
  • 将渐变色标设置为 0%、50% 和 100%。
  • 对于渐变停止点为 0% 和 100%,请选择黑色。
  • 对于渐变停止点为 50%,请选择白色。
设置形状格式
  • 在底部插入一个椭圆形。
  • 使用相同的选项设置形状格式。

结果就是我们开始的温度计图表。

温度计图表结果

甘特图

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

在 Excel 中,您可以通过自定义堆积条形图类型来创建甘特图,以便它描述任务、任务持续时间和层次结构。Excel 甘特图通常使用天数作为沿水平轴的时间单位。

考虑以下数据,其中列 -

  • 任务表示项目中的任务
  • “开始”表示从项目“开始日期”算起的天数
  • 工期表示任务的工期

请注意,任何任务的开始都是上一个任务的开始 + 持续时间。当任务位于层次结构中时,就是这种情况。

甘特图
  • 选择数据。
  • 创建堆积条形图。
堆积条形图
  • 右键单击“开始系列”。
  • 在“设置数据系列格式选项”中,选择“无填充”。
设置数据系列格式
  • 右键单击类别轴。
  • 在“设置轴格式选项”中,选择“按相反顺序排列的类别”。
选择类别
  • 在“图表元素”中,取消选择
    • 传说
    • 网格线
  • 将水平轴的格式设置为
    • 调整范围
    • 每 5 天显示一次主要刻度线
    • 每隔 1 天出现一次小刻度线
  • 设置数据系列的格式,使其看起来令人印象深刻
  • 给出图表标题
图表标题

瀑布图

瀑布图是小型和大型企业中最受欢迎的可视化工具之一。瀑布图非常适合通过分解正负贡献的累积效应来显示您如何得出净值,例如净收入。

Excel 2016 提供瀑布图类型。如果您使用的是早期版本的 Excel,您仍然可以使用堆积柱形图创建瀑布图。

这些列采用颜色编码,以便您可以快速区分正数和负数。初始值和最终值列从水平轴开始,而中间值是浮动列。由于这种外观,瀑布图也称为桥接图。

请考虑以下数据。

瀑布图
  • 准备瀑布图的数据

  • 确保“净现金流”列位于“月”列的左侧(这是因为您在创建图表时不会包含此列)

  • 添加 2 列 – 分别增加和减少正现金流和负现金流

  • 添加一列 开始 - 图表中起始值在净现金流中的第一列

  • 添加列 End - 图表中结束值在净现金流中的最后一列

  • 添加支持中间列的浮动列 –

  • 按如下方式计算这些列的值

计算值
  • 在“浮点型”列的开头和结尾插入一行。放置 n 任意值 50000。这只是为了在图表的左右两侧留出一些空间

数据如下。

浮点柱
  • 选择单元格 C2:H18(不包括净现金流列)
  • 创建堆积柱形图
堆积柱形图
  • 右键单击浮动系列。
  • 单击“设置数据系列格式”。
  • 在“设置数据系列格式选项”中,选择“无填充”。
选择无填充
  • 右键单击负系列。
  • 选择“将颜色填充为红色”。
负极系列
  • 右键单击正系列。
  • 选择“将颜色填充为绿色”。
正极系列
  • 右键单击开始系列。
  • 选择“将颜色填充为灰色”。
  • 右键单击结束系列。
  • 选择“将颜色填充为灰色”。
  • 删除图例。
删除图例
  • 右键单击任何系列
  • 在“设置数据系列格式选项”中,在“系列选项”下选择“间隙宽度”作为 10%
系列选项

给出图表标题。将显示瀑布图。

解说瀑布图

迷你图

迷你图是放置在单个单元格中的小图表,每个图表代表所选内容中的一行数据。它们提供了一种快速查看趋势的方法。

您可以使用快速分析工具添加迷你图。

  • 选择要为其添加迷你图的数据。
  • 在迷你图的数据右侧保留一个空列。
迷你图

“快速分析”按钮“快速分析工具”按钮显示在所选数据的右下角。

底部的快速分析按钮
  • 单击“快速分析“快速分析工具”按钮”按钮。此时将显示“快速分析工具栏”,其中包含各种选项。

快速分析工具栏选项

单击迷你图。显示的图表选项基于数据,可能会有所不同。

单击迷你图

单击“”。每行的折线图显示在数据右侧的列中。

线

数据透视图

数据透视图用于以图形方式汇总数据和浏览复杂数据。

数据透视图以与标准图表相同的方式显示数据系列、类别和图表轴。此外,它还为您提供了图表上的交互式筛选控件,以便您可以快速分析数据的子集。

当大型数据透视表中有数据或包含许多包含文本和数字的复杂工作表数据时,数据透视图非常有用。数据透视图可以帮助您理解此数据。

您可以从以下位置创建数据透视图

  • 数据透视表。
  • 不带数据透视表的独立数据表。

数据透视表中的数据透视图

要创建数据透视图,请按照以下步骤操作 -

  • 单击数据透视表。
  • 单击功能区上“数据透视表工具”下的“分析”。
  • 单击数据透视图。将出现“插入图表”对话框。
插入图表

从选项列中选择簇状列。

选择簇状柱形图

单击“确定”。将显示数据透视图。

显示数据透视图

数据透视图有三个筛选器 - 区域、销售人员和月份。

  • 单击区域筛选器控制选项。此时将显示搜索框,其中包含所有区域的列表。复选框显示在区域旁边。

  • 选择“东”和“南”选项。

滤波器

筛选后的数据同时显示在数据透视图和数据透视表上。

不带数据透视表的数据透视图

不带数据透视表的数据透视图

您可以创建独立的数据透视图,而无需创建数据透视表。

  • 单击数据表。
  • 单击插入选项卡。
  • 单击“图表”组中的数据透视图。将出现“创建数据透视图”窗口。
  • 选择表/范围。
  • 选择要放置数据透视图的位置。

您可以在现有工作表本身或新工作表中选择一个单元格。单击“确定”。

选择单元格

空数据透视图和空数据透视表与数据透视图字段列表一起显示,以生成数据透视图。

空数据透视表
  • 选择要添加到数据透视图的字段

  • 通过将字段拖动到过滤器、图例(系列)、轴(类别)和值中来排列字段

  • 使用数据透视图上的筛选器控件选择要放置在数据透视图上的数据

Excel 将自动创建耦合的数据透视表。

耦合数据透视表