XL 2019 Répartition du sexe dans une équipe avec SommeProd

[carlos]

Bonjour,
Dans le fichier joint j'ai créé une macro qui me permet de connaitre la répartition des sexes pour chaque équipe.
J'aimerais créer une formule avec sommeprod qui fasse la meme chose et qui m'éviterait ainsi de devoir cliquer sur le bouton MAJ à chaque changement.
Bonne journée

 

[Dudu2]

Bonjour,
C'est quoi une équipe ?
10A ? 10 ? A ?

 

[carlos]

Oui Pardon
C'est du relais 4 x 50 mètres.
Dans chaque équipe, il y a 4 places A B C et D
1A c'est l'équipe 1 place A
1B c'est l'équipe 1 place B
Dans mon exemple il faut reconnaitre chaque équipe et associer le sexe. La place n'est pas importante.

 

[Dudu2]

La formule en I7 (à étendre) qui suppose qu'il n'y a pas de "trou" dans la liste des équipes en colonne J est:
=SIERREUR(SOMMEPROD((STXT(DECALER(J$7;0;0;NBVAL(J$7:J$1000));1;NBCAR(DECALER(J$7;0;0;NBVAL(J$7:J$1000)))-1)=STXT(J7;1;NBCAR(J7)-1))*(DECALER(C$7;0;0;NBVAL(J$7:J$1000))="F")) & "F" &
SOMMEPROD((STXT(DECALER(J$7;0;0;NBVAL(J$7:J$1000));1;NBCAR(DECALER(J$7;0;0;NBVAL(J$7:J$1000)))-1)=STXT(J7;1;NBCAR(J7)-1))*(DECALER(C$7;0;0;NBVAL(J$7:J$1000))="M")) & "M";"")

 

[Dudu2]

La formule est rendue quelque peu illisible à cause des DECALER nécessaires pour déterminer la hauteur de la colonne J.

Sinon, la formule de base si on considère qu'il n'y a que J7:J47 est plus facile à lire:
=SOMMEPROD((STXT(J$7:J$47;1;NBCAR(J$7:J$47)-1)=STXT(J7;1;NBCAR(J7)-1))*(C$7:C$47="F")) & "F" &
SOMMEPROD((STXT(J$7:J$47;1;NBCAR(J$7:J$47)-1)=STXT(J7;1;NBCAR(J7)-1))*(C$7:C$47="M")) & "M"

Ce sont donc les J$7:J$47 qui ont été remplacés par les DECALER(J$7;0;0;NBVAL(J$7:J$1000)) qui permettent de rendre la formule indépendante de la hauteur de la colonne J. Et le C$7:C$47 sur le même principe.

Le SIERREUR() évite les #VALEUR! si l'équipe est vide sur les STXT().

 

[carlos]

Bonjour Dudu2,
Super je ne savais pas que c'était possible de faire cela avec des formules.
Ta formule marche très bien sur la plage J$7:J$47 comme tu le dis mais j'aurai besoin d'une formule à hauteur variable en colonne J avec des trous parfois. J'ai essayé d'intégrer le DECALER(J$7;0;0;NBVAL(J$7:J$100)) à la place du J$7:J$47 mais ca me met un message d'erreur . je ne sais pas si cela est possible.
En tout cas merci beaucoup

 

[Dudu2]

La formule est en vert dans le Post #4. Corrigée après la 1ère publication car contenait des typos (je suis sur mon laptop, petit clavier, pas numérique).

 

[carlos]

Merci j'ai corrigé . Cela me mets "" si il y a une erreur.
Il n'y a pas de solution possible en cas de trou alors?

 

[carlos]

Avec un sommeprd ce n'est pas possible?

 

[Dudu2]

Ce serait plus simple si tu séparais le n° d'équipe du couloir.
On n'aurait pas à faire des STXT qui plantent si l'équipe-couloir est vide et qui obligent à ne traiter que les équipes-couloir existantes et donc à en déterminer le nombre.

 

[Dudu2]

Par exemple comme dans ce fichier...

 

[carlos]

Oui c'est bien cela, on y est presque.
Il faut juste régler le problème de l'équipe 1 (Voir dans le fichier joint)

 

[carlos]

Je pense qu'il faudrait ajouter à sommeprod la condition suivante dans la comparaison: (Nbcar(J7) = Nbcar(J$7:J$1000) mais je ne sais pas faire.

 

[carlos]

Re Dudu2,
j'ai intégré ta formule en séparant le numéro de l'équipe et la place dans l'équipe.
Ca marche super bien.
Merci beaucoup

 

[mapomme]

Bonsoir à tous,

Une autre:

=NB.SI.ENS(J$7:J$53;"><";J$7:J$53;GAUCHE(J7;NBCAR(J7)-1)&"?";C$7:C$53;"F")&"F"&NB.SI.ENS(J$7:J$53;"><";J$7:J$53;GAUCHE(J7;NBCAR(J7)-1)&"?";C$7:C$53;"M")&"M"