A continuación os mostramos un ejercicio de tablas dinámicas con el que poder practicar y entender bien cómo y para qué se pueden usar. Como todos sabemos, las tablas dinámicas nos permiten filtrar información masiva de manera rápida y sencilla con unos resultados fiables 100% (no así cuando usamos fórmulas). Como todo en esta vida, para manejar con soltura las tablas dinámicas hay que hacer muchas y con múltiples enfoques y por eso este ejercicio.
Ejercicio
El siguiente ejercicio que presentamos contiene información de 2.000 clientes de una compañía de telecomunicaciones. La información disponible es algo de información personal (sexo, año de nacimiento, provincia) e información interna (importe de la factura, fecha de la factura, tipo de servicio prestado). Esta información se encuentra en una tabla inicial con los datos en bruto.
En la siguiente imagen se puede ver una muestra de la tabla de datos que usaremos para nuestro análisis.
El ejercicio consiste en obtener los datos necesarios de la tabla para poder hacer las siguientes tablas dinámicas:
- Importe por mes y servicio
- Segmentación de servicios por edad
- Segmentación de servicios por sexo
- Importe por localidad
- Diferencias porcentuales entre localidades
- Top 20 mayores facturas acumuladas
En este link podéis bajaros los datos necesarios para resolver el ejercicio: Ejercicio – Segmentación de datos
En el siguiente enlace tenéis un completo tutorial de cómo hacer y plantear tablas dinámicas: click aquí
Solución
Para resolver este ejercicio hay que crear una serie de columnas auxiliares en los datos aportados para poder acceder a la información desde el punto de vista que nosotros queremos dar.
En este caso, a través de la columna de cumpleaños podremos obtener la edad que cada persona tiene y después buscar la franja de edad a la que pertenece.
Para obtener la edad haremos lo siguiente:
=TEXTO(HOY()−[@[cumpleaños]];"aa")
El problema de esta función es que nos arrojará como resultado un número en formato de texto. Para convertir este “texto” en formato numérico deberemos usar la función VALOR (convierte un texto compuesto por números en formato numérico).
=VALOR(TEXTO(HOY()−[@[cumpleaños]];"aa") )
De esta manera obtendremos un número que podremos buscar en nuestra tabla de configuración para darle el rango de edad.
La fórmula que nos permite ligar un número con nuestra tabla de configuración de rango de edades es la fórmula INDICE junto con la función COINCIDIR. Nosotros lo hemos dividido en dos columnas para que sea más sencillo de seguir. En una de las columnas hemos puesto el número de fila en la que se encuentra el rango de edad relacionado con la edad de cada fila.
=COINCIDIR([Edad];Tabla_de_configuración_columna_edad;−1)
Mediante esta formula habremos encontrado la fila en la que se encuentra cada franja de edad. Es decir, el mayor valor siguiente a nuestro número que podemos encontrar en la fórmula.
Una vez calculado la fila dentro de la tabla a la que hace referencia la edad de cada persona, lo único que nos queda por hacer es encontrar el rango de edad en nuestra tabla de configuración a la que hace referencia nuestro último cálculo.
=INDICE(Fila_calculada; Tabla_de_configuración_columna_rango_Edad)
Una vez realizados estos sencillos cálculos (que son más complicados de describir) que de hacer, podremos crear nuestras tablas dinámicas.
Para ver la resolución del ejercicio puedes hacer click en el siguiente enlace:
[sociallocker]
Resolución del ejercicio de tablas dinámicas
[/sociallocker]
Mas sobre Excel: