Excel数据分析：可视化图表之达成分析

说明：本文仅做个人笔记记录，如有疑问欢迎戳我~

1、仪表盘

能够直观的表现出某个指标的完成情况或占比情况，在项目进度、计划完成度较常见。只适合展现数据的累计情况，不适用于数据的分布特征等，同时一般超过100%后不好表现。

有表格如下：

注：因圆环只有一半，所以P1-P3都是辅助数据，相当于把达成率和未达成率均减半

​

1：选择P1-P3数据及表头 - 插入 - 图表 - 饼图或圆环图，选择圆环图

2：在图表右键 - 设置数据系列格式 - 第一扇区起始角度设为270°，圆环大小调整为60%左右，隐藏下方灰色部分（无轮廓+无填充）

3：调整图表元素，添加文本框，在公式栏插入【=百分比的单元格】

​

2、滑珠图

用来展示任务的达成率情况。

有表格如下：

注：其中目标、达成列用于计算达成百分比，不实际应用与图表的制作

​

1：单击数据单元格 - 插入 - 推荐的图表 - 簇状柱形图

2：选中右边100%的柱，右键 - 设置数据系列格式 - 系列重叠设置为100%

3：再次选中100%的柱，右键 - 选择数据 - 图例项右方箭头调整顺序，使【达成率】置于顶层

4：图中的滑珠也是达成率，手动添加一个新达成率的柱。选中【达成率】的柱，右键 - 选择数据 - 添加 - 系列名称选中【达成率】单元格，系列值选择下面的数值后确定

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

6：右键【新达成率】，将线条填充设为无实线

7：有3个素材，分别为深绿色柱，灰色柱和圆点，选中后复制，然后选中对应元素粘贴，完成格式替换

8：调整图表元素 - 选择圆点 - 图表元素 - 数据标签设为居中

​

3、子弹图

子弹图是为了取代仪表盘上常见的那种里程表，时速表等基于圆形的信息表达方式。每一个单元的子弹图只能显示单一的数据信息源；通过添加合理的度量标尺可以显示更精确的阶段性数据信息；通过优化设计还能够用于表达多项同类数据的对比；可以表达一项数据与不同目标的校对结果。

对比条形图，它主要用于对比实际数据与目标数据之间的差异情况，因为外形酷似一个发射出去的子弹而得名，常用于：销售报表展示、KPI考核等场合。

子弹图一般包含3个项目：

A：目标-细小横条，用于代指实际的目标

B：实际-细长的柱形图，用于代指实际的销量数据

C：等级-较宽的柱形图，每个颜色表示不同的KPI等级，一般不超过5个为宜

有表格如下：

含有【实际与目标销量】、【KPI对应的指标】

​

1：整理数据，因为子弹图的本质是一个堆积的柱形图，我们希望【KPI指标数据】进行堆积。故只需要更改下【良好】与【优秀】的指标即可

良好：=【良好】-【一般】

优秀：=【优秀】-【良好】

整理后的表格数据如图：

​

2：选择所有的数据 - 插入 - 插入柱形图或条形图下插入一个【堆积柱形图】 - 点击图表上方选择【切换行列】切换图标的行列关系

3：选中【实际销量】蓝色柱子 - 【更改图标类型】将【实际销量】与【目标销量】放在【次坐标轴】- 【目标销量】的图表类型更改为【带标记的折线图】，得到图表的雏形

4：点击【实际销量】蓝色柱子，Ctrl+1调出设置窗口 - 间隙宽度设置为400%，缩小柱子宽度

5：分别点击后面比较粗的柱子，从下到上为他依次设置一个深-浅的灰色，为中间的细长柱子设置黑色填充色

6：设置目标的黑色横条，使用插入形状来实现

复制预先设置好的横条，粘贴到【目标销量】橙色元素，将折线图线条格式的形状轮廓设为无轮廓

​

4、华夫饼图

又称为直角饼图，可以直观的描绘百分比完成比例情况，典型应用是比较同类型指标完成比例。

与传统的饼图相比较，华夫饼图表达的百分比更清晰和准确，它的每一个格子代表1%。

在反应完成率、达标率这样的占比类数据内容时，比较多见。

假设有百分数85%，用以制作华夫饼图，才有以下集中方式实现：

方法1：

1：在任意单元格录入百分数【85%】

2：在绘制单元格中录入公式【=REPT("○",100-C5*100)&REPT("●",C5*100)】

3：选中该单元格，右击鼠标选择【设置单元格格式】，然后点击【对齐】勾选【自动换行】

4：拖动单元格列宽，使单元格恰好显示十个小方框

5：设置单元格字体颜色即可

​

方法2：

1：制作辅助数据区域

在一个10列2行的数据区域，按照下图

第一行输入：【=IF(MOD($B$2*100,10)<COLUMN(A:A),ROUNDDOWN($B$2*10,0),ROUNDUP($B$2*10,0))】，填充到第一行的10个单元格；

第二行输入：【=10-B4】，填充到第二行的10个单元格。

思路：制作一个堆积柱形图，共10个柱形，每个高度为10个单位，每个单位代表1%

​

2：选择数据区域，创建百分比堆积柱形图，删除不需要的元素

3：选中内部柱形图的区域，拉大到与外框矩形重合，把图形区域调整为正方形

4：将边框高度和宽度设为一致，变成正方形，然后把间隙宽度调整为0

5：在Excel中插入一个圆，填充为你想要的配色，复制粘贴到对应的柱形上面，关键点：填充方式选择层叠，不然这就是一个拉扁的椭圆。

6：同样的办法将另一个圆填充到另一个柱形上面，插入数据标签，选择单元格中的值

​

方法3：

1：选择10行10列（A2:J11），调整其大小为正方形，使其看起来像华夫饼图的网格

2：空白格（L3）输入用于华夫饼图的百分比值，如图

​

3：在网格最后1行的第1个单元格开始的顺序输入1%-100%的值

使用以下公式一次性将百分比从1%-100%插入网格中：【=(COLUMNS($A11:A$11)+10*(ROWS($A11:A$11)-1))/100】

注：将公式输入到最后1行的第1个单元格中，然后将填充柄向右再向上拖动，并将格式设置为百分比值

​

4：选中百分比值 - 开始 - 样式 - 条件格式 - 新建规则

新建规则如下：

选择规则类型【只为包含以下内容的单元格设置格式】

编辑规则说明【介于=0到=$L$3之间的值（L3是具有华夫饼图的链接单元格）】

格式选择相同的填充颜色和字体颜色，以使单元格值不可见

​

确定后得到下图：

​

5：选中华夫饼图区域，使用相同填充颜色和字体颜色（比条件格式中的颜色浅）格式化单元格

​

6：使用文本框插入标签，输入公式【=$L$3】，调整位置和格式，完成

​