Macros-commandes VBA/Consolider des classeurs

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Introduction

La consolidation de classeurs Excel permet de regrouper sur un seul et même classeur diverses données provenant de plusieurs classeurs. Certains d’entre vous diront qu’il n’est pas nécessaire d’utiliser VBA pour effectuer ce genre de travail. Certes vous pouvez le faire via Microsoft Excel si vous n’avez que quelques documents à consolider. Toutefois, si vous êtes amenés à effectuer ce travail à partir de dizaine et de dizaine de classeurs Excel, vous verrez qu’il est préférable d’utiliser la fonction VBA pour économiser un maximum de temps.
Nous prendrons dans cette leçon l’exemple d’un groupe qui veut consolider les résultats de toutes ses filiales. En effet, il est fréquent dans un groupe d’avoir des fichiers Excel types que chaque filiale renvoie au siège qui doit consolider tous ces fichiers pour obtenir les résultats du groupe. Cependant la consolidation de plusieurs fichiers est chronophage et répétitive et peut rapidement être fastidieuse si elle n’est pas automatisée.
Il existe plusieurs façons de consolider des classeurs Excel selon le type de données et le format qu’ils contiennent. A travers des cas pratiques nous allons étudier deux d’entre elles que nous appellerons la consolidation par somme des données et la consolidation par regroupement de bases de données.

• La consolidation par somme des données consiste à sommer dans un seul classeur le total des données se trouvant dans les classeurs à consolider.
• La consolidation par regroupement de bases de données consiste à copier les différentes bases de données se trouvant dans les classeurs à consolider et les coller dans un seul classeur afin d’avoir toutes les données à un seul endroit.

Consolidation par somme de données

Utilité

Dans ce cas nous voulons sommer des tableaux identiques se trouvant dans différents classeurs sur un seul classeur. Ce type de consolidation, que l’on va appeler ici consolidation par somme de données, nécessite que les données de base soient constituées de chiffres et soient sous le même format.

Dans cet exercice nous allons prendre l’exemple d’une chaîne de magasin qui veut consolider les résultats de ses filiales. Chaque filiale renvoie le même fichier avec ses résultats sous forme de tableau qui comporte une liste de produits vendus, les quantités vendues et les chiffres d’affaires associés. Nous voulons sommer l’ensemble de ces résultats dans un seul tableau de synthèse afin d’obtenir le résultat consolidé du groupe. Manuellement il faudrait ouvrir tous les fichiers et sommer une à une chaque cellule dans un fichier de synthèse. Excel propose un outil appelé consolidateur de données qui permet de sommer toutes ces données cependant, cet outil n’est pas automatique et il faut sélectionner les bases à consolider à chaque fois que l’on veut faire la manipulation. Il est toutefois possible d’automatiser cette manipulation, grâce à VBA, afin de ne pas la reproduire à chaque fois que l’on veut consolider les données.

Application

Résultats du magasin de Grenoble	Résultats du magasin de Paris

Comme on peut le constater, les fichiers sont au même format, ils comportent tous les deux une liste de produits, les quantités vendues et les chiffres d’affaires correspondant. On observe toutefois que le magasin de Paris présente des produits qui ne sont pas présents dans le magasin de Grenoble. Cela ne pose pas de problème car VBA va recopier tous les produits et sommer les quantités vendues et les chiffres d’affaires.

Grâce à la consolidation on veut obtenir le tableau consolidé suivant regroupant tous les produits, les quantités vendues et le chiffre d’affaires :

Résultats consolidés des deux magasins

Décomposition du code VBA

Sub Consolidation_somme()

'Sélectionne la cellule de départ du tableau consolidé:
Range ("A1").Select

' Crée le tableau consolidé:
    '1) Sélectionne les classeurs de données à consolider
    '2) Détermine les conditions de consolidation des données. Ici:
        'a) on somme les données (Function:=xlSum)
        'b) on rapatrie la ligne de titres (TopRow) et la colonne des produits (LeftColumn)
        'c) on ne crée pas de liens entre les classeurs de données et le classeur de synthèse
        
    Selection.Consolidate Sources:=Array( _
        "'E:\Wiki\Consolidation par somme de données\[Grenoble.xlsx]Feuil1'!R1C1:R1000C200", _
        "'E:\Wiki\Consolidation par somme de données\[Paris.xlsx]Feuil1'!R1C1:R1000C200"), _
        Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
        
End Sub

Explications et analyse de la macro

Conditions du tableau consolidé	Exemples
Function :=xlSum : Somme les données consolidées	Par exemple, le magasin de Grenoble a vendu 1 000 000 pommes et le magasin de Paris a vendu 1 210 000 pommes ce qui fait un total de ventes de 2 210 000 pommes
TopRow :=True : Rapatrie la ligne de titres dans le fichier consolidé. Si on met « False » on n’obtient pas la ligne de titres et on ne sait donc pas à quoi correspondent les chiffres
LeftColumn :=True : Rapatrie la colonne de gauche (ici les noms de produits). Si on met « False » on n’obtient pas la colonne de gauche et on ne sait donc pas à quels produits correspondent les quantités et les chiffres d’affaires consolidés
CreateLinks :=False : Ne crée pas de liens entre les fichiers. Si on met « True » on obtient le détail par magasin avec des liens entre les fichiers et des formules sommes pour calculer les résultats consolidés.
On note toutefois que le titre "Produits" n'apparaît pas lorsque l’on sélectionne à la fois la condition TopRow :=True et la condition LeftColumn :=True

Remarques

Pour éviter les erreurs et de devoir modifier le code VBA à chaque fois que l’on inclut des nouvelles lignes ou de nouvelles colonnes dans un des classeurs de données, il est conseillé de définir dès le départ une plage plus large que celle de votre tableau. Sinon vous risquez de ne pas prendre en compte toutes les données à consolider. Ici, nous avons défini une plage qui comprend toutes les cellules de la 1ère cellule de la première colonne du fichier (R1C1) jusqu’à la 1 000ème ligne de la 300ème colonne du fichier (R1000C200). Pour ajouter un nouveau classeur à la consolidation, il faut ajouter le lien d’accès à ce classeur et la plage de données dans le code (ex : "'E:\Wiki\Consolidation par magasins\[Grenoble.xlsx]Feuil1'!R1C1:R1000C200",)

Consolidation par regroupement de bases de données

Utilité

Dans ce cas, nous voulons regrouper plusieurs bases de données de même format dans une seule base de données globale. Ce type de consolidation, que l’on va appeler ici consolidation par regroupement de plusieurs bases de données, nécessite que les bases de données à consolider soient sous le même format. Les données qu’elles contiennent peuvent être de n’importe quel format (texte, nombre, date,…) excepté le format image car les données doivent être contenues dans des cellules.

Attention, si les cellules du fichier de synthèse dans lequel vous allez regrouper vos données ne sont pas au même format vous risquez d’avoir de petites surprises notamment si vous consolidez des données au format date. En effet si vous copiez une donnée sous format date dans une cellule au format standard votre date sera copiée en nombre. Par exemple la date 30.01.1993 deviendra 33999. Rassurez-vous vous ne perdez pas vos données, il suffit d’appliquer le bon format à vos cellules pour retrouver vos dates sous le bon format.

Dans cet exercice nous allons prendre l’exemple d’une entreprise qui possède plusieurs bases de données comprenant des coordonnées clients et qui veut regrouper toutes ces coordonnées dans une seule base de données.

Application

Première base de données	Deuxième base de données

Comme on peut le constater les données des deux classeurs sont sous le même format. Nous allons donc copier ces deux bases et les regrouper l’une à la suite de l’autre dans un seul classeur.

Grâce à la consolidation on veut obtenir la base de données consolidée suivante qui regroupe toutes les données contenues dans les différentes bases de données :

Base de données consolidée

Décomposition du code VBA

Sub consolidation_regroupement()

'Stoppe l'actualisation de l'écran. Cela sert à masquer les actions de la macro
Application.ScreenUpdating = False

'Détermine le chemin d'accès aux fichiers
Dim chemin As String
Dim fichier As String
Dim extension As String

chemin = "C:\Users\Wiki\Consolidation par regroupement de bases de données\"
fichier = "Coordonnées clients "
extension = ".xlsx"

'Indique le nombre de fichiers à consolider
nbfichiers = 2

   For i = 1 To nbfichiers
   
   'Ouvre le fichier à consolider
    Workbooks.Open (chemin & fichier & i & extension)
    
    'Sélectionne la feuille où se trouvent les données
    Sheets("Feuil1").Select

    'Compte le nombre de lignes à copier
    n = WorksheetFunction.CountA(Range("A:A"))
    
    'Compte le nombre de colonnes à copier
    m = ActiveSheet.UsedRange.Columns.Count
    
    'Copie les données
    Range(Cells(2, 1), Cells(n, m)).Copy
    
    'Active le classeur de synthèse
    Windows("Synthèse.xlsm").Activate
    
    'Sélectionne la feuille où on va coller les données
    Sheets("Synthèse").Select

    'Compte le nombre de lignes non vides et ajoute 1 pour avoir le numéro de la première ligne vide
    c = WorksheetFunction.CountA(Range("A:A")) + 1
    
    'Sélectionne la première cellule vide
    Range("A" & c).Select
    
    'Colle les données
    ActiveSheet.Paste
    
    'Ferme la base de données qui a été consolidée et passe à la suivante
    Windows(fichier & i & extension).Close
    
    Next i

'Réactive l'actualisation de l'écran
Application.ScreenUpdating = True


End Sub

Explications et analyse de la macro

1. La première étape de ce code est de définir le chemin d’accès aux classeurs à consolider, leur nom et leur extension. Afin d’alléger le code et de rendre la macro plus facilement modifiable nous avons décidé d’utiliser une boucle qui va copier les données des bases une par une pour les coller dans la base consolidée. Pour cela il faut que les classeurs à consolider se trouvent tous dans le même répertoire de notre ordinateur et portent tous le même nom à l’exception d’un nombre qui permet de distinguer les classeurs. Ici nous avons appelé notre première base de données « Coordonnées clients 1 », la seconde « Coordonnées clients 2 », etc… Nous avons donc défini quatre variables :

   • Chemin : cette variable indique le répertoire dans lequel se trouvent les classeurs à consolider
   • Fichier : cette variable indique le nom du classeur à consolider. Vous noterez que dans le code nous avons nommé le classeur « Coordonnées clients » mais nous n’avons pas indiqué de numéro 1, 2, etc… C’est parce que nous avons enregistré cette information dans une variable à part qui sera ensuite gérée par la boucle.
   • Extension : cette variable indique le type d’extension du classeur (xls, xlsx, xlsm). Dans ce code il faut que tous les classeurs aient la même extension pour que la boucle fonctionne toutefois si vous choisissez de noter les noms des classeurs un à un vous pouvez utiliser des classeurs qui n'ont pas la même extension.
   • Nbclasseurs : cette variable indique le nombre de classeurs à consolider

2. La seconde étape est donc la boucle qui est le cœur de la macro car c’est là que va se faire la consolidation des classeurs. La boucle est définie de 1 à nbclasseurs ce qui signifie qu’elle va tourner autant de fois qu’il y a de classeurs à consolider et répéter la même action pour chaque classeur.
3. Tout d’abord la macro va ouvrir le premier classeur à consolider. Elle va trouver ce classeur grâce aux variables que l’on a défini au début et qui, toutes assemblées, donnent l’accès au classeur. La variable i qui va de 1 à nbclasseurs indiquera donc le numéro du classeur à traiter en fonction de la boucle. Par exemple, le premier tour de boucle va traiter le classeur « Coordonnées clients 1 », le second tour de boucle va traiter le classeur « Coordonnées clients 2 » et ainsi de suite.
4. La macro sélectionne ensuite l’onglet du classeur où se trouvent les données à consolider. Attention, il faut penser à changer le nom de l’onglet s’il ne correspond pas à celui de votre classeur. Bien entendu tous les classeurs doivent avoir un onglet du même nom dans lequel se trouveront les données
5. On compte ensuite le nombre de lignes et de colonnes qui comprennent des données afin de définir la plage de données à consolider et on copie ces données. Attention: dans cet exemple, la macro compte le nombre de cellules non vides pour calculer la plage de données à copier. Cela signifie que s'il y a des trous dans la base (lignes vides), la plage de données qui sera prise en compte par la macro risque de ne pas comprendre toutes les données. Exemple: Si j’ai de données sur la colonne de la ligne 1 à la ligne 5 et de la ligne 7 à la ligne 9 j'aurais au total de 8 lignes remplies. Or si je donne cette valeur à ma variable qui calcule la plage de données ma macro va sélectionner la plage A1 à A8 alors que j’ai des données en A9, je ne vais donc pas récupérer toutes mes données. NB: Cette problématique est typique à ce code, il est toutefois possible de l'adapter pour contourner le problème.
6. On active le classeur où l’on va consolider les données et on sélectionne l’onglet où se trouve la base de données consolidées
7. On compte alors le nombre de lignes qui ne sont pas vides dans cet onglet et on ajoute 1 à ce nombre afin d’obtenir le numéro de la première ligne vide à partir de laquelle on va pouvoir coller les données et on colle les données.
8. On ferme le classeur duquel on a extrait les données.
9. On fait un tour de boucle afin de traiter le prochain fichier

Remarques

Dans notre exemple tous les classeurs de base de données à consolider se trouvent dans le même répertoire, portent le même nom et sont seulement différenciés par un numéro accolé à la fin du nom du classeur. Ici nos classeurs sont nommés « Coordonnées clients 1 » et « Coordonnées clients 2 » ce qui permet de faire une boucle dans la macro avec les noms de fichiers. Chaque classeur a également la même extension. Si un des fichiers a un nom ou une extension différente ou se trouve dans un autre répertoire il ne sera pas consolidé par la macro.

Cette consolidation ne fait que regrouper plusieurs classeurs. Si des mêmes données sont présentes dans plusieurs classeurs vous aurez alors des doublons dans votre classeur consolidé. Vous pourrez supprimer ces doublons soit manuellement soit en créant une nouvelle macro que vous exécuterez à la suite de votre macro de consolidation.

Conclusion

En conclusion, la consolidation de classeurs via VBA permet un gain de temps considérable à condition que les bases de données à consolider respectent toutes le même format.
Nous avons tenté de simplifier les processus au maximum cependant il est possible de les modifier et de les améliorer. Notamment, dans la consolidation que l’on a appelée consolidation par regroupement de bases de données il est possible de rapatrier les données sans ouvrir les classeurs sources mais cela complique le code c’est pourquoi nous avons choisi dans notre exemple d'ouvrir les fichiers un à un puis de le refermer.
Il existe également d'autres méthodes pour consolider des classeurs mais nous avons voulu nous concentrer sur ces deux méthodes qui sont très couramment utilisées dans les entreprises et qui sont faciles et rapides à exécuter.
NB: les noms que nous avons attribué aux méthodes de consolidation (consolidation par somme de données et consolidation par regroupement de bases de données) sont purement de notre fait afin d'illustrer leur action, vous ne retrouverez probablement ces appellations nulle part ailleurs.