用Excel2024绘制均值-极差控制图

用EXCEL绘制i—R|限制图

例5-3：某汽车发动机制造厂要求对活塞环零件的制造过程建立均值-极差限制图，对直

径进行质量限制。

表5-14活塞环直径的数据表

样本序号观测值均值极差

174.03074.00274.01973.99274.00874.0100.038

273.99373.99274.00174.0。174.01174.000U.019

373.98874.02474.02174.00574.00274.0080.036

474.00273.99673.99374.01574.00974.0030.022

573.99274.00774.01573.98974.01474.0030.026

674.00973.99473.99773.98573.99373.9960.024

773.99574.00673.99474.00074.00574.0000.012

873.98574.00373.99374.01573.99873.9990.030

974.00873.99574.00974.00574.00474.0040.014

1073.99874.00073.99074.00773.99573.9980.017

1173.99473.99873.99473.99573.99073.9940.008

1274.00474.00074.00774.00073.99674.0010.011

1373.98374.00273.99873.99774.01273.9980.029

1474.00673.96773.99474.00073.98473.9900.039

1574.01274.01473.99873.99974.00774.0060.016

1674.00073.98474.(X)573.99873.99673.9970.021

1773.99474.01273.98674.00574.00774.0010.026

1874.00674.01074.01874.00374.00074.0070.018

1973.98474.00274.00374.00573.99773.9980.021

2074.00074.01074.01374.02074.00374.0090.020

2173.99874.00174.00974.00573.99674.0020.013

2274.00473.99973.99074.00674.00974.0020.019

2374.01073.98973.99074.00974.01474.0020.025

2474.01574.00873.99374.00074.01074.0050.022

2573.98273.98473.99574.01774.01373.9980.035

绘图步骤：

(1)在EXCEL表格中输入25组数据。

切开*稣

B

4MGE

知-11•A*A'二■■11浙

上复制

平/msBBi

==5:乔津国合并点居中

秀贴友口字玄Q

H28▼包

ABCDEFGH

1徉本序号«Plfi

2J1740W740057401Q71827400«

2力勉73的274.00174.00174.011

41373.98874.02474.02!74.00574.002

51474002

1399673.9937401574009

6J573.效274.00774.01573.W974.014

674.00973.9W73.99773JS573.993

817739957400675.9947400074005

9J873.9S574.00373.99374.01573.993

10J974.00873as74.00974.00574.004

Hj1073998740007399074.00773.995

12jH73.99473冽73W73JW73.990

13j12-4.00474.00074.007?4.00073.波

HJ1373.98374.00273.99873J9774.012

15jM7400673,96773.994?400073.984

16j1574.01274.01473.99S13.99974.007

17j1674.00013.9U74.00573.99S73.996

181n7399474012739867400574,007

19jIS74.00674.01074.01874.00374,000

20j1973.9S474.00274.00374.00573.997

20740007401074.0137402074.003

22，21*3998

7400174.0097400573.996

23j2274.00473.99973.99074.006

24jB74.01073.9S973.99074.00974.014

25j2474015740OS739937400074,010

26j2573.98213.9U7199574.01774.013

27|

(2)计算每一组样本的平均值和极差

,C!sy_

抵入页面布局J公式［M审■m

■,6n■，aQ定

D炉用

婚入己教白功求和3使用I至艰文本口期和近忧与ama其他毡sr篇的限

▼的国效••・时弼，弓【用•三龟为J?”・

££五零定义

M20"CxZ

ABcDEFGH

样本序号双则值均值极差

5r

2I174.03074.00274.01973.99274.00874.010'0.038

31273a573.99274.00174.00174.011F74.000'0.019

4373.98874.02474.02174.00574.002’74.008'0.036

51§74.00273.99673.99374.01574.009’74.003'0.022

61573.99274.00774.01573.9S974.014F74.003'0.026

7]674.00973.99473.99773M573.993'73.996'0.024

8I773.99574,00673.99474.00074.005'74.000r0.012

r

973.98574,00373.99374.01573.998’73.9990.030

Fr

107400873.99574,00974,0057s374.0040.014

「r

U7399s7400073.99074,00773.99573.9980.017

121!1739947399873.99473.995TWO’73.994'0.008

r

1312730047400074.00774.000B.99674.001'0.011

141373.98374.00273.99873.99774,012’73.998'0.029

151474.00673,96773.X474.00073如’73.990'0.039

F

161574.01274.01473.99873.99974.007'74.0060.016

171674.000；3她74.00573JM73.X6’73.997'0.021

F74.001'0.026

181773^9474.01273.浓74.00574.007

r

191874.00674.01074.01874.00374.00074.007'0.018

r

20197398A74.00274.00374.00573.99773.998'0.021

r

212074.00074.01074.01374.02074.00374.009'0.020

rF

222173.99874.00174.00974.00573.映74.0020.013

rr

232274.00473.99973.99074.00674.00974.0020.019

F

24B74.01073.99973.99074.00974.01474.002'0.025

252474.01574.00873.99374.00074.010'74.005'0.022

262573.98273.98473.99574.01774.0B'73.998'0.035

271

H<►,Sh88ti,,Sh»»t2/SheM3,"4

(3)计算25组样本平均值的总平均值和25组样本极差的平均值

.••—•.••——•一J--•••••

上，jq1金A@瞒M噩了然

u排序…年选£菽

目Access自网站台文本且具〜源况右连接登新-希的

iSS刊审0标选

K23▼JLL

千

ABc_LDEF一GH

徉本序号双期值均值极差

FF

2174.03074.00274.01973.99274.00S74.0100.038

FF

3273.99573.99274.00174.00174.01174.0000.019

r

4373.MS74,024740217400574,00274.0080.036

rF

R474.00273.99673a374.01574.00974.0030.022

rF

6573.99274.00774.01573.9S974.01474.0030.026

F

767400973.99473.99773.M573.993’73.9960.024

FF

8773.99574.00673JW74.00074.00574.0000.012

r

973.9S574.00373.99374.0157399s73.9990.030

FF

1074,00873.殓7400974.00574.00474.0040.014

F

II73.99874.00073^074.00773.995’73.9980.017

F

121173.99473.99S739947399573.990'73.9940.008

rF

131274.00474.00074.00774.00073.99674.0010.011

rF

141373.W374.00273.99873.99774.01273.9980.029

FF

161574.01274.01473J9873.99974.00774.0060.016

rF

171674.00073.98474.00573.99873.99673.9970.021

rF

181773.99474.01273.9S674.00574.00774.0010.026

F

19!874.00674.01074.01874.00374.000’74.0070.018

F

201973.%474.0027400374.00573.997'73.9980.021

FF

212074.00074.01074.01374.02074.00374.0090.020

rF

2221738874.00174.00974.00J75.99C74.0020.013

F

232274.00473,999739907400674,00974.0020.019

rF

242374.01073.98973为074.00974.01474.0020.025

F

252474.01574.00S73.99374.00074.010’74.0050.022

nz:F

2b373%273.98473.99574.01774.013

2711(74.00!0.022)

H4*>Sheetl,Sheet2Sheet3

tttt

(4)计算［图和R图的限制界限，并输入表格中

\U1-MicrosoftExcel

«A火工行马馆HEi6网视圉

、*SS切i,,...•剧i战

U2•11-AA===浙于自胡舒1

国合并后名中・列

B/u-•$•===*津▼%■・盛3•哀唱g

对开方式•*巳_样式

N22，>

IKABCDEFGHTTL二

1洋本序号双创一均值梃差ITCLCLLCL

2174.03074.00274.01973.9927400S'74.010'0.03874.01474.00173.988

3273勉73.99274.00174.00174.011r74.000'0.01974.01474.00173.988

F

43739887402474021740057400274.008'0.03674.01474.00173.988

5474.00213.99673的37401574.009'74.003'0.02274.01474.00173.988

6573.99274.00774.01573.M974.014'74.003'0.02674.01474.00173.988

7674.00973.9W73.99773.9S573.993‘73.996'0.02474.01474.00173.988

8773勉74.00673.994乂.00074-005'74.000'0.01274.01474.00173.988

98719857300373993740157399s'73.999'0.03074.01474.00173.988

10974.00813.995U.W974.005'4-OM'74.004'0.0M74.01474.00173.988

111073W3900073.W074.00773W5'73.998'0.01774.01474.00173.988

121173.99473.99873.99473.99573.990‘73.994'0.00874.01474.00173.988

131274.00474.00074.00774.00071996r74.001‘0.01174.01474.00!73.988

151474.00673.96773.99474.00073.984'73.990'0.03974.01474.00173.988

r

161574012740147399s13999•400774.006'0.01674.01474.00173.988

171674.00013.9U74.00373.99873.996r73.997'0.02174.01474.00173.988

181773.99474.01273.98674.00574.007‘74.001'0.02674.01474.00173.988

19IS74.00674.01074.01874.00374.000r74.007'0.01874.01474.00173.988

201973.9S474.00274.00374.00573.997'73.998'0.02174.01474.00!73.988

2120740007401074.0137402074003F74.009'0.02074.01474.00173.988

222174.00174.00974.005力.996'74.002'0.01374.01474.00173.988

23227400473999719907400674009'74.002'0.01974.01474.00173.988

242374.01073.9S973.99074.00974.014‘74.002'0.02574.01474.00173.988

252474.01574.00873.99374.00074.010'74.005'0.02274.01474.00173.988

26257398273如739957401774013’73.998'0.03574.01474.00173.988

2174.0010.022

i4■

（7）修改上面的限制图。点中间的绿线，右击，设置数据系列格式。

用同样的方法修改另外两条折线，并将线型改为虚线。

得到以下限制图

74.020

74.015

74.010

74.005

系列1

74.000♦

系列2

73.995

——系列3

73.990

系列4

73.985

73.980

73.975

为变更图中右侧系列1、系列2、系列3、系列4的名称，将表格中UCL、CL、LCL的

名称分别改为UCL=74.014,CL=74.001,LCL=73.988。

I

CL=74.01<CL=74.00LCL=73.988

74.U1474.UU173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474,00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

74.01474.00173.988

选择系列2,编辑,将系列名称改为UCL=74列4。

用同样的方法，分别更改系列3和系列4的名称。

更改系列名称后，如卜.图。

选中系列I,删除。

7r"4T-0V2fc0V「

74.015

7/"4♦.0v1xun

7/•4♦.ownos

/4.UUU\\...........UCL=74.014

73Q95-»\/CLs74.(X)l

72QQn--------------------------------------------_---▼X-_--_--_--_--_--_--_--_--_--_--_--_--_--_--_--_--_--_--_--_-------------L1nC/n=7.n4oQoftA

73QRS

73ago

7<w3.Q^r7J5n

135791113151719212325

设置坐标轴格式，更改最小值和主要刻度单位。

融口次要网格关⑻)

设置主要网格浅格式

雪设置坐房S格式(£)“.

删除图中网络线。

历1•代・汹55-Microsoft卬d

国回网阑凰国1国型第1国回0闽蛆表名欲

「

A.更衰

生1两12

■X做5WB更*标国坐标辅屋物m近荃埃第M壬，诂**三，三0丈也出浜折小涨/筮谓触法

♦坛是,,,♦

标至

MA名或业•丸

不显己师得医

3.jfl

J左一—一一.一1

限制IXX-74.014a-74.OOI.a-73.9

F国1

74.00274.01973勉，.0«74.010'0.03874.0U74.00173.988出示主要单位工・网喧

r

”的274.001“0U74.000'0.01974.01474.00173.988

，024“0N74^05为她'74.008'0.03674.01474.00173.988次0W8线

?386”外3?4^15UM’74.003'0.02274.01474.00173.988j*d次JMW尊依第..壮

F

?4007，01，3W?40U74.003'0.02674.01474.00173.988

7；004noo'“X，no©’72.906'0.02474.01474.00173.OQOm愉4知次WR俵线

rnn

74006觉9W74000740©74.000■0.01274.01474.00173.989工示主要时度・位和次要阴

’73.999■0.030

740037399374015399874.0U74.00173.986度空位削■同唳E

r74.004'0.014

738574.00974.005Y.OO174.0U74.00173.988wUQ-74014

7400073分074.007邛物’73.998'o.on74.01474.00173.988K

tt-74.001

7399173的4”刻，3顿'73.994'0.00874.01474.00173-988

rIXL-73988

74AJ7力•000，3史《74.001'0.011V

74.01474.00173.988

73WO

为她73例”好MOD’73.998'0.02974.01474.00173.988

73%：”包?4fi00”摊’73.990'0.03974.01474.00173.988

r

?4014觉99t?»999?4«?74.006'0.01674.01474.00173.988

73$S0

:JW74005499t739a’73.997'0.02174.01474.00173.988

74012萼*7400574OCT’74.001'0.02674.01474.00173.989

r

74010740IS“83’40®74.007■0.02874.01474.00i?3.988

74.020

74.010