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:

Autor: Quique Arranz

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.

36 opiniones en “Ejercicio de tablas dinámicas resuelto”

  1. hola Quique,
    en la tabla falta la última fila con el filtro adsl y mes marzo, no me cuadraba la cifra y es que falta justo la última por importe de 45, así si da el resultado de 23156 en lugar de 23111 que da en el ejercicio.
    Tampoco entiendo porque solo llegas hasta el mes tres cuando tenemos datos hasta junio. Y como tengo la versión de excel 2007, no puedo segmentar datos? Aunque aún no lo he entendido bien.

    1. Hola Cris,
      el ejercicio sólo tiene datos hasta marzo, por eso sólo te salen tres meses. En este ejercicio no hace falta hacer nada con segmentación de datos.
      Saludos,
      Quique

      1. Hola Quique,
        yo tampoco entiendo porque dices que en este ejercicio solo hay datos hasta marzo. A mí me sale hasta Junio y aunque oculte datos de los ultimos 3 meses los datos de marzo no me coinciden con los ejercicios de resolución. Me podrías ayudar con esto por favor. Gracias

        1. Hola Cinthia, decimos que sólo hay datos hasta marzo porque en la columna Fecha factura no tienes datos más allá de mar-13 o, si prefieres, 01/03/2013. Si tienes más meses revisa de dónde los estás tomando. Saludos,

  2. Hola quique
    no aparece el enlace lo puedes poner gracias
    Para ver la resolución del ejercicio puedes hacer click en el siguiente enlace:

      1. Hola Alexis,
        Para desbloquear debes hacer algo de promoción a la web en las redes sociales. PUedes hacer “Like” en la página de Facebook o compartir el enlace en Twitter o Google+ a través de los iconos que bloquean la descarga.
        Un saludo,
        Quique

  3. EXCELENTE Quique, muchas gracias por tu tiempo y compartir tus conocimientos; tengo una pregunta, estoy interesado en aprender excel en serio y quiero preguntarte en que link puedo aprender el uso de las funciones que sea practico como tu lo has hecho en esta pagina

    1. Hola Antonio:
      En esta misma web, encontrarás la explicación de muchas funciones. Son las más utilizadas. Y como decimos siempre, si quieres conocer sobre la que no hemos escrito, por favor, dinos y, con mucho gusto, escribiremos sobre ella a la mayor brevedad.
      Saludos.

  4. Estimado
    En el excel de respuestas hay errores entre los rangos 60-65…. no sé que hacer para que las edades de 65 estén en ese rango.

    1. Hola Luis:

      Encantado de ayudar pero necesitaría más datos. Envíanos un email con un ejemplo de tabla y lo miramos. Por favor, para proteger la confidencialidad de la información elimina todo lo que pueda identificar a tus clientes (nombre, NIF) y los datos de tu empresa.

      Esperamoss tus noticias.

      1. necesito registrar fecha numero de facturas o recibos montos y saldos de manera automatizada con control maestro de clientes no es preciso detallar productos, solo valores . Agradezco tu repuesta y ayuda

        1. Hola Luis:
          Si estás haciendo un control de facturas pendientes de pago de tus clientes en Excel no hay problema. Tendrás una tabla con todas las facturas y otra con el maestro de clientes. El saldo pendiente de pago por cliente lo puedes calcular con la función SUMAR.SI.CONJUNTO. Incluso SUMAR.SI podría funcionar pero quizá se quede un poco corto.
          En cuanto a qué facturas pagan tus clientes, si tienes una tabla con segmentación de datos sería muy facil extraer las que están pendientes de pago para anotar la fecha de pago en alguna columna que hayas creado con ese fin. En esto no hay muchas maneras de automatizar las cosas. Hay que elegir qué factura está pagando el cliente. Incluso, si tu le mandas un recibo a su banco, tendrás que elegir qué facturas quieres enviar. Y eso te ocurre si utilizas Excel o un ERP como NAV, AX, sAP,…
          Saludos.

          1. Gracias por contestarme. Si puedes me envias una tabla como te la he pedido. para control de cuentas por cobrar y pagar

    1. Hola Lina, para sacar los top n de un valor de la tabla dinámica puedes filtar los valores de la misma a través de las flechitas que verás en la cabecera de las tablas dinámicas.

  5. Estimado, esta muy bueno el ejercicio, pero la verdad no entiendo mucho lo de conicidir, tu en la otra hoja pones el rango cierto a eso te refieres con ” Tabla_de_configuración_columna_edad ” ?

  6. No entiendo la necesidad de calcular el rango de edad.
    Las Tablas dinámicas ya tienen la función “agrupar selección” que permite hacer lo mismo

    1. Sip, pero si lo calculas previamente es mucho más automático. Puedes filtrar cómodamente y tardas muy poquito en hacerlo.
      Gracias por comentar xavi! saludos

  7. Necesito que me puedan aclarar y realizar el rango de las edades como a ustedes les aparece .. yo solo aplique rango de edad y no lo que ustedes mencionan de 20-24..25-29—y asi hasta 60-64…..me es enredado.

    Agradezco su ayuda

  8. Para el calculo de la edad, usar la función =SIFECHA(D2,HOY(),”Y”).

    Para la tabla mensualidad, despues de realizar la tabla dinamica, podemos indicar que agrupe por año y Mes.

  9. Hola,
    no se si sería más rapido hacer los campos de EDAD con una funcion SI e Y anidadas, la funcion de coincidir tal como la usas no me coge bien algunas edades, no me da las filas correctas (ej: para la edad 63, me da la posicion 1, < 25, y así) he probado con SI y me funciona mejor (y ahorrariamos hacer la columna de Fila de rangos y la de Rango de edad, tal que así: agrupo de 20-30, 30-40, etc y tomo los años de nacimiento en vez de las edades:

    =SI(Y(E21954);$L$5;SI(Y(E2>1974;E21984;E21994;E2<1996);$L$7;SI(E2=1994;$L$7;SI(E2=1984;$L$4;SI(E2=1974;$L$5;)))))))

    Muy util el ejemplo de las tablas dinamicas en 1 sola hoja a modo de reporte global, genial!

    Saludos a todos

    1. Disculpad por cierto que en la formula anidada que os comentaba, hay errores de pegado :
      Esto era lo que en relaidad os quería mostrar:

      =SI(Y(E21954);$U$5;SI(Y(E2>1974;E21984;E21994;E2<1996);$U$7;SI(E2=1994;$U$7;SI(E2=1984;$U$4;SI(E2=1974;$U$5;SI(E2=1954;$U$6))))))))

      1. Puede que sea más sencillo cuando sólo hay que anidar unos pocos IF . Pero imaginemos que tenemos 50? Creo que el ejemplo es una simplificación que puede resultar muy útil. Si tienes problemas para que la función COINCIDIR te de errores, busca cómo hacer una mejor tabla de configuración o un workaround para evitar el problema.
        saludos!

  10. Le estoy echando un ojo! Q bien explicado. Gracias por darnos la oportunidad de conocer esta herramienta de las tablas dinámicas! Estoy iniciándome!

Comentarios cerrados.