Récap par Département et par type de formation

[KIM]

Bonjour les ami(e)s, Bonjour le forum,
Je reviens vers vous pour solliciter votre aide mais dans un nouveau domaine : la formation.
J'ai reçois une liste de demandes de formations avec le nom du particpant (plus de 1000), son département (DEP) et le choix des formations demandées ( un "x" dans la case correspondante). Cette liste n'est pas figée. Elle est complétée au fil des jours. J'ai besoin de calculer, d'une manière dynamique, par formules ou par vba,
- Tableau 1: par département (DEP) le nombre de participants par formation et le total par DEP et par formation
- Tableau 2: par département, le nombre de personnes ayant demandé 1, 2, ou plusieurs formations.
Voir tableau ci-joint et résultat souhaité dans la feuille DEP_Résultat
Je vous remercie d'avance
KIM

 

[sadia]

Re : Récap par Département et par type de formation

Bonjour,

j'ai pas tout compris mais voilà ce que j'ai fait...
j'espere que ca te conviendra

 

[KIM]

Re : Récap par Département et par type de formation

Bonjour Sadia et le forum,
Merci de ta réponse rapide, en effet j'ai la liste des demandes par dans l'onglet LISTE. Je souhaite générer automatiquement dans l'onglet DEP un récapitulatif des demandes de formation par DEP et par formation et aussi le nombre de personnes qui ont demandées plusieurs formation en même temps voir le résulta souhaité dans onglet DEP_Résultat.
Dans l'onglet DEP, Col A = liste unique des DEP à partir de la ligne 6, Ligne 5 de col B à Col G = liste des formations, Col H c'est le total à calculer; Ligne 5 col I à M c'est le nombre de demandes simultanées par personne, de 1 à 5, etc.
Dans les autres lignes c'est le nombre de participants correspondant selon les "x" trouvés dans l'onglet LISTE.
Est-ce possible de construire ces 2 tableaux?
Merci d'avance
KIM

 

[sadia]

Re : Récap par Département et par type de formation

je ne comprends tjs pas, je suis desolee pour moi c'est les memes infos que tu trouves sur les deux onglets, non ????

je t'ai mis les formules sur l'onglet DEP_Résultat, est ce que ca te convient ?
est ce que je me suis trompée d'onglet ?

@ demain !!!

 

[KIM]

Re : Récap par Département et par type de formation

Bonsoir Sadia et le forum,
Merci bien,
J'ai mis l'onglet DEP_Resultat pour controler les formules à mettre dans l'onglet DEP. Tes formules dans l'onglet DEP_Resultat correspondent bien à ma demande. Je vais demain appliquer tes formules à mes données réelles et je me permets de revenir vers ce fil si je trouve des anomalies.
Bonne soirée et merci encore
KIM

 

[david84]

Re : Récap par Département et par type de formation

Bonsoir,
pour compléter le travail de sadia, ci joint une version comportant une formule permettant le classement sans doublons de la 1ère colonne du tableau liste.
J'ai également légèrement retouché l'un des 2 sommeprod.
A+

 

[KIM]

Re : Récap par Département et par type de formation

Bonjour David, bonjour Sadia et le forum,

Effectivement il me manquait la creation dynamique de la col A : liste des DEP. Je comptais la creer manuellement mais maintenant c'est chose faite. également la petite modif de la 2ième sommeprod est pratique.

merci encore à vous deux et au Forum.
Bonne journée
KIM

 

[david84]

Re : Récap par Département et par type de formation

Re
Afin d'avoir une plage dynamique de cellules, je te conseille de nommer tes plages. Tu gagneras en lisibilité et en vitesse de traitement. Pour cela, insertion=>nom=>définir sur les versions antérieures à 2007 (enfin je crois car je ne les connais pas) ou sur 2007 :formules=>définir un nom.
- Nomme ta plage et utilise la fonction décaler. Exemple pour ta colonne DEP :
la nommer : DEP par ex (le fait de te placer directement en A5 avant d'ouvrir la boîte de dialogue te propose le nom placé en A5),
- dans "fait référence à" : =decaler(Liste!$A$5;1;;nbval(Liste!$A:$A)-1)
- Clic dans "nom" puis à nouveau dans "fait référence à" et tu verras ta plage sélectionnée.
Si tu ajoute des noms à la suite t(sans laisser de vide bien sûr), ta plage les prendras automatiquement en compte.
- Reproduis l'opération dans tes autres plages
- dans tes formules, surligne chaque plage et remplace la par le nom de la plage : pour ce faire, appuie sur la touche F3 du clavier et sélectionne le nom de la plage choisie).
Ainsi, la formule permettant d'obtenir ta plage sans doublons deviendra :

=SI(LIGNES($1:1)<=SOMME(1/NB.SI(DEP;DEP));INDEX(DEP;EQUIV(MIN(SI(NB.SI(A$5:A5;DEP)=0;NB.SI(DEP;"<"&DEP)));SI(NB.SI(A$5:A5;DEP)=0;NB.SI(DEP;"<"&DEP));0));"")

C'est une formule matricielle qu'il faut donc valider par Ctrl,, Maj et entrée.
A+

 

[Monique]

Re : Récap par Département et par type de formation

Bonjour,

Une suite.
Ce que j’aurais voulu : que l’on puisse se passer de la colonne de totaux de l’onglet « Liste ». Avec Frequence(), ça doit être possible.

Le fichier joint : tous les tableaux ont un format conditionnel, dans le 2ème onglet, les totaux sont en ligne 1 (ce serait bien de mettre le 2ème tableau à gauche du 1er pour que le 1er puisse évoluer vers la droite)

 

[KIM]

Re : Récap par Département et par type de formation

Bonjour Monique, Sadia et David, Bonjour le Forum,
@Monique
Merci de m'avoir rendu le fichier dynamique. experte en la matière je vais suivre ta proposition. En effet j'étais entrain de créer ton dernier tableau col AB-AH à la main. Je ne pensais pas que c'était faisable par formules. j'allais lancer un appel d'aide aux spécialistes vba. Parcontre il me manque une information qui est la DEP de chaque nom dans ce tableau. Est-il possible de concatener la DEP au nom de la personne dans ce tableau (col AB-AH)? par ex DP14-nom1 ; ou avoir 2 colonnes par formation, une pur la DEP et l'autre pour le nom?
merci d'avance
@David, Merci pour tes conseils, Vu le manque du temps, Monique m'a bien dépanné et complété par d'autres informations attendues.
@Sadia, Merci d'avoir répondu rapidement et avec une solution qui marche et qui m'a permis d'avancer dans mon travail.
Cordialement
KIM

 

[Monique]

Re : Récap par Département et par type de formation

Re,
C'est fait (concaténation, pas deux colonnes)

 

[KIM]

Re : Récap par Département et par type de formation

Re,
Merci Monique, La concaténation me va très bien.
Par curiosité, est-il possible de trouver une formule non matricielle pour construire cette liste de noms par formation?

Merci encore
KIM

 

[Monique]

Re : Récap par Département et par type de formation

Re,
Oui mais ça ne vaut pas le coup.
Il faudrait insérer une colonne supp par formation,
mettre une formule donnant un n° à chaque "x"
Puis Index(Nom;Petite.valeur(plage de numéros;1 puis 2 puis etc)

 

[KIM]

Re : Récap par Département et par type de formation

Re,
Merci encore Monique, Je vais appliquer tes formules à mon classeur de données.
Merci le fil & le Forum

Bonne journée
KIM

 

[KIM]

Re : Récap par Département et par type de formation

Re, le fil et le forum,
@Monique,
Je reviens vers toi car je n'ai pas bien compris la formule pour la liste des noms. Mon fichier de travail n'a pas le même format et j'ai du mal à créer la liste des personnes par DeP et par formation. Peux-tu stp m'expliquer la formule et surtout les paramètres de LIGNE?
Merci d'avance
KIM