Licence Creative Commons

Cette séance est mise à disposition selon les termes de la Licence Creative Commons Attribution - Partage dans les Mêmes Conditions 4.0 International.
Auteur : Bertrand Kerautret.


5.1 Mise en forme conditionnelle

Les tableurs offrent en général la possibilité de modifier automatiquement la mise en forme d'une cellule en fonction de son contenu. Cette fonctionnalité se retrouve dans les menus relatifs à la mise en forme de la cellule. L'interface permet de définir des conditions de mise en forme (voir cadre bleu dans les exemples ci-dessous) et de choisir le type de style qui sera appliqué quand le contenu de la cellule vérifiera la condition. Les images ci-dessous illustrent des exemples d'interface de mise en forme conditionnelle que l'on retrouve dans différents logiciels existants:

Exemples d'interface de mise en forme conditionnelle que l'on retrouve dans différents logiciels ( Excel, Office etc.)

 

icone à vous de jouer À vous de jouer !





symbole play video   Si vous souhaitez en savoir plus, visualisez cette vidéo




Mise en forme de moyenne de note


icone à vous de jouer À vous de jouer !

  1. Dans cet exercice nous allons tester cette mise en forme sur un tableau de notes d'un diplôme et nous allons illustrer la mise à jour automatique dynamique à partir de l'utilisation de coefficients de notes. Téléchargez le fichier Seance4Partie1Source et sauvegardez le dans un répertoire de votre choix (Documents par exemple) :

    Cliquer ici pour télécharger le fichier Seance4Partie1Source

  2. Ouvrez le fichier avec Excel. Afin de visualiser la moyenne des différents modules, rajoutez une colonne intitulée moyenne et rajoutez une formule de façon à obtenir la note moyenne pour chaque étudiant.

    3. Capture d'écran montrant la colonne moyenne H et la moyenne de chaque étudiant

  3. A partir de l'outil de mise en forme conditionnelle, situé dans le menu Accueil, rajoutez une règle de mise en forme conditionnelle de façon à mettre en couleur rouge les étudiants dont la note est strictement inférieure à 10.

  4. Même question que précédemment, mais en rajoutant une nouvelle règle de façon à mettre en couleur vert clair les étudiants dont la moyenne est supérieure à 10.

    Capture d'écran montrant la colonne moyenne H et la moyenne de chaque étudiant avec les couleurs en fonction de la note


symbole play video Correction : Mise en forme de moyenne de note et mise en forme conditionnelle avec un code couleur




Mise à jour dynamique avec coefficients par matières


icone à vous de jouer À vous de jouer !

  1. Le responsable de diplôme souhaite intégrer des coefficients par matière de façon à mettre plus d'importance sur les matières centrales à son diplôme sans être obligé de tout recalculer. Rajoutez une case coefficient comme sur l'illustration suivante:

    Case coefficient sur la ligne 1 avec un coefficient de 1 pour la colonne B, C, D, E et F

  2. Dans la première case recopier la formule suivante qui va faire le calcul de la moyenne prenant en compte les valeurs des coefficients des cases de la première ligne.

    3. Formule attendu =(B$1*B3+C$1*C3+D$1*D3+E$1*E3+F$1*F3)/(B$1+C$1+D$1+E$1+F$1)

  3. Appliquez la formule sur l'ensemble des moyennes de tous les étudiants.

  4. Modifiez les coefficients de matières en utilisant les valeurs suivantes: 

    • Histoire: coefficients 3
    • Sport: coefficients 3
    • Français: coefficient 1
    • Maths: coefficient 1
    • Anglais: coefficient 4 





icone à vous de jouer À vous de jouer !

  1. Un directeur des études souhaite améliorer ce fichier de façon à ce qu'il soit capable d'identifier rapidement les matières où l'étudiant se situe dans le premier tiers de la promotion. En utilisant la mise en forme conditionnelle /Règles des valeurs de plage haute/basse/, appliquez les règles de façon à obtenir l'affichage ci-dessus:

    2. Résultat attendu. Visionnez de correction pour le tutoriel

    symbole play video Correction : Mise en forme dynamique avec coefficients par matières


  2. Afin de créer une nouvelle fiche destinée aux étudiants, dupliquez votre feuille actuelle.

  3. Dans la feuille dupliquée, l'objectif est d'avoir une version sans valeurs numériques mais avec un code couleur. Il s'agit d'utiliser les couleurs rouges pour une note inférieure à 8, jaune pour une note inférieure à 12 et vert pour les autres valeurs. En exploitant le gestionnaire de mise en forme, faire en sorte d'obtenir l'affichage suivant:

    4. Résultat attendu de l'exercice avec différents points verts/rouge/jaune remplaçant les notes des étudiants


  4. Comme vous pouvez le remarquer, les modifications des notes de la feuille initiale ne sont pas transmises sur la nouvelle feuille avec les icônes rouges, verts, bleus. Pour effectuer cette transmission, faire en sorte que toutes les cellules contiennent non pas les valeurs numériques mais des références vers les cellules de la première feuille.

  5. Vérifiez qu'une modification de plusieurs notes de la feuille initiale est bien transmise sur la nouvelle feuille.
symbole play video Correction : Mise en forme conditionnelle avec un code couleur



5.2 Fonctions logiques et conditions


Au delà de la mise en forme d'une cellule, il est aussi possible de changer son contenu en fonction d'une condition. Cette fonctionnalité est accessible avec l'utilisation d'une formule (en utilisant le symbole =) et une "fonction conditionnelle" qui permettra d'afficher un contenu si une condition donnée par l'utilisateur est vérifiée. Suivant les logiciels cette fonction particulière se retrouve en général avec le mot clé SI ou IF. 

Les images ci-dessous illustrent de fonction conditionnelles saisie dans différents logiciels:

Illustration d'une fonction conditionnelle saisie dans Excel


Illustration d'une fonction conditionnelle saisie dans Office


Illustration d'une fonction conditionnelle saisie dans Google Sheet


Illustration d'une fonction conditionnelle saisie dans Google Sheet


icone à vous de jouer À vous de jouer !




Les tableurs proposent des fonctions basées sur des opérateurs logiques que nous allons manipuler à travers différents exemples concrets comme l'affichage des étudiants en difficultés. Pour cela, il est possible d'utiliser un test logique qui utilisé dans une fonction SI() va permettre d'afficher du contexte selon une condition particulière.




symbole play video   Si vous souhaitez en savoir plus sur l’importation de données dans Excel, visualisez cette vidéo



Base des fonctions conditionnelles SI()


Pour comprendre le fonctionnement de la fonction SI() il s'agit de tester avec exemple minimaliste. La syntaxe de la fonction SI() est la suivante: 

Capture d'écran du texte: SI(test_logique; [valeur_si _vrai]; [valeur_si_faux])

Cette fonction sera remplacée par valeur_si_vrai si le contenu de test_logique est vrai et sera remplacée par valeur_si_faux dans le cas contraire. 

En utilisant un test logique basique comme 42=43, vérifiez que la fonction est remplacée par la bonne valeur. Vous pourrez prendre une case de votre choix et saisir la ligne suivante et tester de modifier le test:

Capture d'écran du texte: fx =SI(42=43;"condition est VRAI";"condition est FAUX")


icone à vous de jouer À vous de jouer ! Fonction SI

Appliquez la fonction pour indiquer dans la nouvelle colonne les étudiants qui sont ajournés avec une note moyenne inférieure à 10. Vous devez obtenir l'affichage suivant:

Illustration du rajout d'une nouvelle colonne si les étudiants qui sont ajournés ou validés.


symbole play video Correction : Fonction "SI"



Cumuler plusieurs test logiques

Il est aussi possible de tester plusieurs conditions à l'intérieur d'un SI() ou en utilisant OU(). La syntaxe est la suivante :

Syntaxe de la fonction OU: OU(valeur_logique1; [valeur_logique2]; ...)

Le résultat de la fonction sera vrai, si l'une des expressions séparées par des virgules est vraie.

Pour d'autres exemples sur l'utilisation des OU, vous pouvez vous référer à la documentation en cliquant ici →  cliquez ici pour accéder à la documentation.


icone à vous de jouer À vous de jouer !

  1. Dans une nouvelle case, calculez les valeurs des expressions suivantes: OU(42=42; 43=1) et OU(44>42; 44>10).

  2. Sur le même principe, il est aussi possible de construire une expression vérifiant que toutes les expressions sont vraies. Pour cela, il est possible d'utiliser un ET() dont la syntaxe est la même que pour l'opérateur OU de la question précédente. Écrire une expression testant cet opérateur.

  3. En appliquant ces expressions de tests logiques à l'intérieur de la fonction SI(), rajoutez un titre "Note éliminatoire" pour la colonne I et utilisez une formule pour indiquer si l'étudiant a une note éliminatoire. Si tel est le cas il devra afficher "oui".

  4. Enfin, on veut donner l'information si l'étudiant doit redoubler (moyenne < 10 et note éliminatoire), ou si il peut avoir son semestre par compensation, (moyenne < 10 et pas de note éliminatoire). Indication: il est possible d'imbriquer des SI. Vous devez pouvoir obtenir quelque chose comme:

Capture d'écran montrant le rajout d'une colonne "Note éliminatoire" et d'une colonne "Passage S2"


symbole play video Correction : Fonctions "ET" et "OU"


5.3 Tests logiques dans la mise en forme conditionnelle

Les éléments clés des parties précédentes peuvent s'utiliser conjointement pour effectuer des mises en formes plus avancées. Par exemple il est possible d'effectuer une mise en forme en fonction d'une condition. Pour terminer cette séance il s'agit simplement de mettre en surbrillance les noms des étudiants dont le redoublement est proposé au jury de fin d'année.



icone à vous de jouer À vous de jouer !

  1. En utilisant un test logique sur le résultat de la colonne J, ajoutez une mise en forme conditionnelle de façon à mettre en rouge tout les noms des personnes qui vont devoir redoubler.

  2. Même question mais pour identifier les étudiants ayant à la fois une note supérieur à 12 en Math et en Français.

    3. Capture d'écran qui affiche les cellules remplis en vert ou en rouge en fonctions des notes de la consigne vue précédemment

symbole play video Correction : Suite mise en forme conditionnelle



symbole play video   Si vous souhaitez en savoir plus, visualisez cette vidéo




5.4 Validation des données

Dans les tableur il existe aussi une autre fonction avancée qui permet de contrôler les valeurs saisies par l'utilisateur. L'idée centrale est d'ajouter une contrainte dans le type de données que pourra recevoir la cellule ce qui pourra se traduire par l'obligation de saisir, par exemple un chiffre entre 1 et 10 ou de saisir un texte parmi une liste de possibilités qui apparaitra sous une forme de liste déroulante.

Les images ci-dessous illustrent différents style de validation des données rencontrées dans différents logiciels:

Différents styles de validations de données de différents logiciels: Excel, Numbers, Google Sheet et Office


icone à vous de jouer À vous de jouer !





icone à vous de jouer À vous de jouer !

Dans cet exercice il s'agit d'appliquer la validation des données pour contrôler les notes.

  1. Afin qu'un enseignant ne rajoute pas une note en dehors des critères de notation (note entre 0 et 20), rajoutez une validation des données sur la première feuille.

  2. Testez de rajouter une note de 22. Vous devez obtenir l'image suivante:

    Image de la fenêtre qui s'ouvre si la valeur attendue ne correspond pas aux restrictions de validation des données

    3. symbole play video Correction : Validation des données - Partie 1


  3. Le directeur des études souhaite avoir une zone où il va saisir les noms des étudiants à convoquer. Sous le tableau des notes, rajoutez du texte de façon à avoir la zone souhaitée comme sur l'image (a) ci-dessous. Vous pourrez utiliser la fusion des cellules pour avoir l'affichage du titre sur deux cellules.

    4. Capture d'écran de deux cellules fusionnées ( Liste d'étudiants à convoquer<img class=" class="atto_image_button_text-bottom" width="250" height="130">


  4. Mettez en place une validation des données en utilisant la liste comme le critère de validation et comme source la liste des étudiants du tableau de note. Après la mise en place de cette validation des données vous devriez obtenir une saisie ressemblant à l'image suivante:

    5. Capture d'écran du résultat attendu avec une validation des données

symbole play video Correction : Validation des données - Partie 2





La conception de ce cours a bénéficié d'un financement de :

Logo gouvernement “France Relance”  Logo Union Européenne “Financé par” Logo « Ministère de l’enseignement supérieur et de la recherche »Logo « Hybrid it » Université de Lyon Logo Université de Lyon

Modifié le: mercredi 12 juillet 2023, 14:15