利用Excel制作各类品质图表

1、利用Excel表格制作品质图表 BOPP 品管部培 训内内容容品质七大大手法与与EXCEL的的结合运运用MSA与与EXCEL的的结合运运用SPC与与EXCEL的的结合运运用日常工作作中项目目管理与与EXCEL的的结合运运用与PPT结合的的Excel文文档:培训目的的更深入了了解各品品质手法法的推算算和机理理更深入了了解EXCEL的函数数运用和和图表的的制作更方便的的进行日日常工作作和提高高工作质质量,进进一步实实现办公公自动化化品质七大大手法与与EXCEL的的结合运运用品质七大大手法与与EXCEL的的结合运运用QC七大大手法简简介:特征要因因图：寻寻找因因果关系系柏拉图：找找出“重重要的少少数

2、”直方图：了了解解数据分分布与制制程能力力层别法：按按层层分类，分别统统计分析析查检表：调调查查记录数数据用以以分析散布图：找找出出两者之之间的关关系管制图：了了解制制程变异异七大手法法手 法特点与作作用主讲会在”SPC与与EXCEL的的结合运运用”部部分细说说1.柏柏拉图与与EXCEL的的结合运运用首先让我我们来看看以下一一个关于于某款产产品进行行季度的的质量分分析:从此柏拉拉图可以以看出影影响的最最主要因因素(80%原原则).怎样去制制作完成成的呢?所涉及的的Excel函函数:=+-/*1.逻逻辑运算算函数:2.数数学和三三角函数数:SUM返返回回某一单单元格区区域中所所有数字字之和3.其

3、其他:$锁锁定目目标选项项.如如:SUM($C$8:H8):在在单元格格C8到到H8的的所有数数字只和和而C8项是不不随鼠标标拉动或或粘贴而而变更的的,H8项则会会变化.用以上提提及的函函数在Excel表格格里输入入,如下下:=C7/SUM($C$7:$I$7):单元格C7除于于单元格格C7至至I7的的数据之之和Ctrl+在工作sheet里面面进行显显示结果果和运算算式之间间的切换换.切换后结结果如下下:制作表格格:点击或:插插入图图表选择制图图区域,如下阴阴影区选取”两两轴线-柱图”完成以上上步骤,图形会出出现然后进行行装饰:加入:图表标题题;分类(X)轴;数值(Y)轴等等为了图表表左右Y轴

4、的数数据相关关,必须须让左右右Y轴数数据对齐齐:如:刚才才不良的的个数为为:116(左左轴)对对应应的右轴轴应该是是100%双击图表表上的数数值,会会弹出如如下提示示框:根据需要要调节最最大最小小值这样一个个柏拉图图就完美美的诞生生了!2.直直方图与与EXCEL的的结合运运用某客户的的电阻值值要求为为50020欧最近发现现客户的的产品波波动比较较大，所所以归纳纳了近段段时间的的连续七七批IQC来料料测量数数值，数数值如下下：第1批第2批第3批第4批第5批第6批第7批1489.1 476.4 507.4 526.9 495.5 506.5 503.4 2511.1 520.6 475.1 491

5、.4 469.4 500.8 500.5 3504.5 498.3 482.9 498.1 519.0 508.8 518.9 4494.0 491.1 513.3 490.5 480.3 484.3 528.8 5492.7 469.3 489.0 480.4 509.4 484.9 495.6 6492.3 492.7 520.1 534.5 501.4 507.6 507.5 7502.3 499.6 521.0 504.5 500.0 515.2 493.9 8505.2 501.3 481.7 479.8 480.5 482.3 493.3 9519.8 484.9 488.4 48

6、7.4 495.0 490.6 511.3 10485.6 507.5 502.5 497.8 501.0 518.5 527.2 如何对该该些数据据进行处处理呢？我们就就可以运运用直方方图就行行分析!从下图我我们可以以轻松的的分析出出数据的的分布情情况。怎样制作作出得体体美观的直方图图呢？所涉及的的函数:1.统统计函数数:COUNTIF计算区域域中满足足给定条条件的单单元格的的个数。2.数数学与三三角函数数:LOG按所指定定的底数数，返回回一个数数的对数数。返回一组组值中的的最大值值。返回一组组值中的的最小值值。MAXMIN将数字按按指定的的小数位位数进行行取整，利用句句号和逗逗号，以以小数

7、格格式对该该数进行行格式设设置，并并以文本本形式返返回结果果。3.文本本函数:FIXEDCONCATENATE将几个文文本字符符串合并并为一个个文本字字符串。步骤1：计算数数据中的的最大最最小值步骤2：计算全全距（最最大值-最小值值）步骤3：计算组组数K數據數組數50100610100250712250以上1020温馨提示示：右图图为组数数的参考考值步骤4：计算组组距（全全距/组组数K）步骤5：计算上上下组列列用CONCATENATE函函数合并并上下界界数值文文本，计计算如下下：步骤6：计算组组列间的的个数键入COUNTIF和和SUN函数运运算满足足条件的的个数步骤7：制作图图表把下面的的单元

8、格格选取并并点击直直方图即即可制作作开始直直方图图图表运算结果果如下需要留意意的是：点击列可可得左图图图表样样式，若若点击行行为右图图图表样样式，可可根据自自己的需需要选择择确定后，就完成成直观美美丽的直直方图了了！然后根据据图表找找出产品品存在的的差异。3.散散布图与与EXCEL的的结合运运用某钣金件件长度尺尺寸,由由于公司司和供应应商之间间一直在在数据上上面存在在差异,选取30个样样板,分分别注上上编号测测得数据据如下:所涉及的的函数:返回单元元格区域域 array1和和 array2之之间的相相关系数数。使用用相关系系数可以以确定两两种属性性之间的的关系。例如，可以检检测某地地的平均均温

9、度和和空调使使用情况况之间的的关系。语法CORREL(array1,array2)Array1第第一组组数值单单元格区区域。Array2第第二组组数值单单元格区区域。1.统统计函数数:CORREL表示多少少次方; 比如如:56就是是说5的的6次方方2.数学学运算符符:=FIXED(CORREL(C7:C36,D7:D36),2)在单元格格键入:双击:然后弹出出右图对对话框:=FIXED(C372,2)可容易得得到相关关系数的的结果右键图表表上的数数据点勾选显显示R平平方值完成上面面操作后后,一个漂亮亮的散布布图就这这样诞生生了相关系数数R可以以从图表表上或Excel函数数运算出出来,判判别原则

10、则:R90%,R280%为良好好,反之之为较差差.MSA与与EXCEL的的结合运运用评定测量量系统的的质量确定能对对每个零零件重复复读数的的测量系系统评估新的的测量量量具比较不同同的测量量方式，找到最最佳方案案找到并解解决测量量系统的的误差MSA意意义再现性稳定性线性重复性偏倚小样法交叉法计量型计数型MSA类类别:GR&R1.GR&R与EXCEL的结合合运用GR&R是评定定测量系系统的重重复性和和再现性性.所涉及的的函数:1.统统计函数数:AVERAGE返回参数数的平均均值（算算术平均均值）。SQRT返回正平平方根。2.逻逻辑函数数:IF执行真假假值判断断，根据据逻辑计计算的真真假值，返回不不

11、同结果果。可以使用用函数IF对对数值值和公式式进行条条件检测测。VLOOKUP在表格或或数值数数组的首首列查找找指定的的数值，并由此此返回表表格或数数组当前前行中指指定列处处的数值值。所涉及的的公式:%AV=100AV/TV%GRR=100GRR/TV%PV=100PV/TVndc=1.41(PV/GRR)重复性:再现性:GR&R:样板间变变差:%重复性性:%再现性性:%GR&R:样板间变变差:%EV=100EV/TV分级数:样板总变变差:步骤1: 先计计划表格格,如右右图:应该遵循循GR&R的取取样要求求和测量量要求:取样数量量:10pcs(编号号110)测量人员员:3人人(定义义为A,B,

12、C)测量次数数:3次次/人均值分为为3种:个人测量量单个样样板三次次的均值值X个人测量量10个个样板的的均值3.所所有测量量数据的的均值步骤2: 计算算均值和和极差:对应键入入公式:极差分为为4种:个人每个个样板测测量间的的Ra;2.个个人10个样板板间R的的平均值值3.所所有人的的平平均均值4.零零件均值值极差RpRa=MAX(B11:B13)-MIN(B11:B13)对应键入入公式:运算Ra后得步骤3: 计算算EV,AV,GR&R,PV,NDC:试验次数K120.886 30.5908套入步骤骤2的数数值,如如套入运算算的单元元格如D7,D7*D12为为EV值值运算式式运算后结结果如下下:

13、依以上公公式套入入AV/n/r等数值值,如23K20.70710.5231K320.707130.523140.447 50.403 60.374270.353480.337590.3249100.3146例如:样样板数目目为10时,取取值为0.3146经过=I7*G29运运算可得得出PV值ndc=1.41(PV/GRR)分级数:步骤4: 由结结果编写写条件式式判别原则则:当当GR&R10%时时,可以以接受;当10GR&R30%时时,不可可以接受受;当ndc=5时,可可以接受受;当ndc5时时,不可可以接受受=IF(J20/100)30%,不可以以接受,条条件下接接受)=IF(J285,不可可

14、接受,可可以接受受)%GR&RExcel版本本制作完完成!2.交交叉法与与EXCEL的的结合运运用随机选取取50个个产品（最好包包含10个左右右不良样样品）并并编号。确定测量量系统所所有参数数与正常常检验时时一样。三个测量量员分别别对每个个产品测测量三次次并对号号记录结结果，测测量时打打乱产品品顺序）。将测量结结果（合合格记录录1/OK，不不合格记记录0/NG）输入交交叉法计计算表格格。首先回顾顾以下交交叉法的的评估方方法:所涉及的的函数:1.逻逻辑函数数:执行真假假值判断断，根据据逻辑计计算的真真假值，返回不不同结果果。可以以使用函函数IF对对数值和和公式进进行条件件检测。AND在其参数数组

15、中，任何一一个参数数逻辑值值为TRUE，即返返回TRUE；任何何一个参参数的逻逻辑值为为 FALSE，即返返回FALSE。OR步骤1：计算人与与人交叉叉（A*B,B*C,A*C）交叉法运运算法则则：A*CB*DABDC=G5*E9/G9A=B*D/C注：期期待数量量是运运算出来来的，而而数量量则是是实测出出来的，数量量详细细计算如如下页。B总计01A0数量72375期望数量38.00 37.00 751数量47175期望数量38.00 37.00 75总计数量7674150期望数量7674150由上表可可以看出出需要根根据测量量的数据据计算出出：A=0&B=0A=0&B=1A=1&B=0A=1

16、&B=1同理得B&CA&CB=0&C=0B=0&C=1B=1&C=0B=1&C=1A=0&C=0A=0&C=1A=1&C=0A=1&C=1+Excel中中键入的的表达式式（参考考）：=IF(AND(测量量数据!B2=0,人人与人交交叉!B5=1),1,0)A=0,B=0A-B(same)=IF(测量数数据!B2=测测量数据据!E2,1,0)得出的运运算结果果如下：根据运算算的结果果用SUM函数数算出对对应的个个数，引用到指指定的单单元格中中人与标准准交叉（A*S,B*S,C*S）步骤2：人与标准交叉A&SB&S(同A)C&S (同A)A=0&S=0A=1&S=0A=0&S=1A=1&S=1步骤

17、3：计算交交互的Kappa结果果KappaABCA0.91 0.92 B0.91 0.93 C0.92 0.93 设计的运运算公式式：其中：Po为相相同判别别（如A=0&B=0和A=1&B=1）的对对应数量量占的比比率；Pe为相相同判别别（如A=0&B=0和A=1&B=1）的对对应期望望数量占占的比率率。表达式如如下：键入Excel的表达达式测量员A&B&C和S标准值值的Kappa值计算算同上。步骤4：交互的的Kappa判判别的结结论一致性判定标准：通用的比例法则是Kappa0.75则表示有很好的一致性，Kappa0.40则表示一致性不好。有效性判定标准有效性漏报率错误报警率可接受90%2%5

18、%边界接受但要改善80%5%10%不可接受80%5%10%Kappa交叉叉法完成成SPC与与EXCEL的的结合运运用CPK与与EXCEL的的结合运运用SPC是是过程统统计控制制的工具具，而过过程控制制与过程程能力CP&CPK紧紧密结合合。例：两供供应商（A&B）的产产品差异异较大，现对供供应商的的制程能能力进行行评估和和对比。如何用Excel评估估CPK及其相相关参数数?列数较多多，其中中一部分分已隐藏藏所涉及的的函数:统计函数数:返回参数数的平均均值（算算术平均均值）。AVERAGESTDEV估算样本本的标准准偏差。标准偏偏差反映映相对于于平均值值 (mean)的的离散程程度。所涉及的的定义

19、及及计算公公式:注：USL为为规格上上限；SLS为为规格下下限；如果只有有規格上上限,那么:CPK=CP;如果只有有規格下下限,那么:CPK=CPL.(3)工序能力力指數上上限Cpu：(4)工序能力力指數下下限Cpl：=(1)标准方差差（STDEV）：(2)工序能力力指數CP：(5)工序能力力修正指指數CPK：Max=MAX(C6:C40)=MAX(D6:D40)Min=MIN(C6:C40)=MIN(D6:D40)USL=15.1=15.1LSL=14.914.9Mean=AVERAGE(C6:C40)=AVERAGE(D6:D40)=STDEV(C6:C40)=STDEV(D6:D40)C

20、P=(C43-C44)/(6*C46)=(D43-D44)/(6*D46)CPU=(C43-C45)/(3*C46)=(D43-D45)/(3*D46)CPL=(C45-C44)/(3*C46)=(D45-D44)/(3*D46)CPK=MIN(C48:C49)=MIN(D48:D49)键入表达达式，如如下:利用Excel轻松的的完成CPK评评估！简述建立立Xbar-R控制制图的步步骤1.收收集数据据 选择择子组大大小、频频率和子子组数量量 建立立控制图图及记录录原始数数据 计算算每个子子组的X平均值和和极差R 选择择控制图图的刻度度均值图:坐标上上的刻度度值的最最大值与与最小值值至少为为子组

21、均均值的最最大值与与最小值值差的两两倍.极差图:刻度值值应从最最低值为为0开始始到最大大值之间间差值为为初始阶阶段最大大极差的的2倍. 将平平均值和和极差画画到控制制图上Xbar-R与与EXCEL的的结合运运用2.计计算控控制限 计算算极差的的平均值值和过程程平均值值 计算算控制限限 在控控制图上上画出中中心线及及控制线线过程控制制解释任何超出出控制限限的點;連續7點全在中中心線之之上或之之下;連續7點上升或或下降;明显的的非随机机图形等等所涉及的的定义及及计算公公式:A2D3D421.88 *3.27 31.02 *2.57 40.73 *2.28 50.58 *2.11 60.48 *2.

22、00 70.42 0.08 1.92 80.37 0.14 1.86 90.34 0.18 1.82 100.31 0.22 1.78 A2、D3、D4参数数对照表表：步骤1: 先选选取25组,每每组数据据选择5个数据据.部分列被被隐藏步骤2: 根据据前面的的公式算算出Xbar和和Rbar步骤3: 根据据前面的的公式算算出上下下管控线线Rbar=AVERAGE(C9:AA9)UCLR=C10*VLOOKUP(5,AC11:AF20,4,FALSE)LCLR=C10*VLOOKUP(5,AC11:AF20,3,FALSE)UCLXbar=F10+C10*VLOOKUP(5,AC11:AF20,2

23、,FALSE)LclXbar=F10-C10*VLOOKUP(5,AC11:AF20,2,FALSE)步骤4: 根据据前面的的算出的的上下管管控线分分6个区区间(分分别代表表+/-3S)步骤5: 制作作图表双击图表表中该处处刻度,得出如如下页图图框步骤6: 调整整和修改改图表根据控制制线上下下限定义义最大最最小刻度度值双击Xbar线线,得出出以下图图框选取自动动和样式式,如左左下图.双击图框框内任一一空白处处,得出出下提示示框点击图框框 ,弹弹出右图图点击完成成后得到到:完成Xbar的的制作,Rbar同上上就不介介绍.了了.日常工作作中项目目管理与与EXCEL的的结合运运用日常项目目管理与与EXCEL的的结合运运用在日常中中,我们们经常会会遗忘过过很多东东西,比比如某项项目的跟跟进,样样板的完完成状况况等.以下我们们就日常常的一些些项目管管理方面面的举些些例子:Case1:跟进项目计划完成时间是否关闭通知1041-000477-00的承认书制作2010-1-7否时间已超,请注意关闭该问题2深象M3组合螺