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

Por ejemplo, yo tengo este:

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

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

ABC   1
DEF   2

El simple tabla dinámica que puedo crear solo me da este (un recuento de cuántas filas):

ABC   3
DEF   4  

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

De lo que realmente estoy tratando de hacer es averiguar qué valores de la primera columna no tiene 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 manera más fácil de hacerlo, pero pensé en darle una tabla dinámica de una oportunidad…

  • Tenga en cuenta que para las últimas versiones de excel, esta respuesta es la más conveniente.
InformationsquelleAutor user1586422 | 2012-08-09

16 Comentarios

  1. 105

    Insertar una 3ra columna y en la Celda C2 pega esta fórmula

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

    y copiar hacia abajo. Ahora crear su pivote basada en la 1ª y 3ª columna. Ver instantáneas

    Simple de Tabla dinámica para Contar Valores Únicos

    • +1 yo creo que esto es un poco más fácil que mi solución, ya que no requiere de un especial valor para la primera fila
    • Bonito técnica. Yo no sabía acerca de esto. Usted puede hacer la misma cosa con una función de matriz =IF(SUM((A$2:A2=A2)*(B$2:B2=B2)) > 1, 0, 1) (presione Ctrl-Shift-Enter al entrar en la fórmula, por lo que adquiere {} alrededor de ella).
    • Respuesta Universal, que no requiere de ninguna característica específica. Sólo una buena llanura de fórmulas.
    • Cualquier idea sobre cómo extender esto a una situación con tres columnas?
    • En caso de que la actual header3 se mueve a la 4ta columna y la fórmula se vuelve =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)*($C$2:$C2=C2))>1,0,1)
    • Tenga en cuenta que esta respuesta NO va a dar la solución correcta si se filtran algunas de las filas utilizando las opciones de Tabla Pivote. Digamos que la primera fila se filtra. La suma de Abc a continuación, aparecerá a ser 0!
    • Excelente! Podría usted explicar la solución claramente?
    • Es necesario comprender primero cómo Sumproduct funciona 🙂
    • Oh, sí, yo lo sé!
    • esto funciona en mac y
    • Esto está bien para pequeños conjuntos de datos, pero las fórmulas tienen un desagradable N^2 comportamiento que trae Excel a sus rodillas con grandes conjuntos de datos (500k filas). Debe haber un truco que no ir todo el camino hasta la parte superior de cada línea (suponiendo que los datos comienza clasificado)

  2. 245

    ACTUALIZACIÓN: Usted puede hacer esto de forma automática con Excel 2013. He creado esta como una nueva respuesta porque mi respuesta anterior en realidad resuelve un problema un poco diferente.

    Si tienes esa versión, a continuación, seleccione los datos para crear una tabla dinámica, y al crear la tabla, asegúrese de que la opción de «Añadir a estos datos el Modelo de Datos’ tickbox es comprobar (ver más abajo).

    Simple de Tabla dinámica para Contar Valores Únicos

    A continuación, cuando el pivote de la tabla se abre, crear sus filas, las columnas y los valores de normalidad. A continuación, haga clic en el campo que se desea calcular el recuento diferente de y editar el Valor del Campo de Configuración:
    Simple de Tabla dinámica para Contar Valores Únicos

    Finalmente, desplácese hacia abajo hasta la última opción y seleccione ‘Recuento diferente.’
    Simple de Tabla dinámica para Contar Valores Únicos

    Esto debe actualizar la tabla dinámica, los valores para mostrar los datos que usted está buscando.

    • ¿Alguien sabe si esto funciona en LibreOffice también? No parece ser una opción similar, pero tal vez está oculto en algún lugar?
    • Esto me ayudó mucho. Gracias. Nadie sabe qué sucede si puedo abrir un archivo de excel con distinct count resumen en una hoja de Excel 2010, por ejemplo? Tal vez la tabla dinámica conseguir todo desordenado?
    • Si intenta guardar un archivo que utiliza el Modelo de Datos de la función como un viejo formato de Excel, aparece una advertencia de que «Algunas de las funciones de tabla dinámica no serán salvados’ – supongo que cualquier persona que intenta abrir este archivo de Excel 2010 no será capaz de verlo. Si usted está tratando de transferir los datos, siempre se puede copiar la tabla dinámica y pegar los valores en una hoja nueva. No es ideal, pero siempre compatibles! 🙂
    • es mucho mejor, si tienes Excel 2013
    • Esto también puede ser hecho a la existente, tablas dinámicas, por lo que no necesita volver a crear 200+ tablas para obtener acceso a la funcionalidad de recuento diferente?
    • Tengo esta característica en el 2010, así que no acaba de 2013
    • Sólo es para su INFORMACIÓN: si aún no ha guardado el archivo de Excel (.xlsx) archivo aún (por ejemplo: usted abrió una .archivo csv), la opción de «Añadir estos datos el Modelo de Datos» está deshabilitado/gris. La solución es simple para guardar el archivo como un archivo de Excel.
    • Si se agregan los datos del Modelo de Datos, usted será NO ser capaz de campos de Grupo en su pivote, es decir, usted no será capaz de agrupar un campo de fecha y hora por mes/trimestre/año/etc.
    • Es este no se admite en Mac? No aparece esta opción para mí. Estoy en la versión 15.27.
    • Si agrega los datos al Modelo de Datos, a continuación, también me parece no ser capaz de añadir un campo calculado. (información adicional a lo que @blobdon comentado más arriba)
    • Esta opción, de hecho, no existe en un Mac, como Modelos de Datos en general son sólo para Windows característica.
    • Bien maldito… necesita obtener Windoze.
    • Mientras que esta solución le da Distinct Count se deshabilita la posibilidad de personalizar los subtotales (entre otras cosas comentó más arriba)
    • Hola, yo he utilizado esta solución para obtener un recuento diferente y funciona. Sin embargo, tengo una tabla dinámica filtro que es el nombre del mes. Ahora es el ordenamiento de los meses en orden alfabético en lugar de por orden cronológico. ¿Cómo puedo rectificar esto, por favor?

  3. 10

    Me gustaría lanzar una opción adicional a la mezcla, que no requiere de una fórmula, pero podría ser útil si usted necesita para contar valores únicos dentro del conjunto a través de dos columnas diferentes. Utilizando el ejemplo original, no tengo:

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

    y desee 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 aparecer como:

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

    He encontrado la mejor manera para obtener mis datos en este formato y, a continuación, ser capaz de manipular además, fue el uso de los siguientes:

    Simple de Tabla dinámica para Contar Valores Únicos

    Una vez que usted seleccione ‘total de Ejecución en’, a continuación, seleccione el encabezado para el segundo conjunto de datos (en este caso sería el encabezado o título de la columna del conjunto de datos que incluye 123, 456 y 567). Esto le dará un valor máximo con el número total de elementos de ese conjunto, dentro de su conjunto de datos principal.

    Luego copiar estos datos, pegado como valores, a continuación, poner en otra tabla dinámica para manipularlo más fácilmente.

    FYI, había cerca de un cuarto de millón de filas de datos, por lo que esto funcionó mucho mejor que algunos de la fórmula enfoques, especialmente los que tratan de comparar a través de dos columnas o conjuntos de datos porque guardaba estrellarse la aplicación.

    • Tuve un problema completamente diferente, pero esta respuesta sólo me señaló en la dirección correcta. Gracias.
    • esta respuesta se ajusta a mi necesidad como me han 500,000 filas que necesito para aplicar la fórmula y mi equipo se queda sin memoria si estoy tratando de. gracias!
  4. 6

    He encontrado el método más fácil es utilizar el Distinct Count opción en Value Field Settings (izquierda haga clic en el campo en el Values panel). La opción para Distinct Count está en la parte inferior de la lista.

    Simple de Tabla dinámica para Contar Valores Únicos

    Aquí está el antes (SUPERIOR; normal Count) y después (ABAJO; Distinct Count)

    Simple de Tabla dinámica para Contar Valores Únicos

    Simple de Tabla dinámica para Contar Valores Únicos

    • Como de Office 2016: Para poder utilizar esta función de tabla dinámica debe ser creada con «Agregar estos datos en el Modelo de Datos» marcada.
  5. 3

    No es necesario para que la tabla se ordenan de 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 A1:B7, escriba la siguiente fórmula en la Celda C1:

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

    Copia la fórmula a todas las filas y el último de la fila contendrá:

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

    Esto se traduce en un 1 de ser devuelto a la primera vez que un registro se encuentra y 0 para todos los tiempos después.

    Simplemente la suma de la columna en la tabla dinámica

    • En caso de que usted tiene un gran conjunto de datos, el uso de =IF(COUNTIF($B$1:$B1,B1),1,0) – de esta manera, countif se ejecuta sólo una vez!
  6. 2

    Mi aproximación a este problema fue un poco diferente a lo que yo veo aquí, así que voy a compartir.

    1. (Hacer una copia de sus datos)
    2. Concatenar las columnas
    3. Eliminar duplicados en la concatenación de columna
    4. Último pivote sobre 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 este es mi primer post 😉

  7. 1

    Siddhartha la respuesta es excelente.

    Sin embargo, esta técnica puede golpear problemas cuando se trabaja con un gran conjunto de datos (mi computadora se congeló hasta en 50.000 filas). Algunos menos intensivo del procesador métodos:

    Sola comprobación de unicidad

    1. Ordenar por las dos columnas (A, B en este ejemplo)
    2. Utilizar una fórmula que se ve en menos de datos

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

    Múltiples comprobaciones de univocidad

    Si usted necesita para comprobar la singularidad en diferentes columnas, usted no puede basarse en dos tipos.

    Lugar,

    1. Tipo de una sola columna (A)
    2. Añadir fórmula que cubre el número máximo de registros para cada agrupación. Si ABC pueda tener 50 filas, la fórmula será

      =IF(SUMPRODUCT(($A2:$A49=A2)*($B2:$B49=B2))>1,0,1)
      
    • Otro posiblemente menos uso intensivo del procesador es añadir una columna C y en C2 =A2&B2. A continuación, agregue una columna D y D2 poner =IF(MATCH(C2, C$2:C2, 0) = ROW(C1), 1, 0). Rellenar hacia abajo en ambos. Mientras este todavía busca desde el inicio de la gama, que se detiene cuando encuentra la primera de ellas, y en lugar de multiplicar los valores de 50.000 filas juntos sólo tiene que buscar el valor, por lo que debe realizar mucho mejor.
    • Sharp – yo también creo que su técnica se detiene en la primera de encontrar. Pero si usted tiene un montón de valores únicos en C (por ejemplo: sólo el 50 Abc), que continuará enormes cantidades de datos. Característica interesante: su fórmula funciona mejor cuando los datos no están ordenados.
  8. 1

    Excel 2013 puede hacer Count distinct en pivotes. Si no hay acceso a 2013, y es una cantidad más pequeña de datos, puedo hacer dos copias de los datos en bruto, y en la copia de b, seleccione ambas columnas y eliminar los duplicados. Luego de hacer el pivote y el recuento de la columna b.

  9. 1

    Puede utilizar COUNTIFS por múltiples criterios,

    =1/COUNTIFS(a:a,A2,B:B,B2) y, a continuación, arrastre hacia abajo. Puedes poner tantos criterios como quieras, pero tiende a tomar un montón de tiempo para el proceso.

  10. 1

    Paso 1. Agregar una columna

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

    Paso 3. Arrastrar a todos los expedientes

    Paso 4. Filtro de ‘1’ en la columna de fórmula

  11. 0

    Usted puede hacer una columna adicional para almacenar la singularidad, a continuación, suma que en la tabla dinámica.

    Lo que quiero decir es, celular C1 siempre debe ser 1. Celular C2 debe contener la fórmula =IF(COUNTIF($A$1:$A1,$A2)*COUNTIF($B$1:$B1,$B2)>0,0,1). Copia esta fórmula hacia abajo de modo que la célula C3 contendría =IF(COUNTIF($A$1:$A2,$A3)*COUNTIF($B$1:$B2,$B3)>0,0,1) y así sucesivamente.

    Si usted tiene una celda de encabezado, usted querrá mover todos estos hacia abajo una fila y su C3 fórmula debe ser =IF(COUNTIF($A$2:$A2,$A3)*COUNTIF($B$2:$B2,$B3)>0,0,1).

  12. 0

    Si usted tiene los datos ordenados.. me sugieren el uso de la siguiente fórmula

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

    Esto es más rápido, ya que utiliza menos células para calcular.

  13. 0

    Me suelen ordenar los datos por el campo que necesita para hacer el recuento diferente de, a continuación, utilizar la SI(A2=A1,0,1); se obtiene entonces obtener un 1 en la fila superior de cada grupo de Identificadores. Simple y no toma nada de tiempo para calcular en conjuntos de datos grandes.

  14. 0

    Puede utilizar para ayudante de columna también VLOOKUP. He probado y se ve poco más rápido que COUNTIF.

    Si usted está utilizando el encabezado y los datos están empezando en la celda A2, entonces en cualquier celda de la fila el uso de esta fórmula y copiar en todas las celdas de la misma columna:

    =IFERROR(IF(VLOOKUP(A2;$A$1:A1;1;0)=A2;0;1);1)
    
  15. -3

    He encontrado una manera más fácil de hacer esto. Refiriéndose a Siddarth Derrota del ejemplo, si quiero contar valores únicos de la columna A:

    • agregar una nueva columna C y llenar C2 con la fórmula «=1/COUNTIF($A:$A,A2)»
    • arrastrar la fórmula hacia abajo para el resto de la columna
    • pivote con Una columna como etiqueta de fila, y la Suma{columna C) en los valores para obtener el número de valores únicos de la columna Un
    • Lógicamente, esto no puede trabajar para el OP porque no se ven en la columna B. ¿Cómo va la adaptación de este a trabajar con varias columnas?

Dejar respuesta

Please enter your comment!
Please enter your name here