📝 Gestion d’un parc de contrats
🎯 Objectif
Mettre en pratique plusieurs fonctions Excel pour analyser et présenter les données d’un tableau de suivi de contrats.
🗂 Mise en situation
Vous gérez un portefeuille de contrats de maintenance pour des clients.
Chaque contrat a une date de début, une date de fin, un montant annuel, et un statut (actif ou inactif).
Votre objectif est de :
- Identifier les contrats qui arrivent bientôt à échéance
- Calculer leur durée en mois
- Formater proprement les dates
- Mettre en évidence les contrats à renouveler
📊 Données à utiliser
| Contrat | Client | Date début | Date fin | Montant (€) | Statut |
|---|---|---|---|---|---|
| CT-001 | Alpha SARL | 01/03/2023 | 29/02/2024 | 1 200 | Actif |
| CT-002 | Beta SAS | 15/05/2022 | 14/05/2024 | 1 600 | Actif |
| CT-003 | Gamma SARL | 01/01/2023 | 31/12/2023 | 950 | Inactif |
| CT-004 | Delta SA | 01/09/2023 | 31/08/2024 | 1 800 | Actif |
| CT-005 | Epsilon SAS | 10/10/2023 | 09/10/2024 | 2 100 | Actif |
💡 Consignes
1️⃣ Mise en forme des dates
Dans deux colonnes supplémentaires :
- Utilise la fonction
TEXTEpour afficher la date de début sous le format :jj mmmm aaaa(ex. 01 mars 2023) - Idem pour la date de fin
2️⃣ Calcul de la durée du contrat
Ajoute une colonne Durée (mois) et utilise DATEDIF :
=DATEDIF(C2;D2;"m")
Cela donne la durée en mois.
3️⃣ Identification des contrats proches de l’échéance
Crée une colonne A renouveler ? avec la fonction SI :
- Si la date de fin est dans moins de 60 jours et que le statut est Actif, affiche « Oui », sinon « Non »
Exemple de formule possible :
=SI(ET(D2-AUJOURDHUI()<60;F2="Actif");"Oui";"Non")
4️⃣ Mise en forme conditionnelle
- Surligne en orange les lignes dont la colonne A renouveler ? = « Oui »
- Surligne en gris les lignes dont le Statut = « Inactif »
5️⃣ Calcul du montant total des contrats actifs
En dessous du tableau, calcule :
=SOMME.SI(F2:F6;"Actif";E2:E6)
→ Cela donne le montant cumulé des contrats actifs.
✅ Résultat attendu
À la fin, les stagiaires obtiennent un tableau clair :
- Les durées calculées automatiquement
- Les contrats proches de l’échéance marqués et surlignés
- Les dates joliment formatées
- Le total des montants actifs affiché en bas