TPs Excel
Pratique 0 : Rappels
1. Ajouter les données concernant la matière Informatique : 14-17-16
2. Ajouter les données concernant l’élève Frikha Wissem : 15-16-15.75-16.25
3. Modifier la largeur de la colonne C, D et E à 9 pts
4. Modifier la largeur de la colonne F à 15 pts
5. Déplacer le contenu de la cellule A1 vers C1, puis centrer par rapport aux colonnes C, D, E et F
6. Dans la cellule G2 Ecrire le texte « Somme »
7. Dans la cellule G3 faire la somme des notes de l’élève Ben Salah
8. Appliquer la même formule pour les autres élèves
9. Dans la cellule H2 saisir le texte « Moyenne »
[Link] la cellule H3 faites la moyenne des notes de l’élève « Ben Salah »
[Link] la même formule pour les autres élèves
[Link] Moyennes doivent êtres à deux chiffres après la virgule
[Link] la cellule A7 saisir le texte « MoyMax », Dans la cellule H7 afficher la moyenne Maximale, cellule couleur Verte
[Link] la cellule A8 saisir le texte « MoyMin », Dans la cellule H8 afficher la moyenne Minimale, cellule couleur Rouge
[Link] le titre Bulletin de Notes, taille 16, gras, couleur Bleu Foncé et centré par rapport au tableau, Hauteur de la ligne
30 pts
[Link] les libellés des colonnes, Mettre en gras taille 12, Ajouter une bordure au tableau
[Link] aux notes en dessous de la moyenne la couleur Rouge
Pratique 1 : Mise en forme générale et utilisation des formules de calculs
ETAT DES VENTES 31/12/2011
Prix Ventes Ventes Total HT Total HT Montant
Article unitaire Semestre 1 Semestre 2 Semestre 1 Semestre 2 TVA Total
Gocho 0.100 300 245
Club 0.500 50 75
Choco 1.575 100 130
Cake 1 400 344
Totaux
Meilleures
ventes
1. Appliquer les mises en forme suivantes :
– Titre principal : Centré par rapport au tableau, Rouge, 16, police Algerian
– Libellés : Centré, Rose, 10,
– Le reste : Centré, Bleu foncé,
– Format de la cellule Prix unitaire : Monétaire, 3 chiffres après la virgule, symbole TND
– Appliquer les mêmes mises en forme des cellules pour les colonnes Total HT Sem1, Total HT Sem 2,
Montant TVA et Total
2. Compléter le tableau en utilisant les formules suivantes :
Total HT Semestre 1 = Prix unitaire * Ventes Semestre 1
Total HT Semestre 2 = Prix unitaire * Ventes Semestre 2
Montant TVA = (Total HT Semestre 1 + Total HT Semestre 2) * 21%
Total = Total HT Semestre 1 + Total HT Semestre 2 + Montant TVA
Totaux = Total de chaque vente
Meilleures Ventes = Maximum (Total)
3. Représenter graphiquement en secteurs les Prix unitaire en fonction des Articles.
Mr. MANI Med Abdallah Lycée Pilote Sousse Page | 1
TPs Excel
Pratique 2 : Comment faire des calculs avec les heures et utilisation des formules de calculs
En "C2" écrivez la formule suivante:
=(B2*(MINUTE(A2)/60))+(B2*(HEURE(A2)))
Je multiplie d'un coté mon taux horaire "B2" par les minutes de "A2" qui
son divisé par 60 puis de nouveau "B2" par heures de "A2" et j'additionne
le tout pour obtenir le résultat.
Sélectionnez"A11" puis cliquez sur le menu Format puis Cellule
puis sur l'onglet Nombre dans la liste tout en bas cliquez sur
Personnalisée et dans la case Type écrivez [h]:mm (plus de limite de 24h)
et valider par Ok pour obtenir 79:05 .
Rappels :
1. La fonction RANG permet le classement de valeurs (donner un rang à une valeur par rapport à une référence
(Liste de valeurs)) Rang(Nombre ;Référence)
2. La fonction NB compte le nombre de cellules contenant des nombres et les nombres compris dans la liste des
arguments. NB(valeur1; [valeur2]; ...)
3. La fonction NBVAL compte le nombre de cellules qui ne sont pas vides dans une plage.
NBVAL(valeur1; [valeur2]; ...)
4. La fonction [Link] compte le nombre de cellules d’une plage qui répondent à un critère spécifique que vous
spécifiez. [Link](plage; critère)
5. La fonction [Link] permet de calculer la somme des valeurs d’une plage qui répond au critère spécifié.
[Link](plage;critère;[somme_plage])
Pratique 3 : Moyenne, Min, Max, NB, NBVAL, Rang
1. Calculer la moyenne des rendements
2. Calculer le maximum des rendements
3. Calculer le minimum de rendements
4. Calculer le nombre de personnes en utilisant la
fonction NB
5. Calculer le nombre de personnes en utilisant la
fonction NBVAL
6. Affecter un rang pour chaque ouvrier
Pratique 4 : NB, [Link], NBVAL
1. Calculer le nombre personnes
2. Calculer le nombre de personnes présentes
3. Calculer le nombre de personnes absentes avec NB
et NBVAL
4. Calculer le nombre de personnes absentes avec
[Link]
Mr. MANI Med Abdallah Lycée Pilote Sousse Page | 2
TPs Excel
Pratique 5 : [Link]
1. Calculer la quantité totale des livres dont le prix
d’achat est 5
2. Calculer la quantité totale des livres dont le prix
d’achat est 10
3. Calculer la quantité totale des livres dont le prix
d’achat est 15
Pratique 6 : Objectifs Utilisation des mises en forme conditionnelles et des formules conditionnelles
1. mettre les cellules en rouge pour les notes inférieures à 10, en Jaune pour les notes entre 10 et 15 et en Vert
pour les notes supérieures à 15
2. Faire le calcul de la moyenne en utilisant la fonction moyenne
3. Dans la colonne Résultats Ajouter le remarque Recalé si moyenne <8, Admis si moyenne >=10 et Oral si comprise
8<=moyenne<10
En E2 écrivez la formule suivante :=SI(D2<8;"Recalé";SI(D2>=10;"Admis";"Oral"))
Pratique 7 : Les formules Conditionnelles
Client Date Facturation Date Paie Montant Facture Remise Autorisée Montant Net
Mohamed 27/11/2009 01/01/2010 1 256,000 TND
Anis 15/12/2009 01/01/2010 5 200,000 TND
Ali 28/12/2009 01/01/2010 18 269,000 TND
Imen 18/11/2009 01/01/2010 5 896,000 TND
Amira 01/01/2008 01/01/2010 850,000 TND
1. Calculer le montant de la remise qui est égale à 3% du montant de la facture si la différence entre la Date de
Facturation et la Date de Paie <= 30 Jours
2. Calculer le Montant Net = Montant Facture – Remise Autorisée
Pratique 8 : Les filtres de données sur Excel
Titre Prénom Nom Adresse Code Postal Ville
Monsieur Samir Amor 15 rue de mars 5481 Tunis
Monsieur Ali Ben Ahmed 6 rue du peuple 5400 Sousse
Madame Nouha Ben Ali 28 rue de la glace 5436 Tunis
Monsieur Salim Slama 2 rue des coiffeurs 5412 Gbeli
Monsieur Amir Dhieb 59 rue des rougers 5520 Monatir
Madame Imen Akkari 28 rue de la glace 5436 Tunis
Madame Sana Ben Amor 74 rue du canal 5420 Msaken
Madame Hana Korbi 6 impasse des iles 6700 Sousse
Madame Wissal Kati 90 rue des pistes 8347 Sfax
Monsieur Ahmed Ktita 75 Bd du Mexique 5700 Béja
Mr. MANI Med Abdallah Lycée Pilote Sousse Page | 3
TPs Excel
1. Sélectionnez la ligne des titres des colonnes
2. Cliquer sur Données puis Filtrer et Filtre automatique.
A droite de chaque titre apparaît une flèche qui dans le menu déroulant permet de faire des choix pour filtrer des
éléments de la colonne
On peut appliquer plusieurs filtres en même temps
Si vous voulez appliquer plusieurs filtres et il faut supprimer les anciens avant d’insérer les nouveaux critères
3. Appliquer les filtres suivants :
Filtre1 : Afficher uniquement les Femmes
Filtre2 : Afficher les personnes qui habitent à Tunis
Filtre3 : Afficher les dames qui habitent à Tunis
Filtre4 : Afficher les personnes qui un code postal entre 5400 et 5700
Les filtres élaborés
Objectif : Vous désirez conserver les hommes qui on un nom qui commence par S et A.
1. Créer un tableau de critère en recopiant les titres des colonnes du premier tableau en dessous ou sur une autre feuille,
suivi des critères de filtrage.
2. Cliquer sur Données puis Filtrer et Filtre élaboré.
Il y a deux possibilités soit la liste est filtrer sur place
(dans le tableau de départ) ou vers un autre emplacement
qu’il faudra renseigné dans le champ Copier dans :.
Il faut sélectionner la zone de critères (Deuxième tableau)
Pratique 9 : Fonctions avancées : [Link] et [Link]
1. Calculer le total des ventes
STATISTIQUES DE VENTES 2. Dans le tableau 2 Calculer le nombre de
Montant Nombre Montant ventes par département
Département vente de Ventes Total Il s'agit de compter, dans la plage de
Indre et Loire 1 500,00 € Indre cellule A3:A18, le nombre de fois où
Loiret 200,00 € Indre et Loire apparaît la chaîne de caractères "Indre"
Indre 3 620,00 € Cher figurant en D3.
Cher 2 500,00 € Loir et Cher La syntaxe générale est la suivante :
Loir et Cher 6 200,00 € Loiret =[Link](plage_de_cellules;critère)
Eure et Loir 3 600,00 € Eure et Loir 3. Dans la colonne Montant Total Calculer le
Indre et Loire 1 400,00 € montant de vente par département en
Cher 1 900,00 € utilisant la Fonction [Link]
Loir et Cher 5 800,00 € Additionner les valeurs figurant dans la
Loiret 5 600,00 € plage B3:B18, quand le contenu d'une
Indre 5 500,00 € cellule de la plage nommée tableau est
Indre et Loire 8 500,00 € "Indre" figurant en D3.
Loir et Cher 2 580,00 € La syntaxe générale est la suivante
Loiret 7 500,00 € =[Link](plage_de_cellules;critè
re;plage_à_totaliser)
Eure et Loir 2 680,00 €
Indre 2 570,00 €
Attention : la plage à totaliser doit être déclarée
en références absolues pour que la recopie vers le
bas fonctionne correctement.
Mr. MANI Med Abdallah Lycée Pilote Sousse Page | 4