VBA Promedio – AVERAGE, AVERAGEA, AVERAGEIF
In this Article
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.