VBA Média – AVERAGE, AVERAGEA, AVERAGEIF

Este tutorial demonstrará como usar a função Média do Excel no VBA.

A função MÉDIA do Excel é usada para calcular uma média a partir de um intervalo de células em sua planilha que contenha valores. No VBA, ela é acessada usando o método WorksheetFunction.

AVERAGE WorksheetFunction

O objeto WorksheetFunction pode ser usado para chamar a maioria das funções do Excel que estão disponíveis na caixa de diálogo Inserir função no Excel. A função AVERAGE é uma delas.

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

Você pode ter até 30 argumentos na função AVERAGE. Cada um dos argumentos deve se referir a um intervalo de células.

O exemplo abaixo produzirá a média dos valores das células B11 a N11

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

O exemplo abaixo produzirá uma média dos valores das células de B11 a N11 e os valores das células de B12:N12. Se você não digitar o objeto Application, ele será assumido.

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

Atribuir o Resultado de AVERAGE a uma Variável

Talvez você queira usar o resultado da fórmula em outro lugar do código, em vez de gravá-lo diretamente de volta em um intervalo do Excel. Se esse for o caso, você poderá atribuir o resultado a uma variável para usá-lo posteriormente em seu código.

Sub AtribuirMedia()
   Dim result As Integer
'atribuir a variavel   
   result = WorksheetFunction.Average(Range("A10:N10"))
'Mostrar o resultado
   MsgBox "A média das células nesse intervalo é " & result
End Sub

AVERAGE com um Objeto Range

Você pode atribuir um grupo de células ao objeto Range e, em seguida, usar esse objeto Range com o objeto WorksheetFunction.

Sub TestarMediaRange()
   Dim rng As Range
'atribuir o intervalo de células
   Set rng = Range("G2:G7")
'usar o range na fórmula
   Range("G8") = WorksheetFunction.Average(rng)
'liberar o objeto range
  Set rng = Nothing
End Sub

AVERAGE de Vários Objetos Range

Da mesma forma, você pode calcular a média das células de vários Objetos Range.

Sub TestarMediaMultiplosRanges() 
   Dim rngA As Range 
   Dim rngB as Range
'atribuir o intervalo de células
   Set rngA = Range("D2:D10") 
   Set rngB = Range("E2:E10")   
'usar o intervalo na fórmula 
Range("E11") = WorksheetFunction.Average(rngA, rngB)
 'liberar os objetos range
  Set rngA = Nothing 
  Set rngB = Nothing
End Sub

Uso da AVERAGEA

A função AVERAGEA difere da função AVERAGE porque cria uma média de todas as células em um intervalo, mesmo que uma das células contenha texto – ela substitui o texto por um zero e o inclui no cálculo da média. A função AVERAGE ignoraria essa célula e não a levaria em conta no cálculo.

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

No exemplo abaixo, a função AVERAGE retorna um valor diferente da função AVERAGEA quando o cálculo é usado nas células A10 a A11

A resposta para a fórmula MÉDIAA (AVERAGEA)é menor do que a fórmula MÉDIA (AVERAGE), pois ela substitui o texto em A11 por um zero e, portanto, calcula a média de 13 valores em vez dos 12 valores que a MÉDIA (AVERAGE) está calculando.

Como Usar AVERAGEIF

A função AVERAGEIF (MÉDIASE) permite calcular a média dos valores de um intervalo de células que atendem a um determinado critério.

Sub ExemploAverageIf()
   Range("E33") = WorksheetFunction.AverageIf(Range("D5:D30"), "Poupança", Range("E5:E30"))
End Sub

O procedimento acima calculará a média apenas das células do intervalo E5:E30 em que a célula correspondente na coluna D tiver a palavra “Poupança”. Os critérios que você usar devem estar entre aspas.

Desvantagens da WorksheetFunction

Quando você usa a WorksheetFunction para calcular a média dos valores em um intervalo na planilha, é retornado um valor estático, não uma fórmula flexível. Isso significa que, quando seus números no Excel mudarem, o valor retornado pela WorksheetFunction não será alterado.

No exemplo acima, o procedimento TestarMedia criou a média de B11:M11 e colocou a resposta em N11. Como você pode ver na barra de fórmulas, esse resultado é um número e não uma fórmula.

Portanto, se algum dos valores for alterado no intervalo (B11:M11), os resultados em N11 NÃO serão alterados.

Em vez de usar a WorksheetFunction.Average, você pode usar o VBA para aplicar a função AVERAGE a uma célula usando os métodos Formula ou FormulaR1C1.

Uso do Método Formula

O método de fórmula permite que você aponte especificamente para um intervalo de células, por exemplo: B11:M11, conforme mostrado abaixo.

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

Como Usar o Método FormulaR1C1

O método FomulaR1C1 é mais flexível, pois não o restringe a um intervalo definido de células. O exemplo abaixo nos dará a mesma resposta que o anterior.

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

Entretanto, para tornar a fórmula mais flexível, poderíamos alterar o código para que ficasse assim:

Sub TestarMediaAverageFormula() 
   ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-12]:RC[-1])"
End Sub

Onde quer que você esteja em sua planilha, a fórmula calculará a média dos valores nas 12 células diretamente à esquerda dela e colocará a resposta em sua ActiveCell. O intervalo dentro da função AVERAGE deve ser referenciado usando a sintaxe de linha (R) e coluna (C).

Esses dois métodos permitem que você use fórmulas dinâmicas do Excel no VBA.

Agora haverá uma fórmula em N11 em vez de um valor.