利用excel表格制作各種品質圖表qc7msaspc1

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

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

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

4、飾:加入:圖表標題;分類(X)軸;數(shù)值(Y)軸等為了圖表左右Y軸的數(shù)據(jù)相關,必須讓左右Y軸數(shù)據(jù)對齊:如:剛才不良的個數(shù)為:116(左軸) 對應的右軸應該是100%雙擊圖表上的數(shù)值,會彈出如下提示框:根據(jù)需要調節(jié)最大最小值這樣一個柏拉圖就完美的誕生了!2. 直方圖與EXCEL的結合運用某客戶的電阻值要求為50020歐最近發(fā)現(xiàn)客戶的產(chǎn)品波動比較大，所以歸納了近段時間的連續(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 491.4 469.4 500

5、.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 487.4 495.0 49

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

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

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

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

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

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

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

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

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

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

16、ALSE，即返回 FALSE。 OR步驟1：計算人與人交叉（A*B,B*C,A*C）交叉法運算法則：A*CB*DABDC=G5*E9/G9A=B*D/C注：期待數(shù)量是運算出來的，而數(shù)量則是實測出來的，數(shù)量詳細計算如下頁。B總計01A0數(shù)量72375期望數(shù)量38.00 37.00 751數(shù)量47175期望數(shù)量38.00 37.00 75總計數(shù)量7674150期望數(shù)量7674150由上表可以看出需要根據(jù)測量的數(shù)據(jù)計算出：A=0&B=0A=0&B=1A=1&B=0A=1&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

17、=1&C=1+Excel 中鍵入的表達式（參考）：=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)得出的運算結果如下：根據(jù)運算的結果用根據(jù)運算的結果用SUM函數(shù)算出對應的個數(shù)，函數(shù)算出對應的個數(shù)，引用到指定的單元格中引用到指定的單元格中人與標準交叉 （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步驟3：計算交互的Kappa結果KappaABCA0.91 0.92 B0.91 0.93 C0.92 0.93

18、 設計的運算公式：)1(_eeoBAPPPKappa其中：Po為相同判別（如A=0&B=0和A=1&B=1 ）的對應數(shù)量占的比率； Pe為相同判別（如A=0&B=0和A=1&B=1 ）的對應期望數(shù)量占的比率。表達式如下：總數(shù)XXXPbAbAo)(1&10&0期望總數(shù)期望期望XXXPbAbAe)(1&10&0總數(shù)期望總數(shù)注：XX鍵入Excel的表達式測量員A&B&C和S標準值的Kappa值計算同上。步驟4：交互的Kappa判別的結論一致性判定標準：一致性判定標準：通用的比例法則是Kappa0.75則表示有很好的一致性，Kappa0.40則表示一致性不好。有效性判定標準有效性判定標準有效性漏報率錯

19、誤報警率可接受90%2%5%邊界接受但要改善邊界接受但要改善80%80%5%5%10%10%不可接受80%5%10%Kappa交叉法完成交叉法完成SPC與EXCEL的結合運用CPK與EXCEL的結合運用SPC是過程統(tǒng)計控制的工具，而過程控制與過程能力CP&CPK緊密結合。例：兩供應商（A&B）的產(chǎn)品差異較大，現(xiàn)對供應商的制程能力進行評估和對比。如何用如何用Excel評估評估CPK及其相關參數(shù)及其相關參數(shù)?列數(shù)較多，其中列數(shù)較多，其中一部分已隱藏一部分已隱藏所涉及的函數(shù)所涉及的函數(shù):統(tǒng)計函數(shù)統(tǒng)計函數(shù):返回參數(shù)的平均值（算術平均值）。 AVERAGESTDEV估算樣本的標準偏差。標準偏差反映相對于

20、平均值 (mean) 的離散程度。 所涉及的定義及計算公式所涉及的定義及計算公式:注： USL為規(guī)格上限； SLS為規(guī)格下限； 如果只有規(guī)格上限,那么:CPK=CP; 如果只有規(guī)格下限,那么:CPK=CPL.3UTCpu3LTCpl(3) 工序能力指數(shù)上限Cpu： (4) 工序能力指數(shù)下限Cpl ：66LUTTTCP1n).()()(2n2221=(1) 標準方差（STDEV）：(2)工序能力指數(shù) CP：(5)工序能力修正指數(shù)CPK： CpCaCplCpuMinCpk*)1 (),(Max=Max=MAX(C6:C40)=MAX(C6:C40)=MAX(D6:D40)=MAX(D6:D40)M

21、in=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)=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)/

22、(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)C46)=(D45-D44)/(3=(D45-D44)/(3* *D46)D46)CPK=CPK=MIN(C48:C49)=MIN(C48:C49)=MIN(D48:D49)=MIN(D48:D49)鍵入表達式，如下鍵入表達式，如下:利用利用Excel輕松的完成輕松的完成CPK評估！評估！簡述建立簡述建立Xbar-R控制圖的步驟控

23、制圖的步驟1. 收集數(shù)據(jù) 選擇子組大小、頻率和子組數(shù)量 建立控制圖及記錄原始數(shù)據(jù) 計算每個子組的X平均值和極差R 選擇控制圖的刻度 均值圖:坐標上的刻度值的最大值與最小值至少為子組均值的最大值與最小值差的兩倍. 極差圖:刻度值應從最低值為0開始到最大值之間差值為初始階段最大極差的2倍. 將平均值和極差畫到控制圖上Xbar-R與EXCEL的結合運用2. 計算控制限 計算極差的平均值和過程平均值 計算控制限 在控制圖上畫出中心線及控制線 3.過程控制解釋4. 任何超出控制限的點;5.連續(xù)7點全在中心線之上或之下;6.連續(xù)7點上升或下降;7.明顯的非隨機圖形等等8. 所涉及的定義及計算公式所涉及的定

24、義及計算公式: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.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個數(shù)據(jù).部分列被隱藏步驟2: 根據(jù)前面的公式算出Xbar和Rbar步驟3: 根據(jù)前面的公式算出上下管控線Rbar=AVERAGE(C9:

25、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個區(qū)間(分別代表+/-3S)步驟5: 制作圖表雙擊圖表中該處刻度,得出如下頁圖框步驟6: 調整和修改圖表根據(jù)控制線上下限定義最大最小刻度值雙擊Xbar線,得出以下圖框選取自動和樣式,如左下圖.雙擊圖框內任一空白處,得出下提示框點擊圖框 ,彈出右圖點擊完成后得到:完成完成Xbar的制作的制作,Rbar同上就不介同上就不介紹紹.了了.日常工作中項目管理與EXCEL的結合運用日常項目管理與EXCEL的結合運用在日常中,我們經(jīng)常會遺忘過很多東西,比如某項目的跟進,樣板的完成狀況等.以下我們就日常的一些項目管理方面的舉些例子:Case1: 跟進項目計劃完成