Excel: modifier la formule d'un tableau

[Ligeard]

Je prends goût à Excel!

J'ai essayé de comprendre tes formules et de les appliquer dans un nouveau fichier pour l'appel.

J'ai créé mon tableau:
- Ligne 1: Date
- Ligne 2: infos diverses
- Ligne 5 à 41: élèves
- Colonne C à AD: Abs ou rien si présent

J'ai tapé cette formule pour calculer le nombre d'absents (même principe avoir nombre d'Abs/ nombre de colonnes où j'ai tapé une date):
=TEXTE(NB.SI(C5:AD5;"Abs");"##")&"/"&TEXTE(NBVAL(C1:AD1);"##")

Elle fonctionne pour la première ligne 5, mais elle se recopie mal. Par exemple à la ligne 6, j'ai:
=TEXTE(NB.SI(C6:AD6;"Abs");"##")&"/"&TEXTE(NBVAL(C2:AD2);"##")

Je l'ai pourtant rendu matricielle, et procéder comme pour les autres.
Elle ne doit pas être bonne.

Enfin, pour un bilan de l'année dans un autre onglet, je voulais faire la somme de toutes les absences avec cette formule:
=SI(SOMME('Trimestre 3'!AD5;'Trimestre 2'!AD5;'Trimetre 1'!AE5)=0;"absent";SOMME('Trimetre 1'!AE5;'Trimestre 2'!AD5;'Trimestre 3'!AD5))

Mais je ne peux pas la tester.

Encore des solutions à mes nombreux problèmes?

Merci, bonne soirée.

 

[zeltron54]

Bonjour,
Ta formule 1, fonctionne mais il faut modifier comme ça !

=TEXTE(NB.SI(C5:AD5;"Abs");"##")&"/"&TEXTE(NBVAL($C$1:$AD$1);"##")

explications: les $ devant les indices de cellules indiquent que ces cellules ne sont pas à incrémenter lors de la recopie.
les "##" sont le formatage du texte pour l'affichage soit 0,1 ou 2 chiffres, si tu veux avoir le 0 qui s'affiche, met le format "0#".

Quand à ta 2ième formule, je ne comprend pas ce qu'elle est censée faire et à quoi corresponde AD5, AE5 et 'trimestre' , je suppose que ce sont des onglets (1 par trimestre) et que AD5 ou AE5 sont les cellules qui contiennent le nombre d'absent ????

Si ce n'est pas indiscret peux-tu m'envoyer ton fichier , ce serais plus simple pour comprendre et essayer

 

[Ligeard]

Merci pour la correction.

Je t'envoie en MP le lien pour le fichier.

 

[zeltron54]

Bonsoir,

Ton lien ne fonctionne pas.

pour les ## va voir dans format-->cellule onglet nombre --> personnalisée tu trouveras les différents formats possibles.

 

[Ligeard]

OK, je vas voir.
Je retente pour le lien en MP.

 

[zeltron54]

ok !
alors sur ta page total année tu entres
en C5 : =NB.SI('Trimetre 1'!C5:AD5;"Abs")
en D5 : =NB.SI('Trimestre 2'!C5:AD5;"Abs")
en E5 : =NB.SI('Trimestre 3'!C5:AD5;"Abs")

en E6 : =SOMME(C5:E5)

et tu peux entrée ces formules sans la touche cmd (pas de commande matricielle)

et ensuite recopier ces formules vers le bas comme habituellement.

 

[Ligeard]

Merci.

Est-il possible pour l'onglet "Total année" de mettre le nombre d'absences/le nombre de séances?

Je préfère la présentation sous forme de ratio.

 

[zeltron54]

Qu'appelles-tu nombre de séances ? dans tes différentes feuilles (onglets).

est-ce le nombre de dates entrées ou ??

 

[Ligeard]

En fait, ce sera le nombre de dates tapées pour un trimestre.
je crois que c'est: (NBVAL($C$1:$AC$1)

J'ai voulu utiliser une mise en forme conditionnelle: les élèves plus absents que présents, la case "nombre d'absences" apparaît en rouge, avec (NBVAL($C$1:$AC$1)/2

Mais ça ne fonctionne pas, toutes les cases sont et restent en rouges.

 

[zeltron54]

alors dans la feuille total année:
en C5: =TEXTE(NB.SI('Trimetre 1'!C5:AD5;"Abs");"##")&"/"&TEXTE(NBVAL('Trimetre 1'!$C$1:$AD$1);"##")

en D5: =TEXTE(NB.SI('Trimestre 2'!C5:AD5;"Abs");"##")&"/"&TEXTE(NBVAL('Trimestre 2'!$C$1:$AD$1);"##")

en E5: =TEXTE(NB.SI('Trimestre 3'!C5:AD5;"Abs");"##")&"/"&TEXTE(NBVAL('Trimestre 3'!$C$1:$AD$1);"##")

en F5: =TEXTE(NB.SI('Trimetre 1'!C5:AD5;"Abs")+NB.SI('Trimestre 2'!C5:AD5;"Abs")+NB.SI('Trimestre 3'!C5:AD5;"Abs");"##")&"/"&TEXTE(NBVAL('Trimetre 1'!$C$1:$AD$1)+NBVAL('Trimestre 2'!$C$1:$AD$1)+NBVAL('Trimestre 3'!$C$1:$AD$1);"##")

voila pour les formules permettant de mettre sous forme de fraction, mais cela est du texte, plus des nombres.

 

[Ligeard]

Les formules fonctionnent.
Super!

Par contre, pour la mise en forme conditionnelle et les sparklines?

 

[zeltron54]

Vérifie car chez moi tout fonctionne

en F5: =TEXTE(NB.SI('Trimetre 1'!C5:AD5;"Abs")+NB.SI('Trimestre 2'!C5:AD5;"Abs")+NB.SI('Trimestre 3'!C5:AD5;"Abs");"##")&"/"&TEXTE(NBVAL('Trimetre 1'!$C$1:$AD$1)+NBVAL('Trimestre 2'!$C$1:$AD$1)+NBVAL('Trimestre 3'!$C$1:$AD$1);"##")

---------- Nouveau message ajouté à 20h59 ---------- Le message précédent a été envoyé à 20h53 ----------

comme dit plus haut, pour les mises en formes et les graphiques, il faut travailler avec des nombres et là en a transformé tout en texte pour pouvoir afficher sous forme de fraction...

Alors je ne sais pas faire ! désolé... je vais regarder mais j'ai peut d'espoir.

 

[Ligeard]

Les formules fonctionnent, tu as répondu avant que je puisse éditer mon post, désolé.

Comment je peux connaître le nombre d'élèves d'une classe avec une formule?
En fait, le nombre est égale au nombre de cellules qui vont de C5 à C43 et sont remplies.

Mais, si je tape =NBVAL(C5:C43), ça ne va, puisque c'est du texte.
Et je maîtrise pas encore les formules avec du texte où je dois enlever les cellules vides.

---------- Nouveau message ajouté à 21h02 ---------- Le message précédent a été envoyé à 21h00 ----------

D'accord, j'étais justement en train de chercher sur le net cette partie sur le texte, je comprends mieux maintenant.
Je t'en ai déjà demandé beaucoup, tu m'as déjà beaucoup aidé.
Je ferai sans, ce n'est pas primordial, je ne veux que cela t'accapare.

---------- Nouveau message ajouté à 21h12 ---------- Le message précédent a été envoyé à 21h02 ----------

Pour les sparklines, je viens de réaliser quelques graphiques, et en fait, compte tenu de la hauteur des lignes, ce n'est pas exploitable.

Par contre, je reviens sur les formules d'hier sur le nombre d'absences (=TEXTE(NB.SI(C7:AD7;"Absent")+NB.SI(C7:AD7;"ABS");"##")&"/"&TEXTE(NB(C7:AD7)+NB.SI(C7:AD7;"Absent")+NB.SI(C7:AD7;"ABS");"##")) aux devoirs et nombre d'absences aux devoirs surveillés (=TEXTE(NB.SI(C7:AD7;"ABS");"##")&"/"&TEXTE(NB.SI($C$4:$AD$4;4);"##"))

Lorsqu'un élève n'est jamais absent, le résultat affiché est /8 par exemple.
Est-il possible d'obtenir 0/8 dans ces cas?

 

[zeltron54]

pour ton nombre d'élève ils sont de C5 à C41 et non C43.

soit 37 possibilités. la formule "NB.vide" donne le nombre de cellules vides
d'ou la formule à mettre dans la feuille Trimetre 1:

=37-NB.vide(B5:B41)

Lorsqu'un élève n'est jamais absent, le résultat affiché est /8 par exemple.
Est-il possible d'obtenir 0/8 dans ces cas?

pour mettre un 0 devant faut modifier le format "##" par "0#" pour 2 chiffres sinon "0" pour 1 seul chiffre.

 

[Ligeard]

Voilà, j'ai rectifié.

Une dernière formule, et après j'arrête, Excel est chronophage.

J'aimerais connaître le nombre total d'élèves absents par date, à la ligne 42?
Avec =37-NB.VIDE(B5:B41)?

Encore merci.

 

[zeltron54]

tout à fait :=37-NB.VIDE(C5:C41) cette formule recopiée sur la ligne 42 te donneras le nombre d'absent par date.

bon courage à toi !

 

[Ligeard]

Un dernier merci pour l'ensemble de l'aide apporté.

Il ne me reste plus qu'à les utiliser au quotidien, on verra pour les ajouts plus tard.

Bonne soirée, merci.

 

[Ligeard]

Je reviens sur ces fameux fichiers Excel.

Il n'est pas possible d'ajouter ou supprimer une ligne (départ d'un élève)?
J'ai essayé, et les formules ne fonctionnent pas dans ce cas pour la ligne insérer, et pour la ligne supprimer, les formules ne se mettent pas à jour.

Merci.

 

[Ligeard]

Après quelques mois d'utilisation, je suis tout à fait satisfait du tableau.

Toutefois, je souhaite ajouté quelques petits choses.
En fait, je cherche à insérer dans la feuille "Moyennes annuelles", des flèches dans les colonnes E et G montrant l'évolution de la moyenne d'un trimestre à l'autre.
J'ai tapé cette formule: =SI(F3=D3;"g";SI(D3>F3;"m";"k"))

Les soucis sont:
- Je n'arrive pas à mettre de a couleur: flèche verte pour augmentation, rouge pour baisse et orange pour même valeur.
- En cas d'absence à un trimestre, la moyenne est alors "A", et le logiciel met systématiquement que la moyenne a augmenté au trimestre suivant. Je préférerais que la cellule soit dans une quatrième couleur, pour distinguer cela.

Merci

 

[Aliboron]

- Je n'arrive pas à mettre de a couleur: flèche verte pour augmentation, rouge pour baisse et orange pour même valeur.
- En cas d'absence à un trimestre, la moyenne est alors "A", et le logiciel met systématiquement que la moyenne a augmenté au trimestre suivant. Je préférerais que la cellule soit dans une quatrième couleur, pour distinguer cela.

C'est typiquement un cas à résoudre par une "Mise en forme conditionnelle" (menu "Format"). Tu choisis la couleur de la police en fonction de la valeur de la cellule.