Función de Excel para listar archivos en una carpeta sin VBA
Enumerar los archivos en una carpeta es una de las actividades que no se pueden lograr usando fórmulas normales de Excel. Podría decirle que recurra a las macros de VBA o PowerQuery, pero cualquier usuario que no sea de VBA ni de PowerQuery cerraría esta publicación al instante. ¡Pero espera! Aléjese del botón de cerrar, hay otra opción.
Para listar archivos en una carpeta también podemos usar una característica poco conocida de la versión 4 de Excel, que todavía funciona hoy en día, la función ARCHIVOS.
La función ARCHIVOS
Si busca en la lista de funciones de Excel, ARCHIVOS no aparece en la lista. La función ARCHIVOS se basa en una característica antigua de Excel, que debe aplicarse de una manera especial. Las instrucciones a continuación le mostrarán paso a paso cómo usarlo.
Cree un rango con nombre para la función ARCHIVOS
El primer paso es crear un rango con nombre, que contenga la función ARCHIVOS. Dentro de la cinta de Excel, haga clic en Fórmulas – Definir nombre
Dentro de la ventana Nuevo nombre, establezca los siguientes criterios:
- Nombre: listFiles
Puede ser cualquier nombre que desee, pero para nuestro ejemplo usaremos listFiles. - Se refiere a: =ARCHIVOS(Hoja1!$A$1)
Hoja1!$A$1 es la referencia de hoja y celda que contiene el nombre de la carpeta desde la cual se enumerarán los archivos.
Haga clic en Aceptar para cerrar la ventana Nuevo nombre.
Aplicar la función para listar archivos.
El segundo paso es configurar la hoja de trabajo para usar el rango nombrado.
En la celda A1 (o cualquier referencia de celda utilizada en el cuadro Se refiere a), ingrese la ruta de la carpeta desde la cual enumerar los archivos, seguida de un asterisco (*). El asterisco es el carácter comodín para buscar cualquier texto, por lo que enumerará todos los archivos de la carpeta.
Seleccione la celda en la que iniciar la lista de archivos (Celda A3 en la captura de pantalla a continuación), ingrese la siguiente fórmula.
=ÍNDICE(listaArchivos,1)
El resultado de la función será el nombre del primer archivo de la carpeta.
Para recuperar el segundo archivo de la carpeta, ingrese la siguiente fórmula
=ÍNDICE(listaArchivos,2)
Sería complicado cambiar el número de referencia del archivo dentro de cada fórmula individualmente, especialmente si hay cientos de archivos. La buena noticia es que podemos utilizar otra fórmula para calcular el número de referencia automáticamente.
=ÍNDICE(listaArchivos,FILA()-FILA(A$2))
La función FILA() se utiliza para recuperar el número de fila de una referencia de celda. Cuando se usa sin una referencia de celda, devuelve el número de fila de la celda en la que se usa la función. Cuando se usa con una referencia de celda, devuelve el número de fila de esa celda. Usando la función FILAS, es posible crear una lista secuencial de números comenzando en 1 y aumentando en 1 por cada celda en la que se copia la fórmula.
Si la fórmula se copia más que la cantidad de archivos en la carpeta, devolverá un #REF. error.
Finalmente, ajuste la fórmula dentro de una función SIERROR para devolver una celda en blanco, en lugar de un error.
=SIERROR(ÍNDICE(listaArchivos,FILA()-FILA(A$2)),"")
Listado de tipos específicos de archivos
La función ARCHIVOS no solo enumera archivos de Excel; enumera todos los tipos de archivos; pdf, csv, mp3, zip, cualquier tipo de archivo que se te ocurra. Al ampliar el uso de comodines dentro de la ruta del archivo, es posible restringir la lista a tipos de archivos específicos o a nombres de archivos específicos.
La siguiente captura de pantalla muestra cómo devolver solo archivos con "pdf" como los últimos tres caracteres del nombre del archivo.
Los comodines que se pueden aplicar son:
- Signo de interrogación (?): puede sustituir a cualquier carácter.
- Asterisco (*): representa cualquier número de caracteres
- Tilde ( ~ ): se utiliza como carácter de escape para buscar un asterisco o un signo de interrogación dentro del nombre del archivo, en lugar de como comodín.
La siguiente captura de pantalla muestra cómo devolver solo archivos con el nombre "Nueva York", seguido de exactamente tres caracteres.
Usos avanzados del rango con nombre ARCHIVOS
A continuación se presentan algunas ideas sobre cómo podría utilizar la función ARCHIVOS.
Cuente el número de archivos
El rango con nombre creado funciona como cualquier otro rango con nombre. Sin embargo, en lugar de contener celdas, contiene valores. Por lo tanto, si desea calcular la cantidad de archivos dentro de la carpeta, o que cumplen con el patrón de comodines, use la siguiente fórmula:
=CONTARA(lista de archivos)
Crear hipervínculos a los archivos.
¿No sería genial hacer clic en el nombre del archivo para abrirlo automáticamente? Bien . . . simplemente agregue la función HIPERVÍNCULO y podrá hacerlo.
La fórmula en la celda A3 es:
=SIERROR(HIPERVÍNCULO(IZQUIERDA($A$1,LEN($A$1)-1)ÍNDICE(listaArchivos,FILA()-FILA(A$2)),ÍNDICE(listaArchivos,FILA()-FILA(A$2))) ,"")
Comprobar si existe un archivo específico dentro de una carpeta
No es necesario enumerar todos los archivos para saber si existe un archivo dentro de la carpeta. La función COINCIDIR devolverá la posición del archivo dentro de la carpeta.
La fórmula en la celda B3 es:
= COINCIDIR (A3, lista de archivos, 0)
En nuestro ejemplo, existe un archivo que contiene el texto “New Yor*”, como séptimo archivo, por lo tanto, se devuelve un 7. La celda B4 muestra el error #N/A porque "Seattle" no existe en la carpeta.
Busque el nombre del archivo anterior o siguiente
Los archivos devueltos están en orden alfabético, por lo tanto es posible encontrar el archivo anterior o siguiente utilizando la combinación ÍNDICE / COINCIDIR.
El siguiente archivo después de "Denver.xlsx" es "Nueva York.pdf". La fórmula en la celda B3 es:
= ÍNDICE (lista de archivos, COINCIDIR (A3, lista de archivos, 0) +1)
Recuperar valores de cada archivo con INDIRECTO
La función INDIRECTA puede construir una referencia de celda utilizando cadenas de texto. Habiendo recuperado la lista de archivos en una carpeta, sería posible obtener valores de esos archivos.
La fórmula en la celda B3 es:
=INDIRECTO("'"IZQUIERDA($A$1,LEN($A$1)-1)"["A3"]Hoja1'!$A$1")
Para que INDIRECT calcule correctamente, el archivo debe estar abierto, por lo que esto puede ser un defecto importante en esta opción.
Notas de uso
Al trabajar con la función ARCHIVOS hay algunas cosas que se deben tener en cuenta:
- La ruta del archivo y el nombre del archivo no distinguen entre mayúsculas y minúsculas.
- Los archivos se devuelven en orden alfabético.
- La función no devuelve carpetas y archivos ocultos
- El libro de trabajo debe guardarse como un formato de archivo “.xlsm”.
Otras lecturas
Hay una variedad de otras funciones de Excel 4 disponibles que aún funcionan en Excel. Consulte esta publicación para saber cómo aplicarlas y descargar la guía de referencia de funciones macro de Excel 4 .
Si decide utilizar un método VBA, consulte esta publicación.
Deja una respuesta