Consolidation des données dans Microsoft Excel
Mon entreprise vend des produits dans plusieurs régions des États-Unis. Chaque région tient un registre du nombre d’unités de chaque produit vendu au cours des mois de janvier, février et mars. Existe-t-il un moyen facile de créer un classeur principal qui combine toujours les ventes de chaque région et donne un décompte du montant total de chaque produit vendu aux États-Unis au cours de chaque mois?
| Un analyste d’entreprise reçoit souvent des feuilles de calcul qui recoupent les mêmes informations (telles que les ventes mensuelles de produits) de différentes filiales ou régions. Pour déterminer la rentabilité globale de l’entreprise, l’analyste doit généralement combiner ou consolider ces informations dans un seul classeur Microsoft Excel. Les tableaux croisés dynamiques construits à partir de plusieurs plages de consolidation peuvent être utilisés pour atteindre cet objectif, mais La commande de consolidation peu connue (dans l’onglet Données du ruban) est un autre moyen d’atteindre cet objectif. Avec Consolidation, vous pouvez vous assurer que les modifications apportées aux feuilles de calcul individuelles sont automatiquement reflétées dans la feuille de calcul consolidée. |
Mon entreprise vend des produits dans plusieurs régions des États-Unis. Chaque région tient un registre du nombre d’unités de chaque produit vendu au cours des mois de janvier, février et mars. Existe-t-il un moyen facile de créer un classeur principal qui combine toujours les ventes de chaque région et donne un décompte du montant total de chaque produit vendu aux États-Unis au cours de chaque mois?
Le fichier (illustré à la figure 1) affiche les ventes unitaires mensuelles des produits A – H dans l’est des États-Unis en janvier, février et mars. De même, le fichier (illustré à la figure 2) affiche les ventes unitaires mensuelles des produits A – H dans l’ouest des États-Unis de janvier à mars. Votre besoin est de créer une feuille de calcul consolidée qui tabule les ventes totales de chaque produit par mois.
FIGURE 1 Voici les ventes de la région Est de janvier à mars.
FIGURE 2 Voici les ventes de la région Ouest de janvier à mars.
Avant d’utiliser la commande Consolider, il est utile de voir les deux feuilles de calcul ensemble à l’écran. Pour ce faire, ouvrez les deux classeurs, cliquez sur Réorganiser tout dans le groupe Fenêtre de l’onglet Affichage et sélectionnez Mosaïque. Votre écran devrait ressembler à la figure 3.
FIGURE 3 Les ventes Est et Ouest sont organisées sur le même écran.
Ouvrez maintenant une feuille de calcul vierge dans un nouveau classeur, puis cliquez à nouveau sur Réorganiser tout et mosaïque. Dans la feuille de calcul vierge, cliquez sur Consolider dans le groupe Outils de données sous l’onglet Données, et vous voyez la boîte de dialogue Consolider, illustrée à la figure 4.
FIGURE 4 Il s’agit de la boîte de dialogue Consolider.
Pour consolider les données des régions Est et Ouest dans votre nouvelle feuille de calcul vierge, entrez les plages que vous souhaitez consolider dans la zone Référence de la boîte de dialogue Consolider, en cliquant sur Ajouter après avoir sélectionné chaque plage. En cochant les cases Ligne supérieure et Colonne gauche dans la zone Utiliser les étiquettes dans, vous vous assurez qu’Excel consolide les plages sélectionnées en examinant les étiquettes dans la ligne supérieure et la colonne de gauche de chaque plage. Créer des liens vers les données source permet de refléter les modifications des plages sélectionnées dans la feuille de calcul consolidée. Sélectionnez Somme dans la zone Fonction car vous souhaitez qu’Excel additionne les ventes totales de chaque produit par mois. Sélectionner Count, par exemple, compterait le nombre de transactions pour chaque produit au cours de chaque mois; sélectionner Max calculerait la transaction de vente la plus importante pour chaque produit au cours de chaque mois. La boîte de dialogue Consolider doit être remplie comme le montre la figure 5.
Après avoir cliqué sur OK, la nouvelle feuille de calcul ressemble à celle illustrée à la figure 6. (Voir le fichier .) Vous pouvez voir, par exemple, que 1317 unités de produit A ont été vendues en février, 597 unités de produit F ont été vendues en janvier, etc.
FIGURE 5 Il s’agit de la boîte de dialogue Consolidation terminée.
FIGURE 6 Voici les données de ventes totales après consolidation.
Maintenant, dans la cellule C2 , modifiez les ventes de février du produit A de 263 à 363. Notez que dans la feuille de calcul consolidée, l’entrée pour les ventes de février du produit A a également augmenté de 100 (de 1 317 à 1 417). Cette modification se produit car Créer des liens vers les données source a été sélectionné dans la boîte de dialogue Consolider. (Soit dit en passant, si vous cliquez sur le 2 juste en dessous du nom du classeur dans la feuille de calcul consolidée, vous voyez comment Excel a regroupé les données pour effectuer la consolidation.) Le résultat est contenu dans le fichier Eastandwestconsolidated.xlsx. Bien sûr, vous pouvez également résumer les données de différentes feuilles de calcul en utilisant des tableaux croisés dynamiques avec plusieurs plages de consolidation (voir Article 43, «Utilisation de tableaux croisés dynamiques et de segments pour décrire les données»).
Si vous téléchargez fréquemment de nouvelles données dans vos classeurs source , il est judicieux de nommer les plages, y compris vos données sous forme de tableau. Puis les nouvelles données sont automatiquement inclus dans la consolidation. Vous pouvez également choisir de sélectionner des lignes vides sous l’ensemble de données actuel. Lorsque vous remplissez les lignes vides avec de nouvelles données, Excel récupère les nouvelles données lors de la consolidation. Un troisième choix consiste à faire de chaque plage de données une plage dynamique. (Voir le Article 21, «La fonction DECALER», pour plus d’informations.)