以制作生产质量数据可视化看板为例,介绍用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、合格率计算公式:=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)*1004、排名公式:=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的图表填充为绿色,边框设置为无填充。系列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文档之后我会同步到git。