XL 2016 TCD avec plusieurs feuilles

troopers87

XLDnaute Occasionnel
Bonjour à tous,

J'ai besoin dans mon travail de faire un tableau croisé dynamique avec pour sources, plusieurs feuilles Excel.
Pour chaque source de données, il y a une sorte de clé primaire, un point commun qui permettrait à Excel de regrouper toutes les informations dans un TCD.
L'idée serait de pouvoir avoir des champs regroupés par sources de données, comme dans un Cube (que j'utilise professionnellement au quotidien).

Malheureusement, après plusieurs tentatives et errances sur internet, impossible de trouver comment faire...

Pour vous aider à m'aiguiller moi, et d'autres, voici ci-joint un petit fichier exemple :)

Merci d'avance pour votre aide !
 

Pièces jointes

  • TCD.xlsx
    9.9 KB · Affichages: 51

Boo2bafoot

XLDnaute Nouveau
Bonsoir,

Une piste pourrait être de créer une feuille supplémentaire qui consoliderait vos différentes sources de données.
Via la fonction rechercheV par exemple, pour constituer vos 4 colonnes sources dans un même tableau et faire votre TCD à partir de ce dernier.

cf Fichier ci-joint
Cordialement
 

Pièces jointes

  • TCD1.xlsx
    18.5 KB · Affichages: 42

troopers87

XLDnaute Occasionnel
Bonjour Bee2bafoot et merci pour ta réponse,

A ce jour, j'utilise effectivement des RECHERCHEV pour faire mes récap', mais c'est extrêmement gourmand en ressources : j'ai en réalité 4 fichiers d'à peu près 10 000 lignes chacuns et cela me prend 5 minutes montre en main pour tout recalculer.

Si j'utilise un TCD qui concatene les informations des différentes sources de données, ce serait moins gourmand et plus pratique à manipuler :)
 

Boo2bafoot

XLDnaute Nouveau
Pour les questions de performance, c'est du côté de powerpivot qu on va aller creuser alors qui solutionnera mieux ton probleme que ma solution avec recherchev
La question va effectivement etre de savoir si les articles sont uniques comme le dit gosselien. Je te posterai un fichier avec powerpivot dans l'aprem mais il pourrait etre utile que tu nous postes une version de tes bases de donnees un peu plus étayée.
Cordialement
 

chris

XLDnaute Barbatruc
Bonjour à tous

Soit ton exemple est mal choisi soit il reflète ton cas réel et n'y a aucun intérêt à faire un TCD : quelle opération statistique peut-on faire si tu n'as que des prix unitaires comme valeur et une relation de 1 à 1 entre chaque table ?
 

troopers87

XLDnaute Occasionnel
Bonjour à tous les trois et merci pour votre intérêt,

Oui, il est effectivement possible qu'à vouloir trop simplifier mon exemple, cela l'a rendu inintelligible. Toutes mes excuses.

Mon objectif est en fait de faire le récapitulatif d'inventaires que nous faisons dans des magasins disséminés partout en France. Plusieurs articles sont dans chaque magasin, sont comptés par des merchandisers, sont vendus tout au long de l'année et le stock théorique est finalement comparé au stock physique.

Pour gérer tout ceci, voici les 4 fichiers en question (sans les données confidentielles et allégés) :
- 3 fichiers sources : Stock INFO, Ventes INFO et Ventes REELLES
- 1 fichier d'analyse : RECAP 2016, contenant les inventaires des merchandisers (que l'on peut considérer comme une autre source de données).

Les informations des 4 sources de données peuvent être consolidées dans le fichier RECAP grâce à une concaténation du code article et du code magasin, il s'agit donc d'une clé commune aux 4 fichiers. Dans certains fichiers (notamment celui des ventes), la clé n'est pas unique car elle peut se répéter d'un mois sur l'autre : c'est pour cette raison qu'au lieu de RECHERCHEV, j'utilise plutôt des SOMME.SI.ENS avec un critère sur le mois en question.
Nota : pour limiter le risque de double saisie, en temps normal, j'ai une colonne de vérification des doublons.

Dans le fichier RECAP, j'ai à peu près 80 magasins comprenant chacun une dizaine d'articles vendus toute l'année : vu la masse d'information, chaque recalcul (touche F9) que je lance monopolise Excel durant 5 bonnes minutes. C'est pour cette raison que dans le fichier RECAP que je vous ai envoyé, il n'y a qu'un seul magasin.

Voilà, j'espère ne pas vous noyer d'informations en vous donnant cetexemple qui se rapproche bien plus de la réalité. Je ne suis pas sûr qu'il soit très utile d'essaye de le décortiquer, d'autant plus que j'ai un doute sur la pérennité des liens entre les classeurs.

Je voudrais bien tester un TCD avec pour sources de données chacun des 4 fichiers sources, mais si ce n'est pas possible ou qu'il y a d'autres solutions plus appropriées, je suis bien entendu preneur.
 

Pièces jointes

  • RECAP 2016.xlsx
    4.8 MB · Affichages: 79
  • Stock INFO.xlsx
    2.7 MB · Affichages: 45
  • Ventes INFO.xlsx
    3.2 MB · Affichages: 36
  • Ventes REELLES.xlsx
    2.4 MB · Affichages: 40

chris

XLDnaute Barbatruc
Bonjour

Vu la quantité de données et la quasi impossibilité d'Excel à réaliser autant de liaisons, pour ma part je consoliderais dans une base de données.

Si tu as Access, tu pourras créer une structure de base de données relationnelle adéquate et y déverser tes données grâce à des liaisons et des requêtes utilisant les données liées.

Ensuite il sera facile de réaliser une vue utilisable par des TCD Excel.

Même avec PowerQuery ou PowerPivot, il me parait difficile de gérer d'une part 80 liaisons X 3, d'autre part l'ajout de données au fil des mois.

De plus PowerPivot n'utilise pas le modèle relationnel mais le modèle en étoile ce qui limite les liaisons en cascade.
 

troopers87

XLDnaute Occasionnel
Arf, je craignais ce type de réponse...

Mes cours d'Access remontent à bien longtemps, ce sera compliqué de m'y remettre. Il faut malgré tout que je m'y penche car c'est aujourd'hui ingérable sur Excel...
Pour ma culture personnelle, et au-delà de cet exemple, est-il malgré tout possible de regrouper les données de plusieurs feuilles ou classeurs dans un même tableau croisé dynamique ?
 

troopers87

XLDnaute Occasionnel
Hello !

Je vais me renseigner sur PowerQuery, merci pour l'info !
En ce qui concerne le fait de tout mettre dans un classeur, c'est malheureusement impossible : les 3 classeurs sources de données alimentent 3 RECAP qui correspondent à 3 gammes différentes. Et en ce qui concerne les sauvegardes... chat échaudé craint l'eau chaude ^^ Merci pour le conseil.
 

Boo2bafoot

XLDnaute Nouveau
Salut à tous,

Trooper87, j'ai jeté un oeil à tes fichiers.
Il semble que tu aies une clé unique sur tes 4 fichiers : ta concaténation des IdMag et des IdArticles.
Du coup, en transformant tes tableaux en tables de données (Ctrl-L) après les avoir un tout petit peu revu (j'ai viré tes lignes intermédiaires sur ton fichier recap2016, qui polluent le tableau, je te conseille de leur consacrer un autre tableau aisément créable en dehors de ta plage de données), il est possible de les intégrer dans un modèle de données via PowerQuery puis de les transférer dans PowerPivot.
J'ai créé une table dérivée des clés de recherches (la concaténation MagArticle) via PowerPivot (select distinct MagArcticle From StockInfo par exemple) et créé des relations entre les 4 tables importées (tes 4 fichiers et cette nouvelle table).
De là, il est possible de créer un TCD depuis PowerPivot qui reprend les données de tes 4 fichiers. Il suffit de les incrémenter indépendamment et de ne pas oublier dès lors d'actualiser dans ton fichier central de consolidation avec PowerPivot quand de nouvelles données sont ajoutées.

Ci-joint les 4 fichiers sources (modifiés) + fichier avec ton modèle de données PowrQuery & PowerPivot
Teste-le et vois si ça correspond à tes attentes.
NB: il est possible que tu doives recréer les connexions aux fichiers sources

Bonne soirée
 

Pièces jointes

  • RECAP 2016.xlsx
    5.2 MB · Affichages: 66
  • Stock INFO.xlsx
    2.7 MB · Affichages: 40
  • Ventes INFO.xlsx
    3.1 MB · Affichages: 34
  • Ventes REELLES.xlsx
    2.5 MB · Affichages: 40
  • Test trooper87.xlsx
    1.3 MB · Affichages: 35

chris

XLDnaute Barbatruc
Bonjour

A noter que le modèle de données PowerPivot n'est accessible qu'aux versions pro plus à partir de 2013 et donc aussi dans 2016.

On peut je pense faire les relations dans PowerQuery et travailler en TCD classique. Tout dépend des calculs à réaliser dans le TCD...

Le souci est de combiner 80X3 sources...
 

Discussions similaires