Resumen: en este tutorial, aprenderá a usar una fórmula de Excel para encontrar la última aparición de un elemento en una lista.
Recientemente, estaba trabajando en la agenda de una reunión.
Tengo una lista en Excel con una lista de personas y las fechas en que fueron "presidentes de reuniones".
También necesito saber cuándo una persona fue el último "presidente de la reunión" debido a la duplicación en la lista (lo que significa que una persona ha sido presidente de la reunión varias veces).
Esto se debe a que debo asegurarme de que el presidente más reciente no sea reasignado.
Así que decidí usar algunas funciones mágicas de Excel para hacer el trabajo.
A continuación se muestra el resultado final, 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 una buena comprensión de las funciones de Excel, sabe que no hay ninguna función de Excel que pueda hacer esto.Aquí hacemos que los milagros sucedan.
En este tutorial, te mostraré tres formas de hacer esto.
Contenido
Encuentre la última ocurrencia - usando la función MAX
Aquí está la fórmula de Excel que devolverá el último valor de la lista:
=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($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 línea del último nombre coincidente.Por ejemplo, si el nombre es Glen, devolverá 11 porque está en la línea 11.Como nuestra lista comienza en la segunda fila, se resta 1.Por lo tanto, la última aparición de Glen es 10 en nuestra lista.
- SUMPRODUCT se usa para asegurarse de que no tenga que usar Control + Shift + Enter porque SUMPRODUCT puede manejar fórmulas de matriz.
- La función ÍNDICE ahora se usa para encontrar la fecha del último nombre coincidente.
Encuentre la última ocurrencia - usando la función BUSCAR
Aquí hay otra fórmula que hace 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 (verás por qué... sigue leyendo)
- El rango de búsqueda es 1/($A$2:$A$14=$D$3) - devuelve 1 cuando encuentra un nombre coincidente y un error en caso contrario.Entonces terminas con una matriz.例如，查找值為 Glen，數組將為 {#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 parámetro ([result_vector]) es el rango en el que da el resultado, en este caso la fecha.
La razón por la que funciona esta fórmula es que la función BUSCAR utiliza una técnica de coincidencia aproximada.Esto significa que si puede encontrar una coincidencia exacta, regresará, pero si no puede, escaneará toda la matriz hasta el final y devolverá el siguiente valor más grande que sea menor que el valor de búsqueda.
En este caso, el valor de búsqueda es 2 y en nuestra matriz solo obtenemos 1 o un error.Entonces escanea toda la matriz y devuelve la posición del último 1, el último valor coincidente para el nombre.
Buscar la última aparición: uso de la función personalizada (VBA)
Déjame mostrarte otra manera también.
Podemos crear funciones personalizadas (también conocidas como funciones definidas por el usuario) usando VBA.
El beneficio de crear funciones personalizadas es la facilidad de uso.No tiene que preocuparse por crear fórmulas complejas cada vez porque la mayor parte del trabajo se realiza en el backend de VBA.
Creé una fórmula simple (muy parecida a la fórmula BUSCARV).
Para crear funciones personalizadas, necesita código VBA en el editor de VB.Te daré el código y los pasos para ingresarlo al editor de VB más tarde, pero déjame mostrarte cómo funciona primero:
Aquí está la fórmula que le dará el resultado:
=ÚltimaBúsquedaDeArtículo($D$3,$A$2:$B$14,2)
La fórmula toma tres parámetros:
- Encuentre el valor (este será el nombre en la celda D3)
- Encuentre el rango (este será el rango con nombre y fecha – A2:B14)
- número de columna (esta es la columna para la que queremos el resultado)
Después de crear la fórmula y colocar el código en el editor de VB, puede usarla como cualquier otra función normal de la hoja de cálculo de Excel.
Aquí está el código de la fórmula:
Función LastItemLookup (valor de búsqueda como cadena, rango de búsqueda como rango, número de columna como entero) Atenúe i tan largo como i = Rango de búsqueda.Columnas (1). Celdas. Cuenta hasta 1 paso -1 Si valor de búsqueda = Rango de búsqueda. Celdas (i, 1) Entonces LastItemLookup = LookupRange.Cells(i, ColumnNumber) Salir de la función Finalizar si el siguiente i Finaliza la función
Estos son los pasos para poner este código en el editor de VB:
- Ve a la pestaña de desarrollador.
- Haga clic en Opciones de Visual Basic.Esto abrirá el editor de VB en el backend.
- En el panel Explorador de proyectos del editor de VB, haga clic con el botón derecho en cualquier objeto del libro de trabajo en el que desee insertar 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ódulos.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á disponible en todas las hojas del libro de trabajo.
Tenga en cuenta que deberá guardar el libro de trabajo en formato .XLSM, ya que contiene macros.Además, si desea que esta fórmula esté disponible en todos los libros de trabajo que utilice, puede guardarla en su libro de macros personal o crear un complemento a partir de él.
