XL 2016 Liaison entre tableau

[loris00]

Bonjour,
Je réalise un fixer Excel qui va me permettre de déterminer le nombre de personnes formés par équipes.
Je m'explique : je suis en entreprise et il existe plusieurs équipes au sein de cette entreprise. les personnes de ces équipes ont besoin d'être formés pour tel ou tel compétences. Pour cela les personnes des équipes suivent des formations (plusieurs formations différentes par équipes) . Je dois déterminer pour toutes les formations de chaque équipe, le nombre de personnes ayant suivi la formation ET faisant parti de l'équipe.
Pour cela il m'a été fourni 2 tableau :
- 1er tableau : Historique des formations où l'on trouve le nom des personnes avec les formations qu'elles ont suivis (ce tableau comprend toutes les formations passées et certaines personnes ne font plus partit de l'effectif).

- 2eme tableau : Effectif où l'on trouve la liste des personnes (nom, prénom, matricule) par équipe. la données équipe est donné par trois colonnes ORG, au total 66 équipes

- un 3ème tableau facultatif avec la liste des équipes.

Je dois ainsi remplir la colonne "Nombre de personnes ayant suivi la formation et faisant parti de cette l'équipe" de mon tableau final. Sur ce tableau il y a les équipes et les formations qui les concernent.


Pour cela une solution s'offre a moi en plusieurs parti
1 --> supprimer toutes les lignes du tableau "Historique" pour lesquelles le code de formation ne se trouve pas dans mon "tableau final".
2 --> supprimer aussi toutes les lignes de "Historique" pour lesquelles les numéro de matricule ne se trouvent pas dans mon tableau "Effectif".
3 --> intégrer ensuite a mon tableau "Historique" l'équipe d'appartenance de toutes les personnes.
4 --> Compter le nombre de fois ou le stage à été effectué par équipe

Pour ces étapes je ne peux pas les faire a la main car mon tableau "historique" fait 250000 lignes (que 20000 dans l'exemple donné)

Merci de m'aider je suis vraiment en difficulté se serait vraiment gentil. Si vous voyez une autre solution je suis preneur car je ne pense pas que passer par les étapes de surpression soit très bien. si vous connaissez des fonction qui font cela ou même faire cela VBA serait plus simple mais j'aurai besoin dans ce cas là d'explications

PS: dans le fichier on y trouve la liste des formations qui ont changé de nom ainsi que leur dates, merci de ne pas les prendre en compte pour le moment.

 

[Dudu2]

Bonjour,
Une formule, peut-être possible (?) me parait assez compliqué.
Ce serait beaucoup plus facile si tu reportais les 3 éléments de l'équipe dans la feuille 'Historique des formations' avec un INDEX/EQUIV pour qu'elle soit valorisée automatiquement.
Et si tu utilisais la Matricule au lieu du Nom Prénom dans cette même feuille.

 

[Dudu2]

En fait j'ai pu faire un EQUIV sur 2 colonnes donc on peut se passer du Matricule et utiliser le Nom et Prénom pour ajouter les 3 éléments de l'équipe dans la feuille 'Historique des formations' avec une petite formule matricielle. En cas de recalcul sur 22000 ligne c'est un peu long !

Il vaudrait mieux que tu valorises ces informations lors de la construction de la liste, surtout que si une personne change d'équipe après la formation, tu comptabiliseras la formation sur la nouvelle équipe (celle apparaisant dans l'effectif) alors qu'il l'aura subie avec son ancienne équipe.

Le tableau que tu as fourni est assez mal en point car pour vérifier que la formule pour le total que tu recherches était juste il a fallu que je rajoute une ligne donnant un chiffre <> 0 (vérifié par des filtres sur l'historique), car tous les compteurs sont à zéro dans ce tableau sans doute incomplet.

Fichier trop volumineux pour excel-download ! -> (pour 4 jours)

Edit: Concernant le nombre de lignes de l'historique (en dur à x3:x22000 dans les formules) ou celui de l'effectif (en dur x3:x1200 dans les formules), si tu veux le rendre plus adaptatif, tu peux soit utiliser un INDIRECT ou un DECALER sur le NBVAL. Ou bien tu utilises des tableaux Excel (mettre sous forme de tableau) qui offrent des noms pour désigner les colonnes entières.

 

[Dudu2]

Une autre (bien meilleure) option serait de réorganiser tout ça sous forme de TCD. Mais là il faut une expertise que je n'ai pas forcément sauf à cogiter sur le truc.
Edit: Une 3ème option serait peut-être de définir des relations entre les tables pour s'approcher d'un modèle relationnel permettant de générer les tables souhaitées (?).

 

[loris00]

Une autre (bien meilleure) option serait de réorganiser tout ça sous forme de TCD. Mais là il faut une expertise que je n'ai pas forcément sauf à cogiter sur le truc.
Edit: Une 3ème option serait peut-être de définir des relations entre les tables pour s'approcher d'un modèle relationnel permettant de générer les tables souhaitées (?).

Merci beaucoup t’es explications m’ont été d’une grande aide je t’en suis très reconnaissant