En este tutorial, aprenderá a encontrar la última aparición de un elemento en una lista utilizando fórmulas de Excel.
Recientemente, estaba trabajando en establecer la agenda de una reunión.
Tenía una lista en Excel donde tenía una lista de personas y las fechas en las que actuaban como "Presidente de la reunión".
Dado que hubo repetición en la lista (lo que significa que una persona ha sido presidente de la reunión varias veces), también necesitaba saber cuándo fue la última vez que una persona actuó como "presidente de la reunión".
Esto se debió a que tenía que asegurarme de que no se volviera a asignar a alguien que presidió recientemente.
Así que decidí usar un poco de magia de funciones de Excel para hacer esto.
A continuación se muestra el resultado final donde puedo seleccionar un nombre del menú desplegable y me da la fecha de la última aparición de ese nombre en la lista.
Si tiene un buen conocimiento de las funciones de Excel, sabrá que no existe una función de Excel que pueda hacer esto.
Pero estás en la sección Formula Hack, y aquí hacemos que suceda la magia.
En este tutorial, te mostraré tres formas de hacer esto.
Encuentre la última ocurrencia - Usando la función MAX
El crédito a esta técnica se debe a un artículo de Charley Kyd, MVP de Excel.
Aquí está la fórmula de Excel que devolverá el último valor de la lista:
= ÍNDICE ($ B $ 2: $ B $ 14, SUMPRODUCTO (MAX (FILA ($ A $ 2: $ A $ 14) * ($ D $ 3 = $ A $ 2: $ A $ 14)) - 1))
Así es como funciona esta fórmula:
- La función MAX se utiliza para encontrar el número de fila del último nombre coincidente. Por ejemplo, si el nombre es Glen, devolvería 11, ya que está en la fila 11. Dado que nuestra lista comienza desde la segunda fila en adelante, se ha restado 1. Entonces, la posición de la última aparición de Glen es 10 en nuestra lista.
- SUMPRODUCT se usa para garantizar que no tenga que usar Control + Shift + Enter, ya que SUMPRODUCT puede manejar fórmulas de matriz.
- La función INDICE ahora se usa para encontrar la fecha del último nombre coincidente.
Encontrar la última ocurrencia: uso de la función BUSCAR
Aquí hay otra fórmula para hacer el mismo trabajo:
= BUSCAR (2,1 / ($ A $ 2: $ A $ 14 = $ D $ 3), $ B $ 2: $ B $ 14)
Así es como funciona esta fórmula:
- El valor de búsqueda es 2 (verá por qué … siga leyendo)
- El rango de búsqueda es 1 / ($ A $ 2: $ A $ 14 = $ D $ 3): devuelve 1 cuando encuentra el nombre coincidente y un error cuando no lo encuentra. Entonces terminas obteniendo una matriz. Por ejemplo, si el valor de búsqueda es Glen, la matriz sería {# DIV / 0!; # DIV / 0!; 1; # DIV / 0!; # DIV / 0!; # DIV / 0!; # DIV / 0!; # DIV / 0!; # DIV / 0!; 1; # DIV / 0!; # DIV / 0!; # DIV / 0!}.
- El tercer argumento ([result_vector]) es el rango desde el cual da el resultado, que son fechas en este caso.
La razón por la que esta fórmula funciona es que la función BÚSQUEDA utiliza la técnica de coincidencia aproximada. Esto significa que si puede encontrar el valor coincidente exacto, lo devolverá, pero si no puede, escaneará toda la matriz hasta el final y devolverá el siguiente valor más grande, que es menor que el valor de búsqueda.
En este caso, el valor de búsqueda es 2, y en nuestra matriz, solo obtendremos 1 o errores. Por lo tanto, escanea toda la matriz y devuelve la posición del último 1, que es el último valor coincidente del nombre.
Encuentre la última ocurrencia: uso de la función personalizada (VBA)
Permítanme también mostrarles otra forma de hacer esto.
Podemos crear una función personalizada (también llamada función definida por el usuario) usando VBA.
La ventaja de crear una función personalizada es que es fácil de usar. No tiene que preocuparse por crear una fórmula compleja cada vez, ya que la mayor parte del trabajo ocurre en el backend de VBA.
He creado una fórmula simple (que se parece mucho a la fórmula BUSCARV).
Para crear una función personalizada, debe tener el código VBA en el Editor de VB. Te daré el código y los pasos para colocarlo en el Editor de VB en un momento, pero primero déjame mostrarte cómo funciona:
Esta es la fórmula que te dará el resultado:
= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)
La fórmula toma tres argumentos:
- Valor de búsqueda (este sería el nombre en la celda D3)
- Rango de búsqueda (este sería el rango que tiene los nombres y fechas - A2: B14)
- Número de columna (esta es la columna de la que queremos el resultado)
Una vez que haya creado la fórmula y haya puesto el código en VB Editor, puede usarlo como cualquier otra función normal de la hoja de cálculo de Excel.
Aquí está el código de la fórmula:
'Este es un código para una función que encuentra la última aparición de un valor de búsqueda y devuelve el valor correspondiente de la columna especificada' Código creado por Sumit Bansal (https://trumpexcel.com) Función LastItemLookup (Lookupvalue As String, LookupRange As Rango, ColumnNumber como entero) Dim i Tan largo para i = LookupRange.Columns (1) .Cells.Count To 1 Step -1 If Lookupvalue = LookupRange.Cells (i, 1) Then LastItemLookup = LookupRange.Cells (i, ColumnNumber) Salir Función Finalizar Si Siguiente i Finalizar Función
Estos son los pasos para colocar este código en el Editor de VB:
- Vaya a la pestaña Desarrollador.
- Haga clic en la opción de Visual Basic. Esto abrirá el editor de VB en el backend.
- En el panel Explorador de proyectos en el Editor de VB, haga clic con el botón derecho en cualquier objeto del libro en el que desea insertar el código. Si no ve el Explorador de proyectos, vaya a la pestaña Ver y haga clic en Explorador de proyectos.
- Vaya a Insertar y haga clic en Módulo. Esto insertará un objeto de módulo para su libro de trabajo.
- Copie y pegue el código en la ventana del módulo.
Ahora la fórmula estaría disponible en toda la hoja de trabajo del libro de trabajo.
Tenga en cuenta que debe guardar el libro de trabajo en formato .XLSM, ya que tiene una macro. Además, si desea que esta fórmula esté disponible en todos los libros de trabajo que usa, puede guardarla en el Libro de macros personal o crear un complemento a partir de él.
