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

酸菜鱼土豆大侠

已于 2023-08-02 15:43:19 修改

阅读量5k

点赞数 18

分类专栏： Excel 文章标签： excel 信息可视化

于 2023-03-09 12:25:58 首次发布

本文链接：https://blog.csdn.net/chengyj0505/article/details/129420448

版权

Excel 专栏收录该内容

8 篇文章 8 订阅

订阅专栏

以制作生产质量数据可视化看板为例，介绍用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&"月 负责人不合格数量占比"。

制作完的可视化看板如下：

四、补充：

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

1、样式一

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

在这里插入图片描述

2、样式二

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

在这里插入图片描述

3、样式三

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

在这里插入图片描述

4、样式四

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

在这里插入图片描述

开源资源： 数据和excel文档

非常荣幸您能阅读到最后，希望文章中的内容能够帮助到您。
如有转载，请注明出处，谢谢！

酸菜鱼土豆大侠

关注

18
点赞
踩
85

收藏

觉得还不错? 一键收藏
5
评论
Excel 制作可视化看板的思路及操作附开源数据

以制作生产质量数据可视化看板为例，介绍用excel制作可视化看板的思路以及数据透视表、Vlookup、sumif、rank等常用函数的用法。下面内容是对可视化看板选择性的复现。开源的数据和excel文档见文末。
复制链接

扫一扫