Transformer tableau croisé en base de données (powerquery) ?

[trekkeur50]

Bonjour,

je suis sur excel 365.
Je souhaiterais transformer un tableau croisé en base de données (voir exemple ci joint)
J'ai plusieurs fichiers a fusionner et a transformer en base de données
Est ce avec Power query que je vais pouvoir le faire sachant que je ne connais pas du tout cet outil
J'ai 15 fichiers avec de nombreuses lignes et colonnes donc j'aimerais eviter de passer par des formules

Merci de votre aide

 

[Amilo]

Bonjour le forum, trekkeur50,
Power query est très pratique et puissant pour notamment ce genre de transformation,
Je pourrais vous expliquer la démarche à suivre demain soir
Pouvez-vous nous indiquer si vous voulez fusionner tous les fichiers d'un même dossier puis transformer en table ?
Et le cas échéant, si tous les fichiers ont la même structure ?
Cordialement

 

[mapomme]

Bonsoir @trekkeur50, @Amilo 🙂,

Une petite macro vite faite et sans prétention.

• cliquez sur le bouton Hop!
• sélectionner la plage à ventiler (exemple A2:F22 de la feuille "ex1")
  
• sélectionner en destination la cellule A2 de la feuille "Résultat"

puis

• (re)cliquez sur le bouton Hop!
• sélectionner la plage à ventiler (exemple D5:H11 de la feuille "ex2")
• sélectionner en destination la cellule A47 de la feuille "Résultat" (première cellule libre)

Les plages peuvent être dans n'importe quel classeur ouvert dans Excel (tout comme la cellule de destination).

 

[chris]

Bonjour à tous

Avec PowerQuery, c'est très simple.

Cela peut-être fait sur l'ensemble des fichiers d'un dossier

Mais, comme indiqué par Amilo, il faudrait préciser si tous les fichiers ont la même structure, et un seul onglet à traiter...

 

[trekkeur50]

Merci pour vos reponses.
Je suis interessé par vos propositions sur powerquery dans le but d'apprendre cet outil (les explications de construction sont les bienvenus)
J'ai joint 2 fichiers pour mieux faire comprendre mon besoin.
- Tous les fichiers sont issus du meme dossier et les fichiers ont un seul onglet
- Meme stucture dans chaque fichier mais avec des elements differents (ex un PVT peut etre present dans un fichier mais pas dans l'autre)
Impatient d'avoir vos idees
merci

 

[trekkeur50]

Bonsoir @trekkeur50, @Amilo 🙂,

Une petite macro vite faite et sans prétention.

• cliquez sur le bouton Hop!
• sélectionner la plage à ventiler (exemple A2:F22 de la feuille "ex1")
  
• sélectionner en destination la cellule A2 de la feuille "Résultat"

puis

• (re)cliquez sur le bouton Hop!
• sélectionner la plage à ventiler (exemple D5:H11 de la feuille "ex2")
• sélectionner en destination la cellule A47 de la feuille "Résultat" (première cellule libre)

Les plages peuvent être dans n'importe quel classeur ouvert dans Excel (tout comme la cellule de destination).

Magnifique.Maintenant je vais essayer de comprendre la macro.merci

 

[chris]

RE

Sous réserve de

• nettoyer tes fichiers : enlever la ligne tableau initial et l'exemple du résultat car sinon cela n'a pas de sens
• modifier le chemin indiqué dans l'exemple ci-joint.
• placer le fichier de synthèse ailleurs pour qu'il ne se synthétise par lui-même (on pourra éventuellement ajouter un filtre ultérieurement si tu veux la mettre dans le même dossier)

Teste-le sur ton dossier et si OK, je donnerai la marche à suivre...

 

[trekkeur50]

Chris,
j'ai suivi tes conseils
- nettoyage des 27 fichiers du même dossier nommé extraction (j'ai mis 2 d'entre eux en PJ pour te montrer les fichiers réels et non plus des exemples)
- j'ai repris ton fichier ou j'ai modifié le chemin d'acces (C:\Users\Maison\Documents\Travail\Logistique NC\Extraction) et l'ai mis dans un dossier différent des 27 fichiers
...et c'est la ou ca se complique.je ne sais plus quoi faire pour le tester.

 

[Amilo]

Bonsoir le forum, Chris, mapomme, trekkeur50,
Je viens de faire une capture video d'une durée de 3m50 pour faciliter les explications d'une proposition via Power query,
Malheureusement je viens de voir que l'extension .mp4 n'est pas acceptée et ne passe pas en pièce jointe.
Si vous souhaitez je peux vous transmettre le fichier mp4 par message privé.
Edit : Sinon la vidéo via ce lien

Je pourrais vous apporter les explications si besoins
Cordialement

 

[chris]

Re

Pour un fonctionnement automatique il faut que l'onglet à traiter dans tous les classeurs ait le même nom.

J'ai modifié une ligne du traitement afin que cela prenne le 1er onglet quel que soit son nom.

Pour tester, après changement du chemin : onglet données, Actualiser Tout

Edit : la solution d'Amilo, que je salue, est de transformer au préalable chaque plage source en tableau structuré et de traiter les tableaux.

Quelle que soit la solution pour laquelle tu optes, regarde la vidéo.

Si tu choisit mon option je te donnerai les éléments qui diffèrent.

 

[mapomme]

Bonjour @trekkeur50,

Un essai en VBA qui permet de choisir un répertoire puis de sélectionner les fichiers Excel à ventiler au sein du répertoire choisi. Le résultat est sur la feuille "Resultat-Ventil" du classeur de la macro. Il suffit ensuite de copier le résultat vers où on veut.

• ouvrir le fichier
• cliquer sur le bouton Hop! de la feuille "MACRO".
• cliquer sur le bouton représentant un répertoire pour choisir le répertoire où se trouve les fichiers à ventiler
• cliquer sur un fichier de la liste supérieure pour le faire passer dans la liste inférieure (ajouter le fichier dans la liste des fichiers à ventiler)
• cliquer sur un fichier de la liste inférieure pour le faire repasser dans la liste supérieure (ôter le fichier de la liste des fichiers à ventiler)
• cliquer sur le bouton Ventiler pour procéder à la ventilation

 

[Amilo]

Bonsoir à tous,
Juste une petite information pour les débutants Excel 🙂, il s'agissait de la version Excel 2019 dans ma précédente vidéo de démo,
Les manipulations sont exactement identiques avec Excel 2016 sauf l'icône pour la connexion au dossier se nomme "Nouvelle requête" au lieu de "Obtenir des données" dans Excel 2019 (voir image en pièce jointe).

Sinon, je vous transmets une nouvelle vidéo pour montrer l'autre aspect intéressant de Power query :
C'est que toutes les manipulations de transformation de la 1ère vidéo sont pré-enregistrées et automatiquement appliquées aux futurs fichiers qui seront intégrés au dossier.
Il suffit de cliquer simplement sur le bouton "Actualiser" pour prendre en compte les nouveaux fichiers ou toute modification d'une donnée dans n'importe quel fichier du dossier.


Edit : @mapomme, bravo pour l'excellent fichier très simple d'utilisation

Bonne soirée

 

[trekkeur50]

merci à tous pour ces differentes options.J'ai de quoi consulter,comprendre,maîtriser...

 

[trekkeur50]

C'est juste dingue.J'ai réalisé les differentes options en moins de 10 minutes.
Admiratif de la prouesse technique mais encore plus bluffé par votre pédagogie
Encore merci Amilo,Chris et mapomme