Implémenter des techniques de corrélation de données avancées, Excel VBA

Implémenter des techniques de corrélation de données avancées, Excel VBA

Voici une explication détaillée sur la manière d’implémenter des techniques de corrélation de données avancées en VBA Excel.
1. Corrélation de Rang de Spearman (Spearman’s Rank Correlation)
La corrélation de Spearman est une méthode non paramétrique qui mesure la force de la relation monotone (croissante ou décroissante) entre deux variables. Contrairement à la corrélation de Pearson, qui mesure une relation linéaire, Spearman est plus flexible et peut capter des relations non linéaires.
Étapes de l’algorithme :
1. Attribuer des rangs aux données de chaque jeu de variables.
2. Calculer la différence entre les rangs correspondants des deux ensembles de données.
3. Élever au carré les différences obtenues.
4. Calculer la somme des carrés des différences.
5. Appliquer la formule de Spearman pour obtenir le coefficient de corrélation.
Code VBA pour la Corrélation de Rang de Spearman :

Function SpearmanRankCorrelation(rng1 As Range, rng2 As Range) As Double
    Dim n As Long
    Dim rank1() As Double
    Dim rank2() As Double
    Dim diff() As Double
    Dim diffSquared() As Double
    Dim sumDiffSquared As Double
    Dim i As Long
    ' Vérifier que les deux plages ont le même nombre de données
    If rng1.Cells.Count <> rng2.Cells.Count Then
        MsgBox "Les plages doivent avoir le même nombre de cellules"
        Exit Function
    End If
    n = rng1.Cells.Count
    ReDim rank1(1 To n)
    ReDim rank2(1 To n)
    ReDim diff(1 To n)
    ReDim diffSquared(1 To n)
    ' Attribuer des rangs au premier jeu de données (rng1)
    For i = 1 To n
        rank1(i) = WorksheetFunction.Rank(rng1.Cells(i), rng1)
    Next i
    ' Attribuer des rangs au second jeu de données (rng2)
    For i = 1 To n
        rank2(i) = WorksheetFunction.Rank(rng2.Cells(i), rng2)
    Next i
    ' Calculer la différence et la différence au carré
    sumDiffSquared = 0
    For i = 1 To n
        diff(i) = rank1(i) - rank2(i)
        diffSquared(i) = diff(i) ^ 2
        sumDiffSquared = sumDiffSquared + diffSquared(i)
    Next i
    ' Appliquer la formule de Spearman pour calculer la corrélation
    SpearmanRankCorrelation = 1 - (6 * sumDiffSquared) / (n * (n ^ 2 - 1))
End Function

Explication du Code :

• Entrées : La fonction prend deux plages de données (rng1 et rng2), chacune représentant un jeu de valeurs.
• Calcul des Rangs : Nous utilisons la fonction Rank d’Excel pour attribuer des rangs à chaque élément des deux jeux de données.
• Calcul des Différences : Nous calculons les différences entre les rangs des paires de données correspondantes.
• Somme des Carrés des Différences : Nous calculons la somme des carrés des différences.
• Formule de Spearman : Enfin, nous appliquons la formule de Spearman pour obtenir le coefficient de corrélation, qui varie entre -1 (corrélation négative parfaite) et +1 (corrélation positive parfaite).

2. Corrélation Partielle (Partial Correlation)
La corrélation partielle mesure la relation entre deux variables tout en contrôlant l’effet d’une ou plusieurs autres variables. C’est une méthode plus avancée qui permet de mieux comprendre la relation directe entre deux variables, en éliminant l’influence des variables externes.
Étapes de l’algorithme :
1. Réaliser une régression linéaire pour chaque variable par rapport à la ou les variables de contrôle.
2. Calculer les résidus de ces régressions (les erreurs de prédiction).
3. Calculer la corrélation entre les résidus des deux variables (cela donne la corrélation partielle).
Code VBA pour la Corrélation Partielle :

Function PartialCorrelation(rngX As Range, rngY As Range, rngControl As Range) As Double
    Dim X() As Double, Y() As Double, Control() As Double
    Dim n As Long
    Dim ResidualX() As Double, ResidualY() As Double
    Dim i As Long
    Dim betaX As Double, betaY As Double
    Dim correlationXY As Double
    ' Vérifier que les plages ont le même nombre de lignes
    If rngX.Cells.Count <> rngY.Cells.Count Or rngX.Cells.Count <> rngControl.Cells.Count Then
        MsgBox "Les plages doivent avoir le même nombre de cellules"
        Exit Function
    End If
    n = rngX.Cells.Count
    ReDim X(1 To n)
    ReDim Y(1 To n)
    ReDim Control(1 To n)
    ReDim ResidualX(1 To n)
    ReDim ResidualY(1 To n)
    ' Charger les données dans des tableaux
    For i = 1 To n
        X(i) = rngX.Cells(i).Value
        Y(i) = rngY.Cells(i).Value
        Control(i) = rngControl.Cells(i).Value
    Next i
    ' Étape 1 : Régression de X sur la variable de contrôle
    betaX = Regress(X, Control)
    For i = 1 To n
        ResidualX(i) = X(i) - betaX * Control(i)
    Next i
    ' Étape 2 : Régression de Y sur la variable de contrôle
    betaY = Regress(Y, Control)
    For i = 1 To n
        ResidualY(i) = Y(i) - betaY * Control(i)
    Next i
    ' Étape 3 : Calculer la corrélation entre les résidus
    correlationXY = Correlation(ResidualX, ResidualY)
    ' Retourner la corrélation partielle
    PartialCorrelation = correlationXY
End Function
Function Regress(rngDependent As Variant, rngIndependent As Variant) As Double
    ' Régression linéaire simple pour obtenir la pente (beta)
    Dim X() As Double, Y() As Double
    Dim i As Long
    Dim sumX As Double, sumY As Double, sumXY As Double, sumX2 As Double
    Dim beta As Double
    For i = 1 To UBound(rngDependent)
        X(i) = rngIndependent(i)
        Y(i) = rngDependent(i)
    Next i
    sumX = WorksheetFunction.Sum(X)
    sumY = WorksheetFunction.Sum(Y)
    sumXY = WorksheetFunction.SumProduct(X, Y)
    sumX2 = WorksheetFunction.SumProduct(X, X)
    ' Calcul de la pente (beta) pour la régression linéaire
    beta = (sumXY - (sumX * sumY / UBound(X))) / (sumX2 - (sumX ^ 2 / UBound(X)))
    Regress = beta
End Function
Function Correlation(arr1 As Variant, arr2 As Variant) As Double
    ' Calcul de la corrélation de Pearson entre deux tableaux
    Dim sumX As Double, sumY As Double, sumXY As Double
    Dim sumX2 As Double, sumY2 As Double
    Dim i As Long, n As Long
    n = UBound(arr1)
    sumX = WorksheetFunction.Sum(arr1)
    sumY = WorksheetFunction.Sum(arr2)
    sumXY = WorksheetFunction.SumProduct(arr1, arr2)
    sumX2 = WorksheetFunction.SumProduct(arr1, arr1)
    sumY2 = WorksheetFunction.SumProduct(arr2, arr2)
    Correlation = (n * sumXY - sumX * sumY) / Sqr((n * sumX2 - sumX ^ 2) * (n * sumY2 - sumY ^ 2))
End Function

Explication du Code :

• Corrélation Partielle : Cette fonction calcule la corrélation partielle en suivant les étapes suivantes :
• D’abord, elle effectue une régression de X sur la variable de contrôle et calcule les résidus (différences entre les valeurs observées et prédites).
• Ensuite, elle effectue une régression de Y sur la même variable de contrôle et calcule également les résidus de Y.
• Enfin, elle calcule la corrélation de Pearson entre les résidus des deux jeux de données, ce qui donne la corrélation partielle.
• Fonction de Régression : Cette fonction calcule la pente (beta) de la droite de régression linéaire à l’aide de la méthode des moindres carrés.
• Fonction de Corrélation : Elle calcule le coefficient de corrélation de Pearson entre deux jeux de données.

Utilisation :
1. Corrélation de Spearman :

• Pour calculer la corrélation de rang de Spearman entre deux jeux de données dans Excel, entrez la formule suivante dans une cellule :

=SpearmanRankCorrelation(A1:A10, B1:B10)
Cela renverra la corrélation de Spearman entre les jeux de données dans les plages A1:A10 et B1:B10.
2. Corrélation Partielle :

• Pour calculer la corrélation partielle entre deux jeux de données X et Y tout en contrôlant pour une troisième variable Z, utilisez :

=PartialCorrelation(A1:A10, B1:B10, C1:C10)
Cela renverra la corrélation partielle entre A1:A10 (X) et B1:B10 (Y), en contrôlant pour la variable C1:C10 (Z).
Ces techniques avancées vous permettent d’obtenir une meilleure compréhension des relations entre les jeux de données, ce qui est particulièrement utile pour des analyses complexes dans Excel avec VBA.