Excel 数据分析 - 数据可视化

---

---

您可以在 Excel 中通过多种方式显示数据分析报告。但是，如果您的数据分析结果可以可视化为突出显示数据中显著点的图表，您的受众可以快速掌握您想在数据中表达的内容。它还会给您的演示风格留下良好的影响。

在本章中，您将了解如何使用 Excel 图表和 Excel 图表格式化功能，使您能够重点呈现数据分析结果。

使用图表可视化数据

在 Excel 中，图表用于对任何数据集进行图形表示。图表是对数据的视觉表示，其中数据由符号表示，例如柱形图中的条形或折线图中的线条。Excel 为您提供了许多图表类型，您可以选择适合您数据的图表，或者可以使用 Excel 推荐图表选项查看自定义到您数据的图表并从中选择一个。

有关图表类型的更多信息，请参阅教程 Excel 图表。

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

创建组合图表

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

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

如您所见，在这个图表中很难快速直观地比较目标和实际值。它不会对您的结果产生真正的影响。

区分两种数据以比较值的更好方法是使用组合图表。在 Excel 2013 及更高版本中，您可以使用组合图表来实现相同目的。

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

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

• 单击“组合”。

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

• 单击“确定”。

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

如您在图表中观察到的，目标值位于列中，实际值沿线标记。数据可视化效果得到了改善，因为它还向您展示了结果的趋势。

但是，当两个数据值的取值范围差异很大时，这种表示方式效果不佳。

创建带辅助轴的组合图表

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

如果您使用与之前相同的组合图表，您将得到以下结果 -

在图表中，“单位数量”的数据不可见，因为数据范围差异很大。

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

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

将出现“插入图表”对话框，其中“组合”突出显示。

对于图表类型，选择 -

• “单位数量”系列的“带标记的折线”

• “实际利润”系列的“聚类柱形”

• 选中“单位数量”系列右侧的“辅助轴”框，然后单击“确定”。

自定义组合下会出现图表预览。

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

您可以观察主轴上的“实际利润”值和辅助轴上的“单位数量”值。

上图中一个重要的观察结果是第三季度，销量更多，但实际利润较少。这可能是由于为提高销量而产生的促销成本造成的。第四季度的状况有所改善，销量略有下降，但实际利润大幅上升。

区分系列和分类轴

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

为此数据创建一个聚类柱形图。

如您所见，数据可视化效果不佳，因为年份没有显示。您可以通过将年份更改为类别来克服此问题。

删除数据范围中的年份标题。

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

图表元素和图表样式

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

• 单击图表

图表右上角附近会出现三个按钮 -

• 图表元素
• 图表样式
• 图表筛选器

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

• 单击“图表元素”。
• 单击“数据标签”。

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

您可以使用趋势线以图形方式显示数据中的趋势。您可以在图表中扩展趋势线以超出实际数据，以预测未来值。

数据标签

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

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

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

快速布局

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

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

将显示不同的可能布局。当您移动到布局选项时，图表布局将更改为该特定选项。

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

在柱形图中使用图片

您可以通过在列的位置使用图片来更加强调数据演示。

• 单击柱形图上的一个列。

• 在“设置数据系列格式”中，单击“填充”。

• 选择“图片”。

• 在“从插入图片”下，提供文件名或可选的剪贴板（如果您之前复制过图像）。

您选择的图片将出现在图表中列的位置。

带状图

您可能需要展示来自不同地区的某个产品的客户调查结果。带状图适合此目的。带状图是带有一个附加阴影区域的折线图，用于显示数据组的上限和下限。

假设您来自东西部地区的月度客户调查结果为 -

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

使用带状图，您可以如下显示调查结果 -

从您的数据创建一个折线图。

将图表类型更改为 -

• “东部”和“西部”系列的“带标记的折线”。
• “低”、“中”和“高”系列的“堆积柱形”。

您的图表如下所示。

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

您将获得带状而不是列。

为了使图表更具表现力 -

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

最终结果是带状图，其中定义了边界，并在带状图中表示了调查结果。人们可以从图表中快速清楚地看出，虽然西部地区的调查结果令人满意，但东部地区的调查结果在最后一个季度有所下降，需要引起注意。

温度计图表

当您需要表示目标值和实际值时，您可以轻松地在 Excel 中创建温度计图表，该图表以强调的方式显示这些值。

使用温度计图表，您可以如下显示数据 -

按如下所示排列您的数据 -

• 选择数据。
• 创建聚类柱形图。

如您所见，右侧的列是目标。

• 单击图表中的一个列。
• 单击功能区上的“切换行/列”。

• 右键单击“目标”列。
• 单击“设置数据系列格式”。
• 单击“辅助轴”。

如您所见，主轴和辅助轴具有不同的范围。

• 右键单击主轴。
• 在“设置轴格式”选项中，“边界”下，键入 0 作为最小值，1 作为最大值。
• 对辅助轴重复相同的操作。

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

• 右键单击可见列（目标）
• 在“设置数据系列格式”中，选择
  • 填充为“无填充”
  • 边框为“实线”
  • 颜色为“蓝色”

• 在“图表元素”中，取消选中
  • 轴 → 主水平轴
  • 轴 → 副垂直轴
  • 网格线
  • 图表标题
• 在图表中，右键单击主垂直轴
• 在“设置轴格式”选项中，单击“刻度线”
• 对于“主要刻度线类型”，选择“内部”

• 右键单击图表区域。
• 在“设置图表区域格式”选项中，选择
  • 填充为“无填充”
  • 边框为“无线条”

调整图表区域大小，使其呈现温度计形状。

您得到了温度计图表，显示了实际值与目标值的对比。您可以通过一些格式设置使这个温度计图表更具吸引力。

• 插入一个矩形形状，覆盖图表中的蓝色矩形部分。
• 在“设置形状格式”选项中，选择 -
  • 填充为“渐变填充”
  • 类型为“线性”
  • 角度为180⁰
• 将渐变停止点设置为0%、50%和100%。
• 对于0%和100%处的渐变停止点，选择黑色。
• 对于50%处的渐变停止点，选择白色。

• 在底部插入一个椭圆形。
• 使用相同的选项设置形状格式。

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

甘特图

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

在Excel中，您可以通过自定义堆积条形图类型来创建甘特图，使其能够描绘任务、任务持续时间和层次结构。Excel甘特图通常使用天作为横轴上的时间单位。

考虑以下数据，其中列 -

• 任务表示项目中的任务
• 开始表示从项目开始日期起的天数
• 持续时间表示任务的持续时间

请注意，任何任务的开始时间等于前一个任务的开始时间加上持续时间。当任务具有层次结构时，情况就是这样。

• 选择数据。
• 创建堆积条形图。

• 右键单击“开始”系列。
• 在“设置数据系列格式”选项中，选择“无填充”。

• 右键单击类别轴。
• 在“设置轴格式”选项中，选择“以相反顺序显示类别”。

• 在“图表元素”中，取消选中
  • 图例
  • 网格线
• 将横轴格式设置为
  • 调整范围
  • 主要刻度线间隔5天
  • 次要刻度线间隔1天
• 设置数据系列格式使其更具吸引力
• 添加图表标题

瀑布图

瀑布图是中小型企业中最流行的可视化工具之一。瀑布图非常适合显示如何通过分解正负贡献的累积效应来获得净值（如净收入）。

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

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

考虑以下数据。

• 准备瀑布图的数据

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

• 添加两列 - 分别用于正现金流和负现金流的“增加”和“减少”

• 添加一列“开始” - 图表中的第一列，其中包含“净现金流”中的起始值

• 添加一列“结束” - 图表中的最后一列，其中包含“净现金流”中的结束值

• 添加一列“浮动” - 支持中间列

• 计算这些列的值，如下所示

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

数据如下所示。

• 选择单元格C2:H18（排除“净现金流”列）
• 创建堆积柱形图

• 右键单击“浮动”系列。
• 单击“设置数据系列格式”。
• 在“设置数据系列格式”选项中，选择“无填充”。

• 右键单击“负”系列。
• 选择红色作为填充颜色。

• 右键单击“正”系列。
• 选择绿色作为填充颜色。

• 右键单击“开始”系列。
• 选择灰色作为填充颜色。
• 右键单击“结束”系列。
• 选择灰色作为填充颜色。
• 删除图例。

• 右键单击任何系列
• 在“设置数据系列格式”选项的“系列选项”下，将“间隙宽度”设置为10%。

添加图表标题。将显示瀑布图。

迷你图

迷你图是放置在单个单元格中的微型图表，每个图表代表您选择中的一行数据。它们提供了一种快速查看趋势的方法。

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

• 选择要添加迷你图的数据。
• 在数据的右侧保留一列空白用于迷你图。

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

• 单击快速分析 按钮。快速分析工具栏将显示各种选项。

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

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

数据透视图

数据透视图用于以图形方式汇总数据并探索复杂数据。

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

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

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

• 数据透视表。
• 作为独立的数据表，无需数据透视表。

从数据透视表创建数据透视图

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

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

从“柱形图”选项中选择“簇状柱形图”。

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

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

• 单击“区域”筛选器控件选项。搜索框将显示所有区域的列表。区域旁边将显示复选框。

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

筛选后的数据将显示在数据透视图和数据透视表中。

无需数据透视表的数据透视图

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

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

您可以选择现有工作表中的某个单元格，或新工作表中的某个单元格。单击“确定”。

将出现一个空的数据透视图和一个空的数据透视表，以及用于构建数据透视图的数据透视图字段列表。

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

• 通过将字段拖放到“筛选器”、“图例（系列）”、“轴（类别）”和“值”中来排列字段

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

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