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.
FONCTION SI – MISE EN OEUVRE
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 com-pare à 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 argu-ments. 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),
Excel 2007 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 puis-qu‛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.
IMBRICATION DE SI – PLUS DE CRITÈ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),
Excel 2007 Fonction Si – Conditions et Critères
- Tapez le texte L’élève passe,
- Fermez les guillemets (Touche 3 du clavier),
- Tapez un point virgule (;),
A ce stade la formule ne change pas. C‛est à partir de ce point que nous devons poser un nouveau critère sous peine de ne pouvoir envisager que deux solutions. Pour cela, nous allons imbriquer une deuxième fonction SI à l‛intérieur de la première au niveau du SINON de la première fonction SI.
- Tapez SI et ouvrez la parenthèse,
- Sélectionnez de nouveau la moyenne générale (D7),
- Tapez >=,
- Tapez 9,5,
Ce qui en ce point signifie compris entre 9,5 et 10. En effet le compris entre ne s‛exprime pas dans une formule Excel, il se déduit par l‛imbrication des critères. Si Excel lit jusqu‛au deuxième SI de la formule, cela veut dire que le premier critère >=10 n‛est pas vérifié. S‛il vérifie maintenant que la moyenne est >=9,5, nous saurons que la note est bien comprise entre les deux.
- Tapez un point virgule (;),
- Ouvrez les guillemets,
- Tapez le texte Cas à étudier,
- Fermez les guillemets,
- 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 deux parenthèses,
- Validez le calcul par CTRL + Entrée.
En effet le dernier cas se déduit des deux premiers critères. S‛ils ne sont pas vérifiés, nous savons que la note n‛est ni au dessus de 10, ni comprise entre 9,5 et 10. Dans ce cas, pas besoin d‛une nouvelle fonction SI, il suffit décrire l‛action «Lélève Redouble» dans la partie SINON de la deuxième fonction.
Remarque : Nous fermons deux parenthèses car la seconde fonction SI est imbriquée dans la pre-mière. Il faut fermer autant de parenthèses qu‛il y en a d‛ouvertes.
Excel 2007 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 à cet-te adresse : http://www.assistance-en-informatique.com/format-conditionnel-excel_120.aspx
FORMAT CONDITIONNEL DES RÉ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.
Excel 2007 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éri-ques 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 NB.SI.
DÉNOMBREMENT SELON CRITÈRE
La fonction NB.SI 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 NB.SI,
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 for-cément pour l‛utilisateur à plus forte raison si le nombre d‛étudiants est plus impor-tant. Pour cela nous allons mettre en oeuvre la concaténation que nous avions abor-dée dans un cas spécifique au travers d‛un support que vous trouverez à cette adresse : http://www.assistance-en-informatique.com/concatener-assembler-texte_118.aspx
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.