¿Cómo contar valores únicos en Excel 2016? (con imagenes)

Se vuelve complicado ordenar los valores únicos en una gran variedad de celdas si contiene valores duplicados. Digamos que si desea identificar o contar valores únicos en Excel, puede crear su propia fórmula para ello.

Hay varias funciones disponibles en Excel con una combinación de las cuales puede implementar la fórmula para contar valores únicos en Excel. En esta sección, discutiremos algunos métodos útiles para contar valores únicos en Excel:

  1. Cuente los valores únicos usando la fórmula
  2. Manejar espacios en blanco en el rango seleccionado
  3. Cuenta los valores únicos que son texto
  4. Cuenta los valores únicos que son números
  5. Usando el filtro avanzado

Pasos para contar valores únicos en Excel 2016:

Cuente los valores únicos usando la fórmula

Como hemos comentado anteriormente, puede crear su propia fórmula para contar valores únicos en un rango de celdas. Aquí veremos la construcción de dos fórmulas para contar valores únicos.

1ns fórmula

= SUMA (SI (FRECUENCIA (B2: B18, B2: B18)> 0.1))

Ahora, entendamos esta fórmula

FRECUENCIA: Esta función cuenta cuántas veces se ha producido el valor en el rango seleccionado. Devuelve una matriz vertical que muestra la frecuencia de aparición de cada número en el rango.

Nota: Esta función solo considera valor numérico distinto de cero es decir, evite valores de texto y ceros.

Ejemplo: Comprobemos la frecuencia de cada número en el rango B2: B18. Para eso, primero seleccione un rango de salida (igual al tamaño del rango de referencia) y en la celda superior del rango de salida escriba la fórmula:

= FRECUENCIA (B2: B18, B2: B18) (para presionar Ctrl + Mayús + Entrar, como una fórmula de matriz)

Vea en la imagen a continuación, en la primera aparición del valor que muestra, su ocurrencia total en el intervalo de referencia, en ocurrencias en reposo, muestra 0.

Como en la primera aparición de 100, muestra el dígito 6, significa que 100 ocurre 6 veces en el intervalo de referencia, la ocurrencia restante de 100 muestra 0.

Volvamos a nuestra fórmula principal:

= SUMA (SI (FRECUENCIA (B2: B18, B2: B18)> 0.1))

Como vimos en la figura anterior, la función FRECUENCIA devuelve una matriz, calculando las ocurrencias de cada valor en el rango de referencia. La matriz devuelta por la función FRECUENCIA es:

{6, 3, 0, 2, 0, 1, 1, 1, 0, 0, 0, 2, 0, 1, 0, 0, 0}

Ahora, la función SI en la fórmula principal verifica cada valor de matriz devuelto por la función FRECUENCIA y para cada valor distinto de cero devuelve 1

SI ({6, 3, 0, 2, 0, 1, 1, 1, 0, 0, 0, 2, 0, 1, 0, 0, 0}> 0,1)

Resultado SE: (1, 1, 1, 1, 1, 1, 1, 1)

La función de suma suma todos los 1 y genera el resultado final, que es 8

= SUMA (1, 1, 1, 1, 1, 1, 1, 1)

Vea en la imagen de abajo el resultado de la fórmula:

= SUMA (SI (FRECUENCIA (B2: B18, B2: B18)> 0.1)) (para presionar Ctrl + Mayús + Entrar, como una fórmula de matriz)

Cuente la fórmula de valor único 1

2ns fórmula

= PRODUCTO SUMA (1 / CONTAR.SI (B2: B18, B2: B18))

Veamos cómo funcionará esta fórmula para nosotros. Comience con COUNTIF, a diferencia de FREQUENCY, CONTAR funciona para ambos texto Y numérico datos también.

Nota: La función CONTAR.SI muestra el número total de ocurrencias del valor cada vez que aparece en el rango de referencia.

En la imagen a continuación, puede ver cómo funciona la función CONTAR.SI en datos de texto:

= CONTAR.SI (A2: A18; A2: A18) (para presionar Ctrl + Mayús + Entrar, como una fórmula de matriz)

Contando para datos de texto

Mire la figura de arriba, Charles se repite cuatro veces en el intervalo de referencia y con cada aparición de Charles, COUNTIF muestra el dígito 4.

La siguiente imagen muestra cómo funciona CONTAR.SI para datos numéricos. 100 aparece seis veces, por lo que para cada aparición de 100 muestra el dígito 6.

= CONTAR.SI (B2: B18, B2: B18) (para presionar Ctrl + Mayús + Entrar, como una fórmula de matriz)

Contando para datos numéricos

Volviendo a nuestra fórmula principal:

= SUMA (1 / CONTAR.SI (B2: B18, B2: B18))

CONTAR.SI en la fórmula anterior devuelve la matriz {6, 3, 6, 2, 3, 1, 1, 1, 6, 2, 6, 2, 6, 1, 6, 3, 2} y el valor de la matriz entera es dividido por 1 ahora, la fórmula SUM tendrá:

= SUMA (0.1666, 0.3333, 0.1666, 0.5, 0.3333, 1, 1, 1, 0.1666, 0.5, 0.1666, 0.5, 0.1666, 1, 0.1666, 0.3333, 0.5)

Cuando esta SUM ejecuta el resultado que genera es 8

La siguiente imagen muestra el resultado de la fórmula:

= SUMA (1 / CONTAR.SI (B2: B18, B2: B18)) (para presionar Ctrl + Mayús + Entrar, como una fórmula de matriz)

Cuente la fórmula de valor único 2

Para datos de texto, la siguiente fórmula muestra el resultado:

= SUMA (1 / CONTAR.SI (A2: A18, A2: A18)) (para presionar Ctrl + Mayús + Entrar, como una fórmula de matriz)

La fórmula uno a uno cuenta 2.1

Manejar espacios en blanco en el rango seleccionado

Adivina qué pasará con las fórmulas anteriores si el rango seleccionado tiene una (s) celda (s) vacía (s). Entonces devolvería el error # DIV / 0!. Puedes verlo en la imagen de abajo.

gestión de celda vacía

Bueno, un pequeño cambio en la fórmula utilizada anteriormente ayudaría si la modificamos de una manera:

= SUMA (1 / CONTAR.SI (A2: A18, A2: A18 & «»)) (para presionar Ctrl + Mayús + Entrar, como una fórmula de matriz)

gestión de celda vacía 1

Puede observar el resultado en la imagen de arriba, cuenta la celda en blanco como una entrada única y está contando 1 Debido a esto.

Para evitar el recuento de celdas vacías, modificaremos la fórmula de una manera:

= SUMA (1 / CONTAR.SI (A2: A18, A2: A18 & «»)) – CONTAR.SI (A2: A18 «<>» & «*») (para presionar Ctrl + Mayús + Entrar, como una fórmula de matriz)

La formula CONTAR.SI (A2: A18, «<>» & «*») devuelve 1 mientras verifica la celda que son

gestión de celda vacía 2

Ahora puede ver que muestra el resultado correcto ahora.

Cuenta los valores únicos que son texto

En esta sección, veremos lo que podemos hacer si tenemos el rango de referencia que tiene tanto datos numéricos como de texto y queremos contar valores únicos solo en el contexto del texto.

La fórmula para esto es:

= SUMA (ESTEXTO (A2: A10) / CONTAR.SI (A2: A10, A2: A10 & «»)) (para presionar Ctrl + Mayús + Entrar, como una fórmula de matriz)

ISTEXT

Puede ver en la imagen de arriba que solo cuenta el valor de texto único, evitando las celdas en blanco y las celdas que tienen el valor numérico.

Cuenta los valores únicos que son números

Para contar el valor único en el contexto del número en un rango de celdas que contiene datos de texto, datos numéricos y celdas en blanco, construimos la siguiente fórmula:

= SUMA (ISNUMBER (A2: A10) / COUNTIF (A2: A10, A2: A10 & «»))

En la imagen de abajo puedes ver el resultado obtenido al implementar la fórmula anterior:

ES NÚMERO

Usando el filtro avanzado

Paso 1: Esta es una función incorporada en Excel, para esto, debe ir a la pestaña Datos y en el grupo Ordenar y filtrar haga clic en Avanzado.

Filtro avanzado

Paso 2: Aparecerá un cuadro de diálogo Filtro avanzado. Seleccione el botón de opción «Copiar en otra ubicación».

Cuadro de diálogo Filtro avanzado

En la lista, el cuadro de rango selecciona el rango del que desea derivar el registro único.

En el cuadro Copiar a, seleccione el rango en el que desea copiar el registro único.

Marque la casilla Solo registro único.

Filtro avanzado para datos de texto:

Filtro avanzado Registros únicos

Filtro avanzado para datos numéricos:

Filtro avanzado Número de registro único

Se trataba de cómo se pueden contar valores únicos en Excel. Hemos visto varios métodos y fórmulas. No es un límite, puede explorarlo cuanto más desarrolle su fórmula.