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