Automatisez les processus de prévision du taux de désabonnement des clients, Excel VBA
Voici un exemple simple de code VBA pour automatiser la prévision du taux de désabonnement des clients dans Excel. Ce code utilise une méthode de régression linéaire pour prédire le taux de désabonnement en fonction des données historiques des mois précédents.
Scénario :
Supposons que vous avez une feuille Excel avec les données suivantes :
• Colonne A : Mois (par exemple : Janvier, Février, etc.)
• Colonne B : Nombre total de clients (par exemple : 1000, 950, etc.)
• Colonne C : Nombre de clients désabonnés (par exemple : 10, 15, etc.)
Nous allons calculer le taux de désabonnement comme la fraction des clients désabonnés par rapport au nombre total de clients, et utiliser une régression linéaire pour prédire ce taux pour les mois à venir.
1. Préparer les données
Assurez-vous que vos données sont organisées comme suit :
• A1 : « Mois »
• B1 : « Total Clients »
• C1 : « Clients Désabonnés »
• D1 : « Taux de Désabonnement » (calculé comme C / B)
2. Code VBA
Voici un exemple de code VBA pour automatiser cette tâche :
Sub PrevoirTauxDesabonnement()
Dim i As Long
Dim n As Long
Dim totalClients As Range
Dim clientsDesabonnes As Range
Dim tauxDesabonnement As Range
Dim xValues() As Double
Dim yValues() As Double
Dim coeffA As Double, coeffB As Double
Dim forecastMonth As Long
Dim predictedRate As Double
' Définir la plage des données
n = Cells(Rows.Count, 1).End(xlUp).Row - 1 ' Nombre de lignes de données
Set totalClients = Range("B2:B" & n + 1)
Set clientsDesabonnes = Range("C2:C" & n + 1)
Set tauxDesabonnement = Range("D2:D" & n + 1)
' Calculer le taux de désabonnement
For i = 2 To n + 1
Cells(i, 4).Value = Cells(i, 3).Value / Cells(i, 2).Value ' Taux de désabonnement
Next i
' Remplir les valeurs x (mois) et y (taux de désabonnement)
ReDim xValues(n - 1)
ReDim yValues(n - 1)
For i = 1 To n
xValues(i - 1) = i ' Mois sous forme d'entier (1, 2, 3,...)
yValues(i - 1) = tauxDesabonnement.Cells(i).Value ' Taux de désabonnement
Next i
' Calcul de la régression linéaire (y = a * x + b)
Call CalculerRegression(xValues, yValues, coeffA, coeffB)
' Afficher les résultats de la régression
MsgBox "La régression linéaire est : Taux = " & coeffA & " * Mois + " & coeffB
' Prédire le taux pour le mois suivant
forecastMonth = n + 1 ' Mois suivant
predictedRate = coeffA * forecastMonth + coeffB
MsgBox "Le taux de désabonnement prévu pour le mois " & forecastMonth & " est : " & predictedRate
' Ajouter la prédiction dans la feuille (optionnel)
Cells(forecastMonth + 1, 4).Value = predictedRate ' Prédiction dans la colonne D
End Sub
Sub CalculerRegression(xValues As Variant, yValues As Variant, ByRef a As Double, ByRef b As Double)
Dim n As Long
Dim sumX As Double, sumY As Double
Dim sumXY As Double, sumX2 As Double
Dim denominator As Double
n = UBound(xValues) + 1
sumX = 0
sumY = 0
sumXY = 0
sumX2 = 0
For i = 0 To n - 1
sumX = sumX + xValues(i)
sumY = sumY + yValues(i)
sumXY = sumXY + (xValues(i) * yValues(i))
sumX2 = sumX2 + (xValues(i) ^ 2)
Next i
denominator = (n * sumX2) - (sumX ^ 2)
If denominator <> 0 Then
a = ((n * sumXY) - (sumX * sumY)) / denominator ' Coefficient a
b = ((sumX2 * sumY) - (sumX * sumXY)) / denominator ' Coefficient b
Else
a = 0
b = 0
End If
End Sub
3. Explication du code
• Calcul du taux de désabonnement :
Dans la boucle For, on calcule le taux de désabonnement pour chaque mois en divisant le nombre de clients désabonnés (colonne C) par le nombre total de clients (colonne B).
• Régression linéaire :
La fonction CalculerRegression implémente la méthode des moindres carrés pour déterminer les coefficients a (pente) et b (ordonnée à l’origine) de la droite de régression.
• Prédiction du taux de désabonnement :
Après avoir obtenu les coefficients a et b, le programme prédit le taux de désabonnement pour le mois suivant en utilisant la formule de la droite de régression : Taux = a * Mois + b.
4. Comment l’utiliser
1. Ouvrez Excel, appuyez sur Alt + F11 pour ouvrir l’éditeur VBA.
2. Dans l’éditeur VBA, allez dans Insertion > Module et collez le code.
3. Revenez à Excel et créez une feuille avec vos données sur les clients et le désabonnement.
4. Exécutez la macro en appuyant sur Alt + F8, sélectionnez PrevoirTauxDesabonnement, puis cliquez sur « Exécuter ».
5. Résultats
Le code affichera un message avec la régression linéaire, puis prédira le taux de désabonnement pour le mois suivant. Vous pouvez également ajouter la prédiction directement dans la feuille de calcul.
Ce code est une manière simple d’implémenter un modèle de prévision du taux de désabonnement dans Excel avec VBA, et peut être amélioré pour inclure d’autres facteurs et ajustements en fonction des besoins spécifiques de votre entreprise.