Mise en forme conditionnelle

Cette fonction très efficace permet de mettre en valeur des informations particulières dans une feuille de calculs Excel.

Les mises en forme conditionnelles peuvent être très sophistiquées dès lors que vous maîtrisez les formules de calcul d'Excel, et l'utilisation des dollars dans les formules.

Données : Télécharger les données - Fichier résultat

Exercice : Dans le fichier téléchargé, répondre aux questions numérotées suivantes. Pour bien vous organiser, créer une nouvelle feuille pour chaque nouvelle question. Cette feuille sera une copie de la feuille "3255". Vous renommerez chacune des nouvelles feuilles avec un N° correspondant à la question à traiter.

Rappels :

• Copier une feuille : Utiliser CTRL Cliqué / glissé sur l'onglet de la feuille à copier
• Sélectionner une colonne, sans aller jusqu'en bas de la feuille !! Cliquer sur la cellule du haut et faire CTRL MAJ Flêche basse.

Pour commencer : Mettre en surbrillance :

1. Créer une copie de la feuille 3255 en une feuille que vous renommerez "1". Mettre en surbrillance les salaires supérieurs à 3000.
2. Dans une feuille nommée "2", mettre en surbrillance les salaires supérieurs ou égaux à 2500.

Continuer à créer une feuille pour chaque question et répondre aux questions suivantes.

3. Les dates de l'année 1970
4. Les salaires supérieurs à la moyenne des salaires
5. Les salaires inférieurs ou égaux à la moyenne des salaires
6. Utiliser un jeu de 3 icônes pour caractériser les salaires par rapport aux deux seuils de 1500 et 3000. Vous êtes libre sur l'appréciation des bornes.
7. Avec un fond de couleur, les 3 salaires les plus élevés.
8. Les salaires supérieurs ou égaux à une valeur saisie dans une cellule externe au tableau. La cellule externe sera par exemple la cellule K1, dans laquelle vous écrirez une valeur numérique qui servira de seuil à votre mise en forme conditionnelle.
9. Dans cette question, il n'y a pas de nouvelle feuille à créer.
   Cette question demande de changer des mises en forme que vous avez faites. Pour cela, il vous faudra sélectionner les cellules dont vous souhaitez changer la mise en forme, et aller dans le menu "Gérer les règles". Dans l'écran qui s'affiche, sélectionner la règle et cliquer sur le bouton "Modifier la règle".
• Revenir à la feuille "1" et modifier la mise en forme pour que cette fois ci cela concerne les salaires supérieurs à 3500.
• Revenir à la feuille "7", et changer franchement la couleur de mise en forme des salaires les plus élevés.
10. A nouveau sur une nouvelle feuille que vous nommerez "10", mettre en surbrillance avec un fond de couleur, les salaires extrèmes, c'est à dire le plus petit salaire et le plus grand salaire. Cette question est posée pour montrer que l'on peut enchaîner plusieurs mises en forme sur une même sélection.
11. Avec un fond de couleur, les salaires qui ne sont pas compris entre 2000 et 3000.

Ca se complique un peu : Mettre en surbrillance :

12. La colonne des noms correspondant aux femmes
    Il faut commencer par sélectionner les noms. Cette question ne se résoud pas par une régle prédéfinie.
    Donc, vous devez faire "Mise en forme cond."/ Nouvelle règle / Utiliser une formule ....
    Pour trouver la réponse, posez-vous la question suivante .: "Quelle est la condition que doit vérifier la cellule "Archambaud" pour qu'elle soit colorisée ? C'est cette condition qu'il faut écrire dans la zone de saisie.
13. Les lignes entières correspondant aux personnes de Lyon
    * Dans le cas de "lignes entières", il faut, au départ, sélectionner tout le tableau à l'exception de la première ligne contenant les en-têtes de colonne.
    * Le test concerne la cellule de l'agence.
    * Le résultat de la mise en forme est visible ci-dessous.

14. Les lignes entières des femmes gagnant au moins 2000 €
    => Indication : Dans le cas de deux conditions simultanées, il faut utiliser la fonction ET() qui s'écrit comme suit :          =ET(condition1;condition2)
15. La colonne des noms correspondant aux femmes de Lyon
16. Les lignes entières des femmes de Paris travaillant en comptabilité
    => Dans cette question, on passe à 3 conditions simultanées. Il faut savoir que dans la fonction ET(), on peut écrire jusqu'à 30 conditions.

Ca chauffe ! : Avec des formules variées ... et plutôt difficiles !

17. Les lignes des personnes nées en 1970
    * Indication : Pour l'écriture du test, utiliser la fonction ANNEE(). Cette fonction renvoie l'année de la date que vous lui fournissez. A vous de comparer cette année à 1970.
    Pour comprendre la fonction ANNEE, placer vous dans la cellule J2 et saisir la formule =ANNEE(E2). Cela vous montre comment fonctionne cette fonction.
18. Les lignes des personnes ayant leur anniversaire aujourd'hui. Il faut donc comparer les dates de naissance de la feuille Excel avec la date du jour où vous faites l'exercice.
    * La fonction aujourdhui() renvoie la date du jour. Notez qu'il n'y a pas d'apostrophes, et qu'il faut écrire les deux parenthèses bien qu'elles soient vides!
    * Pour résoudre la question, il vous faudra éventuellement modifier les donnéees pour que certaines dates de naissance de la feuille Excel aient les mêmes jour et date que la date à laquelle vous faites l'exercice.
    * La fonction MOIS(...) renvoie le mois d'une date que vous lui indiquez. Donc l'expression MOIS(AUJOURDHUI()) renvoie le mois de la date du jour.
    * En utilisant à bon escient les fonctions ET, AUJOURDHUI, MOIS, JOUR, vous devriez y arriver.
19. Les personne du service informatique de l'agence de Marseille et ayant au moins 50 ans.
    * La difficulté est due à l'écriture du test sur la date.
    Si vous écrivez une expression du type E2=1/12/1960, alors Excel ne voit pas que vous avez écris une date. Dans sa logique, il compare E2 au résultat du calcul de 1 divisé par 12 et divisé ensuite par 1960. Il faut savoir que pour écrire une date dans un test, et pour que celle-ci soit comprise, alors le test doit être écrit comme suit : E2=DATE(1960;12;1). La fonction DATE(  ;  ;  ) reconstruit une date à partir des trois informations année, mois et jour.