ISTA NTIC EXCEL
TP 5
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.
Conseil de classe
Moyenne
Civ Elèves Avis du conseil
générale
M Marc 12
Mme Sophie 12
M Fred 8
Mme Anne 9,6
Mme Christine 14
M Luc 12
M Roger 9,8
Mme Tifanie 13
M Paul 9
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,
Mr. OUZAOUIT Page 1
ISTA NTIC EXCEL
● Tapez le nom de la fonction SI,
● Ouvrez la parenthèse,
Comme vous le constatez, une info-bulle apparaît confirmant que la fonction requiert
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),
● 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.
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),
Mr. OUZAOUIT Page 2
ISTA NTIC EXCEL
● Tapez >=,
● Puis tapez 10 suivi d’un point virgule (;),
● 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 (;),
● Tapez SI et ouvrez la parenthèse,
● Sélectionnez de nouveau la moyenne générale (D7),
● Tapez >=,
● Tapez 9,5,
● 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.
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 pertinemment au problème lorsque le nombre
de possibilités est trop grand.
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.
Mr. OUZAOUIT Page 3
ISTA NTIC EXCEL
La lecture est désormais plus efficace. En un clin d’œil nous remarquons qu’’il y a deux
redoublements.
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 [Link].
DÉNOMBREMENT SELON CRITÈRE
[Link] permet de compter le nombre de cellules qui répondent à un critère ; par exemple,
pour compter le nombre de fois où une valeur apparaît dans une série de données.
Syntaxe : [Link](plage; critères)
La fonction [Link] 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.
Exemple :
saisir dans la cellule B6 : =[Link](A2:A5; “pommes”)
ici on compte le nombre de cellules contenant « pommes » dans les cellules A2 à A5. Le résultat
est 2.
saisir dans la cellule B7 : =[Link](A2:A5;A4)
Compte le nombre de cellules contenant « pêches » (à l’aide du critère dans A4) dans les cellules
A2 à A5. Le résultat est 1.
Mr. OUZAOUIT Page 4
ISTA NTIC EXCEL
saisir dans la cellule B8 : =[Link](B2:B5; “>55”)
Compte le nombre de cellules dont la valeur est supérieure à 55 dans les cellules B2 à B5. Le
résultat est 2.
● Sélectionnez la cellule E18,
● Tapez = pour lancer le calcul,
● Tapez [Link],
Attention de ne pas réaliser le point (.) de la fonction avec le pavé numérique qui
conduira à une virgule(,).
● 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 œuvre la concaténation.
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,
● Tapez & " Admissions",
● Validez par Entrée.
Nous assemblons le résultat numérique au texte Admissions précédé d’un espace.
EXERCICE 2 : Fonction Si et Références absolues
PANNEAU DE CONTRÔLE, VALEURS DYNAMIQUES
En utilisant toujours le tableau de l’exercice 1.
● Sur les cellules G1 à H4, réalisez le panneau de contrôle présenté par la
figure,
Mr. OUZAOUIT Page 5
ISTA NTIC EXCEL
En plaçant toutes les valeurs dans un tableau externe, les formules feront référence à
des cellules dont il suffira de modifier les contraintes (Conditions de passage,
appréciations) pour que les résultats de calcul se mettent à jour. Maintenant, il s’agit de
mettre à jour les formules de la colonne E. Toutes les valeurs statiques doivent être
remplacées par les cellules correspondantes du panneau de contrôle. Ces cellules doivent
être figées (Touche F4) du clavier pour que la formule puisse être reproduite sur
toutes les lignes de la colonne.
● Sélectionnez la première cellule du calcul (E7),
● Enfoncez la touche F2 pour passer en mode saisie,
● Sélectionnez la valeur 10 du premier critère,
● Et cliquez sur la cellule correspondante du panneau G2,
Ainsi nous remplaçons le nombre 10 du critère par une valeur dynamique.
● Enfoncez la touche F4 du clavier pour figer cette cellule,
● Sélectionnez ensuite le texte L’élève passe avec les guillemets,
● Cliquez sur la cellule H2 du panneau pour le remplacer,
● Enfoncez la touche F4 du clavier pour figer cette référence,
...
● Terminez toute la formule en suivant le même procédé de manière à remplacer
toutes les valerus statiques
par les cellules du panneau de contrôle,
● Une fois la formule terminée, validez par CTRL + Entrée,
● Puis tirez la poignée de la cellule pour reproduire le calcul sur toute la
colonne E,
● N’hésitez pas à utiliser la balise active qui se déclenche (petit bouton) pour
rétablir la mise en forme.
Pour constater que désormais tout est bien dynamique, nous allons modifier quelques
contraintes du problème.
● Modifiez la valeur 10 du panneau de contrôle en 12,
● Changer la valeur 9,5 à 10,5,
Mr. OUZAOUIT Page 6
ISTA NTIC EXCEL
Vous le remarquez, tous les résultats se mettent instantanément à jour dans la colonne
E. Le format conditionnel précédemment posé renforce cette effet puisque tous les
redoublements s’affichent en rouge.
● Modifiez maintenant le texte l’élève passe par Admis,
● Puis, modifiez le texte l’élève redouble par Recalé,
Là encore, nous constatons que tous les résultats se mettent instantanément à jour ce
qui n’aurait pas été possible avec les fonctions statiques du début de l’exercice.
Néanmoins ce n’est pas le cas du format conditionnel. Ce dernier était calibré pour
afficher en rouge tous les textes L’élève redouble. Comme nous avons modifié la
contrainte dans le panneau de contrôle ce format n’est plus fonctionnel. Nous devons le
rendre lui aussi dynamique.
FORMAT CONDITIONNEL DYNAMIQUE
● Sélectionnez toutes les cellules du calcul (E7 à E15),
● Déroulez le bouton Mise en forme conditionnelle du ruban Accueil,
● Cliquez sur Gérer les règles tout en bas,
● Dans la boîte de dialogue, cliquez sur Modifier les règles,
La règle en cours (Valeur de la cellule...égale à...="L’élève redouble") apparaît dans la
section inférieure. Il suffit de remplacer le critère statique "L’élève redouble" par la
cellule du panneau de contrôle.
● Sélectionnez ="L’élève redouble" dans la zone de saisie,
● Cliquez sur la cellule H4 du panneau de contrôle,
Celle-ci est automatiquement figée avec les Dollars par Excel.
● Cliquez sur Ok pour valider la première boîte de dialogue,
● Cliquez de nouveau sur Ok pour valider la seconde.
Cette fois le format conditionnel est lui aussi dynamique. Vous pouvez faire l’essaie en
modifiant en cellule H4 le texte Recalé en Refusé. Vous constatez que le format
conditionnel prend en compte le changement.
Mr. OUZAOUIT Page 7