Contar valores únicos en una tabla dinámica

Usar un modelo de datos con una tabla dinámica

El modelo de datos es otra cosa que me gusta de las nuevas versiones de Microsoft Excel. Si usa Excel para Microsoft 365, Excel 2019, Excel 2016 y Excel 2013, tiene acceso al modelo de datos.

Para comenzar, haga clic en cualquier celda de los datos y navegue hasta la pestaña «Insertar» en su cinta.
Haga clic aquí en la tabla dinámica y aparecerá un cuadro de diálogo.
Ahora marque la casilla en la parte inferior del cuadro de diálogo, «Agregar estos datos al modelo de datos» y presione Aceptar.
Después de eso, obtendrá una tabla dinámica habitual y organizará sus datos en los campos de la tabla dinámica como lo hizo anteriormente. Esto le dará la misma tabla dinámica que tenía anteriormente, pero los campos de la tabla dinámica son un poco diferentes.
Aquí está el truco: haga clic en la pequeña flecha junto a «Número de proveedores de servicios» en los campos de la tabla dinámica.
Después de eso, haga clic en «Configuración del campo de valor».
Ahora desplácese hasta el final para obtener «Cuenta separada» y haga clic en Aceptar.
Allá vamos: tiene un número distinto/único para cada región en la tabla dinámica.

Por lo tanto, sólo contamos con 18 proveedores de servicios únicos en el país.

Usando la función CONTAR.SI

Otro enfoque para calcular entradas únicas es simplemente usar la fórmula CONTAR.SI en su hoja de datos.

Comencemos agregando una columna a sus datos con un encabezado de su elección. Aquí lo llamaremos “Contar No”.
Agrega esta fórmula (=SI (CONTAR.SI ($B$2:B2,B2)>1,0,1)) a la celda D2 y arrástrala hasta el final.

¿Cómo funciona esta fórmula?

Primero, establecemos el punto inicial del rango, también llamado Absoluto, es decir $B$2. Esto significa que no cambiará incluso si arrastras la fórmula hacia abajo. Ahora, cuando arrastra la fórmula hacia D3, esta fórmula se convierte en IF(COUNTIF($B$2:B3,B3)>1,0,1)

Léelo como

Countif ( $B$2:B3 , B3 ) le dará la cantidad de veces que B3 existe entre el rango $B$2:B3. La función SI se usa para agregar una condición: SI (( el número de veces que B3 existe en un rango determinado ) es mayor que 1, luego dé 0, de lo contrario devolverá 1)

Ahora, si el nombre en la columna dada aparece más de una vez, la fórmula le devolverá 0; de lo contrario, obtendrá 1. Por lo tanto, para todos estos nombres repetidos obtendrá 0 en la columna No contar.

Ahora crea una tabla dinámica con tus datos.
Aquí debe agregar Ubicación a FILAS y Contar No a los valores.
¡¡Auge!! La tabla dinámica está lista con entradas únicas en cada tabla dinámica.

Utilice Power Pivot para contar valores únicos

Este es el método más poderoso para identificar entradas únicas; Pivote de poder. Asegúrese de tener la pestaña Power Pivot en su cinta. Si no puede encontrar la pestaña, consulte este tutorial .

Como se dijo antes, antes que nada, asegúrese de que la pestaña Power Pivot esté habilitada.
Después de eso, vaya al modelo de datos y haga clic en el botón Administrar .
Aquí se abrirá una ventana, que seguramente estará vacía en caso de que sea la primera vez que importas los datos.
Haga clic en Inicio → Obtener datos externos
Aquí encontrará varias opciones y fuentes disponibles para descargar los datos. Pero necesitamos descargar un Excel simple. Así que sigue los pasos y las capturas de pantalla y haz clic en «De otras fuentes».
Ahora volverá a aparecer un cuadro de diálogo abierto. Desplácese hasta el final para obtener la opción de archivo de Excel y haga clic en Siguiente.
Aquí puede cambiar el nombre de la conexión desde el nombre predeterminado «Excel». Haga clic en Examinar para elegir una ruta a su archivo de datos.
Además, si desea que la columna superior sea la fila del encabezado, marque la opción «Usar la primera fila como encabezado de columna» y haga clic en Siguiente.
Al final, el archivo se importa al modelo de datos y se hace clic en finalizar.
Allá vamos: tiene éxito con las 28 líneas importadas. Ahora ataca de cerca.
Esto es lo que parece.
Desde aquí crearemos una tabla dinámica mediante Inicio → Tabla dinámica
Como tenemos los datos en la Hoja 1, expandiremos las columnas haciendo clic en el pequeño triángulo al lado.
Ahora coloque la ubicación en las líneas y los proveedores de servicios en los valores como lo hicimos antes. Esto dará como resultado una tabla dinámica simple con el número total de proveedores de servicios.
Aquí está el truco. Ahora vaya a la ventana de PowerPivot y haga clic en Medir para obtener la opción Nueva medición .
Ahora agregue una descripción del nombre deseado y comience a escribir la fórmula en la sección de fórmulas.
Cuando empieces a escribir, recibirás automáticamente las sugerencias. Aquí necesitamos la función de conteo distinta. Seleccione la función de conteo distinta.
Después de eso, presione el botón de tabulación o inicie un paréntesis (y seleccione la columna para la cual necesitamos el número distinto. Como aquí, necesitamos el número distinto de proveedores de servicios. Por lo tanto, nuestra fórmula se verá así = DISTINCTCOUNT(Hoja1[Proveedor de servicios]) )
Al final, seleccione la categoría. Dado que estamos averiguando el número único de proveedores de servicios, seleccionaremos la categoría «Números».
Cambie el formato a “Entero” y presione OK. Se agregará otra columna a la tabla dinámica que le brindará las entradas únicas.