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

Publié le 08 septembre 2018
par Sophie Marchand M.Sc.
Power Query Drill Down

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 :

Question Power Query Forum

 

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.

Power Query Drill Down

 

Nous avons ensuite renommé notre requête A5.

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.

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.

Paramètre chemin

 

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.

Power Query Modifier la source

 

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

Créer une fonction

 

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.

Power Query Dossier

 

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.

Power Query Dossier Fusionner Chemin

 

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).

Power Query Appeler 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.

Appeler une fonction

 

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

Power Query Résultat final

 

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.

Power Query récupérer contenu

 

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.

Power Query Requête une seule étape

 


 

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 :

Excel – Introduction à Power Query et au langage M

CFO-Masque_Formations-en-ligne_FBLa 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é.

6 réflexions sur “Récupérer le contenu de cellules spécifiques de plusieurs fichiers Excel”

  1. Bonjour,
    Que se passe-t-il si les classeurs (les feuilles de calcul) ne sont pas toutes identiques avant la cellule A5. Toutes les feuilles ne contiennent rien (c’est l’exemple pris ici), toutes les feuilles contiennent quelque chose en A1, A2, A3, A4 et A5, c’est l’objet de la remarque faite en fin d’article. Mais en supposant que l’on puisse voir un mélange (A3 et A5 non vide, A5 vide, etc …), A la lecture de l’article (je n’ai pas testé), j’ai l’impression que qu’à l’étape “drill down”, power query se place à la première cellule non vide. Ai-je bien compris ?
    Cordialement,
    Laurent

    1. Bonjour Laurent,

      Power Query se positionne à la première cellule non vide de la première colonne si et seulement si les données du fichier Excel ne sont pas sous forme de tableau. Pour travailler avec Power Query, il est toujours préférable que les données soient sous forme de tableaux.

      Dans le cas illustré , j’ai créé des fichiers Excel d’exemples avec seulement 2 cellules chacun par manque de temps et la première valeur est dans la première colonne donc Power Query fait abstraction de toutes les lignes vides qui se présentent avant cette valeur (sur la ligne 5).

      S’il y avait des valeurs en A1, A2, A3, A4 et A5, tel que mentionné dans l’article, on aurait 5 lignes et il faudrait récupérer le contenu de la ligne 5 avec la mention {4}.

      Maintenant, si les valeurs à récupérer sont toutes sur des lignes différentes, il faudrait simplement savoir, dans chacun des fichiers, sur quelle ligne se retrouve la valeur. Ainsi, il faudrait repartir d’une requête présentant l’ensemble des répertoires des fichiers dans une colonne et les numéros de lignes des cellules à aller récupérer dans une autre colonne. En supposant que cette colonne se nommait Index, on n’aurait plus qu’à utiliser la mention {Index} dans notre code M plutôt que la référence à un numéro de ligne fixe.

      En espérant avoir bien répondu à votre question.

      Au plaisir,

      Sophie

  2. Bonjour,

    j’ai un problème avec un import de fichier excel que je compte combiner car je recois le même tous les mois, Query ne veux pas prendre la premier colonne, Il y a pourtant de la donnée à partir de la ligne 5 de cette même colonne. Je ne souhaite pas ouvrir le fichier source (pour le transformer) mais simplement le déposer dans un dossier mais il faudrait que cette colonne A s’affiche!

    Merci pour votre aide!

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut