直方图使用Excel FREQUENCY功能 [英] Histogram using Excel FREQUENCY function

问题描述

在Excel 2010中，列出了 A 中的值列表，并在 B1 。这使我可以使用以下公式创建带有N个仓的柱状图：

In Excel 2010, I have a list of values in column A and a bin size is specified in B1. This allows me to create histograms with N bins using this formula:

{= FREQUENCY（A：A，（ROW（INDIRECT &安培; CEILING（（MAX（A：A）-MIN（A：A））/ B1,1））） - 1）* B1 + MIN（A：A））}

唯一的问题是我需要选择 N个单元格，并应用此公式来获取N个bin用作我的条形图的数据源。是否可以跳过这一步？例如。可以在单个单元格中使用这个公式 - 有些修改 - 以便当作为数据源使用时，它被解释为N个单元格，产生一个N值非常好的直方图。

The only problem is that I need to select N cells and apply this formula to get N bins to be used as data source for my bar chart. Is it possible to skip this step? E.g. Is it possible to use this formula in a single cell - somewhat modified - so that when used as data source, it is interpreted as N cells, producing a nice histogram with N values?

谢谢。

这是答案，导致我上面的公式。

推荐答案

（这是相当不同的在宏观驱动的动态范围调整大小的事情的方法，所以我使用单独的答案...）

动态直方图可以是通过记住命名范围实际上被命名为公式，因此它们的值可能是动态的，在某些情况下极其重要。

A dynamic histogram chart can be built by remembering that "named ranges" are actually named formulas, so their values may be dynamic, extremely so in some cases.

让我们开始假设我们在列A中具有任意值的值，从第1行开始，并且还有另一个单元格包含我们在直方图中我们想要的bin数。在我的工作簿中恰好是E2。所以我们启动名称管理器（在公式选项卡上）并创建

Let's start with the assumption that we have an arbitrary set of values in column A, starting at row 1 and also that we have another cell that contains the number of bins we want in our histogram. In my workbook that happens to be E2. So we fire up the Name Manager (on the "Formulas" tab) and create

num_bins             =Sheet1!$E$2

我已经定义了一些bin，而不是bin大小（我们稍后将定义） ），因为后者使得巧妙地知道如何设置我们的bin边界：我们对于第一个和最后一个bin可能涵盖值范围的不同大小部分的想法感到满意，例如？*

I've gone for defining a number of bins, rather than a bin size (which we'll define later) because the latter makes it tricky to know exactly how to set our bin boundaries: are we happy with the idea that the first and last bins may cover different-sized parts of the range of values, for example?*

我们还可以设置动态公式来描述我们的数据：

We can also set up dynamic formulas to describe our data:

data_count           =COUNT(Sheet1!$A:$A)
data_vals            =OFFSET(Sheet1!$A$1,0,0,data_count,1)
max_val              =MAX(data_vals)
min_val              =MIN(data_vals)

有了这些定义，我们可以看到。每个垃圾桶应该有多大？另一个命名公式：

With those defined, we can get fancy. How big should each bin be? Make another named formula:

bin_size             =(max_val-min_val)/(num_bins)

这里是科学：这些公式使动态数组：

And here comes the science: these formulas make the dynamic arrays:

bin_array            =min_val+ROW(OFFSET(Sheet1!$A$1,0,0,num_bins-1,1))*bin_size
bin_labels           =min_val+ROW(OFFSET(Sheet1!$A$1,0,0,num_bins,1))*bin_size        
data_vals            =FREQUENCY(data_vals,bin_array)

第一个是更棘手的：它使用 num_bins减一个 -size范围的行号生成多个 bin_size 。它不会在 min_val 中启动数组，因为 FREQUENCY（）函数计算每个bin值的项。它比想要的数量小一个，因为函数产生一个更大的数组，其中最后一个条目具有最高的bin数。所以我们为了演示目的而单独制作一个 bin_labels 数组。

The first one is the trickier: it uses the row numbers of a num_bins minus one-size range to generate multiple of bin_size. It doesn't start the array at min_val because the FREQUENCY() function counts items up to each bin value. It's one smaller than the number of bins desired because the function produces an array one larger, where the final entry has the points above the highest bin number. So we make a separate bin_labels array for presentation purposes.

现在我们可以做一个图表。插入（说）一个2-D柱形图并打开选择数据对话框（从功能区或右键单击图表）。添加一个新的系列，将系列值设置为 = Sheet1！freq_array 。有必要包括工作表名称或工作簿名称以使其正常工作。如果您喜欢，请添加一个系列名称，然后单击确定。现在点击水平（类别）轴标签的编辑，并将范围设置为 = Sheet1！bin_labels 。

Now we can make a chart. Insert a (say) a 2-D column chart and open the "Select Data" dialog (either from the ribbon or right-clicking the chart). Add a new series, setting Series values to =Sheet1!freq_array. It's necessary to include either the sheet name or the workbook name to get this to work. Add a series name if you like and click "OK". Now click "Edit" for "Horizontal (Category) Axis Labels" and set the range to =Sheet1!bin_labels.

这里有2000个单元格，其中 = RAND（）* 5 和5个bin（我列出了名称及其公式，其值不在数组中）

Here's 2000 cells with =RAND()*5 and 5 bins (I listed the names and their formulas, with values where they don't produce arrays)

同样的表格将num_bins更改为10.（$ code> RAND（）公式重新计算，所以这个箱子可能不会加上完全相同的值）

And the same sheet after changing num_bins to 10. (The RAND() formulas recalculated, so the bins may not add up to exactly the same values)

• （如果您必须具有用户定义的bin大小， '需要使用 bin_size 表单引用，并使用命名公式计算 num_bins

• (if you must have a user-defined bin size, you'll need to make bin_size the sheet reference and calculate num_bins with a named formula)

这篇关于直方图使用Excel FREQUENCY功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！