Macro : Modification TCD

Bonjour,

Si j'ai bien compris ton pb, la macro n'est pas nécessaire; tu dois utiliser la fonction "LIREDONNEESTABCROISDYNAMIQUE". Elle te permet de récupérer une information dans un TCD.
Afin de bien la comprendre, je te conseil de consulter l'aide d'Excel disponible dans Menu/?/sommaire et sous onglet index de taper "LIREDONNEESTABCROISDYNAMIQUE".

Tu peux aussi à titre d'exemple consulter ce lien <http://www.excel-downloads.com/html/French/forum/read.php?f=1&i=48491&t=47263> dans lequel tu trouveras un exemple de cette fonction.

Si tu as encore un pb et afin de t'aider au mieux, je te propose de mettre ton fichier Zippé (SVP) sur ce forum.

Bon travail

@+

Dan

Dan,

Merci pour ta réponse rapide. La réactivité sur forum est impressionnante.
Et puis c'est plaisant de parler à un Dan; j'ai eu un bon copain qui avait le même prénom ... mais

il y a très peu de chances que tu sois le même !
Tu m'as fait découvrir la fonction "LIREDONNEESTABCROISDYNAMIQUE".
Ta solution est très bonne, mais en mode "flash" en quelque sorte : Un seul TCD à traiter, et pas de

retraitement sur les résultats du TCD.

Malheureusement pour moi mon fichier est plus complexe, et je ne sais pas si je pourrai trouver une

solution sans passer par une macro.
Si tu veux bien me consacrer encore un peu de temps, je t'envoie un fichier exemple compressé

"Modification_TCD.zip", et j'ajoute ci-dessous quelques explications. Merci beaucoup pour

l'attention que tu porte à ce problème.

EXPLICATIONS :

Au départ, j'ai trois onglets :
- Base_Données_historique=>2002
- Base_Données_2003
- Couts
qui sont dans la réalité des sources de données externes, avec lesquelles il faut que je me

débrouille dans l'état dans lequels le service informatique me les donne ... C'est comme ça et pas

autrement !
Dans le fichier exemple, je me suis amusé à les reconstituer avec des fonctions ALEA,

confidentialité pour mon boulot oblige ...
L'onglet Couts est une liste de prix unitaires
Les onglets Base_Données sont un état des volumes de stocks par mois, et par Business Unit (=dépot)

:
- Base_Données_2003 pour l'année 2003
- Base_Données_historique=>2002 pour les années antérieures à 2003

Ces deux bases de données n'en sont qu'une dans les gros systèmes informatiques, mais on a été

obligés de la scinder en deux, car son extraction ou actualisation sur Excel mettait un temps fou.

Elles alimentent deux TCD, respectivement sur les onglets :
- historique stock => 2002
- Stock 2003
À la fin des TCD de ces deux onglets (à droite), je prends le volume total pour chaque ligne, et je

le valorise avec les couts unitaires de l'onglet. Rien de bien méchant, une multiplication avec une

formule RECHERCHEV(). Mais cela représente un premier retraitement.


2e retraitement :

Pour chaque Business Unit (ou dépôt), et pour le même dépôt par TCD, il faut que j'additionne le

total valeur obtenu sur Base_Données_historique=>2002 au total valeur obtenu sur Base_Données_2003.

Concrètement, par exemple pour le dépôt D14 :
- Sur "historique stock => 2002", il faut que je filtre le champ Page Business Unit sur

l'élément D14.
- Sur "Stock 2003", je fais la même chose
- Du coup les deux TCD s'actualisent et me donnent le total valeur stock pour D14
- Il faut additionner ces totaux pour obtenir un total dépôt toutes périodes confondues :

C'est l'objet des formules du tableau Total pour un dépôt (en rouge) dans l'onglet Synthèse.
- Dans ce tableau, on recopie en valeur le résultat de la ligne "total stock a date" dans la

ligne du dépôt concerné dans le tableau "Récap Dépôts" (en jaune, juste en dessous).


Ce 2e retraitement est à répéter pour chaque dépôt, de manière à remplir le tableau "Récap Dépôts"

de l'onglet Synthèse.

L'ensemble du fichier est à réactualiser tous les mois, ce qui explique l'intérêt d'automatiser son

traitement.

Bonsoir,

OK bien reçu ton fichier que je vais analyser pour te proposer quelque chose.

@+

Dan

Bonjour,

Oui Dan l'union fait la force. Alors je prends la premiere partie :

1- 1er traitement : Eliminer les Recherches V et les totaux Hors TCD :

en insérant des éléments calculés correspondants aux couts unitaires de chaque article - voir fichier pour détails. On regroupe tout dans le TCD.

- Nota : dans Stock 2003, il avait été soustrait 9-10-11 - j'ai donc fait
pareil


2- Pour récapituler les BusinessUnits :

2.1 faire une macro qui irait chercher les Totaux des 2 feuilles selon la BU

2.2. jamais fait et vu jusqu'à présent, peut-être une formule avec Decaler
qui raménerait le tout - à voir si faisable - attention il faudrait pas
que le TCD bouge sinon........le pot aux roses se casse.

Celeda

Re: Macro : Modification TCD - Commentaires

Dan, Celeda,

Encore une fois merci de l'intérêt que vous portez à ce problème.
Ce qui me fait le plus plaisir, c'est qu'apparemment c'est la première fois que vous tombez sur ce type de question (Cf. Celeda "jamais fait et vu jusqu'à présent"), et donc cela aiguise votre intérêt.

Néanmoins, je me sens redevable, et je vais voir sur XLD comment faire profiter la communauté d'une macro de pointage que je me suis monté, et qui répond à des besoins très fréquents en comptabilité et en contrôle de gestion.
Terima Kasi
("Donner-recevoir" en Indonésien, autrement dit "merci")


Celeda,

Un commentaire à propos de la soustraction des mois 9-10-11 du total volume stock dans Stock 2003 :
Cela tient à ce que l'on se situe en fin Aout, et qu'on veut obtenir la valeur du stock à fin Août. Par conséquent, on soustrait tous les volumes ultérieurs à cette date.
Mais alors, tu vas peut-être me demander pourquoi ils existent, puisqu'on se situe à fin Août ? Dans les fait, on n'y est pas réellement : il arrive fréquemment qu'on actualise l'extraction de la base de données 2003 du côté du 5-10 septembre; on risque donc de rapatrier des volumes septembre.
Corollaire important :
Le mois suivant, ce ne sera plus les mois 9-10-11 qu'il faudra soustraire, mais seulement les mois 10-11. La formule évolue donc en fonction de la date, et je pense pouvoir intégrer facilement cette contrainte dans ma macro.

Bonne journée sur Excel.
Cordialement,
François-Paul

Bonjour,

Revoici un complément à la proposition de Celeda avec quelques modifications de présentation et la formule on arrive sans macro à extraire les données.

Toutefois, dans les feuilles base de données 2003 et base de données historique 2002, chaque donnée dans la colonne "Buziness units" est transcrite avec des espaces. Il est impératif de garder cette structure ou de savoir sous quelle forme les données seront introduites dans ces feuilles via le service informatique. Le calcul des formules dans la feuille synthèse en dépend.

Il y a certainement moyen d'améliorer le tout à partir des 2 bases de données pour avoir un TCD directement avec les totaux comme tu le souhaites. On pourrait évidemment analyser cela moyennant un petit délai et surtout il serait nécessaire de connaitre quelles sont les différentes présentations souhaitées.

Merci de nous faire tes commentaires et surtout si tu désires que l'on continue sur ce projet.

A bientôt

@+

Dan

Celeda, Dan,

Merci beaucoup pour ce travail.

À moi maintenant de l'analyser et de vous transmettre mes commentaires.

Dan,
Dès à prtésent, et si je t'ai bien compris, la solution d'un seul TCD récap' pour les deux bases de données distinctes est séduisante.
Peut-être ce problème a-t-il déjà été abordé dans un fil antérieur ? Je ne pense pas car j'avais déjà fait une recherche sur "TCD" avant de poser ma question, et je n'avais rien trouvé d'approchant.

Pour moi le projet continue, mais svp, juste un peu de temps pour analyser tout ce que vous avez fait.

À bientôt.

Cordialement,
François-Paul

François-Paul,

Pas de problème. Nous surveillerons ton post.

@+
Dan

Celeda, Dan,

Désolé d'avoir été si long à répondre.
En fait, j'avais posté un long commentaire il y a deux jours, et j'attendais patiemment votre réponse, et je viens de m'apercevoir que ce que j'ai posté n'est pas arrivé ! Tout ça parce que le fichier joint faisait 51 ko.

Je suis plus que jamais attaché à trouver une solution, et votre aide m'est précieuse. Je vous envoie donc mon Post, qui n'est jamais arrivé :


Ça y est, j'ai fait l'analyse de vos différentes suggestions sur Modification_TCD_V3.xls. Vous trouverez ci-joint un compte rendu avec Modification_TCD_V4.xls.

En premier lieu, je suis obligé de revenir sur le projet du TCD commun au deux bases de données.
Ce cas de figure serait pourtant très intéressant à monter par ailleurs.
Mais dans le contexte de mon fichier Excel réel, il n'est pas possible de le mettre en place.
En effet, les bases de données qui alimentent mes TCD dans le fichier exemple n'existent pas dans la réalité : Les TCD sont en fait alimentés par une liste de données externes, par le biais d'un Query Excel sur AS/400.
Conséquences :
- Le paramétrage de l'extraction ne peut étre réalisé qu'en lançant la commande "Lire les données" de l'étape 2 de l'assistant TCD.
- Pour la période 1999-2002, l'actualisation de l'extraction de la base de données AS400 met une journée complète ! C'est pour cela qu'on a isolé l'extraction des données en question dans un TCD distinct, qu'on se garde bien de mettre à jour : Non seulement cela bloque un poste pendant une journée, mais en plus c'est inutile, puisque ce sont des données historiques figées.

En second lieu, j'ai principalement trois commentaires à faire :
- Merci à Celeda pour sa valorisation directement dans le TCD. Avec une remarque : le coût unitaire doit être intégré dans ce calcul en tant que variable, et non en tant que constante. En effet, il peut évoluer d'un mois à l'autre.
Est ce que c'est possible sans passer par une macro ?
- Merci également pour la solution de masquage des mois, à ceci près que cette opération manuelle devra être réalisée chaque mois, pour démasquer le nouveau mois courant. Une solution automatisée (macro) serait préférable (est-ce possible ?), compte tenu de l'inexpérience des utilisateurs sur TCD, et du risque d'oubli de faire cette manip' (en la matière, j'ai malheureusement déjà donné !)
- La solution de Dan est excellente en ce qui concerne la récupération des totaux valorisation par dépôt (LIREDONNEESTABCROISDYNAMIQUE()) : La fonction feuille de calcul donne directement le total valorisation

Enfin, quelques mots sur les éléments Business Unit (=dépôts) : En effets, ils sont restitués par la base de données AS/400 avec tout pleins d'espaces, et comme je l'ai dit plus haut, il faut faire avec. C'est je pense une question de paramétrage du Query Excel. Mais du moment que ce sont toujours les mêmes libellés qui sortent de l'actualisation des extractions, cela n'est pas trop gênant.

J'ai complété vos commentaires en zones bleues dans Modification_TCD_V4.xls, en reprenant ce que je viens d'écrire.
J'espère que ces commentaires supplémentaires vous satisferont et vous permettront de mieux cerner les axes d'amélioration qui subsistent en termes d'automatisation. Si cette question vous intéresse, merci par avance de vos suggestions à ce sujet.

Cordialement,
François-Paul

Bonsoir François-paul,

Bien reçu tes commentaires et ton fichier que j'ai repris pour analyse.

J'ai besoin d'un premier renseignement concernant la valorisation :

1. Est-ce que les articles dans cette feuille coûts sont figés, c'est à dire peux-t-il en venir d'autres ou sont ce les euls possibles. je parle de 1001, 1002, ...
2. Toujours dans cette feuille coûts, est-ce que en regard des articles les données (PU) sont modifiés via As400 ou par toi même directement dans cette feuille.
3. Je suppose que les espaces des Buziness units resteront tels quels. Le cas échéant la synthèse ne fonctionnerait plus convenablement.

Dans l'attente de te lire

@+

Dan

Bonsoir,


1- Concernant les PU qui changent :
- envisager une colonne supplémentaire dans la BD:
en filtrant par article il est aisé de modifier d'un seul coup les prix
unitaires

- prévoir une colonne Total

2- Dans le TCD :

- se servir de cette colonne Total pour y deposer directement le résultat
qui sera éclaté par le TCD

- remettre le Mois en ligne et se servir des filtres qui sont prévus à cet
usage pour decocher le mois que l'on souhaite.
Avec une boite explicative, l'usage sera à même de le faire lui-même.
Il ne faut pas sous-estimer la volonté des users à se servir des petites
flêches déroulantes.
Astuce : Quand on s'adresse à des non-initiés, il est recommandé
d'ajouter une feuille A Lire ou Accueil et d'y déposer les quelques
explications succinctes susceptibles de les aider dans les manipulations.

Celeda