Moyenne des cellules excluant les dates

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Moyennes sans dates et cellules vides

Avec cette nouvelle astuce VBA Excel, nous allons créer une nouvelle fonction capable de réaliser des opérations classiques comme la somme et la moyenne mais tout en ignorant les cellules vides, les dates et les textes, ce qui n'est pas le cas des fonctions originelles. Pour développer cette nouvelle fonction, nous suggérons d'appuyer les travaux sur un classeur Excel offrant un tableau abritant des données de différentes natures.

• Télécharger le classeur somme-moyenne-sans-dates.xlsm en cliquant sur ce lien,
• Cliquer droit sur le fichier résultant et cliquer sur la rubrique Propriétés,
• Cocher la case Débloquer et valider par Ok,
• Double cliquer sur le fichier pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous trouvons effectivement un tableau mélangeant des nombres, des textes, des cellules vides et des dates.



Sur la droite, la fonction Moyenne est appliquée sur l'ensemble de ces données. Mais le résultat livré est absolument incohérent. Aucun des nombres ne dépasse les 20 unités. Pourtant, la moyenne calculée avoisine les 2000. Les textes sont naturellement ignorés. Mais ce sont les cellules vides et les dates intercalées qui leurrent la fonction. Il en serait de même avec la somme ou d'autres fonctions. En effet, les dates sont des numéros de série, soit des nombres très grands. C'est le format Date qui les rend interprétables. Vous pouvez le constater en sélectionnant la case d'une date et en lui appliquant le format standard.

Création de la fonction
Nous suggérons de créer la fonction dans un module du classeur actif. Comme vous le savez, si vous souhaitez que cette fonction soit disponible pour toutes les utilisations, vous devez l'enregistrer dans la bibliothèque d'Excel.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Module1,
• Dans la feuille de code au centre, créer la fonction moyenneSansDates, comme suit :

Function moyenneSansDates(donnees As Range) As Double
Dim plage As Range: Dim cellule As Range
Dim cumul As Integer: Dim combien As Byte

End Function

Nous la déclarons avec un paramètre en attente (donnees). Ce paramètre correspond à la plage de cellules à moyenner que l'utilisateur désignera au moment où il utilisera cette nouvelle fonction. Ensuite, nous déclarons quelques variables, dont deux plages de cellules (Range). La première doit représenter la zone définie par l'utilisateur au moment de la construction de la fonction. La seconde doit permettre de parcourir chaque cellule de cette plage pour les analyser tour à tour. Ensuite, nous déclarons deux variables numériques. La première doit additionner toutes les cellules reconnues comme des nombres. La seconde doit les compter. La division de la première par la seconde fournira la moyenne exacte, sans leurre.

Parcourir les cellules sélectionnées
Pour réaliser cette moyenne exacte, dénigrant les textes, les cellules vides et les dates, nous devons analyser les cellules de la plage passée en paramètre, les unes après les autres. Pour cela, nous suggérons d'engager une boucle For Each.

• A la suite du code, ajouter les instructions VBA suivantes :

...
cumul = 0: combien = 0: Set plage = donnees

For Each cellule In plage

Next cellule
...

Nous initialisons tout d'abord les variables numériques à zéro puisqu'à ce stade elles n'ont pas encore été incrémentées. Nous initialisons notre objet plage sur la plage de cellules passée en paramètre par l'utilisateur. Puis, nous exploitons l'objet cellule pour parcourir cette plage grâce à une boucle For Each.

Tester chaque cellule de la plage
Maintenant, nous devons nous assurer que chaque cellule à additionner est bien un nombre. Mais attention et nous l'avons annoncé, une date est considérée comme un nombre. Donc, nous devons nous assurer que ce nombre n'est pas un numéro de série, soit une date. C'est la raison pour laquelle nous devons engager une instruction conditionnelle multicritère.

• Dans la boucle, créer l'instruction conditionnelle suivante :

...
If IsNumeric(cellule.Value) = True And IsDate(cellule.Value) = False And cellule.Value <> "" Then

End If
...

Les fonctions IsNumeric et IsDate avec les booléens en réponses nous permettent de savoir si la cellule en cours d'analyse est bien numérique et dans le même temps qu'elle n'est pas une date. Mais ce n'est pas tout, une cellule vide ne représente rien mais ressemble à tout. Donc dans le même temps toujours, nous nous assurons que la cellule en cours d'analyse multi testée, n'est pas vide.

Cumuler les nombres
Lorsque tous ces critères sont vérifiés, nous savons que nous pouvons additionner les cellules car elles portent bien des nombres dénués de dates et de cellules vierges.

• Dans les bornes de l'instruction conditionnelle, ajouter les deux lignes VBA suivantes :

...
cumul = cumul + cellule.Value
combien = combien + 1
...

Dans la variable cumul, nous additionnons toutes les valeurs numériques constatées, à chaque passage dans la boucle. Puis, nous incrémentons la variable combien en conséquence. Elle servira à la division pour fournir la moyenne.

La moyenne des valeurs numériques
Précisément, c'est maintenant que doit intervenir le calcul de la moyenne, après la boucle, soit après l'analyse de toutes les cellules de la plage passée en argument de la fonction. Comme vous le savez, une fonction VBA répond par son propre nom. C'est donc elle que nous devons affecter.

• Après la boucle, ajouter la ligne VBA suivante :

...
Next cellule

moyenneSansDates = Round(cumul / combien, 2)

End Function
...

Nous exploitons la fonction Round sur la division de la somme par le nombre de cellules effectivement numériques, pour limiter le nombre de décimales à deux unités.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Cliquer sur la cellule I7 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Taper le nom de la nouvelle fonction suivi d'une parenthèse, soit : moyenneSansDates(,
• Sélectionner toutes les cellules du tableau, soit la plage B3:G15,
• Fermer la parenthèse de la fonction,
• Puis, valider la formule par la touche Entrée du clavier,

Comme vous pouvez le voir, le résultat (9,96) est bien différent de celui livré par la fonction Excel Moyenne engagée sur la feuille en cellule I4. Grâce à cette nouvelle fonction, cette moyenne est exacte malgré les imperfections de la plage. Elle exclut les dates, les vides et les textes. Pour en avoir le coeur net, il vous suffit de sélectionner toutes les cellules numériques avec la touche CTRL du clavier et de consulter l'information numérique livrée sur la moyenne dans la barre d'état en bas de la fenêtre Excel. Le résultat, à quelques décimales près, puisque nous avons arrondi le calcul, corrobore la véracité de la solution.