Trouver un nom dans un tableau à 2 entrées

[Fredgyver]

J'ai cherché partout dans les archives du Forum, j'ai décortiqué plein d'exemples dans lesquels j'ai appris à manier INDEX et EQUIV, mais je n'arrive pas à trouver un NOM d'élève dans un tableau qui contient tous les noms d'élève rangés par classe ...
Ci-joint un fichier simplifié, merci d'avance,
Fred. [file name=Frequentation.zip size=16987]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/Frequentation.zip[/file]

 

[Monique]

Bonjour,

Tu n'as plus besoin de la colonne E
J'ai mis NB.SI() à la place de SommeProd(),
NB.SI() ne tient pas compte des cellules vides
et utilise moins d'énergie.
Par contre, si tu avais des valeurs numériques dans les catégories,
NB.SI te donne ex aequo le plus grand des nombres et la 1ère des lettres.

J'ai aussi modifié la formule matricielle :
MIN() suivi de '' à la place de Petite.Valeur()
C'est plus court
[file name=TriListeClaude.zip size=20634]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/TriListeClaude.zip[/file]

 

[Claude]

Re Monique,

Merci pour tes explications,je vais faire les modifications.

Claude

 

[Fredgyver]

Monique écrit:

Une variante.
Dans la feuille 'Listes',
une formule moins longue qui n'a pas besoin d'être mise au conditionnel.

Oui mais il il y a un problème, Monique, regarde l'intitulé des colonnes dans l'onglet Listes, toutes les classes sont devenues CAP MVA 1 ...

Sinon c'est super ! Bien mieux que je n'avais espéré ...

Encore merci à tous !

 

[Monique]

Bonjour,

Trouvé ?
Si tu ne valides pas par ctrl, maj et entrée la formule de B1:AB1 de la feuille 'Listes',
en effet, la même classe est répétée 36 fois.
C'est ça ?
(il n'y a que les formules nommées qui n'ont pas besoin d'être validées en matriciel)

Message édité par: monique, à: 12/11/2005 19:32

 

[Fredgyver]

Monique écrit:

Trouvé ?

Ben, non ...!

Si tu ne valides pas par ctrl, maj et entrée la formule de B1:AB1 de la feuille 'Listes',
en effet, la même classe est répétée 36 fois.
C'est ça ?

J'ai essayé de faire ça, mais rien à faire !
Et comme je suis complètement dépassé par la formule, je suis bien incapable de dire où est le problème. La seule chose auquel je pense est que je suis sur Mac sous OS X, mais jusque là, pas de pb !

En-effet, le même fichier sous Windows fonctionne parfaitement ... bizarre !

Message édité par: Fredgyver, à: 15/11/2005 16:56

 

[Monique]

Bonjour,

Cellule B1 de la feuille 'Listes'
=INDEX(ColForm;MIN(SI(NB.SI($A1:A1;Listform)=0;LIGNE(Listform))))&''

Une fois validée en matriciel,
(en appuyant simultanément sur les 3 touches ctrl, maj et entrée),
1 crochet se met de chaque côté de la formule,
ça se voit dans la barre de formule :
{=INDEX(ColForm;MIN(SI(NB.SI($A1:A1;Listform)=0;LIGNE(Listform))))&''}

Il ne faut pas saisir les crochets, Excel fait ça tout seul.

Tentative d'explication
=INDEX(ColForm;n° de ligne; pas besoin du n° de colonne)

Si n° de ligne est 5, la formule te renvoie le contenu de la ligne 5 de la plage 'ColForm'

Ce n° de ligne est obtenu par Min(Si(Condition1;Ligne(ListForm)))
Cond 1 et unique : que NB.SI($A1:A1;ListForm)=0
La formule ne prendra pas les classes déjà renvoyées dans la plage de gauche.
Si cette condition est satisfaite, la formule Min(Si(etc))) renvoie le n° de ligne le plus petit de la plage 'ListForm'
Et ça donne :
=INDEX(ColForm;n° de ligne de ListForm)

Le &'' de la fin de le formule
Un fois que tous les n° de ligne sont épuisés, la formule Min(Si(etc)) renvoie 0
=INDEX(ColForm;0) renvoie le contenu de la ligne 1 de la plage ColForm
Si cette cellule est vide, on obtient quand même 0
Mais =INDEX(ColForm;0)&'' renvoie '' càd rien
Et c'est la raison du 'pas besoin de conditionnel'

Idem pour la formule en B2 et sur tout le tableau,
sauf qu'il y a cette fois 2 conditions
=INDEX(ColNom;MIN(SI(Listform=B$1;SI(NB.SI(B$1:B1;Listnom)=0;LIGNE(Listnom)))))&''

 

[Fredgyver]

Merci Mnique pour toutes ces explications ...
Bonjour à Gael et à tous les autres !

Je travaille dessus depuis hier, et je commence à comprendre la logique de ces formules ...

Encore merci :silly:
Fred

Message édité par: Fredgyver, à: 15/11/2005 16:52

 

[Fredgyver]

Gael,

j'ai besoin de tes lumières sur ta formule Classes :

=DECALER(Listes!$B$1;;;;SOMME(1/NB.SI(Listform;Listform)))

Je l'ai décomposée en sous-formules, mais je ne comprend pas comment elle fonctionne !

NB.SI(Listform;Listform) est censé renvoyer le nombre de sections ?
SOMME(1/NB.SI(Listform;Listform)) Pourquoi cette opération ?

Merci d'avance,
Fred

 

[Gael]

Bonsoir Fred, Bonsoir Monique,

Fred, en fait c'est dans l'exemple de Monique que cette fonction est utilisée, mais je peux te l'expliquer quand même.

L'idée est de définir la liste des formations à partir de la feuille Elèves donc de déterminer combien y-a-t-il de formations différentes.

La fonction Decaler part de la première cellule contenant une formation (B1) et sur un nombre de colonnes égal au nombre de formations.

=Somme(1/NB.SI(plage;plage) est une formule connue qui permet de définir le nombre de valeurs différentes dans une plage.

* NB.SI(plage;plage) va compter pour chaque valeur de la plage, le nombre de fois où elle est présente dans cette même plage. Si une valeur est présente 4 fois, chaque fois que la valeur se présente NB.SI va compter 4.

Exemple: soit la liste A,B,A,C,B,A,A

NB.SI(plage;plage) donnera comme résultat:

4,2,4,1,2,4,4

1/NB.SI(plage;plage) va calculer 1/nombre de présence ce qui donnera:

1/4,1/2,1/4,1/1,1/2,1/4,1/4 donc si une valeur est présente 4 fois, on trouvera 4 fois 1/4 et pour n fois, n fois 1/n

La somme de n fois 1/n étant égal à 1, Somme(1/NB.SI....) va compter 1 pour chaque valeur présente n fois dans la plage ce qui donne le nombre de valeurs différentes.

Ce calcul pose quelquefois des problèmes d'arrondis car si 4 * 0,25 donne bien 1, si une valeur est présente 7 fois, on aura 7 * 0,142857142 ce qui donne à peu près 1 (0,999999994). et c'est pareil pour 3 fois, 11 fois ...

J'espère avoir été à peu près clair, mais n'hésite pas à demander d'autres explications complémentaires si c'est nécessaire.

J'ajoute un fichier exemple à partir de la feuille elèves ou chaque étape du calcul est détaillée séparément, c'est la meilleure façon de comprendre.


@+

Gael [file name=Formule.zip size=17529]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/Formule.zip[/file]

Message édité par: Gael, à: 16/11/2005 20:54

Message édité par: Gael, à: 16/11/2005 22:42

 

[Fredgyver]

Merci pour l'explication sur =Somme(1/NB.SI(plage;plage) c'était très pédagogique ! Heureusement que tu avais joint cette feuille de calcul, j'aurais tourné en rond encore un bon moment sinon !

Il s'agit de toute évidence d'une astuce pour palier à une lacune d'Excel, n'est-ce-pas !?

 

[Gael]

Bonjour Fred, Bonjour Monique,

Je suis content que tu ais compris mes explications, car ce n'est pas très simple à décrire.

Effectivement, il n'y a pas sur Excel de fonction toute faite pour déterminer le nombre de valeurs différentes dans une plage. Cela existe dans des macros complémentaires comme Morefun qui contient plein de fonctions intéressantes dont NB.DIFF mais par souci de standardisation, on préfère souvent travailler avec les fonctions de base d'Excel.

Il y a 2 formules classiques en fait qui peuvent faire ce calcul:

{=SOMME(1/NB.SI(Plage;Plage))}

{=SOMME(N(FREQUENCE(Plage;Plage)>0))} (fonctionne uniquement avec des nombres)

Je t'encourage quand même à télécharger Morefun, à l'adresse suivante:


Ce lien n'existe plus

@+

Gael

Message édité par: Gael, à: 17/11/2005 15:17

 

[Fredgyver]

Voici une copie de ma feuille Fréquentation, 4e version, avec des explications en zones de texte, pour que le plus grand en bénéficie !

Encore tous mes remerciements à ceux qui m'ont aidés ...

Je reste à disposition des membres du forum intéressés pour plus d'explications ...

Codialement,
Fred [file name=Frequentation_V4_Demo.zip size=50816]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/Frequentation_V4_Demo.zip[/file]