Excel

Buscar última revisión de archivo

Lookup Last File Revision

Fórmula de Excel: buscar la última revisión del archivoFórmula genérica
{= MAX ( IF ( ISERROR ( SEARCH (H5&'*',files)),0, ROW (files)- ROW ( INDEX (files,1,1))+1))}
Resumen

Para encontrar la posición (fila) de la última revisión del archivo en una tabla, puede usar una fórmula basada en varias funciones de Excel: MAX, IF, ISERROR, ROW e INDEX.

En el ejemplo que se muestra, la fórmula en la celda H6 es:



eliminar ciertos caracteres de la cadena de Excel

{= MAX (IF (ISERROR (SEARCH (H5 & '*', files)), 0, ROW (files) -ROW (INDEX (files, 1,1)) + 1))}



donde 'archivos' es el rango con nombre C4: C11.

Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.



Contexto

En este ejemplo, tenemos varias versiones de archivo enumeradas en una tabla con una fecha y un nombre de usuario. Tenga en cuenta que los nombres de archivo se repiten, excepto por el código que se adjunta al final para representar la versión ('CA', 'CB', 'CC', 'CD', etc.).

Para un archivo dado, queremos ubicar la posición (número de fila) para la última revisión. Este es un problema complicado, porque los códigos de versión al final de los nombres de archivo dificultan la coincidencia con el nombre del archivo. Además, de forma predeterminada, las fórmulas de coincidencia de Excel devolverán la primera coincidencia, no la última coincidencia, por lo que debemos solucionar ese desafío con algunas técnicas complicadas.

Explicación

En el núcleo de esta fórmula, creamos una lista de números de fila para un archivo determinado. Luego usamos la función MAX para obtener el número de fila más grande, que corresponde a la última revisión (última aparición) de ese archivo.



Para encontrar todas las ocurrencias de un archivo dado, usamos la función BUSCAR, configurada con el comodín asterisco (*) para que coincida con el nombre del archivo, ignorando los códigos de versión. SEARCH arrojará un error de VALUE cuando no se encuentre el texto, por lo que ajustamos la búsqueda en ISERROR:

|_+_|

Esto da como resultado una matriz de valores VERDADERO y FALSO como este:

{FALSETRUEFALSEFALSETRUETRUEFALSETRUE}

¿Cómo congelo las celdas en Excel?

Es confuso, pero VERDADERO representa un error (texto no encontrado) y FALSO representa una coincidencia. Este resultado de la matriz se introduce en la función IF como prueba lógica. Para valor si es VERDADERO, usamos cero, y para valor si es verdadero, proporcionamos este código, que genera números de fila relativos para la gama con la que estamos trabajando:

|_+_|

La función SI luego devuelve una matriz de valores como esta:

{10340070}

Todos los números excepto el cero representan coincidencias para 'nombre de archivo1', es decir, el número de fila dentro del rango nombrado 'archivos' donde aparece 'nombre de archivo1'.

Finalmente, usamos la función MAX para obtener el valor máximo en esta matriz, que es 7 en este ejemplo.

Utilice INDICE con este número de fila para recuperar información relacionada con la última revisión (es decir, nombre completo del archivo, fecha, usuario, etc.).

Sin rango con nombre

Los rangos con nombre agilizan y facilitan la configuración de una fórmula más compleja, ya que no es necesario que ingrese las direcciones de las celdas a mano. Sin embargo, en este caso, estamos usando una función adicional (INDICE) para obtener la primera celda del rango con nombre 'archivos', lo que complica un poco las cosas. Sin el rango con nombre, la fórmula se ve así:

|_+_|Autor Dave Bruns


^