XL 2013 Dédoublonnage avec autres conditions

[sichir]

Bonjour à tous,

J'ai fait des recherches sur le forum et trouvé des réponses partielles à mon problème sans réussir à le mettre en oeuvre dans mon cas particulier.

Je cherche à calculer le nombre de sessions et de participants par trimestre et au cumul sur l'année pour différentes formations qui respectent d'autres conditions.
J'ai fait une formule avec un countifs (mon excel est en anglais) équivalent à un nb.ens je pense, mais je ne parviens pas à intégrer une condition sur le dédoublonnage des participants et des jours de formation.

Je joins un fichier avec un onlget CALENDRIER qui permet de suivre les jours de formation par session et un onglet SUIVI qui permet de suivre les inscriptions des participants. L'onglet "ONGLET 2" comporte les calculs et les résultats que je souhaiterai obtenir.

Merci par avance pour votre aide !

 

[CISCO]

Re : Dédoublonnage avec autres conditions

Bonjour

Et pourquoi veux tu avoir un 2 dans ONGLET 2!C3, alors que ta formule te donne 8 ?

@ plus

 

[sichir]

Re : Dédoublonnage avec autres conditions

Bonjour CISCO,

Parce qu'en fait en C4 dans l'onglet 2 je veux le nombre de sessions et non pas le nombre de lignes de l'onglet Calendrier.

En fait dans l'onglet Calendrier il y a bien 8 lignes mais ces lignes correspondent à des séances.
Pour une session il faut compter 4 ou 5 séances. Le numéro de la session à laquelle chaque séance correspond est indiqué dans l'onglet Calendrier en colonne F.

Donc en C4 je voudrais savoir combien de sessions j'ai eues à date.

Merci encore pour ton aide.

 

[CISCO]

Re : Dédoublonnage avec autres conditions

Bonjour

Je n'y arrive pas avec NB.SI.ENS donc j'ai fait avec un SOMMEPROD. Essaye avec

SOMMEPROD((SIERREUR(EQUIV(CALENDRIER!$F$2:$F$200;CALENDRIER!$F$2:$F$200;0)=LIGNE(CALENDRIER!$F$1:$F$199);0))*(CALENDRIER!$D$2:$D$200=$B3)*(CALENDRIER!$G$2:$G$200<=AUJOURDHUI())*(CALENDRIER!$A$2:$A$200="Méthodo")*(CALENDRIER!$E$2:$E$200="confirmé"))

en matriciel, donc à valider avec Ctrl+maj tempo+entrer.

@ plus

 

[sichir]

Re : Dédoublonnage avec autres conditions

Merci CISCO ca à l'air de bien marcher!

Peux-tu juste m'expliquer comment fonctionne cette partie du code ? SIERREUR(EQUIV(CALENDRIER!$F$2:$F$200;CALENDRIER!$F$2:$F$200;0)=LIGNE(CALENDRIER!$F$1:$F$199);0) ?

Sarah

 

[CISCO]

Re : Dédoublonnage avec autres conditions

Bonjour

Dans la colonne F, tu as une série de 1 et de 2, or seuls nous intéressent le premier de ces 1 et le premier de ces 2. Les autres ne doivent pas être pris en compte (puisque ce sont des doublons).
Par conséquent, en faisant EQUIV(CALENDRIER!$F$2:$F$200;CALENDRIER!$ F$2:$F$200;0), j'obtiens plusieurs fois la position du premier 1, et celle du premier 2.
En utilisant EQUIV(CALENDRIER!$F$2:$F$200;CALENDRIER!$ F$2:$F$200;0)=LIGNE(CALENDRIER!$F$1:$F$199) j'obtiens VRAI pour le premier 1 et le premier 2, FAUX pour les autres 1 et les autres 2. Malheureusement, j'obtiens aussi un message d'erreur #NA pour les cellules à partir de F11 qui sont toutes vides. Pour contourner ce dernier problème, j'utilise SIERREUR(.........;0).

La formule doit être validée en matriciel car sinon, EQUIV ne rechercherait que la position du contenu de CALENDRIER!$F$2, et pas celles de tous les contenus des cellules de la plage CALENDRIER!$F$2:$F$200 (de même pour LIGNE(CALENDRIER!$F$1:$F$199)).

On peut un peu simplifier cela en nommant une ou des plages dynamiques (c-à-d dont la hauteur s'adapte automatiquement à la hauteur de la plage contenant des valeurs).
Dans le fichier ci-joint, j'ai fait pour la colonne A avec Plan = DECALER(CALENDRIER!$A$2;;;EQUIV("zz";CALENDRIER!$A:$A;1)-1).
Si tu cliques sur cette définition dans le gestionnaire de noms, tu verras que la plage correspondante est mise en valeur grâce à des pointillés. Ainsi, on peut vérifier la validité de la définition donnée.

Je pourrai définir d'autres plages dynamiques pour les autres colonnes, mais comme ton gestionnaire de noms est déja bien occupé, j'ai préféré faire avec des DECALER, par exemple avec DECALER(Plan;;5) pour obtenir la plage CALENDRIER!$F$2:$F$10.

Il ni y a pas de cellules vides dans cette dernière plage, donc, plus de pb avec des #NA, et la formule peut être maintenant écrite en C3 sous la forme

SOMMEPROD((EQUIV(DECALER(Plan;;5);DECALER(Plan;;5);0)=LIGNE(Plan)-1)*(DECALER(Plan;;3)=$B3)*(DECALER(Plan;;6)<=AUJOURDHUI())*(Plan="Méthodo")*(DECALER(Plan;;4)="confirmé"))

, toujours en matriciel.

Cette formule fonctionne exactement comme ma dernière proposition dans mon dernier post, sauf qu'elle n'utilise que le haut des plages, la partie ne contenant que des cellules non vides.

@ plus

 

[sichir]

Re : Dédoublonnage avec autres conditions

Super c'est très clair!! Merci