Gestion de Parc de Véhicules en Excel

 

Exercice Avancé : Gestion de Parc de Véhicules en Excel

Contexte :
Vous êtes responsable de la gestion d'un parc automobile pour une entreprise. Vous devez suivre l'état des véhicules, les dépenses de maintenance, les kilomètres parcourus et planifier les interventions de maintenance. Cet exercice vous permettra d'utiliser des fonctions avancées d'Excel telles que SOMME.SI.ENS, NB.SI.ENS, RECHERCHEV, INDEX, EQUIV, SI, SOMMEPROD, et la Mise en forme conditionnelle.

Structure du Tableau de Données

1. Tableau des Véhicules (A1)
   Ce tableau contient des informations sur chaque véhicule :

A	B	C	D	E	F
Véhicule	Modèle	Année d'achat	Kilométrage	Dernière maintenance	Statut
V001	Toyota Yaris	2020	45 000	15/08/2023	Actif
V002	Ford Transit	2018	120 000	22/06/2023	Entretien
V003	Renault Clio	2021	30 000	01/10/2023	Actif
V004	Mercedes Sprinter	2019	80 000	01/07/2023	Actif
V005	Peugeot Boxer	2020	90 000	10/09/2023	Actif

 

2. Tableau des Dépenses de Maintenance (H1)
   Ce tableau enregistre les dépenses associées à chaque véhicule :

H	I	J	K
Véhicule	Maintenance des dates	Coût Pièces	Coût de la main d'oeuvre
V001	15/08/2023	200	150
V002	22/06/2023	300	200
V003	01/10/2023	100	120
V004	01/07/2023	400	250
V005	10/09/2023	350	300

 

 

Questions et exercices

1. Calculer les Dépenses Totales de Maintenance pour Chaque Véhicule

Utiliser SOMMEPROD pour calculer les dépenses totales de maintenance (Coût Pièces + Coût Main d'Œuvre) pour chaque véhicule et afficher le résultat dans une colonne supplémentaire (colonne L ) :

=SOMMEPROD((H2:H6=H2:H6)*(J2:J6+K2:K6))

Cela vous permet de calculer la somme des coûts associés à chaque véhicule.

2. Déterminer le Véhicule le Plus Récent à Maintenir

Utiliser INDEX et EQUIV pour trouver le véhicule qui a eu sa dernière maintenance la plus récente.

Pour déterminer le véhicule qui a eu sa dernière maintenance la plus récente en utilisant les fonctions INDEX et EQUIV, il faut repérer la date la plus récente dans la colonne des dates de maintenance, puis récupérer le véhicule correspondant à cette date.

Étapes et formule

1. Trouver la Date la Plus Récente dans la colonne Dernière maintenance (colonn E).
2. Utiliser EQUIV pour obtenir la position de cette date dans la plage de dates.
3. Utiliser INDEX pour récupérer le nom du véhicule correspondant en fonction de cette position.

Dans une cellule (par exemple N2 ), entre la formule suivante :

=INDEX(A2:A6; EQUIV(MAX(E2:E6); E2:E6; 0))

Explication

• MAX(E2:E6): Cette partie de la formule trouve la date la plus récente dans la plage E2

(colonne "Dernière entretien").

• EQUIV(MAX(E2:E6); E2:E6; 0): EQUIV retournez la position de cette date la plus récente dans la colonne E.
• INDEX(A2:A6; ...): INDEX utilisez cette position pour renvoyer le véhicule correspondant dans la plage A2 (colonne "Véhicule").

 

3. Calculer le kilométrage moyen des véhicules actifs

Utilisez la fonction MOYENNE.SI.ENS pour calculer le kilométrage moyen des véhicules qui sont actifs. Ce calcul se fera uniquement pour les véhicules dont le statut est "Actif".

Dans une cellule (par exemple, N1 ), entre la formule suivante :

=MOYENNE.SI.ENS(D2:D6; F2:F6; "Actif")

 

 

Pour calculer le kilométrage moyen des véhicules actifs , vous pouvez utiliser la fonction MOYENNE.SI.ENS, qui permet de faire la moyenne d'un ensemble de valeurs selon un ou plusieurs critères.

Étapes et formule

1. Sélectionne la colonne contenant les kilométrages des véhicules (par exemple, D2).
2. Utiliser la colonne contenant le statut des véhicules (par exemple, F2) pour définir les véhicules Actifs .

Dans une cellule (par exemple N3 ), entre la formule suivante :

=MOYENNE.SI.ENS(D2:D6; F2:F6; "Actif")

Explication

• MOYENNE.SI.ENS(D2:D6; F2:F6; "Actif"):
• D2:D6est la plage de données pour les kilométrages.
• F2:F6est la plage des critères (statut des véhicules).
• "Actif"est le critère, signifiant que seuls les véhicules ayant le statut "Actif" seront inclus dans le calcul de la moyenne.

4. Déterminer le Véhicule le Plus Kilométré

Utilisez une combinaison de INDEX et EQUIV pour trouver le véhicule qui a parcouru le plus de kilomètres.

Dans une cellule (par exemple, O1 ), entre la formule suivante :

=INDEX(A2:A6; EQUIV(MAX(D2:D6); D2:D6; 0))

 

Pour déterminer le véhicule ayant le kilométrage le plus élevé , vous pouvez utiliser une combinaison des fonctions INDEXet EQUIV. Cette méthode permet de trouver la position du kilométrage maximum dans la liste, puis de récupérer le nom du véhicule correspondant.

Étapes et formule

1. Utiliser la fonction MAXpour trouver le kilométrage le plus élevé dans la colonne des kilométrages (par exemple, D2).
2. Utiliser EQUIV pour localiser la position de cette valeur maximale dans la même colonne.
3. Utiliser INDEX pour obtenir le nom du véhicule correspondant dans la colonne des noms (par exemple, A2).

Dans une cellule (par exemple N4 ), entre la formule suivante :

=INDEX(A2:A6; EQUIV(MAX(D2:D6); D2:D6; 0))

Explication

• MAX(D2:D6): Cette partie de la formule trouve le kilométrage le plus élevé dans la plage D2
• EQUIV(MAX(D2:D6); D2:D6; 0): EQUIV retourner la position de ce kilométrage maximum dans la plage D2
• INDEX(A2:A6; ...): INDEX utilisez cette position pour renvoyer le nom du véhicule correspondant dans la plage A2

5. Planifier la Prochaine Maintenance des Véhicules

Ajout d'une colonne supplémentaire, Prochaine Maintenance (colonne G ), pour afficher la date de la prochaine maintenance. Supposons que chaque véhicule doit être entretenu tous les 10 000 km.

Dans la cellule G2 , entre la formule suivante, puis copie-la vers le bas :

=SI(D2+10000>=100000; "À réviser"; D2+10000)

Cette formule ajoute 10 000 km au kilométrage actuel pour indiquer le prochain entretien, mais si le total dépasse 100 000 km, elle indique « À réviser ».

Pour planifier la prochaine maintenance des véhicules, on peut ajouter un intervalle de maintenance standard à la dernière date de maintenance de chaque véhicule. Supposons que les véhicules doivent être entretenus tous les 6 mois. Vous pouvez utiliser la fonction DATE pour calculer la date de la prochaine maintenance.

Étapes et formule

1. définir l'Intervalle de Maintenance : Par exemple, si l'intervalle de maintenance est de 6 mois, cela signifie qu'il faut ajouter 6 mois à la dernière date de maintenance.
2. Utilisez la Fonction DATE et MOIS.DECALER pour ajouter l'intervalle à chaque date de maintenance.

Exemple de calcul

Si la colonne E contient les dates de dernière maintenance, et que vous voulez calculer la date de la prochaine maintenance dans la colonne I , vous pouvez utiliser la formule suivante en I2

=MOIS.DECALER(E2; 6)

• Copiez cette formule vers le bas pour calculer la prochaine date de maintenance pour tous les véhicules.

Explication

• MOIS.DECALER(E2; 6): MOIS.DECALER prend la date de dernière maintenance dans E2 et ajoute 6 mois à cette date.
• Cette formule ajustera la date en conséquence, en ajoutant 6 mois à chaque date de dernière maintenance pour obtenir la date de la prochaine maintenance.

Remarques

• Si les intervalles de maintenance sont différents pour chaque véhicule, vous pouvez créer une colonne supplémentaire (par exemple Intervalle de Maintenance dans G ) où vous définissez le nombre de mois pour chaque véhicule. Ensuite, la formule pourrait être modifiée comme suit :

=MOIS.DECALER(E2; G2)

6. Déterminer les Coûts Totaux de Maintenance par Année

Crée un tableau récapitulatif des coûts d'entretien par année d'achat des véhicules.

Supposons que les années d'achat se trouvent dans la colonne C, les coûts totaux de maintenance dans une nouvelle colonne (par exemple M ), utilisez la formule suivante pour chaque année d'achat unique dans le tableau (par exemple, pour 2020 en cellule P2 ).

=SOMME.SI.ENS (J2:J6; C2:C6; P2) + SOMME.SI.ENS(K2:K6; C2:C6; P2)

Cette formule additionne les coûts de pièces et de main d'œuvre pour les véhicules achetés dans l'année spécifiée (colonne P ).

Pour déterminer les coûts totaux de maintenance par année , vous pouvez suivre ces étapes en utilisant la fonction SOMME.SI.ENS pour additionner les coûts en fonction de l'année de la date de maintenance.

Étapes à suivre

1. Ajouter une Colonne pour l'Année de Maintenance :
• Si vos dates de maintenance sont dans la colonne E , crée une nouvelle colonne (par exemple K ) pour extraire l'année de chaque date de maintenance.
• Dans la cellule K2 , entre la formule suivante :

=ANNEE(E2)

• Copiez cette formule vers le bas pour remplir les cellules de K2 à K6 avec les années correspondantes.
1. Listez les Années Uniques :
• Dans une nouvelle zone de votre tableau, listez les années pour lesquelles vous souhaitez calculer les coûts totaux (par exemple dans la colonne M , de M2 ​​à M5 ).
2. Calculer les Coûts Totaux par Année :
• Dans la colonne N , à côté de chaque année, utilisez SOMME.SI.ENS pour additionner les coûts de maintenance pour cette année.
• En N2 , entre la formule suivante pour l'année dans M2 :

= SOMME.SI.ENS(F2:F6; K2:K6; M2)

• Explication de la formule :
• F2:F6: plage contenant les coûts de maintenance.
• K2:K6: plage contenant les années extraites.
• M2: l'année spécifique pour laquelle vous calculez le coût total.
3. Copier la Formule vers le Bas :
• Copiez la formule de N2 vers le bas (N3, N4, etc.) pour calculer les coûts totaux pour chaque année répertoriée dans la colonne M .

7. Utiliser la Mise en Forme Conditionnelle pour Suivre l'État des Véhicules

• Surveiller les Véhicules à Maintenance Approchante : Applique une mise en forme conditionnelle à la colonne D (Kilométrage) pour mettre en évidence les véhicules dont le kilométrage est supérieur à 90,000 km. Pour cela, va dans Accueil > Mise en forme conditionnelle > Nouvelle règle > Utiliser une formule et entre la formule suivante :

=D2>90000

• Identifier les Véhicules qui Nécessitent une Attention Spéciale : Pour la colonne E (Dernière maintenance), utilisez la fonction AUJOURDHUI()pour mettre en évidence les dates qui remontent à plus de 6 mois. Pour cela, crée une nouvelle règle de mise en forme conditionnelle avec la formule suivante :

=AUJOURDHUI()-E2>180

8. Filtrer et Compter les Véhicules Actifs et en Maintenance

Utiliser NB.SI.ENS pour compter les véhicules avec les statuts "Actif" et "Maintenance" dans des cellules séparées (par exemple Q2 pour "Actif" et Q3 pour "Maintenance").

Pour les véhicules « Actifs » :

=NB.SI.ENS(F2:F6; "Actif")

Pour les véhicules "Entretien" :

=NB.SI.ENS(F2:F6; "Maintenance")

9. Identifiant des Véhicules à Réformer

On considère qu'un véhicule doit être reformé s'il a plus de 5 ans depuis son année d'achat et un kilométrage supérieur à 100 000 km. Ajout d'une colonne Réforme (colonne **Avoir ​SI sui

Dans la cellule H2 ,

=SI(ET((2024 - C2) > 5; D2 > 100000); "À reformer"; "OK")

Cela vérifiera si le véhicule a plus de 5 ans (2024 - Année d'achat) et un kilométrage supérieur à 100 000 km. Si c'est le cas, la cellule affichera "À reformer", sinon "OK".

10. Calculer le Total des Dépenses par Statut de Véhicule

Crée un récapitulatif des dépenses de maintenance en fonction du statut des véhicules, pour voir combien coûte l'entretien des véhicules "Actif" et "Maintenance". Ajout d'une nouvelle zone de récapitulatif (par exemple, R2), un S2 :

=SOMMEPROD((F2:F6="Actif") * (J2:J6 + K2:K6))

Et pour les véhicules "Maintenance", en S3 :

=SOMMEPROD((F2:F6="Maintenance") * (J2:J6 + K2:K6))

Ces formules permettent de calculer les dépenses à

11. Suivre la Consommation en Carburant (Optionnel)

Ajout d'une colonne pour le Carburant (je) qui

Véhicule	Consommation (L/100 km)
V001	6
V002	9
V0	5.
V004	8
V00	7

Ensuite, calculez la consommation totale par véhicule selon le kilométrage parcouru

 (colonne J ) avec J2:

=(D2 * I2) / 100

Copiez cette formule vers le bas pour calculer la consommation totale de carburant pour chaque véhicule.

12. Évaluer les Coûts Annuels de Maintenance et de Carburant

Crée un tableau de synthèse (par exemple, dans T2) pour les coûtsT2 )

SOMME.SI.ENS p

=SOMME.SI.ENS(J2:J6; C2:C6; T2) + SOMME.SI.ENS(K2:K6; C2:C6; T2)

Cette formule additionne les coûts de maintenance et la consommation de carburant pour chaque véhicule en fonction de leur année d'achat, te permettant ainsi de connaître les coûts annuels associés à chaque an.

13. Graphique de synthèse

Enfin, crée un graphique qui te permettra de visualiser la répartition des coûts par type de dépense (entretien, carburant) et par statut de véhicule (actif, en maintenance). Utiliser un graphique en colonnes empilées ou un graphique en secteurs pour mieux comparer les coûts.

Objet

Avec cet exercice, tu devrais avoir :

• Une vue d'ensemble complète sur la gestion des véhicules en fonction de leur coût d'entretien et de leur consommation de voiture
• Une planification des réformes nécessaires
• Un tableau de synthèse pour optimiser les décisions concernant la gestion du parc.

Ce type d'analyse t'aidera à des décisions plus éclairées, comme savoir quels véhicules doivent être réformés, lesquels coûtent le plus en entretien, et comment optimiser la consommation de carburant.