Este tutorial le mostrará cómo usar la función Promedio de Excel en VBA.
La función PROMEDIO de Excel se usa para calcular un promedio de celdas de rango en su hoja de trabajo que tienen valores en ellas. En VBA, se accede mediante el método WorksheetFunction.
Hoja de trabajo PROMEDIO Función
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 PROMEDIO es una de ellas.
| 123 | Función de subpruebaRango ("D33") = Application.WorksheetFunction.Average ("D1: D32")End Sub |
Puede tener hasta 30 argumentos en la función PROMEDIO. Cada uno de los argumentos debe hacer referencia a un rango de celdas.
Este ejemplo a continuación producirá el promedio de la suma de las celdas B11 a N11
| 123 | Promedio de subprueba ()Rango ("O11") = Application.WorksheetFunction.Average (Range ("B11: N11"))End Sub |
El siguiente ejemplo producirá un promedio de la suma de las celdas de B11 a N11 y la suma de las celdas de B12: N12. Si no escribe el objeto Aplicación, se asumirá.
| 123 | Promedio de subprueba ()Rango ("O11") = WorksheetFunction.Average (Range ("B11: N11"), Range ("B12: N12"))End Sub |
Asignar un resultado PROMEDIO a una variable
Es posible que desee utilizar el resultado de su fórmula en otro lugar 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 usar más adelante en su código.
| 1234567 | Sub AssignAverage ()Resultado atenuado como entero'Asignar la variableresult = WorksheetFunction.Average (Range ("A10: N10"))'Muestra el resultadoMsgBox "El promedio de las celdas en este rango es" & resultEnd Sub |
PROMEDIO con un objeto de rango
Puede asignar un grupo de celdas al objeto Range y luego usar ese objeto Range con el Hoja de trabajo Función objeto.
| 123456789 | Sub TestAverageRange ()Atenuar como rango'asignar el rango de celdasEstablecer rng = Rango ("G2: G7")'usa el rango en la fórmulaRango ("G8") = WorksheetFunction.Average (rng)'suelta el objeto de rangoEstablecer rng = NadaEnd Sub |
PROMEDIO Objetos de rango múltiple
Del mismo modo, puede calcular el promedio de las celdas de varios objetos de rango.
| 123456789101112 | SubpruebaAverageMultipleRanges ()Dim rngA como rangoDim rngB como rango'asignar el rango de celdasEstablecer rngA = Rango ("D2: D10")Establecer rngB = Rango ("E2: E10")'usa el rango en la fórmulaRango ("E11") = WorksheetFunction.Average (rngA, rngB)'suelta el objeto de rangoEstablecer rngA = NadaEstablecer rngB = NadaEnd Sub |
Usando AVERAGEA
La función PROMEDIO se diferencia de la función PROMEDIO en que crea un promedio de todas las celdas en un rango, incluso si una de las celdas tiene texto; reemplaza el texto con un cero y lo incluye en el cálculo del promedio. La función PROMEDIO ignoraría esa celda y no la incluiría en el cálculo.
| 123 | Sub TestAverageA ()Rango ("B8) = Application.WorksheetFunction.AverageA (Range (" A10: A11 "))End Sub |
En el siguiente ejemplo, la función PROMEDIO devuelve un valor diferente a la función PROMEDIOEA cuando el cálculo se usa en las celdas A10 a A11
La respuesta para la fórmula PROMEDIO es menor que la fórmula PROMEDIO, ya que reemplaza el texto en A11 con un cero y, por lo tanto, promedia más de 13 valores en lugar de los 12 valores sobre los que se calcula el PROMEDIO.
Usando AVERAGEIF
La función AVERAGEIF le permite promediar la suma de un rango de celdas que cumplen con ciertos criterios.
| 123 | Sub promedio Si ()Rango ("F31") = WorksheetFunction.AverageIf (Range ("F5: F30"), "Savings", Range ("G5: G30"))End Sub |
El procedimiento anterior solo promediará las celdas en el rango G5: G30 donde la celda correspondiente en la columna F tiene la palabra "Ahorro". Los criterios que utilice deben estar entre comillas.
Desventajas de WorksheetFunction
Cuando usa el Hoja de trabajo Función para promediar los valores en un rango en su hoja de trabajo, se devuelve un valor estático, no una fórmula flexible. Esto significa que cuando sus cifras en Excel cambian, el valor devuelto por el Hoja de trabajo Función no cambiará.
En el ejemplo anterior, el procedimiento TestAverage ha creado el promedio de B11: M11 y ha puesto la respuesta en N11. Como puede ver en la barra de fórmulas, este resultado es una cifra y no una fórmula.
Si alguno de los valores cambia por lo tanto en el rango (B11: M11), los resultados en N11 NO cambio.
En lugar de usar el Hoja de trabajo Función Promedio, puede usar VBA para aplicar la función PROMEDIO a una celda usando el Fórmula o Fórmula R1C1 métodos.
Usando el método de fórmula
El método de fórmula le permite apuntar específicamente a un rango de celdas, por ejemplo: B11: M11 como se muestra a continuación.
| 123 | Sub TestAverageFormula ()Rango ("N11"). Fórmula = "= Promedio (B11: M11)"End Sub |
Usando el método FormulaR1C1
El método FomulaR1C1 es más flexible en el sentido de que no lo restringe a un rango establecido de celdas. El siguiente ejemplo nos dará la misma respuesta que el anterior.
| 123 | Sub TestAverageFormula ()Rango ("N11"). Fórmula = "= Promedio (RC [-12]: RC [-1])"End Sub |
Sin embargo, para hacer que la fórmula sea más flexible, podríamos modificar el código para que se vea así:
| 123 | Sub TestCountFormula ()ActiveCell.FormulaR1C1 = "= Recuento (R [-11] C: R [-1] C)"End Sub |
Dondequiera que se encuentre en su hoja de trabajo, la fórmula promediará los valores en las 12 celdas directamente a la izquierda y colocará la respuesta en su ActiveCell. Se debe hacer referencia al rango dentro de la función PROMEDIO utilizando 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.
