Comment consolider plusieurs classeurs Excel dans un classeur unique ?
Dans ce tutoriel, je vais vous montrer comment consolider les valeurs contenues dans plusieurs classeurs dans un classeur de synthèse unique.
Pour cela, nous allons découvrir deux méthodes.
La première d’entre elles va consister à utiliser une formule de synthèse des données et la deuxième va reposer sur l’utilisation d’une fonctionnalité avancée intégrée dans Excel.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Consolider des données dans un document de synthèse
Comme nous venons de le voir dans l’introduction de ce tutoriel, nous allons maintenant, découvrir deux méthodes qui vont nous permettre de consolider les valeurs contenues dans les cellules de plusieurs classeurs Excel dans un seul et même classeur de synthèse
2. Effectuer une somme des valeurs de plusieurs cellules
Pour commencer, nous allons aborder très rapidement la méthode de compilation manuelle, que nous connaissons tous, et qui consiste tout simplement à effectuer une simple somme des valeurs contenues dans chacun de ces classeurs.
Pour cela, nous pourrons effectuer une somme classique avec la fonction SOMME(), ou plus simplement encore, nous pourrons utiliser le signe plus pour effectuer une addition.
Pour cela, nous allons nous rendre tout d’abord sur le tableau de synthèse dans lequel nous allons vouloir retrouver le montant de vente mensuelle réalisée au cours des années 2020 à 2022 pour les trois magasins de Paris, de Lyon et de Lille
Pour cela, nous nous plaçons sur la première cellule correspondant aux ventes de janvier de l’année 2020 :
Nous tapons le signe égal « = » afin de pouvoir insérer une formule dans cette cellule B12 et ensuite, nous allons tout simplement sélectionner le classeur dans lequel se trouve les ventes du magasin de Paris, pour y sélectionner la cellule correspondante :
Comme nous pouvons le constater, Excel insère alors la référence externe de cette cellule dans la formule en cours de rédaction. Nous y retrouvons ainsi le nom du classeur en plus des coordonnées de la cellule B12.
Cela étant fait, nous pouvons saisir le signe plus « + » afin de sélectionner la cellule B12 du deuxième classeur dans lequel se trouve les ventes de janvier 2020 du magasin de Lyon :
Pour finir, nous répétons une dernière fois cette opération pour insérer également les ventes du magasin du Lille dans la formule :
='[Excelformation.fr - Formules plusieurs classeurs Excel - Paris.xlsx]excelformation.fr'!$B$12+'[Excelformation.fr - Formules plusieurs classeurs Excel - Lyon.xlsx]excelformation.fr'!$B$12+'[Excelformation.fr - Formules plusieurs classeurs Excel - Lille.xlsx]excelformation.fr'!$B$12
Lorsque nous avons terminé de récupérer les trois valeurs correspondantes, nous validons la formule en appuyant sur la touche [Entrée] du clavier :
Les ventes réalisées au sein des trois magasins au cours du mois de janvier 2020 représentent donc un total de 706k€.
Ensuite pour calculer l’ensemble des ventes réalisées au cours de l’année 2020 il va nous suffire, d’étendre la formule sur l’ensemble des cellules de la colonne, sauf que si nous observons la formule créée par Excel, nous pouvons constater qu’il s’agit de références absolues, étant donné que celles-ci sont extraites de cellules situées sur d’autres classeurs.
Nous allons donc devoir les convertir en références relatives.
Dans le champ Rechercher, nous saisissons le caractère à faire disparaître, ici le symbole dollar des références absolues, et nous laissons le champ Remplacer vide.
Ensuite, nous appuyons sur le bouton « Remplacer tout » pour faire disparaître tous les dollars de la feuille de calcul.
Attention, si nous souhaitons conserver les dollars saisis sur d’autres cellules, nous devrons tout d’abord sélectionner quelques cellules pour limiter l’effet du remplacer sur celles-ci.
Ensuite, nous pouvons utiliser la poignée de recopie en plaçant le curseur en bas à droite de la cellule B12, jusqu’à ce que celui-ci prenne forme d’une croix, puis tout en maintenant, le bouton de la souris enfoncé, nous allons faire glisser la formule jusqu’en bas, c’est-à-dire jusqu’au mois de décembre :
Ensuite, pour récupérer les ventes des trois années, nous conservons toutes les cellules de la première colonne sélectionnées, et nous allons utiliser une nouvelle fois la poignée de recopie pour effectuer le décalage vers la droite :
Et voilà, nous avons maintenant le montant des ventes réalisées auprès de ces trois magasins mois par mois, pour les trois années
Maintenant, découvrons la méthode de consolidation automatique des cellules.
3. Effectuer une consolidation automatique
Il s’agit d’une fonctionnalité intégrée dans Excel qui va nous permettre non seulement de récupérer des données de plusieurs classeurs Excel, mais également d’en obtenir des sous-totaux
Pour l’utiliser, nous sélectionnons les cellules dans lesquelles nous souhaitons effectuer la consolidation :
Puis nous allons nous rendre dans le menu Données et cliquer sur le bouton Consolider que nous retrouverons dans le groupe Outils des données :
Excel nous présente alors une boîte de dialogue dans laquelle nous allons commencer par sélectionner le type de fonctions que nous allons vouloir appliquer.
Ici, étant donné que nous voulons récupérer la somme des ventes réalisées dans chacun de ses magasins, nous allons bien entendu vouloir effectuer une somme :
Ensuite, dans le champ Référence, nous allons sélectionner les cellules que nous allons vouloir intégrer.
Nous cliquons sur le bouton en forme de flèche, afin de pouvoir aller sélectionner toutes les ventes du magasin Paris pour les douze mois des trois années :
Une fois les cellules sélectionnées (sans les en-têtes), nous appuyons sur la touche [Entrée] du clavier pour valider la référence des cellules à consolider, puis nous appuyons sur le bouton [Ajouter], afin d’insérer la référence que nous venons de sélectionner dans le champ « Toutes les références ».
Ensuite, nous allons répéter la même opération pour les deux autres magasins :
Ensuite, étant donné que nous n’avons consolidé que les données, dans un tableau préexistant, nous n’avons pas besoin d’activer les options « Ligne du haut » et « Colonne de gauche ». Nous verrons juste après comment consolider toutes les données d’un tableau.
Maintenant, si nous souhaitons ne récupérer ces valeurs que pour un besoin ponctuel, nous pouvons valider la consolidation en appuyant sur le bouton OK
Mais si nous voyons un petit peu plus loin, et que nous souhaitons pouvoir réutiliser par la suite facilement les données avec les éventuelles mises à jour, nous pouvons cocher l’option liée aux données sources
De cette manière Excel ne va pas récupérer directement les valeurs des cellules, mais des références à ces dernières, un peu à la manière de ce que nous avions fait dans la première partie de ce tutoriel.
Cela étant fait, nous pouvons valider la consolidation des données en appuyant sur le bouton [OK].
Excel insère alors des données de consolidation des trois fichiers que nous avons sélectionné en y appliquant également un Plan qui permet de n’afficher que les données essentielles.
Si nous voulons afficher les données masquées, il suffit d’appuyer sur le bouton « + », ce qui nous permettra de retrouver le détail magasin par magasin.
4. Effectuer une consolidation complète
Suivant le même fonctionnement, nous pouvons également effectuer une consolidation complète, c’est-à-dire en incluant les libellés de lignes et de colonnes.
Pour cela, nous répétons exactement la même méthode, mais en sélectionnant également ces cellules :
Ensuite, avant de valider la consolidation, nous cochons les cases Ligne du haut et Colonne de gauche pour tenir compte de ces en-têtes :
L’avantage de tenir compte des libellés, c’est que les données seront ainsi consolidées en tenant compte des valeurs de ces derniers.
Pour nous en rendre compte, supprimons les données de la feuille de calcul, puis nous modifions l’une des années de l’un des magasins, par exemple l’année 2020 deviens 2019 :
Il suffit ensuite de relancer la consolidation en validant simplement les paramètres déjà définis :
Lorsque nous validons, Excel ajoute une quatrième colonne dans la synthèse :
Pour finir, étant donné que nous avons lié les données aux données sources, il suffit de nous rendre sur l’un des fichiers de vente et de modifier une valeur pour que la modification soit répercutée sur la consolidation.