Créer une plage dynamique avec prise de décision, Excel VBA
Voici une explication détaillée et le code VBA pour créer une plage dynamique dans Excel afin de prendre des décisions automatiquement en fonction de certaines conditions.
Concept : Créer une Plage Dynamique pour la Prise de Décisions avec VBA
Dans des scénarios de prise de décision, on peut vouloir définir une plage dynamique qui évalue différentes conditions ou critères, comme filtrer des données, sélectionner un sous-ensemble de lignes ou effectuer des calculs basés sur des critères. VBA peut aider à automatiser ce processus efficacement en créant des plages dynamiques qui s’adaptent lorsque les données changent.
Nous allons développer un code VBA qui :
- Crée une plage dynamique.
- Évalue un ensemble de critères pour prendre des décisions.
- Effectue des actions en fonction de l’évaluation.
Scénario :
Imaginez que vous avez un jeu de données dans Excel contenant des transactions de vente avec les colonnes suivantes :
- Nom du Produit
- Quantité Vendue
- Montant des Ventes
- Vendeur
- Date
Nous devons sélectionner dynamiquement des plages en fonction de certaines conditions (par exemple, montant des ventes > 1000 € ou vendeur spécifique). En fonction de ces conditions, le code VBA prendra des décisions comme mettre en surbrillance les lignes ou effectuer certains calculs.
Processus Étape par Étape :
1. Définir la Plage Dynamique :
- La plage s’adapte en fonction du nombre de lignes dans votre jeu de données. Cela permet de s’assurer que même si des données sont ajoutées ou supprimées, la plage inclut toujours toutes les données sans avoir besoin d’ajustements manuels.
2. Sélectionner les Données en Fonction des Conditions :
- Une fois la plage dynamique identifiée, vous pouvez appliquer des conditions pour filtrer des données spécifiques (par exemple, ventes > 1000 €).
3. Prise de Décision :
- Le code VBA décidera de ce qu’il faut faire en fonction de vos conditions (mettre en surbrillance, effectuer des calculs, etc.).
Exemple de Code VBA pour la Prise de Décisions avec Plage Dynamique :
Sub PriseDeDecisionsPlageDynamique()
Dim ws As Worksheet
Dim lastRow As Long
Dim dynamicRange As Range
Dim cell As Range
Dim criteria As Double
Dim totalVentes As Double
' Référencer la feuille de calcul
Set ws = ThisWorkbook.Sheets("Ventes")
' Trouver la dernière ligne de données dans la colonne des ventes (colonne C)
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Définir la plage dynamique de la ligne 2 à la dernière ligne dans la colonne des ventes (Colonne C)
Set dynamicRange = ws.Range("A2:E" & lastRow)
' Définir le critère - montant des ventes supérieur à 1000 €
criteria = 1000
' Initialiser la variable pour totaliser les ventes
totalVentes = 0
' Boucle à travers chaque ligne de la plage dynamique
For Each cell In dynamicRange.Columns(3).Cells ' Colonne C (Montant des Ventes)
If cell.Value > criteria Then
' Si le montant des ventes est supérieur à 1000 €, surligner la ligne en jaune
cell.EntireRow.Interior.Color = RGB(255, 255, 0)
' Ajouter le montant des ventes au total
totalVentes = totalVentes + cell.Value
Else
' Si le montant des ventes est inférieur ou égal à 1000 €, surligner la ligne en rouge
cell.EntireRow.Interior.Color = RGB(255, 0, 0)
End If
Next cell
' Afficher le total des ventes des transactions sélectionnées
MsgBox "Le total des ventes des transactions supérieures à " & criteria & " € est de " & totalVentes & " €"
End Sub
Explication Détailée :
1. Référencement de la Feuille de Calcul : Set ws = ThisWorkbook.Sheets("Ventes")
Ici, on définit la feuille de calcul contenant les données (dans cet exemple, une feuille appelée « Ventes »).
2. Trouver la Dernière Ligne : lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Cette ligne permet de trouver dynamiquement la dernière ligne de données dans la colonne A (en supposant que la colonne A ne contient pas de lignes vides). Cela garantit que la plage sélectionnée inclut toutes les lignes de données, même si des lignes sont ajoutées ou supprimées.
3. Définir la Plage Dynamique : Set dynamicRange = ws.Range("A2:E" & lastRow)
La plage dynamique commence à la ligne 2 et s’étend jusqu’à la dernière ligne de la colonne E (cela représente l’ensemble des données). Ainsi, la plage s’ajuste automatiquement si des lignes sont ajoutées ou supprimées.
4. Critère (Montant des Ventes > 1000 €) : criteria = 1000
Le critère de prise de décision est fixé à 1000 €. Le code va évaluer le montant des ventes et prendre des actions en fonction de si ce montant est supérieur à ce critère.
5. Boucle à Travers la Plage et Prise de Décision :
For Each cell In dynamicRange.Columns(3).Cells If cell.Value > criteria Then ' Action si les ventes > 1000 € Else ' Action si les ventes <= 1000 € End If Next cell
Nous parcourons chaque cellule de la troisième colonne (Montant des Ventes). Si le montant des ventes est supérieur au critère (1000 €), nous effectuons une action (mettre en surbrillance la ligne en jaune). Sinon, nous surlignons la ligne en rouge.
6. Totaliser les Ventes :
totalVentes = totalVentes + cell.Value
Nous totalisons les montants des ventes pour les transactions où le montant est supérieur au critère.
7. Afficher les Résultats :
MsgBox "Le total des ventes des transactions supérieures à " & criteria & " € est de " & totalVentes & " €"
Après avoir traité toutes les lignes, le code affiche une boîte de message montrant le total des ventes des transactions où le montant est supérieur à 1000 €.
Conclusion :
Ce code VBA permet d’automatiser la prise de décisions en fonction de conditions spécifiques tout en ajustant dynamiquement la plage de données en fonction de l’évolution de votre jeu de données. Vous pouvez facilement adapter ce code à d’autres scénarios en modifiant les critères de décision et les actions à effectuer.