利用Excel制作各類品質(zhì)圖表

1、利用Excel制作各類品質(zhì)圖表培培 訓(xùn)訓(xùn) 內(nèi)內(nèi) 容容品質(zhì)七大手法與EXCEL的結(jié)合運(yùn)用MSA與EXCEL的結(jié)合運(yùn)用SPC與EXCEL的結(jié)合運(yùn)用日常工作中項(xiàng)目管理與EXCEL的結(jié)合運(yùn)用與PPT結(jié)合的Excel文檔:培訓(xùn)目的更深入了解各品質(zhì)手法的推算和機(jī)理更深入了解EXCEL的函數(shù)運(yùn)用和圖表的制作更方便的進(jìn)行日常工作和提高工作質(zhì)量,進(jìn)一步實(shí)現(xiàn)辦公自動(dòng)化品質(zhì)七大手法與EXCEL的結(jié)合運(yùn)用品質(zhì)七大手法與EXCEL的結(jié)合運(yùn)用QC七大手法簡介七大手法簡介:特征要因圖：特征要因圖： 尋找因果關(guān)系尋找因果關(guān)系柏拉圖：柏拉圖： 找出找出“重要的少數(shù)重要的少數(shù)”直方圖：直方圖： 了解數(shù)據(jù)分布與制程能力了解數(shù)據(jù)分布

2、與制程能力層別法：層別法： 按層分類，分別統(tǒng)計(jì)分析按層分類，分別統(tǒng)計(jì)分析查檢表：查檢表： 調(diào)查記錄數(shù)據(jù)用以分析調(diào)查記錄數(shù)據(jù)用以分析散布圖：散布圖： 找出兩者之間的關(guān)系找出兩者之間的關(guān)系管制圖：管制圖： 了解制程變異了解制程變異七大手法七大手法手 法特點(diǎn)與作用主主講講會(huì)在會(huì)在”SPC與與EXCEL的結(jié)合運(yùn)用的結(jié)合運(yùn)用”部分細(xì)說部分細(xì)說1. 柏拉圖與EXCEL的結(jié)合運(yùn)用首先讓我們來看以下一個(gè)關(guān)于某款產(chǎn)品進(jìn)行季度的質(zhì)量分析首先讓我們來看以下一個(gè)關(guān)于某款產(chǎn)品進(jìn)行季度的質(zhì)量分析:從此柏拉圖可以看出影響的最從此柏拉圖可以看出影響的最主要因素主要因素(80%原則原則).怎樣去制作完成的呢?所涉及的Excel

3、函數(shù):= = + - / *1. 邏輯運(yùn)算函數(shù)邏輯運(yùn)算函數(shù):2. 數(shù)學(xué)和三角函數(shù)數(shù)學(xué)和三角函數(shù):SUM 返回某一單元格區(qū)域中所有數(shù)字之和 3. 其他其他:$ 鎖定目標(biāo)選項(xiàng). 如:SUM($C$8:H8):在單元格C8到H8的所有數(shù)字只和而C8項(xiàng)是不隨鼠標(biāo)拉動(dòng)或粘貼而變更的,H8項(xiàng)則會(huì)變化.用以上提及的函數(shù)在Excel表格里輸入,如下:=C7/SUM($C$7:$I$7): 單元格單元格C7除于單元格除于單元格C7至至I7的數(shù)據(jù)之和的數(shù)據(jù)之和 Ctrl+在工作sheet里面進(jìn)行顯示結(jié)果和運(yùn)算式之間的切換.切換后結(jié)果如下:制作表格:點(diǎn)擊或: 插入圖表選擇制圖區(qū)域,如下陰影區(qū)選取”兩軸線-柱圖”完成

4、以上步驟,圖形會(huì)出現(xiàn)然后進(jìn)行裝飾:加入:圖表標(biāo)題;分類(X)軸;數(shù)值(Y)軸等為了圖表左右Y軸的數(shù)據(jù)相關(guān),必須讓左右Y軸數(shù)據(jù)對齊:如:剛才不良的個(gè)數(shù)為:116(左軸) 對應(yīng)的右軸應(yīng)該是100%雙擊圖表上的數(shù)值,會(huì)彈出如下提示框:根據(jù)需要調(diào)節(jié)最大最小值這樣一個(gè)柏拉圖就完美的誕生了!2. 直方圖與EXCEL的結(jié)合運(yùn)用某客戶的電阻值要求為50020歐最近發(fā)現(xiàn)客戶的產(chǎn)品波動(dòng)比較大，所以歸納了近段時(shí)間的連續(xù)七批IQC來料測量數(shù)值，數(shù)值如下：第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

5、491.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

6、 487.4 495.0 490.6 511.3 10485.6 507.5 502.5 497.8 501.0 518.5 527.2 如何對該些數(shù)據(jù)進(jìn)行處理呢？我們就可以運(yùn)用直方圖就行分析如何對該些數(shù)據(jù)進(jìn)行處理呢？我們就可以運(yùn)用直方圖就行分析!從下圖我們可以輕松的分析出數(shù)據(jù)的分布情況。怎樣制作出得體美觀怎樣制作出得體美觀的直方圖呢？的直方圖呢？所涉及的函數(shù)所涉及的函數(shù):1. 統(tǒng)計(jì)函數(shù)統(tǒng)計(jì)函數(shù):COUNTIF計(jì)算區(qū)域中滿足給定條件的單元格的個(gè)數(shù)。 2. 數(shù)學(xué)與三角函數(shù)數(shù)學(xué)與三角函數(shù):LOG按所指定的底數(shù)，返回一個(gè)數(shù)的對數(shù)。 返回一組值中的最大值。 返回一組值中的最小值。 MAXMIN將數(shù)字按

7、指定的小數(shù)位數(shù)進(jìn)行取整，利用句號和逗號，以小數(shù)格式對該數(shù)進(jìn)行格式設(shè)置，并以文本形式返回結(jié)果。3.文本函數(shù)文本函數(shù):FIXEDCONCATENATE將幾個(gè)文本字符串合并為一個(gè)文本字符串。 步驟1：計(jì)算數(shù)據(jù)中的最大最小值步驟2：計(jì)算全距（最大值-最小值）步驟3：計(jì)算組數(shù)K數(shù)據(jù)數(shù)組數(shù)50100610100250712250以上1020溫馨提示：右圖為組數(shù)的參考值溫馨提示：右圖為組數(shù)的參考值步驟4：計(jì)算組距（全距/組數(shù)K ）步驟5：計(jì)算上下組列用用CONCATENATE函數(shù)合并上下界數(shù)值文本，函數(shù)合并上下界數(shù)值文本，計(jì)算如下：計(jì)算如下：步驟6：計(jì)算組列間的個(gè)數(shù)鍵入鍵入COUNTIF和和SUN函數(shù)運(yùn)算滿

8、函數(shù)運(yùn)算滿足條件的個(gè)數(shù)足條件的個(gè)數(shù)步驟7：制作圖表把下面的單元格選取并點(diǎn)擊直方圖即可制作把下面的單元格選取并點(diǎn)擊直方圖即可制作開始直方圖圖表開始直方圖圖表運(yùn)算結(jié)果如下運(yùn)算結(jié)果如下需要留意的是：點(diǎn)擊列可得左圖圖表樣式，點(diǎn)擊列可得左圖圖表樣式，若點(diǎn)擊行為右圖圖表樣式，若點(diǎn)擊行為右圖圖表樣式，可根據(jù)自己的需要選擇可根據(jù)自己的需要選擇確定后，就完成直觀美麗的直方圖了！確定后，就完成直觀美麗的直方圖了！然后根據(jù)圖表找出產(chǎn)品存在的差異。然后根據(jù)圖表找出產(chǎn)品存在的差異。3. 散布圖與EXCEL的結(jié)合運(yùn)用某鈑金件長度尺寸,由于公司和供應(yīng)商之間一直在數(shù)據(jù)上面存在差異,選取30個(gè)樣板,分別注上編號測得數(shù)據(jù)如下:所

9、涉及的函數(shù)所涉及的函數(shù):返回單元格區(qū)域 array1 和 array2 之間的相關(guān)系數(shù)。使用相關(guān)系數(shù)可以確定兩種屬性之間的關(guān)系。例如，可以檢測某地的平均溫度和空調(diào)使用情況之間的關(guān)系。語法語法CORREL(array1,array2)Array1 第一組數(shù)值單元格區(qū)域。Array2 第二組數(shù)值單元格區(qū)域。1. 統(tǒng)計(jì)函數(shù)統(tǒng)計(jì)函數(shù): CORREL 表示多少次方; 比如:56就是說5的6次方2.數(shù)學(xué)運(yùn)算符數(shù)學(xué)運(yùn)算符: =FIXED(CORREL(C7:C36,D7:D36),2)在單元格鍵入:雙擊:然后彈出右圖對話框:=FIXED(C372,2)可容易得到相關(guān)系數(shù)的結(jié)果可容易得到相關(guān)系數(shù)的結(jié)果右鍵圖表

10、上的數(shù)據(jù)點(diǎn)右鍵圖表上的數(shù)據(jù)點(diǎn)勾選顯示R平方值完成上面操作后完成上面操作后,一個(gè)漂亮的散布圖就這樣誕生了一個(gè)漂亮的散布圖就這樣誕生了相關(guān)系數(shù)相關(guān)系數(shù)R可以從圖表上或可以從圖表上或Excel函數(shù)運(yùn)算出來函數(shù)運(yùn)算出來,判別原則判別原則: R90%,R280%為良好為良好,反之為較差反之為較差.MSA與EXCEL的結(jié)合運(yùn)用評定測量系統(tǒng)的質(zhì)量確定能對每個(gè)零件重復(fù)讀數(shù)的測量系統(tǒng)評估新的測量量具比較不同的測量方式，找到最佳方案找到并解決測量系統(tǒng)的誤差MSA意義意義再現(xiàn)性再現(xiàn)性穩(wěn)定性穩(wěn)定性線性線性重復(fù)性重復(fù)性偏倚偏倚小樣法小樣法交叉法交叉法計(jì)量型計(jì)量型計(jì)數(shù)型計(jì)數(shù)型MSA類別類別:GR&R1. GR&a

11、mp;R與EXCEL的結(jié)合運(yùn)用GR&R是評定測量系統(tǒng)的重復(fù)性和再現(xiàn)性.所涉及的函數(shù)所涉及的函數(shù):1. 統(tǒng)計(jì)函數(shù):AVERAGE返回參數(shù)的平均值（算術(shù)平均值）。SQRT返回正平方根。2. 邏輯函數(shù):IF執(zhí)行真假值判斷，根據(jù)邏輯計(jì)算的真假值，返回不同結(jié)果?？梢允褂煤瘮?shù) IF 對數(shù)值和公式進(jìn)行條件檢測。VLOOKUP在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。所涉及的公式所涉及的公式:1*KREV )/()*(222nrEVKXAVDIFF)(22AVEVGRR3*KRpPV %AV=100AV/TV%GRR=100GRR/TV%PV=100PV/TVn

12、dc =1.41(PV/GRR)重復(fù)性:再現(xiàn)性:GR&R:樣板間變差:%重復(fù)性:%再現(xiàn)性:%GR&R:樣板間變差:%EV=100EV/TV分級數(shù):)&(22PVRRTV樣板總變差:步驟1: 先計(jì)劃表格,如右圖:應(yīng)該遵循GR&R的取樣要求和測量要求:取樣數(shù)量:10pcs(編號110)測量人員:3人(定義為A,B,C)測量次數(shù):3次/人均值分為3種:個(gè)人測量單個(gè)樣板三次的均值X個(gè)人測量10個(gè)樣板的均值3. 所有測量數(shù)據(jù)的均值步驟2: 計(jì)算均值和極差:)10.321(XXXXaverageXa)8:6(1BBaverageX)(cXbXaXaverageX對應(yīng)鍵入公式

13、:1X例Xa例極差分為4種:個(gè)人每個(gè)樣板測量間的Ra;2. 個(gè)人10個(gè)樣板間R的平均值3. 所有人的 平均值4. 零件均值極差RpRRRRa=MAX(B11:B13)-MIN(B11:B13)對應(yīng)鍵入公式:)(cRbRaRaverageR)10.321(RRRRaverageR運(yùn)算Ra后得)1()1(nXXMinnXXMaxRp步驟3: 計(jì)算EV,AV,GR&R,PV,NDC:1*KREV 試驗(yàn)次數(shù)K120.886 30.5908套入步驟套入步驟2的數(shù)值的數(shù)值,如如套入運(yùn)算的單元格如D7,D7*D12為EV值運(yùn)算式運(yùn)算后結(jié)果如下運(yùn)算后結(jié)果如下:)/()*(222nrEVKXAVDIFF

14、依以上公式套入依以上公式套入AV/n/r等數(shù)值等數(shù)值,如如23K20.70710.5231)(22AVEVGRR3* KRpPVK320.707130.523140.447 50.403 60.374270.353480.337590.3249100.3146例如:樣板數(shù)目為10時(shí),取值為0.3146經(jīng)過=I7*G29運(yùn)算可得出PV值ndc =1.41(PV/GRR)分級數(shù):步驟4: 由結(jié)果編寫條件式判別原則: 當(dāng)GR&R10%時(shí),可以接受; 當(dāng)10GR&R30%時(shí),不可以接受; 當(dāng)ndc=5時(shí),可以接受; 當(dāng)ndc5時(shí),不可以接受=IF(J20/100)30%,不可以接受,條

15、件下接受)=IF(J285,不可接受,可以接受)%GR&R Excel版本制作完成版本制作完成!2. 交叉法與EXCEL的結(jié)合運(yùn)用隨機(jī)選取50個(gè)產(chǎn)品（最好包含10個(gè)左右不良樣品）并編號。確定測量系統(tǒng)所有參數(shù)與正常檢驗(yàn)時(shí)一樣。三個(gè)測量員分別對每個(gè)產(chǎn)品測量三次并對號記錄結(jié)果，測量時(shí)打亂產(chǎn)品順序）。將測量結(jié)果（合格記錄1/OK，不合格記錄0/NG）輸入交叉法計(jì)算表格。首先回顧以下交叉法的評估方法首先回顧以下交叉法的評估方法:所涉及的函數(shù)所涉及的函數(shù):1. 邏輯函數(shù):執(zhí)行真假值判斷，根據(jù)邏輯計(jì)算的真假值，返回不同結(jié)果。可以使用函數(shù) IF 對數(shù)值和公式進(jìn)行條件檢測。AND在其參數(shù)組中，任何一個(gè)參

16、數(shù)邏輯值為 TRUE，即返回 TRUE；任何一個(gè)參數(shù)的邏輯值為 FALSE，即返回 FALSE。 OR步驟1：計(jì)算人與人交叉（A*B,B*C,A*C）交叉法運(yùn)算法則：A*CB*DABDC=G5*E9/G9A=B*D/C注：期待數(shù)量是運(yùn)算出來的，而數(shù)量則是實(shí)測出來的，數(shù)量詳細(xì)計(jì)算如下頁。B總計(jì)01A0數(shù)量72375期望數(shù)量38.00 37.00 751數(shù)量47175期望數(shù)量38.00 37.00 75總計(jì)數(shù)量7674150期望數(shù)量7674150由上表可以看出需要根據(jù)測量的數(shù)據(jù)計(jì)算出：A=0&B=0A=0&B=1A=1&B=0A=1&B=1同理得同理得B&C

17、A&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 中鍵入的表達(dá)式（參考）：=IF(AND(測量數(shù)據(jù)!B2=0,人與人交叉!B5=1),1,0)A=0,B=0A-B(same)=IF(測量數(shù)據(jù)!B2=測量數(shù)據(jù)!E2,1,0)得出的運(yùn)算結(jié)果如下：根據(jù)運(yùn)算的結(jié)果用根據(jù)運(yùn)算的結(jié)果用SUM函數(shù)算出對應(yīng)的個(gè)數(shù)，函數(shù)算出對應(yīng)的個(gè)數(shù)，引用到指定的單元格中引用到指定的單元格中人與標(biāo)準(zhǔn)交叉 （A*S,B*S,C*S）步驟2：人與標(biāo)準(zhǔn)交叉A&SB&

18、S(同A)C&S (同A)A=0&S=0A=1&S=0A=0&S=1A=1&S=1步驟3：計(jì)算交互的Kappa結(jié)果KappaABCA0.91 0.92 B0.91 0.93 C0.92 0.93 設(shè)計(jì)的運(yùn)算公式：)1 (_eeoBAPPPKappa其中：Po為相同判別（如A=0&B=0和A=1&B=1 ）的對應(yīng)數(shù)量占的比率； Pe為相同判別（如A=0&B=0和A=1&B=1 ）的對應(yīng)期望數(shù)量占的比率。表達(dá)式如下：總數(shù)XXXPbAbAo)(1&10&0期望總數(shù)期望期望XXXPbAbAe)(1&10&a

19、mp;0總數(shù)期望總數(shù)注：XX鍵入Excel的表達(dá)式測量員A&B&C和S標(biāo)準(zhǔn)值的Kappa值計(jì)算同上。步驟4：交互的Kappa判別的結(jié)論一致性判定標(biāo)準(zhǔn)：一致性判定標(biāo)準(zhǔn)：通用的比例法則是Kappa0.75則表示有很好的一致性，Kappa0.40則表示一致性不好。有效性判定標(biāo)準(zhǔn)有效性判定標(biāo)準(zhǔn)有效性漏報(bào)率錯(cuò)誤報(bào)警率可接受90%2%5%邊界接受但要改邊界接受但要改善善80%80%5%5%10%10%不可接受80%5%10%Kappa交叉法完成交叉法完成SPC與EXCEL的結(jié)合運(yùn)用CPK與EXCEL的結(jié)合運(yùn)用SPC是過程統(tǒng)計(jì)控制的工具，而過程控制與過程能力CP&CPK緊密結(jié)合。例：

20、兩供應(yīng)商（A&B）的產(chǎn)品差異較大，現(xiàn)對供應(yīng)商的制程能力進(jìn)行評估和對比。如何用如何用Excel評估評估CPK及其相關(guān)參數(shù)及其相關(guān)參數(shù)?列數(shù)較多，其中一列數(shù)較多，其中一部分已隱藏部分已隱藏所涉及的函數(shù)所涉及的函數(shù):統(tǒng)計(jì)函數(shù)統(tǒng)計(jì)函數(shù):返回參數(shù)的平均值（算術(shù)平均值）。 AVERAGESTDEV估算樣本的標(biāo)準(zhǔn)偏差。標(biāo)準(zhǔn)偏差反映相對于平均值 (mean) 的離散程度。 所涉及的定義及計(jì)算公式所涉及的定義及計(jì)算公式:注： USL為規(guī)格上限； SLS為規(guī)格下限； 如果只有規(guī)格上限,那么:CPK=CP; 如果只有規(guī)格下限,那么:CPK=CPL.3UTCpu3LTCpl(3) 工序能力指數(shù)上限Cpu： (

21、4) 工序能力指數(shù)下限Cpl ：66LUTTTCP1n).()()(2n2221=(1) 標(biāo)準(zhǔn)方差（STDEV）：(2)工序能力指數(shù) CP：(5)工序能力修正指數(shù)CPK： CpCaCplCpuMinCpk*)1 (),(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)=AVE

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

23、C46)=(D45-D44)/(3=(D45-D44)/(3* *D46)D46)CPK=CPK=MIN(C48:C49)=MIN(C48:C49)=MIN(D48:D49)=MIN(D48:D49)鍵入表達(dá)式，如下鍵入表達(dá)式，如下:利用利用Excel輕松的完成輕松的完成CPK評估！評估！簡述建立簡述建立Xbar-R控制圖的步驟控制圖的步驟1. 收集數(shù)據(jù) 選擇子組大小、頻率和子組數(shù)量 建立控制圖及記錄原始數(shù)據(jù) 計(jì)算每個(gè)子組的X平均值和極差R 選擇控制圖的刻度 均值圖:坐標(biāo)上的刻度值的最大值與最小值至少為子組均值的最大值與最小值差的兩倍. 極差圖:刻度值應(yīng)從最低值為0開始到最大值之間差值為初始階

24、段最大極差的2倍. 將平均值和極差畫到控制圖上Xbar-R與EXCEL的結(jié)合運(yùn)用2. 計(jì)算控制限 計(jì)算極差的平均值和過程平均值 計(jì)算控制限 在控制圖上畫出中心線及控制線 過程控制解釋 任何超出控制限的點(diǎn);連續(xù)7點(diǎn)全在中心線之上或之下;連續(xù)7點(diǎn)上升或下降;明顯的非隨機(jī)圖形等等 所涉及的定義及計(jì)算公式所涉及的定義及計(jì)算公式:RAXLCLRAXUCLRDLCLRDUCLkRRRRRkXXXXXXXRRkk*/ ).(/ ).(2234321321A2D3D421.88 *3.27 31.02 *2.57 40.73 *2.28 50.58 *2.11 60.48 *2.00 70.42 0.08 1

25、.92 80.37 0.14 1.86 90.34 0.18 1.82 100.31 0.22 1.78 A2、D3、D4參數(shù)對照表：步驟1: 先選取25組,每組數(shù)據(jù)選擇5個(gè)數(shù)據(jù).部分列被隱藏步驟2: 根據(jù)前面的公式算出Xbar和Rbar步驟3: 根據(jù)前面的公式算出上下管控線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,FALSE)LclXbar=F10-C10*VLOOKUP(5,AC11:AF20,2,FALSE)步驟4: 根據(jù)前面的算出的上下管控線分6個(gè)區(qū)間(分別代表+/-3S)步驟5: 制作圖表雙擊圖表中該處刻度,得出如下頁圖框步驟6: 調(diào)整和修改圖表根據(jù)控制線上下限定義最大最小刻度值雙擊Xbar線,得出以下圖框選取自動(dòng)和樣式,如左下圖.雙擊圖框內(nèi)任一空白處,得出下提示框點(diǎn)擊圖框 ,彈出右圖點(diǎn)擊完成后得到:完成完成Xbar的制作的制作,Rbar同上就不介紹同上就不介紹.了了.日常工作中項(xiàng)