Récupérer le contenu de cellules spécifiques de plusieurs fichiers Excel

Tout récemment, un lecteur a demandé, sur notre forum, comment récupérer le contenu de cellules spécifiques de plusieurs fichiers Excel, à l’aide de Power Query :

Cet article vise donc à expliquer comment il est possible de récupérer le contenu de certaines cellules précises provenant de plusieurs fichiers Excel différents.

Récupérer le contenu de cellules spécifiques provenant de plusieurs fichiers Excel

Dans un premier temps, nous avons importé un seul des fichiers Excel dans Power Query et de là, nous avons cliqué sur la cellule contenant la valeur avec le bouton droit de la souris et nous avons choisi l’option “Drill Down”.

Attention! Ici, dans notre exemple, il n’y a des valeurs que dans les cellules A5 et D29 de nos fichiers Excel. Toutes les autres cellules sont vides. Ce faisant, les premières lignes vides n’apparaissent pas dans Power Query et la cellule A5 apparaît donc dans la ligne 1 de la colonne 1.

Nous avons ensuite renommé notre requête A5.

À cette étape, ce que nous voulons, c’est créer une fonction à partir de cette requête. En effet, on voudra appeler cette fonction et l’appliquer sur chacun des fichiers de notre dossier. Ce faisant, nous avons d’abord créé un nouveau paramètre.

Nous avons nommé ce paramètre Chemin, nous lui avons attribué un type texte et nous avons collé le répertoire de notre fichier actuel.

Nous avons ensuite inséré ce nouveau paramètre dans notre requête. Pour ce faire, nous avons cliqué sur l’étape “Source” et dans la barre de formules, nous avons remplacé le répertoire (ainsi que les guillemets) par notre paramètre. À noter que si vous ne voyez pas la barre de formules, vous devez l’activer dans le menu Affichage.

Nous avons ensuite pu créer une fonction à partir de notre requête. Nous avons appelé cette fonction RécupérerA5.

Nous avons ensuite répété le même processus afin de créer une fonction pour récupérer le contenu de la cellule D29.

Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation  des données avec nos formations sur Power Query et Power Pivot dans Excel.

Par la suite, nous avons créé une nouvelle requête qui pointe cette fois-ci, non pas sur un fichier, mais sur le dossier qui comprend tous les fichiers. Dans notre exemple, nous n’avons que 3 fichiers mais la même démarche fonctionnerait très bien s’il y avait une grande quantité de fichiers.

De là, nous avons supprimé les colonnes non nécessaires et nous avons fusionné les colonnes “Folder Path” et “Name” afin de recréer le répertoire de chacun des fichiers.

Il ne nous restait plus qu’à insérer une nouvelle colonne pour appeler notre première fonction personnalisée (puis une autre colonne pour appeler notre deuxième fonction personnalisée).

Pour ce faire, nous avons du informer Power Query sur la fonction à utiliser et la colonne comprenant les répertoires des différents fichiers.

Au final, nous avons obtenu toutes les valeurs des cellules A5 et D29 de nos 3 fichiers Excel.

La procédure ci-dessus est idéale pour un débutant, qui n’a pas à se soucier du code M. Toutefois, si vous êtes curieux à propos du code M, retournez sur l’étape du “Drill Down”. Vous observerez la formule ci-dessous. Cette formule indique d’aller chercher l’information de la première ligne, de la première colonne, de la table résultante de l’étape précédente. En effet, Power Query réfère à la première ligne comme étant 0 et non 1. Bien entendu, si nous n’avions pas eu de cellules vides avant notre cellule A5, il aurait fallu indiquer la ligne 4.

Fort de ces connaissances, nous aurions pu écrire notre requête en une seule étape, comme spécifié ci-dessous. Nous utilisons Item=”Feuil1″ pour préciser que les cellules à récupérer sont dans la Feuil1 et Kind=”Sheet” pour indiquer qu’on cherche les valeurs dans une feuille (ou onglet, si vous préférez), et non dans une table.

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

Pour une introduction au langage M, suivez la formation Excel – Introduction à Power Query et au langage M.

Voici quelques commentaires d’apprenants ayant suivi cette formation :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.