Excel 制作可视化看板的思路及操作

以制作生产质量数据可视化看板为例，介绍用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. 1、合格率计算公式：=TEXT(((C2-D2)/C2)*100,"0.00")&"%"
2. 2、月份计算公式：=MONTH(F2)
3. 3、日计算公式：=DAY(F2)
4. 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. 1、生产数量公式：=SUMIF(生产质量数据!$A:A,[@项目名],生产质量数据!$C:C)；
2. 2、不合格数量公式：=SUMIF(生产质量数据!$A:A,[@项目名],生产质量数据!$D:D)；
3. 3、合格率公式：=((B3-C3)/B3)*100
4. 4、排名公式：=TEXT(RANK([@生产数量],[生产数量]),"0")

复制代码

4.2 获取季度数据统计表

在统计表中通过公式获取《生产质量数据》每一个季度的数据统计。输入季度、生产数量、不合格数量、合格率之后，利用快捷键“CTRL + T”创建表，重命名为表2。

计算公式如下：

1. 1、一季度生产数量公式：=SUMIFS(生产质量数据!$C:C,生产质量数据!$J:J,1)。其他季度只需修改公式中的数字1为对应季度即可。
2. 2、一季度不合格数量公式：=SUMIFS(生产质量数据!$D:D,生产质量数据!$J:J,1)。其他季度只需修改公式中的数字1为对应季度即可。
3. 3、合格率可直接根据生产数量和不合格数量直接获得：=TEXT(((B17-C17)/B17)*100,"0.00")&"%"。

复制代码

4.3 获取车间不合格数量排名统计表

输入生产车间、不合格数量、排名之后，利用快捷键“CTRL + T”创建表，重命名为表3。通过 sumif 函数对《数据透视表》中不合格数量求和统计再通过rank函数进行排名统计：

计算公式如下：

1. 1、不合格数量计算公式：=SUMIF(数据透视表!$A:A,[@生产车间],数据透视表!$C:C)。
2. 2、排名计算公式：=TEXT(RANK([@不合格数量],[不合格数量]),"0")。

复制代码

4.4 获取负责人不合格数量统计表

输入负责人、不合格数量、占比之后，利用快捷键“CTRL + T”创建表，重命名为表4。通过sumif函数对《数据透视表》中不同负责人的不合格数量进行求和统计：
求B43：选中之后使用“Alt + +”快捷键求和。

计算公式如下：

1. 1、不合格数公式：=SUMIF(数据透视表!$A:A,[@负责人],数据透视表!$C:C)。
2. 2、占比公式：=[@不合格数]/$B43*100 （B43为不合格总数）。

复制代码

4.5 获取每日合格率统计表

在“统计表”工作表中手动输入蓝色区域的文字，方便统计指定月份每日数据。之后按照公式求某个月每日的生产数量、不合格数量、合格率。

计算公式如下：

1. 1、在生产数量位置输入公式：=SUMIF(数据透视表!$A:A,B49,数据透视表!$B:B) 将公式向右拉动填充至31位置；
2. 2、在不合格数量位置输入公式：=SUMIF(数据透视表!$A:A,B49,数据透视表!$C:C) 将公式向右拉动填充值31位置；
3. 3、在合格率位置输入公式：= IFERROR((B50-B51)/B50,"") 将公式向右拉动填充值31位置；
4. 4、在辅助列所有位置输入1。

复制代码

5. 可视化数据呈现

5.1 季度数据统计表的呈现

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

5.2 车间不合格数量排名统计表的呈现

通过VLOOKUP+if函数反向查找对应排名的生产线和不良数量。

在下方区域还可以做一个车间和数量的折线图，如下图所示：

公式如下：

1. 1、排名获取公式：=TEXT(1,"0")。
2. 2、生产线获取公式：=VLOOKUP(B12,IF({1,0},表3[[#全部],[排名]],表3[[#全部],[生产车间]]),2,FALSE)。
3. 2、数量获取公式：=IFERROR(VLOOKUP(C13,下拉信息!$J:$K,2,FALSE),'')。

复制代码

5.3 每日合格率统计表的呈现

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

• 选择图表。右键选择数据
  
• 添加辅助列数据：点击添加按钮。
  
• 在红色框区域选择辅助列的值。并点击排序按钮，将系列2的值排到上面，如下图所示。
  

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

• 将设置好的图表剪切到看板主界面对应位置，并拖动大小。更改背景填充颜色为深蓝色、横纵坐标字体的颜色为白色。
  
• 切片器设置：将数据透视表的切片器剪切至看板主界面对应位置：在设计位置输入12（对应12个月份），并调节宽度与高度到合适位置，如下图所示。
  

5.4 负责人不合格数量占比统计表的呈现

选择负责人和占比数据插入旭日图或环形图即可。将插入的图表剪切至看板对应位置，调整大小和背景颜色即可。

调整大小与格式后如图所示：

负责人对应的不合格数量的计算公式如下：

1. 不合格数量：=SUMIF(数据透视表!$A:A,G23,数据透视表!$C:C)。

复制代码

5.5 年度数据统计表的呈现

全年信息汇总也是通过VLOOKUP+IF函数，通过排名反向查找项目名，生产数量、不良数量等信息。

计算公式如下：

1. 1、排名：= TEXT(1,"0")。
2. 2、项目名：=VLOOKUP(J22,IF({1,0},表1[[#全部],[排名]],表1[[#全部],[项目名]]),2,FALSE)。
3. 3、生产数量：=VLOOKUP(J22,IF({1,0},表1[[#全部],[排名]],表1[[#全部],[生产数量]]),2,FALSE)。
4. 4、不合格数量：=VLOOKUP(J22,IF({1,0},表1[[#全部],[排名]],表1[[#全部],[不合格数量]]),2,FALSE)。
5. 5、合格率：=VLOOKUP(J22,IF({1,0},表1[[#全部],[排名]],表1[[#全部],[合格率]]),2,FALSE)。

复制代码

5.6 制作动态标题

每个动态表的标题中添加  “动态月”。只需要在标题里面 输入 以下公式：

1. 1、= 数据透视表!$B1&"月 车间不合格数量排名"。
2. 2、= 数据透视表!$B1&"月 每日合格率统计"。
3. 3、= 数据透视表!$B1&"月 负责人不合格数量占比"。

复制代码

注：数据和excel文档之后我会同步到git。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！