Développer des fonctions d’analyse de données personnalisées, Excel VBA
Voici un exemple détaillé de code VBA pour développer des fonctions d’analyse de données personnalisées dans Excel. Ces fonctions peuvent être adaptées pour des besoins d’analyse variés comme la moyenne pondérée, la normalisation des données, la moyenne mobile et l’écart type.
Exemple 1 : Calcul de la Moyenne Pondérée
La moyenne pondérée est utilisée pour calculer la moyenne des valeurs, en tenant compte de l’importance (poids) de chaque valeur. Voici comment créer une fonction personnalisée pour cela.
Code VBA pour Moyenne Pondérée :
Function MoyennePonderee(valeurs As Range, poids As Range) As Double Dim sommeValeurs As Double Dim sommePoids As Double Dim i As Integer sommeValeurs = 0 sommePoids = 0 ' Vérifier que les plages de valeurs et de poids sont de la même taille If valeurs.Count <> poids.Count Then MsgBox "Le nombre de valeurs doit être égal au nombre de poids.", vbCritical Exit Function End If ' Boucle à travers chaque valeur et calcul de la moyenne pondérée For i = 1 To valeurs.Count sommeValeurs = sommeValeurs + (valeurs.Cells(i).Value * poids.Cells(i).Value) sommePoids = sommePoids + poids.Cells(i).Value Next i ' Retourne la moyenne pondérée If sommePoids <> 0 Then MoyennePonderee = sommeValeurs / sommePoids Else MoyennePonderee = 0 End If End Function
Explication :
1. Définition de la fonction : Function MoyennePonderee(valeurs As Range, poids As Range)
définit la fonction, où valeurs
est la plage de données et poids
est la plage des poids associés.
2. Boucle sur les valeurs : La boucle permet de calculer la somme des valeurs * poids
et la somme des poids
.
3. Retour : La fonction retourne la moyenne pondérée en divisant la somme des valeurs pondérées par la somme des poids.
Exemple 2 : Normalisation des Données
La normalisation permet de réduire les valeurs d’un jeu de données à une plage spécifique, généralement entre 0 et 1. Cela est couramment utilisé dans l’analyse statistique.
Code VBA pour Normalisation :
Function NormaliserDonnees(plageDonnees As Range) As Variant Dim minVal As Double Dim maxVal As Double Dim i As Integer Dim tableauNormalise() As Double minVal = Application.WorksheetFunction.Min(plageDonnees) maxVal = Application.WorksheetFunction.Max(plageDonnees) ' Initialiser le tableau pour stocker les valeurs normalisées ReDim tableauNormalise(1 To plageDonnees.Count) ' Boucle pour normaliser les données For i = 1 To plageDonnees.Count tableauNormalise(i) = (plageDonnees.Cells(i).Value - minVal) / (maxVal - minVal) Next i NormaliserDonnees = tableauNormalise End Function
Explication :
1. Calcul des valeurs Min et Max : Nous utilisons les fonctions Min
et Max
pour obtenir les valeurs minimales et maximales de la plage donnée.
2. Normalisation : La formule (valeur - min) / (max - min)
est appliquée à chaque valeur.
3. Retour du tableau : La fonction retourne un tableau des valeurs normalisées.
Exemple 3 : Calcul de la Moyenne Mobile
La moyenne mobile est une méthode pour analyser les données en créant des moyennes sur des sous-ensembles de données, souvent utilisée pour lisser les fluctuations dans les séries chronologiques.
Code VBA pour Moyenne Mobile :
Function MoyenneMobile(plageDonnees As Range, periode As Integer) As Variant Dim i As Integer Dim tableauMoyenne() As Double Dim somme As Double ' Initialiser le tableau pour stocker les moyennes mobiles ReDim tableauMoyenne(1 To plageDonnees.Count - periode + 1) ' Boucle pour calculer la moyenne mobile For i = periode To plageDonnees.Count somme = 0 ' Additionner les valeurs sur la période actuelle For j = i - periode + 1 To i somme = somme + plageDonnees.Cells(j).Value Next j ' Stocker la moyenne mobile tableauMoyenne(i - periode + 1) = somme / periode Next i MoyenneMobile = tableauMoyenne End Function
Explication :
1. Somme sur la période : La moyenne mobile est calculée en additionnant les valeurs sur une période spécifiée (periode
).
2. Boucle : La fonction boucle sur les données pour calculer la moyenne pour chaque sous-ensemble.
3. Retour du tableau : Elle retourne un tableau des moyennes mobiles pour chaque période.
Exemple 4 : Calcul de l’Écart Type (Calcul Personnalisé)
L’écart type est une mesure de la dispersion des données. Voici comment créer une fonction personnalisée pour le calculer.
Code VBA pour Écart Type :
Function EcartTypePersonnalise(plageDonnees As Range) As Double Dim moyenne As Double Dim sommeCarrés As Double Dim i As Integer ' Calcul de la moyenne moyenne = Application.WorksheetFunction.Average(plageDonnees) sommeCarrés = 0 ' Boucle pour calculer la somme des carrés des écarts par rapport à la moyenne For i = 1 To plageDonnees.Count sommeCarrés = sommeCarrés + (plageDonnees.Cells(i).Value - moyenne) ^ 2 Next i ' Retourner l'écart type EcartTypePersonnalise = Sqr(sommeCarrés / (plageDonnees.Count - 1)) End Function
Explication :
1. Calcul de la Moyenne : La moyenne des données est calculée en premier.
2. Carré des Déviations : La fonction boucle sur chaque valeur et calcule le carré de la déviation par rapport à la moyenne.
3. Retour de l’Écart Type : La fonction retourne l’écart type, qui est la racine carrée de la somme des carrés des écarts divisée par le nombre de points moins 1.
Comment Utiliser Ces Fonctions :
- Une fois le code ajouté à un module VBA, vous pouvez utiliser ces fonctions directement dans vos feuilles Excel comme des fonctions natives.
- Par exemple :
=MoyennePonderee(A1:A10, B1:B10)
calculera la moyenne pondérée des valeurs dansA1:A10
avec les poids correspondants dansB1:B10
.=NormaliserDonnees(A1:A10)
normalisera les données dansA1:A10
.=MoyenneMobile(A1:A10, 3)
calculera la moyenne mobile pour une période de 3 sur les données dansA1:A10
.
Conclusion :
Ces exemples montrent comment créer des fonctions d’analyse de données personnalisées en VBA pour Excel. Vous pouvez les étendre pour effectuer des analyses plus complexes, en ajoutant des logiques supplémentaires ou même des mécanismes de gestion d’erreurs. Ces fonctions peuvent être réutilisées dans plusieurs classeurs et adaptées aux besoins spécifiques de votre analyse de données.