unicos - Contar valores únicos en una columna en Excel
quitar duplicados en tabla dinamica (11)
Tengo un archivo .xls
con una columna con algunos datos. ¿Cómo cuento cuántos valores únicos contiene esta columna?
He buscado en Google muchas opciones, pero las fórmulas que brindan siempre me dan errores. Por ejemplo,
=INDEX(List, MATCH(MIN(IF(COUNTIF($B$1:B1, List)=0, 1, MAX((COUNTIF(List, "<"&List)+1)*2))*(COUNTIF(List, "<"&List)+1)), COUNTIF(List, "<"&List)+1, 0))
devoluciones
Aquí hay otra forma rápida de obtener el valor único, así como obtener los valores únicos. Copie la columna que le interesa en otra hoja de trabajo, luego seleccione la columna completa. Haga clic en Datos -> Eliminar duplicados -> Aceptar. Esto elimina todos los valores duplicados.
Aquí hay una fórmula de matriz elegante (que encontré aquí http://www.excel-easy.com/examples/count-unique-values.html ) que hace el truco muy bien:
Tipo
= SUMA (1 / COUNTIF (Lista, Lista))
y confirmar con CTRL-SHIFT-ENTER
Cuenta única con una condición. Col A
es ID y usa la condición ID=32
, Col B
es Name y estamos tratando de contar los nombres únicos para una ID particular
=SUMPRODUCT((B2:B12<>"")*(A2:A12=32)/COUNTIF(B2:B12,B2:B12))
Estoy usando una hoja de cálculo con encabezados en la fila 1, los datos están en las filas 2 y siguientes.
Los ID están en la columna A. Para contar cuántos valores diferentes hay, coloco esta fórmula desde la fila 2 hasta el final de la hoja de cálculo de la primera columna disponible [F en mi caso]: "=IF(A2=A1,F1+1,1)"
.
Luego utilizo la siguiente fórmula en una celda libre: "=COUNTIF(F:F,1)"
. De esta manera, estoy seguro de que se cuenta cada identificación.
Tenga en cuenta que los ID deben estar ordenados, de lo contrario se contarán más de una vez ... pero a diferencia de las fórmulas de matriz, es muy rápido incluso con una hoja de cálculo de 150000 filas.
Mi conjunto de datos es D3: D786, encabezados de columna en D2, función en D1. La fórmula ignorará los valores en blanco.
= SUMA (IF (FRECUENCIA (IF (SUBTOTAL (3, DESPLAZAMIENTO (D3, FILA (D3: D786) -ROW (D3) ,, 1)), IF (D3: D786 <> "", MATCH ("~" & D3 : D786, D3: D786 y "", 0))), FILA (D3: D786) -ROW (D3) +1), 1))
Al ingresar la fórmula, presione CTRL + SHIFT + ENTER
Encontré esto en el sitio a continuación, hay más explicaciones sobre Excel que no entendí, si te gustan ese tipo de cosas.
Copié y pegué mi conjunto de datos en una hoja diferente para verificarlo y me funcionó.
Otra forma engañosa que se me acaba de ocurrir (¡probó y funcionó!).
- Seleccione los datos en la columna
- En el menú, seleccione
Conditional Formatting
,Highlight Cells
,Duplicate Values
- Seleccione si desea resaltar valores únicos o duplicados.
- Guarde el resaltado
- Seleccione los datos
- Ir a
Data
y luegoFilter
Filtro basado en color:
Es cierto que esto es más para verificaciones de datos de manera excepcional que una hoja de cálculo que usará con frecuencia, ya que requiere algunos cambios de formato.
Para contar el número de valores diferentes en A2: A100 (sin contar los espacios en blanco):
=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))
Copiado de una respuesta de @ Ulli Schmid a ¿Qué está haciendo esta fórmula COUNTIF ()? :
=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
Cuenta las celdas únicas dentro de A1: A100, excluyendo las celdas en blanco y las que tienen una cadena vacía ("").
¿Como hace eso? Ejemplo:
A1:A100 = [1, 1, 2, "apple", "peach", "apple", "", "", -, -, -, ...]
then:
A1:A100&"" = ["1", "1", "2", "apple", "peach", "apple", "", "", "", "", "", ...]
entonces este & "" es necesario para convertir celdas en blanco (-) en cadenas vacías (""). Si tuviera que contar directamente utilizando celdas en blanco, COUNTIF () devuelve 0. Utilizando el truco, tanto "" como - se cuentan como iguales:
COUNTIF(A1:A100,A1:A100) = [2, 2, 1, 2, 1, 2, 94, 94, 0, 0, 0, ...]
but:
COUNTIF(A1:A100,A1:A100&"") = [2, 2, 1, 2, 1, 2, 94, 94, 94, 94, 94, ...]
Si ahora queremos obtener el recuento de todas las celdas únicas, excluyendo espacios en blanco y "", podemos dividir
(A1:A100<>""), which is [1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, ...]
por nuestro resultado intermedio, CONTAR.SI (A1: A100, A1: A100 & ""), y resumir sobre los valores.
SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
= (1/2 + 1/2 + 1/1 + 1/2 + 1/1 + 1/2 + 0/94 + 0/94 + 0/94 + 0/94 + 0/94 + ...)
= 4
Si hubiéramos usado COUNTIF(A1:A100,A1:A100)
lugar de COUNTIF(A1:A100,A1:A100&"")
, entonces algunos de esos 0/94 habrían sido 0/0. Como la división por cero no está permitida, habríamos arrojado un error.
Puede agregar una nueva fórmula para contar registros únicos
=IF(COUNTIF($A$2:A2,A2)>1,0,1)
Ahora puede usar una tabla dinámica y obtener una SUM
de recuento de registros únicos. Esta solución funciona mejor si tiene dos o más filas donde existe el mismo valor, pero desea que la tabla dinámica informe un conteo único.
Puedes hacer los siguientes pasos:
Primero aísle la columna (insertando una columna en blanco antes y / o después de la columna que desea contar los valores únicos si hay columnas adyacentes;
A continuación, seleccione la columna completa, vaya a ''Datos''> ''Filtro avanzado'' y marque la casilla de verificación ''Registros únicos solamente''. Esto ocultará todos los registros que no sean únicos para que pueda contar los únicos seleccionando la columna completa.
Si usa una Mac
- resaltar columna
- dupdo
- abrir terminal.app
- tipo
pbpaste|sort -u|wc -l
Los usuarios de Linux reemplazan pbpaste
con xclip xsel o similar
Usuarios de Windows, es posible, pero tomaría algunas secuencias de comandos ... comience con http://brianreiter.org/2010/09/03/copy-and-paste-with-clipboard-from-powershell/
try - =SUM(IF(FREQUENCY(MATCH(COLUMNRANGE,COLUMNRANGE,0),MATCH(COLUMNRANGE,COLUMNRANGE,0))>0,1))
donde COLUMNRANGE = el rango donde tienes estos valores.
eg - =SUM(IF(FREQUENCY(MATCH(C12:C26,C12:C26,0),MATCH(C12:C26,C12:C26,0))>0,1))
Presione Ctrl + Shift + Enter para hacer que la fórmula sea una matriz (de lo contrario no se calculará correctamente)