Как найти среднее значение в массиве vba

In this Article

  • AVERAGE WorksheetFunction
  • Assign AVERAGE Result to a Variable
  • AVERAGE with a Range Object
  • AVERAGE Multiple Range Objects
  • Using AVERAGEA
  • Using AVERAGEIF
  • Disadvantages of WorksheetFunction
    • Using the Formula Method
    • Using the FormulaR1C1 Method

This tutorial will demonstrate how to use the Excel Average function in VBA.

The Excel AVERAGE Function is used to calculate an average from a range cells in your Worksheet that have values in them. In VBA, It is accessed using the WorksheetFunction method.

AVERAGE WorksheetFunction

The WorksheetFunction object can be used to call most of the Excel functions that are available within the Insert Function dialog box in Excel. The AVERAGE function is one of them.

Sub TestFunction
  Range("D33") = Application.WorksheetFunction.Average("D1:D32")
End Sub

vba average syntax

You are able to have up to 30 arguments in the AVERAGE  function. Each of the arguments must refer to a range of cells.

This example below will produce the average of the sum of the cells B11 to N11

Sub TestAverage()
   Range("O11") = Application.WorksheetFunction.Average(Range("B11:N11"))
End Sub

The example below will produce an average of the sum of the cells in B11 to N11 and the sum of the cells in B12:N12. If you do not type the Application object, it will be assumed.

Sub TestAverage()
  Range("O11") = WorksheetFunction.Average(Range("B11:N11"),Range("B12:N12")) 
End Sub

Assign AVERAGE Result to a Variable

You may want to use the result of your formula elsewhere in code rather than writing it directly back to an Excel Range. If this is the case, you can assign the result to a variable to use later in your code.

Sub AssignAverage()
   Dim result As Integer
'Assign the variable   
   result = WorksheetFunction.Average(Range("A10:N10"))
'Show the result
   MsgBox "The average for the cells in this range is " & result
End Sub

vba average msgbox

AVERAGE with a Range Object

You can assign a group of cells to the Range object, and then use that Range object with the WorksheetFunction object.

Sub TestAverageRange()
   Dim rng As Range
'assign the range of cells
   Set rng = Range("G2:G7")
'use the range in the  formula
   Range("G8") = WorksheetFunction.Average(rng)
'release the range object
  Set rng = Nothing
End Sub

AVERAGE Multiple Range Objects

Similarly, you can calculate the average of the cells from multiple Range Objects.

Sub TestAverageMultipleRanges() 
   Dim rngA As Range 
   Dim rngB as Range
'assign the range of cells 
   Set rngA = Range("D2:D10") 
   Set rngB = Range("E2:E10")   
'use the range in the formula 
Range("E11") = WorksheetFunction.Average(rngA, rngB)
 'release the range object
  Set rngA = Nothing 
  Set rngB = Nothing
End Sub

Using AVERAGEA

The AVERAGEA Function differs from the AVERAGE function in that it create an average from all the cells in a range, even if one of the cells has text in it – it replaces the text with a zero and includes that in calculating the average.   The AVERAGE function would ignore that cell and not factor it into the calculation.

Sub TestAverageA()
   Range("B8) = Application.WorksheetFunction.AverageA(Range("A10:A11")) 
End Sub

In the example below, the AVERAGE function returns a different value to the AVERAGEA function when the calculation is used on cells A10 to A11

vba average averagea

The answer for the AVERAGEA formula is lower than the AVERAGE formula as it replaces the text in A11 with a zero, and therefore averages over 13 values rather than the 12 values that the AVERAGE is calculating over.

Using AVERAGEIF

The AVERAGEIF Function allows you to average the sum of a range of cells that meet a certain criteria.

Sub AverageIf()
   Range("F31") = WorksheetFunction.AverageIf(Range("F5:F30"), "Savings", Range("G5:G30"))
End Sub

The procedure above will only average the cells in range G5:G30 where the corresponding cell in column F has the word ‘Savings’ in it.  The criteria you use has to be in quotation marks.

vba average average if

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

automacro

Learn More

Disadvantages of WorksheetFunction

When you use the WorksheetFunction to average the values in a range in your worksheet, a static value is returned, not a flexible formula. This means that when your figures in Excel change, the value that has been returned by the WorksheetFunction will not change.

vba average static

In the example above, the procedure TestAverage procedure has created the average of B11:M11 and put the answer in N11.   As you can see in the formula bar, this result is a figure and not a formula.

If any of the values change therefore in the Range(B11:M11 ), the results in N11 will NOT change.

Instead of using the WorksheetFunction.Average, you can use VBA to apply the AVERAGE Function to a cell using the Formula or FormulaR1C1 methods.

Using the Formula Method

The formula method allows you to point specifically to a range of cells eg: B11:M11 as shown below.

Sub TestAverageFormula()
  Range("N11").Formula = "=Average(B11:M11)"
End Sub

vba average formula

Using the FormulaR1C1 Method

The FomulaR1C1 method is more flexible in that it does not restrict you to a set range of cells. The example below will give us the same answer as the one above.

Sub TestAverageFormula()
   Range("N11").Formula = "=Average(RC[-12]:RC[-1])"
End Sub

vba average formula r1c1

However, to make the formula more flexible, we could amend the code to look like this:

Sub TestAverageFormula() 
   ActiveCell.FormulaR1C1 = "=Average(R[-11]C:R[-1]C)" 
End Sub

Wherever you are in your worksheet, the formula will then average the values in the 12 cells directly to the left of it and place the answer into your ActiveCell. The Range inside the AVERAGE function has to be referred to using the Row (R) and Column (C) syntax.

Both these methods enable you to use Dynamic Excel formulas within VBA.

There will now be a formula in N11 instead of a value.

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.

Can anybody help me?

Thanks in advanced.

pnuts's user avatar

pnuts

58.1k11 gold badges86 silver badges138 bronze badges

asked Nov 14, 2014 at 9:38

danuca9's user avatar

The easiest answer I’ve found is to use the following:

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 Average function. For instance:

myAverage = Application.WorksheetFunction.Average(getArray(Range("C1:CT56")))

answered Dec 21, 2015 at 15:30

jaysoncopes's user avatar

jaysoncopesjaysoncopes

8053 gold badges13 silver badges26 bronze badges

1

You can enter a formula in the line below the data:

=average(A1:A96)

Isn’t that easier than using VBA for this purpose?

If you really want to use 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

answered Nov 14, 2014 at 10:22

DaveG's user avatar

DaveGDaveG

7416 silver badges16 bronze badges

3

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

answered Nov 14, 2014 at 12:18

danuca9's user avatar

danuca9danuca9

111 gold badge1 silver badge4 bronze badges

0

In Excel, you can use VBA to calculate the average values from a range of cells or multiple ranges. And, in this tutorial, we are going to learn the different ways that we can use it.

Average in VBA using WorksheetFunction

In VBA, there are multiple functions that you can use, but there’s no specific function for this purpose. That does not mean we can’t do an average. In VBA, there’s a property called WorksheetFunction that can help you to call functions into a VBA code.

average-in-vba-using-worksheet-function

Let’s average values from the range A1:A10.

  1. First, enter the worksheet function property and then select the AVERAGE function from the list.
    2-worksheet-function-average
  2. Next, you need to enter starting parenthesis as you do while entering a function in the worksheet.
    3-enter-starting-paranthesis
  3. After that, we need to use the range object to refer to the range for which we want to calculate the average.
    4-ue-the-range-object
  4. In the end, type closing parenthesis and assign the function’s returning value to cell B1.
    5-type-close-pranthesis
Application.WorksheetFunction.Average(Range("A1:A10"))

Now when you run this code, it will calculate the average for the values that you have in the range A1:A10 and enter the value in cell B1.

run-the-code-calculates-average

Average Values from an Entire Column or a Row

In that case, you just need to specify a row or column instead of the range that we have used in the earlier example.

'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"))

Use VBA to Average Values from the Selection

Now let’s say you want to average value from the selected cells only in that you can use a code just like the following.

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

In the above code, we have used the selection and then specified it to the variable “sRange” and then use that range variable’s address to get the average.

VBA Average All Cells Above

The following code takes all the cells from above and average values from them and enters the result in the selected cell.

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

Average a Dynamic Range using VBA

And in the same way, you can use a dynamic range while using VBA to average values.

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

Average a Dynamic Column or a Row

In the same way, if you want to use a dynamic column you can use the following code which will take the column of the active cell and average for all the values that you have in it.

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

And for a row.

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

What is VBA

  • Get Today’s Date and Current Time using VBA
  • Sum Values in Excel using VBA
  • Match Function in VBA
  • MOD in VBA
  • VBA Random Numbers
  • VBA Calculate
  • VBA Concatenate
  • VBA Worksheet Function

 

leonardik

Пользователь

Сообщений: 4
Регистрация: 28.08.2018

Всем привет!

В таблице имеется столбец А с данными. Каждая цифра в этом столбце помечается 1 и 2 (при помощи цикла for..next) в соседнем столбце В.
Таким образом, в моем случае получается 3 динамических массива:
по цифре 1 массивы 10-20-30 и 60-70-80
по цифре 2 массив 40-50
В каждом из этих массивов мне нужно рассчитать среднее и записать рядом.
в 1 массиве среднее 20, во 2 – 45, а 3 – 70.

Если данных в столбце несколько тысяч, то как сделать расчет средних при помощи VBA?

Образец таблицы внизу.

Заранее спасибо за помощь!    :)

10 1
20 1 20
30 1
40 2
50 2 45
60 1
70 1 70
80 1

Изменено: leonardik28.08.2018 20:27:08

 

Юрий М

Модератор

Сообщений: 60729
Регистрация: 14.09.2012

Контакты см. в профиле

#2

28.08.2018 20:29:27

Цитата
leonardik написал:
Образец таблицы внизу

Внизу должен быть Excel-файл, а не описание таблицы.

 

vikttur

Пользователь

Сообщений: 47199
Регистрация: 15.09.2012

В отдельный столбец выведите все числа со второго столбца.
СРЗНАЧЕСЛИ()

 

leonardik

Пользователь

Сообщений: 4
Регистрация: 28.08.2018

Видимо некорректно вопрос сформулировал…
Рабочий файл вложил.

В моем случае, если применить формулу СРЗНАЧЕСЛИ()
Мне нужно, чтобы среднее считалось в каждом из диапазонов ограниченных 1, то есть в моем  случае – А1:А6, А10:А12 и А15:А16.
Также рассчитал средние в этих диапазонах: 12,5; 20 и 24,5 соответственно.

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

Прикрепленные файлы

  • пример.xls (18.5 КБ)

 

vikttur

Пользователь

Сообщений: 47199
Регистрация: 15.09.2012

Формула массива, ввод тремя клавишами
=ЕСЛИ(B2-B3=1;СРЗНАЧ(ИНДЕКС($A$1:A2;МАКС(ЕСЛИ($B$1:B2=””;СТРОКА($B$1:B2)+1))):A2);)

Прикрепленные файлы

  • срзнач.xls (31.5 КБ)

 

Kuzmich

Пользователь

Сообщений: 7999
Регистрация: 21.12.2012

#6

01.09.2018 21:51:02

Код
Sub iMean()
Dim Rng As Range
Dim iSumma As Double
  For Each Rng In Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(2, 1).Areas
    Rng.Cells(1, 2) = WorksheetFunction.Average(Rng.Offset(, -1))
    iSumma = iSumma + WorksheetFunction.Sum(Rng.Offset(, -1))
  Next
    Range("D3") = iSumma
End Sub

Изменено: Kuzmich01.09.2018 21:55:50

 

leonardik

Пользователь

Сообщений: 4
Регистрация: 28.08.2018

#7

03.09.2018 15:06:28

Всем большое спасибо!  :)  Все получилось!

Добавить комментарий