Implémenter une analyse de régression avancée, Excel VBA

Implémenter une analyse de régression avancée, Excel VBA

Voici un code détaillé en VBA Excel pour implémenter une analyse de régression avancée. Ce code effectue une régression linéaire et fournit des résultats tels que les coefficients (pente et intercept) ainsi que la valeur R-squared (coefficient de détermination), parmi d’autres statistiques importantes.
Objectifs du Code :
1. Préparation des données : Il est nécessaire de disposer de deux ensembles de données : l’une pour la variable indépendante (X) et l’autre pour la variable dépendante (Y).
2. Analyse de régression linéaire : Utilisation de la fonction LINEST d’Excel via VBA pour effectuer l’analyse de régression.
3. Résultats : Le code extrait les coefficients de la régression (pente et intercept), la valeur R-squared et d’autres statistiques, puis les affiche dans la feuille Excel.
Voici le code détaillé en VBA pour implémenter l’analyse de régression avancée :
Code VBA Excel :

Sub AnalyseRegressionAvancee()
    ' Déclaration des variables
    Dim PlageX As Range
    Dim PlageY As Range
    Dim PlageResultats As Range
    Dim ResultatsRegression As Variant
    Dim Intercept As Double
    Dim Pente As Double
    Dim RCarre As Double
    Dim ErreurStandard As Double
    Dim FStatistique As Double
    Dim DegrésLiberté As Double
    ' Définir les plages pour X (variable indépendante) et Y (variable dépendante)
    Set PlageX = Range("A2:A100")  ' Supposons que les données X sont dans la colonne A
    Set PlageY = Range("B2:B100")  ' Supposons que les données Y sont dans la colonne B
    ' Vérifier si les plages X et Y ont le même nombre de lignes
    If PlageX.Rows.Count <> PlageY.Rows.Count Then
        MsgBox "Les plages X et Y doivent avoir le même nombre de points de données", vbCritical
        Exit Sub
    End If
    ' Effectuer la régression linéaire en utilisant la fonction LINEST d'Excel
    ResultatsRegression = Application.WorksheetFunction.LinEst(PlageY, PlageX, True, True)
    ' Extraire les résultats de la fonction LINEST
    Intercept = ResultatsRegression(1, 2)    ' Intercept (b)
    Pente = ResultatsRegression(1, 1)        ' Pente (m)
    RCarre = ResultatsRegression(3, 1)       ' Valeur R-squared
    ErreurStandard = ResultatsRegression(2, 1) ' Erreur standard de la régression
    FStatistique = ResultatsRegression(1, 3)  ' Statistique F
    DegrésLiberté = ResultatsRegression(2, 3) ' Degrés de liberté pour la régression
    ' Afficher les résultats de la régression sur la feuille de calcul
    Set PlageResultats = Range("D2") ' Cellule de départ pour l'affichage des résultats
    PlageResultats.Offset(0, 0).Value = "Intercept (b) :"
    PlageResultats.Offset(0, 1).Value = Intercept
    PlageResultats.Offset(1, 0).Value = "Pente (m) :"
    PlageResultats.Offset(1, 1).Value = Pente
    PlageResultats.Offset(2, 0).Value = "R-Squared :"
    PlageResultats.Offset(2, 1).Value = RCarre
    PlageResultats.Offset(3, 0).Value = "Erreur Standard :"
    PlageResultats.Offset(3, 1).Value = ErreurStandard
    PlageResultats.Offset(4, 0).Value = "Statistique F :"
    PlageResultats.Offset(4, 1).Value = FStatistique
    PlageResultats.Offset(5, 0).Value = "Degrés de Liberté :"
    PlageResultats.Offset(5, 1).Value = DegrésLiberté
    MsgBox "Analyse de régression terminée !", vbInformation
End Sub

Explication détaillée du code :
1. Définition des plages de données :

• Set PlageX = Range("A2:A100") et Set PlageY = Range("B2:B100") définissent les plages de données pour la variable indépendante (X) et la variable dépendante (Y). Vous pouvez ajuster ces plages en fonction de la taille de vos données.

2. Fonction LINEST :

• ResultatsRegression = Application.WorksheetFunction.LinEst(PlageY, PlageX, True, True) exécute la régression linéaire. Cette fonction retourne une matrice contenant plusieurs statistiques importantes, telles que les coefficients de régression, la valeur R-squared, l’erreur standard, etc.

3. Extraction des résultats :

• • Après avoir exécuté la régression, les résultats sont extraits de la matrice retournée par LINEST :
    • Intercept (b) : L’ordonnée à l’origine (intercept) de la droite de régression.
    • Pente (m) : La pente de la droite de régression.
    • R-Squared : Le coefficient de détermination, qui mesure la qualité de l’ajustement de la droite de régression aux données.
    • Erreur Standard : L’erreur standard de l’estimation de la régression.
    • Statistique F : La statistique F qui permet d’évaluer la significativité globale du modèle.
    • Degrés de Liberté : Les degrés de liberté utilisés dans les tests statistiques associés à la régression.

4. Affichage des résultats :

• Les résultats sont affichés à partir de la cellule D2 dans la feuille de calcul, en utilisant la méthode Offset. Chaque statistique est clairement étiquetée pour faciliter la compréhension.

5. Gestion des erreurs :

• Avant de procéder à la régression, le code vérifie que le nombre de données dans la plage PlageX est le même que dans la plage PlageY à l’aide de la condition If PlageX.Rows.Count <> PlageY.Rows.Count. Si les tailles des plages ne correspondent pas, un message d’erreur s’affiche et l’exécution du code est arrêtée.

6. Message de confirmation :

• Après l’exécution de l’analyse, une boîte de message informe l’utilisateur que l’analyse est terminée.

Personnalisation Avancée :

• Régression Multiple : Si vous avez plusieurs variables indépendantes (par exemple, les données dans les colonnes A, C, D, etc.), vous pouvez modifier la plage PlageX pour inclure ces colonnes supplémentaires.

Exemple :
Set PlageX = Range("A2:D100") ' Pour plusieurs variables indépendantes

• Affichage de la droite de régression sur un graphique : Après avoir effectué la régression, vous pouvez créer un graphique en nuage de points (scatter plot) et y ajouter la droite de régression. Vous pouvez utiliser les coefficients extraits (pente et intercept) pour afficher cette droite.
• Autres Statistiques : La fonction LINEST peut également retourner d’autres statistiques détaillées, comme les p-values, qui peuvent être utilisées pour évaluer la significativité statistique du modèle de régression.

Conclusion :
Ce code VBA permet d’effectuer une analyse de régression linéaire avancée dans Excel. En utilisant la fonction LINEST, il extrait des statistiques essentielles comme les coefficients de régression et la valeur R-squared, et les affiche clairement dans la feuille de calcul. Cela permet de mieux comprendre la relation entre les variables et d’évaluer la qualité du modèle de régression.