En este tutorial, aprenderá a encontrar la última ocurrencia de un elemento en una lista utilizando fórmulas de Excel.
Recientemente, estuve trabajando en el establecimiento de la agenda para 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».
Como había repeticiones 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 había presidido recientemente.
Así que decidí usar algo de magia de la función de Excel para hacer esto.
A continuación se muestra el resultado final, donde puedo seleccionar un nombre de la lista desplegable y me da la fecha de la última aparición de ese nombre en la lista.
Si usted tiene una buena comprensión de las funciones de Excel, sabrá que no hay ninguna función de Excel que pueda hacer esto.
Pero estás en la sección de Fórmula Hack, y aquí hacemos que la magia suceda.
En este tutorial, te mostraré tres formas de hacerlo.
Glosario
Encontrar la última ocurrencia – Usando la función MAX
El mérito de esta técnica se debe a un artículo de Excel MVP Charley Kyd.
Aquí está la fórmula de Excel que devolverá el último valor de la lista:
=INDEX($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 a partir de la segunda fila, se ha restado 1. Así que la posición de la última ocurrencia de Glen es 10 en nuestra lista.
- SUMPRODUCT se usa para asegurar que no tengas que usar Control + Shift + Enter, ya que SUMPRODUCT puede manejar las fórmulas de la matriz.
- La función de índice se utiliza ahora para encontrar la fecha del último nombre que coincide.
Encontrar la última ocurrencia – Usando la función LOOKUP
Aquí hay otra fórmula para hacer el mismo trabajo:
=LOOKUP(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 (ya verás por qué .. sigue leyendo)
- El rango de búsqueda es1/($A$2:$A$14=$D$3) – Esto devuelve 1 cuando encuentra el nombre coincidente y un error cuando no. Así que acabas obteniendo un array. Por ejemplo, del valor de búsqueda es Glen, el array sería{#DIV/0!;#DIV/0!;1;#DIV/0!;#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 a partir del cual da el resultado, que son las fechas en este caso.
La razón por la que esta fórmula funciona es que la función LOOKUP utiliza la técnica de coincidencia aproximada. Esto significa que si puede encontrar el valor exacto de coincidencia, lo devolverá, pero si no puede, escaneará todo el conjunto hasta el final y devolverá el siguiente valor más grande que sea inferior al valor de búsqueda.
En este caso, el valor de búsqueda es 2, y en nuestra matriz, sólo obtendremos 1’s o errores. Así que escanea toda la matriz y devuelve la posición del último 1, que es el último valor de coincidencia del nombre.
Encontrar la última ocurrencia – Usando la función personalizada (VBA)
Déjame también mostrarte otra forma de hacer esto.
Podemos crear una función personalizada (también llamada función definida por el usuario) usando VBA.
El beneficio de crear una función personalizada es que es fácil de usar. No tienes que preocuparte de crear una fórmula compleja cada vez, ya que la mayor parte del trabajo se realiza en el backend de VBA.
He creado una fórmula simple (que se parece mucho a la fórmula de VLOOKUP).
Para crear una función personalizada, necesitas tener el código VBA en el VB Editor. Te daré el código y los pasos para colocarlo en el VB Editor en un rato, pero déjame primero mostrarte cómo funciona:
Esta es la fórmula que le 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 las fechas – A2:B14)
- Número de columna (esta es la columna de la que queremos el resultado)
Una vez creada la fórmula y puesto el código en VB Editor, puedes usarla como cualquier otra función normal de una 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 ocurrencia 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 Range, ColumnNumber As Integer) Dim i As Long Para i = LookupRange.Columns(1).Cells.Count To 1 Step -1 Si Lookupvalue = LookupRange.Cells(i, 1) entonces LastItemLookup = LookupRange.Cells(i, ColumnNumber) Función de salida Finalizar si Siguiente… Fin de la función
Estos son los pasos para colocar este código en el VB Editor:
- Ir a la pestaña de desarrollo.
<a dpsp-pin-it-button-top-left dpsp-pin-it-button-shape-rectangular " href="https://pinterest.com/pin/create/bookmarklet/?media=https://trumpexcel.com/wp-content/uploads/2018/02/IF-Then-Else-in-Excel-VBA-Developer-Tab-in-ribbon.png&url=https://trumpexcel.com/find-last-occurrence/&is_video=false&description=Find%20the%20Last%20Oc