Discussion :

[batmat86]

Bonjour à vous,

Je viens vers vous car je suis bloqué avec mon niveau excel pour mes besoins, certes ce que j'ai effectué avec un power query fonctionne, mais je voulais savoir si l'on ne peut pas faire plus simple.

J'ai un Tableau structuré A qui contient ma Base d'article.

Je voudrais que mon tableau structuré B qui est dans le même fichier même sur un autre onglet, puisse reprendre de façon "dynamique" la colonne A du Tableau A dans sa colonne A à lui.

Ainsi, si jamais j'ajoute une nouvelle référence d'article sur le tableau A, le veux que le Tableau B puisse l'intégrer automatiquement.

J'ai réussi à le faire avec Power query que j'ai découvert au passage, mais du coup, cela met une alerte de sécurité à l'ouverture du fichier car connexion de données externes alors que finalement, elles sont dans le même fichier ... Oui l'alerte peut être retiré, mais en cas de changement de poste de travail, il faut refaire activer le contenu et risque de perdre ou faire peur à ma conjointe qui sera l'utilisatrice finale du fichier que je monte.

Je ne sais pas si tout est très clair, si besoin, dites le moi.

Pour résumer, je souhaite que la Colonne de Tableau A soit identique à la colonne B du Tableau B et donc que le tableau B puisse s'adapter automatiquement à l'ajout de nouvelle ligne du tableau A.

Merci par avance de votre retour, cordialement.

[Pierre Fauconnier]

Salut.

Sans Power Query ou VBA, je ne vois pas de solution.

Avant d'aller vers du VBA, je me pose la question de la conception de ce classeur. Si c'est pour avoir la même colonne dans les deux tableaux, alors, à quoi sert le second tableau. Autant déplacer les autres colonnes du tableau 2 dans le tableau 1, quitte à créer un niveau de plan pour masquer les colonnes du 1 ou du 2 à la demande.

[batmat86]

Bonjour à vous,

Je vous mets mon fichier, ne pas tenir compte de la feuille 1 qui était l'unique feuille du fichier initialement fait par Madame, pour cela que je souhaite lui faire quelques de mieux pour le long terme et plus simple à gérer.

De même, la feuille 4, c'était lors d'un essai et n'est loin d'être finalisé ...

Egalement ne pas tenir compte de la colonne prix d'achat dans le tableau Base, puisqu'il faudra que je le supprime à terme, pour le gérer dans les entrées et sorties, mais là aussi, j'aurai surement besoin de vous car je ne vois pas comment le gérer mais on verra surement sur un sujet dédié

Pour en revenir à ce sujet, le but d'avoir ma base de référence, c'est de pouvoir l'utiliser ailleurs dans le fichier.

J'ai pu ainsi utilisé la colonne A du Tableau Base (A) pour mon tableau d'entrée sortie avec un indirect, là pas de souci.

J'aurai voulu que le tableau de Stock soit automatiquement mis à jour, même si l'on ajoute de nouvelles références dans la base et aussi dans entrée et sortie biensur. C'est pour cette raison que je voulais que la colonne A du tableau B (Tableau Stock) se remplisse de façon "dynamique" selon l'ajout de référence dans la base. Car tenir deux fois la même liste à jour manuellement me semblait inutile.

Certes le power query a parfaitement répondu à mon attente et cela fonctionne, mais je me demandais s'il n'était pas possible de faire la même chose sans power query ou VBA.

Si cela n'est en effet pas possible, je resterai ainsi. Merci en tout cas du temps à m'avoir lu.

Et encore merci Monsieur Fauconnier pour votre guide des tableaux structurés, je les utilises maintenant depuis des années pour mes suivis du quotidien, suivi de consommation de carburant, d'électricité et autres ... Cela m'a vraiment simplifié la vie depuis des années

Edit : Je sais que mettre à disposition le fichier des fois n'aide pas avec la crainte d'ouvrir un fichier d'un inconnu que je suis pour beaucoup ...

Si cela peut aider, voici des captures des différents Tableaux :

tableau de la Base :



Tableau Entrée Sortie :



Et le Tableau de Stock dont la première colonne est faite avec le power query :

[Pierre Fauconnier]

Il y a un problème de conception de ton classeur.


Tu dois avoir deux tableaux:

• Le premier pour la liste des articles;
• Le second pour la liste des mouvements sur les articles.

Les quantités, et plus tard la valorisation, se feront par TCD, probablement assisté par du Power Pivot en fonction de la technique de valorisation (pmp, lifo, fifo, ...)

Ci-dessous, deux images, celle du haut avec un TCD classique, celle du bas avec un TCD issu de Power Pivot.




Voici deux fichiers: Stock1 avec un simple TCD et des mouvements en positif et négatif, Stock2 avec du Power Pivot


[EDIT] Je n'arrive pas à joindre le fichier2. Je teste à nouveau en rentrant tout à l'heure

[batmat86]

Bonjour, merci par la réponse.

En effet, j'aurai du partir uniquement sur deux tableaux pour la conception et non trois.

Au vue des besoins, puisque je vais devoir savoir les entrées et sorties, j'en déduis que je vais devoir me tourner vers Power Pivot que je ne connais pas encore ^^

Donc au final, il y aura toujours le message de connexion externe ? Comme je l'avais avec Power Query, car si j'ai bien compris Power Pivot va utiliser un peu le même principe ?

J'ai commencé à voir un peu comment fonctionne Pivot, on trouve des exemples pour l'utilisation de fichiers externes sur plusieurs tutoriels sur le net, faut que j'arrive à trouve comment utiliser une source qui est déjà présente dans le fichier actuellement utilisé. Et ensuite voir comment mettre tout cela en place, quelques heures et à jour à prévoir je pense pour mon niveau mais c'est une bonne chose, de pouvoir découvrir et apprendre de nouvelle chose sur Excel.

Après je pense que ton fichier 2 devrait aider aussi la compréhension et ainsi la mise en place de la fonction dans mon tableau, en tout cas merci de l'aide.

Hâte de finir le fichier pour le remplir avec toutes les valeurs et non plus des valeurs de tests

[Pierre Fauconnier]

Non, Power Pivot ne demandera pas de connexion.

Tu peux travailler sans Power Pivot, mais au prix d'un INDEX-EQUIV par ligne (gourmand si beaucoup de lignes), et d'une colonne supplémentaire pour calculer le sens du mouvement




L'avantage de Power Pivot est que tu n'as pas besoin de INDEX-EQUIV ni de colonne supplémentaire. Tu vas en fait calculer les mesures dans Power Pivot puis les amener dans un TCD au départ de Power Pivot.

Voici la démarche avec Power Pivot.

1. Activer Power Pivot si l'onglet n'est pas visible (Onglet Développeur/Compléments Com/Power Pivot);
2. Ajouter les deux tableaux au modèle de données via l'onglet Power Pivot;
3. Dans Power Pivot, aller sur la vue de diagramme et lier les deux tables par le code article;
4. Dans la vue Table de Power Pivot, choisir la tables de mouvements;
5. Ajouter la mesure Qtés entrées:=sumx(filter(t_Mouvements;[E/S]="E");[Qté]) (pour cela, cliquer dans une cellule en dessous des données, puis coller la mesure dans la barre de formule);
6. Ajouter la mesure Qtés sorties:=sumx(filter(t_Mouvements;[E/S]="S");[Qté]);
7. Ajouter la mesure Qtés en stock:=[Qtés entrées]-[Qtés sorties]
8. Ajouter un TCD dans Excel au départ de Power Pivot;
9. Placer les champs de la table T_Articles dans les lignes;
10. Placer les trois mesures dans les valeurs.

[batmat86]

Merci pour toutes les explications, je pense qu'avec le fichier sans les explications j'aurai été incapable de faire le nécessaire.

Là avec la démarche pas à pas, j'ai pu adapter à mon nouveau fichier et en effet cela fonctionne très bien

Il me reste 2 questions ou je ne trouve pas de solution, mais je pense qu'il n'y a peut être pas ...

je préfère donc vous demander afin de continuer à chercher pour rien.

Dans le TCD, j'utilise des segments afin de faciliter le tri rapidement, mais je m'aperçois que l'on ne peut mettre de segment pour les valeurs provenant de pivot. Il y a t'il une solution, afin par exemple de visualiser uniquement les articles en stock ou uniquement ceux en rupture rapidement ?

Et ma deuxième question, concerne toujours le TCD, c'est la première fois que j'aurai un TCD avec autant de valeurs, plusieurs centaines voir milliers à terme, je n'ai pas trouvé comment avoir les totaux généraux en haut de TCD, je ne vois quand bas de colonne. Il y a t'il une astuce ou paramètres que je n'ai pas vu ? Ou simplement une impossibilité du TCD ? Car ce n'est pas forment simple de descendre plusieurs centaines de lignes pour avoir le total.

Encore un très grand merci à vous

[Pierre Fauconnier]

On peut ajouter des segments sur un TCD issu de Power Pivot...

On peut aussi placer les sous-totaux au dessus, mais pas avec la disposition tabulaire. Il faut choisir une autre disposition




Par contre, la ligne de total général sera toujours en bas, quelle que soit l'origine du TCD. Il serait probablement utile de segmenter par catégorie d'articles pour examiner les catégories une à une.

Je ne sais pas combien tu as d'articles, mais dans le TCD, tu n'auras qu'une ligne par articles. Tu as plusieurs milliers d'articles à gérer?

[batmat86]

On peut ajouter des segments sur un TCD issu de Power Pivot...

A mince, je ne vois pas, j'ai bien la possibilité de mettre des segments sur le TCD issu de Power Pivot mais uniquement sur les colonnes de l'un ou l'autre des deux tableaux sources de Pivot.
J'aurai aimé avoir un segment sur le nombre en stock et çà je n'ai pas vu.
Je vais aller chercher encore plus alors si cela est possible.

On peut aussi placer les sous-totaux au dessus, mais pas avec la disposition tabulaire. Il faut choisir une autre disposition

Par contre, la ligne de total général sera toujours en bas, quelle que soit l'origine du TCD. Il serait probablement utile de segmenter par catégorie d'articles pour examiner les catégories une à une.

Je ne sais pas combien tu as d'articles, mais dans le TCD, tu n'auras qu'une ligne par articles. Tu as plusieurs milliers d'articles à gérer?

Pour le moment, je ne suis qu'à environ 500 références mais cela oui risque d'augmenter avec les mois et années à venir ...

Mais je suis bête ... J'ai déjà rentré les marques donc qui peuvent servir à catégoriser ... Ainsi en effet, je peux avoir mes sous totaux en haut de catégorie et c'est très bien Je ne sais pas pourquoi, je n'avais pas pensé à rajouter une ligne dans le TCD avec la bonne hiérarchie ... Donc là c'est tout bon.

Me reste juste à trouver pour avoir un segment sur la quantité en stock si tu me dis que c'est possible, car cela ne m'est pas proposé lors de la création du segment.

[Pierre Fauconnier]

Il n'y a pas de segments sur les mesures, comme tu n'en a pas sur un tcd classique sur les valeurs agrégées. Les segments travaillent sur les valeurs des colonnes sources. Tu dois passer par les filtres.






Les segments d'un TCD sont essentiellement là pour filtrer les dimensions d'analyse. Ils sont en fait le pendant ergonomique des filtres de rapport, dans lesquels tu ne sais pas pousser les valeurs agrégées. Il faut juste noter qu'en plus de leur intérêt ergonomique, ils permettent de lier les TCD entre eux au niveau des "filtres de rapport".

[Pierre Fauconnier]

Note aussi que si tu veux travailler efficacement sur les fabricants et les marques, tu devras avoir les tables concernant ces dimensions sans doublons... Il faudra alors lier les données en arborescence:

Fabricants > Marques > Modèles.

La position de la colonne Type dans la table de base me laisse penser que c'est un "sous-élément" de modèle. Si ta table des mouvements est liée à la table de base par le modèle, tu ne sauras pas analyser la dimension Type par Power Pivot.

Si la saisie des mouvements s'effectue dans Excel, tu auras intérêt à prévoir des listes de validation en cascade car j'imagine mal choisir un article parmi des milliers de lignes d'une liste déroulante. Attention que ton modèle sur quelques articles "test" ne tiendra peut-être pas la route pour un volume plus important de données. Une gestion de stock de milliers d'articles nécessite autre chose qu'Excel à mon avis, ne serait-ce que sur le plan de l'ergonomie de la saisie.

Sans voir tes données, délicat d'aller plus loin.

[batmat86]

Note aussi que si tu veux travailler efficacement sur les fabricants et les marques, tu devras avoir les tables concernant ces dimensions sans doublons... Il faudra alors lier les données en arborescence:

Fabricants > Marques > Modèles.

Pour le moment c'est surtout la marque et le modèle qui vont servir.

La position de la colonne Type dans la table de base me laisse penser que c'est un "sous-élément" de modèle. Si ta table des mouvements est liée à la table de base par le modèle, tu ne sauras pas analyser la dimension Type par Power Pivot.

Oui c'est bien un sous élément de modèle pour définir la matière, mais là aussi pas sur que cela restera à terme dans le fichier, car pas sur qu'il y ai une pertinence, à voir.

Si la saisie des mouvements s'effectue dans Excel, tu auras intérêt à prévoir des listes de validation en cascade car j'imagine mal choisir un article parmi des milliers de lignes d'une liste déroulante. Attention que ton modèle sur quelques articles "test" ne tiendra peut-être pas la route pour un volume plus important de données. Une gestion de stock de milliers d'articles nécessite autre chose qu'Excel à mon avis, ne serait-ce que sur le plan de l'ergonomie de la saisie.

Oui la saisie des mouvements se fera sur Excel. Pour le moment, la saisie j'ai mis la liste déroulante pour le modèle dans la saisie des mouvements, mais la liste déroulante ne sera pas utilisé pour la saisie, la saisie sera manuelle. Mais cela m'a permis de mettre un message d'avertissement si la valeur saisie n'est pas dans la liste, puisque cela voudrait dire qu'elle n'est pas dans la base, donc avec le message, plus qu'à aller ajouter le modèle en base.

Pour ce qui de la gestion du stock, même s'il y a plusieurs centaines de modèles, normalement le stock de chaque modèle ne dépassera presque jamais 1 et pareil, normalement il y a peu d'entrée et sortie, si l'activité de la compagne fonctionnera très très bien, je ne pense pas que l'on dépasse les 100 entrées/sorties au total par mois et encore cela semble un peu un cas extrême, on sera déjà très bien à 60 par mois, quand on voit pour le moment qu'elle n'a pu réaliser que 13 ventes en 2 mois.
Le plus long va être de saisir chaque modèle du stock de départ, donc même si cela a fait plusieurs centaines d'un coup, cela ne sera plus le cas après.

C'est pour cela que je pense qu'Excel sera suffisant pour les modestes besoins qu'elle a.

Sans voir tes données, délicat d'aller plus loin.

Bon comme toi, l'ajout du fichier Excel contenant un Pivot ne semble pas être possible ... Donc je l'ai mis en zip comme tu as fait pour le tiens, c'est surprenant ce fait pour les pièces jointes.

Du coup, je profite de ta présence, car j'avoue buter, si je veux calculer la valeur du stock actuel (bon, c'est plus moi que Madame qui veut l'ajouter ...). Dans l'idéal, je souhaite saisir le prix d'achat uniquement lors de l'achat dans les mouvements, en cas de vente de ne rentre donc pas de montant d'achat, car cela voudrait dire qu'il faudrait que l'on retrouve dans les mouvements le dernier prix d'achat du modèle concerné, donc ce n'est pas pratique, si l'achat a été fait plusieurs mois avant la vente.
Je l'avais fait avant l'utilisation du Pivot en faisant :
(Somme de tout les achats de chaque modèle) / Quantité d'achat du modèle
pour avoir le prix unitaire, et ensuite, je le multipliais par la quantité en stock. Cela fonctionne mais n'est pas exacte, puisque c'est un prix moyen, si un jour la quantité acheté d'un modèle est supérieur à 2 ... Le prix d'achat sera peut être différent entre deux périodes.

Donc là, je ne vois rien pour gérer la valeur de façon simple, et je ne voulais pas me lancer dans une usine à gaz pour le faire, sachant que pour le moment, cela n'est pas une demande ni besoin de ma conjointe. Si vous avez une piste ou réflexion qui pourrait répondre à cette demande, je suis preneur, sinon on va rester sur un tableau simple à utiliser pour elle et je verrais si la nécessité se présente ...

Encore merci pour toute l'aide apportée