Excel数据分析教程 第6章上机题及课后习题

上机题6

1、双轴图

工作当中特别是财务、市场调查工作，在做财务报表的时候有时候需要制作

很复杂的图形来展示数据的走势情况，而且需要用到两坐标轴甚至四坐标轴图表,

即双X轴双Y轴的图表，怎样来制作呢？

Excel2016中双坐标实现的方式很简单，比如在图表中我们要表达商品卖出

的数量和销售额随口期的变化情况，基础数据如图所小：

A

B|C

期

1日

销量：个销售额：元

21

32253043

43482621

54313250

654934290867

7364257

8274500

9363564

107月9日202723

数据得到之后先看看单坐标轴的效果。就以折线图为例，效果如下:

图表标题

6000

5000

4000/---------

3000

2000-------希生个

1000----销售额：元

0■・

这个图表的缺点显而易见，由于销售量和销售额相差太大，在单坐标轴图里

基本就看不出销售量的变化。

现在就需要在这张图上修改，选中“数量”那条线，右击，选择“设置数据

系统格式”，如下图所示：

删除⑼

宣重设以匹配样式(A)

更改系列图表类型①…

选择数据但…

——o——

图表标题□三维旋转⑻…

添加麒蹄圜

添加趋势线(E)…

设置数据系列格式⑹…

W存舞W毋88W-毅一-%器--焚-

---------------1-------------O-----------系列"销量：个、

填充边框

在弹出的的“设置数据系列格式”窗格中，依次选择“系列选项|次坐标轴”,

最后点击“关闭”。

设置数据系列格式

系列选项▼

AQill

/系列选项

系列绘制在

O坟制⑻

•侬ma⑤

这样就在刚才的图表中又添加了一个坐标轴「数量”的变化也很清晰可见,

这就是双坐标轴的优点。

图表标题

600060

5000人50

黑北

—箱售额：元

2000、20

100010销量：个

0)---1---1---!----1---!---1---I---!---.0

mcnnitiininioitniii

9rX86

稍加修饰一下，就变成很漂亮的一个图表。

2、复合饼图设计应用

这里简单介绍一下Excel中几种饼图的应用方法。饼图在日常工作中图表的

应用极为广泛，若要熟练的掌握这些应用，确不是一件容易的事。为了在教学中

能熟练向学生讲解这一知识应用，下面就一个简单的实例来设计几种不同的讲图

用法来展示图表设计中具有的表达特性，尤其是对时间、空间等概念的表达和一

些抽象思维的表达，具有文字和言辞无法取代的传达效果。实例工作表如图所示:

2015年12月三大产品家电下乡销售量统计

1

2指定销售点产品名销售量

3青义店彩电91青义350

4冰箱70龙门295

5手机189方水558

6龙门店

7

3

9方水店

10

11210

复合饼图是将饼图分成两部分，一部分是来了解家电下乡总的销售情况比例

图，另一部分是单独拿出来的其中一个销售点的销售情况，做成一个小饼图以便

查看得更清楚。

具体操作步骤如下：

A、创建饼图：选中数据区域A3:C11单元格，选择“插入”选项中的“图

表，，组中的“饼图।复合饼图”（有的版本叫子母饼图），生成“复合饼图”：

在“标签包括”组中将其中的“值”复选框清除，再选中“类别名称”和“百

分比”复选框；在“标签位置”组中将默认的“最佳匹配”单选按钮改为“数据

标签外”，效果如下图所示：

此时，在图中，小饼都属于大饼中的“其他”，这是Excel自动生成的一个称

谓，两次单击“其他”，使其进入编辑状态，将“其他”改成“方水店”，退出编

辑。另外,有些标签可能与色块有重合，两次单击该标签可拖动标签调整其位置。

最终效果如图：

家电下乡销售量复合饼图

xns于机

6%诗义店彩电

8%

C、调整饼图：复合饼图中，小饼图形对象是nJ以选择的，用鼠标单击讲图

中任意一块，再右击，在快捷菜单中选择“设置数据系列格式”，会弹出“设置

数据系列格式”窗格，点击“系列选项”选项卡：

设置数据系列格式▼X

助选18▼

今aIII

/蔡外选IB

系列给能在

硒分IW36凹I皿T

第二绘囹区中的值(D巨：

漏朗分唐凶

1______________________________________________.

1▼

100%.

▼

26二建甥区大小($)

75%

其中最重要的是“系列分割依据”下拉列表框，默认是“位置”选项，且“第

一绘图区中的值”为3,这两项的意思就是选择位置在数据表中最后3项的数据

构成第二绘图区，本例中即正好是“方水店”的3个值。但最后究竟需要选儿项，

其值是可以选择的，另外，“系列分割依据”下拉列表框中还有“值”、“百分比”、

“自定义”三项可选，如果选择“值”或“百分比”，则会根据“第二绘图区中

的值”来决定把大饼中的最后几个值或最后几个百分比放在小饼中；如果选择“自

定义”，则可以任意点击某色块，在“点属于”后面的下拉列表框中将该色块在

第一绘图区（大饼）和第二绘图区（小饼）中任意切换：

两分岫国£）

嗫■于

京力2）

此外，还有“饼图分离程度”（每块饼中各色块的离散度）、分类间距（两饼

之间的间隔）、“笫二绘图区的大小”（小饼占大饼的百分比）等三个值可以进行

调整。

3、动态饼图设计应用

在进行报表分析时，需要显示不同公司、产品、年度等图表，通常是用多个

图表才能完成，因此重复劳动很多，若设计使用动态图表，这个问题将迎刃而解，

从而大大的提高了工作效率。本例是以销售点来统计各商品的销售量为数据对象

设计的动态饼图应用。实例工作表如图所示：

/ABIC

年月三大产品家电下乡销售量统计

1201912

产名

品

2指定销售点销售量

3青义店91

470

5189

均

-店

6J105

755

8135

方水店

9235

10113

11210

①整理数据应该是这样的:

ABCIDIE

20：5年12月三大产品家电下乡错售量统计

其中D2单元格为选择销售点的序列单元格，E3:F5为对应D2销售点的各

商品销售量，也就是动态饼图的数据源。下面我们来制作这个数据源。首先，在

E3:E5单元格区域输入将来动态饼图的类型名称：

2015年12月三大产品家电下乡谓售量统计

1

声

指定稍用点品名

青义店彩电

水

3箱

手

机

105

方水店

其次制作序列。在Excel2003中，制作序列是这样的：选中D2,单击“数

据”一“有效性”菜单命令，打开“数据有效性”对话框，在“允许”下拉列表

中选择“序列”，在“来源”文本框中输入“全部销售点，青义店,龙门店，方水店”

文本序列，如图所示。

而在Excel2016中，只有这一点不同:选中D2,单击“数据”选项卡一“数

据验证”一“数据验证”菜单命令，如图所示：

.

1-1经营分析倭像B式)-Exel

|百间视图Q告诉我一要做什么…

团连接史[77]▼X清除金匡)快速填充目―合并计算

回雌蹩JL巧5百,配制加一

2郝K砧援立师刀带及第维,届管理数据横

打开“数据验证”对话框，在“允许”下拉列表中选择“序列”，在“来源”

文本框中输入“全部销售点，青义店,龙门店，方水店”文本序列，如图所示：

剩下的动态饼图的操作中Excel2003和2016就是一样的了。

此时D2单元格已经有了下拉菜单，其中能看到也能选择我们刚才输入的各

项，但选了这些项后图表没有什么变化，因为我们还没有为这种点击操作赋予响

应的动作，我们希望在F3:F5单元格里响应。

先设置对应F3单元格引用，在F3单元格中输入公式：

=IF($D$2=、青义店”,C3,IF($D$2二"龙门店U,C6,IF($D$2="方水店

U,C9,C3+C6+C9)))

该公式本质上是一个嵌套的IF函数，其含义是：

在F3单元格里判断D2的值是不是“青义店”，如果是，则在F3中显示C3

的值，如果不是，则执行后面的值。

但后面的值又是一个嵌套的IF函数，其含义是：判断D2的值是不是“龙门

店”，如果是，则在F3中显示C6的值，如果不是，则执行后面的值。

但后面的值还是一个嵌套的IF函数，其含义是：判断D2的值是不是“方水

店”，如果是，则在F3中显示C9的值，如果不是，则执行后面的值。此时后面

已经不再有嵌套函数了，直接就是C3+C6+C9。

这样，我们就根据在D2单元格中选择了不同的值，而在F3单元格里显示

了不同的值，但这些值都是“彩电”的，如果要显示“冰箱”的值，则应显示在

F4单元格，用同样的IF嵌套来实现，而在F4单元格中，要判断的值还是位于

D2单元格，所以D2在这里要用绝对引用，否则当我们拖动复制公式时，将在

F3单元中去判断D3的值了！

D2▼：|xyA=IR$D$2=”青义店：CSHSDSZ：1■龙门店;C6JH$D$2="方水店~.C9,C3-C6+C9》）

ABCEEFGHITKLH

12015年12月三大产品家电F乡销出量统计

产品

2名

指定徜售点

3彩电

吉义店

4漆箱

5手机

6彩电

龙门店

7冰箱

8手机

9彩电

方水店

泳箱

10手机

11

用填充拖动完成其他两个销售点的公式应用。这样D2单元格数据发生变化

时，对应的F3:F5单元格数据也会发生相应的改变。最终我们得到了想要的数据

源。

②绘制动态饼图，选择E3：F5单兀格区间，点击“插入”菜单中的“图表”

选项，选择三维分离饼图，如图所示：

插入页面布局公式数据审闻视图开发工具

黑—•

表格插图加载数据透视图

项^

二维饼图

彩电

BCD

大产品家电下乡销售量统计

工品名督全部销售点

彩电

泳箱

70

手机189

彩电105

冰箱使用此图表类型：

55（7^显示整体的匕阚。

手机135•

彩电235

冰箱113。在下列情况下使用它：

210

手机3I•总数等于100%。

H•图表仅包含几个饼图

切片（太多切片会造成

/角度难以估计）。

0

如果是Excel2003,此时应右击图片，选择“数据源”，在“数据源”对话框

中设置系列名称为“=$D$2”，如图所示：

，2十人4万二八〉口口办七厂？用DN班1I

定销售点产品名销售量二至甑诞•总

青义店91德431

龙门店

方水店

分类标志®）：=Sheet3!$E$3SES5

取消］k上一步皿I［下一步国）＞1［充成©

而在Excel2016中，图表标题的动态修改方法，不是直接点击“图表标题”

修改或设置，而是在图表区（或绘图区）右击，选择“选择数据”，在“选择数

据源”对话框中，点击左下侧的“图例项（系列）”区的“编辑”按钮，如图：

然后在弹出的“编辑数据系列”对话框中，在“系列名称”文本框中输入“二，

双层饼图'!$D$2”后确定。

最后生成动态饼图，如图所示。

A|BC1D

i年12月三大产品家电下乡销售量统计

前售点

销

住

量

产名

品

口

义店

彩

电

泳

91箱105

55

机

709手

18135

水店

手

Excel2016没有直接出现分离式饼图，只有普通三维饼图，但可以手工分离

之。

4、仿信息图一一半圆图的制作

某市统计某个4G用户倍增情况如图所示:

AB

1X市X年4G入户数倍增情况

2月份倍数

3_1月1

44月4

57月7

612月16

请根据以上数据制作成如图所示的半圆图。

分析：

这个信息图表将几个不同的“倍数”用不同大小的半圆形来表现，半圆形的

面积比例应该与“倍数”大小保持一致。我们可以借助气泡图的面积功能来实现

这个模拟。

步骤1：首先需要准备数据。在原有“倍数”数据的基础上，添加两列辅助

数据，一列是通过“倍数”数据开平方得到，也就是半圆形的半径/直径比例。第

二列辅助数据是通过前面计算得到的直径（或半径）数据算出圆心所在位置。这

两组数据都可以用公式来做，以便得到一个动态可变化的结果。做好这两列辅助

数据是完成这个图表的关键，如图所示：

SUM▼\XV勿=SUM（$C$3：C5）+C6/2

AIBIC.D.E

所用公式

=C3/2

44

22=C3+C4/2

57

2.645751此公式可向下填充

6

167.645751.

步骤2：创建一个气泡图，气泡图需要三组数据源，其中X轴数据取上面步

骤中所生成的“辅助2”，即圆心所在位置，Y轴数据全部为0（0的个数要与数

据点个数保持一致，本例有4个数据，所以有4个（）），第三个气泡大小的数据

源取自原有的“倍数”数据列，创建过程较为复杂，描述如下：

步骤2-1：选中任意一列，这里选A2:A6,点击“插入I图表”，在弹出的“插

入图表”对话框中，将没有“推荐图表”（如果选B2:B6区域这里就有“推荐图

表”），我们直接选“所有图表”选项卡，选择其中的“XY散点图I三维气泡图”，

如图所示：

图表标题

步骤2・2：两次单击，选中图例中的“系列1”，右击，单击“选择数据”，在

弹出的“选择数据源”对话框中，在左侧的“图例项”中选中“系列1”，点击其

上的“编辑”，如图所示：

图表方2m

选择数据源

图表数据区域必：

数据区域因太复杂而无法显示。如果i

/T

图例项(系列)凶

0.20

3喊[・加。

步骤23在随后弹出的“编辑数据系列”对话框中，将第一项“系列名称”

选择为“倍数”两个字所在的B2单元格；将“X轴系列值”选择为圆心值所在

的D3:D6单元格；点击“Y轴系列值”右侧的上拉箭头，在弹出的“编辑数据系

列”小窗口中手工输入“0QQ0”，注意4个0之间是英文逗号隔开；在最后的

“系列气泡大小”中选择倍数值所在的B3:B6单元格，如图所示:

编相数据系列?X

系列名称(N)：

='Sheet3(3),!$B$2二倍数

X轴系列值凶:

='Sheet3⑶'!$D$3:$D$6t=0.5,2,4.3228...

Y轴系列值①:编辑数据系列？X

0,0,0,00,0

0,0,0,0国

='Sheet3(3)*!$B$3:$B$6±

确定取消

两次“确定”后退回到Excel界面，发现已生成了如图所示的气泡图:

图表标题

步骤2-4：选中图中的灰色小球，按Delete键删除，此时图例中的“系列3”

也自动删除了，再选中图例中的“系列2"，按Delete键删除，此时终于生成了

我们想要的气泡图原图，如图所示：

图表标题

步骤3：选中纵坐标轴，将最小值设为()，这样就可以将原有的圆形气泡显

示为半圆形，如图所示：

・倍数小(1)0.02

步骤4：设置气泡。选中气泡，设置系列格式，气泡大小的设置选择默认的

“气泡面积”，同时调整气泡大小的缩放值，同时配合图表宽度的调整，使各个

半圆能够紧贴在一起，如图所示：

/系列选项

系列绘制在

•主坐标轴(E)

次坐标轴凶

大小表示

•气泡面积(A)

O气泡充度(W)

缩放气泡大小为(S)与00

□显示负值气泡⑻)

步骤5：显示气泡的数据标签。由于气泡图与散点图一样，数据标签的显示

只能选择系列名称、X值、Y值以及气泡大小，而不能选择文字的分类标签，因

此在这里需要依次单独选中每个标签文本框，然后在公式编辑栏中直接引用月份

分类标签所在的单元格地址，这样可以形成动态的文本链接。

A6：XVA='Sheet3(2)'!$A$6

在此编辑数据标签

ABCD.

X市X年4G入户数倍增情况

月峭倍数辅助1辅助2

1月11

422

72.6457514.322876

1647.645751

IIcP―

图表标题

1

1

1一

二

1三

14

15

16

17

18

19

2o

21

步骤6：进行图表的美化，操作包括设置图表标题、隐藏坐标轴线和网格线，

更改气泡的填充颜色等等。

课后习题6

1、Excel2016有哪些新增的图表类型？

2、在柱形图、环形图、折线图、饼图、雷达图中，哪些适合描述随时间变

化的变量，哪些又适合描述反映百分比的变量？

3、形如“$A$1”这样的单元格引用称为什么，而形如“A$l”这样的单元格

引用又称为什么？

4、操作：为了控制产品质量、加强生产现场管理、提高生产能力，生产部

门的管理人员应定期进行生产误差的分析。用Excel提供的散点图来分析生产误

差,可根据各种因素的数据散点很明显地对比出该因素在生产误差范围内的分布

情况。对分布密集的散点造成的生产误差，生产管理人员必须引起重视。

下表所示为某企业国庆大假后整个10月份出勤情况：

ABC

1日期生产误差缺席员工的数量

22020/10/85422

32020/10/94318

42020/10/104820

52020/10/134321

62020/10/154418

72020/10/175124

82020/10/184825

92020/10/194827

102020/10/204016

112020/10/214320

122020/10/224828

132020/10/234821

142020/10/245529

152020/10/254827

162020/10/264226

172020/10/274322

182020/10/284217

192020/10/295528

202020/10/303921

需要完成以下操作：

（1）请据此制作生产误差散点图。

（2）请据此制作生产误差风险矩阵图。

操作步骤：

（1）请据此制作生产误差散点图。

①选择Bl:C20区域，在“插入”选项卡的“图表”组中选择“散点图”右

侧的下拉按钮匕、在打开的列表中选择笫一项“散点图”以插入散点图，如图所

示：

插入页面布局公式数据审阅视图开发工具帮助Power

―田徽▼曲,▼代▼

I加载推荐的“1]据透视图三维地

俵项▼图表.也d图/

散点图

BCDE散点图

生产误差缺席员工的数量

使用此图表类型：

5422O

•比较至少两组值或两

4318

4820对

4321气池•显示值集之间的关

4418系。

5124

上耳

48O-——d在下列情况下使用它:

480

缺席员工•数据代表单独的测

40

4335量。

48

30

48t

5525

48

426200

43

42

②选择图表区，在“图表工具/设计”选项卡的“位置”组中单击“移动图

表”按钮；

③在弹出的“移动图表”对话框中，选择“新工作表”单选按钮，并在其

后的文本框中将“Chartl”改为“生产误差散点图”，如图：

当然，也可在新的图表工作表建好后直接修改工作表标签，单击完成，生成

如图所示的图表工作表：

被说吕工的

)产基SIWH1St2SMK3

④在“图表工具/设计”选项卡的“图表布局”组中单击“快速布局”，在打

开的列表中选择“布局1”选项（这样做是为了能在不改变布局的情况下快速给

X轴和Y轴显示默认标题）,然后通过单击再单击的方式将图表标题、X轴标题、

Y轴标题分别改为“由于员工不在场而导致的生产误差”、“生产误差”、“缺席员

工的数量”，如图：

由于员工不在场而导致的生产

误差

啾

轻

S

H40

30

唱

20I•缺席员工的数量

唯10L

南O

2040G0

生产误差

⑤：双击X轴（或右击之，选择“设置坐标轴格式”），在弹出的“设置坐标

轴格式”窗格中的“坐标轴选项”中，将其“边界”的“最小值”所对应的文本

框中的值“0”改为“35”，如图所示:

1+J

0

B

⑥在“图表工具，设计”选项卡的“图表布局”组中单击“添加图表元素”

右侧的下拉箭头，在弹出的列表中，选择“图例”旁边的“无”来去掉图例（因

为只有一个数据系列，不用图例也能看明白），或在图表中直接单击选中图例后

按删除键，如图：

⑦单击图表，在右上方三个图标中的“图表元素”,上单击，在弹出的“图

表元素”快捷菜单中选择“趋势线/线性”，如图：

于员工不在场而导致S1生产读金*设置图莱

今Q

如

m»

gHlRM

温■»肺诉均

更拿&Q...

或者在“图表工具/设计”选项卡的“图表布局”组中单击“添加图表元素”

右侧的下拉箭头，在弹出的列表中，选择“趋势线/线性”，如图：

目图表工具

文件开始插入页面布局公式数据百场视图

岫坐的凶>图表样式

I色

£E3i^g(C)»

由于员工不在场而导致的生产误差

击初6标签。,35

为

命误差法出

四同格法⑷

由子例(D

⑧基本制作完成的“生产误差散点图”如下图所示:

由于员工不在场而导致的生产

误差

嚼40

粽30

£20

H

,O0-

OE艇(

35

塔

最后在“图表工具/格式”选项卡最左侧的“当前所选内容”组中分别对国表

标题、X轴和Y轴的字体，以及图表区背景、趋势线等图表元素进行格式设置，

如图：

(2)请据此制作生产误差风险矩阵图。

风险矩阵是一种用来分析公司潜在行为的风险(或收益)的图形化方法。简

单来看，整个风险矩阵是由两部分组成：其一，是原数据；其二，则是背景填充

色效果。因此，首先我们在原表中最前面插入7列，以便生成一个辅助数据来实

现填充色效果。具体做法是造一个9x5的数据表格，其中A列是填充色名称：

选中BLF10区域，生成堆积柱形图，将图表标题去掉、图例放在右边，同

时选中数值轴，将其最大值由默认的6改为5,如下图：

ABCD।EF।

BC

绿

2深

3中

绿

4绿

绿

5黄

6黄

7橙

橙

8红

红

10红

11

12

13■系列9

14■系列8

15

16■系列7

17■系列6

18■系列5

19

■系列4

20

21■系列3

22■系列2

23■系列1

94

单击左下角色块，选中图例“系列1”所代表的蓝色色块（本例中只有这一

块），然后在右侧的“设置数据系列格式I系列选项I填充”中选择“纯色填充”，

然后在下面的“颜色”中调出调色板，选择“系列1”所要求的深绿色；

等左下角变成深绿色后，再点击左紧邻该色块的上方色块，即“系列2”所

代表的橙色块（本例中有两块.呈左卜右下斜置）.然后用同样的方法将其改为

要求的“中绿”色；

重复以上步骤,直到将所有9个系列都由默认的颜色改为要求的颜色，如下:

■系列9

■系列8

■系列7

■系列6

系列5

■系列4

■系列3

■系列2

■系列1

选中数值轴，在“设置坐标轴格式I坐标轴选项I坐标轴选项”中将“横坐

标轴交叉”由“自动”改成“最大坐标轴值”，此时原来在图下方的分类轴（横

轴）就调整到图表上方：

再选中已经到了图上方的分类轴，在“设置坐标轴格式I文本选项I坐标轴

选项”中将“纵坐标轴交叉”设置成“最大分类”，此时原来在图左方的数值轴

（纵轴）就调整到图表右方。

调整后的效果如下：

为什么要做这样的调整呢？因为等一下我们坏要插入数据•,会有新的数值轴

和分类轴，而新增加的横轴利纵轴我们希望它出现在图的左侧和下侧。如果我们

此时不做这样的调，则新增加的横轴和纵轴将出现在图的右侧和上侧。

然后，删除水平网格线，隐藏轴标签（选中数值轴后在“设置坐标轴格式I

文本选项I标签”中将“标签位置”从默认的“轴旁”改为“无分类值司样

设置以上删除水平网络线和隐臧轴标签的操作也可以直接右击后选择删除。

右击任意一个柱子，选择“设置数据系列格式I系列选项”，将“间隙宽度”

设置为0%：

完成此步骤后，整个辅助背景色的效果就此打造完成：

接着在Excel中加入真正的数据信息：选中I2:J20区间，按Ctrl+C复制，然

后鼠标点击图片的任意地方以选中图片，然后再点击主工具栏上的“开始I剪贴

板I粘贴”下拉菜单中的“选择性粘贴”命令，在其中进行如下图所示的设置:

（注意，如果复制时选择的区域为I1:J2区域，则此处应选中“首行为系列名称”

复选框）

31\J11XJ

日期生产误差缺席员工］

BCDE1

2014/10/85422

12014/10/94318

112014/19/104820

1112014/10/134321

11112014/13/154418

11'13/17；5124

1‘10/18!4825

‘13/19|4827

‘13/20；4016

13/21；4320

‘13/22:4828

‘13/23；4821

13/24；5529

‘13/25；4827

‘13/26；4226

‘13/27;4322

'13/28；4217

'13/295528

13/30；3921

点击“确定”后，图表成为如下形状:

■系列10