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 à POITIERS et LIMOGES.
Pour l’année N, chaque usine a fait parvenir au siège de la société les chiffres suivants :
POITIERS :
Quantités produites en année N :
- 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 N+1:
- Augmentation de la production des fauteuils 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.
LIMOGES
Quantités produites en année N :
- 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 N+1 :
- Augmentation de la production du Fauteuil LUXUS PARIS de 1/16ème .
- La chaîne de siège tissu, rénovée en 2017 sera en mesure de produire davantage. Cette augmentation est estimée à 1/5ème par rapport à 2017
- Augmentation de la production des autres sièges cuir et bois de 6%.
- Sièges cuir : on envisage une stabilité de la production.
TRAVAIL A FAIRE
1) A l’aide du tableur EXCEL, présenter sous forme de tableau les résultats et prévisions des 2 usines. (prévoir 1 tableau par feuille de calcul)..
2) Sur une troisième feuille, construire un tableau présentant le total des données des deux tableaux précédents..(utiliser les fonctions de consolidation)
3) Sur une quatrième feuille, construire un tableau présentant la moyenne des productions et prévisions des 2 usines par type de siège fabriqué. (utiliser les fonctions de consolidation)
Dans l’exemple qui va suivre, le tableau de synthèse sera construit automatiquement. Il n’est donc pas nécessaire d’en élaborer la mise en forme à l’avance comme dans la méthode de liaison par les formules de calcul (voir exercice vacancextra).
On va consolider 2 tableaux reprenant le chiffre d’affaires des représentants par type d’articles vendus. Le 1er tableau (conçu sur une première feuille de calcul) contiendra les données du 1er trimestre, le second (conçu sur une seconde feuille de calcul) contiendra les données du 2ème trimestre. On constatera que lors du 2ème trimestre un nouveau représentant apparaît à la place d’un autre, et qu’une nouvelle catégorie d’article apparaît.
Après saisie les tableaux se présentent sous la forme suivante (volontairement, les tableaux ont été positionnés à des emplacement différents dans les 2 feuilles de calcul.
1er tableau sur la feuille (onglet)
« Trimestre 1 »
2ème tableau sur la feuille (onglet) « Trimestre 2 »
La méthode de consolidation est la suivante :
- On va se positionner dans une nouvelle feuille de calcul que l’on va nommer « Semestre ». On sélectionne la cellule à partir de laquelle le tableau sera construit.
- On utilise l’outil « Consolider » du ruban Données
- Consolider. La boîte ci-contre s’ouvre :
Par défaut c’est la fonction « SOMME » qui est sélectionnée dans la zone « Fonction« . Il existe d’autres fonctions comme par exemple moyenne, max, min, ecart-type, etc..
En bas à gauche de la boîte de dialogue, les étiquettes » Ligne du haut » et » Colonne de gauche » sont inactives. Si on coche ces deux cases, le tableau sera construit avec le nom des colonnes (ici le nom des représentants) et le nom des lignes (ici les différents catégories d’articles).
4. Cliquer dans une cellule quelconque de la feuille « TRIMESTRE1 » pour la rendre active.
5. Sélectionner à présent la plage de cellule A1:E4 comprenant les données de la feuille « TRIMESTRE1 » à consolider dans la feuille « SEMESTRE », ainsi que les titres de lignes et de colonnes.
Dans la zone « Référence » de la boîte de dialogue s’affiche :
Cliquer sur le bouton « Ajouter » pour valider. La zone choisie devient une « source » d’information de la feuille « SEMESTRE ». Elle s’ajoute dans le cadre « Toutes les références« .
Répéter l’opération pour la feuille « TRIMESTRE2 »
6. Cliquer dans une cellule quelconque de la feuille « TRIMESTRE2 » pour la rendre active.
7) Sélectionner à présent la plage de cellule A2:E6 contenant les données de la feuille « TRIMESTRE2 » à consolider dans la feuille « SEMESTRE », ainsi que les titres de lignes et de colonnes. (Il est intéressant de noter que la zone sélectionnée dans la feuille « TRIMESTRE1 » est proposée par défaut).
Dans la zone « Référence » de la boîte de dialogue ci-contre s’affiche alors :
Cliquer sur le bouton pour valider. La zone choisie devient une « source » d’information de la feuille « SEMESTRE ». Elle s’ajoute dans le cadre » Toutes les références« .
IMPORTANT :
- Avant de quitter la boîte de dialogue, il faut mettre une croix dans l’option « Lier aux données source » si l’on désire créer un lien dynamique entre la source et la feuille de synthèse (c’est à dire que les modifications qui seraient apportées dans la feuille TRIMESTRE1 ou TRIMESTRE2, seraient automatiquement répercutées sur la feuille SEMESTRE). Attention, la création d’un lien dynamique va alourdir votre fichier. Il est parfois préférable de recommencer la consolidation pour mettre à jour les tableaux de grande taille.
- Cocher les cases « Lignes du Haut » et/ ou » Colonne de gauche » si vous avez inclus dans votre sélection les en têtes de colonnes et/ ou les en têtes de ligne. (C’est le cas dans notre exemple).
- Valider avec le bouton « OK«
Vous obtiendrez le tableau consolidé ci-dessous :
NB : des signes « + » apparaissent sur le coté de la feuille, ils permettent de développer et faire apparaître les calculs. Ces options seront vues dans le cadre du « monde plan »
Les tableaux POITIERS et LIMOGES ont été saisis.
Les tableaux TOTAUX et MOYENNE ont été obtenus à l’outil « Consolider » du ruban « Données«
Feuille de calcul n°3 nommée « Production totale » (avec l’option ‘lier aux données source’ ce qui entraîne une présentation en mode plan)
Lorem ipsum dolor sit amet, consectetur Feuille de calcul n°3 nommée « Moyenne des productions » (avec l’option ‘lier aux données source’ ce qui entraîne une présentation en mode plan)
