本文被阅读次了

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,点击确定。调整图标大小,直方图绘制完毕。如下图所示:

    正态图:与直方图相同,但是图标类型要选择折线图就可以了。

    【完】

    租赁视界新闻中心责任编辑:Vélo
    免责声明:本站行业新闻资讯栏目所提供的内容有部分来源于网络,版权归原作者。 并对这些信息的准确性和完整性不作任何保证!所转载的文章出于传递更多信息之目的, 并不意味着赞同其观点或证实其描述。 站内提供的部分文章和图片资源或是网上搜集或是由网友提供, 若无意中侵犯了哪个媒体、公司或个人的知识产权或权益,敬请来信来电通知我们!本站将在3个工作日内删除!

    What Are Data Visualization Style Guidelines?

    Examples from the Google and London City Intelligence style guidesOrganizations like Google and Lond...