Excel

Extrae elementos únicos de una lista

Extract Unique Items From List

Fórmula de Excel: extraiga elementos únicos de una listaFó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 sobre 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 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:

|_+_|

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 en 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 nombrado '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:

cómo usar la función irr en Excel
|_+_|

Aquí, COUNTIF devuelve una matriz de recuentos de elementos como este:

|_+_|

que se comparan con 1, lo que da como resultado una matriz de valores VERDADERO / FALSO:

|_+_|

que actúan como un 'filtro' para restringir la salida a elementos que ocurren solo una vez en los datos de origen.

En Excel 365 , la Función ÚNICA es la mejor forma de extraer valores únicos.

Autor Dave Bruns


^