利用excel表格制作各种品质图表教学资料.ppt

《利用excel表格制作各种品质图表教学资料.ppt》由会员分享，可在线阅读，更多相关《利用excel表格制作各种品质图表教学资料.ppt（63页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、培培 训训 内内 容容品质七大(qd)手法与EXCEL的结合运用MSA与EXCEL的结合(jih)运用SPC与EXCEL的结合(jih)运用日常工作中项目管理与EXCEL的结合运用与PPT结合的Excel文档:第一页，共63页。培训(pixn)目的更深入了解各品质手法的推算(tusun)和机理更深入了解(lioji)EXCEL的函数运用和图表的制作更方便的进行日常工作和提高工作质量,进一步实现办公自动化第二页，共63页。品质(pnzh)七大手法与EXCEL的结合运用第三页，共63页。品质七大手法(shuf)与EXCEL的结合运用QC七大七大(q d)手法简介手法简介:特征特征(tzhng)要因

2、图：要因图：寻找因果关系寻找因果关系柏拉图：柏拉图：找出找出“重要的少数重要的少数”直方图：直方图：了解数据分布与制程能力了解数据分布与制程能力层别法：层别法：按层分类，分别统计分析按层分类，分别统计分析查检表：查检表：调查记录数据用以分析调查记录数据用以分析散布图：散布图：找出两者之间的关系找出两者之间的关系管制图：管制图：了解制程变异了解制程变异七七大大手手法法手法特点与作用主主讲讲会在会在”SPC与与EXCEL的结合运用的结合运用”部分细说部分细说第四页，共63页。1.柏拉图与EXCEL的结合(jih)运用首先让我们来看以下一个关于首先让我们来看以下一个关于(guny)某款产品进行季度的

3、质量分析某款产品进行季度的质量分析:从此从此(cngc)柏拉图可柏拉图可以看出影响的最主要因以看出影响的最主要因素素(80%原则原则).第五页，共63页。怎样去制作(zhzu)完成的呢?所涉及(shj)的Excel函数:=+-/*1.逻辑运算函数逻辑运算函数(hnsh):2.数学和三角函数数学和三角函数:SUM返回某一单元格区域中所有数字之和3.其他其他:$锁定目标选项.如:SUM($C$8:H8):在单元格C8到H8的所有数字只和而C8项是不随鼠标拉动或粘贴而变更的,H8项则会变化.第六页，共63页。用以上提及的函数在Excel表格里输入(shr),如下:=C7/SUM($C$7:$I$7)

4、:单元格单元格C7除于除于单元格单元格C7至至I7的数据的数据(shj)之和之和 Ctrl+在工作sheet里面进行显示结果和运算(ynsun)式之间的切换.切换后结果如下:第七页，共63页。制作(zhzu)表格:点击(dinj)或:插入(chr)图表选择制图区域,如下阴影区选取”两轴线-柱图”第八页，共63页。完成(wnchng)以上步骤,图形会出现然后进行(jnxng)装饰:加入(jir):图表标题;分类(X)轴;数值(Y)轴等第九页，共63页。为了图表左右Y轴的数据相关,必须让左右Y轴数据对齐(duq):如:刚才不良的个数为:116(左轴)对应的右轴应该是100%双击图表(tbio)上的

5、数值,会弹出如下提示框:根据(gnj)需要调节最大最小值这样一个柏拉图就完美的诞生了!第十页，共63页。2.直方图与EXCEL的结合(jih)运用某客户(kh)的电阻值要求为50020欧最近发现客户的产品(chnpn)波动比较大，所以归纳了近段时间的连续七批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.4 469.4 500.8 500.5 3504.5 498.3 482.9 498.1 519.0 508.8 518.9 4494.0

6、 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 487.4 495.0 490.6 511.3 10485.6 507.5 502.5 497.8 501.0 518.5 527.2 如何对该

7、些数据进行处理呢？我们就可以运用直方图就行分析如何对该些数据进行处理呢？我们就可以运用直方图就行分析!第十一页，共63页。从下图我们可以轻松的分析出数据(shj)的分布情况。怎样怎样(znyng)制作出得体美观制作出得体美观的直方图呢？的直方图呢？第十二页，共63页。所涉及所涉及(shj)的函数的函数:1.统计统计(tngj)函数函数:COUNTIF计算区域中满足给定(idn)条件的单元格的个数。2.数学与三角函数数学与三角函数:LOG按所指定的底数，返回一个数的对数。返回一组值中的最大值。返回一组值中的最小值。MAXMIN将数字按指定的小数位数进行取整，利用句号和逗号，以小数格式对该数进行格

8、式设置，并以文本形式返回结果。3.文本函数文本函数:FIXEDCONCATENATE将几个文本字符串合并为一个文本字符串。第十三页，共63页。步骤(bzhu)1：计算数据中的最大最小值步骤(bzhu)2：计算全距（最大值-最小值）步骤(bzhu)3：计算组数K數據數組數50100610100250712250以上1020温馨提示：右图为组数的参考值温馨提示：右图为组数的参考值步骤4：计算组距（全距/组数K）第十四页，共63页。步骤(bzhu)5：计算上下组列用用CONCATENATE函数函数(hnsh)合并上下合并上下界数值文本，计算如下：界数值文本，计算如下：第十五页，共63页。步骤(bzh

9、u)6：计算组列间的个数键入键入COUNTIF和和SUN函数函数(hnsh)运算满运算满足条件的个数足条件的个数步骤7：制作(zhzu)图表把下面的单元格选取并点击直方图即可把下面的单元格选取并点击直方图即可制作开始直方图图表制作开始直方图图表运算结果如下运算结果如下第十六页，共63页。需要(xyo)留意的是：点击列可得左图图表样式，点击列可得左图图表样式，若点击行为右图图表样式，若点击行为右图图表样式，可根据自己可根据自己(zj)的需要选的需要选择择确定后，就完成直观美丽的直方图了！确定后，就完成直观美丽的直方图了！然后然后(rnhu)根据图表找出产品存在的根据图表找出产品存在的差异。差异。

10、第十七页，共63页。3.散布图与EXCEL的结合(jih)运用某钣金件长度尺寸(chcun),由于公司和供应商之间一直在数据上面存在差异,选取30个样板,分别注上编号测得数据如下:所涉及所涉及(shj)的函数的函数:返回单元格区域array1和array2之间的相关系数。使用相关系数可以确定两种属性之间的关系。例如，可以检测某地的平均温度和空调使用情况之间的关系。语法语法CORREL(array1,array2)Array1第一组数值单元格区域。Array2第二组数值单元格区域。1.统计函数统计函数:CORREL 表示多少次方;比如:56就是说5的6次方2.数学运算符数学运算符:第十八页，共6

11、3页。=FIXED(CORREL(C7:C36,D7:D36),2)在单元格键入(jinr):双击:然后(rnhu)弹出右图对话框:=FIXED(C372,2)可容易得到可容易得到(d do)相关系数相关系数的结果的结果第十九页，共63页。右键图表右键图表(tbio)上的数据点上的数据点勾选显示(xinsh)R平方值完成上面操作后完成上面操作后,一个漂亮一个漂亮(pio ling)的散布图就这样诞生了的散布图就这样诞生了相关系数相关系数R可以从图表上或可以从图表上或Excel函函数运算出来数运算出来,判别原则判别原则:R90%,R280%为良好为良好,反之为较反之为较差差.第二十页，共63页。

12、MSA与EXCEL的结合(jih)运用第二十一页，共63页。评定测量系统(xtng)的质量确定(qudng)能对每个零件重复读数的测量系统评估(pn)新的测量量具比较不同的测量方式，找到最佳方案找到并解决测量系统的误差MSA意义意义第二十二页，共63页。再现再现(zixin)性性稳定性稳定性线性线性重复性重复性偏倚偏倚(pin y)小样小样(xioyng)法法交叉法交叉法计量型计量型计数型计数型MSA类别类别:GR&R第二十三页，共63页。1.GR&R与EXCEL的结合(jih)运用GR&R是评定(pngdng)测量系统的重复性和再现性.所涉及所涉及(shj)的函数的函数:1.统计函数:AVE

13、RAGE返回参数的平均值（算术平均值）。SQRT返回正平方根。2.逻辑函数:IF执行真假值判断，根据逻辑计算的真假值，返回不同结果。可以使用函数IF对数值和公式进行条件检测。VLOOKUP在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。第二十四页，共63页。所涉及所涉及(shj)的公式的公式:%AV=100AV/TV%GRR=100GRR/TV%PV=100PV/TVndc=1.41(PV/GRR)重复性:再现(zixin)性:GR&R:样板间变差:%重复性:%再现(zixin)性:%GR&R:样板间变差:%EV=100EV/TV分级数:样板总变差:第二十五页

14、，共63页。步骤(bzhu)1:先计划表格,如右图:应该遵循GR&R的取样要求和测量要求:取样数量:10pcs(编号(binho)110)测量人员:3人(定义为A,B,C)测量次数:3次/人第二十六页，共63页。均值分为3种:个人测量单个样板(yngbn)三次的均值X个人测量10个样板(yngbn)的均值3.所有测量数据的均值步骤(bzhu)2:计算均值和极差:对应键入(jinr)公式:第二十七页，共63页。极差分为4种:个人每个样板测量(cling)间的Ra;2.个人10个样板间R的平均值3.所有人的平均值4.零件均值极差RpRa=MAX(B11:B13)-MIN(B11:B13)对应(du

15、yng)键入公式:运算(ynsun)Ra后得第二十八页，共63页。步骤(bzhu)3:计算EV,AV,GR&R,PV,NDC:试验次数K120.886 30.5908套入步骤套入步骤(bzhu)2的数值的数值,如如套入运算(ynsun)的单元格如D7,D7*D12为EV值运算(ynsun)式运算后结果如下运算后结果如下:第二十九页，共63页。依以上依以上(yshng)公式套入公式套入AV/n/r等数值等数值,如如23K20.70710.5231第三十页，共63页。K320.707130.523140.447 50.403 60.374270.353480.337590.3249100.3146

16、例如:样板数目为10时,取值为0.3146经过(jnggu)=I7*G29运算可得出PV值ndc=1.41(PV/GRR)分级数(jsh):第三十一页，共63页。步骤4:由结果(jigu)编写条件式判别原则:当GR&R10%时,可以(ky)接受;当10GR&R30%时,不可以(ky)接受;当ndc=5时,可以(ky)接受;当ndc5时,不可以(ky)接受=IF(J20/100)30%,不可以接受(jishu),条件下接受(jishu)=IF(J285,不可接受,可以接受)%GR&R Excel版本制作完成版本制作完成!第三十二页，共63页。2.交叉(jioch)法与EXCEL的结合运用随机选取

17、50个产品（最好包含10个左右不良样品）并编号。确定测量系统所有参数与正常检验时一样。三个测量员分别对每个产品测量三次并对号记录结果，测量时打乱产品顺序）。将测量结果（合格(hg)记录1/OK，不合格(hg)记录0/NG）输入交叉法计算表格。首先回顾以下首先回顾以下(yxi)交叉法的评估方法交叉法的评估方法:第三十三页，共63页。所涉及所涉及(shj)的函数的函数:1.逻辑(luj)函数:执行真假值判断，根据(gnj)逻辑计算的真假值，返回不同结果。可以使用函数IF对数值和公式进行条件检测。AND在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE；任何一个参数的逻辑值为FALSE，即返

18、回FALSE。OR第三十四页，共63页。步骤(bzhu)1：计算(jsun)人与人交叉（A*B,B*C,A*C）交叉(jioch)法运算法则：A*CB*DABDC=G5*E9/G9A=B*D/C注：期待数量是运算出来的，而数量则是实测出来的，数量详细计算如下页。第三十五页，共63页。B总计01A0数量72375期望数量38.00 37.00 751数量47175期望数量38.00 37.00 75总计数量7674150期望数量7674150由上表可以看出(knch)需要根据测量的数据计算出：A=0&B=0A=0&B=1A=1&B=0A=1&B=1同理得同理得B&CA&CB=0&C=0B=0&C

19、=1B=1&C=0B=1&C=1A=0&C=0A=0&C=1A=1&C=0A=1&C=1+第三十六页，共63页。Excel中键入(jinr)的表达式（参考）：=IF(AND(测量(cling)数据!B2=0,人与人交叉!B5=1),1,0)A=0,B=0A-B(same)=IF(测量(cling)数据!B2=测量(cling)数据!E2,1,0)得出的运算结果如下：根据运算的结果用根据运算的结果用SUM函数算出对应的个数，函数算出对应的个数，引用到指定的单元格中引用到指定的单元格中第三十七页，共63页。人与标准(biozhn)交叉（A*S,B*S,C*S）步骤(bzhu)2：人与标准交叉A&S

20、B&S(同A)C&S(同A)A=0&S=0A=1&S=0A=0&S=1A=1&S=1第三十八页，共63页。步骤3：计算交互(jioh)的Kappa结果KappaABCA0.91 0.92 B0.91 0.93 C0.92 0.93 设计的运算(ynsun)公式：其中(qzhng)：Po为相同判别（如A=0&B=0和A=1&B=1）的对应数量占的比率；Pe为相同判别（如A=0&B=0和A=1&B=1）的对应期望数量占的比率。第三十九页，共63页。表达式如下(rxi)：键入(jinr)Excel的表达式第四十页，共63页。测量员A&B&C和S标准值的Kappa值计算(jsun)同上。步骤4：交互的

21、Kappa判别(pnbi)的结论一致性判定一致性判定标标准：准：通用的比例法则是Kappa0.75则表示有很好的一致性，Kappa0.40则表示一致性不好。有效性判定有效性判定标标准准有效性漏报率错误报警率可接受90%2%5%边边界接受但要改善界接受但要改善80%80%5%5%10%10%不可接受80%5%10%Kappa交叉交叉(jioch)法完成法完成第四十一页，共63页。SPC与EXCEL的结合(jih)运用第四十二页，共63页。CPK与EXCEL的结合(jih)运用SPC是过程统计控制(kngzh)的工具，而过程控制(kngzh)与过程能力CP&CPK紧密结合。例：两供应商（A&B）的

22、产品差异较大，现对供应商的制程能力(nngl)进行评估和对比。如何用如何用Excel评估评估CPK及其相关参数及其相关参数?列数较多，其中一列数较多，其中一部分已隐藏部分已隐藏第四十三页，共63页。所涉及所涉及(shj)的函数的函数:统计统计(tngj)函数函数:返回(fnhu)参数的平均值（算术平均值）。AVERAGESTDEV估算样本的标准偏差。标准偏差反映相对于平均值(mean)的离散程度。第四十四页，共63页。所涉及所涉及(shj)的定义及计算公式的定义及计算公式:注：USL为规格(gug)上限；SLS为规格(gug)下限；如果只有規格上限,那么:CPK=CP;如果只有規格下限,那么:

23、CPK=CPL.(3)工序能力(nngl)指數上限Cpu：(4)工序能力指數下限Cpl：=(1)标准方差（STDEV）：(2)工序能力指數CP：(5)工序能力修正指數CPK：第四十五页，共63页。Max=Max=MAX(C6:C40)=MAX(C6:C40)=MAX(D6:D40)=MAX(D6:D40)Min=Min=MIN(C6:C40)=MIN(C6:C40)=MIN(D6:D40)=MIN(D6:D40)USL=USL=15.1=15.1=15.1=15.1LSL=LSL=14.914.914.914.9Mean=Mean=AVERAGE(C6:C40)=AVERAGE(C6:C40)

24、=AVERAGE(D6:D40)=AVERAGE(D6:D40)=STDEV(C6:C40)=STDEV(C6:C40)=STDEV(D6:D40)=STDEV(D6:D40)CP=CP=(C43-C44)/(6*C46)=(C43-C44)/(6*C46)=(D43-D44)/(6*D46)=(D43-D44)/(6*D46)CPU=CPU=(C43-C45)/(3*C46)=(C43-C45)/(3*C46)=(D43-D45)/(3*D46)=(D43-D45)/(3*D46)CPL=CPL=(C45-C44)/(3*C46)=(C45-C44)/(3*C46)=(D45-D44)/(3

25、*D46)=(D45-D44)/(3*D46)CPK=CPK=MIN(C48:C49)=MIN(C48:C49)=MIN(D48:D49)=MIN(D48:D49)键入键入(jin r)表达式，如下表达式，如下:利用利用(lyng)Excel轻松的完成轻松的完成CPK评估！评估！第四十六页，共63页。简述建立简述建立(jinl)Xbar-R控制图控制图的步骤的步骤1.收集数据选择子组大小、频率和子组数量(shling)建立控制图及记录原始数据计算每个子组的X平均值和极差R选择控制图的刻度均值图:坐标上的刻度值的最大值与最小值至少为子组均值的最大值与最小值差的两倍.极差图:刻度值应从最低值为0开

26、始到最大值之间差值为初始阶段最大极差的2倍.将平均值和极差画到控制图上Xbar-R与EXCEL的结合(jih)运用第四十七页，共63页。2.计算控制(kngzh)限计算极差的平均值和过程平均值计算控制(kngzh)限在控制(kngzh)图上画出中心线及控制(kngzh)线3.过程控制解释4.任何超出控制限的點;5.連續7點全在中心(zhngxn)線之上或之下;6.連續7點上升或下降;7.明显的非随机图形等等8.第四十八页，共63页。所涉及所涉及(shj)的定义及计算公式的定义及计算公式:A2D3D421.88*3.27 31.02*2.57 40.73*2.28 50.58*2.11 60.4

27、8*2.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参数(cnsh)对照表：第四十九页，共63页。步骤1:先选取(xunq)25组,每组数据选择5个数据.部分(bfen)列被隐藏步骤2:根据前面(qinmian)的公式算出Xbar和Rbar第五十页，共63页。步骤(bzhu)3:根据前面的公式算出上下管控线Rbar=AVERAGE(C9:AA9)UCLR=C10*VLOOKUP(5,AC11:AF20,4,FALSE)LCLR=C10*VLOOKUP(5,AC11:AF20,3,FALS

28、E)UCLXbar=F10+C10*VLOOKUP(5,AC11:AF20,2,FALSE)LclXbar=F10-C10*VLOOKUP(5,AC11:AF20,2,FALSE)步骤4:根据前面(qinmian)的算出的上下管控线分6个区间(分别代表+/-3S)第五十一页，共63页。步骤5:制作(zhzu)图表双击图表中该处刻度,得出(dch)如下页图框第五十二页，共63页。步骤6:调整(tiozhng)和修改图表根据控制线上下限定义(dngy)最大最小刻度值双击Xbar线,得出(dch)以下图框选取自动和样式,如左下图.双击图框内任一空白处,得出下提示框第五十三页，共63页。点击(dinj

29、)图框,弹出右图点击(dinj)完成后得到:完成完成Xbar的制作的制作,Rbar同上同上(tngshng)就不介绍就不介绍.了了.第五十四页，共63页。日常(rchng)工作中项目管理与EXCEL的结合运用第五十五页，共63页。日常(rchng)项目管理与EXCEL的结合运用在日常中,我们经常会遗忘过很多东西,比如某项目的跟进,样板的完成状况等.以下我们就日常的一些项目管理方面(fngmin)的举些例子:Case1:跟进项目计划完成时间是否关闭通知1041-000477-00的承认书制作2010-1-7否否时间时间已超已超,请请注意关注意关闭该闭该问题问题2深象M3组合螺钉M04-05114

30、0-弹垫过大拧紧后弹垫会张开2009-12-25否否距离完成距离完成还还有有时间时间，请请注注意及意及时时完成完成3品质常用工具与excel的运用技巧2010-1-5否否该项该项目目紧紧急，急，请请速完成速完成4表面处理的培训2009-12-26是是-5到飞黄出差2009-12-27是是-6-7-8-第五十六页，共63页。所用(suyn)到的函数:返回当前日期的序列号。序列号是MicrosoftExcel日期和时间计算(jsun)使用的日期-时间代码。如果在输入函数前，单元格的格式为“常规”，则结果将设为日期格式。TODAY所用(suyn)到的快捷键:ALT+O+D设计单元格条件格式.CTRL

31、+1切换单元格格式.第五十七页，共63页。步骤1:用today函数设置(shzh)今天的时间,如下:步骤2:用IF函数(hnsh)设置条件判别式,如下:=IF(Y8=是,-,IF(W8-$W$5)0时,时间已超,请注意关闭该问题2当时间=0时,该项目紧急，请速完成3当时间0时,距离完成还有时间，请注意及时完成条件为:实现命令的表达式为:第五十八页，共63页。步骤3:用ALT+O+D设置条件(tiojin)格式,如下:时间已超,请注意(zhy)关闭该问题该项目(xingm)紧急，请速完成距离完成还有时间，请注意及时完成单元格分别指代单元格分别指代:完成完成!第五十九页，共63页。Case2:在日

32、常的加班统计中,有时候想知道哪几位同事加班时间已经领先(lnxin),哪几位较为靠后,我们从下表可以轻松得知.第六十页，共63页。所用(suyn)到的函数:返回(fnhu)数据集中第k个最大值。使用此函数可以根据相对标准来选择数值。例如，可以使用函数LARGE得到第一名、第二名或第三名的得分。LARGE步骤1:用SUM函数统计(tngj)加班总时间,如下:第六十一页，共63页。步骤2:用LARGE函数(hnsh)排列其大小,从大至小,如下:步骤(bzhu)3:用ALT+O+D设置条件格式:完成(wnchng)!第六十二页，共63页。常用金属材料及表面处理常用金属材料及表面处理(chl)基本知基本知识培训识培训THANK YOU第六十三页，共63页。