Consolider les données dans plusieurs feuilles de calcul

Consolider les données dans plusieurs feuilles de calcul

Pour synthétiser et reporter des résultats figurant dans des feuilles de calcul distinctes, vous pouvez consolider les données de chaque feuille dans une feuille de calcul maître. Les feuilles de calcul peuvent figurer dans le même classeur que la feuille maître ou dans des classeurs différents. Lorsque vous consolidez des données, vous les assemblez de manière à simplifier la mise à jour et l’agrégation en fonction de vos 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 baser vos feuilles de calcul sur un modèle de feuille de calcul qui utilise une disposition cohérente. Pour en savoir plus sur les modèles, consultez les rubriques suivantes : Créer un modèle C’est également le moment idéal de configurer votre modèle avec des tableaux Excel.

Deux méthodes s’offrent à vous pour consolider des données : par catégorie ou par position.

Consolidation par position : lorsque les données des zones source sont organisées 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. Pour rendre la consolidation de données par catégorie plus flexible, nous vous conseillons plutôt de créer un tableau croisé dynamique.

Remarque : Les exemples présentés dans cet article ont été créés dans Excel 2016. Votre affichage peut donc différer en fonction de la version du programme que vous utilisez. Toutefois, les étapes sont exactement les mêmes.

Étapes de la consolidation

  1. Si vous ne l’avez pas encore fait, dans chaque feuille de calcul qui contient les données que vous voulez consolider, définissez les données en procédant comme suit :

    • Assurez-vous que chaque plage de données est au format liste de sorte que la première ligne de chaque colonne contienne une étiquette, que chaque colonne contienne des faits similaires et que la liste ne contienne aucune ligne ou colonne vide.

    • Placez chaque plage sur une feuille de calcul séparée, mais n’entrez rien d’autre dans la feuille de calcul maître si vous envisagez d’utiliser une consolidation, car Excel renseignera ces informations à votre place.

    • Assurez-vous que toutes les plages ont la même disposition.

  2. 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 d’écraser les données existantes de la feuille de calcul cible par les données que vous consolidez, prenez soin de laisser suffisamment de cellules à droite et en dessous de cette cellule pour les données consolidées.

  3. Dans l’onglet Données, dans le groupe Outils de données, cliquez sur Consolider.

    Groupe Outils de données sous l’onglet Données

  4. Dans la zone Fonction, cliquez sur la fonction de synthèse que vous souhaitez qu’Excel utilise pour consolider les données. La fonction par défaut est SOMME.

    Dans l’exemple suivant, trois plages de feuilles de calcul sont sélectionnées.

    Boîte de dialogue Consolidation des données

  5. Sélectionnez vos données.

    • Si la feuille de calcul qui contient les données à consolider est dans un autre classeur, cliquez tout d’abord sur Parcourir pour rechercher le classeur, puis cliquez sur OK pour fermer la boîte de dialogue Parcourir. Excel entre le chemin d’accès au fichier dans la zone Référence, suivi d’un point d’exclamation. Vous pouvez alors continuer à sélectionner vos données.

    Ensuite, dans la zone Référence, cliquez sur le bouton Réduire la boîte de dialogue pour sélectionner les données dans la feuille de calcul.

    Consolidation des données Réduire la boîte de dialogue

    Cliquez sur le classeur contenant les données que vous voulez consolider, sélectionnez les données, puis cliquez sur le bouton Développer la boîte de dialogue situé à droite pour revenir à la boîte de dialogue Consolider.

  6. Dans la boîte de dialogue Consolider, cliquez sur Ajouter, puis répétez l’opération pour ajouter toutes les plages souhaitées.

  7. Mises à jour automatiques et manuelles : Si vous souhaitez qu’Excel mette à jour votre tableau de consolidation automatiquement lorsque les données source changent, cochez la case Lier aux données source. Si cette case n’est pas cochée, vous pouvez mettre à jour la consolidation manuellement.

    Remarques : 

    • Vous ne pouvez pas créer de liens lorsque les zones source et de destination figurent dans la mêle feuille.

    • Si vous avez besoin de modifier des plages après les avoir ajoutées, vous pouvez cliquer sur chacune d’entre elles dans la boîte de dialogue Consolider et les mettre à jour lorsqu’elles s’affichent dans la zone Référence, puis cliquer sur Ajouter. Cette action a pour effet de créer une référence à la plage. Vous devez alors supprimer l’ancienne avant de procéder à une nouvelle consolidation. Pour ce faire, sélectionnez l’ancienne référence et appuyez sur Suppr.

  8. Appuyez sur OK pour qu’Excel génère la consolidation à votre place. Celle-ci n’est pas mise en forme. Il vous appartient donc de le faire. Vous n’avez besoin de le faire 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.

    • Vérifiez si toutes les catégories que vous ne souhaitez pas consolider sont dotées d’étiquettes uniques qui n’apparaissent que dans une seule plage source.

Utiliser une formule pour consolider des données

  • Si les données à consolider figurent dans des cellules différentes de plusieurs feuilles de calcul

    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 :

    Référence de formule sur plusieurs feuilles Excel

    Conseil : Pour insérer une référence telle que Ventes!B4 dans une formule sans avoir à taper cette référence, entrez la formule jusqu’à l’endroit où vous devez faire figurer la référence, cliquez sur l’onglet de la feuille de calcul, puis sur la cellule. Excel renseigne le nom de la feuille et l’adresse de la cellule à votre place. Notez simplement que les formules telles que celle-ci sont sujettes aux erreurs, car il est facile de sélectionner la mauvaise cellule. Il peut également être difficile de repérer une erreur après la saisie d’une formule.

  • Si les données à consolider figurent dans les mêmes cellules de différentes feuilles de calcul

    Entrez une formule contenant une référence 3D utilisant une référence à une plage de noms de feuilles de calcul. Par exemple, pour consolider des données dans les cellules A2 de Ventes à Marketing (incluse), dans la cellule E5 de la feuille de calcul maître, tapez :

    Formule de référence de feuille 3D Excel

Le saviez-vous ?

Si vous n’avez pas d’abonnement Office 365 ou la dernière version d’Office, vous pouvez essayer ce produit dès aujourd’hui :

Essayez Office 365 ou la dernière version d’Excel

Vous avez une question sur une fonction en particulier ?

Poser une question sur le forum de la communauté Excel

Contribuer à l’amélioration d’Excel

Avez-vous des suggestions sur la manière dont nous pouvons améliorer la prochaine version d’Excel ? Si c’est le cas, consultez les rubriques du forum User Voice sur Excel

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

Fonctions Excel (par ordre alphabétique)

Fonctions Excel (par catégorie)

Développez vos compétences
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×