Explicación
Una forma de extraer múltiples coincidencias en Excel es usar INDICE y COINCIDIR con una columna auxiliar que marca los datos coincidentes. Esto evita la complejidad de una fórmula de matriz más avanzada. En el ejemplo que se muestra, la fórmula en H6 es:
donde ct (G3), datos (B3: E52) y auxiliar (E3: E52) son rangos con nombre.
Cómo funciona esta fórmula
El desafío con las fórmulas de búsqueda que recuperan más de una coincidencia es administrar duplicados (es decir, múltiples coincidencias). Las fórmulas de búsqueda como CONSULTAV e INDICE + COINCIDIR pueden encontrar fácilmente la primera coincidencia, pero es mucho más difícil buscar "todas las coincidencias" cuando los criterios encuentran más de una coincidencia.
Esta fórmula aborda este desafío mediante el uso de una columna auxiliar que devuelve un valor numérico que se puede utilizar para extraer fácilmente múltiples coincidencias. La fórmula en la columna auxiliar se ve así:
La columna auxiliar prueba cada fila en los datos para ver si el Departamento en la columna C coincide con el valor en I3 y el Edificio en la columna D coincide con el valor en J3. Ambas pruebas lógicas deben devolver VERDADERO para que Y devuelva VERDADERO.
Para cada fila, el resultado de la función Y se agrega al "valor anterior" en la columna auxiliar para generar un recuento. El efecto práctico de esta fórmula es un contador incremental que solo cambia cuando se encuentra una (nueva) coincidencia. Entonces el valor permanece igual hasta que se encuentre la siguiente coincidencia. Esto funciona porque los resultados VERDADERO / FALSO devueltos por Y son obligados a valores 1/0 como parte de la operación de suma. Los resultados FALSO no agregan nada, y los resultados VERDADERO agregan 1.
De vuelta en el área de extracción, la fórmula de búsqueda de Nombre en la columna H se ve así:
Trabajando de adentro hacia afuera, la parte INDICE + COINCIDIR de la fórmula busca el nombre de la primera coincidencia encontrada, utilizando el número de fila en la columna G como valor de coincidencia:
INDICE recibe las 3 columnas de datos como la matriz (rango denominado "datos"), y COINCIDIR está configurado para coincidir con el número de fila dentro de la columna auxiliar (el rango nombrado "auxiliar") en modo de coincidencia exacta (tercer argumento establecido en cero) .
Aquí es donde la inteligencia de la fórmula se hace evidente. La columna auxiliar obviamente contiene duplicados, pero no importa, porque COINCIDIR solo coincidirá con el primer valor. Por diseño, cada "primer valor" corresponde a la fila correcta en la tabla de datos.
Las fórmulas en las columnas I y J son las mismas que H, excepto por el número de columna, que se incrementa en cada caso en uno.
La declaración SI que envuelve la fórmula INDEX / MATCH realiza una función simple: verifica cada número de fila en el área de extracción para ver si el número de fila es menor o igual al valor en G3 (rango denominado "ct"), que es El recuento total de todos los registros coincidentes. Si es así, se ejecuta la lógica INDEX / MATCH. Si no, SI genera una cadena vacía ("").
La fórmula en G3 (rango denominado "ct") es simple:
=MAX(helper)
Dado que el valor máximo en la columna auxiliar es el mismo que el recuento total de coincidencias, la función MAX es todo lo que necesitamos.
Nota: el área de extracción debe configurarse manualmente para manejar tantos datos como sea necesario (es decir, 5 filas, 10 filas, 20 filas, etc.). En este ejemplo, se limita a 5 filas solo para mantener la hoja de trabajo compacta.
Aprendí esta técnica en el libro Control + Shift + Enter de Mike Girvin.
La función FILTRAR
Si tiene la versión de matriz dinámica de Excel, la función FILTRAR es mucho más fácil de extraer todos los datos coincidentes.
0 comentarios:
Publicar un comentario