excel数据分析

目录

一、对比分析类图表

1 漏斗图

2 指标完成度

3 完成度对比图

4 滑珠图 

5 仪表盘

6 跑道图

二、分布分析类图表

1 旭日图

2 瀑布图

3 气泡图

4 箱形图

5 旋风图

三、图表优化

1 添加底部数据表

 2 更改条形图的样式

① 操作说明

② 应用-五星级图制作

---

一、对比分析类图表

1 漏斗图

用于突出、对比两组内容的完成进度。

• 法1：加辅助列1＆2  =max（原数据列）- 原本单元格数据 → 插入堆积条形图 → 逆序类别 →设两边数据形式 无填充
• 法2：先排序图表数据 - 降序 →插入 漏斗图 →选择下方的辅助列柱，设置为透明 →美化图表

2 指标完成度

用于突出、对比各指标的完成进度。

• 插入柱状图（包含两组对比数据） → 选中其中一柱 → 设为次坐标轴
• 设置无填充 → 调整实线宽度

3 完成度对比图

用于对比两组内容的进度百分比。

• 选中两组数据 插入条形图 → （将两组数一组按正序,一组按逆序）选中其中一组数据 设置数据系列格式 → 设为次坐标轴

• → 设次坐标轴最小值＆最大值,如设最小值为-1,最大值0
• → 设主坐标轴最小值＆最大值,如设最小值为-1,最大值1
• →将左侧坐标轴的数字，都设为  % ，不带符号

• 设置后调整文字颜色，呈现如下

4 滑珠图 

用于对比多个内容进度百分比。

•  插入图表 → 簇状柱形图 → 选中其中一柱，右键 → 设置数据点格式 - 系列重叠设置为100%

• 再次选中柱 → 右键 - 选择数据 → 图例项 - 右方箭头调整顺序，置顶【完成率】

• 手动再加一个达成率的柱：选中柱，右键 - 选择数据 - 添加 →  系列名称 - 选中【达成率】单元格 → 系列值 - 选中下面的数字 → 确定

• 选中最新的【达成率】柱，右键 - 更改系列图标类型 → 改为【带数据标记的折线图】

  

• 制作素材：分别为深绿色柱、灰色柱和原点。选中深绿色柱复制，再选中100%柱粘贴，即完成替换，其他的数据项同理。

  

• 样式调整：右键 - 设置数据系列格式 → 系列选项 - 间隙宽度适当调大→ 填充 → 层叠并缩放

• 去掉连线：右键- 这只数据系列格式 → 线条 - 无线条

• 整体调整 ：选中整个图表 - 图表元素 → 去掉多余元素，再选择圆点 → 图表元素 - 数据标签 - 设置居中 

  

5 仪表盘

用于突出当前进度。由于仪表盘表现为一半，所以添加辅助列1/2/3，即把达成率、未达成率与总目标÷2

	达成率	未达成率	总目标	辅助列1	辅助列2	辅助列3
3月业绩	46%	54%	100%	23%	27%	50%

• 选中辅助列1/2/3与表头 - 插入 圆环图

•  设置数据系列格式 - 第一扇区的起始角度设置为270° → 隐藏下方灰色部分 - 无轮廓+无填充 → 添加文本框，公式处插入【=百分比的单元格】

6 跑道图

用于对比各个项目进度。各内容进度表如下，其中辅助列为100% - 完成率

内容名称	完成率	辅助列
内容三	20%	80%
内容二	32%	68%
内容一	48%	52%

• 插入- 圆环图 → 选择数据 - 切换行列 → 右键 设置数据系列格式 → 圆环图圆环大小 - 适当调小  → 选中辅助列的项 → 无轮廓无填充

• 选中完成率的圆环 → 添加数据标签 → 直接拖动标签位置调整 
• 插入 形状 - 标注 → 输入项目进度

二、分布分析类图表

1 旭日图

用于突出层级的归属关系。同一层圆环代表同一层级的项目，离圆心越近，项目层级越高。

品类	产品	销售额
品类一	产品1	965
品类一	产品2	348
品类一	产品3	670
品类二	产品4	3879
品类二	产品5	516
品类二	产品6	510
品类三	产品7	2308
品类三	产品8	2216
品类三	产品9	530
品类四	产品10	580
品类四	产品11	460
品类四	产品12	248

• 插入 推荐的图表 → 所有图表 → 插入旭日图 → 美化图表- 双击更改某个区域的配色

2 瀑布图

用于显示各项占比。添加汇总行数据，使后面的项加起来等于总额，排序为依次相减。并添加辅助列差值，数量为汇总数据减去对应的各项数量。

类别	开销	辅助列
汇总	4720	0
电脑	2000	2720
键盘	1000	1720
耳机	800	920
书桌	500	420
书籍	120	300
其他	300	0

• 方法1：插入 推荐的图表 → 所有图表 → 插入瀑布图 → 美化图表
• 方法2：插入 堆积柱形图 →选择下方的辅助列柱，设置为透明 →美化图表，去掉网格线等

3 气泡图

用于各项内容分布情况的分析，表现形式为散点图的扩展。

• 选中数据区域 → 插入 -  XY散点图 - 气泡图 → 设置数据系列格式 - 气泡大小缩放
• 美化图表：去掉网格线标题，加数据标签 → 设置数据标签格式 - 单元格中的值 → 选中产品名称这一列
• 选中XY坐标轴 - 设置坐标轴格式 → 纵坐标交叉的坐标轴值 - 改为市场份额or增长率的平均值 → 刻度线、标签 设置为无

  

4 箱形图

利用分位数分析数据分布。中间的大区域中：最底线为1/4分位数，中间的线为2/4分位数，最上方的线是3/4分位数。以下表的产品销量分布为例，

产品	销量
P1	561.2
P2	270.1
P3	540.8
P4	322.6
P5	368.8
P6	405.5
P7	405.5
P8	540.8
P9	270.1
P10	322.6
P11	222.7
P12	608.5
P13	947
P14	270.1
P15	290.4
P16	540.8

• 仅选中【销量】一列数据 → 插入 - 箱形图
• 选择纵坐标轴 - 设置坐标轴格式，适当调正 纵坐标 的边界值

 5 旋风图

用于突出不同层级的数量分布。以下表中的各年龄层级的男女数量为例，

年龄段	男	女
0-4岁	90	217
4-8岁	1616	191
9-14岁	5160	7080
15-19	210	260
20-24	1718	321

• 选中数据表→ 插入 - 堆积条形图 → 选中某柱 - 右键 →更改 系列图表类型 → 选择其一，设为次坐标轴

• 更改两条X轴的最小值，实现分开绘制：最小值设为最大值的负数 → 勾选【逆序刻度值】
• 更改Y轴标签位置：选中Y轴 → 设置坐标轴格式 →  标签 → 标签位置 - 高 →美化图表，去掉网格线标题，加上数据标签

三、图形优化

1 添加底部数据表

• 插入图形 → 图形右侧 加号 → 图标元素 →数据表

• 在图形的横轴底部，呈现如下

 2 更改条形图的样式

① 操作说明

• 插入 形状 → 调整形状格式、角度 → 复制 → 选中条形图 →粘贴（如修改为小图案 ）
• 设置数据系列格式 → 填充 → 层叠

② 应用-五星级图制作

• 选中表格区域 → 插入 簇状条形图 → 右键 - 设置数据系列格式 
• 系列选项 - 系列重叠改为100%

• 去掉图例、网格线等多余的图表元素，可删除X轴

  

• 制作素材，复制粘贴替换 → 右键 - 设置数据系列格式 →填充 - 层叠并缩放