Fó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 de 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:
(= MAX (IF (ISERROR (SEARCH (H5 & "*", archivos)), 0, FILA (archivos) -ROW (INDICE (archivos, 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 archivos enumeradas en una tabla con una fecha y un nombre de usuario. Tenga en cuenta que los nombres de los archivos se repiten, excepto 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, 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 VALUE cuando no se encuentre el texto, por lo que ajustamos la búsqueda en ISERROR:
ISERROR(SEARCH(H5&"*",files))
Esto da como resultado una matriz de valores VERDADERO y FALSO como este:
(FALSO; VERDADERO; FALSO; FALSO; VERDADERO; VERDADERO; FALSO; VERDADERO)
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 el rango con el que estamos trabajando:
ROW(files)-ROW(INDEX(files,1,1))+1)
La función SI luego devuelve una matriz de valores como esta:
(1; 0; 3; 4; 0; 0; 7; 0)
Todos los números excepto el cero representan coincidencias para "nombre de archivo1", es decir, el número de fila dentro del rango con nombre "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 ÍNDICE con este número de fila para recuperar información relacionada con la última revisión (es decir, nombre de archivo completo, fecha, usuario, etc.).
Sin rango con nombre
Los rangos con nombre facilitan y agilizan 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í:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
