Este artículo hablará sobre cómo hacer coincidir todos los valores de una tabla y recuperarlos en diferentes celdas. Esto es similar a buscar varios valores.
Fórmula genérica
{= INDEX (nombres, PEQUEÑO (IF (grupos = nombre_grupo, FILA (nombres) -MIN (FILA (nombres)) + 1), COLUMNAS (rangos en expansión))), “--Lista finaliza--”)}
¡¡¡Demasiadas funciones y variables !!!. Veamos cuáles son estas variables.
Nombres: Esta es la lista de nombres.
Grupos: La lista del grupo al que pertenecen estos nombres también.
Nombre del grupo: la referencia del nombre del grupo.
Ampliación de rangos: este es un rango en expansión que se usa para obtener un número creciente cuando se copia a la derecha.
Ejemplo: Extraiga los nombres de los empleados en diferentes columnas según su empresa.
Supongamos que tiene una tabla de empleados agrupados según su empresa. La primera columna contiene los nombres de los empleados y la segunda columna contiene el nombre de la empresa.
Ahora necesitamos obtener el nombre de cada empleado en diferentes columnas según su empresa. En otras palabras, necesitamos desagruparlos.
Aquí, he nombrado A2: A10 como Empleado y B2: B10 como Compañía, por lo que la fórmula es fácil de leer.
Escribe esta fórmula de matriz en F2. Use CTRL + MAYÚS + ENTRAR para ingresar esta fórmula.
{= ÍNDICE (Empleado, PEQUEÑO (SI (Compañía = $ E2, FILA (Empleado) -MIN (FILA (Empleado)) + 1), COLUMNAS ($ E $ 1: E1))), "--Lista finaliza--" )}
Copia esta fórmula en todas las celdas. Extraerá cada nombre individual en las diferentes columnas según su grupo.
Como puede ver en la imagen de arriba, cada empleado está segregado en diferentes celdas.
Entonces, ¿cómo funciona esta fórmula?
Para entender la fórmula, veamos la fórmula en G2
Que es = SI.ERROR (INDICE (Empleado, PEQUEÑO (SI (Compañía = $ E3,HILERA(Empleado) -MIN (FILA (Empleado)) + 1), COLUMNAS ($ E $ 1: F2))), "- Finaliza la lista--")
La mecánica es simple y casi la misma que la fórmula VLOOKUP múltiple. El truco consiste en obtener el número de índice de cada empleado de diferentes grupos y pasarlo a la fórmula INDICE. Esto se hace mediante esta parte de la fórmula.
SI(Empresa=$ E3,HILERA(Empleado) -MIN (FILA (Empleado))+1):
Esta parte devuelve una matriz de índices y falso para el nombre de la empresa en $ E3, que contiene "Rankwatch".
{FALSO; 2; FALSO; 4; FALSO; FALSO; 7; FALSO; 9}.
¿Cómo? Vamos a derribarlo desde adentro.
Aquí hacemos coincidir el nombre de la empresa en $ E3 con cada valor en Rango de empresa (Empresa = $ E3).
Esto devuelve una matriz de verdadero y falso. {FALSO;CIERTO;FALSO;CIERTO; FALSO; FALSO;CIERTO;FALSO;CIERTO}.
Ahora la función SI ejecuta sus declaraciones TRUE para TRUE, que es ROW (Empleado) -MIN (ROW (Empleado)) + 1. Esta parte devuelve esta parte devuelve una matriz de índices comenzando desde 1 hasta el número de empleados {1; 2; 3; 4; 5; 6; 7; 8; 9}. La función if aumenta los valores solo para VERDADERO, lo que a su vez nos da {FALSO; 2; FALSO; 4; FALSO; FALSO; 7; FALSO; 9}.
La fórmula actual se simplifica a
= SIERROR (ÍNDICE (Empleado, PEQUEÑO ({FALSO; 2; FALSO; 4; FALSO; FALSO; 7; FALSO; 9},COLUMNAS ($ E $ 1: F2))), "- Finaliza la lista--"). Como sabemos, la función pequeña devuelve el n-ésimo valor más pequeño de una matriz. COLUMNAS ($ E $ 1: F2) esto devuelve 2. La función PEQUEÑO devuelve el segundo valor más pequeño de la matriz anterior, que es 4.
Ahora la fórmula está simplificada = SI.ERROR (ÍNDICE (Empleado, 4), "- Finaliza la lista--"). Ahora, la función INDICE simplemente devuelve el cuarto nombre de empleado matriz que nos da "Sam”.
Así que sí, así es como se extraen los nombres de los grupos en diferentes columnas usando las funciones INDICE, SMALL, ROW, COLUMNS e IF. Si tiene alguna duda con respecto a esta función o si no le está funcionando, hágamelo saber en la sección de comentarios a continuación.
Descargar archivo:
Artículos relacionados:
VLOOKUP Varios valores
Utilice ÍNDICE y COINCIDIR para buscar valor
Valor de búsqueda con varios criterios
Articulos populares:
La función BUSCARV en Excel
CONTAR.SI en Excel 2016
Cómo usar la función SUMIF en Excel