La société PRETA commercialise des articles de prêt à porter.
On vous demande de concevoir un tableau, établi à partir des chiffres ci-dessous, faisant apparaître :
- le total des achats par mois pour chaque article
- le total des achats du trimestre par article
- le total des achats par mois.
Vous utliserez, pour cela, les fonctions de consolidation. Nombres d’articles achetés au cours du trimestre :
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«
Ci-après la boîte de dialogue « Consolider » après saisie et sélection des tableaux source
Ci-après, tableau obtenu avec l’outil « Consolider » du ruban « Données » :
Les symboles « + » permettent d’afficher les lignes de détail qui ont permis la construction du tableau.
Les lignes visibles ne sont que les totaux de ces lignes de détail.
