vba 一组数据的平均值vba excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26926902/
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
average of an array of data vba excel
提问by danuca9
I'm new in this stuff of programming. I'have been searching for this topic and I can't find anything that fits to my problem.
我是编程这方面的新手。我一直在寻找这个主题，但找不到适合我的问题的任何内容。
I have an array of data with measures. I have 31 lines and 96 columns. I want that my VBA code finds the average of each column of data and displays it on the line after the last value of the column.
我有一系列带有度量的数据。我有 31 行和 96 列。我希望我的 VBA 代码找到每列数据的平均值，并将其显示在列的最后一个值之后的行上。
Can anybody help me?
有谁能够帮助我？
Thanks in advanced.
提前致谢。
回答by jaysoncopes
The easiest answer I've found is to use the following:
我找到的最简单的答案是使用以下内容：
Application.WorksheetFunction.Average(dataArray)
Application.WorksheetFunction.Average(dataArray)
This is (hypothetically) the same as using the average function in a worksheet. The only downside seems to be that you have to use an array or some other list of data points. This is easily remedied with the following:
这（假设）与在工作表中使用平均函数相同。唯一的缺点似乎是您必须使用数组或其他一些数据点列表。这可以通过以下方式轻松解决：
Function getArray(dataRange As Range) As Variant()
Dim arr(dataRange.Rows.Count, dataRange.Columns.Count) as Variant
Dim i as Integer, j as Integer
For i = 1 to dataRange.Rows.Count
For j = 1 to dataRange.Columns.Count
arr(i, j) = dataRange(i, j)
Next
Next
getArray = arr
End Function
This will convert the range into an array and will be accessible through the Averagefunction. For instance:
这会将范围转换为数组，并可通过该Average函数访问。例如：
myAverage = Application.WorksheetFunction.Average(getArray(Range("C1:CT56")))
myAverage = Application.WorksheetFunction.Average(getArray(Range("C1:CT56")))
回答by DaveG
You can enter a formula in the line below the data:
您可以在数据下方的行中输入公式：
=average(A1:A96)
Isn't that easier than using VBA for this purpose?
这不是比为此目的使用 VBA 更容易吗？
If you really want to use VBA:
如果你真的想使用 VBA：
Sub ownaverage()
Dim totalsum As Double
Dim totalnum As Double
Dim ownav As Double
totalsum = 0
totalnum = 0
For Each c In Worksheets("Sheet1").Range("D17:E17").Cells
totalsum = totalsum + c.Value
totalnum = totalnum + 1
Next
ownav = totalsum / totalnum
ownaverage = ownav
Range("I27").Select
ActiveCell.FormulaR1C1 = ownaverage()
End Sub
Reference for loops: http://msdn.microsoft.com/en-us/library/office/aa221353(v=office.11).aspx
循环参考：http: //msdn.microsoft.com/en-us/library/office/aa221353(v= office.11).aspx
回答by danuca9
Thanks again for the answer. I was able to figure it out by myself. The code I wrote is:
再次感谢您的回答。我自己弄明白了。我写的代码是：
Private Sub CommandButton2_Click()
Dim soma as Double
Dim media as Double
soma=0
media=0
totalnum=0
With Sheets("sheet1")
For j= 1 to 96
For i =28 to 58
Set target = Cells (i,j)
soma=soma+targe
totalnum=totalnum+1
Next i
Cells(i+1,j).Value = soma/totalnum
soma=0
totalnum=0
Next j
End With
End sub
I hope this code help someone with the same doubts as I had.
我希望这段代码能帮助和我有同样疑问的人。
Thanks again DaveG
再次感谢戴夫
