Analyse avancée des données - Consolidation des données
Vous avez peut-être rencontré différentes situations dans lesquelles vous devez présenter des données consolidées. La source des données peut provenir d'un seul endroit ou de plusieurs endroits. Un autre défi pourrait être que les données pourraient être mises à jour par d'autres personnes de temps à autre.
Vous devez savoir comment configurer une feuille de calcul récapitulative qui consolide les données des sources que vous avez configurées, quand vous le souhaitez. Dans Excel, vous pouvez facilement effectuer cette tâche en quelques étapes avec leData Tool – Consolidate.
Préparation des données pour la consolidation
Avant de commencer à consolider les données, assurez-vous qu'il existe une cohérence entre les sources de données. Cela signifie que les données sont organisées comme suit -
Chaque plage de données se trouve sur une feuille de calcul distincte.
Chaque plage de données est au format liste, avec des étiquettes dans la première ligne.
De plus, vous pouvez avoir des étiquettes pour les catégories, le cas échéant, dans la première colonne.
Toutes les plages de données ont la même disposition.
Toutes les plages de données contiennent des faits similaires.
Il n'y a pas de lignes ou de colonnes vides dans chaque plage.
Dans le cas où les sources de données sont externes, assurez-vous d'utiliser une mise en page prédéfinie sous la forme d'un modèle Excel.
Supposons que vous ayez les données de vente de divers produits de chacune des régions - Est, Nord, Sud et Ouest. Vous devrez peut-être consolider ces données et présenter de temps à autre un résumé des ventes par produit. La préparation comprend les éléments suivants -
Une feuille de travail par région - c'est-à-dire quatre feuilles de travail avec les noms Est, Nord, Sud et Ouest. Ceux-ci peuvent se trouver dans le même classeur ou dans différents classeurs.
Chaque feuille de calcul a la même mise en page, représentant les détails du produit, le nombre d'unités et le montant.
Vous devez consolider les données sur le produit. Par conséquent, assurez-vous que la colonne avec l'étiquette Produit est la première colonne et qu'elle contient les étiquettes Produit.
Consolidation des données dans le même classeur
Si vous disposez de toutes les données que vous devez consolider, dans le même classeur, procédez comme suit -
Step 1 - Assurez-vous que les données de chaque région figurent sur une feuille de calcul distincte.
Step 2 - Ajoutez une nouvelle feuille de calcul et nommez-la Résumé.
Step 3 - Cliquez sur la feuille de calcul Résumé.
Step 4 - Cliquez sur la cellule dans laquelle vous souhaitez placer le résumé des résultats.
Step 5 - Cliquez sur le DATA onglet sur le ruban.
Step 6 - Cliquez sur le Consolidate bouton dans le Data Tools groupe.
le Consolidate la boîte de dialogue apparaît.
Step 7 - Sélectionnez Sum dans la liste déroulante sous Function.
Step 8 - Sélectionnez les données de chaque feuille de calcul comme suit.
- Cliquez sur l'icône dans la zone sous Référence.
- Sélectionnez la feuille de calcul - Est.
- Sélectionnez la plage de données.
- Encore une fois, cliquez sur l'icône dans la zone sous Référence.
La plage sélectionnée apparaît dans la zone Référence -
Step 9 - Cliquez sur le Addbouton à droite de la boîte. La plage de données sélectionnée apparaît dans la zone sousAll References.
Step 10- Répétez les étapes 1 à 5 pour le reste des feuilles de calcul de données - Nord, Sud et Ouest. La boîte de dialogue Consolider se présente comme suit.
Vous pouvez voir que les plages de données apparaissent par feuille de calcul par ordre alphabétique, dans la zone sous All references.
Step 11 - Cochez les cases Top row et Left column en dessous de Use labels in. Cliquez sur OK.
Vos données sont résumées par produit pour les régions - Est, Nord, Sud et Ouest.
Vous pouvez répéter les étapes ci-dessus pour actualiser manuellement vos résultats récapitulatifs, chaque fois que vous en avez besoin.
Consolidation automatique des données
Supposons que vous souhaitiez que votre feuille récapitulative soit mise à jour automatiquement, chaque fois qu'il y a des changements dans les données. Pour ce faire, vous devez disposer de liens vers les données source.
Step 1 - Cochez la case - Create links to source data dans la boîte de dialogue Consolider et cliquez sur OK.
Vos résultats récapitulatifs apparaissent avec un aperçu comme suit -
Vous observerez qu'une nouvelle colonne est insérée à droite de la colonne nommée Product.
Step 2- Cliquez sur le signe + sur le contour dans la ligne contenant la valeur Product nommée Soap. Vous pouvez voir que la nouvelle colonne contient la valeur consolidée pour chaque ensemble de valeurs de produit, par région.
Consolidation des données de différents classeurs
Dans l'exemple précédent, toutes les données que vous devez résumer se trouvent dans le même classeur. Cependant, il est probable que les données soient conservées séparément pour chaque région et mises à jour par région. Dans un tel cas, vous pouvez consolider les données comme suit -
Step 1 - Ouvrez les classeurs contenant les données, disons, les classeurs - East-Sales, North-Sales, South-Sales et West-Sales.
Step 2 - Ouvrez un nouveau classeur.
Step 3 - Sur une nouvelle feuille de calcul, cliquez sur une cellule dans laquelle vous souhaitez que le résumé apparaisse.
Step 4 - Cliquez sur l'onglet DONNÉES sur le ruban.
Step 5 - Cliquez sur Consolider dans la zone Outils de données.
UNE Consolidatela boîte de dialogue apparaît. Dans la boîte de dialogue Consolider -
- Sélectionnez Somme dans la liste déroulante de la zone sous Fonction.
- Cliquez sur l'icône dans la case sous Reference.
- Sélectionnez le classeur - East-Sales.xlsx.
- Sélectionnez la plage de données.
- Encore une fois, cliquez sur l'icône dans la zone sous Référence.
- Clique le Add bouton vers la droite.
La boîte de dialogue Consolider se présente comme suit:
- Cliquez sur l'icône à droite de la zone sous Références.
- Sélectionnez le classeur - North-Sales.xlsx.
- Sélectionnez la plage de données.
- Encore une fois, cliquez sur l'icône à droite de la zone sous Références.
- Cliquez sur Ajouter.
Step 6 - Répétez les étapes 1 à 6 pour ajouter les plages de données des classeurs - South-Sales.xlsx et West-Sales.xlsx.
Step 7 - Sous Use labels in, cochez les cases suivantes.
- Rangée supérieure.
- Colonne de gauche.
Step 8 - Cochez la case Create links to source data.
Votre boîte de dialogue Consolider se présente comme suit -
Vos données sont résumées dans votre classeur.
