Comment répartir une base sur plusieurs feuilles Excel avec les tableaux croisés dynamiques sur Excel ?

Dans ce tutoriel, je vais vous montrer comment il est très simple et très rapide de pouvoir dispatcher automatiquement les valeurs d'une base de données en fonction des éléments d'une colonne dans plusieurs feuilles de calcul, en utilisant une astuce reposant sur l’utilisation des tableaux croisés dynamiques.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Répartir les données d’une base sur des feuilles de calcul différentes

Ce tutoriel est une réponse à la question de Julie qui souhaite savoir comment utiliser les tableaux croisés dynamiques pour obtenir une répartition des valeurs contenues dans une base de données au sein de plusieurs feuilles de calcul.

Pour tout savoir les tableaux croisés dynamiques, vous pouvez consulter ma formation dédiée, en cliquant ici.

Ici, nous disposons d'une base de données que nous allons souhaiter donc dispatcher dans différentes feuilles de calcul : afin que sur chacun d’entre elles, nous puissions retrouver les ventes réalisées par tous les commerciaux dans un pays donné :

La méthode la plus classique va consister à dupliquer la feuille de calcul afin de ne conserver que les données qui vont nous intéresser.

Par exemple ici pour pouvoir dupliquer rapidement cette feuille de calcul nous allons effectuer un clic droit sur l'onglet de la feuille de calcul et nous allons cliquer sur « Déplacer ou copier » :

Sur la boîte de dialogue qui s’affiche, nous choisissons de créer une copie afin de conserver l'original inchangé.

Puis nous validons la copie de la feuille en appuyant sur le bouton [Ok].

Ensuite, nous renommons la nouvelle feuille de calcul du même nom que le pays dans lequel ont été réalisées les ventes qui nous souhaiter conserver, ici nous allons vouloir stocker les données des ventes réalisées en France :

Nous allons ensuite supprimer toutes les données qui ne concerne pas la France, en les sélectionnant, puis en effectuant un clic droit sur les en-têtes de lignes pour choisir « Supprimer » :

Cela étant fait, pour obtenir un nouveau pays nous allons répéter la même opération, en sachant que nous allons pouvoir optimiser légèrement le processus :

• Pour dupliquer rapidement une feuille de calcul nous pouvons appuyer sur la touche [Ctrl] du clavier et nous allons glisser l’onglet de la feuille de calcul vers la droite (le curseur de la souris prend alors la forme d’une feuille avec un symbole « + »). Ensuite, en relâchant le curseur de la souris, la feuille sera automatiquement dupliquée
• Ensuite, pour supprimer des lignes sélectionnées sur la feuille de calcul, plutôt que d'effectuer un clic droit afin de choisir de supprimer les lignes, nous pouvons appuyer sur les touches [Ctrl]+[-]

Il ne restera plus qu'à répéter cette même opération afin de filtrer toutes les données et d'obtenir ainsi une feuille pour chaque élément de notre base de données.

Après avoir réalisé le découpage de chacune des feuilles de calcul, nous allons effectivement retrouver comme nous le voulions la répartition des ventes en fonction des pays :

En revanche, si vous avez réalisé les opérations tout comme moi, vous avez pu constater qu’effectuer l'ensemble de ces tâches peut demander un certain temps.

Cela est d’autant plus vrai qu’ici, nous avons une mini base de données, dans laquelle il n'y a que six pays différents.

Mais nous pourrions imaginer avoir à traiter une immense base de données avec des dizaines, voir même des centaines de pays différents.

Ce qui ne pourrait pas être réalisé comme cela, à la main.

Mais heureusement il existe une fonctionnalité dédiée au sein du tableau croisé dynamique qui va permet d'effectuer cette opération de manière très simple et surtout très rapide.

2. Répartir automatiquement les données d’une base sur plusieurs feuilles grâce aux TCD

Ici, nous allons commencer par supprimer tous les onglets insérés dans la partie précédente, qui maintenant ne vont plus être utiles.

Ensuite, nous allons créer un tableau croisé dynamique à partir de la source des données, depuis le menu Insertion / « Tableau croisé dynamique » :

Puis, nous allons alimenter celui-ci en y intégrant les différents éléments de la base source, depuis le panneau latéral situé à droite :

Ensuite, l’astuce va consister à glisser le champ que nous allons souhaiter utiliser pour définir les différentes feuilles de calcul dans la zone des filtres :

Nous allons donc faire glisser le champ « Pays » de la zone des lignes vers la zone des filtres.

Cela étant fait, il nous suffira de nous rendre dans le menu « Analyse du tableau croisé dynamique », ce menu qui ne sera affiché que lorsque nous sélectionnons une cellule du tableau croisé dynamique et afin de dérouler des « Options » afin de cliquer sur « Afficher les pages de filtres de rapport » :

Excel nous demande alors de confirmer que nous voulons effectivement créer des nouvelles pages en fonction du nom des pays.

Nous pouvons valider en appuyant sur le bouton [OK] afin de constater qu’Excel a bien créé un onglet de feuille de calcul par notre pays, en y insérant les données de vente qui vont correspondent.