本文被阅读次了
2021-09-16 星期四 / Excel,直方图,正态分布 /
Excel中如何制作直方图与正态分布图
图文介绍
经常性的有在excel中做直方图、正态分布图的需求，分享一下如何制作直方图和正态分布图，大家根据不同的数据照着做就可以了。
直方图和正态分布图是分不开的，excel中直方图出来了，正态分布图也就差不多了。先来看如何制作直方图，所有数据放在了A列，然后我们需要统计以下数据：
1、使用的原数据如附件所示
2、我们需要统计的数据如下：
来详细说明一下每个字段如何得到： 规格-规格中心：这个是根据你的产品规格或者你所要统计数据的理论值的
平均值，与原数据无关，如果你要制作与原数据相关的直方图，那么这个就是需要的。我们一般是自己统计原数据的平均值，最大值，最小值来做直方图以及正态分布图的。所以规格中心、规格上限、规格下限得看你自己要求作出哪种直方图or正态分布图来定，你要是使用规格值，那么最大值，最小值，平均值就可以不必统计。
我们这里使用原数据统计的平均值、最大值、最小值来做。 规格-规格上限：同上解释 规格-规格下限：同上解释 数据统计-数据个数：统计我们放在A列中的数值到底有多少个，使用公式=counta(A:A)[红色的是你的数据列，你可以根据自己存放数据的不同而去更改他] 数据统计-最大值：统计数据的最大值，使用公式 =MAX(A:A)[红色的是你的数据列，你可以根据自己存放数据的不同而去更改他] 数据统计-最小值：统计数据的最小值，使用公式 =MIN(A:A)[红色的是你的数据列，你可以根据自己存放数据的不同而去更改他] 数据统计-平均值：数据的平均值，使用公式 =AVERAGE(A:A)[红色的是你的数据列，你可以根据自己存放数据的不同而去更改他] 数据统计-标准偏差：统计数据的标准偏差，使用公式 =STDEV(A:A)[红色的是你的数据列，你可以根据自己存放数据的不同而去更改他] 直方图-最大值：直方图中的数值都使用整数（直方图-组距除外）。一般是使用规格-规格上限与数据统计-最大值中的最大值，我们这里直接使用数据统计-最大值即可。使用公式 = 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组，在分组数据中输入如下公式：
| 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）右下方，鼠标变为实心十字的时候向下拖动就可以了。
直方图：在直方图的第一个单元格(D12)中输入以下公式==FREQUENCY(A:A,D12:D29)[红色为原数据区域，蓝色为我们的分组数据]，记住，只在第一个单元格中输入数据，接下来的操作很关键，选中直方图的数据区域（仅仅是选中，不是复制公式！！从刚刚输入了数据的那
个单元格开始向下拖动到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即可，不需要选中正态图这一列，也就是先用了数组。之后我们将光标置于右下角，拖动复制公式到填充完毕正态图这一列。
如下图所示：
| 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[红色是你的工作表名字]，点击完成。图表的初步样式就出来了。
优化图形：双击直方图的柱子，在选项的选项卡中，分类间距设置为0，点击确定。调整图标大小，直方图绘制完毕。如下图所示：
正态图：与直方图相同，但是图标类型要选择折线图就可以了。
【完】
本站整理
