Comment consolider plusieurs classeurs Excel dans un classeur unique ?

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 :

Excel formation - formule plusieurs classeurs - 01

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 :

Excel formation - formule plusieurs classeurs - 02

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 :

Excel formation - formule plusieurs classeurs - 03

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 :

Excel formation - formule plusieurs classeurs - 04

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.

Le plus simple pour effectuer cette conversion est de lancer la boîte de dialogue de remplacement des valeurs e appuyant sur les touches [Ctrl]+[H] du clavier.

Excel formation - formule plusieurs classeurs - 05

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 :

Excel formation - formule plusieurs classeurs - 06

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 :

Excel formation - formule plusieurs classeurs - 07

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 :

Excel formation - formule plusieurs classeurs - 08

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 formation - formule plusieurs classeurs - 09

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 :

Excel formation - formule plusieurs classeurs - 10

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 :

Excel formation - formule plusieurs classeurs - 11

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 :

Excel formation - formule plusieurs classeurs - 12

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 :

Excel formation - formule plusieurs classeurs - 13

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 :

Excel formation - formule plusieurs classeurs - 14

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 :

Excel formation - formule plusieurs classeurs - 15

Il suffit ensuite de relancer la consolidation en validant simplement les paramètres déjà définis :

Excel formation - formule plusieurs classeurs - 16

Lorsque nous validons, Excel ajoute une quatrième colonne dans la synthèse :

Excel formation - formule plusieurs classeurs - 17

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.

 



Articles qui pourraient vous intéresser

Comment protéger efficacement un classeur Excel en utilisant une clé USB comme protection physique ?
Comment lire un fichier externe (txt, csv,…) avec une fonction Excel VBA ?
Comment créer un arbre généalogique en quelques minutes sur Excel ?
Comment lire un fichier externe depuis un classeur sans VBA Excel ?
Comment consolider plusieurs classeurs Excel dans un classeur unique ?
Comment automatiser la mise à jour des formules Excel ?
Comment vérifier qu’une adresse mail est valide sur Excel ?
Comment protéger efficacement le contenu de vos fichiers Excel ?
Comment afficher une image différente en fonction de la valeur d’une cellule Excel (sans VBA) ?
Comment faire clignoter une cellule Excel ? La mise en forme dynamique
COMMENT EFFACER ET PERSONNALISER LA LISTE DES DOCUMENTS RÉCENTS D’EXCEL (la vraie méthode)
Comment compter le nombre de cellules contenant un nombre de caractères sur Excel sans VBA ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2022 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.