TP Perfectionnement Excel Intermédiaire : Fonctions Avancées et TCD

Telechargé par aitboukhliqwafa58
Travaux Pratiques : Perfectionnement Excel
Niveau Intermédiaire – Fonctions Avancées et Gestion des Données
Module : Outils Bureautiques et Analyse de Données Durée estimée : 1h30
Prérequis : Références relatives/absolues ($A$1), fonctions arithmétiques de base, mise en
forme simple.
Objectifs pédagogiques
Ce travail pratique vise à consolider les compétences intermédiaires sur tableur à travers des
cas concrets d’entreprise :
– Maîtriser les recherches de données dynamiques (RECHERCHEV ou INDEX/EQUIV).
– Utiliser des calculs conditionnels avancés (SOMME.SI.ENS,NB.SI.ENS).
– Mettre en place des logiques conditionnelles imbriquées (SI,ET,OU).
– Automatiser le reporting avec un Tableau Croisé Dynamique (TCD) et des segments.
Exercice 1 : Facturation et Remises Dynamiques (10 points)
Contexte
Une entreprise de vente de matériel informatique gère son journal des commandes. Vous
devez automatiser le calcul des prix unitaires selon un catalogue, puis calculer la remise com-
merciale en fonction de paliers prédénis.
Données fournies
Tableau 1 : Catalogue des Produits (Plage A1:C5)
Code Produit Désignation Prix Unitaire (€)
P01 Clavier Ergonomique 45
P02 Souris Sans Fil 25
P03 Écran 27 Pouces 180
P04 Casque Audio USB 60
Tableau 2 : Commandes Récentes (Plage E1:J6)
N° Cde Code Quantité P.U. (€) Total Brut (€) Total Net (€)
CMD-101 P03 4 [À calculer] [À calculer] [À calculer]
CMD-102 P01 12 [À calculer] [À calculer] [À calculer]
CMD-103 P02 25 [À calculer] [À calculer] [À calculer]
CMD-104 P04 3 [À calculer] [À calculer] [À calculer]
CMD-105 P03 8 [À calculer] [À calculer] [À calculer]
1
Consignes de réalisation
1.1 Validation des données : Créez une liste déroulante dans la colonne Code du Tableau 2
basée sur les codes du catalogue (A2:A5).
1.2 Recherche du prix unitaire : À l’aide de la fonction RECHERCHEV (ou du couple INDEX
et EQUIV), déterminez automatiquement le prix unitaire correspondant au code sélec-
tionné. Veillez à utiliser les références absolues appropriées.
1.3 Calcul du montant brut : Calculez le Total Brut en multipliant la quantité commandée
par le prix unitaire.
1.4 Application de la remise conditionnelle : Calculez le Total Net en appliquant une règle
de remise progressive :
– Si la quantité est strictement supérieure à 10 et que le total brut dépasse 300 €,
accorder une remise de 10 %.
– Si la quantité est supérieure ou égale à 5 (sans remplir la première condition), ac-
corder 5% de remise.
– Sinon, aucune remise n’est accordée.
Indication : Utilisez une structure imbriquée de fonctions SI combinée avec la fonction ET.
1.5 Mise en forme conditionnelle : Mettez en surbrillance verte les cellules du Total Net
bénéciant d’une remise de 10%.
Exercice 2 : Analyse Commerciale Multi-critères et TCD (10 points)
Contexte
Le service commercial souhaite analyser les performances trimestrielles de ses représentants
sur diérentes régions (Nord, Sud, Est, Ouest).
Extrait de la base de données (Feuille « Ventes »)
Date Commercial Région Montant Vente (€) Statut
05/01/2026 Martin Nord 3 200 Validé
12/01/2026 Sophie Sud 1 400 En attente
18/01/2026 Martin Nord 2 100 Validé
22/01/2026 Lucas Ouest 4 500 Validé
04/02/2026 Sophie Sud 3 800 Validé
15/02/2026 Sophie Nord 950 Validé
27/02/2026 Lucas Ouest 1 200 En attente
10/03/2026 Martin Est 2 600 Validé
Consignes de réalisation
2.1 Calculs statistiques conditionnels (Feuille « Synthèse ») :
– À l’aide de la fonction SOMME.SI.ENS, calculez le montant total des ventes Validées
réalisées par le commercial Martin.
2
– À l’aide de la fonction NB.SI.ENS, déterminez le nombre de transactions dont le
montant est supérieur à 2 000 € pour la région Nord.
– Calculez le panier moyen des ventes validées à l’aide de MOYENNE.SI.
2.2 Tableau Croisé Dynamique (TCD) :
– Insérez un TCD sur une nouvelle feuille nommée « Rapport_TCD ».
– Placez les Régions en lignes et les Commerciaux en colonnes.
– Achez en valeurs la Somme des Montants (format monétaire sans décimale).
– Ajoutez un ltre de rapport sur le champ Statut pour ne conserver que les ventes
« Validé ».
2.3 Restitution visuelle :
– Insérez un Segment (ltre visuel interactif) sur le champ Région.
– Créez un graphique croisé dynamique en colonnes groupées illustrant le chire
d’aaires par commercial.
Fin du sujet – Bon travail !
3
1 / 3 100%
La catégorie de ce document est-elle correcte?
Merci pour votre participation!

Faire une suggestion

Avez-vous trouvé des erreurs dans l'interface ou les textes ? Ou savez-vous comment améliorer l'interface utilisateur de StudyLib ? N'hésitez pas à envoyer vos suggestions. C'est très important pour nous!