Développer un modèle de prévision de données personnalisé, Excel VBA

Développer un modèle de prévision de données personnalisé, Excel VBA

Voici un guide détaillé pour développer un modèle de prévision de données personnalisé en utilisant Excel VBA. Ce modèle utilise la régression linéaire pour prédire les valeurs futures basées sur des données historiques.
Étape 1 : Préparation des données

• • Disposition des données : Vous devez préparer un ensemble de données dans Excel avec deux colonnes : une pour la période de temps (par exemple, Date ou Temps) et une autre pour les valeurs observées (par exemple, les ventes, les prix des actions, etc.).
  • Assurez-vous que les données sont propres : aucune valeur manquante ni format incohérent.
  • Exemple de disposition :

| Date | Ventes |
|------------|---------|
| 01/01/2020 | 150 |
| 01/02/2020 | 180 |
| 01/03/2020 | 200 |
| ... | ... |

Étape 2 : Ouvrir Excel et lancer l’éditeur VBA

• Ouvrez votre fichier Excel.
• Appuyez sur Alt + F11 pour ouvrir l’éditeur VBA.
• Dans l’éditeur VBA, insérez un nouveau module en cliquant avec le bouton droit sur un élément du Project Explorer, en sélectionnant Insert puis Module.

Étape 3 : Écrire le code VBA
Nous allons maintenant écrire un macro VBA qui :
1. Prend les données de la feuille Excel.
2. Utilise la régression linéaire (un modèle simple de prévision) pour prédire les valeurs futures.
3. Affiche les valeurs prédites dans Excel.

Sub ForecastData()
    Dim lastRow As Long
    Dim i As Long
    Dim X As Double, Y As Double
    Dim sumX As Double, sumY As Double
    Dim sumXY As Double, sumXX As Double
    Dim slope As Double, intercept As Double
    Dim forecastDate As Date
    Dim forecastValue As Double
    ' Définir la plage de données
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ' Initialiser les sommes
    sumX = 0
    sumY = 0
    sumXY = 0
    sumXX = 0
    ' Parcourir les données pour calculer les sommes
    For i = 2 To lastRow
        X = i - 1 ' La valeur de X (périodes de temps : 1, 2, 3, ...)
        Y = Cells(i, 2).Value ' La valeur de Y (données de ventes)
        sumX = sumX + X
        sumY = sumY + Y
        sumXY = sumXY + X * Y
        sumXX = sumXX + X * X
    Next i
    ' Calculer la pente (b) et l'interception (a) pour la droite de régression linéaire : Y = a + bX
    slope = (lastRow * sumXY - sumX * sumY) / (lastRow * sumXX - sumX * sumX)
    intercept = (sumY - slope * sumX) / lastRow
    ' Afficher l'équation de la droite pour le débogage ou la compréhension
    MsgBox "Équation de la droite : Y = " & intercept & " + " & slope & "X"
    ' Prévoir la prochaine valeur
    forecastDate = Cells(lastRow + 1, 1).Value ' Récupérer la prochaine date (ou période)
    forecastValue = intercept + slope * (lastRow) ' Valeur prédite
    ' Afficher la valeur prédit dans la prochaine ligne
    Cells(lastRow + 1, 2).Value = forecastValue
    ' Optionnel : Vous pouvez mettre en surbrillance ou formater la valeur prédit
    Cells(lastRow + 1, 2).Interior.Color = RGB(255, 255, 0) ' Couleur jaune pour la prévision
    ' Optionnel : Afficher un graphique des données prédites (y compris le point prédit)
    Dim chartObj As ChartObject
    Set chartObj = ActiveSheet.ChartObjects.Add
    chartObj.Chart.ChartType = xlLine
    chartObj.Chart.SetSourceData Source:=Range("A1:B" & lastRow + 1)
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Données Prévisionnelles"
End Sub

Explication du code :
1. Traitement des données :

• Le code commence par calculer le nombre de lignes (lastRow) contenant des données.
• Il calcule ensuite les sommes nécessaires à la régression linéaire : la somme de X (période de temps), la somme de Y (valeurs observées), la somme de XY (multiplication de X et Y) et la somme de XX (carré de X).

2. Régression linéaire :

• • En utilisant la formule de régression linéaire, on calcule la pente (b) et l’interception (a).
  • La formule utilisée est Y = a + bX où :
    • a est l’interception.
    • b est la pente.
    • X est la période de temps.
    • Y est la valeur observée.

3. Prévision :

• Après avoir créé le modèle de régression, la prévision pour la prochaine donnée est calculée.
• La valeur prédite est obtenue en remplaçant la dernière période de temps (X = lastRow) dans l’équation.
• La valeur prédite est affichée dans la prochaine ligne du jeu de données.

4. Visualisation :

• Optionnellement, un graphique est généré pour visualiser à la fois les données historiques et les données prévisionnelles.

Étape 4 : Exécuter la macro

• Fermez l’éditeur VBA.
• De retour dans Excel, appuyez sur Alt + F8, sélectionnez la macro ForecastData, puis cliquez sur Exécuter.
• Le code prévoira la prochaine donnée en fonction du modèle de régression linéaire et affichera la valeur prédite dans la ligne suivante.
• Un graphique sera également affiché montrant les données historiques et la prévision.

Résultat attendu :

• Une nouvelle ligne sera ajoutée au jeu de données avec la valeur prédit.
• La valeur prédit sera mise en surbrillance en jaune.
• Un graphique en ligne sera généré pour afficher les données historiques et les données prévisionnelles.

Cette approche utilise la régression linéaire simple pour la prévision. Vous pouvez l’améliorer en ajoutant des modèles plus sophistiqués, tels que la régression polynomiale ou l lissage exponentiel, en fonction de la complexité de vos données et de vos besoins.