Calculer le R au carré, Excel VBA

Calculer le R au carré, Excel VBA

Pour calculer le R² (ou coefficient de détermination) dans Excel à l’aide de VBA, nous allons suivre un processus détaillé. 

Qu’est-ce que le R² ? 

Le R², aussi appelé « coefficient de détermination », mesure la proportion de la variance des données qui peut être expliquée par un modèle de régression linéaire. Sa valeur varie entre 0 et 1 : 

• Un R² proche de 1 indique que le modèle explique une grande partie de la variance. 
• Un R² proche de 0 indique que le modèle explique peu de la variance. 

Calcul de R² en VBA 

Supposons que vous avez des données dans deux colonnes d’Excel : 

• Colonne A : les valeurs de la variable indépendante XXX 
• Colonne B : les valeurs de la variable dépendante YYY 

Nous allons utiliser une approche de régression linéaire pour calculer le R2R^2R2. En VBA, cela peut être fait via la méthode LinEst, qui calcule les paramètres de la régression, y compris R2R^2R2. 

Code VBA pour calculer R² 

Voici un exemple de code VBA qui calcule le R2R^2R2 à partir des données dans les colonnes A et B : 

Sub Calculer_R2() 
    ' Déclaration des variables 
    Dim PlageX As Range 
    Dim PlageY As Range 
    Dim Resultats As Variant 
    Dim R2 As Double
    ' Définir les plages de données (A2:A10 pour X et B2:B10 pour Y) 
    Set PlageX = Range("A2:A10") 
    Set PlageY = Range("B2:B10")
    ' Utilisation de la fonction LinEst pour effectuer une régression linéaire 
    ' LinEst retourne une matrice contenant plusieurs valeurs, dont R2 
    Resultats = Application.WorksheetFunction.LinEst(PlageY, PlageX, True, True) 
    ' Le R2 est dans la première ligne, deuxième colonne de la matrice retournée par LinEst 
    R2 = Resultats(3, 1) 
    ' Affichage du R2 dans une cellule spécifique (par exemple, C1) 
    Range("C1").Value = "R^2 = " & R2 
End Sub

Explication du code : 

1. Déclaration des variables : 

• PlageX et PlageY représentent les plages de données pour les variables indépendantes et dépendantes respectivement. 
• Resultats est une variable qui va stocker les résultats de la régression. 
• R2 est la variable qui contiendra la valeur de R2R^2R2. 

2. Définition des plages de données : 

• Range(« A2:A10 ») est la plage des valeurs de XXX (indépendantes), et Range(« B2:B10 ») est la plage des valeurs de YYY (dépendantes). Vous pouvez ajuster ces plages selon vos données. 

3. Fonction LinEst : 

• Application.WorksheetFunction.LinEst(PlageY, PlageX, True, True) effectue la régression linéaire entre les variables XXX et YYY. La fonction retourne plusieurs résultats : 
• La première ligne contient les coefficients de la régression (pente, ordonnée à l’origine). 
• La deuxième ligne contient les erreurs standard de ces coefficients. 
• La troisième ligne contient R2R^2R2 (qui est notre objectif ici). 
• La quatrième ligne contient l’erreur standard de YYY estimée. 

4. Accès à R² : 

• R² se trouve dans Resultats(3, 1), qui correspond à la troisième ligne et première colonne de la matrice retournée par LinEst. 

5. Affichage du résultat : 

• Le R² est affiché dans la cellule C1 avec un texte explicatif. 

Exécution du Code 

• Ouvrez Excel et appuyez sur Alt + F11 pour ouvrir l’éditeur VBA. 
• Cliquez sur Insertion puis Module pour créer un nouveau module. 
• Collez le code dans le module. 
• Retournez dans Excel et appuyez sur Alt + F8, sélectionnez Calculer_R2 et cliquez sur Exécuter. 
• Le R² sera affiché dans la cellule C1. 

Exemple de données : 

Si vous exécutez le code avec ces données dans les colonnes A et B, le résultat de R² sera affiché dans la cellule C1. 

Ce code est assez simple et peut être adapté à des données plus complexes ou à des plages dynamiques en ajustant les références des plages.