Excel 2008: créer des "règles"

[Ligeard]

Bonjour,

Je sui désolé si ma question, et le titre, paraissent confus mais je ne sais pas vraiment expliquer ce que je souhaite faire (d'où la difficulté de la recherche sur le net et le forum).

Je suis enseignant et je reporte dans une feuille Excel 2008 les notes des élèves.

J'aimerais savoir s'il est possible de créer des "règles" comme celles-ci: quand je reporte une note, celles sous une certaine valeur (par exemple 10) la cellule soit colorée d'une certaine couleur, automatiquement. Actuellement, je mets une couleur de trame mais je dois le faire manuellement et note par note, ce qui est long et peu pratique.

Est-il possible de rendre cela automatique, avec des macros par exemple?
Si oui, quelqu'un peut-il m'expliquer la méthode?

Enfin, dernière question, puis-je faire cela non plus pour une valeur, mais lorsqu'un élève est "absent"?

Je vous remercie d'avance, en espérant avoir été suffisamment clair.

 

[Aliboron]

Oui, cela se fait par les "Mises en forme conditionnelle" du menu "Format". Tu choisis les critères puis le format à appliquer et voilà...

Si tu as besoin de compléments d'information, décris plus précisément ce que tu fais, ce que tu observes, ce que tu voudrais. Dis en particulier à quoi on peut savoir qu'un élève est absent...

 

[Ligeard]

Merci de ton aide, et très bonne nouvelle.

Par contre, je vais te demander un peu plus de détails pour réaliser cela.

Tout d'abord, tous les devoirs n'ont pas la même valeur (sur 20 ou 10) donc je souhaite créer une règle où la valeur est colorée en rouge lorsqu'elle est inférieure au maximum divisé par 2: et là je ne sais pas comment faire?

Pour un élève absent, j'inscrit "absent", mais du texte peut-il être reconnu?

Merci

 

[Aliboron]

Tout d'abord, tous les devoirs n'ont pas la même valeur (sur 20 ou 10) donc je souhaite créer une règle où la valeur est colorée en rouge lorsqu'elle est inférieure au maximum divisé par 2

Mettons que la note max est dans la cellule A1 et que tu commence par écrire ta condition pour la cellule B2. Tu écris comme condition "la formule est" "B2<($A$1/2)" et tu choisis le format (dans les "Motifs", la couleur rouge). Ensuite tu recopies dans toutes les cellules où tu veux appliquer la formule.

Pour un élève absent, j'inscrit "absent", mais du texte peut-il être reconnu ?

Oui, pas de problème. Cette fois, tu mets tout simplement "La valeur de la cellule" "est égale à" "absent" (attention, pas de guillemets à "absent")

 

[Ligeard]

J'ai réussi pour "absent", et après quelques tentatives j'ai réussi pour les notes.

Est-il possible de faire une mise à jour, en cas de modification de notes, etc?

Un grand merci pour ton aide.

PS: J'ai une autre question: j'aimerais créer une colonne avec la moyenne de l'élève, dois-je créer la formule ligne par ligne, ou il est possible de le faire pour l'ensemble de la feuille.
Je détaille, dans la colonne C, j'aimerais voir apparaître la moyenne. Mais est-ce que je dois taper la formule pour C3 (moyenne des lignes D3, E3,etc.), puis pour C4 (moyenne des lignes D4, E4,etc.), etc.

 

[Aliboron]

Est-il possible de faire une mise à jour, en cas de modification de notes, etc ?

A priori, je dirais que oui, bien sûr, c'est modifiable quand on veut. Mais que veux-tu dire précisément par "en cas de modification de notes" ?

 

[Ligeard]

J'ai modifié une note, est la condition s'applique sans souci, donc il est possible de "mettre à jour".

Il me reste les moyennes à régler.

 

[r e m y]

J'ai modifié une note, est la condition s'applique sans souci, donc il est possible de "mettre à jour".

Il me reste les moyennes à régler.

Bien sûr! la mise en forme conditionnelle reste "collée" à la cellule et chaque fois que la valeur de la cellule change, les conditions sont évaluées et à la première condition vérifiée, la mise en forme correspondante s'applique

Par contre, le nombre de conditions applicable à une cellule est limité. Pas plus de 3 conditions, je crois

 

[Ligeard]

Merci pour votre aide, par contre, je travaillais sur un fichier excel créé par un collègue.

Le problème, c'est qu'il n'y avait pas assez de lignes pour mettre l'ensemble des élèves.
J'ai donc inséré quelques lignes, mais voilà que les formules afin de calculer la moyenne des élèves n'est pas présente pour ces nouvelles lignes. Et qu'il y a des onglets pour une feuile pour l'année à modifier!

Et, je ne comprends absolument pas la formule utilisée, quelqu'un peut-il m'aider? Je désespère.
S'il faut, je peux envoyer ce fichier aux âmes servantes.

Je découvre le potentiel d'Excel, mais aussi ces difficultés.

 

[Fìx]

Copier => coller!

 

[Ligeard]

J'ai essayé, mais ça ne marche pas! Malheureusement!

En fait, le problème c'est le numéro des cellules. Si je colle la formule à la ligne 38 par exemple, les références ne sont pas celles des cellules de la ligne 38 mais de la ligne où j'ai fait "copier".

Dans ce cas, il faudrait modifier le numéro des cellules, mais je ne comprends pas la formule, je ne sais pas quel numéro je dois changer. Et la formule est longue...

 

[Aliboron]

Colle-nous la formule, qu'on voie comment elle est faite. Probablement un problème avec des références "absolues" (quand il y a des dollars devant les intitulés de lignes et/ou de colonnes), mais tu comprends bien qu'on ne peut pas deviner...

 

[Ligeard]

Oui, je comprends tout à fait, et je vous remercie de votre patience pour m'aider.

Je vous colle la formule pour une ligne:
=SI(SOMME(C6:AD6)=0;"A";SOMME(C6:AD6)/(SI(ESTNUM(C6)=VRAI;$C$3)+SI(ESTNUM(D6)=VRAI;$D$3)+SI(ESTNUM(E6)=VRAI;$E$3)+SI(ESTNUM(F6)=VRAI;$F$3)+SI(ESTNUM(G6)=VRAI;$G$3)+SI(ESTNUM(H6)=VRAI;$H$3)+SI(ESTNUM(I6)=VRAI;$I$3)+SI(ESTNUM(J6)=VRAI;$J$3)+SI(ESTNUM(K6)=VRAI;$K$3)+SI(ESTNUM(L6)=VRAI;$L$3)+SI(ESTNUM(M6)=VRAI;$M$3)+SI(ESTNUM(N6)=VRAI;$N$3)+SI(ESTNUM(O6)=VRAI;$O$3)+SI(ESTNUM(P6)=VRAI;$P$3)+SI(ESTNUM(Q6)=VRAI;$Q$3)+SI(ESTNUM(R6)=VRAI;$R$3)+SI(ESTNUM(S6)=VRAI;$S$3)+SI(ESTNUM(T6)=VRAI;$T$3)+SI(ESTNUM(U6)=VRAI;$U$3)+SI(ESTNUM(V6)=VRAI;$V$3)+SI(ESTNUM(W6)=VRAI;$W$3)+SI(ESTNUM(Y6)=VRAI;$Y$3)+SI(ESTNUM(Z6)=VRAI;$AA$3)+SI(ESTNUM(AA6)=VRAI;#REF!)+SI(ESTNUM(AB6)=VRAI;$AB$3)+SI(ESTNUM(AC6)=VRAI;$AC$3)+SI(ESTNUM(AD6)=VRAI;$AD$3))*20)

Pour donner quelques détails: les colonnes de notes débutent de C à AD, et la colonne AE correspond à la colonne moyenne.

Effectivement, il y a des dollars.
Disons que pour un débutant, cette formule paraît barbare.

 

[Aliboron]

Alors, cette formule doit être en AE6, d'après ce que tu décris, et il y a sur la ligne 3 des coefficients ou quelque chose du genre, l'idée étant de faire une moyenne pondérée multipliée par 20.

Bon, déjà, une erreur est facile à identifier :

=SI(SOMME(C6:AD6)=0;"A";SOMME(C6:AD6)/(SI(ESTNUM(C6)=VRAI;$C$3)+SI(ESTNUM(D6)=VRAI;$D$3)+SI(ESTNUM(E6)=VRAI;$E$3)+SI(ESTNUM(F6)=VRAI;$F$3)+SI(ESTNUM(G6)=VRAI;$G$3)+SI(ESTNUM(H6)=VRAI;$H$3)+SI(ESTNUM(I6)=VRAI;$I$3)+SI(ESTNUM(J6)=VRAI;$J$3)+SI(ESTNUM(K6)=VRAI;$K$3)+SI(ESTNUM(L6)=VRAI;$L$3)+SI(ESTNUM(M6)=VRAI;$M$3)+SI(ESTNUM(N6)=VRAI;$N$3)+SI(ESTNUM(O6)=VRAI;$O$3)+SI(ESTNUM(P6)=VRAI;$P$3)+SI(ESTNUM(Q6)=VRAI;$Q$3)+SI(ESTNUM(R6)=VRAI;$R$3)+SI(ESTNUM(S6)=VRAI;$S$3)+SI(ESTNUM(T6)=VRAI;$T$3)+SI(ESTNUM(U6)=VRAI;$U$3)+SI(ESTNUM(V6)=VRAI;$V$3)+SI(ESTNUM(W6)=VRAI;$W$3)+SI(ESTNUM(Y6)=VRAI;$Y$3)+SI(ESTNUM(Z6)=VRAI;$AA$3)+SI(ESTNUM(AA6)=VRAI;#REF!)+SI(ESTNUM(AB6)=VRAI;$AB$3)+SI(ESTNUM(AC6)=VRAI;$AC$3)+SI(ESTNUM(AD6)=VRAI;$AD$3))*20)

Cette référence devait correspondre à une cellule dans une colonne qui a été supprimée.

On voit aussi que, jusqu'à la colonne Y, c'est toujours la cellule sur la troisième ligne de la même colonne qui sert pour le coefficient mais ensuite, ça se modifie, séquelle d'une suppression de cellule. On devrait évidemment avoir .../...+SI(ESTNUM(Z6)=VRAI;$Z$3)+SI(ESTNUM(AA6)=VRAI;$AA$3)+.../...).

Une fois cette correction apportée, la formule devrait fonctionner en tous temps et pouvoir se copier vers le bas sans problème particulier.

On peut aussi simplifier la chose et écrire la formule ainsi :
=(SOMME(C6:AD6)/SOMME(($C$3:$AD$3)*ESTNUM(C6:AD6)*NON(ESTVIDE(C6:AD6))))*20

Ensuite, il faut la valider en tenant la touche "Commande" en même temps qu'on tape la touche "Retour" ce qui a pour effet de la valider comme "fonction matricielle" (c'est à dire que le calcul décrit dans la formule s'exécute pour chacune des cellules concernées) et cela se manifeste par l'affichage d'accolades :
{=(SOMME(C6:AD6)/SOMME(($C$3:$AD$3)*ESTNUM(C6:AD6)*NON(ESTVIDE(C6:AD6))))*20}

Normalement, ça devrait donner le même résultat...

 

[Ligeard]

C'est vraiment gentil de m'aider, j'ai corrigé la formule comme tu me l'a suggéré. J'ai conservé l'ancienne.

Par contre, lorsque je colle la formule par exemple de le ligne 10, à la ligne 12 (nouvelle ligne) les références restent celles de cellules de la ligne 10, le changement ne se fait pas.

J'ai pourtant essayé avec commande et retour, mais je dois mal m'y prendre.

PS: Je viens d'essayer ta formule, et il y a une erreur.

Je me permets de mettre un lien vers cette "maudite" grille : Lien supprimé

 

[Aliboron]

C'est vraiment gentil de m'aider, j'ai corrigé la formule comme tu me l'a suggéré. J'ai conservé l'ancienne.

C'est à dire ? Tu as fait les corrections dans l'ancienne formule et ça fonctionne ?

Par contre, lorsque je colle la formule par exemple de le ligne 10, à la ligne 12 (nouvelle ligne) les références restent celles de cellules de la ligne 10, le changement ne se fait pas.

Quelle formule ? La tienne "corrigée" ? Celle que j'ai indiqué (en formule matricielle) ? Comment as-tu procéder pour coller ? En "tirant" sur le coin en bas à droite de la cellule (là où le curseur se transforme en signe plus) ? En copiant la cellule en entier (menu "Edition" > "Copier" ou raccourci "Commande-C") puis en allant coller (toujours par le menu ou par "Commande-V") ?

J'ai pourtant essayé avec commande et retour, mais je dois mal m'y prendre.
PS: Je viens d'essayer ta formule, et il y a une erreur.

Là, tu veux parler de la formule matricielle. Lorsque tu la saisis ou la modifie, il faut impérativement la valider avec avec la combinaison "Commande-Retour" (ou avec la combinaison "Majuscule-Contrôle-Retour"). Lorsque la validation matricielle n'est pas faite, la cellule affiche des valeurs incohérentes ou (le plus souvent) un code erreur. C'est normal. Lorsque la validation matricielle est faite correctement, on voit dans la barre de formules les accolades qui sont ajoutées autour de la formule (voir plus haut).

Au passage, si aucune note n'a été saisie dans les cellules C6:AD6, tu auras un message d'erreur de division par zéro (en effet, j'ai sauté la toute première partie de ta formule initiale). Il faudrait donc saisir dans la cellule AE6 la formule suivante :
=SI(SOMME(C6:AD6)=0;"-";(SOMME(C6:AD6)/SOMME(($C$3:$AD$3)*ESTNUM(C6:AD6)*NON(ESTVIDE(C6:AD6))))*20)

et la valider comme formule matricielle. Elle s'affichera donc ainsi :
{=SI(SOMME(C6:AD6)=0;"-";(SOMME(C6:AD6)/SOMME(($C$3:$AD$3)*ESTNUM(C6:AD6)*NON(ESTVIDE(C6:AD6))))*20)}

Normalement, rien ne s'oppose à ce que tu la recopies vers le bas (comme vu, en "tirant" sur le coin en bas à droite, par exemple)...

 

[Ligeard]

Alors, j'ai bien avancer avec cette grille.
J'ai ajouté les lignes qui me manquaient, et corrigé la formule lorsque ça n'allait pas.

J'ai conservé la formule présente dans le fichier, j'ai corrigé l'erreur que tu me signalais, au niveau du "ref": .../...+SI(ESTNUM(Z6)=VRAI;$Z$3)+SI(ESTNUM(AA6)=VRAI;$AA$3)+.../...).

Pour ta formule, j'ai du faire une mauvaise manipulation au moment de la rendre matricielle.
Comme cela fonctionne pour l'instant (mais elle n'est pas assez remplie pour être sur), je vais conserver la formule et je testerai la tienne plus tard.

Je faisais surtout une grosse erreur en faisant un simple "copier-coller" pour ajouter la formule, mas j'ai trouvé comme tu me l'as indiqué la fonction dans le coin droit de la cellule.

Une dernière question: pour les conditions, je ne peux les définir dès maintenant?
Je m'explique: je sais maintenant colorier les cellules en rouge où la note est inférieure à une certaine valeur. Mais comme je dois renseigner la cellule avec la valeur de référence, et que cette valeur change selon si c'est un devoir sur 10 ou 20, je ne peux créer une condition pour l'ensemble de la feuille.
Ca ne marche pas comme pour les formules où on tape une fois la formule et on la glisse sur le reste des lignes?

En tout cas un énorme merci pour ta patience et ton aide.

PS: Je n'ai pas compris au final comment taper la formule, je n'ai fait qu'utiliser des formules déjà écrites, quel site permettrait de comprendre comment on les détermine? (pour le futur!)

 

[Aliboron]

pour les conditions, je ne peux les définir dès maintenant ?
Je m'explique: je sais maintenant colorier les cellules en rouge où la note est inférieure à une certaine valeur. Mais comme je dois renseigner la cellule avec la valeur de référence, et que cette valeur change selon si c'est un devoir sur 10 ou 20, je ne peux créer une condition pour l'ensemble de la feuille.
Ca ne marche pas comme pour les formules où on tape une fois la formule et on la glisse sur le reste des lignes ?

En gros, oui. D'après ce que j'ai compris, la référence est sur la ligne 3 (en C3:AD3, plus exactement). Donc, si tu te positionnes sur la cellule C6 et que tu vas dans "Format" > "Mise en forme conditionnelle..." tu mets :
la formule est =C6<(C$3/2)
Comme ça, la formule fera référence à la cellule elle-même, comparée à la cellule de la 3e ligne dans la même colonne (noter la présence d'un seul symbole dollar, qui fige la ligne n°3 dans la formule). Si tu recopies la cellule vers la droite (donc en D6) tu verras qu'elle se modifie en =D6<(D$3/2) et si tu la recopies vers le bas (donc en C7) elle se modifie en =C7<(C$3/2) Autrement dit, tu peux recopier tant que tu veux. Tu peut aussi le faire en passant par "Edition" > "Collage spécial.." et en choisissant de coller le "Format"

PS: Je n'ai pas compris au final comment taper la formule, je n'ai fait qu'utiliser des formules déjà écrites, quel site permettrait de comprendre comment on les détermine ?

Bah, en gros tout bouquin ou site consacré aux bases d'Excel (voire même aux bases des tableurs) devrait te permettre de mieux comprendre les mécanismes de la chose.

 

[Ligeard]

Merci beaucoup, un gros travail de fait.