VBA Promedio – AVERAGE, AVERAGEA, AVERAGEIF

Este tutorial demostrará cómo utilizar la función AVERAGE (Promedio) de Excel en VBA.

La función AVERAGE de Excel se utiliza para calcular un promedio de un rango de celdas en su hoja de trabajo que tienen valores en ellos. En VBA, se accede a ella utilizando el Objeto WorksheetFunction.

AVERAGE WorksheetFunction

El objeto WorksheetFunction se puede utilizar para llamar a la mayoría de las funciones de Excel que están disponibles en el cuadro de diálogo Insertar función en Excel. La función AVERAGE es una de ellas.

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

 

Puede tener hasta 30 argumentos en la función AVERAGE. Cada uno de los argumentos debe referirse a un rango de celdas.

Este ejemplo producirá el promedio de la suma de las celdas B11 a N11

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

El ejemplo siguiente producirá un promedio de la suma de las celdas de B11 a N11 y la suma de las celdas de B12:N12. Si no se escribe el objeto Aplicación, se asumirá.

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

Asignar el Resultado de AVERAGE a una variable

Es posible que quieras utilizar el resultado de tu fórmula en otra parte del código en lugar de escribirlo directamente en un Rango de Excel. Si este es el caso, puede asignar el resultado a una variable para usarla más tarde en su código.

Sub AsignarPromedio()
    Dim result As Integer
    'Asignar la variable
    result = WorksheetFunction.Average(Range("A10:N10"))
    'Mostrar el resultado
    MsgBox "El promedio de las celdas de este rango es " & result
End Sub

 

Promedio con un Objeto de Rango

Puedes asignar un grupo de celdas al objeto Range, y luego utilizar ese objeto Range con el objeto WorksheetFunction.

Sub TestAverageRange()
    Dim rng As Range
    'asignar el rango de celdas
    Set rng = Range("G2:G7")
    'utilizar el rango en la fórmula
    Range("G8") = WorksheetFunction.Average(rng)
    'liberar el objeto de rango
    Set rng = Nothing
End Sub

Promedio de Varios Objetos de Rango

De forma similar, puede calcular el promedio de las celdas de múltiples Objetos de Rango.

Sub TestAverageMultipleRanges()
    Dim rngA As Range
    Dim rngB As Range
    'asignar el rango de celdas
    Set rngA = Range("D2:D10")
    Set rngB = Range("E2:E10")
    'utilizar el rango en la fórmula
    Range("E11") = WorksheetFunction.Average(rngA, rngB)
    'liberar el objeto de rango
    Set rngA = Nothing
    Set rngB = Nothing
End Sub

Uso de AVERAGEA

La función AVERAGEA difiere de la función AVERAGE en que crea un promedio de todas las celdas en un rango, incluso si una de las celdas tiene texto en ella – reemplaza el texto con un cero y lo incluye en el cálculo del promedio. La función AVERAGE ignoraría esa celda y no la incluiría en el cálculo.

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

En el siguiente ejemplo, la función AVERAGE devuelve un valor diferente al de la función AVERAGEA cuando el cálculo se utiliza en las celdas A10 a A11

La respuesta de la fórmula AVERAGEA es inferior a la de la fórmula AVERAGE, ya que sustituye el texto de A11 por un cero y, por tanto, promedia sobre 13 valores en lugar de los 12 valores sobre los que calcula AVERAGE.

Uso de AVERAGEIF

La función AVERAGEIF te permite promediar la suma de un rango de celdas que cumplen un determinado criterio.

Sub PromedioSi()
   Range("E18") = WorksheetFunction.AverageIf(Range("D5:D16"), "Ahorros", Range("E5:E16"))
End Sub

El procedimiento anterior sólo promediará las celdas del rango E5:G16 en las que la celda correspondiente de la columna D tenga la palabra «Ahorros». El criterio que utilices tiene que ir entre comillas.

 

Desventajas de WorksheetFunction

Cuando utilizas la función WorksheetFunction para promediar los valores de un rango en tu hoja de cálculo, se devuelve un valor estático, no una fórmula flexible. Esto significa que cuando tus cifras en Excel cambian, el valor que ha sido devuelto por la WorksheetFunction no cambiará.

En el ejemplo anterior, el procedimiento PruebaPromedio ha creado la media de B11:M11 y ha puesto la respuesta en N11. Como puedes ver en la barra de fórmulas, este resultado es una cifra y no una fórmula.

Por lo tanto, si alguno de los valores cambia en el Rango(B11:M11 ), los resultados en N11 NO cambiarán.

En lugar de utilizar la función WorksheetFunction.Average, puedes utilizar VBA para aplicar la función AVERAGE a una celda utilizando los métodos Formula o FormulaR1C1.

Utilizando el método de la fórmula

El método de la fórmula le permite apuntar específicamente a un rango de celdas, por ejemplo: B11:M11 como se muestra a continuación.

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

 

Uso del método FormulaR1C1

El método FomulaR1C1 es más flexible, ya que no se limita a un rango de celdas determinado. El ejemplo siguiente nos dará la misma respuesta que el anterior.

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

Sin embargo, para que la fórmula sea más flexible, podríamos modificar el código de la siguiente manera:

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

En cualquier lugar de la hoja de cálculo, la fórmula promediará los valores de las 12 celdas situadas directamente a su izquierda y colocará la respuesta en su CeldaActiva. El Rango dentro de la función PROMEDIO tiene que ser referido usando la sintaxis de Fila (R) y Columna (C).

Ambos métodos le permiten utilizar fórmulas dinámicas de Excel dentro de VBA.

Ahora habrá una fórmula en N11 en lugar de un valor.