7Microsoft Office Excel 2010Mise en forme conditionnelle pour les grades v cibles

Si, dans mon autre vie, je suis en fait un professeur de sciences, et qu'il obtient habituellement autour que je suis « bien avec les ordinateurs » On me demande souvent de jeter un oeil à quelques choses. A deux reprises au cours des derniers mois on m'a demandé si je mis en place mise en forme conditionnelle dans Excel pour colorer les résultats des tests des élèves de code par rapport à leurs niveaux cibles. Alors que pas exactement en rapport avec le contenu de base de ce site, il est encore DIY tech­nic­al help so I decided to pub­lish a full set of instruc­tions here. Enjoy…

Microsoft Excel Conditional Formatting: Lookup TablesD'abord, vous avez besoin d'une feuille de recherche

  • Cre­ate a new sheet in the workbook.
  • Remplir 2 colonnes - la gauche avec des notes (1c, 1b, 1une, 2c etc) et le droit avec une valeur "score" (peut être juste 1, 2, 3, 4, 5 etc)
  • Remplir 2 des colonnes supplémentaires avec une « recherche inversée », qui est le même que précédemment, mais avec le 2 colonnes inversées
  • Mettez en évidence le tableau complet de la main gauche que vous avez créé, faites un clic droit sur elle, et sélectionnez "définir le nom". Donne lui un nom, e.g. « Recherche »
  • Mettez en surbrillance la deuxième table que vous avez créé, faites un clic droit et définir un autre nom. Donne lui un nom, e.g. "Reverse_lookup"

Microsoft Excel Conditional Formatting: Define NameCréation des feuilles de calcul

  • Maintenant, créez une feuille de calcul pour chaque classe, groupe ou année (comme il convient).  Inclure une colonne pour les classes cibles et différentes colonnes pour les résultats des tests ou d'évaluation (e.g. comme ci-dessous)
  • Notez dans cet exemple que les données cible est en C3 - C5. Les résultats sont en D3 - F5

Microsoft Excel Conditional Formatting: Example Markbook

Construire la mise en forme conditionnelle de base

  • Sélectionnez la cellule D3 - en haut à gauche des résultats des cellules de données. Assurez-vous que vous êtes sur l'onglet « Accueil » et cliquez sur « mise en forme conditionnelle » et sélectionnez « nouvelle règle »
  • Sélectionnez « utiliser une formule pour déterminer les cellules au format »
  • Ce que nous voulons faire est de comparer la cellule actuelle à la cible, mais il est pas tout à fait aussi simple que cela, en raison de la façon dont Excel compare les valeurs - donc nous avons besoin d'utiliser les tables de consultation.
  • Nous avons aussi besoin de 5 règles - 2 ou plus bas sous-niveaux, 1 au-dessous, comme prévu, 1 ci-dessus et 2 ou plus au-dessus. Vous pouvez créer des règles plus si vous voulez plus de détails ou moins si vous voulez juste au-dessous, sur et au-dessus cible.
  • La formule finale pour 2 ou plus bas sous-niveaux seront =VLOOKUP($C3,Lookup,2,FALSE)-1>VLOOKUP(D3,Lookup,2,FALSE)

    Microsoft Excel Conditional Formatting: New Rule Formula

  • Le C3 dans la première partie fait référence à la cellule cible supérieure, and the D3 refers to the top actu­al res­ult cell. The “Look­up” refers to the look­up table range that we named earlier.
  • Pour briser ce bas - nous sommes à la recherche des valeurs numériques pour la cible et le score réel dans la cellule actuelle. Parce que nous voulons 2 ou plus bas sous-niveaux, nous prenons 1 de la cible et dire qu'il doit toujours être supérieur au score. le $ avant la C C3 assure que lorsque nous utilisons la même formule pour d'autres colonnes, ils seront toujours comparés à la colonne cibles.
  • Maintenant, cliquez sur le format et appliquer la mise en forme souhaitée. J'utilise une couleur d'arrière-plan rouge clair
  • Maintenant, nous avons créé 1 de 5 règles pour une seule cellule.

Microsoft Excel Conditional Formatting: Format CellsExtension de la mise en forme conditionnelle pour plusieurs règles

  • répéter ensuite le processus de création 4 plus de règles.
  • Quand ils sont tous fini mon 5 exemples regarderaient comme ci-dessous. Seule la comparaison et l'ajout ou d'une modification de soustraction entre eux. Notez également l'ordre (que nous arrivons à la prochaine)
=VLOOKUP($C3,Lookup,2,FALSE)-1>VLOOKUP(D3,Lookup,2,FALSE)
=VLOOKUP($C3,Lookup,2,FALSE)>VLOOKUP(D3,Lookup,2,FALSE)
=VLOOKUP($C3,Lookup,2,FALSE)=VLOOKUP(D3,Lookup,2,FALSE)
=VLOOKUP($C3,Lookup,2,FALSE)+1<VLOOKUP(D3,Lookup,2,FALSE)
=VLOOKUP($C3,Lookup,2,FALSE)<VLOOKUP(D3,Lookup,2,FALSE)
  • J'utilise rouge moyen, rouge clair, jaune-bas, vert moyen et vert clair (dans cet ordre) des couleurs d'arrière-sol

Tri de l'ordre des règles

  • Enfin, nous devons nous assurer que les règles sont dans le bon ordre, et de les appliquer à toutes les cellules res-ÜLTS, non seulement la cellule res-ult unique D3.
  • Cliquez sur le bouton de mise en forme conditionnelle à nouveau et choisissez « gérer les règles ... »
  • Assurez-vous que les règles sont dans le bon ordre - comme ci-dessus (rouge moyen, rouge clair, jaune-bas, vert moyen, vert clair)Microsoft Excel Conditional Formatting: Rules Manager
  • Cliquez dans la « applique à » et entrer dans la gamme complète de cellules qui contiennent des résultats. Dans mon exemple, cela est =$D$3:$F$5.  Mettez cela en toute 5 "S'applique aux" boîtes
  • This sheet should now fully work with con­di­tion­al formatting

Expanding facilement des feuilles supplémentaires

  • Pour appliquer à d'autres feuilles simple copie et coller une seule cellule qui a déjà mise en forme conditionnelle lui est appliquée, dans la nouvelle feuille.
  • Ensuite, cliquez sur le bouton de mise en forme conditionnelle, sélectionner les règles homme d'âge, et changer le 5 “applies to” boxes to point to the cells of the new sheet which need con­di­tion­al formatting

Laisser un commentaire

7 commentaires

CMChloé Moore

Salut John,

Avez-vous un exemple de feuille que vous avez réalisée? Strug­gling fol­low­ing the steps here!

Répondre
NNikki

J'aimerais re-publier cet article sur mon propre site web, ce que ce serait bien?

Répondre
GZGary Zawadzki

salut,
Grand tutoriel - merci. J'ai 3 différentes couleurs fonctionne parfaitement mais l' 2 ci-dessus et 2 ci-dessous ne sont pas en forme à leurs couleurs correctes. Ils sont en train de changer de rouge et de vert, mais pas les nuances dont ils ont besoin pour changer. Toute sug-ges-tion s'il vous plaît?

Merci,

Gary

Répondre
JSJon Scaife

Désolé pour le retard à répondre Gary - Je suis occupé à migrer le site vers notre nouveau VPS. Avez-vous l'homme d'âge pour obtenir cette sor-ment? If you did­n’t then by all means zap me over your excel file to have a look at. Je vais revérifier ce guide dans la journée ou à côté 2 to make sure there aren’t any typo­’s or mis­lead­ing parts, il a été connu pour se produire 😉

Répondre
EEmma

J'ai suivi toutes vos instructions ci-dessus mais aucun de mes cellules suis allé couleur?? Toute sug-ges-tion où je vais mal??

Répondre
JSJon Scaife

Je suppose que vous avez les pop-u-lated des données, et la colonne cible est également rempli avec des données?
By all means zap me the file over and I’ll take a quick look — I’ve e‑mailed you

Répondre