únicos valores unicos una tabla recuento quitar porque los entre duplican duplicados distinto dinamica datos contar condiciones con como campo calculado activar excel excel-formula pivot-table

excel - valores - porque se duplican los datos en una tabla dinamica



Tabla de pivote simple para contar valores únicos (16)

ACTUALIZACIÓN: puede hacer esto ahora automáticamente con Excel 2013. He creado esto como una nueva respuesta porque mi respuesta anterior en realidad resuelve un problema ligeramente diferente.

Si tiene esa versión, luego seleccione sus datos para crear una tabla dinámica, y cuando cree su tabla, asegúrese de que la casilla de verificación ''Agregar esta información al modelo de datos'' esté marcada (vea a continuación).

Luego, cuando se abra la tabla pivote, crea tus filas, columnas y valores normalmente. A continuación, haga clic en el campo del que desea calcular el recuento distintivo de y edite la configuración de valores de campo:

Finalmente, desplácese hacia abajo hasta la última opción y elija ''Cuenta Distintiva''.

Esto debería actualizar los valores de la tabla dinámica para mostrar los datos que está buscando.

Esto parece una simple tabla dinámica para aprender. Me gustaría hacer un recuento de valores únicos para un valor particular en el que estoy agrupando.

Por ejemplo, tengo esto:

ABC 123 ABC 123 ABC 123 DEF 456 DEF 567 DEF 456 DEF 456

Lo que quiero es una tabla dinámica que me muestre esto:

ABC 1 DEF 2

La tabla dinámica simple que creo me da esto (un recuento de cuántas filas):

ABC 3 DEF 4

Pero quiero el número de valores únicos en su lugar.

Lo que realmente trato de hacer es averiguar qué valores en la primera columna no tienen el mismo valor en la segunda columna para todas las filas. En otras palabras, "ABC" es "bueno", "DEF" es "malo"

Estoy seguro de que hay una forma más fácil de hacerlo, pero pensé en probar la tabla dinámica ...

Encontré que el enfoque más fácil es usar la opción Distinct Count en Value Field Settings (haga clic con el botón izquierdo en el campo en el panel Values ). La opción para Distinct Count está en la parte inferior de la lista.

Aquí están los anteriores (TOP, Count normal) y after (BOTTOM, Distinct Count )

Encontré una manera más fácil de hacer esto. Refiriéndome al ejemplo de Siddarth Rout, si quiero contar valores únicos en la columna A:

  • agregue una nueva columna C y complete C2 con la fórmula "= 1 / COUNTIF ($ A: $ A, A2)"
  • arrastre la fórmula hasta el resto de la columna
  • pivotear con la columna A como etiqueta de fila, y Suma {columna C) en valores para obtener el número de valores únicos en la columna A

Excel 2013 puede hacer Count distinct en pivots. Si no tengo acceso a 2013, y se trata de una cantidad de datos menor, realizo dos copias de los datos sin procesar, y en la copia b, selecciono ambas columnas y elimino los duplicados. Luego haga el pivote y cuente su columna b.

Inserte una tercera columna y en la celda C2 pegue esta fórmula

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

y cópialo. Ahora crea tu pivote basado en la 1ra y 3ra columna. Ver instantánea

La respuesta de Siddharth es fenomenal.

Sin embargo , esta técnica puede causar problemas cuando se trabaja con un gran conjunto de datos (mi computadora se congeló en 50,000 filas). Algunos métodos menos intensivos en el uso del procesador:

Comprobación de singularidad única

  1. Ordene por las dos columnas (A, B en este ejemplo)

  2. Usa una fórmula que mira menos datos

    =IF(SUMPRODUCT(($A2:$A3=A2)*($B2:$B3=B2))>1,0,1)

Múltiples controles de singularidad

Si necesita verificar la singularidad en diferentes columnas, no puede confiar en dos géneros.

En lugar,

  1. Ordenar columna individual (A)

  2. Agregue fórmula que cubra el número máximo de registros para cada grupo. Si ABC puede tener 50 filas, la fórmula será

    =IF(SUMPRODUCT(($A2:$A49=A2)*($B2:$B49=B2))>1,0,1)

Me gustaría incluir una opción adicional en la combinación que no requiera una fórmula, pero podría ser útil si necesita contar valores únicos dentro del conjunto en dos columnas diferentes. Usando el ejemplo original, no tuve:

ABC 123 ABC 123 ABC 123 DEF 456 DEF 567 DEF 456 DEF 456

y quiero que aparezca como:

ABC 1 DEF 2

Pero algo más como:

ABC 123 ABC 123 ABC 123 ABC 456 DEF 123 DEF 456 DEF 567 DEF 456 DEF 456

y quería que apareciera como:

ABC 123 3 456 1 DEF 123 1 456 3 567 1

Encontré que la mejor manera de obtener mis datos en este formato y luego poder manipularlos era utilizar lo siguiente:

Una vez que seleccione ''Correr total en'', elija el encabezado para el conjunto de datos secundario (en este caso, sería el encabezado o título de columna del conjunto de datos que incluye 123, 456 y 567). Esto le dará un valor máximo con el recuento total de elementos en ese conjunto, dentro de su conjunto de datos principal.

Luego copié estos datos, los pegué como valores y luego los puse en otra tabla dinámica para manipularlos más fácilmente.

Para su información, tenía alrededor de un cuarto de millón de filas de datos, así que funcionó mucho mejor que algunos de los enfoques de fórmula, especialmente los que intentan comparar en dos columnas / conjuntos de datos, ya que siguió bloqueando la aplicación.

Mi enfoque para este problema fue un poco diferente de lo que veo aquí, así que lo compartiré.

  1. (Haga una copia de sus datos primero)
  2. Concatenar las columnas
  3. Eliminar duplicados en la columna concatenada
  4. Último: pivote en el conjunto resultante

Nota: Me gustaría incluir imágenes para hacer esto aún más fácil de entender, pero no puedo porque esta es mi primera publicación;)

No es necesario que la tabla se ordene para la siguiente fórmula para devolver un 1 para cada valor único presente.

suponiendo que el rango de la tabla para los datos presentados en la pregunta es A1: B7 ingrese la siguiente fórmula en la celda C1:

=IF(COUNTIF($B$1:$B1,B1)>1,0,COUNTIF($B$1:$B1,B1))

Copie esa fórmula en todas las filas y la última fila contendrá:

=IF(COUNTIF($B$1:$B7,B7)>1,0,COUNTIF($B$1:$B7,B7))

Esto da como resultado que se devuelva un 1 la primera vez que se encuentra un registro y 0 para todos los tiempos posteriores.

Simplemente suma la columna en tu tabla pivote

Normalmente clasifico los datos por el campo que necesito para hacer el conteo diferenciado de luego uso IF (A2 = A1,0,1); obtiene entonces un 1 en la fila superior de cada grupo de ID. Simple y no toma ningún tiempo para calcular en grandes conjuntos de datos.

Puede hacer una columna adicional para almacenar la singularidad, luego sumarla en su tabla dinámica.

Lo que quiero decir es que la celda C1 siempre debe ser 1 . La celda C2 debe contener la fórmula =IF(COUNTIF($A$1:$A1,$A2)*COUNTIF($B$1:$B1,$B2)>0,0,1) . Copie esta fórmula para que la celda C3 contenga =IF(COUNTIF($A$1:$A2,$A3)*COUNTIF($B$1:$B2,$B3)>0,0,1) y así sucesivamente.

Si tiene una celda de encabezado, querrá moverlos todos por una fila y su fórmula C3 debería ser =IF(COUNTIF($A$2:$A2,$A3)*COUNTIF($B$2:$B2,$B3)>0,0,1) .

Puede usar COUNTIFS para múltiples criterios,

= 1 / COUNTIFS (A: A, A2, B: B, B2) y luego arrastre hacia abajo. Puedes poner tantos criterios como quieras allí, pero suele tardar mucho tiempo en procesarse.

Si tiene los datos ordenados ... sugiero usar la siguiente fórmula

=IF(OR(A2<>A3,B2<>B3),1,0)

Esto es más rápido ya que usa menos celdas para calcular.

Paso 1. Agregue una columna

Paso 2. Use la fórmula = IF(COUNTIF(C2:$C$2410,C2)>1,0,1) en el primer registro

Paso 3. Arrástrelo a todos los registros

Paso 4. Filtra ''1'' en la columna con fórmula