以制作生产质量数据可视化看板为例，介绍用excel制作可视化看板的思路以及数据透视表、Vlookup、sumif、rank等常用函数的用法。主要参考文章，我补充数据并完善了制作可视化看板的思路，下面内容是对可视化看板选择性的复现。开源的数据和excel文档见文末。
文章目录
-
- 一、完成效果图
- 二、制作可视化看板的思路
- 三、制作可视化看板
-
- 1. 可视化界面的排版布局
-
- 1.1 确定可视化界面的内容
- 1.2 可视化界面排版的思路
- 1.3 按照内容设计可视化界面的排版
- 1.3.1 设置看板底色
- 1.3.2 可视化界面的布局
- 2. 原始数据的处理
- 3. 创建数据透视表
- 4. 统计数据
-
- 4.1 获取年度数据统计表
- 4.2 获取季度数据统计表
- 4.3 获取车间不合格数量排名统计表
- 4.4 获取负责人不合格数量统计表
- 4.5 获取每日合格率统计表
- 5. 可视化数据呈现
-
- 5.1 季度数据统计表的呈现
- 5.2 车间不合格数量排名统计表的呈现
- 5.3 每日合格率统计表的呈现
- 5.4 负责人不合格数量占比统计表的呈现
- 5.5 年度数据统计表的呈现
- 5.6 制作动态标题
一、完成效果图
二、制作可视化看板的思路
确定可视化看板的内容和排版布局。
- 确定需要展示的内容（图表的标题和展示字段或者形式）
- 确定看板的排版和布局（颜色、每个模块的大小等）
数据加工
- 第一次处理：整理《原始数据》，增加多个字段并创建表，形成《生产质量数据表》。
- 第二次处理：根据展示内容在《生产质量数据表》中插入《数据透视表》，并选择适当的切片器。
- 第三次处理：基于《生产质量数据表》和《数据透视表》进行数据的统计汇总，完成《统计表》。
数据呈现
- 将《统计表》中汇总的表格信息以表格或者图形的形式呈现到可视化看板中。
三、制作可视化看板
1. 可视化界面的排版布局
1.1 确定可视化界面的内容
目标： 制作生产质量可视化看板。
原始指标：《原始数据》表中字段为项目名、生产车间、负责人、生产数量、不合格数量、生产日期。
展示内容：
1、静态【年表】各项目这一年的生产情况——每个项目的生产数量、不合格数量、合格率，并排名。
2、静态【季度表】各季度的生产情况——每个季度的生产数量、不合格数量和合格率。
3、动态【月表】每个月的生产情况
- 车间——每个车间的生产数量及排名。
- 负责人——每个负责人的负责的生产线出现的不合格数量及占比。
- 每日——每天进行生产的生产线的合格率及占比。
根据内容增加指标： 合格率、月、日、季度。
确定数据透视表的展示项：日、月、负责人、项目、生产车间。
确定数据透视表的求和项：生产数量、不合格数量。
确定每个统计表的标题和字段：
1、年表：
- 标题：全年信息汇总（按项目合格率排名）；
- 字段：项目名、生产数量、不合格数量、合格率、排名；
2、季度表：
- 标题：X季度；
- 字段：生产数量、不合格数量、合格率；
3、月表：
- 标题：X月 生产车间不合格数量排名；
- 字段：排名、生产车间、不合格数量。
- 标题：X月 负责人不合格数量占比；
- 字段：负责人、生产数量。
- 标题：X月 每日合格率统计；
- 字段：日、生产数量、不合格数量、合格率、辅助列。
1.2 可视化界面排版的思路
关键：分析逻辑清晰，明确要展示的内容，有主次之分。
- 排班布局要合适，将重点集中在看板中关键区域。
- 利用有限的页面展示更多内容，所以布局要均匀，比如说页面左右对称等。
- 图表颜色舒适。可以在大作或者小红薯上面找些图片模板做参考。
- 展示的图表不要过于复杂，尽量选择常见的柱形图、扇形图、折线图等。因为越简单的图表越容易解读。
1.3 按照内容设计可视化界面的排版
1.3.1 设置看板底色
选中表格区域填充。这里填充为蓝色（#203764）。
1.3.2 可视化界面的布局
将要展示的信息按区域划分。方便后面数据的输出展示。对应区域用深蓝色（#021A3C）填充。调整格式并输入文字，最终页面如下：
2. 原始数据的处理
打开《原始数据》工作表（该工作表相当于数据库，用来存放原始数据），拿到原始数据后，为给数据看板提供展示的数据，需要对数据做一些处理，在这里我们利用公式计算出生产的合格率、月、日、季度。
在表格中输入列名：合格率、月、日、季度后利用公式计算出数值，再利用快捷键“CTRL+T”创建表，将表名重命名为《生产质量数据》。
计算公式如下：
1、合格率计算公式：=TEXT(((C2-D2)/C2)*100,"0.00")&"%"
2、月份计算公式：=MONTH(F2)
3、日计算公式：=DAY(F2)
4、季度计算公式：=CHOOSE([@月],1,1,1,2,2,2,3,3,3,4,4,4)
有了上面的数据，接着我们使用数据透视表对数据进一步处理。
3. 创建数据透视表
很多统计都可以基于数据透视表完成，所以在对原始数据处理之后，使用数据透视表完成可视化看板的动态部分。
- 选择《生产质量数据》表，插入数据透视表。
- 弹出的对话框点击确定，并将新的表格命名为《数据透视表》。
- 在数据字段列表区域按下图拖动字段到对应位置。
- 添加切片器：将月份字段添加为切片器（这样便可通过选择月份任意显示对应月份的数据）。
切片器样式的设计参考Excel切片器怎么修改颜色?
4. 统计数据
4.1 获取年度数据统计表
新建一个用于存放统计数据的sheet表，命名为统计表。
输入项目名、生产数量、不合格数量、合格率、排名之后，利用快捷键“CTRL + T”创建表，重命名为表1。通过sumif函数对原始数据表进行数据汇总并计算合格率。再通过rank函数进行排名。
计算公式如下：
1、生产数量公式：=SUMIF(生产质量数据!$A:A,[@项目名],生产质量数据!$C:C)；
2、不合格数量公式：=SUMIF(生产质量数据!$A:A,[@项目名],生产质量数据!$D:D)；
3、合格率公式：=((B3-C3)/B3)*100
4、排名公式：=TEXT(RANK([@生产数量],[生产数量]),"0")
4.2 获取季度数据统计表
在统计表中通过公式获取《生产质量数据》每一个季度的数据统计。输入季度、生产数量、不合格数量、合格率之后，利用快捷键“CTRL + T”创建表，重命名为表2。
计算公式如下：
1、一季度生产数量公式：=SUMIFS(生产质量数据!$C:C,生产质量数据!$J:J,1)。其他季度只需修改公式中的数字1为对应季度即可。
2、一季度不合格数量公式：=SUMIFS(生产质量数据!$D:D,生产质量数据!$J:J,1)。其他季度只需修改公式中的数字1为对应季度即可。
3、合格率可直接根据生产数量和不合格数量直接获得：=TEXT(((B17-C17)/B17)*100,"0.00")&"%"。
4.3 获取车间不合格数量排名统计表
输入生产车间、不合格数量、排名之后，利用快捷键“CTRL + T”创建表，重命名为表3。通过 sumif 函数对《数据透视表》中不合格数量求和统计再通过rank函数进行排名统计：
计算公式如下：
1、不合格数量计算公式：=SUMIF(数据透视表!$A:A,[@生产车间],数据透视表!$C:C)。
2、排名计算公式：=TEXT(RANK([@不合格数量],[不合格数量]),"0")。
4.4 获取负责人不合格数量统计表
输入负责人、不合格数量、占比之后，利用快捷键“CTRL + T”创建表，重命名为表4。通过sumif函数对《数据透视表》中不同负责人的不合格数量进行求和统计：
求B43：选中之后使用“Alt + +”快捷键求和。
计算公式如下：
1、不合格数公式：=SUMIF(数据透视表!$A:A,[@负责人],数据透视表!$C:C)。
2、占比公式：=[@不合格数]/$B43*100 （B43为不合格总数）。
4.5 获取每日合格率统计表
在“统计表”工作表中手动输入蓝色区域的文字，方便统计指定月份每日数据。之后按照公式求某个月每日的生产数量、不合格数量、合格率。
计算公式如下：
1、在生产数量位置输入公式：=SUMIF(数据透视表!$A:A,B49,数据透视表!$B:B) 将公式向右拉动填充至31位置；
2、在不合格数量位置输入公式：=SUMIF(数据透视表!$A:A,B49,数据透视表!$C:C) 将公式向右拉动填充值31位置；
3、在合格率位置输入公式：= IFERROR((B50-B51)/B50,"") 将公式向右拉动填充值31位置；
4、在辅助列所有位置输入1。
5. 可视化数据呈现
5.1 季度数据统计表的呈现
将季度数据显示在看板页面：在看板对应位置输入“=”号后选择季度统计表中对应的值即可。将四个季度对应的值全部用同样方式输入即可。
5.2 车间不合格数量排名统计表的呈现
通过VLOOKUP+if函数反向查找对应排名的生产线和不良数量。
在下方区域还可以做一个车间和数量的折线图，如下图所示：
公式如下：
1、排名获取公式：=TEXT(1,"0")。
2、生产线获取公式：=VLOOKUP(B12,IF({1,0},表3[[#全部],[排名]],表3[[#全部],[生产车间]]),2,FALSE)。
2、数量获取公式：=IFERROR(VLOOKUP(C13,下拉信息!$J:$K,2,FALSE),'')。
5.3 每日合格率统计表的呈现
插入条形图：选择合格率所有数据（下图红框区域）点击插入图表。
- 选择图表。右键选择数据
- 添加辅助列数据：点击添加按钮。
- 在红色框区域选择辅助列的值。并点击排序按钮，将系列2的值排到上面，如下图所示。
- 图表设置：将图表系列重叠调为100%。然后再将系列2的图表填充设置为无填充，将边框颜色设置为绿色。将系列1填充为黄色。
- 将设置好的图表剪切到看板主界面对应位置，并拖动大小。更改背景填充颜色为深蓝色、横纵坐标字体的颜色为白色。
- 切片器设置：将数据透视表的切片器剪切至看板主界面对应位置：在设计位置输入12（对应12个月份），并调节宽度与高度到合适位置，如下图所示。
5.4 负责人不合格数量占比统计表的呈现
选择负责人和占比数据插入旭日图或环形图即可。将插入的图表剪切至看板对应位置，调整大小和背景颜色即可。
调整大小与格式后如图所示：
负责人对应的不合格数量的计算公式如下：
不合格数量：=SUMIF(数据透视表!$A:A,G23,数据透视表!$C:C)。
5.5 年度数据统计表的呈现
全年信息汇总也是通过VLOOKUP+IF函数，通过排名反向查找项目名，生产数量、不良数量等信息。
计算公式如下：
1、排名：= TEXT(1,"0")。
2、项目名：=VLOOKUP(J22,IF({1,0},表1[[#全部],[排名]],表1[[#全部],[项目名]]),2,FALSE)。
3、生产数量：=VLOOKUP(J22,IF({1,0},表1[[#全部],[排名]],表1[[#全部],[生产数量]]),2,FALSE)。
4、不合格数量：=VLOOKUP(J22,IF({1,0},表1[[#全部],[排名]],表1[[#全部],[不合格数量]]),2,FALSE)。
5、合格率：=VLOOKUP(J22,IF({1,0},表1[[#全部],[排名]],表1[[#全部],[合格率]]),2,FALSE)。
5.6 制作动态标题
每个动态表的标题中添加 “动态月”。只需要在标题里面 输入 以下公式：
1、= 数据透视表!$B1&"月 车间不合格数量排名"。
2、= 数据透视表!$B1&"月 每日合格率统计"。
3、= 数据透视表!$B1&"月 负责人不合格数量占比"。
开源资源： 数据和excel文档
非常荣幸您能阅读到最后，希望文章中的内容能够帮助到您。
本博客内容均为博主原创，未经授权，请勿转载，谢谢！