项目：EXCEL搭建数据看板

该文章为搭建看板文档，如有错误还望海涵，评论系统还未搭建，如有疑问可通过邮箱联系我。

明确目标

我们首先要明确搭建数据看板的目标是什么,如：

部门	用途	看板类型
生产	保障生产效率、质量和成本可控	生产监控，质量管控，成本优化，设备管理
运营	提升业务效率、用户体验和业绩增长	业务流程监控，用户行为分析，业绩目标追踪，市场与竞争分析
财务	确保资金安全、控制财务风险、支撑战略决策	资金与现金流监控，成本与利润分析，预算与实际对比，财务风险预警

我们搭建看板的目的是为了监控生产和分析生产，激励生产的作用。

确定指标

明确了我们的目标之后，我们需要确认我们所需的指标体系——即用什么可以衡量我们的我们的目的。这里我们以生产质量为例：

生产质量=产品合格率=（生产总数-不合格数）/生产总数(因为浮点数的原因，不要使用1-不合格率)

确认看板模块

时间维度：

日度数据，周度数据，月度数据，季度数据，年度数据

空间维度：

个人，小组长，车间，部门，公司可视化：

表格，折线图（趋势），饼图（占比），柱状图（比较）

数据

我们选用CSDN开源的生产数据，数据来源链接我会放业尾。

对原始数据其如下：

项目名	生产车间	生产数量	不合格数量	负责人	日期
SC001	一车间	1409	2	王成蕾	2021-1-1
SC002	一车间	733	2	刘杰	2021-1-1
SC003	二车间	1071	1	张博	2021-1-1
SC004	三车间	1435	3	赵丽	2021-1-1

我们日期拆解为年，月和日。我们使用可以使用日期函数和取数函数（取数函数需将日期改为aaaa-bb-cc形式。），我这里使用日期函数。

1
2
3
4
5


year()#提取年 
ceiling(month()/3,1)#提取季度 
month()#提取月份
WEEKNUM()#提前周
day()#提前日

处理后结果：

项目名	生产车间	生产数量	不合格数量	负责人	日期	合格率	月	日	季度
SC001	一车间	1409	2	王成蕾	2021-1-1	99.86%	1	1	1
SC002	一车间	733	2	刘杰	2021-1-1	99.73%	1	1	1
SC003	二车间	1071	1	张博	2021-1-1	99.91%	1	1	1

数据透视表

我们对生产效率出于及时性只考虑到月和日这一层次。通过筛选可以看出负责人负责多个车间，所以行从包含角度来排列，从上到下依次为，日，负责人，车间，项目名字段。值则为生产数量，不合格数量字段。筛选器选用月。

切片器：切片器是 Excel 中一种强大的数据分析工具，它可以以直观、交互的方式快速筛选数据透视表中的数据。

插入以月筛选的切片器，通过添加按钮数将其转化为横向。

图表的制作

表

年表：

按项目名汇总生产总量与不合格生产总量。

1

=SUMIF(Sheet3!$A$2:$A$50001,A2,Sheet3!$C$2:$C$50001)#数据源使用原始数据表

月表：

从分析的角度我们需要将异动进行拆解归因，从我们的现有数据来看可以将其拆解为“人”，“产品”，“厂房”——负责人，项目名，生产车间。不合格数=人+设备+原材料+方法+环境（厂房）

所以我们需要分别建立月负责人，月项目名，月生产车间。因为我们需要其能按月变动，所以数据源应为数据透视表。

1

=SUMIF(Sheet4!$C:$C,A20,Sheet4!$F:$F)#月负责人，月项目名，月生产车间都类似

日表：

月份	=数据透视表的月份
日期	1-31（从1-31号，共31列）
生产数量	=SUMIF(数据透视表!$A:A,B49,数据透视表!$B:B)
不合格数量	=SUMIF(数据透视表!$A:A,B49,数据透视表!$C:C)
合格率	=（生产总数-不合格数）/生产总数

图

日合格率波动图

行列转化：

日期	合格率	上限	下限
1	0.997183623	0.997651133	0.996456577

1

==XLOOKUP(A45,$B$35:$AF$35,$B$38:$AF$38)

异常值计算：

1
2
3
4
5


Q1=QUARTILE(B38:AF38,1)
Q3=QUARTILE(B38:AF38,3)
IQR=Q3-Q1
异常上限=Q3+1.5IQR
异常下限=Q1-1.5IQR

纵坐标轴范围：上下限小数点四位+-1

蓝色虚线为趋势线

月份天数不同：将31和30加一个空值检验，为空等于一个值即可。

1

=IF(XLOOKUP(A75,$B$35:$AF$35,$B$38:$AF$38)="",B74,XLOOKUP(A75,$B$35:$AF$35,$B$38:$AF$38))

月负责人不合格占比饼图

数据源为月表中的月负责人表

布局

具体布局思路可以业尾的如何搭建看板链接，或许可以对你有一些启发。

数据通过xlookup进行查找即可。

看板搭建总结

纯excel搭建不够方便，需要操作的太多，数据多有点卡，可视化效果不够好，实时数据可能还需要外部导入，直接从数据库接口获取数据。可以尝试Tableau，Power BI，或excel的power query。

改进方向

实时动态数据，为起到监控作用，其需要具有实时数据输入功能。

异动归因，原设计对异动原因无法有效分析。

异动标准，实时数据的异动标准可能需要历史数据来进行确认，或这用与预测数据和标准数据的偏离度来确认。

Excel看板搭建