Pour synthétiser et reporter les résultats de feuilles de calcul distinctes, vous pouvez consolider les données de chaque feuille dans une feuille de calcul master. Les feuilles peuvent figurer dans le même classeur que la feuille de calcul master ou dans d’autres classeurs. Lorsque vous consolidez des données, vous les assemblez de manière à simplifier leur mise à jour et leur agrégation en fonction des besoins.
Par exemple, si vous avez une feuille de calcul permettant de répertorier les dépenses de chacun de vos bureaux régionaux, vous pouvez utiliser la consolidation pour reporter ces chiffres dans une feuille de calcul maître contenant les dépenses globales de l’entreprise. Cette feuille de calcul maître peut également contenir le chiffre d’affaires total et moyen, les niveaux de stock actuels et les produits vedettes de l’entreprise.
Conseil
Si vous consolidez fréquemment des données, il peut être utile de créer de nouvelles feuilles de calcul à partir d’un modèle de feuille de calcul qui utilise une disposition cohérente. Pour en savoir plus sur les modèles, consultez : Créer un modèle. C’est également le moment idéal pour configurer votre modèle avec des tableaux Excel.
Méthodes de consolidation des données
Il existe deux façons de consolider les données, soit par position, soit par catégorie.
Consolidation par position : les données dans les zones sources sont dans le même ordre et utilisent les mêmes étiquettes. Utilisez cette méthode pour consolider des données provenant d’une série de feuilles de calcul, telles que les feuilles de calcul contenant le budget de différents services qui ont été créées en utilisant le même modèle.
Consolidation par catégorie : lorsque les données des zones source ne sont pas organisées dans le même ordre, mais utilisent les mêmes étiquettes. Utilisez cette méthode pour consolider des données provenant d’une série de feuilles de calcul avec des dispositions différentes, mais qui possèdent les mêmes étiquettes de données.
- La consolidation de données par catégorie revient en quelque sorte à créer un tableau croisé dynamique. Toutefois, avec un tableau croisé dynamique, vous pouvez facilement réorganiser les catégories. Envisagez de créer un tableau croisé dynamique si vous avez besoin d’une consolidation plus flexible par catégorie.
Remarque
Les exemples de cet article ont été créés avec Excel 2016. Bien que votre affichage puisse différer si vous utilisez une autre version d’Excel, les étapes sont les mêmes.
Comment consolider
Pour consolider plusieurs feuilles de calcul dans une feuille de calcul master, procédez comme suit :
Si ce n’est déjà fait, configurez les données dans chaque feuille constitutive en procédant comme suit :
- Assurez-vous que chaque plage de données est sous forme de liste. Chaque colonne doit avoir une étiquette (en-tête) dans la première ligne et contenir des données similaires. Il ne doit y avoir aucune ligne ou colonne vide dans la liste.
- Placez chaque plage dans une feuille de calcul distincte, mais n’entrez rien dans la feuille de calcul master dans laquelle vous envisagez de consolider les données. Excel s’en charge pour vous.
- Assurez-vous que chaque plage a la même disposition.
Dans la feuille de calcul maître, cliquez sur la cellule située dans l’angle supérieur gauche de la zone où vous souhaitez faire apparaître les données consolidées.
Remarque
Pour éviter de remplacer les données existantes dans la feuille de calcul master, veillez à laisser suffisamment de cellules à droite et en dessous pour les données consolidées.
Cliquez sur Consolidation des données> (dans le groupe Outils de données).
Dans la zone Fonction , cliquez sur la fonction de synthèse à utiliser par Excel pour consolider les données. La fonction par défaut est SOMME.
Voici un exemple dans lequel trois plages de feuilles de calcul ont été choisies :
Sélectionnez vos données.
Ensuite, dans la zone Référence , cliquez sur le bouton Réduire pour réduire le panneau et sélectionner les données dans la feuille de calcul.
Cliquez sur la feuille de calcul qui contient les données que vous souhaitez consolider, sélectionnez les données, puis cliquez sur le bouton Développer la boîte de dialogue à droite pour revenir à la boîte de dialogue Consolider .Si une feuille de calcul contenant des données à consolider se trouve dans un autre classeur, cliquez sur Parcourir pour localiser ce classeur. Après avoir localisé et cliqué sur OK, Excel entrera le chemin d’accès au fichier dans la zone Référence et ajoutera un point d’exclamation à ce chemin d’accès. Vous pouvez ensuite continuer à sélectionner d’autres données.
Voici un exemple dans lequel trois plages de feuilles de calcul ont été sélectionnées :
Dans la fenêtre contextuelle Consolider , cliquez sur Ajouter. Répétez cette opération pour ajouter toutes les plages que vous consolidez.
Mises à jour automatiques et manuelles : Si vous voulez qu’Excel mette automatiquement à jour votre table de consolidation lorsque les données sources changent, cochez simplement la case à cocher Créer des liens vers les données sources dans le cas d’une case activée. Si cette case n’est pas cochée, vous pouvez mettre à jour la consolidation manuellement.
Remarque
- Vous ne pouvez pas créer de liens lorsque les zones source et de destination figurent dans la mêle feuille.
- Si vous devez modifier l’étendue d’une plage ou remplacer une plage, cliquez sur la plage dans la fenêtre contextuelle Consolider et mettez-la à jour en suivant les étapes ci-dessus. Une nouvelle référence de plage est créée. Vous devez donc supprimer la précédente avant de procéder à une nouvelle consolidation. Il suffit de choisir l’ancienne référence et d’appuyer sur la touche Suppr.
Cliquez sur OK et Excel générera la consolidation pour vous. Vous pouvez éventuellement appliquer une mise en forme. Il n’est nécessaire de mettre en forme qu’une seule fois, sauf si vous réexécutez la consolidation.
- Toutes les étiquettes qui ne correspondent pas à celles situées dans les autres zones source génèrent des lignes ou des colonnes distinctes dans la consolidation.
- Assurez-vous que les catégories que vous ne souhaitez pas consolider ont des étiquettes uniques qui apparaissent dans une seule plage source.
Utiliser une formule pour consolider des données
Si les données à consolider se trouvent dans des cellules différentes sur des feuilles de calcul différentes :
Entrez une formule avec des références de cellules à d’autres feuilles de calcul, une pour chaque feuille. Par exemple, pour consolider des données de feuilles de calcul appelées Ventes (dans la cellule B4), RH (dans la cellule F5) et Marketing (dans la cellule B9), dans la cellule A2 de la feuille de calcul maître, tapez :
Conseil
Pour entrer une référence de cellule, par exemple Ventes ! B4 : dans une formule sans taper, tapez la formule jusqu’à l’endroit où vous souhaitez obtenir la référence, puis cliquez sur l’onglet de la feuille de calcul, puis sur la cellule. Excel complétera le nom de la feuille et l’adresse de la cellule pour vous. REMARQUE : les formules dans de tels cas peuvent être sujettes à des erreurs, car il est très facile de cliquer accidentellement sur la mauvaise cellule. Il peut également être difficile de repérer une erreur après avoir saisi une formule complexe.
Si les données à consolider se trouvent dans les mêmes cellules sur des feuilles de calcul différentes :
Entrez une formule contenant une référence 3D utilisant une référence à une plage de noms de feuille de calcul. Par exemple, pour consolider les données dans les cellules A2 Ventes à Marketing inclus, dans la cellule E5 de la feuille de calcul master, vous devez entrer les éléments suivants :
Vous avez besoin d’une aide supplémentaire ?
Vous pouvez toujours poser des questions à un expert de la Communauté technique Excel ou obtenir de l’aide dans les Communautés.
Voir aussi
Vue d’ensemble des formules dans Excel
Comment éviter les formules incorrectes
Rechercher et corriger les erreurs dans les formules
Raccourcis clavier et les touches de fonction Excel