FÓRMULA DE EXCEL: EXTRAIGA ELEMENTOS ÚNICOS DE UNA LISTA

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:

|_+_|

donde 'lista' es la rango con nombre B5: B11.

Nota: esta es una fórmula de matriz y debe ingresarse usando control + shift + enter.

cómo hacer el cambio porcentual en Excel

Explicación

El núcleo de esta fórmula es una búsqueda básica con INDEX:

|_+_|

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í:

|_+_|

Aquí, COUNTIF cuenta cuántas veces los elementos que ya están en la lista única aparecen en la lista maestra, usando un expandiendo la referencia para la gama,$ D $ 4: D4.

Una referencia en expansión es unabsolute por un lado, relativo 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.

Nota lareferenciacomienza en D4,una fila encima 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 formación . En cada nueva fila, tenemos una matriz diferente como esta:

|_+_|

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.

cuántos días entre dos fechas sobresalen

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:

|_+_|

COINCIDIR ubica elementos buscando un recuento de cero (es decir, buscando elementos que aún no aparecen en la lista única). Esto funciona, porque COINCIDIR 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:

|_+_|

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.

CONTAR.SI 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
BUSCAR coincidirá con el último valor sin error en la matriz de búsqueda
BUSCAR devuelve el valor correspondiente en result_vector, el rango con nombre 'lista'

Extrae elementos que aparecen solo una vez

La fórmula de BÚSQUEDA 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:

|_+_|

La única adición es la segunda expresión COUNTIF: