Somme de cellules à partir de plusieurs fichiers

Bonjour,

Tous les mois j'ajoute des classeurs Excel dans un répertoire et j'ai besoin d'extraire des données de ces classeurs pas mois et d'en faire la somme.

________________________
Pour simplifier le problème considérons que je dispose des classeurs :
Gr101-01.xlsx et Gr102-01.xlsx pour le mois de janvier
et les fichiers Gr101-02.xlsx et Gr102-02.xlsx pour le mois de février
et que chaque fichier a une seule valeur dans la cellule A1.

J'ai également un classeur récapitulatif.xlsx dans lequel je souhaite créer un tableau à 2 colonnes,
La première colonne avec le nom de chaque mois de l'année et la seconde colonne avec la somme des valeurs en A1 des classeurs[Feuil1] correspondant au mois (01 pour janvier, 02 pour février etc...)

Bien sûr j'aimerais que ça se remette à jour dès que j'ajoute les fichiers du mois de mars puis avril etc ....
________________________

J'ai essayé une approche Power Query mais j'ai des difficultés dans le traitement des noms de fichiers (pour savoir quels sont fichiers pour janvier etc...), ensuite j'ai pensé que je pouvais m'en sortir avec des références externes en mettant dans la case janvier de mon tableau récapitulatif :
='C:\Users\Utilisateur\Documents\Excel\Test\Months\[GR101-01.xlsx]Feuil1'!A1+'C:\Users\Utilisateur\Documents\Excel\Test\Months\[GR102-01.xlsx]Feuil1!A1
puis même chose pour la case février avec les fichiers GR101-02.xlsx ....
mais je dois ajouter les références pour les 12 mois alors que les fichiers n'existent pas encore (avril, mai, juin ...) donc je suis bloqué.

Je voulais votre avis sur ces 2 méthodes, peuvent-elles aboutir ou bien dois-je le faire en VBA ?
Merci de votre aide.

Bonjour,

Proposition en VBA:

Le fichier "récupération.xlsm" contient 2 feuilles,

-1 feuille "Liste" où vous mettrez le bon chemin en A1.

-1 feuille "SYNTHESE" où vous cliquerez sur le bouton pour rapatrier les données.

Cdlt

Bonjour à tous !

Et....

Votre approche initiale via Power Query me semble particulièrement adaptée pour traiter votre besoin.

Cependant votre message est lacunaire...

1. Comment gérez-vous le changement d'année ? Avec un répertoire différent ? Via une cellule du classeur ? Faut-il, le cas échéant, agréger pour un mois donné les valeurs de plusieurs années ?
2. Pour une modélisation fiable, il est nécessaire de joindre un classeur exemple anonymisé mais représentatif de votre réalité. (je doute que vos classeurs ne comportent qu'une valeur unique et ce sans aucune dénomination de champ)
3. Faut-il réellement n'agréger qu'une valeur ?

Bonsoir et merci beaucoup pour vos réponses,

cela m'a permis d'avancer et de progresser sur les 3 méthodes proposées mais j'ai encore du travail et quelques questions.

___________________________

1ère méthode : Références externes

J'ai quand même mis toutes les références externes dans mon fichier récapitulatifs, et pour les fichiers existants ça fonctionne très bien, lorsque je change une valeur dans les fichiers sources la MAJ est instantanée !!!

Et pour les fichiers qui ne sont pas (encore) présents, les cellules affichent #REF mais ce n'est pas gênant dans ce cas je mets simplement 0 (à l'aide de la fonction ESTERREUR(...)) et ça me permet de continuer mes calculs (qui ne sont pas complets tant que les fichiers du mois correspondants ne sont pas là mais ce n'est pas grave).

Par contre le souci est que lorsque je supprime des fichiers sources je m'attendais également à une MAJ instantanée et à voir apparaitre #REF dans les cellules qui n'ont plus de liens mais non ça ne marche pas, la valeur qui avait été calculée précédemment reste. J'ai essayé de fermer et rouvrir le classeur mais ça ne change rien sauf qu'il m'avertit que des liens sont erronés et m'ouvre une boite de dialogue pour que je renseigne les emplacements des fichiers mais même en fermant cette boite la valeur numérique reste alors que je voudrais voir #REF (pour pouvoir mettre 0 à la place et continuer les calculs).

___________________________

2ème méthode : VBA

Merci Arturo, je me suis fortement inspiré de ton exemple et j'ai un code qui fonctionne. :

Par contre j'aimerais améliorer le code sur 2 aspects :

- Peut-on se passer du bouton et mettre directement un appel du code VBA dans les cellules ? De cette façon ça se remettrait à jour dès que l'on ouvre le classeur sans avoir à appuyer sur le bouton.

- Peut-on accéder aux fichiers sources sans les ouvrir, dans mon cas il s'ouvrent brièvement et ça donne un côté clignotant (que mon responsable va trouver pénible je pense), s'il y avait une option "lire les fichiers en mode silencieux" ça éviterait ça et ça accélérerait le code que je trouve un peu long (environ 2 secondes par fichiers ça fera pas loin de 2 minutes quand tous les fichiers seront présents en décembre, mais les fichiers sont sur le réseau et non pas en local sur mon poste alors c'est peut-être normal que ça soit lent).

___________________________

3ème méthode : Power Query

Merci JFL, oui ce sont de bonnes remarques, effectivement j'ai recréé chez moi pour travailler dessus le soir et j'ai juste conservé ma difficulté.

Au boulot les classeurs sont rangés dans des dossiers 2024, 2025 mais nous faisons nos études sur une seule année donc pas d'agrégation à faire. Mais disons que dans mon exemple on travaille uniquement sur les données de 2025 et l'on possède les fichiers pour les 4 premiers mois.

Effectivement les classeurs réels ont plein de valeurs et mon problème réel est d'additionner les valeurs d'une cellule (disons A1) de chaque fichier et parallèlement d'additionner les valeurs d'une autre cellule (disons C1) de chaque fichier, à la fin j'obtiens donc 2 sommes pour chaque mois, mais j'ai pensé que si j'y arrive pour une seule somme alors j'arriverai à adapter mon problème.

J'ai bien creusé la méthode Power Query (j'ai découvert cet outil il y a quelques jours seulement et je commence à voir toutes les possibilités qu'il peut offrir) j'arrive à quelque résultats mais j'ai 3 soucis :

1. Dans le répertoire de fichiers sources il y a également d'autres classeurs Excel qui n'ont pas le même schéma (par les mêmes noms de feuilles ni les mêmes colonnes), lors de l'import du répertoire je pensais pouvoir filtrer en fonction des noms de classeurs mais je n'y arrive pas et j'ai lu sur internet qu'il fallait absolument que tous les classeurs du répertoire aient le même schéma, quelqu'un peut me confirmer ça ?

En ne conservant que des classeurs au même format j'arrive (presque) au résultat voulu mais je ne suis pas sûr de bien m'y prendre.

2. Déjà je supprime les lignes et colonnes pour ne conserver que les 2 cellules qui m'intéressent mais ça ne me semble pas bon, j'ai vu qu'il y avait la fonction drill-down pour extraire la valeur d'une cellule (ou 2 dans mon cas réels) mais je n'ai pas réussi.

3. Ensuite, après avoir fait mes regroupements avec fonctions d'agrégation SOMME en fonction du numéro de mois j'obtiens un tableau comme ceci

---------------------

Mois | Valeur

1 | 289

2 | 256

3 | 267

4 | 244

j'aimerais compléter ce tableau avec les lignes correspondants aux mois de 5 à 12 et en mettant des valeurs 0 sur la colonne valeur mais je n'y arrive pas de façon efficace.

J'ai quand même réussi à créer une seconde requête avec les lignes de 5 à 12 et valeur 0 puis à combiner les 2 requêtes #"Requête ajoutée" = Table.Combine({#"Colonnes renommées", Requête1}), ça concatène les 2 tableaux et j'obtiens bien un tableau à 12 lignes mais j'ai entré manuellement la valeur 5 alors que j'aurais dû la déduire de la taille du tableau initial de 4 lignes donc ça ne va pas, je pense qu'il ne faut pas créer une seconde requête mais qu'il faut ajouter les lignes directement dans la première requête mais je ne vois pas comment faire.

__________________________

Si vous avez des suggestions concernant mes questions sur ces différentes méthodes je suis fortement preneur !

J'ai adoré travailler là-dessus, ce sont des technologies toutes nouvelles pour moi et qui me dépassent encore mais je pense que je pourrais vite devenir addicte.

A bientôt, bonne soirée

EDIT. Désolé j'oubliais un autre point qui me bloque.

Lorsque j'insère une table à partir de Power Query il me dit que je dois choisir un autre emplacement car il y a déjà une table en XML, mais comment faire, car à chaque fois que je vais ouvrir mon fichier il va me dire qu'il y a déjà un tableau et ça va coincer ?

Bonsoir à tous !

1/ Filtrage des classeurs du répertoire.

Pour cerner les fichiers à traiter, il existe de multiples manières. Cette opération est grandement facilitée quand il existe une convention de nommage des fichiers, ce qui semble être le cas ici : extension particulière, nom des fichiers débutant par "Gr" (la casse est importante), présence d'un caractère ("-") à une position fixe, longueur fixe du nom, etc....

2/ Extraction et restitution des données.

Le mieux est que vous fournissiez 2 classeurs anonymisés afin que l'on puisse bâtir une requête fonctionnelle.

Si j'ai bien intégré votre besoin, il s'agit d'agréger mensuellement deux valeurs (A1 et C1) sur la base de deux fichiers pour un mois donné. Vous confirmez ?

Bonjour à tous,

Bien que power query soit plus adapté pour ce genre d'exercice, je réponds quand même à votre demande.

-La mise à jour se fait à l'ouverture du fichier.

-Pour éviter le scintillement à chaque chargement de fichier, on rend invisible le fichier tant que tout n'est pas chargé, ce qui ne changera rien à la vitesse d'exécution qui elle, dépend de la puissance du pc, chez-moi c'est très rapide.

Cdlt

Bonjour à tous et un grand merci pour vos réponses,

Effectivement j'ai le choix des technologies à utiliser (VBA ou Power Query) mais j'aimerais bien réussir à faire les 2 (pour continuer à apprendre).

_____________________________________

Arturo83,
Je m'étais inspiré de votre code mais je l'avais un peu trop changé alors je l'ai bien repris et cette fois-ci :
- je n'ai plus l'effet clignotement
- le code se lance tout seul à l'ouverture du fichier (grâce au Workbook_Open())
C'est super
et sinon j'ai ajouté du code pour :
- reconstruire le nom des classeurs plutôt que de les écrire dans la feuille "Liste",
- éviter que les valeurs soient ajoutées 2 fois, 3 fois ... lorsqu'on réouvre le classeur 2 fois, 3 fois ...

Niveau vitesse, sur mon ordinateur à la maison c'est en effet très rapide (pourtant je n'ai pas un super PC) mais au boulot c'était très lent, je vais remodifier mon code demain et on verra.

J'ai ajouté mon classeur avec les 2 dernières modifications ajoutées, ça fonctionne comme je souhaite mais c'est sûrement pas du code bien écrit, si un expert veut bien jeter un oeil ce serait super, merci d'avance.

Sinon j'ai un autre souci c'est que j'ai des fichiers excel vides qui s'ouvrent et que je ne peux pas fermer, la seule méthode que j'ai trouvé pour les fermer est d'aller dans le gestionnaire des tâches et de fermer Excel mais ça m'embête je ne vois pas d'où ça vient, je suis quasi sûr que ça vient de mes tests en VBA.

_____________________________________

JFL,
Oui je confirme il s'agit d'agréger toutes les valeurs A1 ensemble pour le mois de janvier puis février et ensuite toutes les valeurs C1 pour le mois de janvier puis février ...

A la fin je dois obtenir un tableau comme ceci (valeurs fictives) :

Effectivement nous avons une convention de nommage assez claire mais il y a aussi dans le répertoire des fichiers avec d'autres noms et que l'on doit ignorer dans la présente tâche et qui n'ont pas le même schéma (nom de feuilles différents)
Là par exemple j'ai ajouté un fichier fictif "text.xlsx" que je veux ignorer lors de l'import :

Pour ça j'ai fait un filtre sur les lignes mais j'ai dû aller sur la roue dentelée, je trouve étrange qu'il ne me propose pas directement cette option lors de l'import.

Bon mais finalement j'arrive (presque) à ce que je souhaite, je fais mes groupBy par mois en sommant les valeurs A1 d'une part et C1 d'autre part.

Finalement il me reste à ajouter les lignes de 5 à 12 (pour compléter mon tableau) puis changer les numéros de 1 à 12 en noms de mois.
Donc voici ma requête finale avec la liste d'instructions :

let
    Source = Folder.Files("C:\Users\Utilisateur\Documents\Excel\2025"),
    #"Fichiers masqués filtrés1" = Table.SelectRows(Source, each Text.StartsWith([Name], "GR")),
    #"Appeler une fonction personnalisée1" = Table.AddColumn(#"Fichiers masqués filtrés1", "Transformer le fichier", each #"Transformer le fichier"([Content])),
    #"Colonnes renommées1" = Table.RenameColumns(#"Appeler une fonction personnalisée1", {"Name", "Source.Name"}),
    #"Autres colonnes supprimées1" = Table.SelectColumns(#"Colonnes renommées1", {"Source.Name", "Transformer le fichier"}),
    #"Colonne de tables développée1" = Table.ExpandTableColumn(#"Autres colonnes supprimées1", "Transformer le fichier", Table.ColumnNames(#"Transformer le fichier"(#"Exemple de fichier"))),
    #"Autres colonnes supprimées" = Table.SelectColumns(#"Colonne de tables développée1",{"Source.Name", "Column1", "Column3"}),
    #"Colonnes renommées" = Table.RenameColumns(#"Autres colonnes supprimées",{{"Column1", "A1"}, {"Column3", "C1"}}),
    #"Plage de texte extraite" = Table.TransformColumns(#"Colonnes renommées", {{"Source.Name", each Text.Middle(_, 6, 2), type text}}),
    #"Lignes groupées" = Table.Group(#"Plage de texte extraite", {"Source.Name"}, {{"sumA1", each List.Sum([A1]), type number}, {"sumC1", each List.Sum([C1]), type number}}),
    #"Colonnes renommées2" = Table.RenameColumns(#"Lignes groupées",{{"Source.Name", "NumeroMois"}})
in
    #"Colonnes renommées2"

Si vous pouvez m'éclairer sur :
- Comment correctement filtrer les fichiers lors de l'import,
- Comment compléter mon tableau pour qu'il contienne 12 lignes (ajouter les lignes de 5 à 12 si on est au mois de mai, mais ajouter les lignes de 10 à 12 si on est au mois d'octobre etc...)

J'ai ajouté un zip avec les fichiers mensuels (juste 2 valeurs en A1 et C1) ainsi qu'un fichier "test.xslx" qui ne sert à rien mais justement je veux l'ignorer dans mon import.

Merci beaucoup pour votre aide.
Bon dimanche et à bientôt.

Bonjour,

Pas de problèmes constatés lorsqu'un classeur est vide, il se ferme bien.

Voici votre code avec une légère modification:

Option Explicit

Sub Recup_Donnees()
    Dim f1 As Worksheet
    Dim Chemin As String, Classeur As String
    Dim i As Long, j As Long
    Dim Valeur As Double

    Application.Visible = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Set f1 = Sheets("Feuil1")
    Chemin = "C:\Users\Utilisateur\Documents\Excel\2025\"

    '**** Suppression du "Mask" **********************************

    On Error GoTo Sortie
    For i = 1 To 12
        For j = 101 To 102
            Classeur = "GR" & j & "-" & Format(i, "00") & ".xlsx" '****** Modification ici **********************************
            'Debug.Print "Classeur: " & Classeur

            If Dir(Chemin & Classeur) <> "" Then
                Workbooks.Open Filename:=Chemin & Classeur
                Valeur = Range("A1").Value
                ActiveWorkbook.Close
                'Debug.Print "Valeur: " & Valeur
                f1.Range("B" & 1 + i).Value = f1.Range("B" & 1 + i).Value + Valeur
            End If

            Valeur = 0
        Next j
    Next i
Sortie:
    On Error GoTo 0
    Application.Visible = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Cdlt

Bonjour à tous !

Slash07 a écrit :

- Comment correctement filtrer les fichiers lors de l'import,
- Comment compléter mon tableau pour qu'il contienne 12 lignes (ajouter les lignes de 5 à 12 si on est au mois de mai, mais ajouter les lignes de 10 à 12 si on est au mois d'octobre etc...)

1/ Filtre dès l'import

La fonction "lecture" des fichiers du répertoire de travail n'intègre pas la possibilité de filtrage.
Cette opération peut s'avérer très complexe et nécessite une fonction dédiée.

2/ Tableau complet.

Voir solution présentée dans le classeur joint.

Merci pour vos réponses très rapides,

__________________________________
Arturo83,

Merci pour les corrections, j'ai oublié dans mon dernier message d'ajouter ce que j'ai fait pour éviter que des valeurs soient ajoutées lors de la réouverture du classeur, finalement mon code ressemble à cela :

Option Explicit

Sub Recup_Donnees()
    Dim f1 As Worksheet
    Dim Chemin As String, Classeur As String
    Dim i As Long, j As Long
    Dim Valeur As Double, sumValue As Double

    Application.Visible = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Set f1 = Sheets("Feuil1")
    Chemin = "C:\Users\Utilisateur\Documents\Excel\2025\"

    On Error GoTo Sortie
    For i = 1 To 12
        sumValue = 0

        For j = 101 To 102
            Classeur = "GR" & j & "-" & Format(i, "00") & ".xlsx"

            If Dir(Chemin & Classeur) <> "" Then
                Workbooks.Open Filename:=Chemin & Classeur
                Valeur = Range("A1").Value
                ActiveWorkbook.Close
                sumValue = sumValue + Valeur
            End If

            Valeur = 0
        Next j

        f1.Range("B" & 1 + i).Value = sumValue
    Next i
Sortie:
    On Error GoTo 0
    Application.Visible = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Après quelques tests j'ai l'impression que le test "If Dir(Chemin & Classeur) <> "" Then" n'est pas utile et que s'il ne trouve pas un des fichiers alors il sort directement ce qui me va bien.

__________________________________

JFL,

Merci beaucoup, ça marche très bien !
Je ne connais pas (encore) le langage M alors je me contente d'utiliser les fonctionnalités de l'interface PWQ mais je crois que vous avez créé vos actions vous-même.

Déjà lorsque je fais mes manipulations PWQ à partir de l'interface cela me crée toujours les mêmes actions, notamment ça me me concatène toutes les tables ensemble à partir de la feuille que j'avais sélectionnée lors de l'import, alors que vous conservez les données sous forme d'une seule colonne qui (à priori contient tout tant qu'on ne lui a pas dit quoi extraire) et qui ressemble à ça :

Ensuite vous faites une extraction dans Record puis "Expand" pour finalement avoir les valeurs souhaitées, alors que moi je supprimais les lignes et colonnes pour seulement conserver ce qui m'intéressait, je suppose que tout ça fait partie des bonnes pratiques PWQ et que forcément je dois apprendre le langage M :).

Par contre j'ai du mal à comprendre l'instruction : Table.AddColumn(NomMois, "ClasseurXL", each Excel.Workbook([Content])[Data]{0})
je vois que cela ajoute une colonne nommée "ClasseurXL" qui contient pour chaque ligne (c'est-à-dire pour chaque fichier), le contenu de la première feuille (indice 0), mais je ne vois pas comment l'adapter si je veux choisir un nom de feuille précis, parce que moi dans l'interface PWQ me demande toujours le nom de feuille qui m'intéresse.

C'est vraiment bluffant, je me demande bien ce qu'on ne peut pas faire avec Power Query.

Bonsoir à tous !

Slash07 a écrit :

Par contre j'ai du mal à comprendre l'instruction : Table.AddColumn(NomMois, "ClasseurXL", each Excel.Workbook([Content])[Data]{0})
je vois que cela ajoute une colonne nommée "ClasseurXL" qui contient pour chaque ligne (c'est-à-dire pour chaque fichier), le contenu de la première feuille (indice 0), mais je ne vois pas comment l'adapter si je veux choisir un nom de feuille précis, parce que moi dans l'interface PWQ me demande toujours le nom de feuille qui m'intéresse.

Pour sélectionner une feuille nommément, il est nécessaire d'aménager le code de l'étape "ClasseurXL" :

= Table.AddColumn(NomMois, "ClasseurXL", each Excel.Workbook([Content]){[Name="Feuil1"]}[Data])

Le nom de la feuille (ici "Feuil1") est à adapter à votre réalité.

Bonsoir à tous,

JFL, c'est parfait j'ai pu adapter avec le nom de la feuille et ça fonctionne très bien.

Arturo83, Ma solution VBA fonctionne également, concernant les lenteurs ça doit venir de notre réseau.

Merci à vous.
à bientôt.

Bonjour à tous !

Bien....

Je vous remercie de ce retour.