En réponse à une question sur notre forum: Récupérer le contenu de cellules spécifiques de plusieurs fichiers Excel

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

En réponse à une question sur notre forum: Récupérer le contenu de cellules spécifiques de plusieurs fichiers Excel

Tout récemment, un lecteur a posé la question suivante dans notre forum:

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 précises 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 :

Annie Tremblay
Écrit il y a 5 jours
J'ai eu la chance que mon employeur m'offre l'ensemble des parcours.

C'est vraiment une opportunité d'apprendre et d'élargir mes connaissances. Sophie Marchand est une excellente formatrice et la combinaison des vidéos et exercices aident a consolider les apprentissages. Merci pour tout!

Amélie Tremblay
Écrit il y a 4 mois
Une des rares formations où nous quittons avec une boîte à outils.

Très bonne formation applicable à ma réalité. Une des rares formations où nous quittons avec une boîte à outils.

Cécile BERNARD
Écrit il y a 5 mois
Je ne regrette absolument pas mon choix !

Excellent !! Je suivais déjà Sophie via Twitter ou directement sur le site du CFO masqué (forum et astuce) et quand il a fallu déterminer quel organisme de formation prendre, j'ai naturellement pensé au CFO Masqué. Je ne regrette absolument pas mon choix !

Stephanie Lambert
Écrit il y a 2 ans
Enfin du contenu que je ne connaissais presque pas

J'ai enfin pu avoir l'expérience d'un nouvel apprenant et comme je m'y attendais, elle fut très positive. Les notions sont bien expliquées et illustrées avec des exemples concrets. Il est très utile d'avoir les mêmes tables de données pour pouvoir reproduire les exemples. Les documents pdf fournis sont clairs et bien faits.


CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

2 réflexions sur “En réponse à une question sur notre forum: 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

Laisser un commentaire

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

Scroll to Top