EXCEL: Valores únicos por conteo

Para extraer una lista de valores únicos de un conjunto de datos, filtrados por conteo u ocurrencia, puede usar UNICOS con FILTRAR y aplicar criterios con la función CONTAR.SI. En el ejemplo que se muestra, la fórmula en D5 es:

=UNICOS(FILTRAR(data,CONTAR.SI(data,data)>1))

que genera los 3 valores únicos que aparecen más de una vez en el rango "datos" con nombre (B5: B16).

Nota: En este ejemplo, estamos extrayendo una lista única de valores que aparecen más de una vez. En otras palabras, estamos creando una lista de duplicados :) El lenguaje es algo confuso.

Cómo funciona esta fórmula

Este ejemplo utiliza la función UNICOS junto con la función FILTRAR. Puedes ver un ejemplo más básico aquí.

El truco en este caso es aplicar criterios a la función FILTRAR para permitir solo valores basados ​​en el conteo de ocurrencias. Trabajando de adentro hacia afuera, esto se hace con CONTAR.SI y la función FILTRAR aquí:

FILTRAR(data,CONTAR.SI(data,data)>1)

El resultado de CONTAR.SI es una serie de recuentos como este:

{3;1;3;3;2;1;1;3;1;2;3;3}

que se verifican con la comparación lógica> 1 para obtener una matriz o valores VERDADERO / FALSO:

{VERDADERO;FALSO;VERDADERO;VERDADERO;VERDADERO;FALSO;FALSO;VERDADERO;FALSO;VERDADERO;VERDADERO;VERDADERO}

Observe que VERDADERO corresponde a valores en los datos que aparecen más de una vez. Esta matriz se devuelve a FILTRAR como argumento de inclusión, que se utiliza para filtrar los datos. FILTRAR devuelve otra matriz como resultado:

{"red";"green";"green";"blue";"red";"blue";"red";"green"}

Esta matriz se devuelve directamente a la función UNICOS como argumento de la matriz. Aviso de los 12 valores originales, solo 8 sobreviven.

UNICOS luego elimina duplicados y devuelve la matriz final:

{"red";"green";"blue"}

Si los valores en B5: B16 cambian, la salida se actualizará inmediatamente.

Cuenta> 2

La fórmula en F5, que enumera los colores que aparecen al menos 2 veces en los datos de origen, es:

=UNICOS(FILTRAR(data,CONTAR.SI(data,data)>2))

Rango de fuente dinámica

Debido a que los datos (B5: B15) son un rango con nombre normal, no se redimensionará si se agregan o eliminan datos. Para usar un rango dinámico que se redimensionará automáticamente cuando sea necesario, puede usar una tabla de Excel o crear un rango dinámico con nombre con una fórmula.