Excel VBA-平均工作表中的所有数字单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17178121/
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
Excel VBA- Average all numeric cells in a worksheet
提问by Parseltongue
I'm trying to create a macro that iterates over all used cells in a worksheet and return the average. The eventual goal is to get the average value of the numbers in each worksheet, and produce a line graph with the averages.
我正在尝试创建一个宏，它遍历工作表中所有使用的单元格并返回平均值。最终目标是获得每个工作表中数字的平均值，并生成带有平均值的折线图。
I'm having difficulty understanding how to do this. My strategy right now (which is probably sub-optimal) involves a) finding the first row with numeric data; b) finding the first column with numeric data; c) finding the last row with numeric data; d) finding the last column with numeric data; d) creating a range over those cells; e) averaging the range
我很难理解如何做到这一点。我现在的策略（这可能是次优的）涉及 a) 找到带有数字数据的第一行；b) 找到带有数字数据的第一列；c) 找到带有数字数据的最后一行；d) 找到带有数字数据的最后一列；d) 在这些单元格上创建一个范围；e) 平均范围
Here's my current code
这是我当前的代码
Sub AverageAllNumbers()
Dim fRow As Long
Dim fColumn As Long
Dim lRow As Long
Dim lColumn As Long
Dim dblAverage As Long
Dim averageRange As Range
fRow = Cells.Find(What:=Number, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
fColumn = Cells.Find(What:=Number, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
lRow = Cells.Find(What:=Number, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lColumn = Cells.Find(What:=Number, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
averageRange = Range(Cells(fRow, fColumn), Cells(lRow, lColumn))
dblAverage = Application.WorksheetFunction.Average(averageRange)
MsgBox dblAverage
End Sub
Almost nothing works-- 'lColumn' produces 16384, and fRow and fColumn produces 1, which is not even a used cell in my spreadsheet.
几乎没有任何效果——'lColumn' 产生 16384，而 fRow 和 fColumn 产生 1，这在我的电子表格中甚至不是一个使用过的单元格。
What's going on?
这是怎么回事？
回答by Cody Piersall
Have you tried using the Worksheet.UsedRangeProperty?
您是否尝试过使用该Worksheet.UsedRange属性？
Sub AverageAll()
Dim average As Double
Dim averageRange As Range
' Set averageRange to UsedRange; no need to find it ourselves.
Set averageRange = ActiveSheet.UsedRange
average = Application.WorksheetFunction.average(averageRange)
MsgBox average
End Sub
It worked for me in a test case, albeit a trivial one.
它在一个测试用例中对我有用，尽管是微不足道的。
回答by Gary's Student
Should be a one-liner:
应该是单行的：
Sub SheetAverage()
MsgBox Application.WorksheetFunction.Average(ActiveSheet.UsedRange)
End Sub
