TP N° 5 : LA PROTECTION DU DOCUMENT

1. Lancer Excel et nommer la feuille1 "Bulletin de paie".

2. Sauvegarder le classeur sous le nom "Bulletin de paie".
3. Réaliser le travail suivant :

4. Mettre le tableau en forme suivante (utiliser les outils mise en forme) : police : « Comic Sans
MS » ; taille : 12 pt ; couleur : orange ; bordure : conteur extérieur.
5. Saisir les données suivantes : Fès, Rabat, Casa, Tanger, Agadir dans la plage M4 :M8
6. Sélectionner les cellules M4 : M8.
7. Cliquer sur le bouton droit→Définir un nom→Saisir le nom Ville, puis cliquer sur OK. Ou bien
sous l’onglet Formule cliquer sur « Définir un nom »
➢ La liste de choix : La liste est un moyen qui permet de contrôler et de valider les
données :
8. Créer une liste de choix sur la ville : Sélectionner la cellule C7 →sous l’onglet Données /
Validation des données →Une boite de dialogue avec trois onglets s’affiche
✓ Le 1er onglet : Options
▪ Cliquer sur Autoriser et sélectionner Liste
▪ Cliquer sur source et cliquer sur la touche de fonction F3.
▪ Sélectionner le nom Ville dans la liste des noms et cliquer sur OK.
▪ Décocher l’option Ignorer si vide.
✓ Le 2ème onglet : Message de saisie
▪ Passer au 2ème onglet pour mettre un message qui sera affiché quand la cellule est
sélectionnée.
▪ Dans titre, écrire Message
▪ Dans Message de saisie, saisissez « Sélectionnez une ville ».
✓ Le 3ème Onglet : Alerte d’erreur
▪ Passer au dernier onglet pour écrire le message qui sera affiché si vous écrivez une ville
n’existant pas dans la liste.
▪ Le style est par défaut Arrêt.
▪ Dans le titre saisissez « Erreur ».
▪ Dans le message d’erreur, saisissez « Cette ville n’existe pas »

Professeur Hamid MANSOUR Page 37 sur 46

 ▪ Cliquer sur OK.
▪ Sélectionner une ville dans la liste.
▪ Maintenant Saisir une ville qui n’existe pas dans la liste et Tapez Entrée.
9. Créer une liste de choix sur la fonction :
✓ Saisir les données suivantes : « Ouvrier, Comptable, Commerçant, Secrétaire et cadre »
dans la plage des cellules N4 :N8.
✓ Nommer les données saisies Fonction
✓ Attribuer à la cellule C11 les données « Fonction ».
✓ Dans message de saisie, saisir le message : « Sélectionner une fonction ».
✓ Dans Alerte d’erreur, saisissez « Cette fonction n’est pas disponible »
10. Calculer le taux d’IGR
On suppose que si le Salaire Brut > 2500, le taux d’IGR = 13%. Sinon, il sera égal à 0% :
11. Calculer l’IGR
L’IGR = Salaire But * Taux d’IGR
12. Calculer le Salaire Net
Le Salaire Net = Salaire Brut – IGR - CNSS
➢ Protéger le classeur
13. Pour protéger le classeur cliquer sur le menu Fichier→Protéger le classeur→protéger la
structure du classeur, taper le mot de passe.
➢ Pour ôter la protection du classeur :
14. Pour ôter la protection du classeur cliquer sur le menu Fichier→Protéger le classeur→protéger
la structure du classeur, retaper le mot de passe déjà saisi.
➢ Protéger la feuille
15. Pour protéger la feuille cliquer sur le menu Fichier→Protéger le classeur→ protéger la feuille
active, taper le mot de passe.
➢ Pour ôter la protection du classeur :
16. Pour ôter la protection de la feuille cliquer sur le menu Fichier→dans « Protéger le classeur »
cliquer sur « ôter la protection » et retaper le mot de passe déjà saisi.
➢ Déverrouiller une cellule
Vous voulez maintenant protéger toute la feuille, mais laissez la liste de la ville non protégée. Il
faut donc déverrouiller la cellule avant la protection de la feuille :
17. Sélectionner la cellule C7, sous l’onglet Accueil, cliquer sur Format → verrouiller la cellule.
18. Modifier le contenu de la cellule C7.
19. Essayer avec les autres cellules.
➢ Protéger tout le fichier
20. Pour protéger le fichier cliquer sur le menu Fichier→Protéger le classeur→chiffre avec mot de
passe, taper le mot de passe.
21. Enregistrer le classeur et fermer-le.
22. Ouvrir le classeur de nouveau et saisir le mot de passe dans la boite de dialogue qui s’affiche et
cliquez sur OK.

Professeur Hamid MANSOUR Page 38 sur 46

 TP N° 6 : LES GRAPHIQUES
1. Lancer Excel et nommer la feuille1 " Graphiques ".
2. Sauvegarder le classeur sous le nom " Graphiques ".
3. Réaliser le travail suivant :

4. Renvoyer à la ligne automatiquement les cellules A5 : G5

Dans cet exercice vous allez insérer trois graphiques :
✓ Variation de stock
✓ Évolution des prix
✓ Évolution des quantités
1 graphique : Variation de stock
er

5. Insérer un graphique de type Secteurs 3D, en sélectionnant les plages des cellules A5 :A10 et
D5 :D10, attribuer un titre « Variation de stock », afficher la légende en bas du graphique,
afficher les pourcentages des séries.
6. Déplacer le graphique sous le tableau et redimensionner-le.
7. Sous l’onglet création choisir un style de votre choix pour le graphique.
2ème graphique : Évolution des prix
8. Insérer un graphique de type Courbes, en sélectionnant les plages des cellules A5 :A10 et
F5 :G10, attribuer le titre au graphique « Évolution des prix », dans la zone titre de l’axe
horizontal principal écrire «Produits», dans la zone titre de l’axe vertical principal écrire
« Valeurs ».
3ème graphique : Évolution des quantités
Le troisième graphe que vous allez créer permet la comparaison entre les quantités vendues et les
quantités achetées.
9. Insérer un graphique de type histogramme 3D, en sélectionnant les plages des cellules A5 :C10
et, attribuer le titre au graphique « Évolution des quantités », dans la zone titre de l’axe
horizontal principal écrire «Produits», dans la zone titre de l’axe vertical principal écrire
« Valeurs ».
Ajouter des données dans le graphe :
Vous voulez ajoutez des nouvelles données dans le graphe sans refaire le graphique :
Sélectionner la cellule A11 et saisir les données suivantes :
Imprimante 1500 700 800 300 500 700
10. Cliquer sur la zone du graphique du 1 graphique (Secteur) et étendre la sélection aux cellules
er

A11 et D11, Regarder dans le graphe pour vérifier si les données sont insérées.
11. Faire la même chose pour ajouter les données dans les deux autres graphiques.

Professeur Hamid MANSOUR Page 39 sur 46

 TP N° 7 : LA FONCTION DE RECHERCHEV
Pour illustrer l’utilisation de la fonction recherchev (), nous allons travailler sur un exemple.
Il s’agira d’élaborer une facture.

Pour cela on va commencer par créer un classeur nommé "Gestion des factures" composé de
trois feuilles :
▪ L’onglet Feuil1 sera renommé « Produits».
▪ L’onglet Feuil2 sera renommé « Clients».
▪ L’onglet Feuil3 sera renommé « Facture ».
1- Sur la feuille « Produits » créer le tableau suivant :

2- Sur la feuille « Clients » créer le tableau suivant :

3- Sur la feuille « Facture » et créer la facture suivante :

➢ Renommer les cellules :

Pour construire notre facture, nous allons utiliser les fonctions de recherche
d’Excel. Ces fonctions vont faire référence à des plages de cellules contenant les
données nécessaires.
La première plage de cellules qui nous intéresse, est celle contenant les produits.

Professeur Hamid MANSOUR Page 40 sur 46

 4- Cliquer sur l’onglet (feuille) « Produits ». nommer la plage de cellules A4 : C8 par le nom
« Produit ».
5- Cliquer sur l’onglet (feuille) « Clients ». nommer la plage de cellules A5 : D9 par le nom
« Client ».
➢ Fonction de RECHERCHEV
Nous voulons maintenant remplir notre facture en cherchant les données des autres feuilles. Par
exemple chercher le client :
6- Cliquer sur l’onglet (feuille) « Facture », Sélectionner la cellule E6, cliquer sur dans la
barre de formule, dans la catégorie de fonction sélectionner Recherche & Matrices et
Sélectionner la fonction RECHERCHEV et valider par OK.
La fonction de RECHERCHEV contient quatre paramètres :
✓ Valeur_cherchée : c’est la valeur avec laquelle on cherche. Pour notre exemple c’est le N°client.
Sélectionner donc la cellule C6.
✓ Table_matrice : c’est la table dans laquelle on cherche. Appuyer sur la touche F3 et sélectionner
le nom « client ».
✓ No_index_col : c’est le numéro de la colonne de la table qui contient l’information cherchée.
Saisir 2.
✓ Valeur_proche : prend deux valeurs :
o Vrai : vous acceptez une valeur proche en cas ou l’information cherchée n’existe pas.
o Faux : vous exigez la valeur exacte.
7- Saisir Faux dans la valeur proche, cliquer enfin sur le bouton OK. Automatiquement le nom
client est renvoyé dans la cellule E6.
8- Chercher maintenant la Ville et le Tél.
9- Changer la valeur de la cellule C6. Qu’est-ce que vous remarquez ?
10- Vous allez rechercher maintenant la désignation :
✓ Valeur_cherchée : c’est la référence. Sélectionnez donc la cellule A10.
✓ Table_matrice : On cherche dans la table Produit. Appuyer sur la touche F3 et sélectionner le
nom « produit».
✓ No_index_col : la désignation se trouve dans la colonne N°2. Saisir donc 2.
✓ Valeur_proche : saisir faux dans la valeur proche
✓ Cliquer enfin sur le bouton OK.
11- Glisser la formule pour avoir le résultat dans les autres cellules.
12- Par la même procédure, vous allez rechercher le prix unitaire de chaque produit. Ici, vous
devez changer le No_index_col qui deviendra 3.
Nous voulons maintenant remplir notre facture en cherchant les données des autres feuilles. Par
exemple chercher le client :
13- Sélectionner la cellule E10 et insérer la fonction PRODUIT pour le calculer le Montant total.
14- Copier la même formule vers les autres cellules.
15- Sélectionner la cellule E14 et insérer la fonction SOMME pour calculer le Total HT.
16- Sélectionner la cellule E15 et insérer la fonction PRODUIT pour calculer la TVA.
17- Sélectionner la cellule E16 et insérer la fonction SOMME pour calculer le Total TTC.

Professeur Hamid MANSOUR Page 41 sur 46

 TP N° 8 : LES BASE DE DONNEES
Définition :
Une base de données est un ensemble d’informations associées à un sujet particulier tel que la
gestion de stock, la gestion d’une école…etc.
Gérer une base de données signifie les opérations suivantes :
✓ Ajouter des données.
✓ Modifier des données
✓ Supprimer des données
✓ Rechercher des données
Comment gérer une base de données sous Microsoft Excel ?
Dans cet exercice, vous allez travailler sur une base de données « Gestion personnel ».
Il s’agit d’une société qui veut gérer son personnel en utilisant l’Excel.
1. Lancer Excel et nommer la feuille1 "Personnel".
2. Sauvegarder le classeur sous le nom "Gestion personnel".
3. Réalisez le travail suivant :

Ajouter des données

Pour gérer les données dans la base :
4. Cliquer sur la base de données (Par exemple la cellule C5).
5. Ajouter le bouton "Formulaire" à la barre d’outils accès rapide et cliquer dessus et le formulaire
s’affiche.
6. Cliquer sur Suivante pour passer à la fiche suivante, et sur Précédente pour revenir au
précédent.
7. Cliquer sur Nouvelle. Le curseur se place dans la première zone. Saisir les données suivantes :

8. Pour rechercher une personne, cliquer sur Critère, dans la zone Matricule entrer 4.
9. Cliquer sur Grille et cliquer sur Suivante. L’enregistrement numéro 4 s’affiche.
10. Pointer sur le matricule numéro 7 et modifier son salaire (5500).
11. Rechercher maintenant tous salariés mariés.
12. Cliquer enfin sur Fermer pour fermer le formulaire.
Trier les données dans la base
13. Sélectionner une cellule dans la base→ Cliquer sur Données / Trier. Dans la boite de dialogue
des critères s’ouvre, Cliquer sur la zone du 1er critère et choisir Nom prénom. sélectionner
l’ordre Décroissant (de Z à A)→Cliquez sur OK.

Professeur Hamid MANSOUR Page 42 sur 46

14. Trier maintenant votre base selon le Salaire par ordre Croissant (de plus petit au plus grand).
Filtrer des données
15. Sélectionner une cellule dans la base→Cliquez sur Données / Filtrer. Des flèches apparaissent
dans chaque champ :
16. Cliquer sur le champ État civil et sélectionner Célibataire pour afficher juste les salariés
célibataires.
17. Cliquer de nouveau sur le champ État Civil et sélectionner Tous pour afficher toutes les
données.
18. Cliquer sur le champ Fonction et sélectionner Commerçant pour afficher juste les salariés
commerçants.
19. Afficher tous les enregistrements.
20. Afficher maintenant tous les salariés dont le salaire varie entre 4000 et 6000 (utiliser un filtre
personnalisé).
Filtre élaboré
21. Sélectionner la cellule M4 et saisir le tableau de critère suivant :
Matricule Fonction
1 Commerçant
22. Nommer le tableau Critère.
23. Pointer dans votre base et cliquer sur l’onglet Données, dans la zone Trier et Tiltrer cliquer sur
Avancé. Puis cliquer dans la Zone de critère, appuyer sur la touche F3 du clavier, sélectionner
Critère et cliquez sur OK. Les données répondant aux critères sont sélectionnées
automatiquement dans la base.
24. Pour afficher toutes les données, cliquez sur Effacer de la zone Trier et Filtrer.
Créer des Sous-totaux
Les Sous-totaux permettent de grouper les données selon un critère :
1er exemple : Créer des Sous-totaux selon l’état civil :
25. Trier d’abord les données selon l’État civil.
26. Cliquez sur Données / Sous-totaux. Dans la boite de dialogue qui s’ouvre :
✓ Dans A chaque changement de, sélectionnez État civil.
✓ Utilisez la fonction Somme.
✓ Ajoutez un sous-total au Salaire.
✓ Cliquez sur OK.
Pour réduire l’affichage des données, cliquer sur le signe – à gauche du tableau.
Pour Développer l’affichage des données, cliquer sur le signe +
2ème exemple : Créer des Sous-totaux selon la fonction :
27. Supprimer d’abord les sous totaux en cliquant sur Données / Sous-totaux, puis cliquer sur le
bouton Supprimer Tout.
28. Trier d’abord les données selon la fonction.
29. Cliquer sur Données / Sous-totaux. Dans la boite de dialogue qui s’ouvre :
✓ Dans A chaque changement de, sélectionner Fonction.
✓ Utilisez la fonction Somme.
✓ Ajoutez un sous-total au Salaire.
✓ Cliquez sur OK.

Professeur Hamid MANSOUR Page 43 sur 46

 TP N° 9 : LE TABLEAU CROISE DYNAMIQUE
Définition : Le tableau croisé dynamique est un outil qui permet d’analyser les données.
➢ Créer une liste personnalisée
1. On va créer une liste qui contient les éléments suivants :
Ordinateur, Imprimante, DVD
✓ Sélectionnez l’onglet Fichier dans le Ruban, puis cliquez sur Options.
✓ Sélectionnez Options avancées dans la partie gauche de la boîte de dialogue.
✓ Sous Général, cliquez sur Modifier les listes personnalisées. Définissez la nouvelle
liste dans la zone Entrée de la liste, en prenant le soin de séparer les entrées de la
liste par une virgule. Cliquez sur Ajouter puis sur OK.
2. Nommez la feuille1 « Vente ».
3. Créez la base de données suivante :

4. Dans la cellule D5 tapez « Ordinateur », puis utilisez la poigné de recopie pour la plage de
cellule A6 :A13.
5. Trier la base de données
Trier votre base selon le mois, dans l’option ordre choisissez « liste personnalisée » et
sélectionnez la liste de mois.
6. Filtrez les données
✓ Affichez les ventes du mois janvier.
✓ Affichez les ventes de SLIMANI Ali
✓ Affichez les ventes des ordinateurs du mois janvier
7. Sous-totaux
✓ Créez des sous-totaux selon le mois.
✓ Créez des sous-totaux selon le représentant
✓ Créez des sous-totaux selon le produit.
8. Le tableau croisé dynamique
✓ Sélectionnez une cellule de votre base
✓ Cliquez sur Insertion / TblCroiséDynamique.
✓ Dans la fenêtre affichée à droite :
o Faites glisser le champ Mois dans étiquettes de lignes.
o Faites glisser le champ Produit dans étiquettes de colonnes.
o Faites glisser le champ Représentant dans étiquettes de lignes du tableau.
o Faites glisser le champ Montant dans valeurs.
Voilà le résultat que vous devez obtenir :

Professeur Hamid MANSOUR Page 44 sur 46

9. Mettre en forme le tableau
✓ Cliquez sur le rapport dans n’importe quelle cellule.
✓ Dans l’onglet « Création », choisir le style « Style de tableau croisé dynamique foncé 6 ».
10. Filtrer le tableau
✓ Cliquez dans le champ étiquettes de lignes, sélectionnez le champ « MOIS » :

✓ Décochez tout, et cochez seulement le mois Janvier.

✓ Réafficher tous les enregistrements
11. Représenter le tableau
✓ Cliquez sur le rapport dans n’importe quelle cellule.
✓ Sous l’onglet « Options » choisissez l’outil « Graphique croisé dynamique » et insérez un
graphe de type Histogramme.

ATELIER
OBJECTIFS
- Se familiariser avec MS Excel
- Comprendre la notion de filtre sous MS Excel
- Réaliser des filtres sur des données sous MS Excel : filtre automatique et filtre élaboré
ACTIVITÉS

Liste des participants à la compétition de surf

N° Nom Prénom Age Sexe Ville
1 KACHLOUL Hassan 16 M Rabat
2 MRABET Salwa 19 F Essaouira
3 ZAHOURI Ali 18 M Casablanca
4 SEBTI Rachid 35 M Marrakech
5 EDDAHIR Noureddine 25 M Agadir
6 EL HILALI Khalil 27 M Rabat
7 RIZKI Laila 21 F Tanger
8 MOUJAHID Samir 19 M Essaouira

Professeur Hamid MANSOUR Page 45 sur 46

 8 RACHACH Hamid 50 M Essaouira
9 BAKIZ Fatima 22 F Oujda
10 FOUHAMI Khalid 29 M Casablanca
11 GHAZOUANI Mohamed 25 M Rabat
12 SAIDI Fatiha 26 F Casablanca
13 SOUMALI Fadwa 23 F Casablanca
15 CHOURI Hassna 26 F Agadir
16 EL HSAINI Najlaa 30 F Marrakech
17 OUARDI Raja 14 F Casablanca

Affichez (Après reproduction des données sous Excel) :

➢ Les participants hommes

➢ Les participants de la ville Essaouira
➢ Les participants hommes de la ville Essaouira
➢ Les participants qui ont 26 ans
➢ Les participants dont, l'âge est supérieur à 26 ans
➢ Les participants dont, l'âge est entre 20 et 25 ans
➢ Les participants qui ont 26 ans et ceux qui ont 30 ans
➢ Les participants dont, l'âge est supérieur à 26 ans et qui habitent à la ville d'Essaouira
➢ Les participants dont, le nom commence par M
➢ Les participants dont le nom commence par la lettre M ou la lettre E
➢ Les participants dont le nom commence par la lettre E et qui ont l'âge entre 16 et 20 ans,
➢ Les participants qui ont 20 ou 27 ou 30 ans
Les participants dont le nom

Professeur Hamid MANSOUR Page 46 sur 46