Excel

Extrae múltiples coincidencias en filas separadas

Extract Multiple Matches Into Separate Rows

Fórmula de Excel: extrae múltiples coincidencias en filas separadasResumen

Para extraer múltiples coincidencias en celdas separadas, en filas separadas, puede usar una fórmula de matriz basada en INDICE y SMALL. En el ejemplo que se muestra, la fórmula en E5 es:

|_+_|

Esta es una fórmula de matriz y debe ingresarse con Control + Shift + Enter.



Después de ingresar la fórmula en la primera celda, arrástrela hacia abajo y hacia el otro lado para completar las otras celdas.

contar si es menor o igual que
Explicación

Nota: esta fórmula utiliza dos rangos con nombre: 'nombres' se refiere a C4: C11 y 'grupos' se refiere a B4: B11. Estos nombres también se definen en la captura de pantalla anterior.

La esencia de esta fórmula es la siguiente: estamos usando la función PEQUEÑO para obtener un número de fila que corresponda a una 'enésima coincidencia'. Una vez que tenemos el número de fila, simplemente lo pasamos a la función de la función INDICE, que devuelve el valor en esa fila.

El truco es que SMALL está trabajando con una matriz construida dinámicamente por IF en este bit:

¿Cómo se hace una lista desplegable en Excel?
|_+_|

Este fragmento prueba los 'grupos' del rango con nombre para el valor en E4. Si se encuentra, devuelve un número de fila 'normalizado' de una matriz de números de fila creada con esta parte de la fórmula:

|_+_|

El resultado es una matriz que contiene números de fila donde hay una coincidencia y FALSE donde no. La matriz se parece a esto:

{1FALSEFALSEFALSEFALSE6FALSE}

cómo usar la consulta en Excel

Esta matriz entra en PEQUEÑA. El valor k de SMALL (nth) proviene de un rango en expansión :

|_+_|

Cuando se copia en la tabla de resultados, el rango se expande, lo que hace que k (nth) aumente. La función PEQUEÑA devuelve cada número de fila coincidente, que se proporciona a la función INDICE como número_fila, con el rango denominado 'nombres' como matriz.

Manejo de errores

Cuando ROWS devuelve un valor para k que no existe, SMALL arroja un error #NUM. Esto sucede después de que se hayan producido todos los partidos. Para suprimir el error, usamos IFERROR para detectar el error y devolver un cuerda vacía ('').

Autor Dave Bruns


^