Comment combiner de nombreux fichiers et tableaux

[Bastien43]

Bonjour,

Je suis débutant sur Excel. Je sollicite votre aide pour synthétiser des données.

Les données étant confidentielles, j'ai simplifié mon problème.

Voici le détail :

J'ai plusieurs fichiers Excel nommés CADI1, CADI2, CADI3. Chacun représente un CADI rempli par un client.

Chaque CADI possède une seule feuille et un tableau. Les tableaux sont de dimensions identiques.

Dans le tableau apparaît plusieurs sous-détails (en-tête en jaune en gras) : "Fruits, légumes, produits laitiers, etc. ". Chaque sous-détails est indépendant (exemple : un "fruit" ne peut pas aller dans le sous-détail "légume" ou "poisson"). Par contre, dans un même sous-détail peut apparaître plusieurs fois le même fruit (exemple : une "pomme" peut apparaître dans "Fruits" sur plusieurs lignes avec un montant et une quantité total qui diffèrent).

Je souhaite récupérer l'ensemble des données des 3 fichiers dans un seul tableau Excel "CADI Synthese" :
- éviter les doublons dans chaque sous-détails (la "pomme" apparaît une seule fois)
- sommer chaque colonne (ainsi pour la pomme apparaît : le montant total pour la "pomme", la quantité totale pour la "pomme").

Je récupère les données dans le tableau Syntèse à l'aide d'un bouton (macro?).

J'ai envisagé définir un tableau par sous catégorie (sous-tableau "Fruits", sous-tableau "Légumes" etc.) et ensuite combiner les données des ces nouveaux tableaux avec la fonction "obtenir des données..." etc.

Cependant, la difficulté est de regrouper plusieurs fichiers Excel externes.

A cela, j'ai donné l'exemple pour 3 fichiers CADI1 à CADI3 mais le nombre de fichiers peut varier de 1 à 100. Comment automatiser le processus en un seul clic sur le bouton "Synthèse des CADIS" ? Quelle est la macro correspondante ?

Je vous remercie pour votre aide. Je bloque depuis plusieurs jours sur le problème.

 

[Hasco]

Bonjour,

Dans le fichier joint vous verrez une feuille nommée 'Tout' qui contient un tableau avec tous les produits des feuilles CADI des classeurs se trouvant dans le même répertoire.

Cette extraction est faite au moyen de PowerQuery (inclus dans xl 2019). Elle prend pour source un répertoire dont vous devrez changer le chemin dans PQ (Etape 1 de la requête nommée 'Répertoire')
Comme vos tableaux sources ne sont pas des tableaux structurés, la fonction ExtraireDatas comporte des étapes qui seraient autrement inutiles.

Attention dans vos entêtes de colonne vous mettez des espaces traînants en fin de libellés ("Désignation " au lieu de "Désignation"). Là aussi cela oblige à des étapes de requêtes inutiles autrement.

Vous trouverez également une feuille TCD tiré du tableau de cette première feuille et qui vous totalise par Catégorie "Fruit, viandes etc." et produit.

Vous trouverez un tutoriel Microsoft sur la manière de combiner des données de plusieurs fichier par PQ en suivant ce lien : https://support.office.com/fr-fr/article/combiner-les-données-de-plusieurs-sources-de-données-power-query-70cfe661-5a2a-4d9d-a4fe-586cc7878c7d

 

[chatounet]

Bonjour Bastien43, bonjour Roblochon (humm)
Je propose de faire une consolidation.
chaque référence est la sélection des tableaux entiers p18:r182.
Le résultat dans le fichier joint.
Cordialement

 

[job75]

Bonjour,

humm chatounet, il semble manquer des choses...

A+

 

[Bastien43]

Bonjour,

Dans le fichier joint vous verrez une feuille nommée 'Tout' qui contient un tableau avec tous les produits des feuilles CADI des classeurs se trouvant dans le même répertoire.

Cette extraction est faite au moyen de PowerQuery (inclus dans xl 2019). Elle prend pour source un répertoire dont vous devrez changer le chemin dans PQ (Etape 1 de la requête nommée 'Répertoire')
Comme vos tableaux sources ne sont pas des tableaux structurés, la fonction ExtraireDatas comporte des étapes qui seraient autrement inutiles.

Attention dans vos entêtes de colonne vous mettez des espaces traînants en fin de libellés ("Désignation " au lieu de "Désignation"). Là aussi cela oblige à des étapes de requêtes inutiles autrement.

Vous trouverez également une feuille TCD tiré du tableau de cette première feuille et qui vous totalise par Catégorie "Fruit, viandes etc." et produit.

Vous trouverez un tutoriel Microsoft sur la manière de combiner des données de plusieurs fichier par PQ en suivant ce lien : https://support.office.com/fr-fr/article/combiner-les-données-de-plusieurs-sources-de-données-power-query-70cfe661-5a2a-4d9d-a4fe-586cc7878c7d

Bonjour,

Merci beaucoup pour votre aide. Je ne connaissais pas l’outil de Microsoft, c'est très puissant. J'ai pu résoudre le problème.

J'ai une autre question : si je déplace le dossier où je suis en train de travailler (dans lequel sont les fichiers), comment actualiser automatiquement le chemin d'accès ?

J'ai noté la présence de "Paramètres des sources de données" dans l'outils Power Query mais comment avoir un chemin d'accès "relatif" seulement au dossier et non au chemin d'accès complet (C:\users\...)

Au final, je pensais créer une macro et l'affecter à un bouton "Actualisation" pour actualiser le fichier...

Je vous remercie énormément pour votre apport,
Cordialement,
Bastien

 

[Bastien43]

Bonjour Bastien43, bonjour Roblochon (humm)
Je propose de faire une consolidation.
chaque référence est la sélection des tableaux entiers p18:r182.
Le résultat dans le fichier joint.
Cordialement

Bonjour,

Merci pour votre réponse. C'est une autre approche intéressante.

Je vais aussi travailler dessus.

Cordialement

 

[chatounet]

Bonjour à tout le monde

Job75, oui il évident qu'il manque des choses, je n'avais téléchargé que les 2 premiers fichiers (cadi1 et cadi2) pensant que cela suffirait pour le principe. Mea maxima culpa.
Mais peut-être qu'il est question d'autre chose ?

Bien à vous tous portez-vous bien

 

[job75]

Bonjour Bastien43, Roblochon, chatounet,

Téléchargez les fichiers joints dans le même dossier et voyez la macro du bouton :

Sub Synthese()
Dim plage As Range, chemin$, fichier$, d As Object, r As Range, i&, x$, n&, lig&
Set plage = [P20:R36,P38:R53,P55:R62,P64:R71,P73:R80,P82:R89,P91:R98,P100:R115,P117:R133,P135:R167,P169:R182]
chemin = ThisWorkbook.Path & "\" 'dossier à adapter
fichier = Dir(chemin & "*.xlsx") '1er fichier du dossier
Set d = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
plage.ClearContents 'RAZ
While fichier <> ""
    For Each r In plage.Areas
        r.Offset(, 3).FormulaArray = "='" & chemin & "[" & fichier & "]CADI'!" & r.Address(ReferenceStyle:=xlR1C1) 'formule de liaison matricielle
        For i = 1 To r.Rows.Count
            x = CStr(r(i, 4))
            If x <> "0" Then
                If Not d.exists(x) Then
                    n = Application.CountA(r.Columns(1)) + 1
                    d(x) = n 'mémorise la ligne
                    r(n, 1) = x
                End If
                lig = d(x)
                If IsNumeric(r(i, 5)) Then r(lig, 2) = r(lig, 2) + r(i, 5)
                If IsNumeric(r(i, 6)) Then r(lig, 3) = r(lig, 3) + r(i, 6)
            End If
    Next i, r
    fichier = Dir 'fichier suivant du dossier
Wend
plage.EntireColumn.Offset(, 3).ClearContents 'RAZ des colonnes S T U auxiliaires
End Sub

Comme on le voit elle utilise des formules de liaison (matricielles).

Je l'ai testée avec 100 fichiers sources, elle s'exécute chez moi en 3 secondes environ.

C'est assez rapide mais certainement moins qu'avec Power Query.

A+

 

[chatounet]

Bonsoir à toutes, tous

Toujours aussi formidable JOB75

Portez-vous tous bien et soyez heureux

 

[job75]

La macro précédente ne va pas bien, il faut commencer par boucler sur les plages :

Sub Synthese()
Dim plage As Range, chemin$, d As Object, r As Range, n&, fichier$, i&, x$, lig&
Set plage = [P20:R36,P38:R53,P55:R62,P64:R71,P73:R80,P82:R89,P91:R98,P100:R115,P117:R133,P135:R167,P169:R182]
chemin = ThisWorkbook.Path & "\" 'dossier à adapter
fichier = Dir(chemin & "*.xlsx") '1er fichier du dossier
Set d = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
plage.ClearContents 'RAZ
For Each r In plage.Areas
    d.RemoveAll 'RAZ
    n = 0
    fichier = Dir(chemin & "*.xlsx") '1er fichier du dossier
    While fichier <> ""
        r.Offset(, 3).FormulaArray = "='" & chemin & "[" & fichier & "]CADI'!" & r.Address(ReferenceStyle:=xlR1C1) 'formule de liaison matricielle
        For i = 1 To r.Rows.Count
            x = CStr(r(i, 4))
            If x <> "0" Then
                If Not d.exists(x) Then
                    n = n + 1
                    d(x) = n 'mémorise la ligne
                    r(n, 1) = x
                End If
                lig = d(x)
                If IsNumeric(r(i, 5)) Then r(lig, 2) = r(lig, 2) + r(i, 5)
                If IsNumeric(r(i, 6)) Then r(lig, 3) = r(lig, 3) + r(i, 6)
            End If
        Next i
        fichier = Dir 'fichier suivant du dossier
    Wend
Next r
plage.EntireColumn.Offset(, 3).ClearContents 'RAZ des colonnes S T U auxiliaires
End Sub

Fichier (2), avec 100 fichiers sources la durée d'exécution est toujours de 3 secondes.

 

[Bastien43]

Bonjour job75,

Je viens d'adapter la macro à mon fichier et tout fonctionne à la perfection. Merci énormément pour votre aide.

J'utilise les tableaux croisés dynamiques de PowerQuery. Cela fonctionne. Je dois bien penser à cliquer 2 fois sur "actualiser tout" pour récupérer l'ensemble des données, sinon tous les tableaux ne se mettent pas à jour...

Avec votre macro, le résultat est rapide et exact dès le premier clic.

Encore merci,
Cordialement
Bonne journée