Contar elementos únicos con una tabla dinámica en Excel

Problema

Queremos contar los elementos únicos de un campo mediante una tabla dinámica en Excel.

Solución

Antes del Excel 2013 esto no era posible. Con Excel 2013, 2016 y Office 365 es posible emplear la función de resumen Recuento distinto, siempre que usemos el modelo de datos en Excel.

1. Al crear la tabla dinámica, seleccionamos: Agregar estos datos al modelo de datos y hacemos clic en Aceptar.

2. Añadimos el campo que queremos contar en el área valores

3. Clic en la flecha situada a la derecha del nombre del campo y luego selecciona la opción de Configuración de campo de valor.

4. En Resumir campo de valor por seleccionamos Recuento distinto y clic en Aceptar.

Resultado

Se puede apreciar como el campo Recuento distinto de Campo 2 cuenta los elementos únicos. Mientras que Recuento de Campo 2 el número de valores no vacíos, el equivalente a CONTARA.

Referencias

• Crea un modelo de datos en Excel

Entradas relacionadas

• Desactivar IMPORTARDATOSDINAMICOS como referencia a tablas dinámicas
• Desactivar autoajustar anchos de columnas al actualizar tablas dinámicas
• Opciones de cálculo de fórmulas, rendimiento y tratamiento de errores
• Reducir el tamaño de ficheros con tablas dinámicas en Excel
• Tablas dinámicas con origen de datos dinámico

Numerar las celdas visibles con el autofiltro en Excel

Problema

Al filtrar un rango de datos con autofiltro, queremos que solamente se numeren las celdas visibles.

Solución

Utilizamos la función SUBTOTALES precedida de dos signos negativos. El primer argumento es 2 (CONTAR) para contar los números e ignorará en el encabezado como texto. En el segundo rango fijamos la fila como referencia absoluta A$1 o A$2. Arrastramos la fórmula hacia abajo.

=--SUBTOTALES(2;A$1:A2)

Notas

Utilizamos los dos signos negativos (operador unario) delante de subtotales porque si no, el autofiltro de Excel no reconocería la última fila del rango. Al haber una función SUBTOTAL en la última fila del rango, Excel interpreta que es una fila especial para calcular los totales, excluyéndola del filtro.

Si la columna que usamos para contar (columna A) contiene texto (columna B) en lugar de números, explearíamos como primer argumento de SUBTOTALES el 3 (CONTARA):

=--SUBTOTALES(3;B$2:B2)

En el caso de que nuestro rango incluya filas ocultas emplearemos como primer argumento 102 o 103 para pasarlas por alto.

=--SUBTOTALES(102;B$2:B2)

Referencias

• Contextures
• Subtotales

Tabla dinámica utiliza la función contar en vez de suma con celdas vacías

En las tablas dinámicas, Excel utiliza como función resumen la función suma para datos numéricos y la función contar para datos no numéricos:

Se usa de forma predeterminada para los campos de valor que tienen valores numéricos. Si el campo contiene espacios en blanco o numérico (texto, fecha, o booleano) los valores cuando lo coloca en el área de valores de la Lista de campos, la tabla dinámica utiliza la función Contar para el campo.

Problema

Origen de datos con celdas en blanco:

Tabla dinámica que cuenta los valores en lugar de sumarlos:

Solución

1. Cambiar la función resumen para sumar en lugar de contar

Varios modos de acceder a la Configuración de campo valor:
• En la cinta de opciones Campo activo: Configuración de campo.
• Botón secundario del ratón sobre el campo en cuestión y seleccionamos Configuración de campo valor.
• Desde la lista de campos de tabla dinámica, haciendo clic sobre el campo.
2. Ir a las celdas en blanco y rellenarlas con ceros
• Seleccionamos el rango de origen de datos, área actual alrededor de la celda activa: Ctr+Mayús+*
• Ir al menú Ir a Especial y seleccionamos Celdas en blanco. Atajo F5 o Ctrl+I, Alt+E, presionamos B y Aceptar.

• Escribimos 0 y, con el botón Ctrl presionado, hacemos clic en Entrar.

Referencias

• Microsoft support