Mac Excel 2011 - 具有正态分布的直方图

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 2 and 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?

但是......正常情况应该叠加在条形图上。我怎么能得到这个效果？

1 个解决方案

#1

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

The column B formulae are:

B列公式为：

Last Point =MAX(A2:A6)

最后一点= MAX（A2：A6）

Mean =SUMPRODUCT(B2:B6,A2:A6)/SUM(B2:B6)

平均值= SUMPRODUCT（B2：B6，A2：A6）/ SUM（B2：B6）

E(x^2f) =SUMPRODUCT(A2:A6^2,B2:B6)

E（x ^ 2f）= SUMPRODUCT（A2：A6 ^ 2，B2：B6）

E(xf)^2 =SUMPRODUCT(A2:A6,B2:B6)^2

E（xf）^ 2 = SUMPRODUCT（A2：A6，B2：B6）^ 2

E(f) =SUM(B2:B6)

E（f）= SUM（B2：B6）

Variance =B10-B11/B12

方差= B10-B11 / B12

StDev =SQRT(B13/(B12-1))

StDev = SQRT（B13 /（B12-1））

Part 2: Chart Trickery

Data table:

Column D is just an incremental counter. This will be the number of data points in the normal distribution curve.

D列只是一个增量计数器。这将是正态分布曲线中的数据点数。

E2 =D2/$B$8 etc.

E2 = D2 / $ B $ 8等

F2 =NORMDIST(E2,$B$9,$B$14,FALSE) etc.

F2 = NORMDIST（E2，$ B $ 9，$ B $ 14，FALSE）等

Chart:

Now, add Columns E:F to 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.
• 将系列更改为X-Y图。这可能需要对图表系列进行一些编辑，以强制单个系列使用您想要的X和Y值。
• Change the series to use the secondary axes (both X and Y).
• 更改系列以使用辅助轴（X和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.
• 将辅助X轴范围更改为0.5-5.5（即柱形图类别值两侧的0.5）。这将有效地对齐主X轴和辅X轴。
• Change the secondary Y-axis range to 0-1
• 将辅助Y轴范围更改为0-1
• Format the X-Y series appearance to taste (I suggest removing value markers).
• 格式化X-Y系列外观（我建议删除值标记）。

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 Peltier for innumerable charting inspirations over the years.

后记：感谢John Peltier多年来无数的图表灵感。