Discussion :

[Sylcoco]

Bonjour,

Dans le fichier joint de r�sultats sportif je recherche les infos individuelles d'un joueur pour composer une �quipe de son club.
Je connais un peu la RECHERCHEV mais je bloque pour combiner LIGNE , EQUIV et DECALER.

Merci de votre aide.

Sylvain

[78chris]

Bonjour

En I2 � �tirer sur 4 cellules

Code :

S�lectionner tout - Visualiser dans une fen�tre � part

=SIERREUR(DECALER($B$1;EQUIV($G2&$H2;$A$2:$A$100&$C$2:$C$100;0)+COLONNE(A1)-1;0);"")

En M2 � �tirer sur 2 cellules

Code :

S�lectionner tout - Visualiser dans une fen�tre � part

=SIERREUR(SOMME.SI.ENS(DECALER(D$1;EQUIV($G2&$H2;$A$2:$A$100&$C$2:$C$100;0);0;NB.SI.ENS($A$2:$A$100;$G2;$C$2:$C$100;$H2));DECALER(D$1;EQUIV($G2&$H2;$A$2:$A$100&$C$2:$C$100;0);0;NB.SI.ENS($A$2:$A$100;$G2;$C$2:$C$100;$H2));">="&GRANDE.VALEUR(DECALER(D$1;EQUIV($G2&$H2;$A$2:$A$100&$C$2:$C$100;0);0;NB.SI.ENS($A$2:$A$100;$G2;$C$2:$C$100;$H2));3));0)

On peut simplifier la 2�me formule avec 365

Code :

S�lectionner tout - Visualiser dans une fen�tre � part

=SIERREUR(LET(Equip;DECALER(D$1;EQUIV($G2&$H2;$A$2:$A$100&$C$2:$C$100;0);0;NB.SI.ENS($A$2:$A$100;$G2;$C$2:$C$100;$H2));SOMME.SI.ENS(Equip;Equip;">="&GRANDE.VALEUR(Equip;3)));0)

ou sinon une formule nomm�e Equip au lieu du LET

[Raccourcix]

Bonjour

Avec une version disposant des fonctions FILTRE et PRENDRE

en I2 avec propagation automatique sur 4 colonnes maximum d'o� le PRENDRE ;4 si jamais 5 joueurs ou plus dans une m�me �quipe

Code :

S�lectionner tout - Visualiser dans une fen�tre � part

=TRANSPOSE(PRENDRE(FILTRE($B$2:$B$100;($A$2:$A$100=G2)*($C$2:$C$100=H2);"");4))

et pour la somme des 3 premiers tours en M2 et N2

Code :

S�lectionner tout - Visualiser dans une fen�tre � part

=SOMME(PRENDRE(FILTRE(D$2:D$100;($A$2:$A$100=$G2)*($C$2:$C100=$H2);0);3))

St�phane

[Raccourcix]

En compl�ment, si vous avez la fonction GROUPER.PAR vous pouvez faire une synth�se par club et par �quipe de la somme des 3 premiers tours

Code :

S�lectionner tout - Visualiser dans une fen�tre � part

=GROUPER.PAR(CHOISIRCOLS($A$1:$C$100;1;3);D$1:E$100;LAMBDA(x;SOMME(PRENDRE(x;3)));3;0;{1;2};$A$1:$A$100<>"")

CHOISIRCOLS($A$1:$C$100;1;3) pour regrouper par les colonnes 1 puis 3
D$1:E$100 pour s�lectionner les colonnes de valeurs
LAMBDA(x;SOMME(PRENDRE(x;3))) pour additionner les 3 premi�res valeurs seulement
3 pour afficher l'en-t�te
0 pour ne pas afficher la ligne total
{1;2} pour trier les colonnes 1 et 2 dans l'ordre croissant (club puis �quipe) - {-2;-1} pour les �quipes en ordre d�croissant puis les clubs �galement en ordre d�croissant - ne rien mettre pour conserver l'ordre des donn�es
$A$1:$A$100<>"" pour n'afficher que les clubs non vide (utile comme que les donn�es ne sont pas au format tableau)

Cette nouvelle fonction est bluffante. avec un TCD normal il aurait �t� complexe voir impossible de faire la somme des 3 premi�res valeurs

St�phane

[78chris]

RE

et pour la somme des 3 premiers tours en M2 et N2

Code :

S�lectionner tout - Visualiser dans une fen�tre � part

=SOMME(PRENDRE(FILTRE(D$2:D$100;($A$2:$A$100=$G2)*($C$2:$C100=$H2);0);3))

St�phane

C'est les 3 plus grands scores qui sont demand�s, donc

Code :

S�lectionner tout - Visualiser dans une fen�tre � part

=SOMME(PRENDRE(TRIER(FILTRE(D$2:D$100;($A$2:$A$100=$G2)*($C$2:$C100=$H2);0);1;-1);3))

Je laisse le soin � Raccourcix de proposer le =GROUPER.PAR pour ce classement...

[Raccourcix]

Merci Chris de la pr�cision

il faut modifier le LAMBDA en ajoutant le TRIER ou utiliser GRANDE.VALEUR et SEQUENCE

Code :

S�lectionner tout - Visualiser dans une fen�tre � part

1
2
3
LAMBDA(x;SOMME(PRENDRE(TRIER(x;;-1);3))) 
ou
LAMBDA(x;SOMME(GRANDE.VALEUR(x;SEQUENCE(3))))

La nouvelle fonction PIVOTER.PAR est �galement int�ressante :

Code :

S�lectionner tout - Visualiser dans une fen�tre � part

=PIVOTER.PAR($A$1:$A$100;$C$1:C$100;D$1:E$100;LAMBDA(x;SOMME(PRENDRE(TRIER(x;;-1);3)));3;0;1;0;1;$A$1:$A$100<>"")

pour obtenir un tableau dynamique avec les clubs en ligne, les �quipes en colonnes et la somme des 3 plus grandes valeurs pour les Tour1 et Tour2

St�phane

[Sylcoco]

Messieurs bonjour,

Merci beaucoup pour vos r�ponses.
Je d�crypte tout �a.

Merci,

Sylvain

[Sylcoco]

Messieurs,

Merci encore pour vos r�ponses.
Cela fonctionne parfaitement.

Je vais prendre le temps d'analyser.

Sylvain

[Sylcoco]

Bonjour,

D�sol� mais lorsque seulement 3 noms ont un n� d'�quipe les cellules de joueur1 � joueur4 sont compl�t�es alors qu'ils ne sont que trois.
Pour info il est possible qu'il y ait plusieurs joueurs d'un m�me club sans qu'ils soient forc�ment en �quipe, et les �quipes peuvent �tre compos�es de 3 ou 4 joueurs.

De m�me pour les scores :
- si 4 joueurs dans l'�quipe : sont pris les 3 plus grands scores des 4 joueurs.
- si 3 joueurs dans l'�quipe : sont pris les scores des joueurs de ces 3 joueurs.

Merci

Sylvain

[Raccourcix]

Bonjour

les formules propos�es le 14/08 fonctionnent

Pour la colonne I (ne rien �crire en colonnes J, K et L, elles se remplissent directement � partir de la colonne I)
en I2

Code :

S�lectionner tout - Visualiser dans une fen�tre � part

=TRANSPOSE(PRENDRE(FILTRE($B$2:$B$100;($A$2:$A$100=G2)*($C$2:$C$100=H2);"");4))

et pour les colonnes M et N
ici M2

Code :

S�lectionner tout - Visualiser dans une fen�tre � part

=SOMME(PRENDRE(TRIER(FILTRE(D$2:D$100;($A$2:$A$100=$G2)*($C$2:$C$100=$H2);0);1;-1);3))

St�phane

[Sylcoco]

St�phane bonjour,

Effectivement je confirme que les formules propos�es le 14/08 fonctionnent parfaitement.
L'erreur vient de moi, je pense n'avoir pas tr�s bien formul� ma demande.

Merci pour la mise � jour.

Sylvain

[modus57]

Bonjour @ tous,

Voici le classeur modifi� suivant les solutions de Raccourcix avec un tableau structur� pour les donn�es, le nombre de joueurs variable et le nombre de lignes variables suivant le nombre de lignes du tableau.

� toutes fins utiles.

Sinc�res salutations.

[Sylcoco]

Bonjour,

Merci pour ce tableau.

Sylvain