La consolidation par position n'est possible que si les étiquettes de lignes et de colonnes sont identiques.
Exercice 1 – consolidation par position
Un peu de pratique !
Dans l'exercice ci-dessous, vous avez le chiffre d'affaires, le coût et la marge dégagé dans trois pays et qui sont répartis par trimestre. Il y a un onglet pour chaque trimestre de l’année en cours.
Votre travail : Vous devez créer un onglet afin d'avoir une consolidation des trois trimestres selon la méthode de "consolidation par position". (Cf : cours sur la consolidation de données)
Petite astuce : si vous voulez que les étiquettes de ligne et de colonnes restent identiques vous pouvez utiliser la fonction "validation de données" pour n’autoriser que des libellés que vous aurez choisis (ex : dans ce cas-ci nous n’avons que trois choix : CA, Coûts et Marge ou bien encore Russie, Chine et France).
En premier lieu, on crée un nouvel onglet sur lequel figurera le tableau de consolidation. On sélectionne une cellule de cet onglet, c’est là qu’apparaitra le tableau. Ici, on sélectionne simplement la cellule A1. On va ensuite dans « Données » puis on clique sur « Consolider »
À partir de là une fenêtre s’ouvre :
Il faut changer la fonction en adéquation avec ce que nous cherchons à obtenir. Ici on veut la somme du CA, des coûts et de la marge par pays. On sélectionne donc « Somme » (la valeur par défaut).
Au niveau de la référence, on sélectionne les différentes feuilles de données. Elles se situent dans le même classeur mais sur des onglets différents :
Les données peuvent se trouver dans des classeurs différents, il suffit alors de cliquer sur « parcourir » et d’aller chercher à chaque fois le fichier puis le classeur et enfin le ou les onglets concernés.
Petite astuce : On peut ouvrir au préalable les classeurs et sélectionner directement les feuilles de données qui s'y trouvent et dont nous avons besoin lors du choix des références. Cela permet un gain de temps car on n’a pas besoin de passer par « parcourir » à chaque fois.
On sélectionne ensuite les tables de valeurs et on clique sur "ajouter" à chaque fois pour qu’elles s’affichent dans « Toutes les références ».
Petite astuce : Nommez au préalable les plages de données, cela sera plus simple pour vous lors de cette étape (sélectionner les différentes références).
Les onglets « ligne du haut » et « colonne de gauche » permettent de prendre en compte les étiquettes pour qu’Excel restitue les données dans le bon ordre. Ici les étiquettes de lignes et de colonnes sont parfaitement identiques, il n’est donc pas utile de les sélectionner.
Cependant, si on ne les coches pas, voici ce qu’on obtient :
Il n’y a aucune étiquette de ligne et de colonne.
La première solution est donc de finalement cliquer sur « ligne du haut » et « colonne de gauche ». On obtient donc ceci :
Ou on peut également choisir au préalable de créer un tableau compte rendu sur la même base que nos tableaux sources mais avec un contenu vide et on sélectionne tout le tableau avant de faire la consolidation à la place d’une seule cellule. On sélectionne ensuite « ligne du haut » et « colonne de gauche » :
Ce qui nous donnera un tableau avec la même mise en forme que nos tableaux sources :
« Lier aux données source » permet, si l’on change une ou plusieurs données dans les tables de données en références (donc ici au niveau des onglets T1, T2, T3 ou T4) de mettre automatiquement à jour le tableau de consolidation. On peut ne pas cocher cet onglet et effectuer une mise à jour, seulement celle-ci sera manuelle.
On obtient donc ceci :
On peut voir qu’il y a des petits « + » sur le côté. Ceux-ci servent à voir le détail des informations contenues dans les cellules. Par exemple, si l’on clique sur la premier « + » on obtient :
On peut donc voir le détail des informations prises en compte dans la somme du CA pour les différents pays. Cette fonction permet de disposer des différentes informations sans devoir aller les rechercher dans les différents fichiers et permet également un contrôle des calculs automatiques.