變數CPK、Sigma、PPM製程分析表

 

分析項目        =規格輸入!B6 =規格輸入!B19

規格上限        =規格輸入!E6 =規格輸入!E19

規格下限        =規格輸入!E7 =規格輸入!E20

CL：規格中心 =K5+(K4-K5)/2 =L5+(L4-L5)/2

T：規格寬度  =K4-K5     =L4-L5

分析圖    上限        =規格輸入!E8 =規格輸入!E21

分析圖    下限        =規格輸入!E9 =規格輸入!E22

產線量測資料取點數    =COUNT(E2:E65517)       =COUNT(F2:F65517)

單邊除3σ，雙邊除6σ      =IF((K4*K5)=0,3,6)  =IF((L4*L5)=0,3,6)

上限最小單位 =IF(K4>1000,100,IF(K4>100,10,IF(K4>10,1,IF(K4>1,0.1,IF(K4>0.1,0.01,IF(K4>0.01,0.001,0.0001)))))) =IF(L4>1000,100,IF(L4>100,10,IF(L4>10,1,IF(L4>1,0.1,IF(L4>0.1,0.01,IF(L4>0.01,0.001,0.0001))))))

下限最小單位 =IF(K5>1000,100,IF(K5>100,10,IF(K5>10,1,IF(K5>1,0.1,IF(K5>0.1,0.01,IF(K5>0.01,0.001,0.0001)))))) =IF(L5>1000,100,IF(L5>100,10,IF(L5>10,1,IF(L5>1,0.1,IF(L5>0.1,0.01,IF(L5>0.01,0.001,0.0001))))))

量測資料上限        =MAX(E2:E65517)   =MAX(F2:F65517)

量測資料下限        =MIN(E2:E65517)    =MIN(F2:F65517)

X：群體平均值      =AVERAGE((E2:E65517)) =AVERAGE((F2:F65517))

σp：群體標準差  =STDEV((E2:E65517))      =STDEV((F2:F65517))

ca準度指標   =(K16-K6)/(K7/2)     =(L16-L6)/(L7/2)

cp精度指標   =K7/(K11*K17)        =L7/(L11*L17)

cpk精準度指標     =K19*(1-ABS(K18))  =L19*(1-ABS(L18))

正態分佈ppm       ="正態分佈不良率為："&ROUND(10^6*(1-NORM.DIST(3*K20, 0, 1, 1))*2,0)&" ppm" ="正態分佈不良率為："&ROUND(10^6*(1-NORM.DIST(3*L20, 0, 1, 1))*2,0)&" ppm"

量測值>=上限的數量    =COUNTIF((E2:E65517),">"&K4)        =COUNTIF((F2:F65517),">"&L4)

量測值<=下限的數量    =COUNTIF((E2:E65517),"<"&K5)        =COUNTIF((F2:F65517),"<"&L5)

極限域ppm   ="量測"&K10&"點中，大於上限的，共有"&K22&"點，小於下限的，共有"&K23&"點，目前此批製程不良率為"&ROUND(10^6*(K22+K23)/K10,0)&" ppm"       ="量測"&L10&"點中，大於上限的，共有"&L22&"點，小於下限的，共有"&L23&"點，目前此批製程不良率為"&ROUND(10^6*(L22+L23)/L10,0)&" ppm"

四分位確認：        下離群值：    =QUARTILE(E2:E65517,1)-1.5*(QUARTILE(E2:E65517,3)-QUARTILE(E2:E65517,1))        =QUARTILE(F2:F65517,1)-1.5*(QUARTILE(F2:F65517,3)-QUARTILE(F2:F65517,1))

        上離群值：    =QUARTILE(E2:E65517,3)+1.5*(QUARTILE(E2:E65517,3)-QUARTILE(E2:E65517,1))        =QUARTILE(F2:F65517,3)+1.5*(QUARTILE(F2:F65517,3)-QUARTILE(F2:F65517,1))      

獨立雙樣本 t 檢定（變異數不同）   =IF(T.TEST(E2:E65517,F2:F65517,2,3)<0.05,"兩者間證明有顯著的相關連性。","兩者間沒有證據證明有顯著的相關連性。")             　

成對雙樣本 t 檢定       =IF(T.TEST(E3:E65518,F3:F65518,2,3)<0.05,"兩者間證明有顯著的相關連性。","兩者間沒有證據證明有顯著的相關連性。")             　

                        　

=規格輸入!B6&"區間" =IF($K$13>=1,"最小單位1",IF($K$13=0.1,"最小單位0.1",IF($K$13=0.01,"最小單位0.01",IF($K$13=0.001,"最小單位0.001","")))) 數量
="≦"& O2 =IF($K$13>=1,ROUND($K$9-($K$13),0),IF($K$13=0.1,ROUND($K$9-($K$13),1),IF($K$13=0.01,ROUND($K$9-($K$13),2),IF($K$13=0.001,ROUND($K$9-($K$13),3),ROUND($K$9-($K$13),4))))) =FREQUENCY(製程統計表!E2:E65535,O2:O14)
=IF($K$13>=1,INT(O2)&"~"&INT(O3),IF($K$13=0.1,INT((O2)*10)/10&"~"&INT(O3*10)/10,IF($K$13=0.01,INT((O2)*100)/100&"~"&INT(O3*100)/100,IF($K$13=0.001,INT((O2)*1000)/1000&"~"&INT(O3*1000)/1000,INT((O2)*10000)/10000&"~"&INT(O3*10000)/10000)))) =IF($K$13>=1,ROUND($O2+$O$15,0),IF($K$13=0.1,ROUND($O2+$O$15,1),IF($K$13=0.01,ROUND($O2+$O$15,2),IF($K$13=0.001,ROUND($O2+$O$15,3),ROUND($O2+$O$15,4))))) =FREQUENCY(製程統計表!E2:E65535,O2:O14)
=IF($K$13>=1,INT(O3)&"~"&INT(O4),IF($K$13=0.1,INT((O3)*10)/10&"~"&INT(O4*10)/10,IF($K$13=0.01,INT((O3)*100)/100&"~"&INT(O4*100)/100,IF($K$13=0.001,INT((O3)*1000)/1000&"~"&INT(O4*1000)/1000,INT((O3)*10000)/10000&"~"&INT(O4*10000)/10000)))) =IF($K$13>=1,ROUND($O3+$O$15,0),IF($K$13=0.1,ROUND($O3+$O$15,1),IF($K$13=0.01,ROUND($O3+$O$15,2),IF($K$13=0.001,ROUND($O3+$O$15,3),ROUND($O3+$O$15,4))))) =FREQUENCY(製程統計表!E2:E65535,O2:O14)
=IF($K$13>=1,INT(O4)&"~"&INT(O5),IF($K$13=0.1,INT((O4)*10)/10&"~"&INT(O5*10)/10,IF($K$13=0.01,INT((O4)*100)/100&"~"&INT(O5*100)/100,IF($K$13=0.001,INT((O4)*1000)/1000&"~"&INT(O5*1000)/1000,INT((O4)*10000)/10000&"~"&INT(O5*10000)/10000)))) =IF($K$13>=1,ROUND($O4+$O$15,0),IF($K$13=0.1,ROUND($O4+$O$15,1),IF($K$13=0.01,ROUND($O4+$O$15,2),IF($K$13=0.001,ROUND($O4+$O$15,3),ROUND($O4+$O$15,4))))) =FREQUENCY(製程統計表!E2:E65535,O2:O14)
=IF($K$13>=1,INT(O5)&"~"&INT(O6),IF($K$13=0.1,INT((O5)*10)/10&"~"&INT(O6*10)/10,IF($K$13=0.01,INT((O5)*100)/100&"~"&INT(O6*100)/100,IF($K$13=0.001,INT((O5)*1000)/1000&"~"&INT(O6*1000)/1000,INT((O5)*10000)/10000&"~"&INT(O6*10000)/10000)))) =IF($K$13>=1,ROUND($O5+$O$15,0),IF($K$13=0.1,ROUND($O5+$O$15,1),IF($K$13=0.01,ROUND($O5+$O$15,2),IF($K$13=0.001,ROUND($O5+$O$15,3),ROUND($O5+$O$15,4))))) =FREQUENCY(製程統計表!E2:E65535,O2:O14)
=IF($K$13>=1,INT(O6)&"~"&INT(O7),IF($K$13=0.1,INT((O6)*10)/10&"~"&INT(O7*10)/10,IF($K$13=0.01,INT((O6)*100)/100&"~"&INT(O7*100)/100,IF($K$13=0.001,INT((O6)*1000)/1000&"~"&INT(O7*1000)/1000,INT((O6)*10000)/10000&"~"&INT(O7*10000)/10000)))) =IF($K$13>=1,ROUND($O6+$O$15,0),IF($K$13=0.1,ROUND($O6+$O$15,1),IF($K$13=0.01,ROUND($O6+$O$15,2),IF($K$13=0.001,ROUND($O6+$O$15,3),ROUND($O6+$O$15,4))))) =FREQUENCY(製程統計表!E2:E65535,O2:O14)
=IF($K$13>=1,INT(O7)&"~"&INT(O8),IF($K$13=0.1,INT((O7)*10)/10&"~"&INT(O8*10)/10,IF($K$13=0.01,INT((O7)*100)/100&"~"&INT(O8*100)/100,IF($K$13=0.001,INT((O7)*1000)/1000&"~"&INT(O8*1000)/1000,INT((O7)*10000)/10000&"~"&INT(O8*10000)/10000)))) =IF($K$13>=1,ROUND($O7+$O$15,0),IF($K$13=0.1,ROUND($O7+$O$15,1),IF($K$13=0.01,ROUND($O7+$O$15,2),IF($K$13=0.001,ROUND($O7+$O$15,3),ROUND($O7+$O$15,4))))) =FREQUENCY(製程統計表!E2:E65535,O2:O14)
=IF($K$13>=1,INT(O8)&"~"&INT(O9),IF($K$13=0.1,INT((O8)*10)/10&"~"&INT(O9*10)/10,IF($K$13=0.01,INT((O8)*100)/100&"~"&INT(O9*100)/100,IF($K$13=0.001,INT((O8)*1000)/1000&"~"&INT(O9*1000)/1000,INT((O8)*10000)/10000&"~"&INT(O9*10000)/10000)))) =IF($K$13>=1,ROUND($O10-$O$15,0),IF($K$13=0.1,ROUND($O10-$O$15,1),IF($K$13=0.01,ROUND($O10-$O$15,2),IF($K$13=0.001,ROUND($O10-$O$15,3),ROUND($O10-$O$15,4))))) =FREQUENCY(製程統計表!E2:E65535,O2:O14)
=IF($K$13>=1,INT(O9)&"~"&INT(O10),IF($K$13=0.1,INT((O9)*10)/10&"~"&INT(O10*10)/10,IF($K$13=0.01,INT((O9)*100)/100&"~"&INT(O10*100)/100,IF($K$13=0.001,INT((O9)*1000)/1000&"~"&INT(O10*1000)/1000,INT((O9)*10000)/10000&"~"&INT(O10*10000)/10000)))) =IF($K$13>=1,ROUND($O11-$O$15,0),IF($K$13=0.1,ROUND($O11-$O$15,1),IF($K$13=0.01,ROUND($O11-$O$15,2),IF($K$13=0.001,ROUND($O11-$O$15,3),ROUND($O11-$O$15,4))))) =FREQUENCY(製程統計表!E2:E65535,O2:O14)
=IF($K$13>=1,INT(O10)&"~"&INT(O11),IF($K$13=0.1,INT((O10)*10)/10&"~"&INT(O11*10)/10,IF($K$13=0.01,INT((O10)*100)/100&"~"&INT(O11*100)/100,IF($K$13=0.001,INT((O10)*1000)/1000&"~"&INT(O11*1000)/1000,INT((O10)*10000)/10000&"~"&INT(O11*10000)/10000)))) =IF($K$13>=1,ROUND($O12-$O$15,0),IF($K$13=0.1,ROUND($O12-$O$15,1),IF($K$13=0.01,ROUND($O12-$O$15,2),IF($K$13=0.001,ROUND($O12-$O$15,3),ROUND($O12-$O$15,4))))) =FREQUENCY(製程統計表!E2:E65535,O2:O14)
=IF($K$13>=1,INT(O11)&"~"&INT(O12),IF($K$13=0.1,INT((O11)*10)/10&"~"&INT(O12*10)/10,IF($K$13=0.01,INT((O11)*100)/100&"~"&INT(O12*100)/100,IF($K$13=0.001,INT((O11)*1000)/1000&"~"&INT(O12*1000)/1000,INT((O11)*10000)/10000&"~"&INT(O12*10000)/10000)))) =IF($K$13>=1,ROUND($O13-$O$15,0),IF($K$13=0.1,ROUND($O13-$O$15,1),IF($K$13=0.01,ROUND($O13-$O$15,2),IF($K$13=0.001,ROUND($O13-$O$15,3),ROUND($O13-$O$15,4))))) =FREQUENCY(製程統計表!E2:E65535,O2:O14)
=IF($K$13>=1,INT(O12)&"~"&INT(O13),IF($K$13=0.1,INT((O12)*10)/10&"~"&INT(O13*10)/10,IF($K$13=0.01,INT((O12)*100)/100&"~"&INT(O13*100)/100,IF($K$13=0.001,INT((O12)*1000)/1000&"~"&INT(O13*1000)/1000,INT((O12)*10000)/10000&"~"&INT(O13*10000)/10000)))) =IF($K$13>=1,ROUND($O14-$O$15,0),IF($K$13=0.1,ROUND($O14-$O$15,1),IF($K$13=0.01,ROUND($O14-$O$15,2),IF($K$13=0.001,ROUND($O14-$O$15,3),ROUND($O14-$O$15,4))))) =FREQUENCY(製程統計表!E2:E65535,O2:O14)
="＞"& O13 =IF($K$13>=1,ROUND($K$8+($K$12),0),IF($K$13=0.1,ROUND($K$8+($K$12),1),IF($K$13=0.01,ROUND($K$8+($K$12),2),IF($K$13=0.001,ROUND($K$8+($K$12),3),ROUND($K$8+($K$12),4))))) =FREQUENCY(製程統計表!E2:E65535,O2:O14)
組距 =(O14-O2)/13 　
加總 　 =SUM(P2:P14)

規格中心畫線
X軸 Y軸
=MATCH(MAX($P20:$P32),P20:P32) 0
=MATCH(MAX($T20:$T32),T20:T32) =P26規格中心偏移值判斷
=規格輸入!$B$6&"+規格偏移" =規格輸入!$B$19&"+規格偏移"
=MATCH(MAX($P2:$P14),P2:P14) =MATCH(MAX($T2:$T14),T2:T14)
=IF(R37<N37,"製程平均值偏下限，請將製程平均值向上限移動。",IF(R37>N37,"製程平均值偏上限，請將製程平均值向下限移動。","製程平均值與理論正態分布相符。")) =IF(S37<N38,"製程平均值偏下限，請將製程平均值向上限移動。",IF(S37>N38,"製程平均值偏上限，請將製程平均值向下限移動。","製程平均值與理論正態分布相符。"))