Pour un utilisateur d’Excel, copier/coller des données est de très loin la tâche la plus courante … et la plus rébarbative !

Souvent, les fichiers de suivi sont organisés par onglet (et il peut y en avoir beaucoup !).

fichier du suivi des ventes : 1 onglet par mois
fichier du suivi des transporteurs : 1 onglet par fournisseur
fichier du suivi des inventaires : 1 onglet par entrepôt

Chaque semaine ou chaque mois, vous vous astreignez au même rituel. Pour mettre à jour vos indicateurs de suivi consolidés, vous copiez/collez les données de chaque onglet dans un nouvel onglet.

Grâce à Power Query, vous pouvez très simplement automatiser ces tâches répétitives et sources d’erreur.

Je vous explique en quelques étapes comment le faire sans VBA et sans écrire une seule ligne de code.

En moins de 10 minutes, vous allez automatiser la consolidation des données de tous vos onglets. Un retour sur investissement immédiat …

Dans cet exercice, notre objectif est de réaliser un tableau de bord sur Excel pour suivre les articles en rupture dans nos magasins.

Nous disposons d’un fichier Excel qui contient trois onglets. Chaque onglet contient les données d’un magasin. Les onglets ont tous la même structure : nombre de colonnes, nom des colonnes, type de données.

Pour chaque magasin, nous disposons des données suivantes :

La catégorie
Le code article
Le libellé de l’article
La quantité en stock

Pour créer notre tableau de bord, nous avons besoin de constituer une table qui contient les données de tous les magasins. Nous allons utiliser Power Query pour réaliser les transformations suivantes :

charger les données de chaque onglet
créer une nouvelle table
ajouter les données de chaque onglet dans la nouvelle table

Importer les données du 1er onglet du fichier

La première étape consiste à charger le premier onglet du fichier Excel. Cela va nous permettre de récupérer la liste de tous les onglets.

Créer un nouveau fichier Excel et lancer Power Query (Menu Données > Obtenir des données > Lancer l’éditeur Power Query).
Depuis le menu Accueil, sélectionnez Nouvelle requête > Nouvelle source > Fichier > Classeur Excel
Sélectionner le fichier et cliquer sur Importer
Dans la fenêtre Navigateur, sélectionner le premier onglet et cliquer sur OK

Supprimer les 3 dernières étapes appliquées

Power Query charge les données et les affiche dans une table.

Dans le panneau Paramètres d’une requête, nous pouvons voir que Power Query a effectué plusieurs transformations.

La première étape - Source - contient la liste de toutes les tables dont les onglets qui nous intéressent. Nous allons supprimer les autres étapes pour repartir de cette liste.

Sélectionner la dernière étape - Type modifié - et cliquer sur la croix pour la supprimer
Renouveler cette opération pour les deux étapes précédentes : En-têtes promus et Navigation

Power Query affiche la liste de toutes les tables du fichier Excel.

Filtrer la liste pour ne conserver que les onglets

Lorsqu’il charge un fichier Excel, Power Query liste l’ensemble des tables du fichier. Pour être sûr de ne charger que les données des magasins, nous allons filtrer la liste retournée.

Cliquer sur le menu déroulant de la colonne Kind
Cocher uniquement Sheet (onglet) et cliquer sur OK

La liste contient à présent uniquement les onglets du fichier Excel.

Conserver uniquement les colonnes Name et Data

La liste contient de nombreuses colonnes. Pour notre exercice, seules les colonnes Name et Data nous intéressent. Nous allons supprimer les autres.

Sélectionner les colonnes Name et Data (avec la touche Maj Flottante)
Clic droit et sélectionner Supprimer les autres colonnes

La liste ne contient à présent uniquement les colonnes qui nous intéressent.

Développer la colonne Data

Chaque ligne de la liste contient le nom de l’onglet (colonne Name) et une table contenant toutes les données de l’onglet (colonne Data).

Si on clique dans une cellule à côté du mot Table, Power Query affiche en bas de la fenêtre les données de l’onglet correspondant.

Nous allons maintenant récupérer les données de chaque onglet pour constituer notre table unique.

Cliquer sur le menu déroulant de la colonne Data
Décocher Utiliser le nom de la colonne d’origine comme préfixe
Cliquer sur OK

La liste contient à présent toutes les données de tous les onglets.

Utiliser la première ligne pour les en-têtes

Pour pouvoir utiliser notre nouvelle table, nous allons devoir effectuer plusieurs transformations.

Nous allons commencer par récupérer le nom des colonnes.

Dans le menu Accueil > Transformer, cliquer sur Utiliser la première ligne pour les en-têtes
Renommer l’en-tête de la première colonne : Magasin

Supprimer les lignes d’en-têtes des autres onglets

Dernière transformation avant de pouvoir utiliser notre table : supprimer les lignes d’en-têtes de chaque onglet.

Cliquer sur le menu déroulant de la colonne GT
Décocher le nom GT et cliquer sur OK

Les données des différents onglets sont maintenant consolidées dans une table unique.

Charger les données de la table dans Excel

Dernière opération avant d’exploiter vos données : Enregistrer votre requête. Dans un premier temps, n’enregistrer que la connexion est une bonne pratique.

Cela vous laisse le choix de son utilisation : Chargée dans Excel sous forme de tableau, TCD ou graphique ou dans Power Pivot pour la relier à d’autres tables.

Cliquer sur le menu déroulant du bouton Fermer et charger
Sélectionner Fermer et charger dans …

Nous avons maintenant les données pour réaliser notre tableau de bord.

Répartition en % des articles en rupture sur tous les magasins ou par magasin
Répartition des articles en rupture par catégorie
Répartition par magasin

En fonction de nos objectifs et de nos priorités, nous pouvons analyser nos données en croisant les données des différents magasins.

Ensuite, vous pourrez modifier les données ou même ajouter un nouvel onglet. Et vos indicateurs de suivi consolidés se mettront à jour automatiquement et dynamiquement.

Et maintenant, à vous de jouer ! Prenez vos données et suivez les étapes pas à pas pour construire votre propre tableau de bord.

Gestion de stocks, approvisionnement, ventes, finances. Je suis curieux de connaître les cas d’usages que vous rencontrez dans votre quotidien. N'hésitez pas à me laisser un commentaire.

AUTRES ARTICLES DATA SUPPLY CHAIN