Excel Data Analysis 简明教程

Excel Data Analysis - Data Visualization

您可以在 Excel 中以多种方式显示数据分析报告。但是，如果您的数据分析结果可以可视化为图表，以突出数据中的要点，您的受众可以快速掌握您想要在数据中展示的内容。它还对您的演示文稿样式产生良好影响。

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

Visualizing Data with Charts

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

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

在本章中，您将了解可以使用 Excel 图表的高亮显示数据分析结果的不同技术。

Creating Combination Charts

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

creating combination charts

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

clustered column chart

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

区分两种类型数据以比较值的一种更佳方法是使用组合图表。在 Excel 2013 及更高版本中，您可以将组合图表用于相同目的。

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

  1. 在功能区的图表工具选项卡下点击设计选项卡。

  2. 在类型组中点击更改图表类型。更改图表类型对话框将出现。

use vertical columns

  1. Click Combo.

  2. 将实际系列的图表类型更改为带有标记的直线。预览将出现在自定义组合下。

  3. Click OK.

change chart type

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

customized combination chart

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

但是，当两种数据值的数据范围有显着差异时，这种类型的表示效果不佳。

Creating a Combo Chart with Secondary Axis

假设您有 2015-2016 财政年度不同地区所发货的产品数量和实际利润数据。

combo chart

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

combination chart

在图表中， No. of Units 的数据不可见，因为数据范围有显着差异。

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

  1. Click the INSERT tab.

  2. 点击图表组中的组合。

  3. 从下拉列表中点击创建自定义组合图表。

custom combo chart

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

对于图表类型，选择 -

标记线用于单位数量系列

聚类柱用于实际利润系列

勾选单位数量系列右侧的辅助轴框并点击确定。

图表预览将出现在自定义组合中。

custom combination

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

combo chart with secondary axis

您可以在主轴上看到实际利润值，在辅轴上可以看到单位数。

图表中的一个显著观察结果是第 3 季度，其中售出的单位数更多，但获得的实际利润更低。这可能应该归因于为增加销售而产生的促销成本。第 4 季度的情况有所改善，销售额略有下降，实际利润大幅上升。

Discriminating Series and Category Axis

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

discriminating series and category axis

为此数据创建一个簇状柱形图。

clustered column

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

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

remove header year

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

year as category

Chart Elements and Chart Styles

图表元素为您的图表提供了更多描述，从而帮助更直观地可视化数据。

  1. Click the Chart

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

  1. Chart Elements

  2. Chart Styles

  3. Chart Filters

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

  1. Click Chart Elements.

  2. Click Data Labels.

click data labels

  1. Click Chart Styles

  2. 选择适合您数据的样式和颜色。

select style and color

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

trendline

Data Labels

Excel 2013 及更高版本为您提供了多种选项来显示数据标签。您可以选择一个数据标签，按您喜欢的方式设置格式，然后使用克隆当前标签将格式复制到图表中的其他数据标签。

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

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

data label

Quick Layout

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

  1. Click the chart.

  2. 单击图表工具下方的设计选项卡。

  3. Click Quick Layout.

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

quick layout

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

Using Pictures in Column Charts

使用图片代替列，可以更强调您的数据展示。

  1. 单击柱形图上的柱形。

  2. 在“数据系列格式”中，单击“填充”。

  3. Select Picture.

  4. 在“从下列位置插入图片”下，提供文件名或（如果您以前复制了图像）剪贴板。

using pictures

您选择图片会出现在图表中的列中。

place pictures

Band Chart

您可能需要显示来自不同区域的产品的客户调查结果。带状图适合此目的。带状图是线形图，带有阴影区域，可显示组数据的上限和下限。

假设您东部和西部地区的客户调查结果按月分布为 -

band chart

< 50% 的数据为低，50% - 80% 为中等，> 80% 为高。

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

display survey results

根据您的数据创建一个线形图。

create line chart

将图表类型改为 -

  1. 东部和西部系列为带有标记的线。

  2. 低、中等和高系列为堆积柱形。

change the chart type

您的图表如下所示。

chart looks

  1. 单击其中一个列。

  2. 在“数据系列格式”中将间隙宽度更改为 0%。

formating data series

您将获得带状而不是列。

bands

让图表更美观 -

  1. Add Chart Title.

  2. Adjust Vertical Axis range.

  3. 将条带颜色改为绿-黄-红。

  4. Add Labels to bands.

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

band charts

Thermometer Chart

如果需要表示目标值和实际值，你可以轻松地在 Excel 中创建温度计图表，该图表可以直观地显示这些值。

使用温度计图表，你可以以下列方式显示数据——

thermometer chart

按照以下方式排列数据——

arrange your data

  1. Select the data.

  2. 创建簇状条形图。

clustered charts

你会观察到，右侧的条形图就是目标。

  1. 单击图表中的一个条形图。

  2. 单击功能区上的“切换行/列”。

target

  1. 右键单击目标列。

  2. 单击“设置数据系列格式”。

  3. Click on Secondary Axis.

secondary axis

你会观察到主要轴和次要轴的范围不同。

  1. 右键单击主要轴。

  2. 在“设置轴”选项中，在“界限”下方，为最小值输入 0，为最大值输入 1。

  3. 对次要轴重复该操作。

primary and secondary axis have different ranges

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

  1. 右键单击可见列（目标）

  2. 在“设置数据透视图”中，为“填充”选择无填充，为“边框”选择线条，为“颜色”选择蓝色

target column

  1. 在“图表元素”中，取消选中以下选项：“轴→主要水平轴→次垂直网格线→图表标题”

  2. 在图表中，右键单击主要垂直轴

  3. 在“设置轴”选项中，单击“刻度线”

  4. 对于主要类型，选择“内部”

format axis

  1. 右键单击图表区域。

  2. 在“格式化图表区域”选项中，选择填充无，边框无线。

chart area

调整图表区域大小，获得温度计形状。

thermometer shape

获得您的温度计图表，显示实际值和目标值。您可以通过一些格式设置使此温度计图表更加引人注目。

  1. 插入一个矩形形状，覆盖图表中的蓝色矩形部分。

  2. 在“格式化形状”选项中，选择：填充渐变，类型线性，角度 1800

  3. 将渐变停止点设置在 0%、50% 和 100%。

  4. 对于位于 0% 和 100% 的渐变停止点，选择颜色黑色。

  5. 对于位于 50% 的渐变停止点，选择颜色白色。

format shape

  1. 在底部插入一个椭圆形。

  2. 使用相同选项设置形状。

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

thermometer chart result

Gantt Chart

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

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

请考虑以下数据，其中该列：

  1. 任务代表项目中的任务

  2. 开始代表距离项目开始日期的天数

  3. 持续时间代表任务持续时间

请注意，任何任务的开始都是前一个任务的开始时间加上持续时间。当任务属于分层级结构时就是这种情况。

gantt chart

  1. Select the data.

  2. Create Stacked Bar Chart.

stacked bar chart

  1. Right-click on Start Series.

  2. 在格式化数据序列选项中，选择无填充。

format data series

  1. Right-click on Categories Axis.

  2. 在“格式轴”选项中，选择“按逆序分类”。

select categories

  1. 在“图表元素”中，取消选中“图例网格线”。

  2. 设置水平轴格式，以调整主要刻度标记（5 天间隔）和次要刻度标记（1 天间隔）。

  3. 设置数据系列格式，使其更具观感。

  4. Give a Chart Title

chart title

Waterfall Chart

瀑布图是小型的和大企业最流行的可视化工具之一。瀑布图通过剖析正负贡献的累积效应，是展示如何达成净值（如净收入）的理想之选。

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

柱形以彩色编码，以便您可以快速识别正数和负数。初始值和最终值柱形从横轴开始，而中间值则是浮动柱形。由于这种外观，瀑布图也称为桥形图。

考虑以下数据。

waterfall chart

  1. 为瀑布图准备数据

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

  3. 添加 2 列——分别代表正现金流的“增加”和负现金流的“减少”

  4. 添加一列“开始”——图表的第一列，其中包含净现金流内的起始值

  5. 添加一列“结束”——图表中的最后一列，其中包含净现金流内的结束值

  6. 添加一列“浮动”——它支持中间列

  7. 如下所示计算这些列的值：

compute values

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

数据将如下所示。

float column

  1. 选择单元格 C2:H18（排除“净现金流”列）

  2. Create Stacked Column Chart

stacked column chart

  1. 右键单击“浮动”序列。

  2. Click Format Data Series.

  3. 在格式化数据序列选项中，选择无填充。

select no fill

  1. 右键单击“负”序列。

  2. 选择填充颜色为红色。

negative series

  1. 右键单击“正”序列。

  2. 选择填充颜色为绿色。

positive series

  1. 右键单击开始系列。

  2. 选择灰色的填充颜色。

  3. 右键单击结束系列。

  4. 选择灰色的填充颜色。

  5. Delete the Legend.

delete legend

  1. 右键单击任意系列

  2. 在格式化数据系列选项中，在系列选项下选择间隙宽度为 10%。

series options

为图表命名。瀑布图将显示。

deplays waterfall chart

Sparklines

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

您可以使用快速分析工具添加火花线。

  1. 选择您要添加火花线的数据。

  2. 在数据的右侧保留一个空列，用于火花线。

sparkline

快速分析按钮显示在您选定数据的右下角。

quick analysis button at bottom

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

quick analysis toolbar options

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

click sparklines

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

line

PivotCharts

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

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

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

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

  1. A PivotTable.

  2. 作为独立数据表，不包含数据透视表。

PivotChart from PivotTable

遵循以下步骤创建枢纽图表 −

  1. Click the PivotTable.

  2. 单击功能区上“透视表工具”下的“分析”。

  3. 单击“枢纽图表”。弹出插入图表对话框。

insert chart

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

select clustered column

单击确定。显示枢纽图表。

display pivotchart

枢纽图表有三个筛选器——地区、销售人员和月份。

  1. 单击“区域筛选器控制”选项。出现搜索框，其中包含所有地区的列表。选中框显示在地区旁边。

  2. 选择“东部”和“南部”选项。

filter

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

pivotchart without pivottable

PivotChart without a PivotTable

您可以在不创建数据透视表的情况下创建单独的枢纽图表。

  1. Click the Data Table.

  2. Click the Insert tab.

  3. 单击“图表”组中的“枢纽图表”。出现创建枢纽图表窗口。

  4. Select the Table/Range.

  5. 选择您希望放置枢纽图表的“位置”。

您可以在现有工作表本身的单元格中，或者在新的工作表中进行选择。单击确定。

choose cell

空的枢纽图表、空的数据透视表和用于构建枢纽图表的枢纽图表字段列表同时出现。

empty pivottable

  1. 选择要添加到枢纽图表的字段

  2. 通过将字段拖动到 FILTER、图例（系列）、AXIS（类别）和 VALUE 中，对字段进行整理

  3. 使用枢纽图表上的筛选器控件来选择要放在枢纽图表上的数据

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

coupled pivottable