Maitrise du pack Office (Excel)
TP 4 : Consolidation dans Excel
En informatique, la consolidation permet de regrouper les données provenant de différentes
sources afin d’obtenir un rapport structuré.
La consolidation des feuilles de travail permet de synthétiser les données de plusieurs tableaux
en un seul. Un lien peut être conservé entre les zones sources contenant les données et la zone
de destination. Le mode plan permet alors d’afficher le détail de chacun des chiffres consolidés.
Une feuille de consolidation rassemble des résultats provenant d'autres classeurs de structure
identique. Ces résultats sont le fruit d'une fonction Excel. La fonction SOMME est proposée
par défaut mais d'autres comme MOYENNE, MAX, MIN, Ecartypep, Ecartype, Nbval…
peuvent être choisies.
La consolidation peut s’effectuer de deux manières différentes :
• Par position : La consolidation est basée sur les références des zones sources ;
• Par catégorie : La consolidation est basée sur les étiquettes de lignes et/ou de colonnes
(les libellés de la colonne de gauche et de la ligne du haut).
1. Par position
Ce mode convient à la consolidation de tableaux présentant une structure identique. Il suffit de
donner les références des zones à consolider pour que la consolidation puisse s’effectuer. Seules
les zones où figureront des valeurs sont à indiquer dans la zone de destination. Les libellés de
lignes et de colonnes ne sont pas à inclure dans les zones (que ce soient les zones sources ou la
zone de destination).
Pour créer une consolidation en combinant des données organisées de manière similaire sur
plusieurs feuilles de calcul et classeurs, il faut suivre les étapes ci-après.
• Ouvrir tous les fichiers (classeurs) contenant les données à consolider ;
• Sur les rubans de données, sélectionner Outils de données, puis Consolider ;
ERROUSSO Hanae
Maitrise du pack Office (Excel)
• Sélectionner la méthode de consolidation ;
• Sélectionner les données, y compris les étiquettes, puis cliquer sur Ajouter ;
• Répéter l'étape précédente pour chaque feuille de calcul ou classeur contenant les
données en question ;
ERROUSSO Hanae
Maitrise du pack Office (Excel)
• Cocher les cases « première ligne », « colonne de gauche » et « créer des liens vers la
source de données » (il est inutile de cocher ces cases si les étiquettes et les liens en
direct ne sont pas nécessaires) et cliquer sur le bouton OK.
2. Par catégorie
Ce mode convient à la consolidation de données non ordonnées comportant des titres de lignes
et/ou de colonnes (étiquettes) identiques. Excel retrouve les données correspondant à ces titres
et les consolide, tout en gardant un lien entre données sources et zone de destination.
La procédure est quasiment identique à la précédente hormis le fait qu’il est nécessaire d’inclure
dans les références des zones sources et de la zone de destination les libellés des lignes et/ou
des colonnes afférentes aux valeurs. La consolidation s’effectuera dans l’ordre des étiquettes
des lignes de la zone de destination.
Il faut commencer simplement par reproduire une structure d’accueil des données consolidées
(copier le tableau et effacer les données pour garder les libellés de lignes et colonnes), puis
sélectionner la zone de destination y compris les titres de ligne et de colonne.
3. Créer ou modifier un tableau simultanément sur plusieurs feuilles
Certains travaux exigent de multiplier les feuilles de calcul, comportant des formats ou des
données proches. Par exemple : une même structure de tableau, mais avec des données
différentes sur 15 feuilles. Evidemment, il est possible de copier/coller ce tableau 15 fois. Mais
il est plus simple d’apporter des modifications à toutes les feuilles de calculs simultanément.
Pour cela :
• Clic droit sur l'onglet de la feuille active ;
• Cliquer sur Sélectionner toutes les feuilles ;
• Faire les modifications destinées aux autres feuilles sur la feuille active.
ERROUSSO Hanae
Maitrise du pack Office (Excel)
Dans Excel, il est possible d’utiliser la commande Déplacer ou Copier pour créer une feuille
avec le même format que la feuille active.
A cette fin, cliquer sur le Barre d'onglets de la feuille au format souhaité, et faire un clic droit
pour sélectionner Déplacer ou copier dans le menu contextuel. Ensuite, dans la boîte de dialogue
contextuelle, cocher l’option Créer une copie.
ERROUSSO Hanae
Maitrise du pack Office (Excel)
Une fois le bouton Ok appuyé, Une nouvelle feuille est créée avec le même format avant la
feuille CAS rapp.
4. Liaisons entre feuilles et classeurs
La création d'un lien entre deux feuilles ou entre deux classeurs, est en réalité très simple. En
fait chaque fois qu’une formule est créée en y insérant des références, des liens entre différentes
cellules sont établis. Mais il ne s'agit pas de liens à proprement parler, car les différentes
références se trouvent sur la même feuille. Quand une formule est commencée (avec le signe
=) et qu’une cellule se situant dans une autre feuille est pointée, (en sélectionnant la feuille avec
l'onglet), le lien est créé automatiquement. La différence dans la formule est l'apparition du nom
de la feuille qui a été liée (feuille source) à côté de la référence.
Le principe pour créer une liaison entre classeurs est le même que pour les feuilles, à la
différence qu'il faut les ouvrir.
Si les classeurs changent d’emplacement, il arrive que la liaison entre la source et la cible soient
perdues ; il est cependant possible de la rétablir de même que de la modifier ou de la rompre
volontairement. Pour y parvenir, dans le groupe "Connexions" de l'onglet "Données", cliquer
sur Modifier les liaisons.
5. Exercices
Exercice 1 :
Dans une feuille supplémentaire, insérez un tableau consolidant les trois premières feuilles du
classeur.
ERROUSSO Hanae
Maitrise du pack Office (Excel)
Exercice 2 :
Dans une feuille supplémentaire, insérez un tableau pour consolider les 4 premières feuilles du
classeur.
Exercice 3 :
Agrégez les heures travaillées dans un tableau de consolidation.
Exercice 4 :
Le fichier de travail est un dossier qui comprends 3 classeurs. Sur le quatrième classeur (à
créer), agrégez les données des produits et des villes.
Exercice 5 :
La société Novopra fabrique et commercialise des sièges de bureau de luxe. Ces sièges sont
fabriqués sur 2 sites de production situés à Tarbes et Pau. En 2000, chaque usine a fait parvenir
au siège de la société les chiffres suivants :
ERROUSSO Hanae
Maitrise du pack Office (Excel)
Tarbes :
Quantités produites et 2000 :
• Fauteuil Cuir et Bois LUXUS PARIS : 123 580 unités,
• Fauteuil Cuir et Bois TYPO CONFORT : 112 120 unités,
• Siège Cuir PRESIDENT : 25 250 unités,
• Siège Cuir AMBASSADEUR : 78 920 unités,
• Siège Cuir SENATEUR : 67 430 unités,
• Siège Tissu MINISTRE : 167 620 unités.
Prévisions 2001 :
• Augmentation de la production des Sièges Cuir et Bois de 3%,
• Travaux sur chaîne de montage des Sièges Tissus : baisse de production de 15%,
• Sièges Cuir : 200 unités supplémentaires pour chaque siège.
Pau :
Quantités produites et 2000 :
• Fauteuil Cuir et Bois LUXUS PARIS : 166 820 unités,
• Siège Cuir PRESIDENT : 33 120 unités,
• Fauteuil Cuir et Bois TYPO CONFORT : 145 700 unités,
• Siège Tissu MINISTRE : 198 310 unités.
• Siège Cuir AMBASSADEUR : 65 420 unités,
• Siège Cuir SENATEUR : 55 670 unités,
Prévisions 2001 :
• Augmentation de la production du Fauteuil LUXUS PARIS de 1/16ème,
• La chaîne de Siège Tissu, rénovée en 2000 sera en mesure de produire davantage. Cette
augmentation est estimée à 1/5ème par rapport à 2000.
• Augmentation de la production des autres Sièges Cuir et Bois de 6%,
• Sièges Cuir : on envisage une stabilité de la production.
Consignes de réalisation :
• Présenter les résultats et prévisions des deux usines (un tableau par usine) ;
• Construire un tableau présentant le total des données des deux usines (Utiliser les fonctions
de consolidation) ;
• Construire un tableau présentant la moyenne des données des deux usines (Utiliser les
fonctions de consolidation) ;
• Enregistrer le fichier sous le nom NOVOPRA.XLS.
ERROUSSO Hanae
