在Excel中生成bin大小的数组 [英] Generate array of bin sizes in Excel
问题描述
Microsoft Excel中的 FREQUENCY 功能可用于生成直方图。第一个参数是一组数据值(例如A1:A100)。第二个是一组容器大小。我想从三个参数
The FREQUENCY function in Microsoft Excel is useful for generating histograms. The first parameter is an array of data values (E.g. A1:A100). The second one is an array of bin sizes. I want to generate this array from three parameters:
- 最小数据值:MIN(A1:A100)
- 最大数据值:MAX(A1:A100)
- bin大小:常量
也就是说,公式/函数应该返回所需值的列表,而不是手动将它们放在单元格中。
That is, a formula/function should return the list of wanted values instead of manually putting them in cells.
例如考虑最小值为10,最大值为100,bin大小为20.这将产生一系列以下bin大小:10,30,50,70,90
E.g. Consider a minimum value of 10, a maximum value of 100 and a bin size of 20. This would yield an array of the follow bin sizes: 10, 30, 50, 70, 90
如何完成这个?谢谢。
推荐答案
如果您希望该公式生成的数组尝试这个
If you want that array generated by a formula try this
=(ROW(INDIRECT( 1: &安培; CEILING((MAX(A1:A100)-MIN(A1:A100))/ B1,1))) - 1)* B1 + MIN(A1:A100)
其中B1是您的bin大小常数,例如在FREQUENCY功能中,将
where B1 is your bin size constant, e.g. within the FREQUENCY function that would be
= FREQUENCY(A1:A100,(ROW(INDIRECT(1:& CEILING (A1:A100)-MIN(A1:A100))/ B1,1))) - 1)* B1 + MIN(A1:A100))
用CTRL + SHIFT + ENTER确认
confirmed with CTRL+SHIFT+ENTER
这篇关于在Excel中生成bin大小的数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!