Fusionner des classeurs Excel
Bonjour à tous !
Avant toute chose, petite présentation : Je m'appelle Romain, j'ai 24 ans et je suis stagiaire en achats pour une boîte trèèèès connue dans le BTP en ce moment.
On m'a confié une mission qui est d'améliorer des outils de reporting dont le but est simple (sur le papier) : Automatiser la fusion de données comptables de plusieurs classeurs Excel pour pouvoir lire facilement quels ont été la nature et le montant des achats dans l'année.
Ainsi j'ai besoin de savoir à quelle famille de produits le produit acheté appartient et si cet achat a été effectué dans le cadre d'un contrat avec le fournisseur ou au contraire si c'est un achat simple hors contrat (comme si vous alliez acheter votre baguette de pain quotidienne chez le boulanger).
Pour faire plus simple : J'ai 3 classeurs Excel et j'ai besoin de filtrer et fusionner les informations en un seul classeur.
- J'ai d'une part un classeur qui me donne toute la liste des achats sur l'année avec le fournisseur, le montant et la catégorie du produit
- Un classeur qui classe les catégories de produit par familles
- Un classeur qui liste tous les contrats en achat
Petite précision sur le dernier classeur : Celui-ci regroupe TOUS les fournisseurs avec lesquels tout notre groupe est en contrat. Le but ici est tout simplement de repérer les fournisseurs avec lesquels nous sommes en contrat et de l'inscrire dans le classeur principal.
L'objectif ensuite serait d'en faire des macros, dans la mesure du possible...
Est-ce assez clair ? N'hésitez pas à me demander des détails, si je parviens à accomplir cette tâche j'ai de bonnes chances que l'on me propose un CDI
Si besoin j'essayerai d'upload une copie d'écran de chaque classeur avec des fausses données à l'intérieur pour illustrer un peu mieux ma requête.
Bonne journée à tous et merci d'avance à ceux qui voudront bien m'aider !
Romain
Bonjour
Sur 2016 la meilleure approche serait PowerQuery intégré et non VBA
Avec des copies d'écran qui sont des images, on ne peut rien faire.
Donc poste 3 exemples représentatifs de tes classeurs avec des données bidons mais cohérentes.
Bonjour Romarin,
J'espère pour toi que ton potentiel futur employeur ne vienne pas sur le forum dans ce cas
Merci pour vos réponses !
Voici deux classeurs Excel avec des données "bidons" censées représenter respectivement :
- Le classeur qui me donne toute la liste des achats sur l'année avec le fournisseur (colonne E sous l'intitulé "Libellé tiers"), le montant et la catégorie du produit. La colonne en jaune "CC/HC" est censée accueillir l'information disant si l'achat est effectué dans un contrat (CC) ou hors contrat (HC).
- Le classeur qui donne la liste de tous les fournisseurs qui sont en contrat avec nous (j'ai volontairement ajouté le fournisseur "BABAROAS" dedans pour le test).
Toutes les données ont été remplacées et je n'ai laissé que 10% des listes (du classeur 1 et du classeur 2).
Merci pour votre aide !
PS : J'ai volontairement omis de mettre le classeur qui catégorise les catégories par "famille", par manque de temps mais si je sais comment fusionner les deux premiers, je devrais pouvoir me débrouiller pour le troisième, en théorie
RE
Je trouve dangereux de croiser des tables sur un libellé...
Voir l'onglet paramètres avant d'actualiser (Données, Actualiser tout)
Il faudrait supprimer les 2 colonnes du classeur achat puisqu'on les récupère par croisement (j'ai mis CC/HC2 pour l'instant)
RE
Merci d'éviter les MP : le forum est là pour les échanges...
Je te conseillais de modifier les coordonnées des classeurs à utiliser dans l'onglet paramètres
L'as-tu fait ? Si non commence par cela avec les classeurs que tu as envoyés.
Si la structure des vrais classeurs est similaire, tu pourras ensuite tester avec les chemins et noms des vrais...
Je n'ai jamais vu le message "impossible, les combinaisons ne sont pas bonnes" ...
Bonjour Chris,
Bien vu ! Merci pour ta réponse le fournisseur BABAROAS est bien catalogué comme CC ! Superbe !
Peux-tu me dire comment tu as procédé ? As-tu utilisé PowerQuery ?
Merci beaucoup !
Au temps pour moi, j'ai cru que c'était en Actualisant tout que les classeurs avaient fusionné mais visiblement la colonne CC/HC2 était déjà préremplie lorsque je l'avais téléchargé.
En fait pour faire simple j'aimerais arriver à ton résultat avec mes deux classeurs de base sans avoir à remplir la colonne CC/HC au cas par cas, mais d'automatiser la procédure en en quelques clics. Peut-être en faisant des macros suite à la fusion des deux classeurs ?
Merci pour ton aide !
RE
Non c'est la requête PowerQuery qui a rempli CC/HC2 (tu devrais relire plus attentivement mes posts et regarder les deux fichiers que tu as transmis...
Quand à l'autre colonne sans le troisième classeur je ne pouvais la remplir
Pourtant quand je retélécharge ton classeur, toujours avec les anciens paramètres (T:\TEMP\classeur 1.xlsx
T:\TEMP\classeur 2.xlsx) la colonne CC/HC2 apparaît quand même sans avoir exécuté quoi que ce soit.
C'est peut-être normal tu me diras, seulement je ne sais pas comment fonctionne un PowerQuery, peux-tu m'en dire un peu plus dans notre cas ? J'ai cru comprendre qu'en mettant les bons chemins d'accès et en actualisant tout la première fois, j'utilisais des "données externes", je ne sais de quoi il s'agit en fait.
Je t'envoie le troisième classeur demain pour un test
Merci du temps que tu prends!
RE
Tant que tu n'actualises, les valeurs générées sur mon PC restent affichées...
C'est comme un TCD : c'est l'actualisation qui provoque le recalcul et les erreurs s'il ne trouve pas les données.
PowerQuery est un outil qui combine les avantages des bases de données et notamment le croisement de tables par des requêtes et ceux du tableur en proposant pas mal de fonctions et facilités proches d'Excel.
Tes 2 classeurs ne sont pas copiés dans le classeur de synthèse, ce sont donc des données externes mais de façon générale Excel considère les requêtes stockées dans PowerQuery comme des données externes au classeur.
Microsoft a des pages présentant PowerQuery.
RE
Tant que tu n'actualises, les valeurs générées sur mon PC restent affichées...
C'est comme un TCD : c'est l'actualisation qui provoque le recalcul et les erreurs s'il ne trouve pas les données.
PowerQuery est un outil qui combine les avantages des bases de données et notamment le croisement de tables par des requêtes et ceux du tableur en proposant pas mal de fonctions et facilités proches d'Excel.
Tes 2 classeurs ne sont pas copiés dans le classeur de synthèse, ce sont donc des données externes mais de façon générale Excel considère les requêtes stockées dans PowerQuery comme des données externes au classeur.
Microsoft a des pages présentant PowerQuery.
Je crois que c'est un peu plus clair, merci. J'ai d'ailleurs tenté de faire moi-même une combinaison PowerQuery avec 3 classeurs mais apparemment je n'y parviens pas car
Voici le troisième classeur
En l'occurrence il désigne une Famille de produit pour chaque fournisseur.
comme la liste des fournisseurs est incomplète, c'est normal que certaines lignes fournisseurs soient vides.
Thanks !
Oui c'est normal
Impec ! Est-ce que tu sais comment je peux l'incorporer à mes classeurs?
RE
Comment cela l'incorporer ?
Cela fait une synthèse des 3 classeurs qu'on laisse en dehors... d'où la légèreté du classeur
Pardon je voulais dire : Comment récupérer ta formule et l'incorporer au classeur qui contient les bons nombres et les bons libellés?
Est-ce qu'il suffit de remplacer les chemins d'accès par les vrais classeurs?
OUI
J'ai donc repris ton classeur et j'y ai mis les chemins d'accès des bons classeurs.
Malheureusement lorsque je clique sur "Actualiser tout", ce message apparaît :
As-tu des informations? Je précise que suite au message d'erreur j'ai changé l'extension du classeur2 de xls à xlsx mais le message est toujours le même lorsque j'actualise le tout..
Bonjour,
Il ne faut pas changer l'extension du fichier.
Il faut l'ouvrir et enregistrer sous .xlsx.
Cdlt.