Consolider les données dans plusieurs feuilles de calcul

Pour résumer et signaler les résultats à partir de feuilles de calcul distinctes, vous pouvez consolider les données de chaque feuille dans une feuille de calcul master. Les feuilles peuvent se trouver dans le même classeur que la feuille de calcul master ou dans d’autres classeurs. Lorsque vous consolidez des données, vous assemblez les données afin de pouvoir les mettre à jour et les agréger plus facilement si nécessaire.

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 des 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.

Il existe deux façons de consolider les données, par position ou par catégorie.

Consolidation par position : les données dans les zones sources ont 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.

Procédez comme suit pour consolider plusieurs feuilles de calcul dans une feuille de calcul master :

Si vous ne l’avez pas déjà fait, configurez les données dans chaque feuille constituante en procédant comme suit :
- Vérifiez que chaque plage de données est au format 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 n’importe où dans la liste.
- Placez chaque plage sur une feuille de calcul distincte, mais n’entrez rien dans la feuille de calcul master où vous envisagez de consolider les données. Excel le fera pour vous.
- Vérifiez 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 de cette cellule pour les données consolidées.
Cliquez sur Données>Consolider (dans le groupe Outils de données ).
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.

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 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.

Si une feuille de calcul contenant des données que vous devez consolider se trouve dans un autre classeur, cliquez sur Parcourir pour rechercher ce classeur. Après avoir localisé et cliqué sur OK, Excel entre le chemin du fichier dans la zone Référence et ajoute un point d’exclamation à ce chemin. 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 souhaitez qu’Excel mettent automatiquement à jour votre table de consolidation lorsque les données sources changent, case activée simplement la zone Créer des liens vers les données sources. 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 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. Cela crée une nouvelle référence de plage, vous devez donc supprimer la précédente avant de consolider à nouveau. Choisissez simplement l’ancienne référence et appuyez sur la touche Suppr.
Cliquez sur OK pour qu’Excel génère la consolidation pour vous. Si vous le souhaitez, vous pouvez 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 toutes les catégories que vous ne souhaitez pas consolider ont des étiquettes uniques qui apparaissent dans une seule plage source.

Si les données à consolider se situent dans différentes cellules de 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 entrer une référence de cellule, telle que Sales ! B4 : dans une formule sans taper, tapez la formule jusqu’au point où vous avez besoin de la référence, puis cliquez sur l’onglet de la feuille de calcul, puis sur la cellule. Excel complète le nom de la feuille et l’adresse de la cellule pour vous. REMARQUE : dans de tels cas, les formules peuvent être sujettes aux erreurs, car il est très facile de sélectionner accidentellement la mauvaise cellule. Il peut également être difficile de repérer une erreur après avoir entré une formule complexe.

Si les données à consolider se situent dans les mêmes cellules de 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 de Ventes à Marketing inclus, dans la cellule E5 de la feuille de calcul master, entrez les éléments suivants :

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