Fonction Excel pour lister les fichiers dans un dossier sans VBA
La liste des fichiers dans un dossier est l’une des activités qui ne peuvent pas être réalisées à l’aide de formules Excel normales. Je pourrais vous dire de vous tourner vers les macros VBA ou PowerQuery, mais tous les utilisateurs non-VBA et non PowerQuery fermeraient ce message instantanément. Mais attendez! Reculez du bouton de fermeture, il y a une autre option.
Pour lister les fichiers dans un dossier, nous pouvons également utiliser une fonctionnalité peu connue d’Excel version 4, qui fonctionne toujours aujourd’hui, la fonction FILES.
Si vous recherchez dans la liste des fonctions Excel, FILES n’est pas répertorié. La fonction FICHIERS est basée sur une ancienne fonctionnalité Excel, qui doit être appliquée d’une manière spéciale. Les instructions ci-dessous vous montreront étape par étape comment l’utiliser.
La première étape consiste à créer une plage nommée, qui contient la fonction FILES. Dans le ruban Excel, cliquez sur Formules -> Définir le nom
Dans le Nouveau nom fenêtre définissez les critères suivants:
Cliquez sur D’accord pour fermer le Nouveau nom la fenêtre.
La deuxième étape consiste à configurer la feuille de calcul pour utiliser la plage nommée.
Dans la cellule A1 (ou selon la référence de cellule utilisée dans le Fait référence à ) entrez le chemin du dossier à partir duquel lister les fichiers, suivi d’un astérisque
. L’astérisque est le caractère générique pour rechercher n’importe quel texte, il répertorie donc tous les fichiers du dossier.
=INDEX(listFiles,1)
Formule FILES – premier résultat
Le résultat de la fonction sera le nom du premier fichier du dossier.
=INDEX(listFiles,2)
Pour récupérer le deuxième fichier du dossier, entrez la formule suivante
=INDEX(listFiles,ROW()-ROW(A$2))
Il serait pénible de changer le numéro de référence du fichier dans chaque formule individuellement, surtout s’il y a des centaines de fichiers. La bonne nouvelle est que nous pouvons utiliser une autre formule pour calculer automatiquement le numéro de référence.
>La fonction ROW () est utilisée pour récupérer le numéro de ligne d’une référence de cellule. Lorsqu’il est utilisé sans référence de cellule, il renvoie le numéro de ligne de la cellule dans laquelle la fonction est utilisée. Lorsqu’il est utilisé avec une référence de cellule, il renvoie le numéro de ligne de cette cellule. En utilisant la fonction ROWS, il est possible de créer une liste séquentielle de nombres commençant à 1 et augmentant de 1 pour chaque cellule dans laquelle la formule est copiée.
FICHIERS contenant des erreurs
=IFERROR(INDEX(listFiles,ROW()-ROW(A$2)),"")
Liste des types spécifiques de fichiers
La fonction FILES ne répertorie pas seulement les fichiers Excel; il répertorie tous les types de fichiers; pdf, csv, mp3, zip, tout type de fichier auquel vous pouvez penser. En étendant l’utilisation de caractères génériques dans le chemin du fichier, il est possible de restreindre la liste à des types de fichiers spécifiques ou à des noms de fichiers spécifiques.La capture d’écran ci-dessous montre comment renvoyer uniquement les fichiers avec “pdf
FICHIERS avec des caractères génériques
– Représente un nombre quelconque de caractèresTilde (~) – Utilisé comme caractère d’échappement pour rechercher un astérisque ou un point d’interrogation dans le nom du fichier, plutôt que comme un caractère générique.La capture d’écran ci-dessous montre comment renvoyer uniquement les fichiers portant le nom ”
“, Suivi d’exactement trois caractères.
Vous trouverez ci-dessous quelques idées sur la manière dont vous pourriez utiliser la fonction FILES.
=COUNTA(listFiles)
La plage nommée créée fonctionne comme toute autre plage nommée. Cependant, plutôt que de contenir des cellules, il contient des valeurs. Par conséquent, si vous souhaitez calculer le nombre de fichiers dans le dossier ou qui correspondent au modèle générique, utilisez la formule suivante:
Ne serait-il pas génial de cliquer sur le nom du fichier pour l’ouvrir automatiquement? Bien . . . ajoutez simplement la fonction HYPERLINK et vous pouvez.
=IFERROR(HYPERLINK(LEFT($A$1,LEN($A$1)-1)&INDEX(listFiles,ROW()-ROW(A$2)), INDEX(listFiles,ROW()-ROW(A$2))),"")
La formule de la cellule A3 est:
Il n’est pas nécessaire de répertorier tous les fichiers pour savoir si un fichier existe dans le dossier. La fonction MATCH retournera la position du fichier dans le dossier.
=MATCH(A3,listFiles,0)
FICHIERS avec fonction MATCHLa formule dans la cellule B3 est:Dans notre exemple, un fichier qui contient le texte “Nouveau Yor * »Existe, comme le 7ème fichier, donc un 7 est renvoyé. La cellule B4 affiche l’erreur # N / A car ”
n’existe pas dans le dossier.
Les fichiers renvoyés sont classés par ordre alphabétique, il est donc possible de trouver le fichier suivant ou précédent en utilisant la combinaison INDEX / MATCH.FILES trouve le fichier suivant ou précédentLe fichier suivant après “Denver.xlsx” est ”
=INDEX(listFiles,MATCH(A3,listFiles,0)+1)
«. La formule de la cellule B3 est:
La fonction INDIRECT peut construire une référence de cellule à l’aide de chaînes de texte. Après avoir récupéré la liste des fichiers dans un dossier, il serait possible d’obtenir des valeurs à partir de ces fichiers.
=INDIRECT("'"&LEFT($A$1,LEN($A$1)-1)&"["&A3&"]Sheet1'!$A$1") FICHIERS avec INDIRECT
Pour que INDIRECT calcule correctement, le fichier doit être ouvert, cela peut donc être une faille importante dans cette option.
Le classeur doit être enregistré au format de fichier «.xlsm»
Lectures complémentaires
Il existe une variété d’autres fonctions Excel 4 disponibles qui fonctionnent toujours dans Excel. Consultez cet article pour savoir comment les appliquer et téléchargez le guide de référence des fonctions de macro Excel 4.
Si vous décidez d’utiliser une méthode VBA, consultez cet article.
N’y allez pas encore, il y a beaucoup plus à apprendre sur comment-supprimer. Consultez les derniers articles:
