Ejercicio de tablas dinámicas resuelto

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.

ejercicio de tablasd dinámicas tabla datos

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:

Mas sobre Excel:

Quique es un fanático del Excel desde que empezó a trabajar con ello hace unos pocos años. Adquirió su conocimiento a través de Google y de infinitas pruebas y más pruebas. Además, Quique es un entusiasta de su familia (mujer e hijos), del deporte y de la cerveza… no dudes en contactar con él para cualquier duda que tengas.

Uso de cookies

Este sitio web utiliza cookies para que usted tenga la mejor experiencia de usuario. Si continúa navegando está dando su consentimiento para la aceptación de las mencionadas cookies y la aceptación de nuestra política de cookies, pinche el enlace para mayor información ACEPTAR

Shares
Share This

Comparte este post

Comparte si te resulta útil la información.