Nom Complet :
HICHAM MEZIANI
NISRIN MANSOURI
RABHA MAMOUINI
TP Microsoft Excel
Objectifs:
Mise en forme : Maîtriser l'esthétique et la lisibilité des données avec des styles de tableau adaptés.
Formats de données : Assurer la précision des calculs en utilisant les formats appropriés pour
différentes données.
Formules : Développer des compétences en création de formules pour des calculs précis.
Filtres : Utiliser les filtres pour analyser des sous-ensembles spécifiques de données.
Tri : Organiser et analyser les données grâce à des méthodes de tri efficaces.
Graphiques : Créer des visualisations pertinentes pour représenter et interpréter les données.
Instructions:
1. Authentifiez-vous sur https://www.office.com
2. Dans votre espace de travail, créez un nouveau classeur Excel vierge.
3. Renommer le classeur avec le nom « Bilan des ventes »
4. Renommer la feuille 1 avec le nom « Janvier2023 »
5. Copier et collez les données à partir de la plate-forme en utilisant l’option « Coller les
valeurs »
Mise en forme du tableau :
6. Appliquez un style de tableau à votre choix pour rendre vos données plus lisibles.
Sélectionnez les données de votre tableau.
Allez dans l'onglet Accueil → Mettre sous forme de tableau.
Choisissez un style de tableau dans les options proposées.
7. Ajuster les largeurs de colonnes au contenu
Ajustez les largeurs des colonnes : Placez votre curseur entre deux en-têtes
de colonnes et double-cliquez pour ajuster automatiquement.
Formats de données :
8. Convertissez les colonnes de prix en format monétaire avec le symbole "DH" et deux
décimales.
Sélectionnez la colonne contenant les prix.
Allez dans l'onglet Accueil → Groupe Nombre → Sélectionnez Monétaire
et choisissez le symbole "DH".
9. Assurez-vous que les données de la colonne « Quantité Vendue » est au format
numérique.
Sélectionnez la colonne "Quantité Vendue" et définissez-la au format Nombre.
10. Formater la colonne « Date » avec le format « dimanche 1 janvier 2023 ».
Formatez la colonne "Date" :
• Sélectionnez la colonne.
• Cliquez avec le bouton droit → Format de cellule → Personnalisée.
• Tapez dddd d mmmm yyyy pour afficher "dimanche 1 janvier 2023".
Formules:
11. Dans une nouvelle colonne intitulée " Prix HT " calculez le prix Hors Taxes en utilisant la
formule :
Insérez une nouvelle colonne avec comme titre "Prix HT".
Dans la première cellule (ex. D2), entrez la formule : =B2*C2 (où B2 est le
prix unitaire et C2 la quantité vendue).
Faites glisser la poignée de recopie vers le bas pour appliquer la formule à
toute la colonne.
Prix HT = Prix unitaire * Quantité vendue
12. Dans une nouvelle colonne intitulée "TVA", calculez la TVA (Taxe sur la Valeur Ajoutée) pour
chaque transaction. Utilisez un taux de 20%.
Ajoutez une colonne "TVA".
Dans la première cellule (ex. E2), entrez la formule : =D2*20% (où D2 est le
Prix HT).
Recopiez la formule sur toute la colonne.
13. Dans une autre colonne "Prix Total TTC", calculez le prix TTC (Toutes Taxes Comprises) en
utilisant la formule :
Ajoutez une colonne "Prix Total TTC".
Dans la première cellule (ex. F2), entrez la formule : =D2+E2.
Recopiez la formule sur toute la colonne.
Prix TTC = Prix HT +TVA
14. Ajoutez une ligne à la fin du tableau qui affiche la somme de chaque colonne
Insérez une ligne de total en bas.
Utilisez la fonction =SOMME(plage) dans chaque colonne pour calculer les
totaux.
Filtres:
15. Appliquez un filtre à votre tableau.
Sélectionnez tout le tableau.
Allez dans l'onglet Données → Filtrer.
16. Utilisez ce filtre pour afficher seulement les transactions pour le "Produit A".
Filtrer pour "Produit A" :
Cliquez sur la flèche dans l'en-tête de la colonne.
Sélectionnez uniquement "Produit A" dans la liste déroulante.
17. Utilisez ensuite le filtre pour afficher toutes les transactions où plus de 150 unités ont été
vendues.
Filtrer pour plus de 150 unités :
Sélectionnez la colonne "Quantité Vendue".
Allez dans le filtre numérique → Supérieur à → Entrez "150".
5. Tri :
18. Triez le tableau par Date de façon décroissante sans inclure la ligne du total.
Par Date :
Cliquez sur une cellule de la colonne "Date".
Allez dans l'onglet Données → Trier.
Sélectionnez Décroissant.
19. Triez ensuite le tableau par "Prix TTC" de façon décroissante.
Par Prix TTC :
Cliquez sur une cellule de la colonne "Prix TTC".
Répétez les mêmes étapes en choisissant Décroissant.
6. Graphiques:
20. Créez un graphique en colonnes qui affiche le Total TTC par date.
Graphique en colonnes (Total TTC par date) :
Sélectionnez les colonnes "Date" et "Prix TTC".
Allez dans l'onglet Insertion → Graphiques en colonnes.
Choisissez un modèle et personnalisez-le.
21. Créez un graphique en courbe qui montre l'évolution du "Prix TTC" par date.
Graphique en courbes (Évolution Prix TTC par date) :
Sélectionnez les mêmes colonnes.
Allez dans l'onglet Insertion → Graphiques en courbes.
Personnalisez le titre et les axes.
7. Analyse :
22. Quel est le produit le plus vendu en quantité ?
Sélectionnez votre tableau contenant les colonnes "Produit" et "Quantité Vendue".
Créez un tableau croisé dynamique :
Allez dans Insertion → Tableau Croisé Dynamique.
Placez-le dans une nouvelle feuille.
Configurez le tableau croisé dynamique :
Faites glisser Produit dans la zone "Lignes".
Faites glisser Quantité Vendue dans la zone "Valeurs".
Assurez-vous que la fonction d'agrégation est définie sur Somme.
Le tableau croisé dynamique affichera la somme totale des quantités vendues pour
chaque produit.
Identifier le produit avec la quantité maximale
Triez la colonne des quantités dans le tableau croisé dynamique en ordre
décroissant :
o Cliquez sur la flèche dans l'en-tête de la colonne "Quantité Vendue".
o Choisissez Trier du plus grand au plus petit.
Le produit en haut de la liste sera celui avec la plus grande quantité vendue.
23. Quelle est la date avec les revenus les plus élevés (Prix Total hors taxe) ?
Étape 1 : Identifier la valeur maximale de "Prix HT"
Sélectionnez toute la colonne contenant les valeurs "Prix HT" (exemple : colonne E).
Dans une cellule vide, utilisez la fonction suivante pour trouver le montant maximum :
=MAX(E2:E50) : Cette formule renvoie le montant maximal de la colonne
"Prix HT".
Étape 2 : Identifier la ligne correspondante
Utilisez la fonction EQUIV pour trouver la position de ce montant maximum :
=EQUIV(MAX(E2:E50), E2:E50, 0) : E2:E50 est la plage contenant les
valeurs "Prix HT". Cette formule renvoie le numéro de la ligne dans
la plage où se trouve le montant maximum.
Étape 3 : Extraire la date correspondante
Utilisez la fonction INDEX pour récupérer la date correspondant à cette ligne :
=INDEX(A2:A50, EQUIV(MAX(E2:E50), E2:E50, 0))
o A2:A50 est la plage contenant les dates.
o EQUIV donne la ligne, et INDEX renvoie la valeur de la date à cette ligne.
Résultat attendu
Avec les données fournies :
• La valeur maximale de "Prix HT" est 1 050 000,00 DH.
• Elle correspond à la date vendredi 6 janvier 2023.
24. Combien d'argent a été collecté en TVA durant cette période ?
Insérez une formule pour calculer directement la TVA pour la date spécifiée :
=SOMME.SI(A2:A47; INDEX(A2:A47;EQUIV(MAX(E2:E47);E2:E47;0)); F2:F47)
Résultat attendu
D'après les données fournies :
• Les lignes correspondantes pour vendredi 6 janvier 2023 incluent :
o Produit G : 210 000,00 DH de TVA.
o Produit A : 19 200,00 DH de TVA.
• Total TVA collectée :
210 000,00 + 19 200,00 = 229 200,00 DH.
8. Fonction SI:
Dans une nouvelle colonne "Remise", appliquez une remise de 10% sur le "Prix Total hors taxe en
Dhs " pour les transactions où plus de 175 unités ont été vendues. Sinon, la remise est de 0.
Ajoutez une colonne "Remise".
Dans la première cellule, entrez la formule : =SI(D2>175;E2*10%;0).
Recopiez la formule.
Ajoutez une colonne "Prix après remise".
Entrez la formule : =E2-H2 (où H2 est la remise calculée).
Recopiez pour les lignes restantes.
Consignes de rendu :
Sauvegardez et soumettez votre fichier contenant le tableau traité.
