Contenido
Todas las funciones clásicas de búsqueda y sustitución de tipos VPR (VLOOKUP), GPR (HLOOKUP), MÁS EXPUESTO (JUEGO) y aquellos como ellos tienen una característica importante: buscan de principio a fin, es decir, de izquierda a derecha o de arriba a abajo en los datos de origen. Tan pronto como se encuentra la primera coincidencia, la búsqueda se detiene y solo se encuentra la primera aparición del elemento que necesitamos.
¿Qué hacer si necesitamos encontrar no la primera, sino la última ocurrencia? Por ejemplo, la última transacción del cliente, el último pago, el pedido más reciente, etc.?
Método 1: encontrar la última fila con una fórmula de matriz
Si la tabla original no tiene una columna con una fecha o un número de serie de una fila (pedido, pago...), entonces nuestra tarea es, de hecho, encontrar la última fila que satisfaga la condición dada. Esto se puede hacer con la siguiente fórmula matricial:
Aquí:
- Función IF (SI) comprueba todas las celdas de una columna una por una Local y muestra el número de línea si contiene el nombre que necesitamos. El número de línea en la hoja nos lo da la función LÍNEA (FILA), pero como necesitamos el número de fila en la tabla, además tenemos que restar 1, porque tenemos un encabezado en la tabla.
- Entonces la función MAX (MÁX.) selecciona el valor máximo del conjunto formado de números de fila, es decir, el número de la línea más reciente del cliente.
- Función ÍNDICE (ÍNDICE) devuelve el contenido de la celda con el último número encontrado de cualquier otra columna de tabla requerida (Código del pedido).
Todo esto debe ser ingresado como fórmula matricial, es decir:
- En Office 365 con las últimas actualizaciones instaladas y soporte para arreglos dinámicos, simplemente puede presionar Participar.
- En todas las demás versiones, después de ingresar la fórmula, deberá presionar el atajo de teclado Ctrl+Shift+Participar, que automáticamente le agregará llaves en la barra de fórmulas.
Método 2: búsqueda inversa con la nueva función BUSCAR
Ya escribí un artículo largo con un video sobre una nueva característica VER (BUSCARX), que apareció en las últimas versiones de Office para reemplazar el antiguo BUSCARV (VLOOKUP). Con la ayuda de BROWSE, nuestra tarea se resuelve de manera bastante elemental, porque. para esta función (a diferencia de BUSCARV), puede establecer explícitamente la dirección de búsqueda: de arriba hacia abajo o de abajo hacia arriba; su último argumento (-1) es responsable de esto:
Método 3. Busque una cadena con la fecha más reciente
Si en los datos de origen tenemos una columna con un número de serie o una fecha que desempeña un papel similar, la tarea se modifica: no necesitamos encontrar la última línea (la más baja) con una coincidencia, sino la línea con la última ( máximo) fecha.
Ya he discutido en detalle cómo hacer esto usando funciones clásicas, y ahora intentemos usar el poder de las nuevas funciones de matriz dinámica. Para mayor belleza y conveniencia, también convertimos la mesa original en una mesa "inteligente" usando un atajo de teclado Ctrl+T o comandos Inicio – Formato como tabla (Inicio — Formatear como tabla).
Con su ayuda, esta "pareja asesina" resuelve nuestro problema con mucha gracia:
Aquí:
- Función primero FILTRO (FILTRAR) selecciona solo aquellas filas de nuestra tabla donde en la columna Local – el nombre que necesitamos.
- Entonces la función GRADO (CLASIFICAR) ordena las filas seleccionadas por fecha en orden descendente, con la oferta más reciente en la parte superior.
- Función ÍNDICE (ÍNDICE) extrae la primera fila, es decir, devuelve la última operación que necesitamos.
- Y, finalmente, la función de FILTRO externo elimina las columnas 1 y 3 adicionales de los resultados (Código del pedido и Local ) y deja solo la fecha y el monto. Para esto, se utiliza una matriz de constantes. {0;1;0;1}, definiendo qué columnas queremos (1) o no queremos (0) mostrar.
Método 4: encontrar la última coincidencia en Power Query
Bueno, en aras de la exhaustividad, veamos una solución a nuestro problema de búsqueda inversa utilizando el complemento Power Query. Con su ayuda, todo se resuelve de manera muy rápida y hermosa.
1. Convirtamos nuestra tabla original en una "inteligente" usando un atajo de teclado Ctrl+T o comandos Inicio – Formato como tabla (Inicio — Formatear como tabla).
2. Cárguelo en Power Query con el botón Desde tabla/rango de la pestaña. Datos (Datos: de la tabla/rango).
3. Ordenamos (a través de la lista desplegable del filtro en el encabezado) nuestra tabla en orden descendente de fecha, para que las transacciones más recientes estén en la parte superior.
4… En la pestaña elige un equipo Agrupar por (Transformar — Agrupar por) y configurar la agrupación por clientes, y como función de agregación, seleccionar la opción Todas las lineas (Todas las filas). Puede nombrar la nueva columna como desee, por ejemplo Detalles.
Después de agrupar, obtendremos una lista de nombres únicos de nuestros clientes y en la columna Detalles – tablas con todas las transacciones de cada uno de ellos, donde la primera línea será la última transacción, que es lo que necesitamos:
5. Agregue una nueva columna calculada con el botón columna personalizada de la pestaña. Añadir columna (Agregar columna — Agregar columna personalizada)e ingrese la siguiente fórmula:
Aquí Detalles – esta es la columna de la que tomamos tablas por clientes, y 0 {} es el número de la fila que queremos extraer (la numeración de filas en Power Query comienza desde cero). Obtenemos una columna con registros (Record), donde cada entrada es la primera fila de cada tabla:
Queda por expandir el contenido de todos los registros con el botón con flechas dobles en el encabezado de la columna. último trato seleccionando las columnas deseadas:
… y luego elimine la columna que ya no es necesaria Detalles haciendo clic derecho en su título – Quitar columnas (Quitar columnas).
Después de subir los resultados a la hoja a través de Inicio — Cerrar y cargar — Cerrar y cargar (Inicio — Cerrar y cargar — Cerrar y cargar a…) obtendremos una tabla tan agradable con una lista de transacciones recientes, como queríamos:
Cuando cambie los datos de origen, no debe olvidar actualizar los resultados haciendo clic derecho sobre ellos: el comando Actualizar y guardar (Actualizar) o atajo de teclado Ctrl+otro+F5.
- La función BUSCAR es descendiente de BUSCARV
- Cómo utilizar las nuevas funciones de matriz dinámica SORT, FILTER y UNIC
- Encontrar la última celda que no esté en blanco en una fila o columna con la función BUSCAR