excel - 使用 Excel FREQUENCY 函数绘制直方图

Question

在 Excel 2010 中，A 列中有一个值列表，并且在 B1 中指定了 bin 大小。这允许我使用以下公式创建具有 N 个箱的直方图:

{=频率(A:A,(ROW(INDIRECT("1:"&CEILING((MAX(A:A)-MIN(A:A))/B1,1)))-1) *B1+MIN(A:A))}

唯一的问题是我需要选择 N 个单元格并应用此公式来获取 N 个数据桶用作条形图的数据源。是否可以跳过这一步？例如。是否可以在单个单元格中使用此公式(稍作修改)，以便在用作数据源时，将其解释为 N 个单元格，从而生成具有 N 个值的漂亮直方图？

谢谢。

Here's the answer that led me to the formula above.

Answer 1

(这与宏驱动的动态范围调整大小的方法相当不同，所以我使用单独的答案......)

可以通过记住“命名范围”实际上命名为公式来构建动态直方图，因此它们的值可能是动态的，在某些情况下尤其如此。

首先假设我们在 A 列中有一组任意值，从第 1 行开始，并且我们还有另一个单元格，其中包含我们想要在直方图中显示的 bin 数量。在我的工作簿中，恰好是 E2。因此，我们启动名称管理器(在“公式”选项卡上)并创建

num_bins             =Sheet1!$E$2

我已经定义了垃圾箱的数量，而不是垃圾箱的大小(我们稍后将定义)，因为后者使得准确了解如何设置垃圾箱边界变得很棘手:我们对这样的想法感到满意吗？例如，第一个和最后一个容器可能覆盖值范围的不同大小部分？*

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

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)

有了这些定义，我们就可以开始想象了。每个垃圾箱应该有多大？制作另一个命名公式:

bin_size             =(max_val-min_val)/(num_bins)

科学来了:这些公式构成动态数组:

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 减去 1 大小范围的行号来生成 bin_size 的倍数。它不会在 min_val 处启动数组，因为 FREQUENCY() 函数对每个 bin 值为止的项目进行计数。它比所需的 bin 数量小一，因为该函数生成一个大一的数组，其中最终条目的点高于最高的 bin 编号。因此，我们创建一个单独的 bin_labels 数组用于演示目的。

现在我们可以制作图表了。插入(例如)二维柱形图，然后打开“选择数据”对话框(从功能区或右键单击图表)。添加新系列，将系列值设置为 =Sheet1!freq_array。必须包含工作表名称或工作簿名称才能使其正常工作。如果您愿意，请添加系列名称，然后单击“确定”。现在，单击“水平(类别)轴标签”的“编辑”，并将范围设置为 =Sheet1!bin_labels。

这里有 2000 个带有 =RAND()*5 的单元格和 5 个容器(我列出了名称及其公式，以及不生成数组的值)

将 num_bins 更改为 10 后的同一张表。(重新计算了 RAND() 公式，因此 bin 加起来可能不会达到完全相同的值)

• (如果您必须有用户定义的 bin 大小，则需要将 bin_size 设为工作表引用并使用命名公式计算 num_bins)