Pourquoi forcément présenter que les nouveautés ? Et si on repartait quelques dizaines d'années en arrière !
Je vous propose de déterrer une méthode archaïque, oubliée dans les limbes du temps, qui ne se transmet qu'entre anciens🧙♂️ et qui de plus, n'a jamais été correctement documentée ni présentée (les quelques exemples trouvables passent à côté de l'intérêt de l'outil !).
On va obtenir avec cet technique une consolidation dynamique :
- Portant sur plusieurs feuilles.
- Permettant à l'utilisateur de facilement paramétrer le résultat désiré via des listes déroulante.
Fichier Excel de départ
Un classeur avec les 6 feuilles suivantes
- Janvier-Dijon
- Février-Dijon
- Mars-Dijon
- Janvier-Lyon
- Février-Lyon
- Mars-Lyon
Chaque feuille est ainsi caractérisée par 2 attributs : Mois et Ville.
Elles contiennent chacune un tableau à 2 dimensions (Une dimension Ligne = étiquettes de ligne et une dimension Colonne = étiquettes de colonne).
- Les tableaux peuvent avoir :
- des tailles différentes (plus ou moins de lignes ou de colonnes => ici plus ou moins de produits par exemple).
- des positions différentes (positionnement différent dans la feuille=> ici commencer en E8 et non en A1).
- un tri/ordre différent en colonnes et en lignes (ici des produits classés différemment).
Résultat attendu
Un tableau croisé dynamique permettant, via des listes déroutantes, d'afficher facilement diverses synthèses (consolidations) des feuilles du classeurs en fonction de certaines villes ou de toutes les villes et en fonction de certains mois ou de tous les mois.
Exemple de consolidation possibles : La somme des valeurs des tableaux pour
- La ville de Dijon (quel que soit le mois).
- La ville de Lyon (quel que soit le mois).
- Le mois de janvier (quel que soit la ville).
- ...
Prérequis
> On ajoute l'Assistant Tableau croisé dynamique à la barre d'outils Accès rapide ou à un des rubans.
Génération du tableau croisé dynamique
> On démarre l'Assistant tableau croisé dynamique.
Étape 1
> On sélectionne l'option Plages de feuilles de calculs avec étiquettes.
Contrairement à la 1ère option de l'assistant (TCD classique), ici on ne travaille pas à partir d'une liste (une base de données Excel), c'est à dire un tableau à une dimension Colonne (tableau avec seulement des étiquettes de colonnes), mais à partir de plusieurs tableaux ayant tous 2 dimensions (une dimension Colonne et une dimension Ligne).
J'ai souvent entendu/lu comme explication que c'était pour utiliser plusieurs bases de données (chose impossible au vu de la structure des tableaux de départs) 😔.
Étape2a
> On sélectionne toujours l'option Plusieurs (création manuelle) même si l'on utilise qu'un seul champ de page (on verra à l'étape suivante de quoi il s'agit).
La plupart des exemples sélectionnent la 1ère option Un seul (création automatique), pour obtenir à la fin un résultat sans intérêt, vide de sens et donc inexploitable !
Étape2b
> Via la zone Plage et le bouton Ajouter, on va "mémoriser" toutes les plages à consolider.
La manipulation est assez rapide si vos plages ont la même position et taille car après avoir ajouté la 1ère, simplement en changeant d'onglet en bas, Excel garde la dimension de la précédente plage.
> On va sélectionner dans notre cas l'option pour 2 champs de page (correspondants à nos 2 critères Mois et Ville).
Le terme "champs de pages" est l'ancien nom pour les "champs Filtres".
On aura ainsi, pour le tableau croisé dynamique généré à la fin de l'assistant, deux listes déroulantes permettant de sélectionner la ville et le mois et ainsi filtrer ce tableau croisé dynamique et obtenir la consolidation désirée.
On va devoir catégoriser chacune des plages sélectionnées et ainsi générer les valeurs présentent dans les 2 listes déroulantes. C'est la partie "mystérieuse" de cet outil dont beaucoup d'utilisateurs sont passés à côté !
> Pour cela on va :
- Sélectionner la 1ère plage dans la liste Toutes les plages.
- Affecter (saisir ou sélectionner) les étiquettes correspondantes pour les 2 champs de page via les listes Champ1 et Champ2.
- Exemple pour la feuille "Janvier-Dijon", on va saisir Janvier dans Champ1 et Dijon dans Champ2 (saisie obligatoire, les valeurs Janvier et Dijon n'ayant jamais été utilisées pour l'instant).
- On refait la même manipulation avec les autres plages dans la liste Toutes les plages.
Pour la feuille suivante "Janvier-Lyon" par exemple :- Dans Champ1, on peut soit sélectionner la valeur Janvier dans la liste (ayant déjà été saisie préalablement pour la plage Janvier-Dijon) soit la réécrire.
- Dans Champ2, Lyon n'ayant jamais été utilisé on doit forcément écrire la valeur.
- On poursuit la manipulation pour les autres plages de la liste Toutes les plages.
Étape 3
On va choisir où positionner ce nouveau tableau croisé dynamique.
Personnalisations du résultat
On peut par exemple :
- Renommer les titres des champs de page : ici Mois en B2 et Ville en B3.
- Effacer les mentions inutiles (Étiquettes de colonnes en C5, Étiquettes de lignes en B6, Somme de Valeur en B5) avec la touche Retour Arrière (BackSpace) ou en saisissant un espace à la place.
- Supprimer les valeurs/éléments Total via les outils filtres en C5 et B6.
Autres possibilités :
- On peut changer la fonction de synthèse (ici Somme est la fonction par défaut) par une autre (Moyenne par exemple). Il faudra peut-être ne plus afficher la ligne 9 et colonne H (grands totaux) si elles n'ont plus de sens avec cette nouvelle fonction (Ruban Création>Totaux généraux).
- On peut aussi changer le mode d'affichage des valeurs (En % colonne par exemple).
- On peut ajouter des champs et éléments calculés au tableau.
Comme tous tableau croisé dynamique, en cas de mise à jour des données, il faudra demander une actualisation des données.
Pour retourner dans une des étapes de l'assistant, il suffit de sélectionner une des cellules du tableau et de cliquer sur l'icône de l'assistant. Ce qui nous permettra d'ajouter/supprimer/modifier/catégoriser des plages.
Merci pour votre attention bienveillante.