macos Mac Excel 2011 - 正态分布的直方图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11545505/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mac Excel 2011 - Histogram with normal distribution
提问by CodeGuy
Let's say I have a list of values and I have already chunked them into groups to make a histogram.
假设我有一个值列表，并且我已经将它们分组以制作直方图。
Since Excel doesn't have histograms, I made a bar plot using the groups I developed. Specifically, I have the frequencies 2 6 12 10 2and it produces the bar plot you see below.
由于 Excel 没有直方图，我使用我开发的组制作了一个条形图。具体来说，我有频率2 6 12 10 2，它会生成您在下面看到的条形图。
Next, I want to add a normal distribution (line plot) with a mean of 0.136 and standard deviation of 0.497 on top of this histogram. How can I do this in excel? I need the axis to line up such that it takes up the width of the bar plot. Otherwise, you get something like I've attached.
接下来，我想在此直方图的顶部添加一个均值为 0.136 且标准差为 0.497 的正态分布（线图）。我怎样才能在excel中做到这一点？我需要将轴对齐，使其占据条形图的宽度。否则，你会得到像我附加的东西。
But...the normal should be overlayed on the bar plot. How can I get this effect?
但是……法线应该叠加在条形图上。我怎样才能得到这个效果？
回答by andy holaday
There are two main part to this answer:
这个答案有两个主要部分：
First, I reverse-engineered the grouped data to come up with an appropriate mean and standard deviation on this scale.
首先，我对分组数据进行逆向工程，以在此范围内得出适当的均值和标准差。
Second, I employed some chart trickery to make the normal distribution curve look right when superimposed on the column chart. I used Excel 2007 for this; hopefully you have the same options available in your version.
其次，我使用了一些图表技巧，使正态分布曲线叠加在柱形图上时看起来很正确。为此，我使用了 Excel 2007；希望您的版本中具有相同的选项。
Part 1: Reverse-Engineer
第 1 部分：逆向工程
The column B formulae are:
B列公式为：
Last Point =MAX(A2:A6)
Mean=SUMPRODUCT(B2:B6,A2:A6)/SUM(B2:B6)
E(x^2f) =SUMPRODUCT(A2:A6^2,B2:B6)
E(xf)^2 =SUMPRODUCT(A2:A6,B2:B6)^2
E(f) =SUM(B2:B6)
Variance =B10-B11/B12
StDev=SQRT(B13/(B12-1))
最后一点 =MAX(A2:A6)
平均值=SUMPRODUCT(B2:B6,A2:A6)/SUM(B2:B6)
E(x^2f) =SUMPRODUCT(A2:A6^2,B2:B6)
E(xf)^2 =SUMPRODUCT(A2:A6,B2:B6)^2
E(f) =SUM(B2:B6)
方差=B10-B11/B12
标准差=SQRT(B13/(B12-1))
Part 2: Chart Trickery
第 2 部分：图表技巧
Data table:
数据表：
Column Dis just an incremental counter. This will be the number of data points in the normal distribution curve.
Column D只是一个增量计数器。这将是正态分布曲线中的数据点数。
E2 =D2/$B$8 etc.
F2 =NORMDIST(E2,$B$9,$B$14,FALSE) etc.
E2 = D2/$B$8 等等。
F2 =NORMDIST(E2,$B$9,$B$14,FALSE) 等等。
Chart:
图表：
Now, add Columns E:Fto the chart. You will need to massage a few things:
现在，将列添加E:F到图表中。你需要按摩几件事：
- Change the series to be an X-Y plot. This might require some editing of the chart series to force a single series to use your desired X and Y values.
- Change the series to use the secondary axes (both X and Y).
- Change the secondary X-axis range to 0.5-5.5 (i.e., 0.5 on either side of the column chart category values). This will effectively align the primary and secondary X-axes.
- Change the secondary Y-axis range to 0-1
- Format the X-Y series appearance to taste (I suggest removing value markers).
- 将系列更改为 XY 图。这可能需要对图表系列进行一些编辑，以强制单个系列使用您想要的 X 和 Y 值。
- 更改系列以使用辅助轴（X 和 Y）。
- 将辅助 X 轴范围更改为 0.5-5.5（即，柱形图类别值两侧的 0.5）。这将有效地对齐主要和次要 X 轴。
- 将辅助 Y 轴范围更改为 0-1
- 根据口味格式化 XY 系列外观（我建议删除值标记）。
The result so far:
到目前为止的结果：
Lastly, you can remove the tick marks and labels on the secondary axes to clean up the look.
最后，您可以删除辅助轴上的刻度线和标签以清理外观。
Postscript: Thanks to John Peltierfor innumerable charting inspirations over the years.
后记：感谢John Peltier多年来无数的图表灵感。
