Calculer la médiane mobile, Excel VBA

Calculer la médiane mobile, Excel VBA

Calculer une médiane mobile (aussi appelée « médiane glissante ») dans Excel avec VBA est une tâche courante pour l’analyse de séries temporelles ou de données numériques. La médiane mobile est utilisée pour lisser les données en calculant la médiane d’une fenêtre glissante de valeurs dans une série de données. 

Objectif 

Le but ici est d’écrire un code VBA pour calculer la médiane mobile d’une plage de données dans Excel. La fenêtre de la médiane mobile est définie par un nombre de périodes (par exemple, 3, 5, etc.). 

Exigences 

1. Données : Une colonne de données numériques. 
2. Fenêtre de la médiane mobile : Un nombre défini de périodes (par exemple, 3 ou 5). 
3. Sortie : Une autre colonne où les résultats de la médiane mobile seront affichés. 

Exemple de données 

Imaginons que vos données se trouvent dans la colonne A, de la cellule A2 à A100. Vous souhaitez calculer la médiane mobile sur une fenêtre de 3 périodes et afficher les résultats à partir de la cellule B3. 

Code VBA détaillé 

Voici le code VBA qui effectue ce calcul : 

Sub CalculerMédianeMobile() 
    Dim plageDonnees As Range 
    Dim plageRésultats As Range 
    Dim n As Integer 
    Dim i As Long 
    Dim j As Long 
    Dim fenetre() As Double 
    Dim mediane As Double 
    ' Définir la plage de données (colonne A de A2 à A100) 
    Set plageDonnees = Range("A2:A100") 
    ' Définir la taille de la fenêtre (par exemple 3 périodes) 
    n = 3 
    ' Initialiser la plage de résultats (colonne B à partir de B3) 
    Set plageRésultats = Range("B3:B100") 
    ' Vérifier si la plage de résultats a la bonne taille 
    If plageRésultats.Rows.Count < plageDonnees.Rows.Count - n + 1 Then 
        MsgBox "La plage de résultats est trop petite !" 
        Exit Sub 
    End If 
    ' Calculer la médiane mobile 
    For i = n To plageDonnees.Rows.Count 
        ' Créer un tableau pour stocker les données de la fenêtre 
        ReDim fenetre(n - 1) 
        ' Remplir le tableau avec les données de la fenêtre 
        For j = 0 To n - 1 
            fenetre(j) = plageDonnees.Cells(i - j, 1).Value 
        Next j 
        ' Trier le tableau pour trouver la médiane 
        Call TrierTableau fenetre 
        ' Calculer la médiane (le nombre du milieu dans le tableau trié) 
        mediane = fenetre(Int(n / 2)) 
        ' Afficher la médiane dans la colonne B 
        plageRésultats.Cells(i - n + 1, 1).Value = mediane 
    Next i 
End Sub 
Sub TrierTableau(ByRef tableau() As Double) 
    Dim i As Long, j As Long 
    Dim temp As Double 
    ' Tri à bulle pour trier le tableau par ordre croissant 
    For i = LBound(tableau) To UBound(tableau) - 1 
        For j = i + 1 To UBound(tableau) 
            If tableau(i) > tableau(j) Then 
                ' Échanger les valeurs 
                temp = tableau(i) 
                tableau(i) = tableau(j) 
                tableau(j) = temp 
            End If 
        Next j 
    Next i 
End Sub

Explication du code 

1. Définition des plages de données et des résultats : 

• plageDonnees définit la plage de cellules qui contient vos données. Ici, les données se trouvent dans la colonne A de la cellule A2 à A100. 

• plageRésultats définit où les résultats de la médiane mobile seront affichés. Nous commençons à la cellule B3 pour éviter d’écrire dans les premières lignes où les fenêtres de médiane ne sont pas complètes. 

2. Fenêtre glissante de médiane (n périodes) : 

• Le code permet de définir la taille de la fenêtre en ajustant la variable n. Dans l’exemple, la fenêtre de 3 périodes est utilisée, mais cela peut être ajusté selon vos besoins. 

3. Calcul de la médiane mobile : 

• Pour chaque position i dans la plage de données, une fenêtre de taille n est extraite (les n dernières valeurs). 

• Ces valeurs sont ensuite triées, et la médiane est calculée comme étant l’élément du milieu du tableau trié. 

• Le résultat de la médiane est enregistré dans la colonne B. 

4. Tri des valeurs de la fenêtre : 

• Le code utilise une procédure auxiliaire TrierTableau qui trie les valeurs de la fenêtre de manière croissante, en utilisant l’algorithme de tri à bulles. 

• Une fois triées, la médiane est simplement la valeur au centre du tableau trié (pour une fenêtre de taille n impaire). 

Points à améliorer ou adapter : 

• Le tri des valeurs est effectué avec un algorithme de tri à bulles. Pour de grandes plages de données, vous pourriez envisager d’utiliser un algorithme plus rapide comme le tri rapide. 

• La taille de la fenêtre n est définie dans le code, mais vous pouvez également ajouter une boîte de dialogue pour permettre à l’utilisateur de saisir cette taille. 

Pour exécuter le code : 

1. Ouvrez Excel. 
2. Appuyez sur Alt + F11 pour ouvrir l’éditeur VBA. 
3. Allez dans Insertion > Module et collez le code. 
4. Appuyez sur F5 pour exécuter la macro et calculer la médiane mobile. 

Vous pouvez ajuster la taille de la fenêtre ou les plages de données selon vos besoins.