martes, diciembre 23, 2014

Como contar registros únicos en tablas dinámicas

Eduardo, colega de trabajo, es inteligente, aplicado y no le gusta depender del departamento de informática. Por eso, maneja una serie de pequeñas bases de datos en Excel (para el horror del departamento de IT ya mencionado). Para generar sus reportes usa principalmente tablas dinámicas de las cuales, para ponerlo de alguna manera, está perdídamente enamorado. A tal punto que todos mis intentos de mostrarle las bondades de otras herramientas como, por poner un ejemplo, Filtro Avanzado, siempre fracasan en forma rotunda.
Por eso cuando Eduardo entró ayer en mi oficina, sólo por la mirada, me di cuenta que algo le estaba pasando. No era una visita de cortesía.

- ¿Cómo hago para saber cuántos clientes tengo en mi base de datos?
 - Dado que todo lo hacés con tablas dinámicas, te sugiero que arrastres el campo de clientes al área de los datos usando la función Contar para totalizar.
- Si, es lo que hice; pero cada cliente aparece en más de una fila en la base de datos y entonces me cuenta la cantidad de veces que cada cliente aparece, no la cantidad de clientes.
- Por supuesto, así funcionan las tablas dinámicas.
- Pero, ¿cómo, no hay una función para contar registros únicos?
- Si y no...
- Uf, ya empezaste. ¿Si o no?
- En Excel 2013 hay una función para contar registros únicos en un reporte dinámico; en Excel 2010 y anteriores, no.
- ¡Ah! Yo uso Excel 2010, ¿cómo hago?
- Creando un campo auxiliar en la base de datos.

Supongamos que nuestra base de datos es la tabla de facturas de la base de datos Northwind

Para contar cuántos clientes hay en la base de datos creamos una tabla dinámica arrastrando los campos País y Cliente al área de las filas y nuevamente el campo Cliente al área de datos usando la función Contar (dado que el campo Cliente no contiene valores numéricos, Excel usará esta función en forma automática)

Inmediatamente podemos ver que en Argentina hay 3 clientes, pero la tabla dinámica muestra 11.
En las versiones de Excel anteriores a Excel 2013, tenemos que usar una columna auxiliar.
Insertamos la columna Auxiliar entre los campos Cliente y Dirección; en esta columan ponemos la fórmula =CONTAR.SI($B$3:B3,B3) que copiamos a todas las filas


El campo Auxiliar muestra el número de orden de aparación de cada cliente. Ahora podemos usar este campo como filtro de la tabla dinámica para que muestre sólo los registro donde el valor de Auxiliar es 1

Ahora podemos ver que la cuenta es correcta


Con Excel 2013, las cosas son más sencillas. No necesitamos crear ningún campo auxiliar. Sencillamente creamos la tabla dinámicas a partir de la base de datos. En el asistente de tablas dinámicas nos aseguramos de marcar la opción "Agregar estos datos al Modelo de datos" (esta opción sólo existe en Excel 2013)

Una vez creada la tabla, arrastramos el campo País al área de filas y el campo Cliente al área de los datos; seleccionamos el área de datos y abrimos el menú de configuración del campo. En la casilla de elección del tipo de cálculo tenemos una nueva función: "Recuento distinto"


Elegimos esta función y apretamos aceptar


Podemos ver que el encabezamiento del campo ha cambiado de "Recuento de cliente" a "Recuento distinto de Cliente" y que efectivamente tenemos 84 clientes en la base de datos.

Personalmente pienso que la traducción tendría que haber sido "Recuento único".


14 comentarios:

  1. Es probable que recuento distinto se debe a la herencia de consultas en motores de bases de datos, ya que para obtener resultados únicos se utiliza la declaración DISTINCT.

    ResponderBorrar
  2. Hola Cristián, la diferencia en el resultado se debe a cómo trabajan las tablas dinámicas. En forma similar al Autofiltro, el resulta de Cuente es el recuento de todas las filas que cumplen con la condición. Lo que hizo Microsoft en Excel 2013 es introducir una nueva función que no existe en las versiones anteriores.

    ResponderBorrar
  3. Me faltó indicar que comentaba sobre el último párrafo de la traducción :)

    ResponderBorrar
  4. Ah, por supuesto. Me apresuré a comentar sin leer a fondo lo que escribiste. Mis disculpas.

    ResponderBorrar
  5. Excelente! Muchas gracias.

    ResponderBorrar
  6. Gracias por la información!!!, fue un gran salvavidas para un informe que debo preparar y no encontraba la manera de contar los registros únicos....

    ResponderBorrar
  7. Se puede aplicar esta opción (recuento distinto) a una tabla dinámica que se creó en Excel 2010 y actualmente la trabajo en Excel 2013??? Consultó porque la misma la cree con consultas múltiples de diferentes bases de datos. Y otra cosa, cuando aplicó esta opción, luego lo podré abrir en un Excel 2010??agradeceré sus comentarios.

    ResponderBorrar
  8. Si abres la table en Excel 2013 la función está disponible. Pero no hay compatibilidad hacia atrás, es decir, no funcionará en Excel 2010 o anteriores.

    ResponderBorrar
  9. Hola Mil gracias por compartir tus conocimientos. Tengo un inconveniente, estoy aplicando el recuento, pero cuando deseo utilizar campos calculados no me permite, pues aparecen deshabilitados. Como puedo habilitarlos para utilizar dichos campos calculados.

    ResponderBorrar
  10. En el modelo de datos los campos (columnas calculadas) se crean dentro del editor del modelo. ¿Qué versión estas usando, 2013, 2016, 365?

    ResponderBorrar
  11. Hola, es curioso que solo funcione en 2013 y no posteriores. Tengo 2016 y no lo encuentro disponible, tuve que usar el Contar.si que tiene algunas barreras, cuando cruzo el item con la fecha. Solo aparece una vez...:-(

    ResponderBorrar
    Respuestas
    1. Andrés, funciona en todas las versiones desde 2013 en adelante. Asegurate de tener PowerPivot activado. Fijate en Opciones-Add Ins, selecciona la opción COM y activá el complemento.

      Borrar
    2. Acabo de acordarme en que Excel 2016 PowerPivot está incluido sólo en la versiones Pro Plus o superiores.

      Borrar
    3. Y siguiendo con el hilo te sugiero usar Power Query para el cálculo (PQ viene incluido en todas las versiones).

      Borrar

Nota: sólo los miembros de este blog pueden publicar comentarios.