Développer des fonctions d’analyse de données personnalisées, Excel VBA

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 dans A1:A10 avec les poids correspondants dans B1:B10.
• =NormaliserDonnees(A1:A10) normalisera les données dans A1:A10.
• =MoyenneMobile(A1:A10, 3) calculera la moyenne mobile pour une période de 3 sur les données dans A1: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.