Ce tutoriel va vous montrer comment utiliser la fonction Excel Average dans VBA.
La fonction Excel MOYENNE est utilisée pour calculer une moyenne à partir d'une plage de cellules de votre feuille de calcul contenant des valeurs. En VBA, il est accessible à l'aide de la méthode WorksheetFunction.
MOYENNE Feuille de travailFonction
L'objet WorksheetFunction peut être utilisé pour appeler la plupart des fonctions Excel disponibles dans la boîte de dialogue Insérer une fonction dans Excel. La fonction MOYENNE en fait partie.
| 123 | Fonction de sous-testPlage("D33") = Application.WorksheetFunction.Average("D1:D32")Fin du sous-marin |
Vous pouvez avoir jusqu'à 30 arguments dans la fonction MOYENNE. Chacun des arguments doit faire référence à une plage de cellules.
Cet exemple ci-dessous produira la moyenne de la somme des cellules B11 à N11
| 123 | Moyenne de sous-test()Range("O11") = Application.WorksheetFunction.Average(Range("B11:N11"))Fin du sous-marin |
L'exemple ci-dessous produira une moyenne de la somme des cellules de B11 à N11 et de la somme des cellules de B12:N12. Si vous ne saisissez pas l'objet Application, il sera supposé.
| 123 | Moyenne de sous-test()Range("O11") = WorksheetFunction.Average(Range("B11:N11"),Range("B12:N12"))Fin du sous-marin |
Affectation d'un résultat MOYEN à une variable
Vous souhaiterez peut-être utiliser le résultat de votre formule ailleurs dans le code plutôt que de l'écrire directement dans une plage Excel. Si tel est le cas, vous pouvez affecter le résultat à une variable à utiliser plus tard dans votre code.
| 1234567 | Sous-affectationMoyenne()Dim result As Integer'Attribuer la variablerésultat = WorksheetFunction.Average(Range("A10:N10"))'Afficher le résultatMsgBox "La moyenne des cellules de cette plage est " & résultatFin du sous-marin |
MOYENNE avec un objet Range
Vous pouvez affecter un groupe de cellules à l'objet Range, puis utiliser cet objet Range avec le Feuille de travailFonction objet.
| 123456789 | Sous TestAverageRange()Dim rng As Range'attribuer la plage de cellulesDéfinir rng = Range("G2:G7")'utiliser la plage dans la formuleRange("G8") = WorksheetFunction.Average(rng)'libérer l'objet rangeDéfinir rng = RienFin du sous-marin |
MOYENNE Objets à plusieurs plages
De même, vous pouvez calculer la moyenne des cellules de plusieurs objets de plage.
| 123456789101112 | Sous TestAverageMultipleRanges()Dim rngA comme plageDim rngB comme plage'attribuer la plage de cellulesDéfinir rngA = Plage("D2:D10")Définir rngB = Range("E2:E10")'utiliser la plage dans la formuleRange ("E11") = WorksheetFunction.Average(rngA, rngB)'libérer l'objet rangeDéfinir rngA = RienDéfinir rngB = RienFin du sous-marin |
Utilisation d'AVERAGEA
La fonction MOYENNE diffère de la fonction MOYENNE en ce qu'elle crée une moyenne à partir de toutes les cellules d'une plage, même si l'une des cellules contient du texte - elle remplace le texte par un zéro et l'inclut dans le calcul de la moyenne. La fonction MOYENNE ignorerait cette cellule et ne la prendrait pas en compte dans le calcul.
| 123 | Sous TestMoyenneA()Plage("B8) = Application.WorksheetFunction.AverageA(Range("A10:A11"))Fin du sous-marin |
Dans l'exemple ci-dessous, la fonction MOYENNE renvoie une valeur différente à la fonction MOYENNEA lorsque le calcul est utilisé sur les cellules A10 à A11
La réponse pour la formule MOYENNE est inférieure à la formule MOYENNE car elle remplace le texte dans A11 par un zéro, et fait donc la moyenne sur 13 valeurs plutôt que sur les 12 valeurs sur lesquelles la MOYENNE calcule.
Utilisation de AVERAGEIF
La fonction AVERAGEIF vous permet de faire la moyenne de la somme d'une plage de cellules qui répondent à certains critères.
| 123 | Sous-moyenneSi()Plage("F31") = WorksheetFunction.AverageIf(Plage("F5:F30"), "Économies", Plage("G5:G30"))Fin du sous-marin |
La procédure ci-dessus ne fera la moyenne que des cellules de la plage G5:G30 où la cellule correspondante de la colonne F contient le mot « Épargne ». Les critères que vous utilisez doivent être entre guillemets.
Inconvénients de WorksheetFunction
Lorsque vous utilisez le Feuille de travailFonction pour faire la moyenne des valeurs d'une plage de votre feuille de calcul, une valeur statique est renvoyée, pas une formule flexible. Cela signifie que lorsque vos chiffres dans Excel changent, la valeur renvoyée par le Feuille de travailFonction ne changera pas.
Dans l'exemple ci-dessus, la procédure TestAverage a créé la moyenne de B11:M11 et a mis la réponse dans N11. Comme vous pouvez le voir dans la barre de formule, ce résultat est un chiffre et non une formule.
Si l'une des valeurs change donc dans la plage (B11:M11 ), les résultats dans N11 seront NE PAS monnaie.
Au lieu d'utiliser le WorksheetFunction.Average, vous pouvez utiliser VBA pour appliquer la fonction MOYENNE à une cellule en utilisant le Formule ou FormuleR1C1 méthodes.
Utilisation de la méthode de la formule
La méthode de formule vous permet de pointer spécifiquement vers une plage de cellules, par exemple : B11:M11 comme indiqué ci-dessous.
| 123 | Sous TestFormuleMoyenne()Plage("N11").Formule = "=Moyenne(B11:M11)"Fin du sous-marin |
Utilisation de la méthode FormulaR1C1
La méthode FomulaR1C1 est plus flexible dans la mesure où elle ne vous limite pas à une plage définie de cellules. L'exemple ci-dessous nous donnera la même réponse que celui ci-dessus.
| 123 | Sous TestFormuleMoyenne()Plage("N11").Formule = "=Moyenne(RC[-12]:RC[-1])"Fin du sous-marin |
Cependant, pour rendre la formule plus flexible, nous pourrions modifier le code pour qu'il ressemble à ceci :
| 123 | Sous TestCountFormula()ActiveCell.FormulaR1C1 = "=Count(R[-11]C:R[-1]C)"Fin du sous-marin |
Où que vous soyez dans votre feuille de calcul, la formule fera alors la moyenne des valeurs dans les 12 cellules directement à sa gauche et placera la réponse dans votre ActiveCell. La plage à l'intérieur de la fonction MOYENNE doit être référencée à l'aide de la syntaxe de ligne (R) et de colonne (C).
Ces deux méthodes vous permettent d'utiliser des formules Excel dynamiques dans VBA.
Il y aura désormais une formule dans N11 au lieu d'une valeur.
