Consolidation Excel (Table des matières)
- Introduction à la consolidation dans Excel
- Comment consolider les données dans plusieurs feuilles de calcul?
Consolider les données dans Excel
La consolidation en général signifie la collecte ou la collecte de certaines choses communes en un seul endroit. Il peut s'agir de n'importe quelle sorte de données ou de quelque chose comme des informations ou des nombres. La consolidation dans Excel fait référence à une fonction qui combine des informations ou des données de différents classeurs / feuilles de calcul dans une seule feuille de calcul / classeur. En d'autres termes, cette fonction prend les données de différentes feuilles de calcul pour les combiner en une seule feuille de calcul qui peut être facilement mise à jour.
Par exemple, si vous avez quatre feuilles de calcul. Chaque feuille a des dépenses pour un trimestre. Vous pourriez avoir besoin d'une seule feuille avec les dépenses des quatre trimestres. C'est à ce moment que la fonction de consolidation entre en jeu. Lorsque les données des feuilles de calcul sont dans la même position, ce qui signifie sous les mêmes étiquettes et dans le même ordre. Nous utilisons la «consolidation par poste». Et lorsque les données des feuilles de calcul ne sont pas sous les mêmes étiquettes et pas dans le même ordre. Ensuite, nous utilisons «Consolidation par catégorie». C'est comme créer un tableau croisé dynamique.
Comment consolider les données dans plusieurs feuilles de calcul?
Voyons comment consolider les données dans plusieurs feuilles de calcul avec quelques exemples.
Exemple # 1 - Consolider les données dans le même classeur
Disons que nous avons une feuille de calcul dans laquelle nous avons des données de vente avec quatre feuilles de calcul marquées selon leur région comme Nord, Sud, Est et Ouest. Maintenant, nous aimerions consolider les données en un seul endroit plutôt que dans une feuille dans le même classeur. Il existe une cinquième feuille nommée fichier consolidé.
Cet exemple montrera les ventes consolidées pour toutes les régions. Voici les exemples de fichiers de données. Ici, vous pouvez voir les différents noms de feuille de calcul et le dernier fichier consolidé sur lequel nous devons travailler.
Il s'agit de notre modèle dans la feuille «fichier consolidé» et nous allons maintenant commencer à consolider les données des feuilles de calcul.
Nous allons maintenant cliquer sur la cellule B3.
Où nous voulons que la fonction «Consolider» insère les données d'autres feuilles. Comme nous pouvons le voir ci-dessus, la cellule B3 est sélectionnée et nous allons maintenant passer à l'onglet Données dans le ruban Excel et aller à Consolider.
Une fois que nous avons cliqué sur Consolider, la fenêtre ci-dessous apparaîtra:
La première chose que nous regardons est la liste déroulante «Fonction» qui montre de nombreuses fonctions arithmétiques comme la somme, le nombre, le maximum, la moyenne, etc. Puisque nous voulons une somme de ventes, nous sélectionnerons «Somme» dans la liste déroulante. Maintenant, nous allons aller à l'onglet référence pour ajouter une référence à nos données à partir de différentes feuilles de calcul. Nous irons ensuite à notre première feuille qui contient les données de vente pour le Nord. Il nous suffit de sélectionner les données de vente et non l'en-tête et les lignes. Ceci est illustré ci-dessous.
La référence est indiquée dans la case «Référence» comme ceci.
Cliquez sur «Ajouter» et la référence sera ajoutée dans la case «Toutes les références».
De la même manière, nous allons maintenant ajouter une référence de toutes les autres feuilles comme Nord, Sud, Est et Ouest. Une fois que nous avons terminé d'ajouter les références, cliquez sur «OK».
Nous avons maintenant des données consolidées des ventes pour les cadres mensuellement, au niveau national.
Exemple # 2 - Consolider les produits de vente annuels judicieusement
En cela, nous avons des ventes trimestrielles pour les produits A, B, C et D et nous voulons des ventes annuelles consolidées par produit.
Ici, nous avons des données sur quatre feuilles, la première feuille avec les données de vente pour le T1, ensuite les données pour le T2 suivies par les données pour le T3 et le T4. Nous allons maintenant aller à l'onglet référence pour ajouter des références à nos données à partir de différentes feuilles de calcul. Nous allons passer à notre première feuille qui contient les données de vente du T1. Nous sélectionnerons les données comme indiqué ci-dessous.
Maintenant, nous allons passer à l'onglet Données et cliquer sur Consolider et la fenêtre ci-dessous apparaîtra.
Nous cliquons sur «Ajouter» et la référence sera ajoutée dans la case «Toutes les références».
Nous cliquons sur «Ajouter» et la référence sera ajoutée dans la case «Toutes les références».
Nous cliquons sur «Ajouter» et la référence sera ajoutée dans la case «Toutes les références».
Vous trouverez ci-dessous notre modèle de fiche technique consolidée. Nous allons maintenant sélectionner la cellule B2 pour obtenir les données de ventes totales à partir d'autres feuilles.
Nous sélectionnerons la fonction «Sum» dans le menu déroulant. De même, nous ajouterons des références des feuilles Q2, Q3 et Q4. Cela apparaîtra comme ceci. Toutes les références de toutes les feuilles sont maintenant ajoutées. Cliquez sur OK"
Nous avons maintenant nos données de ventes annuelles consolidées avec les totaux pour chaque produit.
Dans le cas, si nous voulons insérer le tableau consolidé dans une feuille de calcul vierge au lieu de créer un tableau modèle. Nous pouvons aussi le faire. En prenant nos données d'échantillon précédentes, nous ferons la consolidation ci-dessous.
Nous n'avons pas de modèle de table de consolidation et nous voulons obtenir des données consolidées dans une feuille de calcul vierge avec des étiquettes de ligne et de colonne. Nous ajouterons une nouvelle feuille de calcul, dans notre cas, il s'agit du «fichier consolidé».
Maintenant, comme avant, nous allons passer à l'onglet Données Nous allons maintenant sélectionner la référence dans nos fiches techniques. Nous allons commencer avec la feuille «Nord» et ensuite procéder avec les feuilles «Sud», «Est» et «Ouest». Nous sélectionnerons les données comme indiqué ci-dessous, qui incluent les étiquettes de ligne et de colonne. Nous ajouterons ensuite la référence dans la case «Toutes les références»: Ajoutez maintenant toutes les références de la même manière à partir de toutes les fiches techniques. Nous allons maintenant vérifier à la fois la «rangée du haut» et la «colonne de gauche», puis appuyer sur OK. Nous voyons maintenant des données de vente consolidées avec des étiquettes de ligne et de colonne. Si vous souhaitez mettre à jour les données consolidées lorsque la feuille individuelle est mise à jour, cliquez sur la case «Créer des liens pour créer des données». Si vous souhaitez mettre à jour les données manuellement, ne cochez pas la case et cliquez sur OK. Les cellules du tableau consolidé contiennent désormais la formule de somme, comme indiqué ci-dessous. Cela le rend de nature dynamique. Il s'agit d'un tableau de consolidation dynamique. Si nous avons des données dans différents classeurs et que nous voulons les consolider en un seul classeur. Nous pouvons également le faire. Cela nous donne une grande flexibilité et facilité. Nous avons des données sur les ventes par trimestre de divers produits, par exemple A, B, C et D dans différents classeurs, comme indiqué ci-dessous. Une fois tous les classeurs ouverts, nous irons dans un nouveau classeur et cliquons sur la cellule B3. Ajoutez les références de toutes les feuilles de calcul ci-dessous. Nous avons regroupé les données de tous les classeurs dans un nouveau classeur. De plus, toute modification des données de tout classeur sera également mise à jour dans le nouveau classeur de données consolidées. Nous avons donc appris à utiliser la fonction de consolidation dans Excel à l'aide d'exemples. Il s'agit d'une fonction très utile pour fusionner ou collecter des données dans une seule feuille à partir de différentes feuilles de calcul ou classeurs. Ceci est un guide de consolidation dans Excel. Nous discutons ici de la façon de consolider les données dans plusieurs feuilles de calcul, ainsi que d'exemples pratiques et d'un modèle Excel téléchargeable. Vous pouvez également consulter nos autres articles connexes -
Exemple # 3 - Consolidation des données de différents classeurs
Choses à retenir sur la consolidation dans Excel
Articles recommandés