XL 2016 FUSION DE PLUSIEURS FICHIERS EXCEL DIFFERENTS

Menstru LH

XLDnaute Nouveau
Bonjour à tous,

Je bloque ! Malgré les heures de recherche sur internet et différents forums, je reconnais bloquer :(

Mon problème est simple mais techniquement (pour moi) compliqué !

J'ai globalement 1500 fichiers .xls (tous enregistrés de la même façon sous une version excel 1997-2003).
Ces 1500 fichier sont à peu près les mêmes mais pas EXACTEMENT !

J'aimerai pouvoir fusionner tous ces fichiers et ne récupérer globalement que quelques lignes qui m'intéressent.

En clair, je rêve d'avoir un fichier RESULTAT qui vient récupérer les lignes 9 à 25 de tous mes fichiers et m'affichent ces résultats seulement que dans une seule feuille :)

Vous pensez que c'est envisageable ?

Un grand merci d'avance

ps : je joins deux fichiers exemple pour comprendre mon problème

Bonne soirée
 

Fichiers joints

chris

XLDnaute Barbatruc
Bonjour

Pour ma part je m'interroge sur la finalité : cela équivaut à mettre les en-tête de 1500 factures (et encore tronquée car dans l'un des exemple l'adresse du client est en ligne 8) bout à bout mais cela fera un résulta assez peu exploitable...

Donc si tu nous disais plutôt la finalité, on pourrait peut-être sortir autre chose qu'un magma infâme...
 

Menstru LH

XLDnaute Nouveau
Bonjour @chris et bonjour la communauté,

Alors la finalité est simple : j'ai besoin de récupérer les données YYY, la date, et l'encadré qui donne le type de prestation (zingerie, étanchéité, etc.) des 1500 factures. Une fois que j'ai ces données nettoyées, je peux faire des TCD pour analyser les typologies de chantiers menés.

Seul hic, ces informations ne sont pas toujours exactement au même endroit dans les fichiers Excel (parfois, la date peut être à la ligne 15 et parfois ligne 16).
Du coup, mon idée était de se dire "je prends une plage de plusieurs lignes pour avoir un premier filtre et ensuite je trierai"

Qu'en pensez-vous ?

Merci
 

chris

XLDnaute Barbatruc
Re

Poste 3 fichers avec des noms, dates bidons mais pas des X et Y, ainsi qu'une synthèse de ce que tu vises au final.

Doit être faisable par PowerQuery intégré à Excel 2016
 

chris

XLDnaute Barbatruc
Bonjour

le souci c'est que la structure de la facture est complètement différente de l'une à l'autre :
  • dans l'une le libelle N° FACTURE est en colonne A dans l'autre en B
  • dans l'une les libellés HT et TTC en A das l'autre en G
  • dans l'une l'objet des travaux est sous la ligne des en têtes dans l'autre au-dessus
As-tu vérifié si selon les périodes il y a une homogénéité des factures d'une même période ?

Que l'on utilise VBA ou POWERQUERY il faut trouver une logique. Pêcher les infos précédées d'un intitulé est faisable mais pour les autres il faut un repère topologique...
 

Menstru LH

XLDnaute Nouveau
Rebonjour @chris

Je viens de vérifier, globalement j'ai deux structures de fichiers où je peux trouver une période pour le fichier 1 et une autre période pour le fichier 2

Donc à mon avis, l'exercice serait du coup double ?! Et dans ce cas là est-ce que des fonctions simples de combiner des fichiers sur Excel seraient envisageables ?

Merci
 

chris

XLDnaute Barbatruc
Re

Ci-joint un exemple PowerQuery sur le cas de l 'année 2013

Met tous tes fichiers (ou pour commencer une dizaine) de ce type dans un dossier.
Ouvre le classeur joint, modifie le chemin puis Données Actualiser Tout et dis ce que tu obtiens
 

Fichiers joints

zebanx

XLDnaute Accro
Bonjour Chris, Menstru LH

La solution POWERQUERY est surement la meilleure.
Une solution sur vieux "coucou" (excel 2003) en prenant l'un des fichiers comme vous le verrez dans le "zip".

Idée directrice (macros dans le master qui est le fichier RECAP_V1)
1- on incorpore toutes les factures (vu le nombre, il faudra surement prévoir plusieurs répertoires) par une MACRO.
L'incorporation écrase toutes les autres factures dans le fichier RECAP_V1, on repart de 0 à chaque fois pour éviter d'avoir des doublons.
2- on reprend dans une feuille RECAP toutes les valeurs en "jaune" sur chaque feuille.

Remarques :
- Il faut dézipper le dossier
- Que le fichier "recap" soient dans le même répertoire que les factures (attention, la boucle travaille sur tous les fichiers XLS")

Bien évidemment :

- Comme les fichiers de factures présentent deux modèles, on pourrait avoir une présentation différente des résultats dans la colonne RECAP (il n'y a pas d'ordre, la boucle descend dans chaque feuille..)

- Il faut colorier toutes les zones de recherche dans chaque facture.
Ce serait possible de le faire assez automatiquement du moment que les cellules en jaune sont TOUJOURS les mêmes. Ici j'ai fait 3 copies de la facture donc évidemment ça fonctionne. Mais si la présentation est différente, ça pourrait prendre du temps... donc il faut déjà faire un travail de recherche.
---> on peut imaginer pour ces factures là que les lignes 1 à 22 sur chaque facture ne posent pas de problème mais que pour les 3 dernières cellules jaunes, cela dépend du nombre de lignes remplies pour les travaux effectués. On pourrait avoir un total HT soit en "G47" mais parfois en "G40", parfois en "G60".
Il est possible peut-être de faire une macro, mais ça dépend donc d'une présentation homogène des factures (et ça c'est votre travail de vérification et de connaissance de vos fichiers):cool:


Voilà, si ça vous intéresse. Mais encore une fois, faites d'abord la demande de Chris. Je n'ai pas powerquery mais ça à l'air pratique dans votre cas.

@+
zebanx
 

Fichiers joints

Dernière édition:

Menstru LH

XLDnaute Nouveau
Bonjour @chris,

Merci pour ta réponse et ton fichier, j'ai remplacé le chemin d'accès et voilà le message :

[Expression.error] Désolé...nous n'avons pas trouvé la colonne "facture" de la table

Qu'ai-je mal fait ?

Mecri

ps : merci également à Zebanx pour ta suggestion et tes astuces
 

zebanx

XLDnaute Accro
Bonjour à Menstru LH le forum

J'ai un peu avancé de mon côté sur un sujet qui m'intéresse avec de si grandes bases.

En respectant les contraintes suivantes :
- les fichiers doivent avoir une présentation homogène (vous avez 2 types de factures mais je ne me suis basé que sur une facture) donc les données sont dans les mêmes cellules à chaque fois, du moins celles qui vous intéressent (nom client, adresse, n° facture, type de travaux, montant H.T., montant T.V.A, montant T.T.C.)
- C'est particulièrement vrai pour les champs compris entre les lignes 8 à 22 (du nom du client jusqu'à la nature des travaux)
- plus de couleurs à mettre par contre, contrairement à post #10 (intégré dans un code "couleur" sur le fichier de récupération)

Les différents codes permettent :
- de reprendre les informations souhaitées, sauf erreur
- de les remplir les unes à la suite des autres

Pour voir tout cela, il faut dézipper le fichier joint et l'enregistrer où vous voulez, sans rajouter de fichiers pour tester.

La macro principale boucle sur TOUS les fichiers xls du même répertoire. Si vous voulez les enregistrer par lot (0 à 200 par exemple, puis 201 à 400...), il faudra prévoir de déplacer les fichiers déjà enregistrés dans un répertoire de stockage.

Vu le temps pour enregistrer 4 factures (<0.5 secondes) , ça ne devrait pas prendre trop de temps...(enfin je trouve par rapport à la masse de fichiers XLS du départ).
Et la deuxième présentation devrait pouvoir se faire sans trop de problème.

@+
 

Fichiers joints

Dernière édition:

chris

XLDnaute Barbatruc
Bonjour à tous
Bonjour @chris,
Merci pour ta réponse et ton fichier, j'ai remplacé le chemin d'accès et voilà le message :
[Expression.error] Désolé...nous n'avons pas trouvé la colonne "facture" de la table
Le traitement cherche le libellé "FACTURE N°" pour récupérer le numéro qui suit.

Il doit manquer dans un ou n fichier
 

Menstru LH

XLDnaute Nouveau
Bonjour Chris,

Mecri pour ton message, du coup, quelle manip je dois faire ? si j'ai bien compris il se peut qu'il manque un numéro dans une facture en particulier ?

Et éventuellement, ne pas récupérer le numéro de facture tu penses que c'est modifiable ? SI oui, de quel façon ?

Merci
 

chris

XLDnaute Barbatruc
Re

As-tu vérifié que la requête traite bien uniquement des fichiers du type de EX 1_v2 avec FACTURE N° en colonne A ?

Comme déjà dit, que ce soit par PowerQuery, en VBA ou autre il faut une logique : je me suis basé sur l'exemple fourni pour créer une 1ère moulinette.
La seconde viendra si déjà la 1ère fonctionne...
 

zebanx

XLDnaute Accro
Bonjour à Chris, MenstruLH

@chris :
Pourrais-tu stp m'indiquer, quand le sujet sera traité, combien de temps il faut approximativement :
- pour paramétrer les champs de power query (en considérant que les données ne sont pas présentées de manière homogène par colonne ce qui doit t'obliger à paramétrer plus "manuellement")
- quelle est la durée de traitement pour les 1500 factures ? (ou pour un lot de 50 ou 100 factures pour me donner une idée du temps de passage / VBA).

Je te remercie par avance pour tes précisions.

@MenstruLH : les retours à tes demandes sont quand même rapides, c'est un peu "lourd" :confused: d'attendre parfois longtemps pour avancer sur ce fil vraiment intéressant. Et t'aurais pu me répondre au post #12 il me semble...;).
 

Menstru LH

XLDnaute Nouveau
Bonjour @chris, bonjour @zebanx

Excusez pour mon retard à nouveau, des soucis persos...Mais ca y'est ! Maintenant je suis revenu à fond et je vais pouvoir être actif autant que vous l'êtes et je vous en remercie sincèrement d'ailleurs pour votre précieuse aide ...

@chris : OK pour te remarque sur le requête, ma question est du coup : comment je peux faire pour vérifier que la requête est bonne ? Car j'ai vérifié les fichiers intégrés et j'ai bien FACTURE N° en colonne A pour l'ensemble de mes fichiers

@zebanx : je viens de tester ta macro, elle est géniale !!! je vais faire un essai sur le premier type de fichier que j'ai (environ 1000 factures). Pour le second type, la structure n'étant pas la même, je dois modifier une partie du code j'imagine ?

Encore merci messieurs pour votre aide et surtout encore désolé pour mon retard (qui ne se reproduira plus)
 

Menstru LH

XLDnaute Nouveau
@zebanx : je viens de tester ta macro, elle est géniale !!! je vais faire un essai sur le premier type de fichier que j'ai (environ 1000 factures). Pour le second type, la structure n'étant pas la même, je dois modifier une partie du code j'imagine ?
@zebanx j'ai essayé de remplacer les fichiers F001, F002, etc. par les vrais fichiers et rien ne s'affichent...j'imagine que pour le test, il n'y a pas possibilité de remplacer les fichiers F001, F002, mais ma question porte plutôt sur la codification de mes factures. En effet, celles-ci n'ont pas du tout la même codification (le nom peut être F001 mais aussi FACT100 ou CLIENT1008) et je me demande si ça ne vas pas poser un problème ?!

Merci pour ton retour
 

zebanx

XLDnaute Accro
Bonjour Menstru-LH,

Je ne vois pas pourquoi il y aurait un problème (extension de fichier testé par exemple).
La seule chose, c'est que le code va colorier des zones bien précises donc je l'ai fait sur 1 type de facture ("EX1 V2" du post #6) donc il faut paramétrer en conséquence.
Ma compréhension, c'est qu'il y a uniquement 2 types de présentation de factures et toutes homogènes dans leur construction (cf#12 sur les premières lignes qui doivent avoir les mêmes cellules utilisées et les 3 dernières (montant HT, TVA et montant TTC) qui peuvent être sur une ligne différente suivant la longueur des travaux détaillés effectués mais toujours dans la même colonne).
Si c'est beaucoup plus hétérogène que cela, le paramétrage sera trop difficile à mettre en place...
La codification des factures ne pose aucun problème il me semble.

Je pourrais avancer mais il me faut des renseignements supplémentaires :
- est-ce le cas : V1 et V2 sont les deux seules matrices utilisées ?
- vous me dites que ça ne fonctionne pas, pourriez-vous me zipper une dizaine de factures qui n'ont pas été intégrées svp (bidonnez les sommes, adresses...).

Et il faut quand même travailler avec Chris car sa solution est de toute manière à terme la plus convaicante surement pour intégrer de nouveaux fichiers et qu'il serait utile de voir quelle est la méthode la plus rapide.
Pour information, on doit arriver chez moi pour 1500 factures entre 3 et 4 minutes de traitement le temps de balayer toutes les factures (estimé).

Dans l'attente de votre retour,
@+ zebanx
 
Dernière édition:

Menstru LH

XLDnaute Nouveau
Je peux le faire mais il me faut des renseignements supplémentaires :
- est-ce le cas : V1 et V2 sont les deux seules matrices utilisées ?
- vous me dites que ça ne fonctionne pas, pourriez-vous me zipper une dizaine de factures qui n'ont pas été intégrées svp (bidonnez les sommes, adresses...).

En effet V1 et V2 sont les deux seules matrices

Les factures ne s'intègrent pas : voilà la manip que j'ai faite : j'ai remplacé les 4 fichiers F00i par mes factures (dans le même répertoire) et lorsque dans le fichier RECAP_tvx je clique sur incorporer, j'ai aucune ligne qui s'incorpore !

Ci-joint, 4 factures pour comprendre

Merci :)
 

Fichiers joints

Créez un compte ou connectez vous pour répondre

Vous devez être membre afin de pouvoir répondre ici

Créer un compte

Créez un compte Excel Downloads. C'est simple!

Connexion

Vous avez déjà un compte? Connectez vous ici.

Haut Bas