TP1: la mise en forme des cellules et insertion des formules :
A
|
B
|
C
|
D
|
E
|
F
|
1
|
Notes des
élèves
|
||||
2
|
Nom
|
Arabe
|
Math
|
Anglais
|
Sc. Physique
|
3
|
Hassan
|
12,5
|
10,5
|
14
|
13,75
|
4
|
Salah
|
8,5
|
4,5
|
10
|
9,25
|
5
|
Malika
|
17
|
18,5
|
16
|
15
|
- Ajouter les données concernant la matière Informatique : 14-17-16
- Ajouter les données concernant l’élève Fahd : 15-16-15.75-16.25
- Modifier la largeur de la colonne C, D et E à 9 pts
- Modifier la largeur de la colonne F à 15 pts
- Dans la cellule G2 Ecrire le texte « Somme »
- Dans la cellule G3 faire la somme des notes de l’élève Hassan
- Appliquer la même formule pour les autres élèves
- Dans la cellule H2 saisir le texte « Moyenne »
- Dans la cellule H3 faites la moyenne des notes de l’élève « Hassan »
- Appliquer la même formule pour les autres élèves
- Les Moyennes doivent êtres à deux chiffres après la virgule
- Dans la cellule A7 saisir le texte « MoyMax », Dans la cellule H7 afficher la moyenne Maximale, cellule couleur Verte
- Dans la cellule A8 saisir le texte « MoyMin », Dans la cellule H8 afficher la moyenne Minimale, cellule couleur Rouge
- Ajouter le titre Bulletin de Notes, taille 16, gras, couleur Bleu Foncé et centré par rapport au tableau, Hauteur de la ligne 30 pts
- Centrer les libellés des colonnes, Mettre en gras taille 12, Ajouter une bordure au tableau
- Affecter 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
|
|||||||
Article
|
Prix unitaire
|
Ventes Semestre
1
|
Ventes Semestre
2
|
Total HT
Semestre 1
|
Total HT
Semestre 2
|
Montant 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.
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]; ...)
- La fonction NBVAL compte
le nombre de cellules qui ne sont pas vides dans une plage.
ð NBVAL(valeur1;
[valeur2]; ...)
- La fonction NB.SI compte
le nombre de cellules d’une plage qui répondent à un critère spécifique
que vous spécifiez. è
NB.SI(plage;
critère)
- La fonction SOMME.SI permet
de calculer la somme des valeurs d’une plage qui répond au
critère spécifié.
ð SOMME.SI(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, NB.SI,
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 NB.SI
|
Pratique 5 : Somme.SI
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
|
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 : NB.SI et SOMME.SI
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
1. Calculer le total des ventes
2. Dans le tableau 2 Calculer le nombre de ventes par département
Il s'agit de compter, dans la plage de cellule A3:A18, le nombre de fois où apparaît la chaîne de caractères "Indre" figurant en D3.
ð La syntaxe générale est la suivante : =NB.SI(plage_de_cellules;critère)
3. Dans la colonne Montant Total Calculer le montant de vente par département en utilisant la Fonction SOMME.SI
Additionner les valeurs figurant dans la plage B3:B18, quand le contenu d'une cellule de la plage nommée tableau est "Indre" figurant en D3.
ð La syntaxe générale est la suivante =SOMMME.SI(plage_de_cellules;critère;plage_à_totaliser)
Attention : la plage à totaliser doit être déclarée en références absolues pour que la recopie vers le bas fonctionne correctement.
This is dummy text. It is not meant to be read. Accordingly, it is difficult to figure out when to end it. But then, this is dummy text. It is not meant to be read. Period.
ConversionConversion EmoticonEmoticon