Excel vba 计算一列的平均值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14887210/
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 calculate average of a column
提问by Fang Li
I want to calculate average of a column and put the value below.
I wrote this code in VBA but returned value is always 0.
我想计算一列的平均值并将值放在下面。
我在 VBA 中编写了这段代码，但返回值始终为 0。
Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
Dim sum As Integer
Dim count As Integer
count = 0
sum = 0
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Activate
sum = sum + ActiveCell.Value
count = count + 1
Loop
ActiveCell.Value = sum / count
ActiveCell.Offset(0, 5).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
End Sub
回答by CuberChase
As @Tahbaza points out, unless your ActiveCellis at the top of the row, it will only count from the row in the column were the active cell is.
正如@Tahbaza 指出的那样，除非您ActiveCell位于行的顶部，否则它只会从活动单元格所在的列中的行开始计数。
Your code also has an error in that it won't count the active cell in the first iteration of the loop so this value will be missed from the average.
您的代码也有一个错误，因为它不会在循环的第一次迭代中计算活动单元格，因此该值将从平均值中丢失。
I've fixed the two problems in the following set of code, however there would be other improvements (ie not using Selectat all) if not for the suggestion following.
我已经在下面的一组代码中修复了两个问题，但是Select如果不是下面的建议，还会有其他改进（即根本不使用）。
Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
Dim sum As Integer
Dim count As Integer
count = 0
sum = 0
Selection.End(xlUp).Select '<---Go to the top of the range
Do While ActiveCell.Value <> ""
sum = sum + ActiveCell.Value
count = count + 1
ActiveCell.Offset(1, 0).Activate '<---Move down *after* you done the sumcount
Loop
ActiveCell.Value = sum / count
ActiveCell.Offset(0, 5).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
End Sub
You can achieve your desired outcome with a single line of code as follows. This assumes the cells you wish to have averaged are continuous and there are no empty cells. It also assumes your values start in this first row of the worksheet and there has to be at least two rows of values.
您可以使用如下一行代码实现您想要的结果。这假设您希望平均的单元格是连续的，并且没有空单元格。它还假设您的值从工作表的第一行开始，并且必须至少有两行值。
Sub ColumnAverage()
Cells(1, ActiveCell.Column).End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Average(ActiveSheet.Columns(ActiveCell.Column))
End Sub
Edit: In fact if you want don't need a static value but can use the worksheet function formula in the last cell of the column. Its a bit of a beast but if you break it down it should make sense:
编辑：事实上，如果您不需要静态值，但可以在列的最后一个单元格中使用工作表函数公式。它有点像野兽，但如果你分解它应该是有道理的：
Sub ColumnAverageFormula()
Cells(1, ActiveCell.Column).End(xlDown).Offset(1, 0).Formula = "=Average(" & Cells(1, ActiveCell.Column).Address(0, 0) & ":" & Cells(1, ActiveCell.Column).End(xlDown).Address(0, 0) & ")"
End Sub
