6.4 Si, Et, Ou
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View 6.4 Si, Et, Ou as PDF for free.
More details
- Words: 8,717
- Pages: 40
TABLEUR 416491847
SI
Présentation SI La fonction SI permet d’afficher un résultat différent en fonction du contenu d’une autre cellule. La syntaxe utilisée est la suivante : =SI (condition à évaluer ; valeur si la condition est VRAIE; valeur si la condition est FAUSSE) Le résultat de la formule peut être une valeur numérique, une formule de calcul ou du texte. Dans les conditions à évaluer vous pouvez utiliser les opérateurs suivants : Égal = Supérieur à > Inférieur à < Supérieur ou égal à >= Inférieur ou égal à <=
Exemples 1. Si l’élève a 18 ans ou plus, alors il est majeur, sinon il est mineur. La formule en C1 sera : =SI(B1>=18;«majeur»;«mineur») B1 représente la cellule qui contient l’âge de l’élève. Le texte se met entre guillemets. Que se passetil si vous remplacez 19 par 17 ? par 18 ?
=SI(B1>=18;"majeur";"mineur") A B C 1 Age : 19 majeur 2 3
2. Si le montant de la facture est supérieure à 200 €, alors vous avez une remise de 3%, sinon vous n’avez pas de remise La formule en B2 du calcul de la remise sera : =SI(B1>200;B1*3%;0) B1 est la cellule qui contient le montant de la facture. Que se passetil si vous remplacez 200 par 100 ? par 300 ?
=SI(B1>200;B1*3%;0) A 1 Montant brut 2 Remise 3
B
C 300 9
APPRENDRE SUR INTERNET http://www.excelexercice.com/ **** Fonction > Condition > Fonction SI 416491847
P. COULAUD- 18/12/2013
1 / 40
TABLEUR 416491847
SI
Utiliser des références relatives et absolues Insérer les fonctions : SOMME, SI
Exercice 1 LAVOIE (3)
FICHE DE DÉCOMPTE DES HEURES
AVRIL
Nom : LAVOIE Heures normales : 35 Heures à +25% : de 36h à 43h SEMAINES
Nb d'heures
HS + 25%
Semaine 1
37
2
Semaine 2
35
Semaine 3
38
3
Semaine 4
41
6
151
11
TOTAUX
Travail à faire 1. Ouvrir OU créer le classeur MICROCD_ Nom; puis créer OU modifier la feuille nommée LAVOIE 2. Saisir les données du tableau, si ce n'est déjà fait 3. Calculer les heures supplémentaires à +25% (n'hésitez pas à utiliser l'AIDE) exemple : =SI($C$5
416491847
P. COULAUD- 18/12/2013
2 / 40
Exercice 2 CRAYON SI simple
Stocks Qté commandée
Qté Disponible
Qté livrée
Reste à livrer
Stylo bille
50
45
45
5
Stylo à plume
30
32
30
0
Gomme
37
52
37
0
Crayon couleur
12
15
12
0
Crayon papier
75
49
49
26
Pièces
Travail à faire 1. Créer le classeur CRAYON _Nom et la feuille COMMANDE 2. Saisir les données du tableau 3. Mettre en forme 4. Réaliser les formules (Insertion > fonction > SI) et utiliser l'outil de recopie de formules : a) calcul de la quantité livrée b) calcul du reste à livrer 5. Enregistrer et faire un aperçu
416491847
P. COULAUD- 18/12/2013
3 / 40
TABLEUR 416491847
MOYENNE MIN MAX SI
Utiliser des références relatives et absolues Insérer les fonctions : MOYENNE, MIN, MAX, SI
Exercice 3 CADO Vous travaillez au service commercial de la société CADO qui commercialise des cadeaux d'affaires. Huit représentants prospectent dans la région Ouest. Une prime de 3% des ventes d'un vendeur est attribuée à ceux dont les ventes trimestrielles sont supérieures à la moyenne trimestrielle. Vous êtes chargés de concevoir un tableau qui permette de calculer • le total des ventes par vendeur • le total des ventes par mois ainsi que la moyenne, le minimum et le maximum • le total, la moyenne, le minimum et le maximum du trimestre • la prime trimestrielle de chaque représentant
ANALYSE DES VENTES : 1er trimestre Vendeurs LAPORTE BOSSE MARTIN PRÉAU DUTEIL FOREST BLONDEL PINAULT Totaux Moyenne Ventes minimum Ventes maximum
janvier 6 200 2 200 3 700 6 250 2 250 2 150 3 700 3 900 30 350 3 794 2 150 6 250
février 5 900 3 050 3 900 6 060 2 240 3 240 3 300 4 500 32 190 4 024 2 240 6 060
mars 4 550 3 300 4 200 6 800 3 300 2 950 5 900 4 200 35 200 4 400 2 950 6 800
Totaux 16 650 8 550 11 800 19 110 7 790 8 340 12 900 12 600 97 740 12 218 7 790 19 110
Prime 500
573
387 378 1 838 459 378 573
Travail à faire 1. Créer le classeur sous CADO_Nom et la feuille TRIMESTRE_1 2. Saisir les données du tableau 3. Réaliser les formules et utiliser l'outil de recopie de formules a) calcul de la moyenne par mois : fonction MOYENNE b) calcul des minimum et maximum par mois : MIN et MAX c) calcul de la prime : fonction SI 4. Mettre en forme : a) titre en Arial 14, gras, centré b) hauteur des lignes augmentée pour aérer la tableau 5. Enregistrer et faire un aperçu 6. Faire un diagramme en secteur des ventes trimestrielles de chaque commercial en affichant le pourcentage sur le diagramme et la légende à coté
416491847
P. COULAUD- 18/12/2013
4 / 40
Répartition des ventes trimestrielles par représentant 12.89%
17.03%
LAPORTE BOSSE 13.20% MARTIN PRÉAU DUTEIL FOREST
8.75%
8.53%
12.07% 7.97% 19.55%
416491847
P. COULAUD- 18/12/2013
5 / 40
Exercice 4 RAPPEL SI L’entreprise RAPPEL décide de créer un échéancier de suivi des factures. Une lettre de rappel sera envoyée si aucun règlement n'est parvenu au plus tard 8 jours après la date d’échéance.
DATE DU JOUR :
19/03/66 Date d'échèance 18/02/66
Compte à rebours 0
Lettre de rappel
Olivier
Date de facture 08/02/66
PA1013
Paul
08/02/66
18/02/66
29
Oui
QU1014
Quentin
08/02/66
25/02/66
0
RO1015
Roll
09/02/66
25/02/66
22
Oui
SO1016
Soul
09/02/66
28/02/66
19
Oui
TR1017
Trin
09/02/66
28/02/66
0
02/03/66
UR1018
Urban
10/02/66
12/02/66
0
14/02/66
VA1019
Vaux
10/02/66
28/02/66
19
WI1020
Wisty
10/02/66
12/02/66
0
15/02/66
XA1021
Xan
10/02/66
28/02/66
0
02/03/66
N° facture
Client
OL1012
Date de règlement 06/02/66 15/02/66
Oui
Travail à faire 1. Créer le classeur RAPPEL_ Nom et créer la feuille LR (lettre de rappel) 2. Saisir les données du tableau 3. Effectuer les calculs des colonnes : a) compte à rebours entre la date du jour et la date d'échéance b) lettre de rappel : laisser un délai de 8 jours après l'échéance avant de faire la lettre. 4. Enregistrer ; faire un aperçu
416491847
P. COULAUD- 18/12/2013
6 / 40
Exercice 5 EVAL 1 SI – MOYENNE – MIN – MAX Concevoir un tableau comme le modèle cidessous. Information : Le montant des coûts correspond à 65 % du C.A., et le pourcentage peut évoluer à tout moment. Une observation apparaît lorsque le résultat mensuel est strictement supérieur à 50 000 €. Travail à faire 1. Créer le classeur EVAL_ Nom et créer la feuille CA 2. Saisir les données du tableau 3. Effectuer les calculs suivants : a) Coût 65% = CA * 65% = B5*$C$4 b) Résultat = CA – Coût = B5C5 4. Calculer les totaux, la moyenne du résultat, le plus fort et le plus faible des résultats 5. Colonne observation : si le résultat est supérieur à 50 000 € alors le résultat mensuel est correct, ce qui donne la formule =SI(D6>50000;"Résultat mensuel correct";"") 6. Mettre en forme et en page le tableau 7. Réaliser un graphique en secteur représentant le pourcentage de chaque mois 8. Enregistrer; faire un aperçu
Analyse du chiffre d'affaires de l'année N CA HT Janvier Février Mars Avril Mai Juin Juillet Août Septembre Octobre Novembre Décembre TOTAL
125 000 147 000 156 000 123 000 154 000 125 000 110 000 178 000 177 000 90 000 162 000 220 000 1 767 000
Coût 65% 81 250 95 550 101 400 79 950 100 100 81 250 71 500 115 700 115 050 58 500 105 300 143 000 1 148 550
Résultat 43 750 51 450 54 600 43 050 53 900 43 750 38 500 62 300 61 950 31 500 56 700 77 000 618 450
Résultat moyen Résultat le plus fort de l'année Résultat le plus faible de l'année
416491847
P. COULAUD- 18/12/2013
Observation Résultat mensuel correct Résultat mensuel correct Résultat mensuel correct
Résultat mensuel correct Résultat mensuel correct Résultat mensuel correct Résultat mensuel correct
51 538 77 000 31 500
7 / 40
Résultat
7.07%
12.45%
8.32% 9.17% 8.83% 5.09% 6.96% 10.02% 8.72% 7.07%
416491847
6.23%
Janvier Février Mars Avril Mai Juin Juillet Août Septembre Octobre Novembre Décembre
10.07%
P. COULAUD- 18/12/2013
8 / 40
TABLEUR 416491847
MOYENNE SI
Utiliser des références relatives et absolues Insérer les fonctions : MOYENNE, SI
Exercice 6 BAR DES VOYAGEURS Vous travaillez à l'hôtel des Voyageurs et êtes en charge de la gestion du bar de l'hôtel. Vous devez faire un tableau dans lequel vous saisirez le montant des ventes TTC du bar chaque jour. Le tableau contiendra au total six mois, A la fin de chaque mois, vous ferez apparaître le total TTC, le montant de la TVA (taux normal), le total HT ainsi que la moyenne journalière. Votre travail consiste à réaliser un modèle de tableau qui servira ensuite à la saisie des recettes du bar. Tous les six mois, on ouvrira un nouveau modèle pour commencer un nouveau tableau. Le modèle est le suivant : HOTEL DES VOYAGEURS Recettes du bar
Taux TVA : 19,60%
Mois Jours 1 2 3 ... ... 28 29 30 31 TOTAL TTC TVA HT Moy/jour
416491847
P. COULAUD- 18/12/2013
9 / 40
Travail 1 à faire 1. Créer le classeur BAR_DES_VOYAGEURS_Nom et la feuille SEMESTRE_MODELE 2. Réaliser le modèle avec les formules suivantes a) Total TTC : fonction SOMME b) HT = TTC / (1+ taux de TVA) c) TVA = TTC HT d) Moyenne/jour : fonction MOYENNE; afin d'éviter des messages d'erreur, utiliser la fonction SI : SI(B18=0;0;MOYENNE(B6:B17)) 3. Mettre en forme et en page le tableau 4. Enregistrer; faire un aperçu
6. Saisir les données cicontre dans la feuille SEMESTRE_1 7. Vérifier vos formules et la cohérence de vos résultats 8. Enregistrer; faire un aperçu
Recettes journalières du bar janvier
février
mars
182,20
114,50
215,60
93,70
154,70
146,70
129,70
85,80
290,20
105,60
213,50
227,00
127,00
122,50
197,50
99,50
133,20
138,40
225,30
228,50
141,00
125,30
243,20
134,60
161,30
132,50
166,00
223,60
187,50
153,00
148,90
154,00
141,20
225,60
152,00
224,70
150,00
147,40
215,20
102,60
303,70
76,60
325,60
185,20
214,20
227,60
271,00
132,40
128,00
71,00
117,20
89,60
131,00
133,60
148,90
148,80
131,00
195,40
224,30
226,70
172,60
202,00
156,70
168,40
221,20
145,00
146,20
CONGES ANNUELS
Travail à faire 2 5. Copier la feuille SEMESTRE_MODELE en SEMESTRE_1 ; enregistrer
269,50
172,60
173,80
202,30
143,80
416491847
P. COULAUD- 18/12/2013
10 / 40
TABLEUR 416491847
SI imbriqués
Présentation SI imbriqués On peut imbriquer jusqu’à sept fonctions SI les unes dans les autres : = SI(condition;si VRAI ; SI(condition;si VRAI ; SI(condition;si VRAI ; SI(condition;si VRAI ; SI(condition;si VRA I; SI(condition;si VRAI ; SI(condition;si VRAI;si FAUX))))))) =SI(condition;si VRAI; SI(condition;si VRAI; SI(condition;si VRAI; SI(condition;si VRAI; SI(condition;si VRAI; SI(condition;si VRAI; SI(condition;si VRAI;si FAUX)))))))
Exemple : Calcul du prix d’entrée selon l’âge de la personne : • Age <= 8 ans 10€ • Age entre 9 et 17 ans 15€ • Age >= 18 ans 20€ La formule de calcul du prix sera la suivante : =SI(B1<=8;10; SI(B1>=18;20;15)) =SI(B1<=8;10;SI(B1>=18;20;15)) A B 1 Age 17 2 Prix entrée 15 € 3
416491847
P. COULAUD- 18/12/2013
C
11 / 40
TABLEUR 416491847
SI imbriqués
Gérer plusieurs feuilles Nommer des cellules Utiliser la fonction : SI
Exercice 7 ROUIX 1 (2 → SI, ET, OU) Faites les calculs qui s’imposent en tenant compte des indications suivantes : • le taux de remise est de 3% si le hors taxes dépasse 10 000 € • pour les grossistes ce taux sera de 5% et à condition que le hors taxes dépasse 10 000 €
CALCUL DES REMISES CLIENTS GARD
Grossiste
Brut Hors taxes 14 650,00
HECTOR
Détaillant
8 703,00
0%
0,00
8 703,00
IBO
Particulier
14 540,00
3%
436,20
14 103,80
JURA
Grossiste
8 775,00
0%
0,00
8 775,00
KILL
Particulier
7 602,00
0%
0,00
7 602,00
LAMA
Grossiste
11 215,00
5%
560,75
10 654,25
MANN
Détaillant
8 703,00
0%
0,00
8 703,00
NUZ
Détaillant
17 525,00
3%
525,75
16 999,25
Nom
Type client
5%
Montant Remise 732,50
Net Hors taxes 13 917,50
Remise
Travail à faire 1. Créer le classeur ROUIX_Nom et la feuille REMISE 2. Saisir les données du tableau 3. Effectuer les calculs nécessaires en utilisant des SI IMBRIQUÉS : =SI (chiffres affaires<10 000 ; 0 ; SI(client="Grossiste" ; remise 2 ; remise 1)) 4. Enregistrer; faire un aperçu
CORRIGE
GARD
Grossiste
Brut Hors taxes 14 650,00
5%
Montant Remise 732,50
HECTOR
Détaillant
8 703,00
0%
0,00
8 703,00 =SI(C5<10000; 0 ; SI(B5="Grossiste" ; 0,05; 0,03))
IBO
Particulier
14 540,00
3%
436,20
14 103,80 =SI(C6<10000; 0 ; SI(B6="Grossiste" ; 0,05; 0,03))
JURA
Grossiste
8 775,00
0%
0,00
8 775,00 =SI(C7<10000; 0 ; SI(B7="Grossiste" ; 0,05; 0,03))
Nom
Type client
416491847
Remise
Net Formules de la remise Hors taxes 13 917,50 =SI(C4<10000; 0 ; SI(B4="Grossiste" ; 0,05; 0,03))
P. COULAUD- 18/12/2013
12 / 40
Exercice 8 BISCOTTE SI IMBRIQUÉE Travail à faire 1. Créer le classeur BISCOTTE_Nom et la feuille TAUX_TVA 2. Saisir les données du tableau Code TVA 1 =
2,10%
Code TVA 2 =
10,00%
Code TVA 3 =
20,00%
ARTICLES
CODE TVA
TAUX TVA
ECRAN
2
10,00%
BISCOTTES
1
2,10%
IMPRIMANTE
3
20,00%
MULOT
1
2,10%
CLAVIER
1
2,10%
SCANNER
3
20,00%
3. Réaliser les formules nécessaires afin de déterminer le taux de TVA à partir des codes TVA. 4. Enregistrer
CORRIGE Code TVA 1 =
2,10%
Code TVA 2 =
10,00%
Code TVA 3 =
20,00%
ARTICLES
CODE TVA
ECRAN
2
BISCOTTES
1
IMPRIMANTE
3
MULOT
1
CLAVIER
1
SCANNER
3
416491847
TAUX TVA 10,00% =SI(B43=1;$B$38; SI(B43=2;$B$39;$B$40)) 2,10% =SI(B44=1;$B$38; SI(B44=2;$B$39;$B$40)) 20,00% =SI(B45=1;$B$38; SI(B45=2;$B$39;$B$40)) 2,10% =SI(B46=1;$B$38; SI(B46=2;$B$39;$B$40)) 2,10% =SI(B47=1;$B$38; SI(B47=2;$B$39;$B$40)) 20,00% =SI(B48=1;$B$38; SI(B48=2;$B$39;$B$40))
P. COULAUD- 18/12/2013
13 / 40
Exercice 9 ENFANTS SI IMBRIQUÉE Travail à faire 1. Créer le classeur ENFANTS_Nom et la feuille PRIME 2. Saisir les données du tableau
SEXE
NBRE D'ENFANTS
ANNE
F
0
0 €
BERTRAND
M
1
50 €
CLOÉ
F
2
100 €
DAMIEN
M
1
50 €
ELRIC
F
5
350 €
FANNY
M
3
210 €
GUY
F
2
100 €
HUGO
M
2
100 €
NOMS
PRIME
3. Calculer la prime accordée aux salariés sachant que : - les salariés sans enfants n'y ont pas droit - cette prime est fonction du nombre d'enfants : la prime s'élève à 50 € par enfant pour les salariés avec 1 ou 2 enfants et à 70 € par enfant pour les salariés ayant 3 enfants ou plus. 4. Enregistrer
SEXE
NBRE D'ENFANTS
ANNE
F
0
0 € =SI(C31=0;0; SI(C31<=2; 50*C31 ; 70*C31))
BERTRAND
M
1
50 € =SI(C32=0;0; SI(C32<=2; 50*C32 ; 70*C32))
CLOÉ
F
2
100 € =SI(C33=0;0; SI(C33<=2; 50*C33 ; 70*C33))
NOMS
416491847
PRIME
P. COULAUD- 18/12/2013
14 / 40
Exercice 10 ACTEURS SI IMBRIQUÉE Les Oscar sont des trophées remis aux héros ayant, soit tourné plus de 15 films, soit ayant dépassé les 200 millions d'entrées. De plus, un Oscar d'Or est attribué au héro ayant le plus de films et le plus d'entrées. Travail à faire 1. Créer le classeur ACTEURS_Nom et la feuille OSCAR 2. Saisir les données du tableau Nombre de films
Spectateurs en millions
CHARLOT
16
35
D'ARTAGNAN
12
210
CLEOPATRE
8
26
ARSENE LUPIN
18
250
MICKEY
14
150
NOURNOURS
14
240
SUPERMAN
9
160
ZORRO
13
90
Héros
3. Déterminer, dans une colone supplémentaire, qui mérite un simple Oscar 4. Déterminer, dans une colone supplémentaire, qui recevra l'Oscar d'Or ; il vous faut calculer a) le MAXIMUM des films b) le MAXIMUM des entrés c) puis les conparer aux films et entrées de chaque acteur 5. Enregistrer
Nombre de films
Spectateurs en millions
CHARLOT
16
35
Oscar
=SI(B3>=15;"Oscar";SI(C3>=200;"Oscar";""))
D'ARTAGNAN
12
210
Oscar
=SI(B4>=15;"Oscar";SI(C4>=200;"Oscar";""))
CLEOPATRE
8
26
ARSENE LUPIN
18
250
MICKEY
14
150
NOURNOURS
14
240
SUPERMAN
9
160
ZORRO
13
90
18
250
Héros
Maximum
416491847
Oscar
Oscar d'Or
=SI(B5>=15;"Oscar";SI(C5>=200;"Oscar";"")) Oscar
Oscar d'Or
=SI(D6="Oscar";SI(B6=$B$11;SI(C6=$C$11;"Oscar d'Or";"");"");"")
Oscar
P. COULAUD- 18/12/2013
15 / 40
Exercice 11 NEGOCE (2) SI IMBRIQUÉE – NOMMER UNE CELLULE Vous travaillez au service gestion de l'entreprise NEGOCE et vous êtes chargé de calculer les commissions trimestrielles des revendeurs. Vous disposez des montants des ventes des revendeurs pour les deux derniers trimestres et vous devez regrouper ces chiffres dans un tableau. Ensuite, vous calculerez les commissions selon les tranches dans un autre tableau qui sera automatiquement mis à jour en cas de modifications du premier tableau (les tranches ne sont pas cumulables). Informations Les données sont les suivantes : LOSSE
MARTIN
NORCA
juillet
7 400
2 400
0
août
2 400
4 500
0
septembre
4 800
4 100
0
octobre
0
2 900
2 700
novembre
0
6 700
960
décembre
0
8 400
4 800
Calcul des commissions : 3ème trimestre
• •
ventes = 0 ventes > 0 les ventes
4ème trimestre
• •
ventes = 0 alors commission = 0 0 < ventes < 12 000 € alors commission de 5% sur les ventes 12 000 € ventes alors commission de 7% sur les ventes
•
alors alors
commission = 0 commission de 5% sur
Travail à faire 1 1. Reprendre le classeur NEGOCE_Nom et créer la feuille SEMESTRE_2 2. Saisir les données des ventes 3. Réaliser les formules de calcul des ventes par trimestre par représentant en nommant ces montants (6 noms) : Insertion > Noms NOMMER UNE CELLULE Cette commande permet de donner un nom à une cellule en vue d'utiliser ce nom dans une formule de calcul. Sélectionner la cellule (ou le bloc de cellules) à nommer, puis LIBREOFFICE EXCEL
416491847
insertion > Noms > Définir
Formules > Définir un nom
P. COULAUD- 18/12/2013
16 / 40
4. Mettre en forme et en page le tableau 5. Enregistrer et faire un aperçu Travail à faire 2 6. Utiliser, dans votre classeur NEGOCE, une nouvelle feuille nommée COMMISSION_S2 et sauvegarder 7. Réaliser le tableau des commissions : a) rappeler le montant des ventes par liaison avec la feuille SEMESTRE_2 en utilisant les noms des cellules correspondantes b) calcules les commissions par trimestre : fonction SI 8. Mettre en forme et en page le tableau 9. Enregistrer et faire un aperçu
CORRIGE COMMISSIONS DES VENDEURS 3ème trimestre Ventes
Commission
Ventes
Commission
LOSSE
14 600
730
0
0
MARTIN
11 000
550
18 000
1 260
NORCA
0
0
8 460
423
25 600
1 280
26 460
1 683
TOTAL
416491847
4ème trimestre
P. COULAUD- 18/12/2013
17 / 40
Exercice 12 LAVOIE (4)**** SI IMBRIQUÉE
FICHE DE DÉCOMPTE DES HEURES
AVRIL
Nom : LAVOIE Heures normales : 35 Heures à +25% : de 36h à 43h Heures à +50% : à partir de 44h SEMAINES
Nb d'heures
HS + 25%
Semaine 1
37
2
Semaine 2
46
8
Semaine 3
38
3
Semaine 4
44
8
1
165
21
4
TOTAUX
HS + 50%
3
Travail à faire 1. Ouvrir OU créer le classeur MICROCD_ Nom; puis créer OU modifier la feuille nommée LAVOIE 2. Saisir les données du tableau, si ce n'est déjà fait 3. Calculer les heures supplémentaires : a) Explication de la formule pour les heures à +25% : SI (je travaille +35 h alors SI (je travaille 44 h alors je calcule mes HS 25 % sinon je travaille sur toute la tranche 36 h 43 h) =SI($H$5=44;H2343;"") 4. Calculer les totaux 5. Mettre en forme et en page le tableau 6. Enregistrer; faire un aperçu
416491847
P. COULAUD- 18/12/2013
18 / 40
Exercice 13 JARDIFLOR*** SI IMBRIQUÉE Vous travaillez dans la SARL JARDIFLOR dont le responsable est M. SPACEVERT. Celuici désire mettre en place un système de ristournes pour les bons clients. Il vous remet le document suivant avec les tranches qui sont cumulables et les taux de ristourne :
TABLEAU DE CALCUL DES RISTOURNES N° du client :
411021
Nom du client
TERRES ET DECO
Montant du chiffre d'affaire H.T. :
2130,66 Montant de la Taux de ristourne tranche
Tranches de chiffres d'affaires
Montant
de
0,00 à
500,00
500
1%
5,00
de
500,00 à
1 200,00
700
2%
14,00
de
1 200,00 à
3 000,00
931
3%
27,92
de
3 000,00 à
10 000,00
4%
0,00
AVOIR HT TVA
46,92 19,60%
AVOIR TTC
9,20 56,12
Travail à faire 1. Créer le classeur JARDIFLOR_Nom et la feuille RISTOURNE 2. Saisir les données du tableau 3. Réaliser les formules de calcul : a) sur les tranches ; utiliser la fonction SI) b) tester la cohérence de vos formules en simulant différents chiffres d'affaires HT 4. Mettre en forme et en page le tableau 5. Enregistrer et faire un aperçu
416491847
P. COULAUD- 18/12/2013
19 / 40
Exercice 14 APPRECIATIONS *** SI IMBRIQUÉE x 4 Un enseignant a noté ses élèves sur un devoir et veut élaborer un tableau lui permettant de convertir les notes en appréciations :
APPRECIATIONS Tableau de notes Nom Notes Anaud 12 Bernard 4 Constance 5 David 9 Ermine 16 Fernand 14 Gaspard 5 Hector 18 Ivan 17 Jean 8 Louis 12 Marc 15 Noa 16
Appréciation C E D C B B D A A D C B B
Tableau de conversion Notes Appréciation Borne sup. De 17 à 20 A De 13 à 16 B 16 De 9 à 12 C 12 De 5 à 8 D 8 De 0 à 4 E 4
Travail à faire 1 1. Créer le classeur NOTES_Nom et feuille APPRECIATIONS 2. Saisir le tableau des élèves avec leur note 3. Ajouter une colonne avec la formule adéquate afin d'attribuer automatiquement les appréciations : =SI(B3>16;"A"; SI(B3>12;"B"; SI(B3>8;"C"; SI(B3>4;"D";"E")))) 4. Mettre en forme et en page le tableau 5. Enregistrer et faire un aperçu 6. Reprendre et modifier la formule afin qu'il n'y ait ni lettres (A, B …) ni chiffres (16, 12 …) mais des liens vers le tableau de conversion : =SI(B4>G$5;F$4; SI(B4>G$6;F$5; SI(B4>G$7;F$6; SI(B4>G$8;F$7;F$8)))) 7. Enregistrer et faire un aperçu
416491847
P. COULAUD- 18/12/2013
20 / 40
Exercice 15 NEGOCE 1** SI IMBRIQUÉE x 4 Les clients de l'entreprise NEGOCE ont été facturé des montants suivants : Les frais de port facturés aux clients s'élèvent à • 100 € si le total des marchandises est inférieur à 2 000 € • 50 € si le total des marchandises est compris entre 2 000 € et 5 000 € • gratuit si le total des marchandises dépasse 5 000 €.
Client ARMAND BOULI CIRIL DANIEL ELVET FRASSE GART HECTOR IRWIN JURA
Montant 2 550,00 € 5 320,00 € 950,00 € 1 450,00 € 6 845,00 € 3 245,00 € 560,00 € 1 980,00 € 4 980,00 € 8 640,00 €
Travail à faire 1 1. Créer le classeur NEGOCE_Nom et la feuille PORTCLIENT 2. Saisir le tableau des clients puis ajouter une colonne afin de calculer les frais de port de chacun =SI(B23<2000;100; SI(B23<5000;50;"Offert")) 3. Mettre en forme et en page le tableau 4. Enregistrer et faire un aperçu Travail à faire 2 1. Saisir le tableau du barème des frais de port 2. Dans le tableau des clients, ajouter une seconde colonne de frais de port selon le nouveau barème (les formules devront être liées au tableau des barèmes) :
Barème des frais de port Moins de 1 000 € De 1 001 € à 2 000 € De 2 001 € à 4 000 € De 4 001 € à 6 000 € A partir de 6 001
Frais de port 100,00 € 80,00 € 60,00 € 30,00 € Offert
Borne sup. 1 000,00 € 2 000,00 € 4 000,00 € 6 000,00 €
=SI(B23<$I$23;$H$23; SI(B23<$I$24;$H$24; SI(B23<$I$25; $H$25; SI(B23<$I$26; $H$26;$H$27)))) 3. Mettre en forme et en page le tableau 4. Enregistrer et faire un aperçu
CORRIGE Client ARMAND BOULI CIRIL DANIEL ELVET FRASSE GART HECTOR IRWIN JURA
416491847
Montant Frais port (1) Frais port (2) 2 550,00 € 50,00 € 60,00 € 5 320,00 € Offert 30,00 € 950,00 € 100,00 € 100,00 € 1 450,00 € 100,00 € 80,00 € 6 845,00 € Offert Offert 3 245,00 € 50,00 € 60,00 € 560,00 € 100,00 € 100,00 € 1 980,00 € 100,00 € 80,00 € 4 980,00 € 50,00 € 30,00 € 8 640,00 € Offert Offert
P. COULAUD- 18/12/2013
21 / 40
TABLEUR 416491847
NB.SI SOMME.SI
Présentation NB.SI et SOMME.SI NB.SI permet de compter le nombre de cellules d'une plage qui répondent à un critère donné : NB.SI(plage;critère) SOMME.SI permet d'additionner des cellules en fonction de critères prédéfinis SOMME.SI(plage;critère;somme_plage)
Exemple 1 : NB.SI Voici un tableau de personnes avec leur âge. Déterminer le nombre de personnes ayant : 1. moins de 35 ans :
=NB.SI(C2:C14;"<35")
2. au moins 50 ans :
=NB.SI(C2:C14;">=50")
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
416491847
A
B
PRENOM
AGE
Luc
25
Pierre
32
Paul
33
Jacques
21
Anne
36
Marie
37
Hubert
47
Charles
51
Solange
52
Victor
58
Fleur
25
Antoine
28
Laure
31
C
< 35
7
=NB.SI(C2:C14;"<35")
> 50
3
=NB.SI(C2:C14;">=50")
P. COULAUD- 18/12/2013
22 / 40
Exemple 2 : SOMME.SI Voici un tableau listant différents titres de livres avec leur genre. Il s'agit de déterminer le stock pour chaque genre : 1. Policier 2. Roman 3. SF 4. BD
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
A
B
C
TITRE
GENRE
STOCK
Titre 1
Policier
10
Titre 2
Roman
5
Titre 3
SF
2
Titre 4
BD
5
Titre 5
Roman
5
Titre 6
Roman
4
Titre 7
Policier
8
Titre 8
BD
3
Titre 9
SF
2
Titre 10
Roman
4
Titre 11
SF
2
Titre 12
BD
6
Titre 13
Policier
3
D
E
SOMME Policier
21
=SOMME.SI(B2:B14;"Policier";C2:C14)
SOMME Roman
18
=SOMME.SI(B2:B14;"Roman";C2:C14)
SOMME SF
6
=SOMME.SI(B2:B14;"SF";C2:C14)
SOMME BD
14
=SOMME.SI(B2:B14;"BD";C2:C14)
APPRENDRE SUR INTERNET http://www.excelexercice.com/ **** Fonction > Condition
416491847
P. COULAUD- 18/12/2013
23 / 40
TABLEUR 416491847
NB.SI SOMME.SI SOMMEPROD
Gérer plusieurs feuilles Nommer des cellules Utiliser les fonctions NB.SI, SOMME.SI, SOMMEPROD
Exercice 16 VOTE NB.SI Lors d'un vote, les bulletins dépouillés ont donné le résultat suivant :
Pierre Yves Anne Pierre Pierre Yves Sophie Anne Yves Anne Pierre Yves Sophie Anne Pierre Jules
Urne N°1 Pierre Anne Sophie Yves Jules Anne Pierre Anne Anne Anne Yves Sophie Anne Jules Jules Anne
Pierre Yves Sophie Anne Pierre Pierre Yves Sophie Anne Sophie Sophie Pierre Yves Yves Sophie Jules
En utilisant la fonction NB.SI, déterminez le nombre de vote pour chaque candidat Travail à faire 1. Créer le classeur VOTE_Nom et la feuille RESULTAT 2. Saisir les données du tableau 3. Déterminez le nombre de votes pour chaque candidat en utilisant la fonction NB.SI 4. Mettre en forme et en page les tableaux 5. Enregistrer; faire un aperçu
CORRIGÉ Candidat Pierre Yves Anne Sophie Jules
416491847
Nb de bulletins 11 10 13 9 5
P. COULAUD- 18/12/2013
24 / 40
Exercice 17 FOX** NB.SI SOMME.SI Dans la société FOX, le tableau cidessous retrace les frais de déplacement pour le mois de janvier.
Feuille de frais du mois de janvier Collaborateur Jean Thierry Philippe Thierry Frédéric René Didier Jean Didier Thomas Frédéric Thierry René Jean Thomas Philippe Frédéric René Thierry Didier Jean
Date 04/01 04/01 05/01 06/01 06/01 07/01 08/01 11/01 12/01 12/01 13/01 14/01 15/01 18/01 18/01 19/01 20/01 21/01 21/01 21/01 22/01
Frais 15 17 24 8 52 34 42 53 18 25 22 60 95 125 49 56 7 34 26 58 102
Travail à faire 1. Créer le classeur FOX_Nom et la feuille FRAIS_PRO 2. Saisir les données du tableau 3. Sous ce tableau, réaliser un nouveau tableau qui calculera : a) le nombre de fiche de remboursement de frais par salarié (Fonction > NB.SI) b) le montant des remboursements par salariés (Fonction > SOMME.SI) 4. Enregistrer; faire un aperçu
CORRIGÉ Récapitulatif du mois Collaborateur Nb de rapport Didier 3 Frédéric 3 Jean 4 Philippe 2 René 3 Thierry 4 Thomas 2 TOTAL 21
416491847
Frais 118 81 295 80 163 111 74 922
P. COULAUD- 18/12/2013
25 / 40
Exercice 18 LIBRAIRIE NB.SI SOMME.SI Date
Produit
Qtés Prix unitaire commandées
01/09/13
Crayon
75
1,00 €
02/09/13
Gomme
15
1,50 €
03/09/13
Papier
200
8,00 €
04/09/13
Crayon
50
1,00 €
05/09/13
Feutre
30
4,00 €
06/09/13
Crayon
100
1,00 €
07/09/13
Gomme
30
1,50 €
08/09/13
Feutre
40
4,00 €
09/09/13
Papier
50
8,00 €
10/09/13
Crayon
20
1,00 €
Travail à faire 1. Créer le classeur LIBRAIRIE_Nom et la feuille COMMANDES 2. Saisir les données du tableau 3. Créer une colonne total et réaliser les formules des totaux par ligne 4. Sous ce tableau, vous concevrez un nouveau tableau où vous souhaitez connaître le nombre de fois que des crayons ont été commandés, ainsi que pour les autres produits : NB.SI 5. Compléter votre tableau aifn de calculer le nombre de crayons commandés, ainsi que des autres produits : SOMME.SI 6. Enfin vous calculerez le montant commandé par produit : SOMME.SI 7. Enregistrer ; faire un aperçu
Crayon
Nombre de commandes 4
Qtés commandées 245
Feutre
2
70
280,00 €
Gomme
2
45
67,50 €
Papier
2
250
2 000,00 €
10
610
2 592,50 €
Total
Montant 245,00 €
=NB.SI($B$36:$B$45;B49) =SOMME.SI($B$36:$B$45;B50;$C$36:$C$45) =SOMME.SI($B$36:$B$45;B51;$E$36:$E$45)
416491847
P. COULAUD- 18/12/2013
26 / 40
Exercice 19 NOTES DES ÉLÈVES*** NB.SI SOMME.SI SOMMEPROD Voici le tableau brut des notes des élèves : Nom ALIX BUSNOT DELAUTRE DUVAL DUVAL FLEURY GAUTIER LAGADEC LAMY LE LE GLAUNEC LEGUILLON LEPIETRE LIOEDDINE MENNIER METTE MICHEL MICHEL MONTAGNE ROBERGE ROUXEL VOISIN
Prénom Yoann Corinne Chloé Sylvain Sandra Brice Sébastien Anne-Claire Sophie Thi Adele Julie Blandine Mohamed Maud Christelle Elodie Valentin Anthony Magali Marc Aurélie
CTRL 1 7 10 4 6 7 11 8 4 4 8 7 6 4 7 7 11 7 6 10 11 4 4
Devoir 1 18 10 18 14 12 8 Abs 18 8 14 18 17 18 8 17 15 18 10 14 14 12 13
CTRL 2 6 8 9 11 10 9 12 7 12 14 14 10 Abs 12 6 10 11 9 10 10 6 9
CTRL3 11 17 15 Abs 17 9 16 10 13 13 8 15 12 18 15 16 9 12 15 11 14 10
Moyenne
Moyenne Note la Plus Basse Note la plus haute NB d'élèves ayant 10 ou plus NB d'élèves ayant entre 8 et 10 NB d'élève ayant 8 ou moins de 8
Travail à faire 1
Respecter la PROGESSION des consignes 1. 2. 3. 4. 5.
Créer (OU ouvrir) le classeur NOTES_Nom et créer la feuille NOTES DES ELEVES Saisir les données du tableau Réaliser les calculs prévus dans le tableau Mettre en forme et en page le tableau Enregistrer; faire un aperçu
Travail à faire 2 1. Dupliquer la feuille NOTES DES ELEVES en NOTES COEFFICIENTS 2. Ajouter une ligne Coefficient : coefficients 2 aux contrôles et coefficient 1 au devoir 3. Modifier vos calculs en tenant compte des coefficients ; Quelle difficultés rencontrezvous ? La réponse est dans la suite Travail à faire 3 4. Reprenez vos formules de moyenne en combinant cette fois les fonctions (lire l'aide associée à ces formules afin de comprendre leur utilisation) : SOMMEPROD et SOMME.SI 5. Enregistrer =SOMMEPROD(C76:F76;$C$75:$F$75) / SOMME.SI(C76:F76;">0";$C$75:$F$75)
416491847
P. COULAUD- 18/12/2013
27 / 40
CORRIGÉ NOTES Nom ALIX BUSNOT DELAUTRE DUVAL DUVAL FLEURY GAUTIER LAGADEC LAMY LE LE GLAUNEC LEGUILLON LEPIETRE LIOEDDINE MENNIER METTE MICHEL MICHEL MONTAGNE ROBERGE ROUXEL VOISIN
Prénom Yoann Corinne Chloé Sylvain Sandra Brice Sébastien Anne-Claire Sophie Thi Adele Julie Blandine Mohamed Maud Christelle Elodie Valentin Anthony Magali Marc Aurélie
Moyenne Note la Plus Basse Note la plus haute NB d'élèves ayant 10 ou plus NB d'élèves ayant entre 8 et 10 NB d'élève ayant 8 ou moins de 8
CTRL 1 7 10 4 6 7 11 8 4 4 8 7 6 4 7 7 11 7 6 10 11 4 4
Devoir 1 18 10 18 14 12 8 Abs 18 8 14 18 17 18 8 17 15 18 10 14 14 12 13
CTRL 2 6 8 9 11 10 9 12 7 12 14 14 10 Abs 12 6 10 11 9 10 10 6 9
CTRL3 11 17 15 Abs 17 9 16 10 13 13 8 15 12 18 15 16 9 12 15 11 14 10
Moyenne 10,50 11,25 11,50 10,33 11,50 9,25 12,00 9,75 9,25 12,25 11,75 12,00 11,33 11,25 11,25 13,00 11,25 9,25 12,25 11,50 9,00 9,00
6,95 4,00 11,00 5 0 17
14,00 8,00 18,00 18 0 3
9,76 6,00 14,00 12 4 5
13,14 8,00 18,00 18 2 1
10,93 9,00 13,00 16 6 0
CTRL 1 2 7 10 4 6 7 11 8 4 4 8 7 6 4 7 7 11 7 6 10 11 4 4
Devoir 1 1 18 10 18 14 12 8 Abs 18 8 14 18 17 18 8 17 15 18 10 14 14 12 13
CTRL 2 2 6 8 9 11 10 9 12 7 12 14 14 10 Abs 12 6 10 11 9 10 10 6 9
CTRL3 2 11 17 15 Abs 17 9 16 10 13 13 8 15 12 18 15 16 9 12 15 11 14 10
Moyenne
6,95 4,00 11,00 5 0 17
14,00 8,00 18,00 18 0 3
9,76 6,00 14,00 12 4 5
13,14 8,00 18,00 18 2 1
10,48 8,43 12,71 14 8 0
NOTES AVEC COEFFICIENTS Nom
Prénom Coefficients ALIX Yoann BUSNOT Corinne DELAUTRE Chloé DUVAL Sylvain DUVAL Sandra FLEURY Brice GAUTIER Sébastien LAGADEC Anne-Claire LAMY Sophie LE Thi LE GLAUNEC Adele LEGUILLON Julie LEPIETRE Blandine LIOEDDINE Mohamed MENNIER Maud METTE Christelle MICHEL Elodie MICHEL Valentin MONTAGNE Anthony ROBERGE Magali ROUXEL Marc VOISIN Aurélie Moyenne Note la Plus Basse Note la plus haute NB d'élèves ayant 10 ou plus NB d'élèves ayant entre 8 et 10 NB d'élève ayant 8 ou moins de 8
416491847
P. COULAUD- 18/12/2013
9,43 11,43 10,57 9,60 11,43 9,43 12,00 8,57 9,43 12,00 10,86 11,29 10,00 11,71 10,43 12,71 10,29 9,14 12,00 11,14 8,57 8,43
28 / 40
Exercice 20 BERLIOZ 3*** (4 → RECHERCHE) FREQUENCE NB.SI – SOMME.SI – SOMMEPROD Vous travaillez pour le lycée BERLIOZ. Vous êtes chargés de construire un tableau récapitulatif des notes obtenues par les élèves d'une classe lors de leur dernier contrôle de connaissances.
Travail à faire 1 1. Ouvrir ou créer le classeur BERLIOZ_Nom 2. Dupliquer la feuille CLASSE_1 afin de créer la feuille CLASSE_1_COEF 3. Insérer les coefficients en haut du tableau :
Résultats de l'examen : classe 1 (avec coefficient des matières) Math
Fran.
Langue
Physiq.
Histoire
4
3
1
2
1
Vialat
6
9
5
10
7
Fallourd
7
5
8
9
10
Dubois
10
11
8
7
11
Dupuis
8
10
12
13
9
Dupont
12
10
11
12
Brosseau
15
19
14
6
16
Théau
11
12
9
9
11
Serres
17
18
14
Colbert
11
13
14
15
16
Cadu
17
16
15
12
17
Verdon
8
7
5
6
Coefficient
4. Calculer la moyenne de chaque élève en tenant compte des coefficients : fonctions SOMMEPRODUIT et SOMME.SI. Exemple de formule à réaliser : =SOMMEPROD($C$25:$G$25;C26:G26) / SOMME.SI(C26:G26;">0";$C$25:$G$25) Travail à faire 2 1. Calculer la moyenne, les notes maximale et minimale par élève et par matière 2. En bas du tableau, ajouter 3 lignes sur 2 colonnes (descriptifs et bornes supérieures) : a) moins de 8 8 b) de 8 à 12 12 c) plus de 12 20 3. Utiliser la fonction FREQUENCE pour les notes de mathématiques a) Insertion > Fonction > FREQUENCE : les données = notes des élèves en Math les classes = borne de chaque intervalle ATTENTION pour Excel : il faut valider par CTRL + MAJ + ENTREE 4. Refaire la manipulation pour chacune des autres matières
416491847
P. COULAUD- 18/12/2013
29 / 40
Résultats de l'examen : classe 1
Nombre élèves :
11
(avec coefficient des matières) Math
Fran.
Langue
Physiq.
Histoire
4
3
1
2
1
Vialat
6
9
5
10
Fallourd
7
5
8
Dubois
10
11
Dupuis
8
10
Dupont
12
Brosseau
15
Théau
11
Serres
17
Colbert
11
Cadu Verdon
Moy.
Nb de notes
Min
Max
7
7,55
5,00
5,00
10,00
9
10
7,18
5,00
5,00
10,00
8
7
11
9,64
5,00
7,00
11,00
12
13
9
9,91
5,00
8,00
13,00
10
11
12
11,50
4,00
10,00
12,00
19
14
6
16
14,45
5,00
6,00
19,00
12
9
9
11
10,73
5,00
9,00
12,00
18
14
16,29
3,00
14,00
18,00
13
14
15
16
13,00
5,00
11,00
16,00
17
16
15
12
17
15,64
5,00
12,00
17,00
8
7
5
6
6,90
4,00
5,00
8,00
Moyenne
11,09
11,33
11,30
10,09
11,50
11,16
Note maximale
17,00
19,00
18,00
15,00
17,00
16,29
Note minimale
6,00
5,00
5,00
5,00
6,00
6,90
Coefficient
Moins de 8
8
4
2
3
3
2
De 8 à 12
12
4
4
3
5
5
Plus de 12
20
3
3
4
3
3
416491847
P. COULAUD- 18/12/2013
30 / 40
TABLEUR 416491847
SI ET OU
Présentation Les fonctions ET et OU peuvent être combinées avec la fonction SI lorsque la condition concerne au moins deux arguments.
Exemple Admission si l’âge < 18 ans et la moyenne >=12: La formule de calcul du prix sera la suivante : =SI(ET(A2<18;B2>=12);«admis»;«refusé») =SI(ET(A2<18;B2>=12);«admis»;«refusé») A B C 1 Age Moyenne 2 17 11 refusé 3
APPRENDRE SUR INTERNET http://www.excelexercice.com/ **** Fonction > Condition
416491847
P. COULAUD- 18/12/2013
31 / 40
Exercice 21 ROUIX 2 (1 → SI imbriqués) SI ET Faites les calculs qui s’imposent en tenant compte des indications suivantes : • le taux de remise est de 3% si le hors taxes dépasse 10 000 € • pour les grossistes ce taux sera de 5% et à condition que le hors taxes dépasse 10 000 €
CALCUL DES REMISES CLIENTS GARD
Grossiste
Brut Hors taxes 14 650,00
HECTOR
Détaillant
8 703,00
0%
0,00
8 703,00
IBO
Particulier
14 540,00
3%
436,20
14 103,80
JURA
Grossiste
8 775,00
0%
0,00
8 775,00
KILL
Particulier
7 602,00
0%
0,00
7 602,00
LAMA
Grossiste
11 215,00
5%
560,75
10 654,25
MANN
Détaillant
8 703,00
0%
0,00
8 703,00
NUZ
Détaillant
17 525,00
3%
525,75
16 999,25
Nom
Type client
5%
Montant Remise 732,50
Net Hors taxes 13 917,50
Remise
Travail à faire 1. Ouvrir ou créer le classeur ROUIX_Nom et la feuille REMISE 2. Saisir les données du tableau 3. Effectuer les calculs nécessaires en utilisant les fonctions SI et ET : =SI( ET(client="Grossiste" ; chiffre d'affaires>10000) ; remise 2 ; SI(chiffre d'affaires>10000; remise 1 ; 0 )) 4. Enregistrer; faire un aperçu
CORRIGE
KILL
Particulier
7 602,00
0%
0,00
7 602,00 =SI( ET(B8="Grossiste" ; C8>10000) ; 0,05 ; SI(C8>10000; 0,03; 0 ))
LAMA
Grossiste
11 215,00
5%
560,75
10 654,25 =SI( ET(B9="Grossiste" ; C9>10000) ; 0,05 ; SI(C9>10000; 0,03; 0 ))
MANN
Détaillant
8 703,00
0%
0,00
8 703,00 =SI( ET(B10="Grossiste" ; C10>10000) ; 0,05 ; SI(C10>10000; 0,03; 0 ))
NUZ
Détaillant
17 525,00
3%
525,75
16 999,25 =SI( ET(B11="Grossiste" ; C11>10000) ; 0,05 ; SI(C11>10000; 0,03; 0 ))
416491847
P. COULAUD- 18/12/2013
32 / 40
Exercice 22 ARMAND** SI – ET – OU On désire attribuer les 2 ristournes suivantes : • une première ristourne de fin d'année de 2% aux clients qui sont grossistes OU avec lesquels on a fait plus de 100 000 € de CA • une seconde ristourne de 1% aux clients qui sont grossistes ET qui nous ont acheté plus de 200 000 € de marchandises Nom du client EMELINE ACQUIN HENDOL JUNEZ GAUTIER
Type de client Grossiste Détaillant Grossiste Détaillant Grossiste
Achats 125 000 138 000 185 000 59 500 213 000
Ristourne 1 2 500 € 2 760 € 3 700 € 0€ 4 260 €
Ristourne 2 0€ 0€ 0€ 0€ 2 130 €
Travail à faire 1 1. Créer le classeur ARMAND_Nom et la feuille RISTOURNE 2. Saisir les données du tableau 3. Ajouter une colonne ristourne 1, dans laquelle on calculera la 1ère ristourne, et créer la formule suivante (respecter les majuscules) : =SI(OU(B4="Grossiste";C4>100000);C4*2%;0) 4. Ajouter une seconde colonne ristourne 2 et créer la formule suivante : =SI(ET(B2="Grossiste";C2>200000);C2*1%;0) 5. Enregistrer; faire un aperçu
L'entreprise souhaite verser une prime de fin d'année à ses représentants s'ils remplissent l'une OU l'autre des conditions suivantes : - avoir plus de 5 ans d'ancienneté - avoir réalisé plus d'1 million d'euros de chiffre d'affaires. Nom du Années Représentant d'ancienneté ARMAND 3 FLORA 6 NINIAN 7 SEBATI 1
Chiffre d'affaires 1 213 000 997 000 1 016 000 757 000
Prime 1 000 € 1 000 € 1 000 € 0€
Travail à faire 2 1. Créer, dans le classeur ARMAND_Nom, une nouvelle feuille nommée PRIMES et sauvegarder 2. Saisir les données du tableau 3. Ajouter une colonne PRIME et concevoir la formule de calcul de la prime 4. Enregistrer; faire un aperçu
416491847
P. COULAUD- 18/12/2013
33 / 40
Exercice 23 CLIENTS SI – ET – OU
Source : http://www.excelexercice.com/fonction/condition/fonctionetou/
Le tableau suivant fournit différentes informations sur vos clients. Vous souhaitez sélectionner dans ce fichier vos clients en fonction de certains critères. Si les critères correspondent à vos attentes, vous inscrirez 1 dans la colonne résultat, 0 si le test n’est pas concluant. De cette façon, il ne vous reste plus qu’à effectuer l’addition de la colonne résultat pour savoir combien de clients correspondent à vos critères. Résoudre toutes les questions cidessous : 1. Combien de clients sont mariés et ont un seul enfant ? 2. Combien de clients sont célibataires ou divorcés ? 3. Combien de clients sont mariés avec un revenu supérieur à 75 000 ? 4. Combien de clients sont des hommes, célibataires et sans enfants ? 5. Combien de clients sont des femmes avec 1 ou 2 enfants. 6. Combien de clients sont mariés ou en concubinage, avec un revenu supérieur ou équivalent à 50 000 et qui ont au moins 2 enfants ? Nom JANJUAH SARA CHAMPNEY JONAS SAMEN SATAKE LEON BYERLY WARNER GAUDEFROY CHAP SIMPSON FORDYCE TORTELLI ADCOCK CONTAT ZIZZARI FREYER JONES
Prénom Bob Sumit Robert Lee Michael Kaori Fernando Kimberly Norris Marie Mathew Aubrey David Olavo David Thibault Stephen Jean-Luc David
Genre Homme Homme Homme Homme Homme Homme Homme Femme Homme Femme Femme Homme Homme Femme Femme Homme Femme Homme Homme
Revenu 20 000 80 000 67 000 35 000 77 000 70 000 23 000 97 000 26 000 89 000 77 000 34 000 61 000 27 000 72 000 85 000 84 000 50 000 31 000
Situation Concubinage Marié Marié Concubinage Célibataire Marié Célibataire Concubinage Divorcé Concubinage Célibataire Divorcé Concubinage Célibataire Divorcé Concubinage Célibataire Célibataire Célibataire
Enfant 0 1 0 0 0 3 3 1 3 0 2 0 2 0 3 1 3 2 0
Résultat
Les solution sont : =SI(ET(E2=”Marié”;F2=1);1;0) =SI(OU(E2=”Célibataire”;E2=”Divorcé”);1;0) =SI(ET(E2=”Marié”;D2>75000);1;0) =SI(ET(C2=”Homme”;E2=”Célibataire”;F2=0);1;0) La solution pour la question 5 est : =SI(ET(C2=”Femme”;OU(F2=1;F2=2));1;0) La solution pour la question 6 est : =SI(ET(D2>=50000;F2>=2;OU(E2=”Marié”;E2=”Concubinage”));1;0)
416491847
P. COULAUD- 18/12/2013
34 / 40
Exercice 24 CONCOURS 1*** (2 → valeur cible) SI – ET – OU
Notes du concours Matières scientifiques
Matières littéraires
Math
Physique
Chimie
Français
Hist. Géo.
Anglais
Alain
18
15
16
14
18
15
Béa
13,5
12
10,8
12
11
10
Cloé
11
11,8
12
11
10
11
Daniel
12
11
13
15
14
13
Eric
14
12
10,5
13
9
13
Fernand
5
8
7
9
7
7
Travail à faire 1 ANNÉE N : le concours d'entrée d'une école d'ingénieur se fait selon le critère suivant : la moyenne générale des notes doit être supérieure ou égale à 12 1. 2. 3. 4.
Créer le classeur CONCOURS_Nom et la feuille ANNEE_N Saisir le tableau Effectuer le calcul de la moyenne générale pour chaque étudiant Réaliser un tableau RESULTATS en dessous du précédent tableau : a) la liste des étudiants est liée au 1er tableau (tout changement de nom dans le tableau NOTES se répercute automatiquement dans le tableau RESULTATS) b) dans une colonne ANNEE N, déterminer automatiquement les étudiants admis(e) ou refusé(e) 5. Mettre en forme les tableaux
Travail à faire 2 ANNÉE N + 1 : le concours d'entrée a changé et les critères de sélection sont maintenant les suivants : - la moyenne des notes scientifiques doit être supérieure ou égale à 12 ET - la moyenne des notes littéraires doit être supérieure ou égale à 10 1. Calculer les moyennes Scientifique et Littéraire 2. Créer un double de la feuille que vous nommerez ANNEE_N+1 3. Lier les noms de cette nouvelle feuille avec les noms de la feuille ANNEE_N (tous les noms se mettent à jour automatiquement en fonction des changements réaliser dans la feuille ANNEE_N) 4. Dans le tableau RESULTATS, ajouter une colonne ANNEE_N+1 et déterminer d'après les critères de l'ANNEE N+1, les étudiants admis(e) ou refusé(e) 5. Réaliser une représentation graphique en toile affichant la répartition des notes par matière
416491847
P. COULAUD- 18/12/2013
35 / 40
Travail à faire 3 ANNÉE N + 2 : le concours d'entrée a changé et les nouveaux critères de sélection sont les suivants (ces critères sont à remplir simultanément) : - la moyenne des notes scientifiques doit être supérieure ou égale à 12 - aucune note scientifique ne doit être inférieure à 11 - la moyenne des notes littéraires doit être supérieure ou égale à 11 Un rattrapage est prévu pour les étudiants refusés remplissant les conditions suivantes : avoir au moins une des deux moyennes des blocs scientifique ou littéraire supérieure à 12 1. Créer un double de la feuille ANNEE_N+1 que vous nommerez ANNEE_N+2 2. Dans le tableau RESULTATS, ajouter 2 colonnes : a) ANNEE_N+2 : les étudiants admis(e) ou refusé(e) d'après les critères b) les admissibles parmi ceux qui ont été refusés 3. Modifier les formules afin que les critères de notes et de moyennes ne soient plus inclus dans les formules, mais qu'ils soient contenus dans des cellules afin de pouvoir changer ces critères sans retoucher les formules
416491847
P. COULAUD- 18/12/2013
36 / 40
CORRIGÉ
Notes du concours Matières scientifiques
Matières littéraires
Moyenne
Math
Physique
Chimie
Français
Hist. Géo.
Anglais
Générale Scientifique Littéraire
Alain
18
15
16
14
18
15
16,00
16,33
15,67
Béa
13,5
12
10,8
12
11
10
11,55
12,10
11,00
Cloé
11
11,8
12
11
10
11
11,13
11,60
10,67
Daniel
12
11
13
15
14
13
13,00
12,00
14,00
Eric
14
12
10,5
13
9
13
11,92
12,17
11,67
Fernand
5
8
7
9
7
7
7,17
6,67
7,67
Math 20 Physique
Anglais
10
0
Chimie
Hist. Géo.
Français Alain Béa Daniel Eric
Cloé Fernand
Résultats Année N Critère MS
12
Critère ML
Année N+1 Année N+2 Admissibles 12
12
12
10
11
12
Critère NS
416491847
11
Alain
Admis(e)
Admis(e)
Admis(e)
Béa
Refusé(e)
Admis(e)
Refusé(e) Admissible
Cloé
Refusé(e)
Refusé(e)
Refusé(e)
Daniel
Admis(e)
Admis(e)
Admis(e)
Eric
Refusé(e)
Admis(e)
Refusé(e) Admissible
Fernand
Refusé(e)
Refusé(e)
Refusé(e)
P. COULAUD- 18/12/2013
37 / 40
Exercice 25 LYCEE** SI – ET – OU RANG Vous travaillez dans un lycée et êtes amené à concevoir un relevé de note. Travail à faire 1 1. Créer une nouvelle feuille nommée MATHS_T2 et enregistrer le classeur LYCEE_Nom 2. Saisir le tableau
Notes de mathématiques (trim. 2) Nom élève
Nombre élèves :
Devoir 1
Devoir 2
Devoir 3
Alain
8,25
10,50
11,25
Bernard
9,25
8,50
6,75
Claude
14,25
13,25
16,50
Daniel
14,50
12,00
11,50
Eric
17,75
16,25
18,50
Fernand
12,00
13,75
14,75
Guy
4,75
5,75
8,00
Hector
8,00
10,50
11,25
Ivan
6,75
8,00
10,75
Jules
11,00
10,00
11,50
3. Calculer : a) la moyenne de chaque élève b) la moyenne par devoir, ainsi que la note la plus haute et la note la plus basse c) le nombre d’élèves de la classe 4. Ajouter une colonne pour le préavis du professeur de mathématiques concernant le passage ou le redoublement dépend de la moyenne du 2ème trimestre qui doit être supérieure ou égale à 10. Effectuer le nécessaire afin de faire apparaître ce préavis. 5. Le professeur de mathématiques décide d'attribuer une mention à chaque élève en fonction de la moyenne : a) mention Insuffisant pour une moyenne inférieure à 8 b) mention Moyen pour une moyenne comprise entre 8 inclus et 12 c) mention Bien pour une moyenne supérieure à 12 inclus
416491847
P. COULAUD- 18/12/2013
38 / 40
Travail à faire 2 6. Dupliquer la feuille MATHS_T2 et nommer la nouvelle feuille TOUTES_MATIERES 7. Modifier le tableau : a) ajouter la colonne absence b) conserver le calcul des moyennes, des notes les plus hautes et les plus basses
Moyenne des notes toutes matières E Nom élève Alain
Trim. 1
Trim. 2
Nombre élèves :
Trim. 3
Absences
9,25
10,24
11,05
1
Bernard
10,00
9,36
8,96
14
Claude
11,50
12,80
12,80
12
Daniel
12,30
12,30
11,28
1
Eric
12,45
13,21
14,01
Fernand
11,20
11,50
11,60
Guy
6,80
6,74
7,00
Hector
9,20
10,28
10,00
4
Ivan
7,86
6,80
8,60
15
Jules
9,86
10,10
9,50
2
Karl
13,56
13,59
14,12
1
c) lier les noms avec la feuille MATHS_T2 d) saisir les données du tableau 8. Déterminer l'avis de passage selon les critères suivants : a) un étudiant passe si : - sa moyenne est supérieure ou égale à 10 OU - sa moyenne est comprise entre 8 (inclus) et 10, et si il a été absent moins de 10 fois (inclus) b) un étudiant redouble si : - sa moyenne est inférieure à 8 OU - sa moyenne est comprise entre 8 (inclus) et 10, et si il a été absent plus de 10 fois 9. Ajouter une colonne indiquant le classement des élèves (fonction RANG) Application 10. Dans la feuille MATH_T2, une appréciation particulière est donnée aux élèves dont la moyenne est supérieure ou égale à 15 : « Félicitations » élaborer la formule nécessaire 11. Dans la feuille TOUTESMATIERES, on veut indiquer par une remarque « En progression » si l'élève à progresser régulièrement ; pour cela il faut que la moyenne du trimestre 1 soit inférieure à la moyenne du trimestre 2 ellemême inférieure à celle du trimestre 3. 12. Effectuer une représentation graphique sous forme de secteur des absences
416491847
P. COULAUD- 18/12/2013
39 / 40
CORRIGÉ Notes de mathématiques (trim. 2)
B
Nombre élèves :
Nom élève
Devoir 1
Devoir 2
8,25
10,50
Alain
10
D
Devoir 3 Moyenne 11,25
Préavis
Mention
10,00
Passage
Moyen Moyen
Bernard
9,25
8,50
6,75
8,17
Redoublement
Claude
14,25
13,25
16,50
14,67
Passage
Bien
Daniel
14,50
12,00
11,50
12,67
Passage
Bien
Eric
17,75
16,25
18,50
17,50
Passage
Bien
Fernand
12,00
13,75
14,75
13,50
Passage
Bien
Guy
4,75
5,75
8,00
6,17
Redoublement
Insuffisant
Hector
8,00
10,50
11,25
9,92
Redoublement
Moyen
Ivan
6,75
8,00
10,75
8,50
Redoublement
Moyen
Jules
11,00
10,00
11,50
10,83
Passage
Moyen
Karl
15,50
16,25
15,00
15,58
Passage
Bien
Moyenne
10,65
10,85
12,08
Note maximale
17,75
16,25
18,50
Note minimale
4,75
5,75
6,75
Moyenne des notes toutes matières
Nombre élèves :
E Trim. 2
Félicitations
Trim. 3
11
F
G
Absences
Moyenne
Avis de passage
Progression
Rang
9,25
10,24
11,05
1
10,18
Passage
En progression
6
Bernard
10,00
9,36
8,96
14
9,44
Redoublement
9
Claude
11,50
12,80
12,80
12
12,37
Passage
3
Daniel
12,30
12,30
11,28
1
11,96
Passage
Eric
12,45
13,21
14,01
13,22
Passage
En progression
2
Fernand
11,20
11,50
11,60
11,43
Passage
En progression
5
Alain
Trim. 1
Félicitations
C
A
Nom élève
Appréciation
4
Guy
6,80
6,74
7,00
1
6,85
Redoublement
Hector
9,20
10,28
10,00
4
9,83
Passage
7
Ivan
7,86
6,80
8,60
15
7,75
Redoublement
10
Jules
9,86
10,10
9,50
2
9,82
Passage
8
Karl
13,56
13,59
14,12
13,76
Passage
Nombre de passages
8
Moyenne
10,36
10,63
10,81
Note maximale
13,56
13,59
14,12
Note minimale
6,80
6,74
7,00
% de passages
11
En progression
1
72,73%
H
Répartition des abscences 2.00% 4.00% 28.00% 30.00%
24.00%
416491847
Alain Claude Eric Guy Ivan Karl
Bernard Daniel Fernand Hector Jules
8.00% 2.00% 2.00%
P. COULAUD- 18/12/2013
40 / 40
SI
Présentation SI La fonction SI permet d’afficher un résultat différent en fonction du contenu d’une autre cellule. La syntaxe utilisée est la suivante : =SI (condition à évaluer ; valeur si la condition est VRAIE; valeur si la condition est FAUSSE) Le résultat de la formule peut être une valeur numérique, une formule de calcul ou du texte. Dans les conditions à évaluer vous pouvez utiliser les opérateurs suivants : Égal = Supérieur à > Inférieur à < Supérieur ou égal à >= Inférieur ou égal à <=
Exemples 1. Si l’élève a 18 ans ou plus, alors il est majeur, sinon il est mineur. La formule en C1 sera : =SI(B1>=18;«majeur»;«mineur») B1 représente la cellule qui contient l’âge de l’élève. Le texte se met entre guillemets. Que se passetil si vous remplacez 19 par 17 ? par 18 ?
=SI(B1>=18;"majeur";"mineur") A B C 1 Age : 19 majeur 2 3
2. Si le montant de la facture est supérieure à 200 €, alors vous avez une remise de 3%, sinon vous n’avez pas de remise La formule en B2 du calcul de la remise sera : =SI(B1>200;B1*3%;0) B1 est la cellule qui contient le montant de la facture. Que se passetil si vous remplacez 200 par 100 ? par 300 ?
=SI(B1>200;B1*3%;0) A 1 Montant brut 2 Remise 3
B
C 300 9
APPRENDRE SUR INTERNET http://www.excelexercice.com/ **** Fonction > Condition > Fonction SI 416491847
P. COULAUD- 18/12/2013
1 / 40
TABLEUR 416491847
SI
Utiliser des références relatives et absolues Insérer les fonctions : SOMME, SI
Exercice 1 LAVOIE (3)
FICHE DE DÉCOMPTE DES HEURES
AVRIL
Nom : LAVOIE Heures normales : 35 Heures à +25% : de 36h à 43h SEMAINES
Nb d'heures
HS + 25%
Semaine 1
37
2
Semaine 2
35
Semaine 3
38
3
Semaine 4
41
6
151
11
TOTAUX
Travail à faire 1. Ouvrir OU créer le classeur MICROCD_ Nom; puis créer OU modifier la feuille nommée LAVOIE 2. Saisir les données du tableau, si ce n'est déjà fait 3. Calculer les heures supplémentaires à +25% (n'hésitez pas à utiliser l'AIDE) exemple : =SI($C$5
416491847
P. COULAUD- 18/12/2013
2 / 40
Exercice 2 CRAYON SI simple
Stocks Qté commandée
Qté Disponible
Qté livrée
Reste à livrer
Stylo bille
50
45
45
5
Stylo à plume
30
32
30
0
Gomme
37
52
37
0
Crayon couleur
12
15
12
0
Crayon papier
75
49
49
26
Pièces
Travail à faire 1. Créer le classeur CRAYON _Nom et la feuille COMMANDE 2. Saisir les données du tableau 3. Mettre en forme 4. Réaliser les formules (Insertion > fonction > SI) et utiliser l'outil de recopie de formules : a) calcul de la quantité livrée b) calcul du reste à livrer 5. Enregistrer et faire un aperçu
416491847
P. COULAUD- 18/12/2013
3 / 40
TABLEUR 416491847
MOYENNE MIN MAX SI
Utiliser des références relatives et absolues Insérer les fonctions : MOYENNE, MIN, MAX, SI
Exercice 3 CADO Vous travaillez au service commercial de la société CADO qui commercialise des cadeaux d'affaires. Huit représentants prospectent dans la région Ouest. Une prime de 3% des ventes d'un vendeur est attribuée à ceux dont les ventes trimestrielles sont supérieures à la moyenne trimestrielle. Vous êtes chargés de concevoir un tableau qui permette de calculer • le total des ventes par vendeur • le total des ventes par mois ainsi que la moyenne, le minimum et le maximum • le total, la moyenne, le minimum et le maximum du trimestre • la prime trimestrielle de chaque représentant
ANALYSE DES VENTES : 1er trimestre Vendeurs LAPORTE BOSSE MARTIN PRÉAU DUTEIL FOREST BLONDEL PINAULT Totaux Moyenne Ventes minimum Ventes maximum
janvier 6 200 2 200 3 700 6 250 2 250 2 150 3 700 3 900 30 350 3 794 2 150 6 250
février 5 900 3 050 3 900 6 060 2 240 3 240 3 300 4 500 32 190 4 024 2 240 6 060
mars 4 550 3 300 4 200 6 800 3 300 2 950 5 900 4 200 35 200 4 400 2 950 6 800
Totaux 16 650 8 550 11 800 19 110 7 790 8 340 12 900 12 600 97 740 12 218 7 790 19 110
Prime 500
573
387 378 1 838 459 378 573
Travail à faire 1. Créer le classeur sous CADO_Nom et la feuille TRIMESTRE_1 2. Saisir les données du tableau 3. Réaliser les formules et utiliser l'outil de recopie de formules a) calcul de la moyenne par mois : fonction MOYENNE b) calcul des minimum et maximum par mois : MIN et MAX c) calcul de la prime : fonction SI 4. Mettre en forme : a) titre en Arial 14, gras, centré b) hauteur des lignes augmentée pour aérer la tableau 5. Enregistrer et faire un aperçu 6. Faire un diagramme en secteur des ventes trimestrielles de chaque commercial en affichant le pourcentage sur le diagramme et la légende à coté
416491847
P. COULAUD- 18/12/2013
4 / 40
Répartition des ventes trimestrielles par représentant 12.89%
17.03%
LAPORTE BOSSE 13.20% MARTIN PRÉAU DUTEIL FOREST
8.75%
8.53%
12.07% 7.97% 19.55%
416491847
P. COULAUD- 18/12/2013
5 / 40
Exercice 4 RAPPEL SI L’entreprise RAPPEL décide de créer un échéancier de suivi des factures. Une lettre de rappel sera envoyée si aucun règlement n'est parvenu au plus tard 8 jours après la date d’échéance.
DATE DU JOUR :
19/03/66 Date d'échèance 18/02/66
Compte à rebours 0
Lettre de rappel
Olivier
Date de facture 08/02/66
PA1013
Paul
08/02/66
18/02/66
29
Oui
QU1014
Quentin
08/02/66
25/02/66
0
RO1015
Roll
09/02/66
25/02/66
22
Oui
SO1016
Soul
09/02/66
28/02/66
19
Oui
TR1017
Trin
09/02/66
28/02/66
0
02/03/66
UR1018
Urban
10/02/66
12/02/66
0
14/02/66
VA1019
Vaux
10/02/66
28/02/66
19
WI1020
Wisty
10/02/66
12/02/66
0
15/02/66
XA1021
Xan
10/02/66
28/02/66
0
02/03/66
N° facture
Client
OL1012
Date de règlement 06/02/66 15/02/66
Oui
Travail à faire 1. Créer le classeur RAPPEL_ Nom et créer la feuille LR (lettre de rappel) 2. Saisir les données du tableau 3. Effectuer les calculs des colonnes : a) compte à rebours entre la date du jour et la date d'échéance b) lettre de rappel : laisser un délai de 8 jours après l'échéance avant de faire la lettre. 4. Enregistrer ; faire un aperçu
416491847
P. COULAUD- 18/12/2013
6 / 40
Exercice 5 EVAL 1 SI – MOYENNE – MIN – MAX Concevoir un tableau comme le modèle cidessous. Information : Le montant des coûts correspond à 65 % du C.A., et le pourcentage peut évoluer à tout moment. Une observation apparaît lorsque le résultat mensuel est strictement supérieur à 50 000 €. Travail à faire 1. Créer le classeur EVAL_ Nom et créer la feuille CA 2. Saisir les données du tableau 3. Effectuer les calculs suivants : a) Coût 65% = CA * 65% = B5*$C$4 b) Résultat = CA – Coût = B5C5 4. Calculer les totaux, la moyenne du résultat, le plus fort et le plus faible des résultats 5. Colonne observation : si le résultat est supérieur à 50 000 € alors le résultat mensuel est correct, ce qui donne la formule =SI(D6>50000;"Résultat mensuel correct";"") 6. Mettre en forme et en page le tableau 7. Réaliser un graphique en secteur représentant le pourcentage de chaque mois 8. Enregistrer; faire un aperçu
Analyse du chiffre d'affaires de l'année N CA HT Janvier Février Mars Avril Mai Juin Juillet Août Septembre Octobre Novembre Décembre TOTAL
125 000 147 000 156 000 123 000 154 000 125 000 110 000 178 000 177 000 90 000 162 000 220 000 1 767 000
Coût 65% 81 250 95 550 101 400 79 950 100 100 81 250 71 500 115 700 115 050 58 500 105 300 143 000 1 148 550
Résultat 43 750 51 450 54 600 43 050 53 900 43 750 38 500 62 300 61 950 31 500 56 700 77 000 618 450
Résultat moyen Résultat le plus fort de l'année Résultat le plus faible de l'année
416491847
P. COULAUD- 18/12/2013
Observation Résultat mensuel correct Résultat mensuel correct Résultat mensuel correct
Résultat mensuel correct Résultat mensuel correct Résultat mensuel correct Résultat mensuel correct
51 538 77 000 31 500
7 / 40
Résultat
7.07%
12.45%
8.32% 9.17% 8.83% 5.09% 6.96% 10.02% 8.72% 7.07%
416491847
6.23%
Janvier Février Mars Avril Mai Juin Juillet Août Septembre Octobre Novembre Décembre
10.07%
P. COULAUD- 18/12/2013
8 / 40
TABLEUR 416491847
MOYENNE SI
Utiliser des références relatives et absolues Insérer les fonctions : MOYENNE, SI
Exercice 6 BAR DES VOYAGEURS Vous travaillez à l'hôtel des Voyageurs et êtes en charge de la gestion du bar de l'hôtel. Vous devez faire un tableau dans lequel vous saisirez le montant des ventes TTC du bar chaque jour. Le tableau contiendra au total six mois, A la fin de chaque mois, vous ferez apparaître le total TTC, le montant de la TVA (taux normal), le total HT ainsi que la moyenne journalière. Votre travail consiste à réaliser un modèle de tableau qui servira ensuite à la saisie des recettes du bar. Tous les six mois, on ouvrira un nouveau modèle pour commencer un nouveau tableau. Le modèle est le suivant : HOTEL DES VOYAGEURS Recettes du bar
Taux TVA : 19,60%
Mois Jours 1 2 3 ... ... 28 29 30 31 TOTAL TTC TVA HT Moy/jour
416491847
P. COULAUD- 18/12/2013
9 / 40
Travail 1 à faire 1. Créer le classeur BAR_DES_VOYAGEURS_Nom et la feuille SEMESTRE_MODELE 2. Réaliser le modèle avec les formules suivantes a) Total TTC : fonction SOMME b) HT = TTC / (1+ taux de TVA) c) TVA = TTC HT d) Moyenne/jour : fonction MOYENNE; afin d'éviter des messages d'erreur, utiliser la fonction SI : SI(B18=0;0;MOYENNE(B6:B17)) 3. Mettre en forme et en page le tableau 4. Enregistrer; faire un aperçu
6. Saisir les données cicontre dans la feuille SEMESTRE_1 7. Vérifier vos formules et la cohérence de vos résultats 8. Enregistrer; faire un aperçu
Recettes journalières du bar janvier
février
mars
182,20
114,50
215,60
93,70
154,70
146,70
129,70
85,80
290,20
105,60
213,50
227,00
127,00
122,50
197,50
99,50
133,20
138,40
225,30
228,50
141,00
125,30
243,20
134,60
161,30
132,50
166,00
223,60
187,50
153,00
148,90
154,00
141,20
225,60
152,00
224,70
150,00
147,40
215,20
102,60
303,70
76,60
325,60
185,20
214,20
227,60
271,00
132,40
128,00
71,00
117,20
89,60
131,00
133,60
148,90
148,80
131,00
195,40
224,30
226,70
172,60
202,00
156,70
168,40
221,20
145,00
146,20
CONGES ANNUELS
Travail à faire 2 5. Copier la feuille SEMESTRE_MODELE en SEMESTRE_1 ; enregistrer
269,50
172,60
173,80
202,30
143,80
416491847
P. COULAUD- 18/12/2013
10 / 40
TABLEUR 416491847
SI imbriqués
Présentation SI imbriqués On peut imbriquer jusqu’à sept fonctions SI les unes dans les autres : = SI(condition;si VRAI ; SI(condition;si VRAI ; SI(condition;si VRAI ; SI(condition;si VRAI ; SI(condition;si VRA I; SI(condition;si VRAI ; SI(condition;si VRAI;si FAUX))))))) =SI(condition;si VRAI; SI(condition;si VRAI; SI(condition;si VRAI; SI(condition;si VRAI; SI(condition;si VRAI; SI(condition;si VRAI; SI(condition;si VRAI;si FAUX)))))))
Exemple : Calcul du prix d’entrée selon l’âge de la personne : • Age <= 8 ans 10€ • Age entre 9 et 17 ans 15€ • Age >= 18 ans 20€ La formule de calcul du prix sera la suivante : =SI(B1<=8;10; SI(B1>=18;20;15)) =SI(B1<=8;10;SI(B1>=18;20;15)) A B 1 Age 17 2 Prix entrée 15 € 3
416491847
P. COULAUD- 18/12/2013
C
11 / 40
TABLEUR 416491847
SI imbriqués
Gérer plusieurs feuilles Nommer des cellules Utiliser la fonction : SI
Exercice 7 ROUIX 1 (2 → SI, ET, OU) Faites les calculs qui s’imposent en tenant compte des indications suivantes : • le taux de remise est de 3% si le hors taxes dépasse 10 000 € • pour les grossistes ce taux sera de 5% et à condition que le hors taxes dépasse 10 000 €
CALCUL DES REMISES CLIENTS GARD
Grossiste
Brut Hors taxes 14 650,00
HECTOR
Détaillant
8 703,00
0%
0,00
8 703,00
IBO
Particulier
14 540,00
3%
436,20
14 103,80
JURA
Grossiste
8 775,00
0%
0,00
8 775,00
KILL
Particulier
7 602,00
0%
0,00
7 602,00
LAMA
Grossiste
11 215,00
5%
560,75
10 654,25
MANN
Détaillant
8 703,00
0%
0,00
8 703,00
NUZ
Détaillant
17 525,00
3%
525,75
16 999,25
Nom
Type client
5%
Montant Remise 732,50
Net Hors taxes 13 917,50
Remise
Travail à faire 1. Créer le classeur ROUIX_Nom et la feuille REMISE 2. Saisir les données du tableau 3. Effectuer les calculs nécessaires en utilisant des SI IMBRIQUÉS : =SI (chiffres affaires<10 000 ; 0 ; SI(client="Grossiste" ; remise 2 ; remise 1)) 4. Enregistrer; faire un aperçu
CORRIGE
GARD
Grossiste
Brut Hors taxes 14 650,00
5%
Montant Remise 732,50
HECTOR
Détaillant
8 703,00
0%
0,00
8 703,00 =SI(C5<10000; 0 ; SI(B5="Grossiste" ; 0,05; 0,03))
IBO
Particulier
14 540,00
3%
436,20
14 103,80 =SI(C6<10000; 0 ; SI(B6="Grossiste" ; 0,05; 0,03))
JURA
Grossiste
8 775,00
0%
0,00
8 775,00 =SI(C7<10000; 0 ; SI(B7="Grossiste" ; 0,05; 0,03))
Nom
Type client
416491847
Remise
Net Formules de la remise Hors taxes 13 917,50 =SI(C4<10000; 0 ; SI(B4="Grossiste" ; 0,05; 0,03))
P. COULAUD- 18/12/2013
12 / 40
Exercice 8 BISCOTTE SI IMBRIQUÉE Travail à faire 1. Créer le classeur BISCOTTE_Nom et la feuille TAUX_TVA 2. Saisir les données du tableau Code TVA 1 =
2,10%
Code TVA 2 =
10,00%
Code TVA 3 =
20,00%
ARTICLES
CODE TVA
TAUX TVA
ECRAN
2
10,00%
BISCOTTES
1
2,10%
IMPRIMANTE
3
20,00%
MULOT
1
2,10%
CLAVIER
1
2,10%
SCANNER
3
20,00%
3. Réaliser les formules nécessaires afin de déterminer le taux de TVA à partir des codes TVA. 4. Enregistrer
CORRIGE Code TVA 1 =
2,10%
Code TVA 2 =
10,00%
Code TVA 3 =
20,00%
ARTICLES
CODE TVA
ECRAN
2
BISCOTTES
1
IMPRIMANTE
3
MULOT
1
CLAVIER
1
SCANNER
3
416491847
TAUX TVA 10,00% =SI(B43=1;$B$38; SI(B43=2;$B$39;$B$40)) 2,10% =SI(B44=1;$B$38; SI(B44=2;$B$39;$B$40)) 20,00% =SI(B45=1;$B$38; SI(B45=2;$B$39;$B$40)) 2,10% =SI(B46=1;$B$38; SI(B46=2;$B$39;$B$40)) 2,10% =SI(B47=1;$B$38; SI(B47=2;$B$39;$B$40)) 20,00% =SI(B48=1;$B$38; SI(B48=2;$B$39;$B$40))
P. COULAUD- 18/12/2013
13 / 40
Exercice 9 ENFANTS SI IMBRIQUÉE Travail à faire 1. Créer le classeur ENFANTS_Nom et la feuille PRIME 2. Saisir les données du tableau
SEXE
NBRE D'ENFANTS
ANNE
F
0
0 €
BERTRAND
M
1
50 €
CLOÉ
F
2
100 €
DAMIEN
M
1
50 €
ELRIC
F
5
350 €
FANNY
M
3
210 €
GUY
F
2
100 €
HUGO
M
2
100 €
NOMS
PRIME
3. Calculer la prime accordée aux salariés sachant que : - les salariés sans enfants n'y ont pas droit - cette prime est fonction du nombre d'enfants : la prime s'élève à 50 € par enfant pour les salariés avec 1 ou 2 enfants et à 70 € par enfant pour les salariés ayant 3 enfants ou plus. 4. Enregistrer
SEXE
NBRE D'ENFANTS
ANNE
F
0
0 € =SI(C31=0;0; SI(C31<=2; 50*C31 ; 70*C31))
BERTRAND
M
1
50 € =SI(C32=0;0; SI(C32<=2; 50*C32 ; 70*C32))
CLOÉ
F
2
100 € =SI(C33=0;0; SI(C33<=2; 50*C33 ; 70*C33))
NOMS
416491847
PRIME
P. COULAUD- 18/12/2013
14 / 40
Exercice 10 ACTEURS SI IMBRIQUÉE Les Oscar sont des trophées remis aux héros ayant, soit tourné plus de 15 films, soit ayant dépassé les 200 millions d'entrées. De plus, un Oscar d'Or est attribué au héro ayant le plus de films et le plus d'entrées. Travail à faire 1. Créer le classeur ACTEURS_Nom et la feuille OSCAR 2. Saisir les données du tableau Nombre de films
Spectateurs en millions
CHARLOT
16
35
D'ARTAGNAN
12
210
CLEOPATRE
8
26
ARSENE LUPIN
18
250
MICKEY
14
150
NOURNOURS
14
240
SUPERMAN
9
160
ZORRO
13
90
Héros
3. Déterminer, dans une colone supplémentaire, qui mérite un simple Oscar 4. Déterminer, dans une colone supplémentaire, qui recevra l'Oscar d'Or ; il vous faut calculer a) le MAXIMUM des films b) le MAXIMUM des entrés c) puis les conparer aux films et entrées de chaque acteur 5. Enregistrer
Nombre de films
Spectateurs en millions
CHARLOT
16
35
Oscar
=SI(B3>=15;"Oscar";SI(C3>=200;"Oscar";""))
D'ARTAGNAN
12
210
Oscar
=SI(B4>=15;"Oscar";SI(C4>=200;"Oscar";""))
CLEOPATRE
8
26
ARSENE LUPIN
18
250
MICKEY
14
150
NOURNOURS
14
240
SUPERMAN
9
160
ZORRO
13
90
18
250
Héros
Maximum
416491847
Oscar
Oscar d'Or
=SI(B5>=15;"Oscar";SI(C5>=200;"Oscar";"")) Oscar
Oscar d'Or
=SI(D6="Oscar";SI(B6=$B$11;SI(C6=$C$11;"Oscar d'Or";"");"");"")
Oscar
P. COULAUD- 18/12/2013
15 / 40
Exercice 11 NEGOCE (2) SI IMBRIQUÉE – NOMMER UNE CELLULE Vous travaillez au service gestion de l'entreprise NEGOCE et vous êtes chargé de calculer les commissions trimestrielles des revendeurs. Vous disposez des montants des ventes des revendeurs pour les deux derniers trimestres et vous devez regrouper ces chiffres dans un tableau. Ensuite, vous calculerez les commissions selon les tranches dans un autre tableau qui sera automatiquement mis à jour en cas de modifications du premier tableau (les tranches ne sont pas cumulables). Informations Les données sont les suivantes : LOSSE
MARTIN
NORCA
juillet
7 400
2 400
0
août
2 400
4 500
0
septembre
4 800
4 100
0
octobre
0
2 900
2 700
novembre
0
6 700
960
décembre
0
8 400
4 800
Calcul des commissions : 3ème trimestre
• •
ventes = 0 ventes > 0 les ventes
4ème trimestre
• •
ventes = 0 alors commission = 0 0 < ventes < 12 000 € alors commission de 5% sur les ventes 12 000 € ventes alors commission de 7% sur les ventes
•
alors alors
commission = 0 commission de 5% sur
Travail à faire 1 1. Reprendre le classeur NEGOCE_Nom et créer la feuille SEMESTRE_2 2. Saisir les données des ventes 3. Réaliser les formules de calcul des ventes par trimestre par représentant en nommant ces montants (6 noms) : Insertion > Noms NOMMER UNE CELLULE Cette commande permet de donner un nom à une cellule en vue d'utiliser ce nom dans une formule de calcul. Sélectionner la cellule (ou le bloc de cellules) à nommer, puis LIBREOFFICE EXCEL
416491847
insertion > Noms > Définir
Formules > Définir un nom
P. COULAUD- 18/12/2013
16 / 40
4. Mettre en forme et en page le tableau 5. Enregistrer et faire un aperçu Travail à faire 2 6. Utiliser, dans votre classeur NEGOCE, une nouvelle feuille nommée COMMISSION_S2 et sauvegarder 7. Réaliser le tableau des commissions : a) rappeler le montant des ventes par liaison avec la feuille SEMESTRE_2 en utilisant les noms des cellules correspondantes b) calcules les commissions par trimestre : fonction SI 8. Mettre en forme et en page le tableau 9. Enregistrer et faire un aperçu
CORRIGE COMMISSIONS DES VENDEURS 3ème trimestre Ventes
Commission
Ventes
Commission
LOSSE
14 600
730
0
0
MARTIN
11 000
550
18 000
1 260
NORCA
0
0
8 460
423
25 600
1 280
26 460
1 683
TOTAL
416491847
4ème trimestre
P. COULAUD- 18/12/2013
17 / 40
Exercice 12 LAVOIE (4)**** SI IMBRIQUÉE
FICHE DE DÉCOMPTE DES HEURES
AVRIL
Nom : LAVOIE Heures normales : 35 Heures à +25% : de 36h à 43h Heures à +50% : à partir de 44h SEMAINES
Nb d'heures
HS + 25%
Semaine 1
37
2
Semaine 2
46
8
Semaine 3
38
3
Semaine 4
44
8
1
165
21
4
TOTAUX
HS + 50%
3
Travail à faire 1. Ouvrir OU créer le classeur MICROCD_ Nom; puis créer OU modifier la feuille nommée LAVOIE 2. Saisir les données du tableau, si ce n'est déjà fait 3. Calculer les heures supplémentaires : a) Explication de la formule pour les heures à +25% : SI (je travaille +35 h alors SI (je travaille 44 h alors je calcule mes HS 25 % sinon je travaille sur toute la tranche 36 h 43 h) =SI($H$5
416491847
P. COULAUD- 18/12/2013
18 / 40
Exercice 13 JARDIFLOR*** SI IMBRIQUÉE Vous travaillez dans la SARL JARDIFLOR dont le responsable est M. SPACEVERT. Celuici désire mettre en place un système de ristournes pour les bons clients. Il vous remet le document suivant avec les tranches qui sont cumulables et les taux de ristourne :
TABLEAU DE CALCUL DES RISTOURNES N° du client :
411021
Nom du client
TERRES ET DECO
Montant du chiffre d'affaire H.T. :
2130,66 Montant de la Taux de ristourne tranche
Tranches de chiffres d'affaires
Montant
de
0,00 à
500,00
500
1%
5,00
de
500,00 à
1 200,00
700
2%
14,00
de
1 200,00 à
3 000,00
931
3%
27,92
de
3 000,00 à
10 000,00
4%
0,00
AVOIR HT TVA
46,92 19,60%
AVOIR TTC
9,20 56,12
Travail à faire 1. Créer le classeur JARDIFLOR_Nom et la feuille RISTOURNE 2. Saisir les données du tableau 3. Réaliser les formules de calcul : a) sur les tranches ; utiliser la fonction SI) b) tester la cohérence de vos formules en simulant différents chiffres d'affaires HT 4. Mettre en forme et en page le tableau 5. Enregistrer et faire un aperçu
416491847
P. COULAUD- 18/12/2013
19 / 40
Exercice 14 APPRECIATIONS *** SI IMBRIQUÉE x 4 Un enseignant a noté ses élèves sur un devoir et veut élaborer un tableau lui permettant de convertir les notes en appréciations :
APPRECIATIONS Tableau de notes Nom Notes Anaud 12 Bernard 4 Constance 5 David 9 Ermine 16 Fernand 14 Gaspard 5 Hector 18 Ivan 17 Jean 8 Louis 12 Marc 15 Noa 16
Appréciation C E D C B B D A A D C B B
Tableau de conversion Notes Appréciation Borne sup. De 17 à 20 A De 13 à 16 B 16 De 9 à 12 C 12 De 5 à 8 D 8 De 0 à 4 E 4
Travail à faire 1 1. Créer le classeur NOTES_Nom et feuille APPRECIATIONS 2. Saisir le tableau des élèves avec leur note 3. Ajouter une colonne avec la formule adéquate afin d'attribuer automatiquement les appréciations : =SI(B3>16;"A"; SI(B3>12;"B"; SI(B3>8;"C"; SI(B3>4;"D";"E")))) 4. Mettre en forme et en page le tableau 5. Enregistrer et faire un aperçu 6. Reprendre et modifier la formule afin qu'il n'y ait ni lettres (A, B …) ni chiffres (16, 12 …) mais des liens vers le tableau de conversion : =SI(B4>G$5;F$4; SI(B4>G$6;F$5; SI(B4>G$7;F$6; SI(B4>G$8;F$7;F$8)))) 7. Enregistrer et faire un aperçu
416491847
P. COULAUD- 18/12/2013
20 / 40
Exercice 15 NEGOCE 1** SI IMBRIQUÉE x 4 Les clients de l'entreprise NEGOCE ont été facturé des montants suivants : Les frais de port facturés aux clients s'élèvent à • 100 € si le total des marchandises est inférieur à 2 000 € • 50 € si le total des marchandises est compris entre 2 000 € et 5 000 € • gratuit si le total des marchandises dépasse 5 000 €.
Client ARMAND BOULI CIRIL DANIEL ELVET FRASSE GART HECTOR IRWIN JURA
Montant 2 550,00 € 5 320,00 € 950,00 € 1 450,00 € 6 845,00 € 3 245,00 € 560,00 € 1 980,00 € 4 980,00 € 8 640,00 €
Travail à faire 1 1. Créer le classeur NEGOCE_Nom et la feuille PORTCLIENT 2. Saisir le tableau des clients puis ajouter une colonne afin de calculer les frais de port de chacun =SI(B23<2000;100; SI(B23<5000;50;"Offert")) 3. Mettre en forme et en page le tableau 4. Enregistrer et faire un aperçu Travail à faire 2 1. Saisir le tableau du barème des frais de port 2. Dans le tableau des clients, ajouter une seconde colonne de frais de port selon le nouveau barème (les formules devront être liées au tableau des barèmes) :
Barème des frais de port Moins de 1 000 € De 1 001 € à 2 000 € De 2 001 € à 4 000 € De 4 001 € à 6 000 € A partir de 6 001
Frais de port 100,00 € 80,00 € 60,00 € 30,00 € Offert
Borne sup. 1 000,00 € 2 000,00 € 4 000,00 € 6 000,00 €
=SI(B23<$I$23;$H$23; SI(B23<$I$24;$H$24; SI(B23<$I$25; $H$25; SI(B23<$I$26; $H$26;$H$27)))) 3. Mettre en forme et en page le tableau 4. Enregistrer et faire un aperçu
CORRIGE Client ARMAND BOULI CIRIL DANIEL ELVET FRASSE GART HECTOR IRWIN JURA
416491847
Montant Frais port (1) Frais port (2) 2 550,00 € 50,00 € 60,00 € 5 320,00 € Offert 30,00 € 950,00 € 100,00 € 100,00 € 1 450,00 € 100,00 € 80,00 € 6 845,00 € Offert Offert 3 245,00 € 50,00 € 60,00 € 560,00 € 100,00 € 100,00 € 1 980,00 € 100,00 € 80,00 € 4 980,00 € 50,00 € 30,00 € 8 640,00 € Offert Offert
P. COULAUD- 18/12/2013
21 / 40
TABLEUR 416491847
NB.SI SOMME.SI
Présentation NB.SI et SOMME.SI NB.SI permet de compter le nombre de cellules d'une plage qui répondent à un critère donné : NB.SI(plage;critère) SOMME.SI permet d'additionner des cellules en fonction de critères prédéfinis SOMME.SI(plage;critère;somme_plage)
Exemple 1 : NB.SI Voici un tableau de personnes avec leur âge. Déterminer le nombre de personnes ayant : 1. moins de 35 ans :
=NB.SI(C2:C14;"<35")
2. au moins 50 ans :
=NB.SI(C2:C14;">=50")
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
416491847
A
B
PRENOM
AGE
Luc
25
Pierre
32
Paul
33
Jacques
21
Anne
36
Marie
37
Hubert
47
Charles
51
Solange
52
Victor
58
Fleur
25
Antoine
28
Laure
31
C
< 35
7
=NB.SI(C2:C14;"<35")
> 50
3
=NB.SI(C2:C14;">=50")
P. COULAUD- 18/12/2013
22 / 40
Exemple 2 : SOMME.SI Voici un tableau listant différents titres de livres avec leur genre. Il s'agit de déterminer le stock pour chaque genre : 1. Policier 2. Roman 3. SF 4. BD
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
A
B
C
TITRE
GENRE
STOCK
Titre 1
Policier
10
Titre 2
Roman
5
Titre 3
SF
2
Titre 4
BD
5
Titre 5
Roman
5
Titre 6
Roman
4
Titre 7
Policier
8
Titre 8
BD
3
Titre 9
SF
2
Titre 10
Roman
4
Titre 11
SF
2
Titre 12
BD
6
Titre 13
Policier
3
D
E
SOMME Policier
21
=SOMME.SI(B2:B14;"Policier";C2:C14)
SOMME Roman
18
=SOMME.SI(B2:B14;"Roman";C2:C14)
SOMME SF
6
=SOMME.SI(B2:B14;"SF";C2:C14)
SOMME BD
14
=SOMME.SI(B2:B14;"BD";C2:C14)
APPRENDRE SUR INTERNET http://www.excelexercice.com/ **** Fonction > Condition
416491847
P. COULAUD- 18/12/2013
23 / 40
TABLEUR 416491847
NB.SI SOMME.SI SOMMEPROD
Gérer plusieurs feuilles Nommer des cellules Utiliser les fonctions NB.SI, SOMME.SI, SOMMEPROD
Exercice 16 VOTE NB.SI Lors d'un vote, les bulletins dépouillés ont donné le résultat suivant :
Pierre Yves Anne Pierre Pierre Yves Sophie Anne Yves Anne Pierre Yves Sophie Anne Pierre Jules
Urne N°1 Pierre Anne Sophie Yves Jules Anne Pierre Anne Anne Anne Yves Sophie Anne Jules Jules Anne
Pierre Yves Sophie Anne Pierre Pierre Yves Sophie Anne Sophie Sophie Pierre Yves Yves Sophie Jules
En utilisant la fonction NB.SI, déterminez le nombre de vote pour chaque candidat Travail à faire 1. Créer le classeur VOTE_Nom et la feuille RESULTAT 2. Saisir les données du tableau 3. Déterminez le nombre de votes pour chaque candidat en utilisant la fonction NB.SI 4. Mettre en forme et en page les tableaux 5. Enregistrer; faire un aperçu
CORRIGÉ Candidat Pierre Yves Anne Sophie Jules
416491847
Nb de bulletins 11 10 13 9 5
P. COULAUD- 18/12/2013
24 / 40
Exercice 17 FOX** NB.SI SOMME.SI Dans la société FOX, le tableau cidessous retrace les frais de déplacement pour le mois de janvier.
Feuille de frais du mois de janvier Collaborateur Jean Thierry Philippe Thierry Frédéric René Didier Jean Didier Thomas Frédéric Thierry René Jean Thomas Philippe Frédéric René Thierry Didier Jean
Date 04/01 04/01 05/01 06/01 06/01 07/01 08/01 11/01 12/01 12/01 13/01 14/01 15/01 18/01 18/01 19/01 20/01 21/01 21/01 21/01 22/01
Frais 15 17 24 8 52 34 42 53 18 25 22 60 95 125 49 56 7 34 26 58 102
Travail à faire 1. Créer le classeur FOX_Nom et la feuille FRAIS_PRO 2. Saisir les données du tableau 3. Sous ce tableau, réaliser un nouveau tableau qui calculera : a) le nombre de fiche de remboursement de frais par salarié (Fonction > NB.SI) b) le montant des remboursements par salariés (Fonction > SOMME.SI) 4. Enregistrer; faire un aperçu
CORRIGÉ Récapitulatif du mois Collaborateur Nb de rapport Didier 3 Frédéric 3 Jean 4 Philippe 2 René 3 Thierry 4 Thomas 2 TOTAL 21
416491847
Frais 118 81 295 80 163 111 74 922
P. COULAUD- 18/12/2013
25 / 40
Exercice 18 LIBRAIRIE NB.SI SOMME.SI Date
Produit
Qtés Prix unitaire commandées
01/09/13
Crayon
75
1,00 €
02/09/13
Gomme
15
1,50 €
03/09/13
Papier
200
8,00 €
04/09/13
Crayon
50
1,00 €
05/09/13
Feutre
30
4,00 €
06/09/13
Crayon
100
1,00 €
07/09/13
Gomme
30
1,50 €
08/09/13
Feutre
40
4,00 €
09/09/13
Papier
50
8,00 €
10/09/13
Crayon
20
1,00 €
Travail à faire 1. Créer le classeur LIBRAIRIE_Nom et la feuille COMMANDES 2. Saisir les données du tableau 3. Créer une colonne total et réaliser les formules des totaux par ligne 4. Sous ce tableau, vous concevrez un nouveau tableau où vous souhaitez connaître le nombre de fois que des crayons ont été commandés, ainsi que pour les autres produits : NB.SI 5. Compléter votre tableau aifn de calculer le nombre de crayons commandés, ainsi que des autres produits : SOMME.SI 6. Enfin vous calculerez le montant commandé par produit : SOMME.SI 7. Enregistrer ; faire un aperçu
Crayon
Nombre de commandes 4
Qtés commandées 245
Feutre
2
70
280,00 €
Gomme
2
45
67,50 €
Papier
2
250
2 000,00 €
10
610
2 592,50 €
Total
Montant 245,00 €
=NB.SI($B$36:$B$45;B49) =SOMME.SI($B$36:$B$45;B50;$C$36:$C$45) =SOMME.SI($B$36:$B$45;B51;$E$36:$E$45)
416491847
P. COULAUD- 18/12/2013
26 / 40
Exercice 19 NOTES DES ÉLÈVES*** NB.SI SOMME.SI SOMMEPROD Voici le tableau brut des notes des élèves : Nom ALIX BUSNOT DELAUTRE DUVAL DUVAL FLEURY GAUTIER LAGADEC LAMY LE LE GLAUNEC LEGUILLON LEPIETRE LIOEDDINE MENNIER METTE MICHEL MICHEL MONTAGNE ROBERGE ROUXEL VOISIN
Prénom Yoann Corinne Chloé Sylvain Sandra Brice Sébastien Anne-Claire Sophie Thi Adele Julie Blandine Mohamed Maud Christelle Elodie Valentin Anthony Magali Marc Aurélie
CTRL 1 7 10 4 6 7 11 8 4 4 8 7 6 4 7 7 11 7 6 10 11 4 4
Devoir 1 18 10 18 14 12 8 Abs 18 8 14 18 17 18 8 17 15 18 10 14 14 12 13
CTRL 2 6 8 9 11 10 9 12 7 12 14 14 10 Abs 12 6 10 11 9 10 10 6 9
CTRL3 11 17 15 Abs 17 9 16 10 13 13 8 15 12 18 15 16 9 12 15 11 14 10
Moyenne
Moyenne Note la Plus Basse Note la plus haute NB d'élèves ayant 10 ou plus NB d'élèves ayant entre 8 et 10 NB d'élève ayant 8 ou moins de 8
Travail à faire 1
Respecter la PROGESSION des consignes 1. 2. 3. 4. 5.
Créer (OU ouvrir) le classeur NOTES_Nom et créer la feuille NOTES DES ELEVES Saisir les données du tableau Réaliser les calculs prévus dans le tableau Mettre en forme et en page le tableau Enregistrer; faire un aperçu
Travail à faire 2 1. Dupliquer la feuille NOTES DES ELEVES en NOTES COEFFICIENTS 2. Ajouter une ligne Coefficient : coefficients 2 aux contrôles et coefficient 1 au devoir 3. Modifier vos calculs en tenant compte des coefficients ; Quelle difficultés rencontrezvous ? La réponse est dans la suite Travail à faire 3 4. Reprenez vos formules de moyenne en combinant cette fois les fonctions (lire l'aide associée à ces formules afin de comprendre leur utilisation) : SOMMEPROD et SOMME.SI 5. Enregistrer =SOMMEPROD(C76:F76;$C$75:$F$75) / SOMME.SI(C76:F76;">0";$C$75:$F$75)
416491847
P. COULAUD- 18/12/2013
27 / 40
CORRIGÉ NOTES Nom ALIX BUSNOT DELAUTRE DUVAL DUVAL FLEURY GAUTIER LAGADEC LAMY LE LE GLAUNEC LEGUILLON LEPIETRE LIOEDDINE MENNIER METTE MICHEL MICHEL MONTAGNE ROBERGE ROUXEL VOISIN
Prénom Yoann Corinne Chloé Sylvain Sandra Brice Sébastien Anne-Claire Sophie Thi Adele Julie Blandine Mohamed Maud Christelle Elodie Valentin Anthony Magali Marc Aurélie
Moyenne Note la Plus Basse Note la plus haute NB d'élèves ayant 10 ou plus NB d'élèves ayant entre 8 et 10 NB d'élève ayant 8 ou moins de 8
CTRL 1 7 10 4 6 7 11 8 4 4 8 7 6 4 7 7 11 7 6 10 11 4 4
Devoir 1 18 10 18 14 12 8 Abs 18 8 14 18 17 18 8 17 15 18 10 14 14 12 13
CTRL 2 6 8 9 11 10 9 12 7 12 14 14 10 Abs 12 6 10 11 9 10 10 6 9
CTRL3 11 17 15 Abs 17 9 16 10 13 13 8 15 12 18 15 16 9 12 15 11 14 10
Moyenne 10,50 11,25 11,50 10,33 11,50 9,25 12,00 9,75 9,25 12,25 11,75 12,00 11,33 11,25 11,25 13,00 11,25 9,25 12,25 11,50 9,00 9,00
6,95 4,00 11,00 5 0 17
14,00 8,00 18,00 18 0 3
9,76 6,00 14,00 12 4 5
13,14 8,00 18,00 18 2 1
10,93 9,00 13,00 16 6 0
CTRL 1 2 7 10 4 6 7 11 8 4 4 8 7 6 4 7 7 11 7 6 10 11 4 4
Devoir 1 1 18 10 18 14 12 8 Abs 18 8 14 18 17 18 8 17 15 18 10 14 14 12 13
CTRL 2 2 6 8 9 11 10 9 12 7 12 14 14 10 Abs 12 6 10 11 9 10 10 6 9
CTRL3 2 11 17 15 Abs 17 9 16 10 13 13 8 15 12 18 15 16 9 12 15 11 14 10
Moyenne
6,95 4,00 11,00 5 0 17
14,00 8,00 18,00 18 0 3
9,76 6,00 14,00 12 4 5
13,14 8,00 18,00 18 2 1
10,48 8,43 12,71 14 8 0
NOTES AVEC COEFFICIENTS Nom
Prénom Coefficients ALIX Yoann BUSNOT Corinne DELAUTRE Chloé DUVAL Sylvain DUVAL Sandra FLEURY Brice GAUTIER Sébastien LAGADEC Anne-Claire LAMY Sophie LE Thi LE GLAUNEC Adele LEGUILLON Julie LEPIETRE Blandine LIOEDDINE Mohamed MENNIER Maud METTE Christelle MICHEL Elodie MICHEL Valentin MONTAGNE Anthony ROBERGE Magali ROUXEL Marc VOISIN Aurélie Moyenne Note la Plus Basse Note la plus haute NB d'élèves ayant 10 ou plus NB d'élèves ayant entre 8 et 10 NB d'élève ayant 8 ou moins de 8
416491847
P. COULAUD- 18/12/2013
9,43 11,43 10,57 9,60 11,43 9,43 12,00 8,57 9,43 12,00 10,86 11,29 10,00 11,71 10,43 12,71 10,29 9,14 12,00 11,14 8,57 8,43
28 / 40
Exercice 20 BERLIOZ 3*** (4 → RECHERCHE) FREQUENCE NB.SI – SOMME.SI – SOMMEPROD Vous travaillez pour le lycée BERLIOZ. Vous êtes chargés de construire un tableau récapitulatif des notes obtenues par les élèves d'une classe lors de leur dernier contrôle de connaissances.
Travail à faire 1 1. Ouvrir ou créer le classeur BERLIOZ_Nom 2. Dupliquer la feuille CLASSE_1 afin de créer la feuille CLASSE_1_COEF 3. Insérer les coefficients en haut du tableau :
Résultats de l'examen : classe 1 (avec coefficient des matières) Math
Fran.
Langue
Physiq.
Histoire
4
3
1
2
1
Vialat
6
9
5
10
7
Fallourd
7
5
8
9
10
Dubois
10
11
8
7
11
Dupuis
8
10
12
13
9
Dupont
12
10
11
12
Brosseau
15
19
14
6
16
Théau
11
12
9
9
11
Serres
17
18
14
Colbert
11
13
14
15
16
Cadu
17
16
15
12
17
Verdon
8
7
5
6
Coefficient
4. Calculer la moyenne de chaque élève en tenant compte des coefficients : fonctions SOMMEPRODUIT et SOMME.SI. Exemple de formule à réaliser : =SOMMEPROD($C$25:$G$25;C26:G26) / SOMME.SI(C26:G26;">0";$C$25:$G$25) Travail à faire 2 1. Calculer la moyenne, les notes maximale et minimale par élève et par matière 2. En bas du tableau, ajouter 3 lignes sur 2 colonnes (descriptifs et bornes supérieures) : a) moins de 8 8 b) de 8 à 12 12 c) plus de 12 20 3. Utiliser la fonction FREQUENCE pour les notes de mathématiques a) Insertion > Fonction > FREQUENCE : les données = notes des élèves en Math les classes = borne de chaque intervalle ATTENTION pour Excel : il faut valider par CTRL + MAJ + ENTREE 4. Refaire la manipulation pour chacune des autres matières
416491847
P. COULAUD- 18/12/2013
29 / 40
Résultats de l'examen : classe 1
Nombre élèves :
11
(avec coefficient des matières) Math
Fran.
Langue
Physiq.
Histoire
4
3
1
2
1
Vialat
6
9
5
10
Fallourd
7
5
8
Dubois
10
11
Dupuis
8
10
Dupont
12
Brosseau
15
Théau
11
Serres
17
Colbert
11
Cadu Verdon
Moy.
Nb de notes
Min
Max
7
7,55
5,00
5,00
10,00
9
10
7,18
5,00
5,00
10,00
8
7
11
9,64
5,00
7,00
11,00
12
13
9
9,91
5,00
8,00
13,00
10
11
12
11,50
4,00
10,00
12,00
19
14
6
16
14,45
5,00
6,00
19,00
12
9
9
11
10,73
5,00
9,00
12,00
18
14
16,29
3,00
14,00
18,00
13
14
15
16
13,00
5,00
11,00
16,00
17
16
15
12
17
15,64
5,00
12,00
17,00
8
7
5
6
6,90
4,00
5,00
8,00
Moyenne
11,09
11,33
11,30
10,09
11,50
11,16
Note maximale
17,00
19,00
18,00
15,00
17,00
16,29
Note minimale
6,00
5,00
5,00
5,00
6,00
6,90
Coefficient
Moins de 8
8
4
2
3
3
2
De 8 à 12
12
4
4
3
5
5
Plus de 12
20
3
3
4
3
3
416491847
P. COULAUD- 18/12/2013
30 / 40
TABLEUR 416491847
SI ET OU
Présentation Les fonctions ET et OU peuvent être combinées avec la fonction SI lorsque la condition concerne au moins deux arguments.
Exemple Admission si l’âge < 18 ans et la moyenne >=12: La formule de calcul du prix sera la suivante : =SI(ET(A2<18;B2>=12);«admis»;«refusé») =SI(ET(A2<18;B2>=12);«admis»;«refusé») A B C 1 Age Moyenne 2 17 11 refusé 3
APPRENDRE SUR INTERNET http://www.excelexercice.com/ **** Fonction > Condition
416491847
P. COULAUD- 18/12/2013
31 / 40
Exercice 21 ROUIX 2 (1 → SI imbriqués) SI ET Faites les calculs qui s’imposent en tenant compte des indications suivantes : • le taux de remise est de 3% si le hors taxes dépasse 10 000 € • pour les grossistes ce taux sera de 5% et à condition que le hors taxes dépasse 10 000 €
CALCUL DES REMISES CLIENTS GARD
Grossiste
Brut Hors taxes 14 650,00
HECTOR
Détaillant
8 703,00
0%
0,00
8 703,00
IBO
Particulier
14 540,00
3%
436,20
14 103,80
JURA
Grossiste
8 775,00
0%
0,00
8 775,00
KILL
Particulier
7 602,00
0%
0,00
7 602,00
LAMA
Grossiste
11 215,00
5%
560,75
10 654,25
MANN
Détaillant
8 703,00
0%
0,00
8 703,00
NUZ
Détaillant
17 525,00
3%
525,75
16 999,25
Nom
Type client
5%
Montant Remise 732,50
Net Hors taxes 13 917,50
Remise
Travail à faire 1. Ouvrir ou créer le classeur ROUIX_Nom et la feuille REMISE 2. Saisir les données du tableau 3. Effectuer les calculs nécessaires en utilisant les fonctions SI et ET : =SI( ET(client="Grossiste" ; chiffre d'affaires>10000) ; remise 2 ; SI(chiffre d'affaires>10000; remise 1 ; 0 )) 4. Enregistrer; faire un aperçu
CORRIGE
KILL
Particulier
7 602,00
0%
0,00
7 602,00 =SI( ET(B8="Grossiste" ; C8>10000) ; 0,05 ; SI(C8>10000; 0,03; 0 ))
LAMA
Grossiste
11 215,00
5%
560,75
10 654,25 =SI( ET(B9="Grossiste" ; C9>10000) ; 0,05 ; SI(C9>10000; 0,03; 0 ))
MANN
Détaillant
8 703,00
0%
0,00
8 703,00 =SI( ET(B10="Grossiste" ; C10>10000) ; 0,05 ; SI(C10>10000; 0,03; 0 ))
NUZ
Détaillant
17 525,00
3%
525,75
16 999,25 =SI( ET(B11="Grossiste" ; C11>10000) ; 0,05 ; SI(C11>10000; 0,03; 0 ))
416491847
P. COULAUD- 18/12/2013
32 / 40
Exercice 22 ARMAND** SI – ET – OU On désire attribuer les 2 ristournes suivantes : • une première ristourne de fin d'année de 2% aux clients qui sont grossistes OU avec lesquels on a fait plus de 100 000 € de CA • une seconde ristourne de 1% aux clients qui sont grossistes ET qui nous ont acheté plus de 200 000 € de marchandises Nom du client EMELINE ACQUIN HENDOL JUNEZ GAUTIER
Type de client Grossiste Détaillant Grossiste Détaillant Grossiste
Achats 125 000 138 000 185 000 59 500 213 000
Ristourne 1 2 500 € 2 760 € 3 700 € 0€ 4 260 €
Ristourne 2 0€ 0€ 0€ 0€ 2 130 €
Travail à faire 1 1. Créer le classeur ARMAND_Nom et la feuille RISTOURNE 2. Saisir les données du tableau 3. Ajouter une colonne ristourne 1, dans laquelle on calculera la 1ère ristourne, et créer la formule suivante (respecter les majuscules) : =SI(OU(B4="Grossiste";C4>100000);C4*2%;0) 4. Ajouter une seconde colonne ristourne 2 et créer la formule suivante : =SI(ET(B2="Grossiste";C2>200000);C2*1%;0) 5. Enregistrer; faire un aperçu
L'entreprise souhaite verser une prime de fin d'année à ses représentants s'ils remplissent l'une OU l'autre des conditions suivantes : - avoir plus de 5 ans d'ancienneté - avoir réalisé plus d'1 million d'euros de chiffre d'affaires. Nom du Années Représentant d'ancienneté ARMAND 3 FLORA 6 NINIAN 7 SEBATI 1
Chiffre d'affaires 1 213 000 997 000 1 016 000 757 000
Prime 1 000 € 1 000 € 1 000 € 0€
Travail à faire 2 1. Créer, dans le classeur ARMAND_Nom, une nouvelle feuille nommée PRIMES et sauvegarder 2. Saisir les données du tableau 3. Ajouter une colonne PRIME et concevoir la formule de calcul de la prime 4. Enregistrer; faire un aperçu
416491847
P. COULAUD- 18/12/2013
33 / 40
Exercice 23 CLIENTS SI – ET – OU
Source : http://www.excelexercice.com/fonction/condition/fonctionetou/
Le tableau suivant fournit différentes informations sur vos clients. Vous souhaitez sélectionner dans ce fichier vos clients en fonction de certains critères. Si les critères correspondent à vos attentes, vous inscrirez 1 dans la colonne résultat, 0 si le test n’est pas concluant. De cette façon, il ne vous reste plus qu’à effectuer l’addition de la colonne résultat pour savoir combien de clients correspondent à vos critères. Résoudre toutes les questions cidessous : 1. Combien de clients sont mariés et ont un seul enfant ? 2. Combien de clients sont célibataires ou divorcés ? 3. Combien de clients sont mariés avec un revenu supérieur à 75 000 ? 4. Combien de clients sont des hommes, célibataires et sans enfants ? 5. Combien de clients sont des femmes avec 1 ou 2 enfants. 6. Combien de clients sont mariés ou en concubinage, avec un revenu supérieur ou équivalent à 50 000 et qui ont au moins 2 enfants ? Nom JANJUAH SARA CHAMPNEY JONAS SAMEN SATAKE LEON BYERLY WARNER GAUDEFROY CHAP SIMPSON FORDYCE TORTELLI ADCOCK CONTAT ZIZZARI FREYER JONES
Prénom Bob Sumit Robert Lee Michael Kaori Fernando Kimberly Norris Marie Mathew Aubrey David Olavo David Thibault Stephen Jean-Luc David
Genre Homme Homme Homme Homme Homme Homme Homme Femme Homme Femme Femme Homme Homme Femme Femme Homme Femme Homme Homme
Revenu 20 000 80 000 67 000 35 000 77 000 70 000 23 000 97 000 26 000 89 000 77 000 34 000 61 000 27 000 72 000 85 000 84 000 50 000 31 000
Situation Concubinage Marié Marié Concubinage Célibataire Marié Célibataire Concubinage Divorcé Concubinage Célibataire Divorcé Concubinage Célibataire Divorcé Concubinage Célibataire Célibataire Célibataire
Enfant 0 1 0 0 0 3 3 1 3 0 2 0 2 0 3 1 3 2 0
Résultat
Les solution sont : =SI(ET(E2=”Marié”;F2=1);1;0) =SI(OU(E2=”Célibataire”;E2=”Divorcé”);1;0) =SI(ET(E2=”Marié”;D2>75000);1;0) =SI(ET(C2=”Homme”;E2=”Célibataire”;F2=0);1;0) La solution pour la question 5 est : =SI(ET(C2=”Femme”;OU(F2=1;F2=2));1;0) La solution pour la question 6 est : =SI(ET(D2>=50000;F2>=2;OU(E2=”Marié”;E2=”Concubinage”));1;0)
416491847
P. COULAUD- 18/12/2013
34 / 40
Exercice 24 CONCOURS 1*** (2 → valeur cible) SI – ET – OU
Notes du concours Matières scientifiques
Matières littéraires
Math
Physique
Chimie
Français
Hist. Géo.
Anglais
Alain
18
15
16
14
18
15
Béa
13,5
12
10,8
12
11
10
Cloé
11
11,8
12
11
10
11
Daniel
12
11
13
15
14
13
Eric
14
12
10,5
13
9
13
Fernand
5
8
7
9
7
7
Travail à faire 1 ANNÉE N : le concours d'entrée d'une école d'ingénieur se fait selon le critère suivant : la moyenne générale des notes doit être supérieure ou égale à 12 1. 2. 3. 4.
Créer le classeur CONCOURS_Nom et la feuille ANNEE_N Saisir le tableau Effectuer le calcul de la moyenne générale pour chaque étudiant Réaliser un tableau RESULTATS en dessous du précédent tableau : a) la liste des étudiants est liée au 1er tableau (tout changement de nom dans le tableau NOTES se répercute automatiquement dans le tableau RESULTATS) b) dans une colonne ANNEE N, déterminer automatiquement les étudiants admis(e) ou refusé(e) 5. Mettre en forme les tableaux
Travail à faire 2 ANNÉE N + 1 : le concours d'entrée a changé et les critères de sélection sont maintenant les suivants : - la moyenne des notes scientifiques doit être supérieure ou égale à 12 ET - la moyenne des notes littéraires doit être supérieure ou égale à 10 1. Calculer les moyennes Scientifique et Littéraire 2. Créer un double de la feuille que vous nommerez ANNEE_N+1 3. Lier les noms de cette nouvelle feuille avec les noms de la feuille ANNEE_N (tous les noms se mettent à jour automatiquement en fonction des changements réaliser dans la feuille ANNEE_N) 4. Dans le tableau RESULTATS, ajouter une colonne ANNEE_N+1 et déterminer d'après les critères de l'ANNEE N+1, les étudiants admis(e) ou refusé(e) 5. Réaliser une représentation graphique en toile affichant la répartition des notes par matière
416491847
P. COULAUD- 18/12/2013
35 / 40
Travail à faire 3 ANNÉE N + 2 : le concours d'entrée a changé et les nouveaux critères de sélection sont les suivants (ces critères sont à remplir simultanément) : - la moyenne des notes scientifiques doit être supérieure ou égale à 12 - aucune note scientifique ne doit être inférieure à 11 - la moyenne des notes littéraires doit être supérieure ou égale à 11 Un rattrapage est prévu pour les étudiants refusés remplissant les conditions suivantes : avoir au moins une des deux moyennes des blocs scientifique ou littéraire supérieure à 12 1. Créer un double de la feuille ANNEE_N+1 que vous nommerez ANNEE_N+2 2. Dans le tableau RESULTATS, ajouter 2 colonnes : a) ANNEE_N+2 : les étudiants admis(e) ou refusé(e) d'après les critères b) les admissibles parmi ceux qui ont été refusés 3. Modifier les formules afin que les critères de notes et de moyennes ne soient plus inclus dans les formules, mais qu'ils soient contenus dans des cellules afin de pouvoir changer ces critères sans retoucher les formules
416491847
P. COULAUD- 18/12/2013
36 / 40
CORRIGÉ
Notes du concours Matières scientifiques
Matières littéraires
Moyenne
Math
Physique
Chimie
Français
Hist. Géo.
Anglais
Générale Scientifique Littéraire
Alain
18
15
16
14
18
15
16,00
16,33
15,67
Béa
13,5
12
10,8
12
11
10
11,55
12,10
11,00
Cloé
11
11,8
12
11
10
11
11,13
11,60
10,67
Daniel
12
11
13
15
14
13
13,00
12,00
14,00
Eric
14
12
10,5
13
9
13
11,92
12,17
11,67
Fernand
5
8
7
9
7
7
7,17
6,67
7,67
Math 20 Physique
Anglais
10
0
Chimie
Hist. Géo.
Français Alain Béa Daniel Eric
Cloé Fernand
Résultats Année N Critère MS
12
Critère ML
Année N+1 Année N+2 Admissibles 12
12
12
10
11
12
Critère NS
416491847
11
Alain
Admis(e)
Admis(e)
Admis(e)
Béa
Refusé(e)
Admis(e)
Refusé(e) Admissible
Cloé
Refusé(e)
Refusé(e)
Refusé(e)
Daniel
Admis(e)
Admis(e)
Admis(e)
Eric
Refusé(e)
Admis(e)
Refusé(e) Admissible
Fernand
Refusé(e)
Refusé(e)
Refusé(e)
P. COULAUD- 18/12/2013
37 / 40
Exercice 25 LYCEE** SI – ET – OU RANG Vous travaillez dans un lycée et êtes amené à concevoir un relevé de note. Travail à faire 1 1. Créer une nouvelle feuille nommée MATHS_T2 et enregistrer le classeur LYCEE_Nom 2. Saisir le tableau
Notes de mathématiques (trim. 2) Nom élève
Nombre élèves :
Devoir 1
Devoir 2
Devoir 3
Alain
8,25
10,50
11,25
Bernard
9,25
8,50
6,75
Claude
14,25
13,25
16,50
Daniel
14,50
12,00
11,50
Eric
17,75
16,25
18,50
Fernand
12,00
13,75
14,75
Guy
4,75
5,75
8,00
Hector
8,00
10,50
11,25
Ivan
6,75
8,00
10,75
Jules
11,00
10,00
11,50
3. Calculer : a) la moyenne de chaque élève b) la moyenne par devoir, ainsi que la note la plus haute et la note la plus basse c) le nombre d’élèves de la classe 4. Ajouter une colonne pour le préavis du professeur de mathématiques concernant le passage ou le redoublement dépend de la moyenne du 2ème trimestre qui doit être supérieure ou égale à 10. Effectuer le nécessaire afin de faire apparaître ce préavis. 5. Le professeur de mathématiques décide d'attribuer une mention à chaque élève en fonction de la moyenne : a) mention Insuffisant pour une moyenne inférieure à 8 b) mention Moyen pour une moyenne comprise entre 8 inclus et 12 c) mention Bien pour une moyenne supérieure à 12 inclus
416491847
P. COULAUD- 18/12/2013
38 / 40
Travail à faire 2 6. Dupliquer la feuille MATHS_T2 et nommer la nouvelle feuille TOUTES_MATIERES 7. Modifier le tableau : a) ajouter la colonne absence b) conserver le calcul des moyennes, des notes les plus hautes et les plus basses
Moyenne des notes toutes matières E Nom élève Alain
Trim. 1
Trim. 2
Nombre élèves :
Trim. 3
Absences
9,25
10,24
11,05
1
Bernard
10,00
9,36
8,96
14
Claude
11,50
12,80
12,80
12
Daniel
12,30
12,30
11,28
1
Eric
12,45
13,21
14,01
Fernand
11,20
11,50
11,60
Guy
6,80
6,74
7,00
Hector
9,20
10,28
10,00
4
Ivan
7,86
6,80
8,60
15
Jules
9,86
10,10
9,50
2
Karl
13,56
13,59
14,12
1
c) lier les noms avec la feuille MATHS_T2 d) saisir les données du tableau 8. Déterminer l'avis de passage selon les critères suivants : a) un étudiant passe si : - sa moyenne est supérieure ou égale à 10 OU - sa moyenne est comprise entre 8 (inclus) et 10, et si il a été absent moins de 10 fois (inclus) b) un étudiant redouble si : - sa moyenne est inférieure à 8 OU - sa moyenne est comprise entre 8 (inclus) et 10, et si il a été absent plus de 10 fois 9. Ajouter une colonne indiquant le classement des élèves (fonction RANG) Application 10. Dans la feuille MATH_T2, une appréciation particulière est donnée aux élèves dont la moyenne est supérieure ou égale à 15 : « Félicitations » élaborer la formule nécessaire 11. Dans la feuille TOUTESMATIERES, on veut indiquer par une remarque « En progression » si l'élève à progresser régulièrement ; pour cela il faut que la moyenne du trimestre 1 soit inférieure à la moyenne du trimestre 2 ellemême inférieure à celle du trimestre 3. 12. Effectuer une représentation graphique sous forme de secteur des absences
416491847
P. COULAUD- 18/12/2013
39 / 40
CORRIGÉ Notes de mathématiques (trim. 2)
B
Nombre élèves :
Nom élève
Devoir 1
Devoir 2
8,25
10,50
Alain
10
D
Devoir 3 Moyenne 11,25
Préavis
Mention
10,00
Passage
Moyen Moyen
Bernard
9,25
8,50
6,75
8,17
Redoublement
Claude
14,25
13,25
16,50
14,67
Passage
Bien
Daniel
14,50
12,00
11,50
12,67
Passage
Bien
Eric
17,75
16,25
18,50
17,50
Passage
Bien
Fernand
12,00
13,75
14,75
13,50
Passage
Bien
Guy
4,75
5,75
8,00
6,17
Redoublement
Insuffisant
Hector
8,00
10,50
11,25
9,92
Redoublement
Moyen
Ivan
6,75
8,00
10,75
8,50
Redoublement
Moyen
Jules
11,00
10,00
11,50
10,83
Passage
Moyen
Karl
15,50
16,25
15,00
15,58
Passage
Bien
Moyenne
10,65
10,85
12,08
Note maximale
17,75
16,25
18,50
Note minimale
4,75
5,75
6,75
Moyenne des notes toutes matières
Nombre élèves :
E Trim. 2
Félicitations
Trim. 3
11
F
G
Absences
Moyenne
Avis de passage
Progression
Rang
9,25
10,24
11,05
1
10,18
Passage
En progression
6
Bernard
10,00
9,36
8,96
14
9,44
Redoublement
9
Claude
11,50
12,80
12,80
12
12,37
Passage
3
Daniel
12,30
12,30
11,28
1
11,96
Passage
Eric
12,45
13,21
14,01
13,22
Passage
En progression
2
Fernand
11,20
11,50
11,60
11,43
Passage
En progression
5
Alain
Trim. 1
Félicitations
C
A
Nom élève
Appréciation
4
Guy
6,80
6,74
7,00
1
6,85
Redoublement
Hector
9,20
10,28
10,00
4
9,83
Passage
7
Ivan
7,86
6,80
8,60
15
7,75
Redoublement
10
Jules
9,86
10,10
9,50
2
9,82
Passage
8
Karl
13,56
13,59
14,12
13,76
Passage
Nombre de passages
8
Moyenne
10,36
10,63
10,81
Note maximale
13,56
13,59
14,12
Note minimale
6,80
6,74
7,00
% de passages
11
En progression
1
72,73%
H
Répartition des abscences 2.00% 4.00% 28.00% 30.00%
24.00%
416491847
Alain Claude Eric Guy Ivan Karl
Bernard Daniel Fernand Hector Jules
8.00% 2.00% 2.00%
P. COULAUD- 18/12/2013
40 / 40
Related Documents
6.4 Si, Et, Ou
August 2019 17
Ou
June 2020 24
Ou
November 2019 33
64
December 2019 30
64
April 2020 23
64
November 2019 41More Documents from ""
Brochure-h2o Nov2018 Draft2 Web (1)
August 2019 11
97523591-conception-et-dimensionnement-du-lagunage-aere.pdf
August 2019 12
Acposte3lc2019
October 2019 7