Excel 制作可视化看板的思路及操作 附开源数据

以制作生产质量数据可视化看板为例，介绍用excel制作可视化看板的思路以及数据透视表、Vlookup、sumif、rank等常用函数的用法。主要参考文章，我补充数据并完善了制作可视化看板的思路，下面内容是对可视化看板选择性的复现。开源的数据和excel文档见文末。

文章目录

一、完成效果图

二、制作可视化看板的思路

• 确定可视化看板的内容和排版布局。- 确定需要展示的内容（图表的标题和展示字段或者形式）- 确定看板的排版和布局（颜色、每个模块的大小等）
• 数据加工- 第一次处理：整理《原始数据》，增加多个字段并创建表，形成《生产质量数据表》。- 第二次处理：根据展示内容在《生产质量数据表》中插入《数据透视表》，并选择适当的切片器。- 第三次处理：基于《生产质量数据表》和《数据透视表》进行数据的统计汇总，完成《统计表》。
• 数据呈现- 将《统计表》中汇总的表格信息以表格或者图形的形式呈现到可视化看板中。

三、制作可视化看板

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文档

四、补充：

虽然可视化看板的布局和报表内容都不错，但是样式看起来不是很高级，我研究了一下看板的样式，可以把看板样式分为两类，一类是边界感清晰，暗色和亮色结合，另外一种就是边界感不清晰，颜色也不是特别亮（但要有一点点亮色），以此做出来3种样式。

1、样式一

通过设置单元格渐变的样式（主要用到的颜色#203764、#305496、#be04f2），可以看到图表之间的边界感很明显，亮色只有2种，主要以一种亮色为主。缺点紫色的存在感太强，看起来有点花哨。

2、样式二

尝试弱化一下边界，添加了圆角的形状框。

3、样式三

改变亮色太显眼的问题，清除所有单元格的颜色，用（#0f1022）填充，然后在每个表或者图上面覆盖一个矩形形状框，设置阴影和填充颜色（#093b87），调整透明度就可以。

4、样式四

也可以修改亮色，换一个比较暗一点的颜色。