excel可视化看板（详细讲解如何用excel做可视化看板）

可视化看板教程（低代码）

效果图

说明：本文以最近发布的《生产质量可视化看板》为例，详细介绍制作步骤。包含代码及注释。所有公式全部列出。以图文结合的方式展现。

主要内容介绍：

可视化界面的布局

数据的录入与读取

信息汇总分类

1、可视化界面的布局

设置工作表的大小,一般设置为横向，页边距0.2，表格设置见下图。

1.1看板底色设置。选中表格区域填充。这里填充为蓝色

1.2可视化界面布局。将要展示的信息按区域划分。方便后面数据的输出展示。对应区域用深蓝色填充。

2、数据录入与读取

2.1录入数据之前需要新建一个工作表作为数据源（也可以称之为数据库），方便我们数据录入后存储。点击“ ”创建图标后命名为“数据源”。

2.2 数据源表格的设置：将我们需要录入数据的信息横向输入在数据源的表格内并按”CTRL T”创建表。红色框选区域数据不需要录入，可根据前面的录入数据通过公式计算。

2.2.1 合格率计算公式：=(C3-D3)/C3

2.2.2 月份转换公式：=MONTH(F3)

2.2.3 日转换公式：=DAY(F3)

2.2.4 季度转换公式：=CHOOSE([@月],1,1,1,2,2,2,3,3,3,4,4,4)

2.3 创建好数据库和录入公式后可手动在数据库内录入几条数据测试是否有问题。无问题后进入下一步。

2.4 从看板录入数据：

2.4.1在看板对应位置输入要录入数据的标题和输入框。

2.4.2 在开发工具内插入按钮，如下图所示：

2.4.3 将按钮改名为“录入”。并将按钮拖放到合适位置。

2.4.4 进入VBA界面。

2.4.5 插入模块：点击右边空白处，选择插入-模块。

2.4.6 在模块内输入代码

Sub 数据录入() '创建数据录入放入宏

a = Sheet2.Cells(Rows.Count, 1).End(xlUp)(2, 1).Row 数据源单元格从下往上数，读取第一列最后一个非空单元格的值并下移一行。（a 为下图的行号。）

If Sheet1.Cells(6, 2) = "" Or Sheet1.Cells(6, 3) = "" Or Sheet1.Cells(6, 4) = "" Then

（Sheet1.Cells(6, 2)：项目名输入框的行号和列号）

MsgBox "请先输入相关信息！"

判断输入框是否有输入内容，如过未输入则弹出"请先输入相关信息！"

的提示框。

Exit Sub

退出宏

End If

数据赋值，将输入框的内容赋值到数据库对应位置， a为行号

Sheet2.Cells(a, 1) = Sheet1.Cells(6, 2) '项目名赋值

Sheet2.Cells(a, 2) = Sheet1.Cells(6, 3) '生产线赋值

Sheet2.Cells(a, 3) = Sheet1.Cells(6, 4) '生产数量赋值

Sheet2.Cells(a, 5) = Sheet1.Cells(8, 2) '责任归属赋值

Sheet2.Cells(a, 4) = Sheet1.Cells(8, 3) '不良数量赋值

Sheet2.Cells(a, 6) = Sheet1.Cells(3, 17) '日期赋值

MsgBox "信息录入成功！"

Sheet1.Range("b6:d6") = "" '单元格清空

Sheet1.Range("b8:c8") = "" '单元格清空

End Sub

2.4.7 指定宏:选择刚刚插入的“录入”按钮，右键选择指定宏，选择刚刚在模块中创建的宏即可。

2.5 季度数据统计表：在数据源表格中通过公式获得每一个季度的数据统计。

2.5.1 一季度生产数量公式：=SUMIF(表1[季度],1,表1[生产数量])。其他季度只需修改公式中的数字1为对应季度即可。

2.5.2 一季度不良数量公式：=SUMIF(表1[季度],1,表1[不良数量])。其他季度只需修改公式中的数字1为对应季度即可。

2.5.3 合格率可直接根据生产数量和不良数量直接获得：=IFERROR((Q3-R3)/Q3,"0")

2.5.4 将季度数据显示在看板页面：在看板对应位置输入“=”号后选择季度统计表中对应的值即可。将四个季度对应的值全部用同样方式输入即可。

2.6 数据透视表：选择数据源的数据表格，插入数据透视表。

2.6.1弹出的对话框点击确定，并将新的表格命名为数据透视表。

2.6.2 在数据字段列表区域按下图拖动字段到对应位置。

2.6.3 添加切片器：将月份字段添加为切片器（这样便可通过选择月份任意显示对应月份的数据）

2.6.4 新建“数据统计”工作表，下图蓝色区域手动输入，方便统计指定月份每日数据。

2.6.5 在生产数量位置输入公式：=SUMIF(数据透视表!$A:$A,数据统计!B2,数据透视表!$B:$B) 将公式向右拉动填充至31位置

2.6.6 在生产数量位置输入公式：=SUMIF(数据透视表!$A:$A,数据统计!B2,数据透视表!$C:$C) 将公式向右拉动填充值31位置

2.6.7 在合格率位置输入公式：=IFERROR((B3-B4)/B3,"") 将公式向右拉动填充值31位置

2.6.8 将辅助列所有位置输入1.1

2.7 插入条形图：选择合格率所有数据（下图红框区域）点击插入图表。

2.7.1 选择图表。右键选择数据

2.7.2 添加辅助列数据：点击添加按钮。

2.7.3 在红色框区域选择辅助列的值。并点击排序按钮，将系列2的值排到上面，如下图所示。

2.7.4 图表设置：将图表系列重叠调为100%。然后再将系列2的图表填充设置为无填充，将边框颜色设置为绿色。将系列1的图表填充为绿色，边框设置为无填充。

2.7.5 设置好的图表如下图：

2.8 将设置好的图表剪切到看板主界面对应位置，并拖动大小。如下图所示。

2.9 切片器设置：将数据透视表的切片器剪切至看板主界面对应位置：在设计位置输入12（对应12个月份），并调节宽度与高度到合适位置。

3、下拉信息设置：

3.1 新建下拉信息工作表，并在工作表中输入对应内容，按Ctrl t创建超级表。

3.2 名称管理器：选中对应表格依次点击公式—根据所选内容创建定义名称—勾选首行—确定即可。按同样的方式将三个表格设置完成。

3.3 下拉信息设置：选中看板界面对应的输入框，依次选择数据--数据验证—序列，设置需要下拉选择的输入框。

4.信息汇总分类

4.1 根据下拉信息表格进行数据的汇总统计：过sumif函数对数据透视表中不良数量求和统计再通过rank函数进行排名统计：

4.1.1 不良数量计算公式：=SUMIF(数据透视表!$A:$A,[@生产线],数据透视表!$C:$C)

4.1.2 排名计算公式：=RANK([@不良数量],[不良数量]) COUNTIF(K4:$K$8,K4)-1

4.2 根据下拉信息表格良品责任归属统计：通过sumif函数对数据透视表中不同责任归属求和统计：

4.2.1 月度不良数量公式：=SUMIF(数据透视表!$A:$A,[@责任归属],数据透视表!$C:$C)

4.2.2 占比公式：=[@月度不良数量]/$P$2 （P2为月度不良总数）

4.3 年度数据统计汇总：通过通过sumIF函数对数据源数据汇总。再通过rank函数进行排名。

4.3.1 生产数量公式：=SUMIF(数据源!$A:$A,[@项目名],数据源!$C:$C)

4.3.2 不良数量公式：=SUMIF(数据源!$A:$A,[@项目名],数据源!$D:$D)

4.3.3 排名公式：=RANK([@合格率],[合格率]) COUNTIF(E4:$E$11,E4)-1

4.4 可视化数据呈现：

4.4.1 不良排名表格呈现：通过VLOOKUP if函数反向查找对应排名的生产线和不良数量。公式如下

生产线获取公式：=IFERROR(VLOOKUP(B13,IF({1,0},表3[[#全部],[排名]],表3[[#全部],[生产线]]),2,FALSE),"")

数量获取公式：=IFERROR(VLOOKUP(C13,下拉信息!$J:$K,2,FALSE),"")

4.4.2 不良品责任归属：选择责任归属和占比数据插入旭日图或环形图即可。将插入的图表剪切至看板对应位置，调整大小和背景颜色即可（这里不详细说明）

4.5全年信息汇总：全年信息汇总也是通过VLOOKUP IF函数，通过排名反向查找项目名，生产数量、不良数量等信息（4.4.1节可查看详细公式）

结语：本期教程就分享到这里。喜欢本文的话可在评论区留言和点赞支持，有疑问也可私信小编继续讨论。

,