Tableau croisé dynamique faisant référence à un autre TCD (efficace)
Bonjour,
Je suis nouveau sur ce forum et par ce présent je tiens à remercier déjà tous les partenaires de celui-ci.
Je vous expose mon problème ou ma recherche de meilleur solution
Dans une base de donnée j'ai l'activité de chaque collaborateur. Ils font plusieurs opérations par jour .
Je souhaite faire un bilan annuel sur le nombre de colis et le poids journalier manipulé par chacun.
Pour ce faire, j'ai fait un premier tcd qui me regroupe les quantités par jour pour chaque collaborateur ( tcd jour ).
ensuite j'ai refait un tcd avec comme référence le tableau ( tcd jour) , en fait j'ai repris les cellules de ce tableau pour en faire un tableau annuel ( tcd _ann ) avec le nb de jour travaillé par personnes.
ensuite je refait un troisième tableau pour avoir le calcul de ma moyenne. (tcd _ final).
Je trouve ma méthode très basique , d'autant que je dois sélectionner chaque " base de donnée manuellement".
je vous sollicite donc pour avoir un avis sur ma méthode mais aussi pour avoir une proposition plus rapide et qui se met à jour directement.
( comment faire reference à un tcd pour en creer un autre )
cordialement
Bonjour,
Pour actualiser vos TCD faites ceci :
- click droite sur l'onglet TCD
- choisir l'option "Visualiser le code"
- Coller le code ci-dessous dans la fenêtre
Private Sub Worksheet_Activate()
ThisWorkbook.RefreshAll
End Sub- Enregistrez votre fichier au format XLSM
Dans votre feuille 1, vous pourriez mettre votre tableau en format structuré avec l'icone "Mise sous forme de tableau".
Cela va vous créer un nom dans le gestionnaire de noms ("Tableau1" probablement) qui pourra être utilisé dans la définition de la plage du 1er TCD (à la place de feuil1!A3:H9056).
Pour le deuxième TCD, faites un copier-coller du premier TCD puis organisez vos champs TCD
Pour le troisième TCD, cela parait OK
Grâce à votre nom Tableau1, la plage reprise pour le(s) TCD se mettra à jour automatiquement en fonction de vos nouvelles données.
Attention à vos années en feuil1 qui sont au format Texte
Cela suppose que vous avez besoin des 3 TCD bien sûr
Cordialement
Bonjour à tous
Ajout d'une colonne à la source afin de dédoublonner les dates
Puis un TCD avec 2 champs calculés pour les moyennes
Je n'ai pas compris s'il fallait se limiter aux moyennes ou non : j'ai mis les 2 versions de TCD
En installant l'add on PowerQuery (intégré à Excel à partir de 2016) on pourrait ne pas ajouter la colonne Unicité à la source ni les champs calculés et même, si l'on veut, directement faire le calcul sans TCD
J'aurais juré que 78Chris allait venir sur le fil...
Le demandeur est sur excel 2010 dans son profil
RE
ET alors ?
Je n'ai utilisé que des possibilités présentes dans 2010 et suggéré qu'on pourrait améliorer en ajoutant l'add on PowerQuery compatible avec 2010 (puisqu'après ce n'est plus un add on...)
J'ai ajouté la couleur sur le conditionnel... comme cela tu peux voir que c'est une possibilité future et non celle fournie...
ET alors ?
Pas de soucis. C'est juste un constat sur les fils TCD dans lesquels je réponds. D'ailleurs il y en a encore 4 ou 5 dans ce lien -->https://forum.excel-pratique.com/liste/sujets-sans-reponse
Je n'ai utilisé que des possibilités présentes dans 2010
Ok tant mieux car je n'utilise pas (power query) --> à tord probablement. Puis je ne pense pas avoir cette possibilité sur Excel 365 (devenu 2016)
RE
Sur Mac non, mais sur PC PowerQuery est intégré sur 2016, 2019 et 365 et accessible depuis l'onglet Données (seul l'add on a un onglet spécifique)
Après comme c'est intégré, cela fait partie des menus de bases.
Oui je savais sous MAC. Là je suis sous PC.
Si je ne me trompe Power Query est dispo via l'icone "Nouvelle Requete", puis descendre dans les menus pour lancer l'éditeur Power Query. Juste je suppose
RE
Sur 2016, le menu est différent de mémoire par rapport aux versions suivantes donc ce doit être cela
Sur 2019 et 365 c'est Obtenir de données, lancer PQ
Mais
- dans 80% des cas on utilise un tableau donc un clic depuis le tableau : Données, A partir d'un Tableau
- dans 18 % des 20% restants on part d'un fichier ou dossier externe depuis le bouton Obtenir des données
- les 2% restant on lance à partir de rien et encore on peut depuis le bouton Obtenir des données choisir Requête vide
On ne lance directement PQ que pour revenir sur ce qui est déjà fait et évidemment
RE
dans 80% des cas on utilise un tableau donc un clic depuis le tableau : Données, A partir d'un Tableau
Effectivement si je fais un clic dans un tableau, cela m'ouvre Power Query.
y a plus qu'à....
Bon Lambelin est où ?
Tout d'abord un très grand merci pour avoir étudier mon problème.
Une spéciale dédicace @78chris
vos solutions sont diablement efficace !!
merci
Si cela n'est pas exagérer et pour mon futur pourriez-vous @78chris m'éclairer sur le champs calculé que vous avez mis en place. Je ne comprend pas la logique.
Bonjour
Je m'absente cet aprem
Je réponds ce soir
RE
Se placer dans un champs du TCD, Onglet Outils de TCD, puis onglet Options, Champs éléments et jeux, champ calculé
N.B. : La fenêtre n'affiche pas la formule en entier=SI(Unicité=0;0;'Poids Total ligne'/Unicité) en raison du retour à la ligne dans la source
Il est à noter qu'Excel fait la somme des champs utilisés dans le calcul, donc en réalité somme('Poids Total ligne')/somme(Unicité)
Bonjour 78Chris
Merci pour ton éclairage, j'ai bien compris la rédaction de ta formule.
Par contre je ne comprend pas le calcul qui est fait et pourtant c'est bon.
Tu utilises ensuite le regroupement de somme
J'ai réduis le nombre d'enregistrement par jour mais je n'arrive pas à faire le calcul manuellement
| ANNEE | Préparateur | Date Flashage | Poids Total ligne | NB colis | poids Unit | Nb cols | Unicité | calcul |
| 2017 | GD | 20170102 | 62,64 | 6 | 10,440 | 6 | 0,5000 | 2 |
| 2017 | GD | 20170102 | 3703,491 | 482 | 7,684 | 2892 | 0,5000 | 2 |
| 2017 | GD | 20170103 | 250,56 | 24 | 10,440 | 24 | 0,3333 | 3 |
| 2017 | GD | 20170103 | 2567,469 | 340 | 7,551 | 2040 | 0,3333 | 3 |
| 2017 | GD | 20170103 | 724,684 | 72 | 10,065 | 1728 | 0,3333 | 3 |
| 2017 | GD | 20170124 | 2266,685 | 320 | 7,083 | 1920 | 0,2000 | 5 |
| 2017 | GD | 20170124 | 995,634 | 62 | 16,059 | 744 | 0,2000 | 5 |
| 2017 | GD | 20170124 | 45,555 | 5 | 9,111 | 30 | 0,2000 | 5 |
| 2017 | GD | 20170124 | 331,2 | 16 | 20,700 | 64 | 0,2000 | 5 |
| 2017 | GD | 20170124 | 608,448 | 32 | 19,014 | 192 | 0,2000 | 5 |
D'apres ce que je comprend le tcd fait la somme sur MOyc qui et le calcul (nb de colis / unicité)
donc je fais (6/0.5)+(6/0.5)+(24/.3333)+ .........+(32/.2)=4 459 alors que l'on devrait trouver 453
Je me permet d'insister pour vraiment bien comprendre, votre solution etant tellement efficace.
bien cordialement
Bonjour
Unicité donne 1 par date et préparateur donc la somme de Unicité calculée par la champ calculé donne le nombre de jours ou ce préparateur travaillé l'année concernée
Donc les champs calculés vont diviser le NB total de colis pour l'un, le poids total des colis pour l'autre par ce nombre de jours, car comme je l'ai dit un champ calculé fait automatiquement la somme des champs indiqués dans la formule
"en réalité somme('Poids Total ligne')/somme(Unicité)"
Que l'on utilise dans le TCD le champ calculé en somme, moyenne, min ou max ne change rien car il n'y a qu'une valeur pour l'année
Bonsoir désolé de mon silence, petit break qui fait du bien
merci pour ta reponse cela m'éclaire sur beaucoup de problemes que je rencontre justement avec ces fameuses fonction de calcul dans le TCD