Développer une solution de prévision de données personnalisée, Excel VBA

Développer une solution de prévision de données personnalisée, Excel VBA

Voici un guide détaillé pour développer une solution de prévision de données personnalisée en Excel VBA :
Étape 1 : Configurer le classeur Excel
Tout d’abord, assurez-vous que votre classeur Excel a la structure suivante :
1. Feuille de données : C’est là que seront stockées vos données brutes. Supposons que vous avez des données historiques pour la prévision. Les colonnes peuvent inclure « Date » (par exemple, séries temporelles) et « Valeur » (les données que vous souhaitez prévoir).
Exemple :
Date | Valeur
2021-01-01 | 100
2021-01-02 | 110
2021-01-03 | 120
2. Feuille de sortie de prévision : Cette feuille affichera les données prévues. Elle peut inclure des valeurs prédites pour des dates futures, avec des colonnes comme « Date » et « Valeur prévue. »
3. Modèle de prévision : Selon le type de modèle de prévision que vous utilisez (par exemple, régression linéaire, lissage exponentiel), vous devrez peut-être organiser les paramètres et les résultats du modèle d’une manière spécifique.
Étape 2 : Écrire le code VBA
L’étape suivante consiste à écrire le code VBA pour effectuer le calcul de prévision. Voici un exemple d’un modèle de prévision linéaire simple :

Sub ForecastData()
    Dim DataRange As Range
    Dim DateRange As Range
    Dim ValueRange As Range
    Dim ForecastRange As Range
    Dim LastRow As Long
    Dim ForecastPeriod As Integer
    Dim X() As Double, Y() As Double
    Dim Slope As Double, Intercept As Double
    Dim i As Long, j As Long
    Dim PredictedValue As Double
    ' Définir les plages
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Set DateRange = Range("A2:A" & LastRow)
    Set ValueRange = Range("B2:B" & LastRow)
    ForecastPeriod = 10 ' Nombre de jours à prévoir
    ' Tableaux pour stocker les données pour la régression linéaire
    ReDim X(1 To LastRow - 1)
    ReDim Y(1 To LastRow - 1)
    ' Remplir les tableaux X et Y
    For i = 1 To LastRow - 1
        X(i) = DateRange.Cells(i + 1, 1).Value
        Y(i) = ValueRange.Cells(i + 1, 1).Value
    Next i
    ' Calculer la pente et l'ordonnée à l'origine de la droite en utilisant la fonction LINEST
    Slope = Application.WorksheetFunction.LinEst(Y, X)(1, 1)
    Intercept = Application.WorksheetFunction.LinEst(Y, X)(1, 2)
    ' Afficher les valeurs prévisionnelles
    Set ForecastRange = Range("A" & LastRow + 1 & ":A" & LastRow + ForecastPeriod)
    For j = 1 To ForecastPeriod
        ' Calculer la valeur prévisionnelle selon le modèle de régression linéaire
        PredictedValue = Slope * (DateRange.Cells(LastRow, 1).Value + j) + Intercept
        ForecastRange.Cells(j, 1).Value = DateRange.Cells(LastRow, 1).Value + j
        ForecastRange.Cells(j, 2).Value = PredictedValue
    Next j
End Sub

Étape 3 : Comprendre le Code
1. Définir les plages :

• DateRange : Fait référence à la plage contenant les dates historiques.
• ValueRange : Fait référence à la plage contenant les valeurs historiques (les données à prévoir).
• LastRow : Identifie la dernière ligne des données afin que le code sache où se termine la série de données.

2. Tableaux pour la régression linéaire :

• X et Y : Tableaux utilisés pour stocker les données de dates et de valeurs afin de les utiliser pour le calcul de la régression linéaire.

3. Utilisation de LINEST pour la régression linéaire :

• Slope et Intercept : Ce sont les paramètres calculés par la fonction LINEST qui modélisent la relation linéaire entre la date (variable indépendante) et les valeurs (variable dépendante).

4. Prévision des données :

• La prévision est effectuée pour un nombre de périodes (par exemple, 10 jours à venir) en fonction du modèle de régression linéaire. La date prévue est placée dans la plage de prévision, et la valeur prévue est calculée en utilisant la formule y = mx + b (où m est la pente, et b est l’ordonnée à l’origine).

Étape 4 : Exécuter le Code
1. Ouvrez le classeur Excel où les données sont stockées.
2. Appuyez sur Alt + F11 pour ouvrir l’éditeur VBA.
3. Dans l’éditeur, allez dans Insertion > Module et collez le code VBA dans le module.
4. Fermez l’éditeur et revenez à Excel.
5. Appuyez sur Alt + F8, sélectionnez ForecastData, puis cliquez sur « Exécuter. »
Étape 5 : Voir les Résultats
Après avoir exécuté le code, les valeurs prévues apparaîtront dans la « Feuille de sortie de prévision », à partir de la ligne sous votre dernier point de données.
Par exemple, si le dernier point de données est le 2021-01-03 et que vous prévoyez 10 jours à venir, la prévision commencera le 2021-01-04 et affichera les valeurs prédites pour chaque jour suivant.
Conclusion
Cet exemple de base démontre l’utilisation d’un modèle de régression linéaire pour la prévision des données. En fonction de vos données et du type de modèle de prévision que vous souhaitez utiliser, vous pouvez personnaliser ce code. Pour des modèles plus complexes, vous pouvez envisager d’utiliser le lissage exponentiel, les modèles ARIMA, ou d’autres techniques statistiques. L’idée principale est de comprendre les hypothèses sous-jacentes du modèle de prévision choisi et de savoir comment l’appliquer dans Excel VBA pour l’automatiser.