Fórmula genérica
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Resumen
Para extraer solo valores únicos de una lista o columna, puede usar una fórmula de matriz basada en INDICE, COINCIDIR y CONTAR. En el ejemplo que se muestra, la fórmula en D5, copiada, es:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
donde "lista" es el rango con nombre B5: B11.
Nota: esta es una fórmula de matriz y debe ingresarse usando control + shift + enter.
Explicación
El núcleo de esta fórmula es una búsqueda básica con INDEX:
=INDEX(list,row)
En otras palabras, dé a INDEX la lista y un número de fila, e INDEX recuperará un valor para agregar a la lista única.
El trabajo duro es averiguar el número de FILA para dar ÍNDICE, de modo que solo obtengamos valores únicos. Esto se hace con MATCH y COUNTIF, y el truco principal está aquí:
COUNTIF($D$4:D4,list)
Aquí, COUNTIF cuenta cuántas veces los elementos que ya están en la lista única aparecen en la lista maestra, utilizando una referencia en expansión para el rango, $ D $ 4: D4.
Una referencia en expansión es absoluta por un lado, relativa por el otro. En este caso, a medida que se copia la fórmula, la referencia se expandirá para incluir más filas en la lista única.
Tenga en cuenta que la referencia comienza en D4, una fila por encima de la primera entrada única, en la lista única. Esto es intencional: queremos contar elementos * ya * en la lista única, y no podemos incluir la celda actual sin crear una referencia circular. Entonces, comenzamos en la fila de arriba.
Importante: asegúrese de que el encabezado de la lista única no aparezca en la lista maestra.
Para los criterios en CONTAR.SI, estamos usando la propia lista maestra. Cuando se le dan varios criterios, COUNTIF devolverá varios resultados en una matriz. En cada nueva fila, tenemos una matriz diferente como esta:
(0;0;0;0;0;0;0) // row 5 (1;0;0;0;1;0;0) // row 6 (1;1;0;0;1;0;1) // row 7 (1;1;1;1;1;0;1) // row 8
Nota: CONTAR.SI maneja varios criterios con una relación "O" (es decir, CONTAR.SI (rango, ("rojo", "azul", "verde")) cuenta el rojo, el azul o el verde.
Ahora tenemos las matrices que necesitamos para encontrar posiciones (números de fila). Para esto, usamos MATCH, configurado para una coincidencia exacta, para encontrar valores cero. Si ponemos las matrices creadas por COUNTIF arriba en MATCH, esto es lo que obtenemos:
MATCH(0,(0;0;0;0;0;0;0),0) // 1 (Joe) MATCH(0,(1;0;0;0;1;0;0),0) // 2 (Bob) MATCH(0,(1;1;0;0;1;0;1),0) // 3 (Sue) MATCH(0,(1;1;1;1;1;0;1),0) // 6 (Aya)
COINCIDIR ubica elementos buscando un recuento de cero (es decir, buscando elementos que aún no aparecen en la lista única). Esto funciona, porque MATCH siempre devuelve la primera coincidencia cuando hay duplicados.
Finalmente, las posiciones se introducen en INDICE como números de fila, e INDEX devuelve el nombre en esa posición.
Versión sin matriz con LOOKUP
Puede crear una fórmula sin matriz para extraer elementos únicos utilizando la función de BÚSQUEDA flexible:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
La construcción de la fórmula es similar a la fórmula INDEX MATCH anterior, pero LOOKUP puede manejar la operación de matriz de forma nativa.
- COUNTIF devuelve recuentos de cada valor de "lista" en el rango de expansión $ D $ 4: D4
- La comparación con cero crea una matriz de valores VERDADERO y FALSO
- El número 1 se divide por la matriz, creando una matriz de errores 1 y # DIV / 0
- Esta matriz se convierte en el lookup_vector dentro de LOOKUP
- El valor de búsqueda de 2 es mayor que cualquier valor en el lookup_vector
- LOOKUP coincidirá con el último valor sin error en la matriz de búsqueda
- BUSCAR devuelve el valor correspondiente en result_vector, el rango denominado "lista"
Extrae elementos que aparecen solo una vez
La fórmula de BUSCAR anterior es fácil de ampliar con lógica booleana. Para extraer una lista de elementos únicos que aparecen solo una vez en los datos de origen, puede usar una fórmula como esta:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
La única adición es la segunda expresión COUNTIF:
COUNTIF(list,list)=1
Aquí, COUNTIF devuelve una matriz de recuentos de elementos como este:
(2;2;2;2;2;1;2)
que se comparan con 1, lo que da como resultado una matriz de valores VERDADERO / FALSO:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
que actúan como un "filtro" para restringir la salida a elementos que ocurren solo una vez en los datos de origen.
