excel中如何制作直方图与正态图教程1.docx
- 文档编号:11014699
- 上传时间:2023-02-24
- 格式:DOCX
- 页数:23
- 大小:77.23KB
excel中如何制作直方图与正态图教程1.docx
《excel中如何制作直方图与正态图教程1.docx》由会员分享,可在线阅读,更多相关《excel中如何制作直方图与正态图教程1.docx(23页珍藏版)》请在冰豆网上搜索。
excel中如何制作直方图与正态图教程1
excel中如何制作直方图与正态图--教程
原创文章,by无敌孔子
经常性的有在excel中做直方图、正态分布图的需求,今天看了excelhome论坛大牛的讲解视频,有一种茅塞顿开的感觉,分享一下如何制作直方图和正态分布图,大家根据不同的数据照着做就可以了。
有需要视频教学和原数据的,请留言。
、
直方图和正态分布图是分不开的,excel中直方图出来了,正态分布图也就差不多了。
先来看如何制作直方图,所有数据放在了A列,然后我们需要统计以下数据:
1、我使用的原数据如下(来源于excelhome论坛的配套资料)
51.7
50.6
57.9
56.9
56.7
56.7
55.3
56.1
53.7
54.5
56.9
51.9
52.1
55.1
54.9
54.7
55.3
55.3
54.5
54.9
54.5
55.3
54.9
54.3
53.7
53.5
53.7
53.1
54.5
53.1
53.9
53.5
53.3
53.9
53.5
53.5
52.5
53.3
53.5
53.3
53.7
53.1
54.5
53.9
56.7
54.5
54.3
55.1
54.1
54.5
53.9
53.1
53.3
55.3
55.7
56.1
54.7
53.1
53.3
52.7
53.1
52.9
53.1
54.3
53.1
52.7
53.1
53.3
53.1
53.3
53.1
53.3
55.1
54.7
54.9
54.3
53.9
53.7
53.9
53.5
54.5
54.3
55.5
55.7
55.5
54.9
55.3
55.5
53.7
54.1
53.9
55.7
55.9
53.7
53.5
53.1
52.3
52.7
52.9
53.3
53.9
52.7
53.5
53.1
52.7
51.9
52.5
53.9
54.5
55.7
55.3
54.9
53.1
52.9
54.1
53.3
54.7
53.9
54.3
54.1
53.7
53.3
52.7
52.9
52.5
53.9
53.5
54.1
54.1
54.7
54.9
54.9
54.1
53.3
52.9
53.7
53.9
54.3
54.1
54.5
54.7
54.9
52.1
52.9
53.5
52.7
53.1
53.1
53.5
52.9
52.9
53.1
53.3
52.7
53.5
53.9
54.9
55.1
54.3
55.1
54.3
54.3
53.9
54.5
54.5
54.3
55.3
54.5
54.9
53.5
52.1
55.3
55.7
55.7
55.5
54.5
57.7
54.7
53.7
53.1
53.7
55.9
56.1
53.9
53.7
53.3
53.9
53.9
54.5
54.7
56.1
55.7
53.1
53.7
53.5
53.9
53.9
53.5
53.3
53.1
52.5
55.9
55.7
54.1
54.3
54.1
54.1
54.5
54.5
55.1
53.1
53.3
54.1
54.3
53.9
54.1
54.7
54.7
53.7
53.1
53.3
52.7
53.5
52.9
53.7
56.5
56.1
55.7
55.5
56.9
57.7
56.5
55.7
54.1
54.7
55.7
55.5
53.1
52.7
53.1
53.3
53.5
54.3
54.1
54.5
54.7
55.7
55.5
54.1
54.3
54.7
53.1
53.3
53.1
52.7
53.1
53.7
53.1
54.7
54.5
55.1
54.7
54.5
56.1
55.7
53.3
52.5
53.7
54.1
53.3
52.1
52.3
53.1
53.3
53.5
53.3
53.1
52.7
53.1
55.7
55.1
54.3
53.7
53.1
52.9
53.1
52.7
52.5
53.1
53.5
53.1
53.3
54.1
55.1
54.9
56.1
55.7
56.5
54.7
53.7
2、我们需要统计的数据如下:
规格
数据统计
直方图
规格中心
数据个数
最大值
规格上限
最大值
最小值
规格下限
最小值
区间
平均值
直方图柱数
标准偏差
直方图组距
来详细说明一下每个字段如何得到:
规格-规格中心:
这个是根据你的产品规格或者你所要统计数据的理论值的平均值,与原数据无关,如果你要制作与原数据相关的直方图,那么这个就是需要的。
我们一般是自己统计原数据的平均值,最大值,最小值来做直方图以及正态分布图的。
所以规格中心、规格上限、规格下限得看你自己要求作出哪种直方图or正态分布图来定,你要是使用规格值,那么最大值,最小值,平均值就可以不必统计。
[原创by无敌孔子
规格-规格上限:
同上解释
规格-规格下限:
同上解释
数据统计-数据个数:
统计我们放在A列中的数值到底有多少个,使用公式=counta(A:
A)[红色的是你的数据列,你可以根据自己存放数据的不同而去更改他]
数据统计-最大值:
统计数据的最大值,使用公式=MAX(A:
A)[红色的是你的数据列,你可以根据自己存放数据的不同而去更改他]
数据统计-最小值:
统计数据的最小值,使用公式=MIN(A:
A)[红色的是你的数据列,你可以根据自己存放数据的不同而去更改他]
数据统计-平均值:
数据的平均值,使用公式=AVERAGE(A:
A)[红色的是你的数据列,你可以根据自己存放数据的不同而去更改他]
数据统计-标准偏差:
统计数据的标准偏差,使用公式=STDEV(A:
A)[红色的是你的数据列,你可以根据自己存放数据的不同而去更改他]
直方图-最大值:
直方图中的数值都使用整数(直方图-组距除外)。
一般是使用规格-规格上限与数据统计-最大值中的最大值,[原创by无敌孔子=D3[红色的是根据自己存放数据的不同而去更改他]
直方图-最小值:
同上,使用数据统计-最小值。
使用公式=D4[红色的是根据自己存放数据的不同而去更改他]
直方图-区间:
使用直方图-最大值-直方图-最小值。
使用公式 =G2-G3 [红色的是根据自己存放数据的不同而去更改他]
直方图-直方图柱数:
直方图柱数就是咱们要制作直方图的柱体的数量,在excel中就是图形中长方柱的个数。
一般等于数据统计-数据个数开方再加上1.。
使用公式=SQRT(D2)+1[红色的是根据自己存放数据的不同而去更改他]
直方图-直方图组距:
组距就是excel直方图中每个柱体之间相距多少,一般是使用直方图-区间除以直方图-柱数。
使用公式==G4/(G5-1)[这里还需要减1是因为我们在计算直方图-直方图柱数的时候加了1.红色的是根据自己存放数据的不同而去更改他]
完成统计的结果如下所示:
B列
C列
D列
E列
F列
G列
I列
2行
数据统计
前面单元格使用公式为
直方图
前面单元格使用公式为
3行
数据个数
300
=COUNT(A:
A)
最大值
58
=D3
4行
最大值
57.90
=MAX(A:
A)
最小值
51
=D4
5行
最小值
50.60
=MIN(A:
A)
区间
7
=G2-G3
6行
平均值
54.10
=AVERAGE(A:
A)
直方图柱数
18
=SQRT(D2)+1
7行
标准偏差
1.15
=STDEV(A:
A)
直方图组距
0.42
=G4/(G5-1)
3、下一步就是我们平均分开组距、计算绘制直方图需要的频数、正太图需要的频率。
分组数据:
分组数据就是excel直方图中的分组区间,一般就是最小值加上组距,我们计算出共有18组,在分组数据中输入如下公式:
[原创by无敌孔子
10行
C列
D列
E列
11行
分组数据
直方图
12行
1
=G3
=FREQUENCY(A:
A,D12:
D29)
13行
2
=D12+$G$6
14行
3
=D13+$G$6
15行
4
=D14+$G$6
16行
5
=D15+$G$6
17行
6
=D16+$G$6
18行
7
=D17+$G$6
19行
8
=D18+$G$6
20行
9
=D19+$G$6
21行
10
=D20+$G$6
22行
11
=D21+$G$6
23行
12
=D22+$G$6
24行
13
=D23+$G$6
25行
14
=D24+$G$6
26行
15
=D25+$G$6
27行
16
=D26+$G$6
28行
17
=D27+$G$6
29行
18
=D28+$G$6
主要是填写第一个公式和第二个公式(注意H7单元格是绝对引用的),在第二个公式之后的单元格只需要鼠标挪动到第二个单元格(C13)右下方,鼠标变为实心十字的时候向下拖动就可以了。
[原创by无敌孔子
直方图:
在直方图的第一个单元格(D12)中输入以下公式=
)[红色为原数据区域,蓝色为我们的分组数据],记住,只在第一个单元格中输入数据,接下来的操作很关键,选中直方图的数据区域(仅仅是选中,不是复制公式!
!
从刚刚输入了数据的那个单元格开始向下拖动到E29),然后鼠标点击到编辑栏中,按下ctrl+shift+enter,频率就统计好了。
注意这时候光标在直方图的单元格中时,你会发现编辑框中显示公式被一个{}括起来,这表示这个公式应用到了一个数组中,这个是制作直方图的关键。
输入公式和输出的结果如下图:
10行
C列
D列
E列
11行
分组数据
直方图
12行
1
=G3
=FREQUENCY(A:
A,D12:
D29)
13行
2
=D12+$G$6
=FREQUENCY(A:
A,D12:
D29)
14行
3
=D13+$G$6
=FREQUENCY(A:
A,D12:
D29)
15行
4
=D14+$G$6
=FREQUENCY(A:
A,D12:
D29)
16行
5
=D15+$G$6
=FREQUENCY(A:
A,D12:
D29)
17行
6
=D16+$G$6
=FREQUENCY(A:
A,D12:
D29)
18行
7
=D17+$G$6
=FREQUENCY(A:
A,D12:
D29)
19行
8
=D18+$G$6
=FREQUENCY(A:
A,D12:
D29)
20行
9
=D19+$G$6
=FREQUENCY(A:
A,D12:
D29)
21行
10
=D20+$G$6
=FREQUENCY(A:
A,D12:
D29)
22行
11
=D21+$G$6
=FREQUENCY(A:
A,D12:
D29)
23行
12
=D22+$G$6
=FREQUENCY(A:
A,D12:
D29)
24行
13
=D23+$G$6
=FREQUENCY(A:
A,D12:
D29)
25行
14
=D24+$G$6
=FREQUENCY(A:
A,D12:
D29)
26行
15
=D25+$G$6
=FREQUENCY(A:
A,D12:
D29)
27行
16
=D26+$G$6
=FREQUENCY(A:
A,D12:
D29)
28行
17
=D27+$G$6
=FREQUENCY(A:
A,D12:
D29)
29行
18
=D28+$G$6
=FREQUENCY(A:
A,D12:
D29)
分组数据
直方图
1
51
1
2
51.02
0
3
51.44
0
4
51.86
1
5
52.29
6
6
52.71
21
7
53.13
45
8
53.55
43
9
53.97
39
10
54.39
34
11
54.81
36
12
55.24
21
13
55.66
16
14
56.08
18
15
56.50
10
16
56.92
6
17
57.34
0
18
57.76
2
正态图:
使用NORMDIST函数,也是一个数组函数,但是使用方法与上面直方图的公式有区别。
在正太图的第一个单元格,也就是F12单元格输入=NORMDIST(D12,$D$5,$D$6,0),第一个数据D12是分组数据的第一个数据,第二个数据$D$5是绝对引用了平均值,第三个数据$D$6是绝对引用了标准偏差,第四个填写0即可。
之后就在这个单元格直接按ctrl+shift+enter即可,不需要选中正态图这一列,也就是先用了数组。
之后我们将光标置于右下角,拖动复制公式到填充完毕正态图这一列。
[原创by无敌孔子
如下图所示:
10行
C列
D列
E列
F列
11行
分组数据
直方图
正态图
12行
1
=G3
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D12,$D$5,$D$6,0)
13行
2
=D12+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D13,$D$5,$D$6,0)
14行
3
=D13+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D14,$D$5,$D$6,0)
15行
4
=D14+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D15,$D$5,$D$6,0)
16行
5
=D15+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D16,$D$5,$D$6,0)
17行
6
=D16+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D17,$D$5,$D$6,0)
18行
7
=D17+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D18,$D$5,$D$6,0)
19行
8
=D18+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D19,$D$5,$D$6,0)
20行
9
=D19+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D20,$D$5,$D$6,0)
21行
10
=D20+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D21,$D$5,$D$6,0)
22行
11
=D21+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D22,$D$5,$D$6,0)
23行
12
=D22+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D23,$D$5,$D$6,0)
24行
13
=D23+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D24,$D$5,$D$6,0)
25行
14
=D24+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D25,$D$5,$D$6,0)
26行
15
=D25+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D26,$D$5,$D$6,0)
27行
16
=D26+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D27,$D$5,$D$6,0)
28行
17
=D27+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D28,$D$5,$D$6,0)
29行
18
=D28+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D29,$D$5,$D$6,0)
数据值如下:
分组数据
直方图
正态图
1
51
1
0.003467187
2
51.02
0
0.009839148
3
51.44
0
0.024424794
4
51.86
1
0.053039275
5
52.29
6
0.100752883
6
52.71
21
0.167421264
7
53.13
45
0.243364366
8
53.55
43
0.309454353
9
53.97
39
0.344214721
10
54.39
34
0.334931134
11
54.81
36
0.285085307
12
55.24
21
0.212269397
13
55.66
16
0.138258924
14
56.08
18
0.078775687
15
56.50
10
0.039263088
16
56.92
6
0.017118667
17
57.34
0
0.00652903
18
57.76
2
0.002178315
实际输入公式表格中应该是这样,上面的表格为了说清楚,加上了顶端的提示列和左侧的提示行。
分组数据
直方图
正态图
1
=G3
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D12,$D$5,$D$6,0)
2
=D12+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D13,$D$5,$D$6,0)
3
=D13+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D14,$D$5,$D$6,0)
4
=D14+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D15,$D$5,$D$6,0)
5
=D15+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D16,$D$5,$D$6,0)
6
=D16+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D17,$D$5,$D$6,0)
7
=D17+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D18,$D$5,$D$6,0)
8
=D18+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D19,$D$5,$D$6,0)
9
=D19+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D20,$D$5,$D$6,0)
10
=D20+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D21,$D$5,$D$6,0)
11
=D21+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D22,$D$5,$D$6,0)
12
=D22+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D23,$D$5,$D$6,0)
13
=D23+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D24,$D$5,$D$6,0)
14
=D24+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D25,$D$5,$D$6,0)
15
=D25+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D26,$D$5,$D$6,0)
16
=D26+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D27,$D$5,$D$6,0)
17
=D27+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D28,$D$5,$D$6,0)
18
=D28+$G$6
=FREQUENCY(A:
A,D12:
D29)
=NORMDIST(D29,$D$5,$D$6,0)
4、终于到了绘制图片的时候了
选中直方图数据(E11:
E29)区域,点击图表,选择柱形图,单击下一步(office2007单击柱形图会之后出来图形,你需要单击右键,单击选择数据,然后在分类x轴标志里面做以下的操作即可),在系列选项卡的分类X轴标志里面输入=sheet1!
D12:
D29[红色是你的工作表名字],点击完成。
图表的初步样式就出来了。
[原创by无敌孔子
优化图形:
双击直方图的柱子,在选项的选项卡中,分类间距设置为0,点击确定。
调整图标大小,直方图绘制完毕。
如下图所示:
正态图:
与直方图相同,但是图标类型要选择折线图就可以了.。
很晚了,先睡觉了,有空再讲如何将直方图和正态图做到一张表格中。
[原创by无敌孔子
[原创by无敌孔子
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel 如何 制作 直方图 正态图 教程
![提示](https://static.bdocx.com/images/bang_tan.gif)