在Excel中创建一个直方图.docx

1、在Excel中创建一个直方图在Excel中创建一个直方图 生成随机数摘要统计创建直方图是做统计分析的重要组成部分，因为它提供了数据的可视化表示。这蒙地卡罗模拟的例子在第3部分中，我们反复地跑了一个随机的销售预测模型，结束了我们的单响应变量，利润有5000个可能的值（观察）。如果你还没有的话，下载的销售预测实例试算表。最后一步是分析结果，计算出有多少的利润可能会根据我们的不确定性在我们的模型作为输入值的不同而有所差异。我们将首先在Excel中创建一个直方图。下图显示了最终的结果。请继续阅读下面的内容，了解如何使直方图。图1：在Excel中的直方图响应变量的利润，使用条形图。（从蒙地卡罗模拟使用n

2、 = 5000点和40箱）。我们可以收集大量的信息，从该直方图： 它看起来像利润将是积极的，大部分的时间。 的不确定性是相当大的，在-1000到3400之间不等。 看起来并不像一个完美的正态分布的分布。 似乎不存在是异常值，截断，多种模式，等直方图讲述了一个好故事，但在许多情况下，我们要估计的概率是低于或高于一定的价值，或在一组的规格限制。直接跳到下一个步骤在我们的分析中，将汇总统计数据，或继续阅读下面的内容，学习如何在Excel中创建直方图。生成随机数摘要统计在Excel中创建一个直方图方法1：使用直方图工具，分析工具白。这可能是最简单的方法，但你必须重新运行该工具，每个给你做一个新的模拟。

3、，你仍然需要创建一个数组箱（将在下面讨论）。方法2：在Excel中使用频率功能。这是在电子表格中的销售预测的示例中所使用的方法。我喜欢这种方法的原因之一是，你可以使直方图动态的，也就是说，每次您重新运行的MC模拟，图表将自动更新。这是你如何做到这一点：步骤1：创建一个数组箱下图显示了如何轻松地创建动态数组箱。用于创建N个均匀间隔的数字的阵列，这是一个基本的技术。要创建动态数组，输入下列公式：B6= $ B $ 2B7= B6 +（$ B $ 3 - $ B $ 2）/ 5然后，复制单元格B7下降到B11图2：一个动态数组的5箱。你创建的阵列箱后，你可以继续使用直方图的工具，或者您也可以继续进行

4、下一个步骤。步骤2：使用Excel中的频率计算公式下图是一个屏幕截图，例如Monte Carlo模拟。我不打算详细解释FREQUENCY函数，因为你可以看它在Excel的帮助文件。但是，有一点要记住的是，它是一个数组函数，并输入公式后，你将需要按Ctrl + Shift + Enter键。需要注意的是仿真结果（利润）是在G列中，有5000个数据点（点：J5 = COUNT（G：G）。数列的公式：FREQUENCY（data_array中，bins_array）a）选择单元格J8：J48b）输入数组公式：= FREQUENCY（G：G，I8：I48）C）按Ctrl + Shift + Enter

5、组合图3：在Excel中创建一个动态缩放柱状图布局。创建缩放直方图的如果你想比较的概率分布直方图，你将需要扩展，使直方图曲线下的面积等于1的概率分布的特性之一。的直方图通常包括落入各bin中的数据点，在y-轴的计数，但缩放后，将y轴的频率（一个不那么易于解释的数目，可以在所有实用性不用担心）。频率并不代表概率！要扩展的直方图，可以使用下面的方法：标度=（计数/点）/（BinSize的）A）K8 =（J8 / $ J $ 5）/（$ I $ 9 - $ I8美元）b）复制细胞K8至K48c）按F9强制重新计算（可能需要一段时间）第3步：创建柱状图条形图，折线图或面积图：创建直方图，只需创建一个条

6、形图使用回收箱的标签和计数或比例列的值列。提示：要减少的酒吧之间的间距，用鼠标右键单击酒吧，然后选择“数据系列格式”。 .“。然后进入“选项”选项卡上，缩小差距。上面的图1这种方式创建的。更灵活的柱状图用条形图和面积图，其中一个问题是，在x-轴的数目只是标签。这可以使很难覆盖的数据使用不同数量的点或箱是各不相同的大小时，显示的适度规模。但是，您可以使用散点图创建一个直方图。后行箱的x值和计数或Y值的比例列列，Y误差线的线向下延伸到x轴（占100）。您可以右键单击这些错误条改变线的宽度，颜色等。图4：直方图使用散点图和错误酒吧。Creating a Histogram in Excel Gene

7、rating Random NumbersSummary StatisticsCreating a histogramis an essential part of doing a statistical analysis because it provides a visual representation of data.In Part 3 of this Monte Carlo Simulation example, we iteratively ran a stochasticsales forecast modelto end up with 5000 possible values

8、 (observations) for our single response variable,profit. If you havent already, download theSales Forecast Example Spreadsheet.The last step is toanalyze the resultsto figure out how much the profit might be expected to vary based on our uncertainty in the values used as inputs for our model. We wil

9、l start off by creating ahistogram in Excel. The image below shows the end result. Keep reading below to learn how to make the histogram.Figure 1:A Histogram in Excel for the response variableProfit, created using a Bar Chart.(From a Monte Carlo simulation usingn= 5000 points and 40 bins).We can gle

10、an a lot of information from this histogram: It looks like profit will be positive,mostof the time. The uncertainty is quite large, varying between -1000 to 3400. The distribution does not look like a perfect Normal distribution. There doesnt appear to be outliers, truncation, multiple modes, etc.Th

11、ehistogramtells a good story, but in many cases, we want to estimate theprobabilityof being below or above some value, or between a set of specification limits. To skip ahead to the next step in our analysis, move on to Summary Statistics, or continue reading below to learn how to create the histogr

12、am in Excel.Generating Random NumbersSummary StatisticsCreating a Histogram in ExcelMethod 1:Using the Histogram Tool in the Analysis Tool-Pak.This is probably the easiest method, but you have to re-run the tool each to you do a new simulation. AND, you still need to create an array of bins (which w

13、ill be discussed below).Method 2:Using the FREQUENCY function in Excel.This is the method used in the spreadsheet for the sales forecast example. One of the reasons I like this method is that you can make the histogram dynamic, meaning that every time you re-run the MC simulation, the chart will aut

14、omatically update. This is how you do it:Step 1:Create an array ofbinsThe figure below shows how to easily create a dynamic array of bins. This is a basic technique for creating an array of N evenly spaced numbers.To create the dynamic array, enter the following formulas:B6= $B$2B7= B6+($B$3-$B$2)/5

15、Then, copy cell B7 down to B11Figure 2:A dynamic array of 5 bins.After you create the array of bins, you can go ahead and use the Histogram tool, or you can proceed with the next step.Step 2:Use ExcelsFREQUENCYformulaThe next figure is a screen shot from the example Monte Carlo simulation. Im not go

16、ing to explain the FREQUENCY function in detail since you can look it up in the Excels help file. But, one thing to remember is that it is an array function, and after you enter the formula, you will need to press Ctrl+Shift+Enter. Note that the simulation results (Profit) are in columnGand there ar

17、e 5000 data points ( Points:J5=COUNT(G:G).The Formula for theCountcolumn:FREQUENCY(data_array,bins_array)a) Select cells J8:J48b) Enter the array formula: =FREQUENCY(G:G,I8:I48)c) Press Ctrl+Shift+EnterFigure 3:Layout in Excel for Creating a Dynamic Scaled Histogram.Creating a Scaled HistogramIf you

18、 want to compare your histogram with a probability distribution, you will need to scale the histogram so that thearea under the curve is equal to 1(one of the properties of probability distributions). Histograms normally include thecountof the data points that fall into each bin on the y-axis, but a

19、fter scaling, the y-axis will be thefrequency(a not-so-easy-to-interpret number that in all practicality you can just not worry about).The frequency doesnt represent probability!To scale the histogram, use the following method:Scaled = (Count/Points) / (BinSize)a) K8 = (J8/$J$5)/($I$9-$I$8)b) Copy c

20、ell K8 down to K48c) Press F9 to force a recalculation (may take a while)Step 3:Create theHistogramChartBar Chart, Line Chart, or Area Chart:To create the histogram, just create a bar chart using theBinscolumn for theLabelsand theCount or Scaledcolumn as theValues.Tip:To reduce the spacing between t

21、he bars, right-click on the bars and select Format Data Series. Then go to theOptionstab and reduce theGap. Figure 1 above was created this way.A More Flexible Histogram ChartOne of the problems with using bar charts and area charts is that the numbers on the x-axis are justlabels. This can make it

22、very difficult to overlay data that uses a different number of points or to show the proper scale when bins are not all the same size. However, you CAN use ascatter plotto create a histogram. After creating a line using theBinscolumn for theX ValuesandCount or Scaledcolumn for theY Values, addY Error Barsto the line that extend down to the x-axis (by setting thePercentageto 100%). You can right-click on these error bars to change the line widths, color, etc.Figure 4:Example Histogram Created Using a Scatter Plot and Error Bars.