Excel VBA计算列的平均值

本文介绍了Excel VBA计算列的平均值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想计算一列的平均值并将其值放在下面. 我在VBA中编写了此代码，但返回值始终为0.

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.

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

推荐答案

正如@Tahbaza指出的那样，除非您的ActiveCell位于行的顶部，否则它将仅从该列中处于活动单元格的行开始计数是.

As @Tahbaza points out, unless your ActiveCell is at the top of the row, it will only count from the row in the column were the active cell is.

您的代码还存在一个错误，因为它不会在循环的第一次迭代中对活动单元进行计数，因此该值将从平均值中丢失.

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.

我已经修复了以下代码集中的两个问题，但是如果不遵循以下建议，还会有其他改进(即完全不使用Select).

I've fixed the two problems in the following set of code, however there would be other improvements (ie not using Select at all) if not for the suggestion following.

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

实际上，如果您不需要静态值，但可以在列的最后一个单元格中使用工作表函数公式.它有点像野兽，但如果将其分解，它应该是有道理的:

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