Как усреднить значения в excel через vba?

В Excel вы можете использовать VBA для расчета средних значений из диапазона ячеек или нескольких диапазонов. И в этом уроке мы узнаем различные способы его использования.

Среднее значение в VBA с использованием WorksheetFunction

В VBA есть несколько функций , которые вы можете использовать, но специальной функции для этой цели не существует. Это не значит, что мы не можем взять среднее значение. В VBA есть свойство WorksheetFunction , которое помогает вызывать функции в коде VBA.

Усредним значения в диапазоне А1:А10.

1. Сначала введите свойство функции рабочего листа, затем выберите функцию СРЗНАЧ из списка.
   
2. Затем вы должны ввести начальную скобку, как при вводе функции в электронную таблицу.
   
3. После этого нам нужно использовать объект диапазона для ссылки на диапазон, для которого мы хотим вычислить среднее значение.
   
4. В конце введите закрывающую скобку и присвойте возвращаемое значение функции ячейке B1.
   

 Application.WorksheetFunction.Average( Range ("A1:A10"))

Теперь, когда вы запускаете этот код , он вычисляет среднее значение значений в диапазоне A1:A10 и вводит значение в ячейку B1.

Средние значения всего столбца или строки

В этом случае вам просто нужно указать строку или столбец вместо диапазона, который мы использовали в предыдущем примере.

 'for the entire column A Range ("B1") = Application.WorksheetFunction.Average( Range ("A:A")) 'for entire row 1 Range ("B1") = Application.WorksheetFunction.Average( Range ("1:1"))

Используйте VBA для усреднения значений выбора

Теперь предположим, что вы хотите вычислить среднее значение только для выбранных ячеек, поскольку вы можете использовать такой код.

 Sub vba_average_selection() Dim sRange As Range Dim iAverage As Long On Error GoTo errorHandler Set sRange = Selection iAverage = WorksheetFunction.Average(Range(sRange.Address)) MsgBox iAverage Exit Sub errorHandler: MsgBox "make sure to select a valid range of cells" End Sub

В приведенном выше коде мы использовали выборку, затем указали ее в переменной «sRange», затем использовали адрес этой переменной диапазона, чтобы получить среднее значение.

VBA усредняет все ячейки выше

Следующий код берет все ячейки выше и их средние значения и вводит результат в выбранную ячейку .

 Sub vba_auto_Average() Dim iFirst As String Dim iLast As String Dim iRange As Range On Error GoTo errorHandler iFirst = Selection. End (xlUp). End (xlUp).Address iLast = Selection. End (xlUp).Address Set iRange = Range(iFirst & ":" & iLast) ActiveCell = WorksheetFunction.Average(iRange) Exit Sub errorHandler: MsgBox "make sure to select a valid range of cells" End Sub

Усреднение динамического диапазона с использованием VBA

Аналогичным образом вы можете использовать динамический диапазон при использовании VBA для усреднения значений.

 Sub vba_dynamic_range_average() Dim iFirst As String Dim iLast As String Dim iRange As Range On Error GoTo errorHandler iFirst = Selection.Offset(1, 1).Address iLast = Selection.Offset(5, 5).Address Set iRange = Range(iFirst & ":" & iLast) ActiveCell = WorksheetFunction.Average(iRange) Exit Sub errorHandler: MsgBox "make sure to select a valid range of cells" End Sub

Усреднение динамического столбца или строки

Аналогично, если вы хотите использовать динамический столбец, вы можете использовать следующий код, который возьмет столбец активной ячейки и среднее значение всех имеющихся там значений.

 Sub vba_dynamic_column() Dim iCol As Long On Error GoTo errorHandler iCol = ActiveCell.Column MsgBox WorksheetFunction.Average(Columns(iCol)) Exit Sub errorHandler: MsgBox "make sure to select a valid range of cells" End Sub

И за скандал.

 Sub vba_dynamic_row() Dim iRow As Long On Error GoTo errorHandler iRow = ActiveCell.Row MsgBox WorksheetFunction.Average(Rows(iCol)) Exit Sub errorHandler: MsgBox "make sure to select a valid range of cells" End Sub