vba VBA中的简单直方图？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9004409/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Simple Histogram in VBA?
提问by Alaa Elwany
I have data stored in some column (Say, Column A). The length of Column A is not fixed (depends on previous steps in the code).
我将数据存储在某列（比如 A 列）中。A 列的长度不固定（取决于代码中前面的步骤）。
I need a histogram for the values in Column A, and have it in the same sheet. I need to take the values in column A, and automatically compute M Bins, then give the plot.
我需要 A 列中值的直方图，并将其放在同一张表中。我需要取 A 列中的值，并自动计算 M Bins，然后给出绘图。
I looked online for a "simple" code, but all codes are really fancy, with tons of details that I don't need, to the extent that I am not even able to use it. (I am a VBA beginner.)
我在网上寻找了一个“简单”的代码，但所有的代码都非常花哨，有很多我不需要的细节，以至于我什至无法使用它。（我是 VBA 初学者。）
I found the following code that seems to do the job, but I am having trouble even calling the function. Besides, it only does computations but does not make the plot.
我发现以下代码似乎可以完成这项工作，但我什至无法调用该函数。此外，它只做计算，不做绘图。
Sub Hist(M As Long, arr() As Single)
Dim i As Long, j As Long
Dim Length As Single
ReDim breaks(M) As Single
ReDim freq(M) As Single
For i = 1 To M
freq(i) = 0
Next i
Length = (arr(UBound(arr)) - arr(1)) / M
For i = 1 To M
breaks(i) = arr(1) + Length * i
Next i
For i = 1 To UBound(arr)
If (arr(i) <= breaks(1)) Then freq(1) = freq(1) + 1
If (arr(i) >= breaks(M - 1)) Then freq(M) = freq(M) + 1
For j = 2 To M - 1
If (arr(i) > breaks(j - 1) And arr(i) <= breaks(j)) Then freq(j) = freq(j) + 1
Next j
Next i
For i = 1 To M
Cells(i, 1) = breaks(i)
Cells(i, 2) = freq(i)
Next i
End Sub
And then I try to call it simply by:
然后我尝试简单地通过以下方式调用它：
Sub TestTrial()
Dim arr() As Variant
Dim M As Double
Dim N As Range
arr = Range("A1:A10").Value
M = 10
Hist(M, arr) ' This does not work. Gives me Error (= Expected)
End Sub
采纳答案by Alex K.
Not 100% sure as to the efficacy of that approach but;
不能 100% 确定该方法的有效性，但是；
- Remove the parens as your calling a sub;
Hist M, arr Mis declared asdoublebut received by the function as along; this won't work so declare it in the calling routine aslong- You will need to recieve
arr() As Variant Range -> Arrayproduces a 2 dimensional array so the elements arearr(1, 1) .. arr(n, 1)
- 在调用 sub 时删除括号；
Hist M, arr M被声明为double但被函数接收为long; 这将不起作用，因此在调用例程中将其声明为long- 你需要收到
arr() As Variant Range -> Array产生一个二维数组，所以元素是arr(1, 1) .. arr(n, 1)
回答by jahu
A little late but still I want to share my solution. I created a Histogramfunction which might be used as array formulain the excel spread sheet. Note: you must press
CTRL+SHIFT+ENTERto enter the formula into your workbook. Input is the range of values and the number M of bins for the histogram. The output range must have M rows and two columns. One column for the bin value and one column for the bin frequency.
有点晚了，但我仍然想分享我的解决方案。我创建了一个Histogram函数，可以在 Excel 电子表格中用作数组公式。注意：您必须按
CTRL+SHIFT+ENTER将公式输入到您的工作簿中。输入是值的范围和直方图的箱数 M。输出范围必须有 M 行和两列。一列用于 bin 值，一列用于 bin 频率。
Option Explicit
Option Base 1
Public Function Histogram(arr As Range, M As Long) As Variant
On Error GoTo ErrHandler
Dim val() As Variant
val = arr.Value
Dim i As Long, j As Integer
Dim Length As Single
ReDim breaks(M) As Single
ReDim freq(M) As Integer
Dim min As Single
min = WorksheetFunction.min(val)
Dim max As Single
max = WorksheetFunction.max(val)
Length = (max - min) / M
For i = 1 To M
breaks(i) = min + Length * i
freq(i) = 0
Next i
For i = 1 To UBound(val)
If IsNumeric(val(i, 1)) And Not IsEmpty(val(i, 1)) Then
If val(i, 1) > breaks(M) Then
freq(M) = freq(M) + 1
Else
j = Int((val(i, 1) - min) / Length) + 1
freq(j) = freq(j) + 1
End If
End If
Next i
Dim res() As Variant
ReDim res(M, 2)
For i = 1 To M
res(i, 1) = breaks(i)
res(i, 2) = freq(i)
Next i
Histogram = res
ErrHandler:
'Debug.Print Err.Description
End Function
