Cours Excel fonction SI, methodes et formules
FONCTION SI, CONDITIONS ET CRITÈRES - EXCEL 2007
Nous proposons maintenant d‛aborder l‛utilisation de la fonction de raisonnement dans Excel, la fonction Si, au travers d‛un cas pratique très simple mettant en ouvre seulement des valeurs et critères statiques. Bien sûr, l‛objectif d‛Excel est de créer des feuilles complètement dynamiques où les données se mettent automatiquement à jour en fonction des hypothèses placées dans un tableau de bord. Dans un autre support nous aborderons la notion de fonction Si combinée avec l‛utilisation des références absolues par le biais d‛un panneau de contrôle.
Vous trouverez le classeur d‛application dans le dossier Sources.
Ce tableau est la synthèse d‛un conseil de classe. Dans les premières colonnes apparaissent les prénoms des élèves; en colonne D leur moyenne générale respective et enfin en colonne E, l‛avis du conseil à calculer.
FONCTIONSI- MISEENOEUVRE
Objectif : Partons sur un cas simple. Dans la colonne Avis du conseil doit apparaître le texte L’élève passe si sa moyenne générale est supérieure ou égale à 10 et L’élève redouble dans le cas contraire.
La fonction Si est plus complexe que les formules de calcul que nous avons abordées jusqu‛alors. Elle nécessite trois arguments. Le premier argument est le critère, soit une cellule que l‛on compare à une valeur. Le deuxième argument correspond à l‛action que doit effectuer la formule lorsque le critère est vérifié. Le troisième élément correspond à l‛action que doit effectuer la formule lorsque le critère n‛est pas vérifié. Chaque argument étant séparé par un point virgule. Littéralement : =SI(Critère;Alors;Sinon)
Si la moyenne est >= 10 (Critère), écrire le texte L‛élève passe (Alors) sinon écrire le texte L‛élève redouble (Sinon).
- Sélectionnez toutes les cellules du calcul (E7 à E15),
- Tapez = pour initialiser le calcul,
- Tapez le nom de la fonction SI,
- Ouvrez la parenthèse,
Comme vous le constatez, une info-bulle apparaît confirmant que la fonction recquiert trois arguments. Ces info-bulles sont apparues depuis la version 2003. Lorsque vous ouvrez la parenthèse d‛une fonction et qu‛aucune info-bulle ne s‛affiche, vous savez d‛ores et déjà que le calcul comporte une erreur. Sans doute un souci dans le nom de la fonction
- Cliquez sur la première moyenne générale (D7),
Fonction Si - Conditions et Critères
- Tapez le symbole supérieur (>) suivi de égal (=), ● Puis tapez 10,
Nous venons de spécifier le critère. Est-ce que la moyenne est supérieure ou égale à 10 ?
- Tapez un point virgule (;) pour passer à la suite,
Vous notez que le deuxième argument de l‛info-bulle se met en gras. Ainsi vous savez en temps réel ce que vous devez indiquer à la fonction SI. Nous devons maintenant écrire le texte L‛élève passe. Tout texte doit être encadré de guillemets dans une fonction Excel.
- Ouvrez les guillemets (Touche 3 du clavier),
- Tapez le texte L’élève passe,
- Fermez les guillemets (Touche 3 du clavier),
- Tapez un point virgule (;),
- Ouvrez de nouveau les guillemets,
- Tapez le texte L’élève redouble,
- Fermez les guillemets,
- Enfin n’oubliez pas de fermer la parenthèse,● Validez le calcul par CTRL + Entrée.
Le calcul est reporté sur toute la colonne et chaque avis du conseil correspond bien à la moyenne générale en regard. Notez que si vous modifiez l‛une des moyennes, l‛avis du conseil se met instantanément à jour puisqu‛il lui est lié par un calcul.
Le résultat est intéressant mais pas vraiment satisfaisant. La fonction Si dans son expression la plus simple ne permet de poser qu‛un seul critère et donc de n‛envisager que deux possibilités ! Imaginons que le conseil souhaite faire passer l‛élève dont la moyenne est supérieure ou égale à 10, redoubler l‛élève dont la moyenne est strictement inférieure à 9,5 et débattre pour l‛élève dont la note est proche de la moyenne, entre 9,5 et 10. Ce cas de figure envisage trois possibilités. Nous devons pour cela poser deux critères. La solution proposée par Excel est l’imbrication de fonction SI.
IMBRICATIONDESI - PLUSDECRITÈRES
- Supprimez les calculs précédemment réalisés,
- Sélectionnez les cellules E7 à E15,
- Tapez le symbole égal (=) pour initialiser le calcul,
- Tapez SI et ouvrez la parenthèse,
- Cliquez sur la première moyenne (D7),
- Tapez >=,
- Puis tapez 10 suivi d’un point virgule (;),
- Ouvrez les guillemets (Touche 3 du clavier),
Fonction Si - Conditions et Critères
Vous constatez que les résultats sont reproduits sur l‛intégralité de la colonne et que les trois possibilités sont bien envisagées.
Vous pouvez continuer l‛imbrication de fonctions SI de la même façon. Lorsque N possibilités se proposent, vous utiliserez N-1 fonctions SI. Ici nous avions 3 possibilités et nous avons employé 2 fonctions SI. A partir d‛un certain nombre néanmoins la syntaxe se complique et il sera temps de se demander si l‛utilisation de la fonction SI est judicieuse. Nous verrons dans de prochains supports, que d‛autres fonctions Excel permettent de répondre pertinnement au problème lorsque le nombre de possibilités est trop grand.
Il convient maintenant de faire ressortir en couleur les élèves qui redoublent pour avoir une idée de la proportion de réussite par le biais d‛une lecture rapide de la feuille. Pour cela, nous mettons en oeuvre le format conditionnel que nous avions énoncé dans un support que vous trouverez à cette adresse :
FORMATCONDITIONNELDESRÉSULTATS
Les élèves qui redoublent doivent apparaître en couleur.
- Sélectionnez toutes les cellules du calcul (E7 à E15),
- Déroulez le bouton Mise en forme conditionnelle du ruban Accueil,
- Pointez sur Règles de mise en surbrillance des cellules, ● Cliquez sur Egal à,
- Dans la zone de saisie, tapez l’élève redouble,
Pour la reconnaissance de la règle, attention de taper le texte exactement comme vous l‛avez saisi dans la formule.
- Vous notez que les cellules concernées sont instantanément mises en surbrillance,
- Déroulez la liste déroulante sur la droite,
- Cliquez sur Texte rouge, ● Validez en cliquant sur Ok.
La lecture est désormais plus efficace. En un clin d‛oeil nous remarquons qu‛il y a deux redoublements.
Bien sûr ce format est dynamique. Il suffirait de modifier l‛une des moyennes pour faire redoubler un nouvel élève et constater l‛apparition d‛une autre cellule en rouge.
Fonction Si - Conditions et Critères
Dernier petit point; vous notez la présence d‛une cellule verte en bas de la colonne E. Cette cellule doit afficher le nombre d‛étudiants admis. Nous savons faire des sommes sur des valeurs numériques mais qu‛en est il lorsqu‛il s‛agit de compter des cellules de texte ?
Excel propose une fonction de dénombrement qui s‛appelle .
DÉNOMBREMENTSELONCRITÈRE
La fonction attend deux arguments. Le premier correspond à la plage de cellules sur laquelle elle doit compter. Le second correspond au critère pour savoir quoi compter.
- Sélectionnez la cellule E18,
- Tapez = pour lancer le calcul, ● Tapez ,
Attention de ne pas réaliser le point (.) de la fonction avec le pavé numérique qui conduira à une virgule(,) chez nous les français.
- Ouvrez la parenthèse,
Notez une fois de plus l‛apparition instantanée de l‛info-bulle qui confirme que la fonction attend deux indications.
- Sélectionnez les cellules E7 à E15,
- Tapez un point virgule (;),
- Ouvrez les guillemets, le critère est un texte,
- Tapez fidèlement L’élève passe,
- Fermez les guillemets,
- Fermez la parenthèse,
- Validez le calcul par Entrée.
Le résultat retourné est 5. Résultat parlant pour celui qui conçoit le calcul mais pas forcément pour l‛utilisateur à plus forte raison si le nombre d‛étudiants est plus important. Pour cela nous allons mettre en oeuvre la concaténation que nous avions abordée dans un cas spécifique au travers d‛un support que vous trouverez à cette adresse :
L‛objectif est d‛afficher dans la cellule 5 admissions. 5 étant toujours le résultat dynamique du calcul assemblé avec un texte d‛explication.
- Sélectionnez la cellule E18,
- Enfoncez la touche F2 du calvier pour forcer la saisie,
- Enfoncez la touche Fin du clavier pour vous placer à la fin de la formule, ● Tapez & " Admissions", ● Validez par Entrée.
Nous assemblons le résultat numérique au texte Admissions précédé d‛un espace.
Université Cadi Ayyad Marrakech
Faculté des Sciences Juridiques, Economiques et sociales
Semestre 4 Toute Option
TP Informatique
Fonction Si Atelier II
- Lancer un Nouveau Classeur Excel, puis l’enregistrer sous le nom « Atelier II ».
- Nommer la première feuille « Chiffre d’affaire ».
TP : Informatique Thème : Fonction SI |
1 2008-2009 |
Enseignant : A.Charkaoui |
- Construire la le tableau suivant :
Instruction de calcul :
- Calcul du montant :
Montant = Qtè x PU
- Pour tout les colonne qui contiennent des montants :
- Sélectionner les cellules
- Format de la cellule
iii. Nombre / Monétaire / Euro. iv. Nombre de décimal 0
- Remise :
La remise est attribuée par fourchette du montant réalisé : Plus de 100 000 le fournisseur donne une remise de 10% et 5% pour le reste.
- Net Commercial :
Net Commercial = Montant – Remise
- TVA :
TVA = Net Commercial x 20%
- Moyen de transport :
Pour Mounir on donne comme moyen de transport : Voiture.
Pour Jamal : Camion Noura : Camionnette Les autre RIEN.
TP
Université Constantine 2
Département sciences de gestion
TP Excel N°3
M.S.A. SECURITE
|
|
Dieval |
Montagne |
Delettre |
|||
C.A. réalisé |
Commission |
C.A. réalisé |
Commission |
C.A. réalisé |
Commission |
Janvier |
165800 |
196200 |
167400 |
||
Février |
204780 |
185200 |
127400 |
||
Mars |
196500 |
159900 |
166400 |
||
Avril |
152666 |
132000 |
167400 |
||
Mai |
132000 |
150200 |
127400 |
||
Juin |
115000 |
165343 |
200400 |
||
Juillet |
161543 |
167400 |
162400 |
||
Août |
122356 |
127400 |
127400 |
||
Septembre |
128880 |
166400 |
166400 |
||
Octobre |
160666 |
200000 |
22400 |
||
Novembre |
121321 |
214465 |
110400 |
||
Décembre |
111220 |
243162 |
100400 |
Concevoir une feuille de calcul des représentants pour l'année
Ils touchent 10% de leurs ventes s'ils réalisent leur objectif. Dans le cas contraire ils ne perçoivent que 8%
Utiliser la fonction SI() et nommer les cellules fixes dont voici la liste :
C5 |
Obj_Diéval |
C6 |
Obj_Montagne |
C7 |
Obj_Delettre |
G6 |
Taux_A |
G7 |
Taux_NA |
1 (Fonction SI)
Consignes :
- Calculer le montant de la prime sachant que :
- si les ventes sont supérieures au quota, la prime est égale à 50 € par unité vendue pour les micro-ordinateurs vendus dépassant le quota.
- sinon la prime est nulle.
- Effectuer les calculs des totaux, quelle est la prime minimum ? quelle est la prime maximum ?
- Calculer le nombre de pièces vendues par région.
PALMARES DES VENTES DES MEILLEURES VENTES
D'ORDINATEURS
Représentants |
Département |
Région |
Ventes (en |
Quota |
Prime |
quantité) |
|||||
SVOÏTKA |
Savoie |
Rhône-Alpes |
300 |
250 |
|
GAELLE |
Côte-d'Or |
Bourgogne |
260 |
280 |
|
MAEVA |
Ain |
Rhône-Alpes |
250 |
270 |
|
SOIZIC |
Isère |
Rhône-Alpes |
320 |
300 |
|
FLORIAN |
Nièvre |
Bourgogne |
180 |
190 |
|
LOIC |
Nièvre |
Bourgogne |
240 |
250 |
|
ARNAUD |
Côtes d'Armor |
Rhône-Alpes |
400 |
350 |
|
JEAN |
Loire |
Rhône-Alpes |
280 |
280 |
|
SYLVIANE |
Yonne |
Bourgogne |
270 |
200 |
|
BLANCHET |
Ardèche |
Rhône-Alpes |
200 |
240 |
|
JUNIO |
Rhône |
Rhône-Alpes |
310 |
300 |
|
HUGOT |
Côtes d'Armor |
Rhône-Alpes |
190 |
170 |
|
Totaux Maximum Minimum |
Nbre de pièces vendues pour la région Bourgogne : |
Nbre de pièces vendues pour la région Rhône-Alpes : |
2 (Fonction de tri)
À la veille d’un conseil de classe, les différents professeurs vous remettent les informations suivantes :
Professeur de Français :Professeur d’Anglais
DUBOIS Pierre :12 BONUE Florian : 11
CORRY Jean marie :14 CORRY Jean-Marie : 12
GASTIEN Sylvie : 13 CORTENE Mylène : 13
BONUE Florian : 8 DUBOIS Pierre : 16
CORTENE Mylène 11 GASTIEN Sylvie : 9
RAMOI Paul : 10 LOBERT Corinne : 12
LOBERT Corinne : 7 RAMOI Paul : 14
SIORNE Patrick : 9 SIORNE Patrick : 6
Professeur de MathématiquesProfesseur d’Histoire/Géographie
DUBOIS Pierre : 15 SIORNE Patrick : 14
CORRY Jean marie : 6 DUBOIS Pierre : 10
GASTIEN Sylvie : 18 CORRY Jean marie : 9
BONUE Florian : 19 CORTENE Mylène : 18
CORTENE Mylène : 9 GASTIEN Sylvie : 15
RAMOI Paul : 14 BONUE Florian : 7
LOBERT Corinne : 11 RAMOI Paul : 13
SIORNE Patrick : 10 LOBERT Corinne : 13
Consignes :
- Afin de faire ressortir la moyenne par élève et par matière, présenter ces informations sous forme de 2 tableaux (sur 2 feuilles de calcul différentes) à l’aide du tableur Excel.
- Le premier tableau fera apparaître les élèves par ordre alphabétique.
- Le second tableau fera apparaître les élèves par ordre décroissant de leur moyenne.
- Utiliser les fonctions de tri du tableur
15
Microsoft Excel 2010
3 (Fonctions ET, OU et SI)
Consignes :
- Reproduisez la fiche de renseignements et la facture sur une feuille du classeur.
- Conditions de vente :
- Remise 1 : 2% de remise pour les grossistes.
- Remise 2 : 5% de remise pour les grossistes si le total 1 est supérieur à 10 000 €.
- Escompte : si le paiement s'effectue comptant :
- 2% pour les détaillants.
- 3% pour les grossistes.
- Frais de port : ils s'élèvent à 50 € et ils ne sont pas facturés dans l'un ou l'autre des deux cas suivants :
- si la vente est emportée.
- si le total T.T.C. est supérieur à 15 000 €.
- Essayez d'utiliser les fonctions OU() et ET() combinées avec la fonction SI()
- Testez le devis avec les cas suivants :
Cas 1 : Grossiste achetant 12000 € de marchandises, paiement comptant, livré.
Cas 2 : Grossiste achetant 9000 € de marchandises, paiement comptant, emporté.
Cas 3 : Détaillant achetant 25000 € de marchandises, paiement comptant, emporté.
Cas 4 : Détaillant achetant 12000 € de marchandises, paiement différé, livré.
Cas 5 : Grossiste achetant 12000 € de marchandises, paiement comptant, emporté.
Fiche de renseignements |
|
Grossiste (OUI/NON) |
|
Paiement comptant (OUI/NON) |
|
Vente emportée (OUI/NON) |
|
Facture |
|
Marchandises HT |
|
Remise 1 |
|
Sous-total 1 |
|
Remise 2 |
|
Sous-total 2 |
|
Escompte |
|
Total Hors Taxes |
|
T.V.A. |
|
Total T.T.C. |
|
Frais de port |
|
NET A PAYER |