Formulaire Excel sans VBA

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.

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.

Créer une plage nommée pour la fonction FILES

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

FICHIERS - Définir le ruban de nom

Dans le Nouveau nom fenêtre définissez les critères suivants:

  • Nom: listFiles
    Il peut s’agir de n’importe quel nom que vous souhaitez, mais pour notre exemple, nous utiliserons listFiles.
  • Fait référence à: = FICHIERS (Feuille1! $ A $ 1)
    Sheet1! $ A $ 1 est la référence de feuille et de cellule contenant le nom du dossier à partir duquel les fichiers doivent être répertoriés.

FICHIERS - Nouvelle liste de noms

Cliquez sur D’accord pour fermer le Nouveau nom la fenêtre.

Appliquer la fonction pour lister les fichiers

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)

Sélectionnez la cellule dans laquelle démarrer la liste des fichiers (cellule A3 dans la capture d'écran ci-dessous), entrez la formule suivante.

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.

Si la formule est copiée au-delà du nombre de fichiers dans le dossier, elle renverra un #REF! Erreur.

FICHIERS contenant des erreurs

=IFERROR(INDEX(listFiles,ROW()-ROW(A$2)),"")

Enfin, enveloppez la formule dans une fonction IFERROR pour renvoyer une cellule vide, plutôt qu'une erreur.

FICHIERS, fonction IFERROR

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

”Comme les trois derniers caractères du nom de fichier.

FICHIERS avec des caractères génériques

  • Les jokers qui peuvent être appliqués sont:
  • Point d’interrogation (?) – Peut remplacer n’importe quel caractère unique.
  • Astérisque

– 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 ”

New York.

“, Suivi d’exactement trois caractères.

 

Utilisations avancées de la plage nommée FILES

Vous trouverez ci-dessous quelques idées sur la manière dont vous pourriez utiliser la fonction FILES.

=COUNTA(listFiles)

Comptez le nombre de fichiers

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:

Créer des hyperliens vers les fichiers

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))),"")

FICHIERS avec hyperliens

La formule de la cellule A3 est:

Vérifier si un fichier spécifique existe dans un dossier

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 ”

Seattle »

n’existe pas dans le dossier.

Rechercher le nom du fichier suivant ou précédent

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)

New York.pdf

«. La formule de la cellule B3 est:

Récupérer les valeurs de chaque fichier avec INDIRECT

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

La formule de la cellule B3 est:

Pour que INDIRECT calcule correctement, le fichier doit être ouvert, cela peut donc être une faille importante dans cette option.

  • Notes d’utilisation
  • Lorsque vous travaillez avec la fonction FILES, il y a quelques points à prendre en compte:
  • Le chemin et le nom du fichier ne sont pas sensibles à la casse
  • Les fichiers sont renvoyés par ordre alphabétique

Les dossiers et fichiers cachés ne sont pas renvoyés par la fonction

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:

Laisser un commentaire

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