- Java锁的逻辑(结合对象头和ObjectMonitor)
- 还在用饼状图?来瞧瞧这些炫酷的百分比可视化新图形(附代码实现)⛵
- 自动注册实体类到EntityFrameworkCore上下文,并适配ABP及ABPVNext
- 基于Sklearn机器学习代码实战
以制作生产质量数据可视化看板为例,介绍用excel制作可视化看板的思路以及数据透视表、Vlookup、sumif、rank等常用函数的用法。主要 参考文章 ,我补充数据并完善了制作可视化看板的思路,下面内容是对可视化看板选择性的复现。开源的数据和excel文档见文末.
确定可视化看板的内容和排版布局.
- 确定需要展示的内容(图表的标题和展示字段或者形式)
- 确定看板的排版和布局(颜色、每个模块的大小等)
数据加工 。
- 第一次处理:整理《原始数据》,增加多个字段并创建表,形成《生产质量数据表》。
- 第二次处理:根据展示内容在《生产质量数据表》中插入《数据透视表》,并选择适当的切片器。
- 第三次处理:基于《生产质量数据表》和《数据透视表》进行数据的统计汇总,完成《统计表》。
数据呈现 。
- 将《统计表》中汇总的表格信息以表格或者图形的形式呈现到可视化看板中。
目标: 制作生产质量可视化看板.
原始指标: 《原始数据》表中字段为项目名、生产车间、负责人、生产数量、不合格数量、生产日期.
展示内容:
1、静态【年表】各项目这一年的生产情况——每个项目的生产数量、不合格数量、合格率,并排名.
2、静态【季度表】各季度的生产情况——每个季度的生产数量、不合格数量和合格率.
3、动态【月表】每个月的生产情况 。
- 车间——每个车间的生产数量及排名。
- 负责人——每个负责人的负责的生产线出现的不合格数量及占比。
- 每日——每天进行生产的生产线的合格率及占比。
根据内容增加指标: 合格率、月、日、季度.
确定数据透视表的 展示项 :日、月、负责人、项目、生产车间.
确定数据透视表的 求和项 :生产数量、不合格数量.
确定每个 统计表的标题和字段 :
1、年表:
- 标题:全年信息汇总(按项目合格率排名);
- 字段:项目名、生产数量、不合格数量、合格率、排名;
2、季度表:
- 标题:X季度;
- 字段:生产数量、不合格数量、合格率;
3、月表:
- 标题:X月 生产车间不合格数量排名;
- 字段:排名、生产车间、不合格数量。
- 标题:X月 负责人不合格数量占比;
- 字段:负责人、生产数量。
- 标题:X月 每日合格率统计;
- 字段:日、生产数量、不合格数量、合格率、辅助列。
关键 :分析逻辑清晰,明确要展示的内容,有主次之分.
- 排班布局要合适,将重点集中在看板中关键区域。
- 利用有限的页面展示更多内容,所以布局要均匀,比如说页面左右对称等。
- 图表颜色舒适。可以在 大作 或者小红薯上面找些图片模板做参考。
- 展示的图表不要过于复杂,尽量选择常见的柱形图、扇形图、折线图等。因为越简单的图表越容易解读。
选中表格区域填充。这里填充为蓝色(#203764).
将要展示的信息按区域划分。方便后面数据的输出展示。对应区域用深蓝色(#021A3C)填充。调整格式并输入文字,最终页面如下:
打开《原始数据》工作表(该工作表相当于数据库,用来存放原始数据),拿到原始数据后,为给数据看板提供展示的数据,需要对数据做一些处理,在这里我们利用公式计算出生产的合格率、月、日、季度.
在表格中输入列名:合格率、月、日、季度后利用公式计算出数值,再利用快捷键“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)
有了上面的数据,接着我们使用数据透视表对数据进一步处理.
很多统计都可以基于数据透视表完成,所以在对原始数据处理之后,使用数据透视表完成可视化看板的动态部分.
选择《生产质量数据》表,插入数据透视表.
弹出的对话框点击确定,并将新的表格命名为《数据透视表》.
在数据字段列表区域按下图拖动字段到对应位置.
切片器样式的设计参考 Excel切片器怎么修改颜色?
新建一个用于存放统计数据的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")
在统计表中通过公式获取《生产质量数据》每一个季度的数据统计。输入季度、生产数量、不合格数量、合格率之后,利用快捷键“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")&"%"。
输入生产车间、不合格数量、排名之后,利用快捷键“CTRL + T”创建表,重命名为表3。通过 sumif 函数对《数据透视表》中不合格数量求和统计再通过rank函数进行排名统计:
计算公式如下:
1、不合格数量计算公式:=SUMIF(数据透视表!$A:A,[@生产车间],数据透视表!$C:C)。
2、排名计算公式:=TEXT(RANK([@不合格数量],[不合格数量]),"0")。
输入负责人、不合格数量、占比之后,利用快捷键“CTRL + T”创建表,重命名为表4。通过sumif函数对《数据透视表》中不同负责人的不合格数量进行求和统计:
求B43:选中之后使用“Alt + +”快捷键求和.
计算公式如下:
1、不合格数公式:=SUMIF(数据透视表!$A:A,[@负责人],数据透视表!$C:C)。
2、占比公式:=[@不合格数]/$B43*100 (B43为不合格总数)。
在“统计表”工作表中手动输入蓝色区域的文字,方便统计指定月份每日数据。之后按照公式求某个月每日的生产数量、不合格数量、合格率.
计算公式如下:
1、在生产数量位置输入公式:=SUMIF(数据透视表!$A:A,B49,数据透视表!$B:B) 将公式向右拉动填充至31位置;
2、在不合格数量位置输入公式:=SUMIF(数据透视表!$A:A,B49,数据透视表!$C:C) 将公式向右拉动填充值31位置;
3、在合格率位置输入公式:= IFERROR((B50-B51)/B50,"") 将公式向右拉动填充值31位置;
4、在辅助列所有位置输入1。
将季度数据显示在看板页面:在看板对应位置输入“=”号后选择季度统计表中对应的值即可。将四个季度对应的值全部用同样方式输入即可.
通过VLOOKUP+if函数反向查找对应排名的生产线和不良数量.
在下方区域还可以做一个车间和数量的折线图,如下图所示:
公式如下:
1、排名获取公式:=TEXT(1,"0")。
2、生产线获取公式:=VLOOKUP(B12,IF({1,0},表3[[#全部],[排名]],表3[[#全部],[生产车间]]),2,FALSE)。
2、数量获取公式:=IFERROR(VLOOKUP(C13,下拉信息!$J:$K,2,FALSE),'')。
插入条形图:选择合格率所有数据(下图红框区域)点击插入图表.
选择图表。右键选择数据 。
添加辅助列数据:点击添加按钮.
在红色框区域选择辅助列的值。并点击排序按钮,将系列2的值排到上面,如下图所示.
选择负责人和占比数据插入旭日图或环形图即可。将插入的图表剪切至看板对应位置,调整大小和背景颜色即可.
调整大小与格式后如图所示:
负责人对应的不合格数量的计算公式如下:
不合格数量:=SUMIF(数据透视表!$A:A,G23,数据透视表!$C:C)。
全年信息汇总也是通过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)。
每个动态表的标题中添加 “动态月”。只需要在标题里面 输入 以下公式:
1、= 数据透视表!$B1&"月 车间不合格数量排名"。
2、= 数据透视表!$B1&"月 每日合格率统计"。
3、= 数据透视表!$B1&"月 负责人不合格数量占比"。
注:数据和excel文档之后我会同步到git.
最后此篇关于Excel制作可视化看板的思路及操作的文章就讲到这里了,如果你想了解更多关于Excel制作可视化看板的思路及操作的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。
我想制作一个引用另一个 excel 文件中的单元格的公式。我已经弄清楚了,如下所示: ='C:\Users\17\Desktop\[JAN-11 2011.xlsx]1'!$H$44 但由于此工作表中
有谁知道是否可以在 Excel 中生成缺少地址门牌号的报告? 例如,我们在 Apple St (no.5, 9, 11) 有三个地址记录,是否可以生成一个报告: 列出工作簿中每条街道的所有记录街道编号
这个问题已经有答案了: VBA auto hide ribbon in Excel 2013 (7 个回答) 已关闭 4 年前。 我试图在打开工作文件时隐藏我的丝带。 我已点击以下链接,但不断收到运行
我编写了一个 VBA 程序来删除元音。我无法从 excel 调用该函数。我收到 #NAME 错误。下面的代码 Function REMOVEVOWELS(Txt) As String 'Removes
嗨,我正在尝试在 MS excel 中应用一个函数(正确函数) 但是当我编写这个函数并使用填充句柄将其复制到其他单元格时,我在所有复制的单元格中得到相同的输出。 但是当我点击单元格时,引用是好的。但结
假设我有一个格式如下的电子表格: Sheet 1 | Sheet 2 name email | name e
我正在尝试简化财务报告中的数据输入,因此我尝试使用 Excel Visual Basic 制作表格。 到目前为止我做了2个用户表单,以后我会做5个。我做了用户表单,以便数据输入运算符(operator
我需要对单元格公式而不是单元格内容执行 Mid 或 Find。 如果我的单元格公式是: =[功能](Arg1, Arg2, Arg3) 我需要能够将 Arg2 提取到另一个单元格。 如果不使用 VBA
我想用 VBA 管理嵌入在另一个 Excel 文件中的 Excel 文件。我可以使用 .docx 文档找到很多结果,但我坚持使用 .xlsx 文档。 我最后一次尝试是使用 OLE 对象,但停留在“Sa
我最近一直在尝试使用 perl 和一些模块来读取 Excel 文件,尤其是单元格的格式。 例如,我写了一段使用 ParseExcel 模块读取单元格背景颜色的 perl 代码。然而,在测试时我注意到对
我目前正在使用 Maatwebsite 的 Excel 包,并且能够很好地生成一个包含我想要的列和值的表格,但我希望能够生成表格,其他表格位于单个 Excel 工作表的下方。可能吗? 上面附上的屏幕截
我需要以下方面的指导。我有一个包含 150000 条记录的文件 (excel)。收到另一个包含 5000-6000 条记录的 excel 文件,需要根据第二个文件中信息的某些条件删除该行。 我使用字典
我有我认为的标准公式,根据我使用的 Excel 版本、Excel 365 或 Excel 2019 的不同,它的行为会有所不同 =IF(F5=$M$1;IFERROR(IF(AND(IFERROR(F
信息: 我有一个名为 Demo.xlsm 的 Excel 文件 此文件包含一个名为 UserForm1 的用户表单,该用户表单会在打开文件时自动加载。 打开文件时,名为 Demo.xlsm 的工作簿也
我在A Excel工作表中有一个列,其值是1 1 1 2 2 2 3 3 3 4 4 4....,在B Excel工作表中有另一列,其值1 2 4 ....,什么我想要的是从 B 读取值并查看它们是否
所以,我有这个问题,我想通过使用 OR 函数检查调整列的条件来找到列的平均值,我尝试将 OR 放入 AverageIf 函数,失败,还尝试了“Average(IF( OR("再次不是正确的返回。认为这
假设我想要这种类型的formula = SUM(startcell:endcell)的答案,但是startcell和endcell组件发生了变化。 因此,我希望能够使用 和 中的任何值,而不是直接在公
我正在寻找一个简单的 Excel 宏,它可以根据单元格中的特定数字/值将行从一张工作表复制到 Excel 中的另一张工作表。我有两张纸。一个称为“master”,另一个表称为“top10”。 这是数据
我正在尝试调用另一个工作簿中的 Excel 宏。它是一个特定于工作表的宏,但 Microsoft 文档和网上研究给出的语法仅提供了一种仅通过工作簿访问宏的方法。该语法是: Application.Ru
我检查了很多不同的帖子,但似乎找不到我正在寻找的确切代码。另外,我以前从未使用过 VBA,因此我尝试从其他帖子中获取代码并输入我的信息以使其正常工作。还没有运气。在工作中,我们有一个 Excel 薪资
我是一名优秀的程序员,十分优秀!