Para el siguiente ejemplo, volvemos a trabajar con dos tablas, modelo primero, y, modelo segundo, tablas parecidas a ejemplos anteriores, en este caso, en la primera tabla tenemos una fecha de venta, un producto vendido, y, las ventas.
En la segunda tabla, tenemos un ID, un producto, una fecha de fin de mes, y, costes de publicidad, tenemos tres productos, por lo que, para cada fin de mes, vamos a tener tres productos.
Queremos realizar el ejemplo como modelo de datos, donde queremos una tabla dinámica, donde veamos para cada mes, el total y los costes publicitarios, por lo que debemos de calcular los números correctos.
Vamos a necesitar crear una tabla de fechas a partir del modelo, la haremos de forma dinámica, por si se añaden nuevas fechas.
Lo primero es saber la fecha mínima, para ello, usare la función MIN en la columna fecha del primer modelo.
Lo voy a realizar en pasos diferentes, aunque al final, será una única función.
=MIN(Modelo_primero[Fecha])
Tenemos la fecha mínima.
Ahora, uso la función MAX, para extraer la fecha máxima.
=MAX(Modelo_primero[Fecha])
Tenemos la fecha mínima y máxima.
Si resto la fecha máxima menos la fecha mínima, obtengo los días del modelo.
=M5-L5
Obtenemos 730 días.
Pero, debemos de sumar 1, en caso contrario, nos devolverá un día menos.
=(M5-L5)+1
Y, tenemos 731 días.
Prácticamente, ya tenemos el calendario, lo siguiente es usar la función SECUENCIA, para crear una matriz en vertical, que debe de ser de 731 filas, entonces, escribimos el signo igual, seguido de la función, y, abrimos un paréntesis.
=SECUENCIA(
Como argumento filas, es la operación de resta que hemos realizado anteriormente.
=SECUENCIA((MAX(Modelo_primero[Fecha])-MIN(Modelo_primero[Fecha]))+1
Omitimos el argumento columnas, como argumento inicio, va a ser la fecha mínima.
=SECUENCIA((MAX(Modelo_primero[Fecha])-MIN(Modelo_primero[Fecha]))+1;;MIN(Modelo_primero[Fecha])
Y, como argumento paso, ponemos 1.
Cerramos paréntesis, y, aceptamos.
=SECUENCIA((MAX(Modelo_primero[Fecha])-MIN(Modelo_primero[Fecha]))+1;;MIN(Modelo_primero[Fecha]);1)
Vemos que la primera fecha es 01/01/2020, y, la ultima fecha es 31/12/2021, con lo que ya tenemos nuestro calendario.
Ahora, extraemos el número de mes.
=MES(J3)
El nombre del mes, que lo haremos con la función TEXTO.
=TEXTO(J3;"mmmm")
Por último, el año.
=AÑO(J3)
Seleccionamos las tres celdas, y, arrastramos.
Vamos a convertir el modelo en tabla, para ello, con una celda dentro del modelo, pulsamos CTRL más T.
Se abre la ventana de crear tabla, nos aseguramos de que está marcada la casilla la tabla tiene encabezados, y, aceptamos.
Vemos que nos devuelve un error de desbordamiento, debido a que no podemos usar funciones matriciales en una tabla, vamos a la pestaña de diseño de tabla, dentro del grupo herramientas, hacemos clic en convertir en rango.
En la ventana que se abre, hacemos clic en SI.
Vamos a copiar las fórmulas, y, pegarlas como valores, para ello, nos colocamos en la celda J3, pulsamos CTRL mas cursor derecha, y, cursor abajo, pulsamos CTRL mas C, para copiar, dentro del modelo, hacemos clic con botón alternativo de ratón, y, seleccionamos pegar como valores.
Ahora, lo convertimos en tabla.
Vamos a la pestaña diseño de tabla, y, le cambiamos el nombre.
Tenemos que crear una tabla de calendario, para relacionarla con las dos tablas, es decir, tenemos que crear una relación entre fecha de la tabla calendario, y, fin de mes, de la tabla modelo segundo, y, entre fecha de la tabla calendario, y, fecha de la tabla primer modelo.
Después, solo tendremos que arrastrar mes, y, año al área de filas, y, resumir por las ventas, y, costos publicitarios.
Voy a llevar el primer modelo a Power Pivot, para ello, con una celda dentro del modelo, vamos a la pestaña de Power Pivot, y, hacemos clic en agregar a modelo de datos.
Repetimos el paso, y, nos llevamos las dos tablas restantes.
Ya tenemos las tres tablas en Power Pivot.
Lo siguiente es crear las relaciones, entre la tabla calendario, y el primer, y, segundo modelo, para ello, desde la pestaña de inicio, dentro del grupo ver, hacemos clic en vista de diagrama.
Vemos nuestras tablas.
De la tabla calendario, arrastramos fecha hasta fecha del modelo primero.
Se crea una relación de uno a varios, donde el lado uno es la columna fecha de la tabla calendario, porque las fechas son únicas, y, el lado varios en la columna fecha del modelo primero, porque se pueden repetir.
Arrastramos de nuevo fecha de la tabla calendario a fin de mes de la tabla segundo modelo, se volverá a crear una relación de uno a varios.
Cuando filtremos por una fecha de la tabla calendario, pasara por la columna fin de mes de la tabla modelo segundo, y, solo encontrara una coincidencia, que es el último día del mes, y, nos devolverá las ventas, después, pasa por la columna fecha de la tabla modelo primero, donde coincidirá una fecha única de la columna fecha de la tabla calendario con varias fechas de la columna fecha de la tabla modelo primero, y, nos dará el total.
El siguiente paso es crear nuestras medidas, selecciono la tabla modelo primero, voy a la zona de cálculo, como nombre de la medida, voy a poner Ventas, y, sumare la columna de total ventas.
Lo ponemos en formato de moneda, para ello, desde la pestaña de inicio, hacemos clic en el siguiente icono, y, seleccionamos nuestra moneda.
En el mismo modelo, creamos otra medida, llamada publi, donde sumamos la columna de publicidad del modelo segundo.
Lo ponemos en formato de moneda.
Vamos a crear nuestra tabla dinámica, para ello, desde la pestaña de inicio, desplegamos tabla dinámica, y, seleccionamos tabla dinámica.
Se abre la ventana crear tabla dinámica, marcamos hoja de calculo existente, y, la ponemos dentro de los modelos.
Llevo a filas año, y, mes de la tabla calendario, y, las dos medidas que hemos creados.
Pero los meses no están ordenados, pues, vamos a ordenarlos, para ello, desplegamos etiquetas de fila, y, seleccionamos mas opciones de ordenación.
Se abre la ventana ordenar, marcamos ascendente (A a Z) por, y, seleccionamos nombre mes, en caso de que no aparezca, volvemos a la tabla dinámica, con una celda dentro de la tabla, hacemos clic con botón alternativo de ratón, y, actualizamos.
Hacemos clic en más opciones.
Desmarcamos la casilla ordenar automáticamente cada vez que se actualice el informe, en la ventana primer criterio de ordenación, elegimos, enero, febrero, marzo…
Aceptamos, y, en la siguiente ventana, volvemos a aceptar.
Ya tenemos los meses ordenados.
Haciendo un pequeño repaso, la relación entre la columna fin de mes de la tabla modelo segundo, y, la columna fecha de la tabla calendario, solo devuelve una fila, porque solo tenemos una fecha para cada mes, pero como tenemos tres productos, tenemos tres fechas de fin de mes para cada producto, si nos fijamos en la tabla modelo segundo, y, nos fijamos en la fecha 31/01/2020, vemos dichas tres filas, que si la sumamos nos da el valor de publi que aparece en la tabla dinámica.
Pues, este era el ejemplo que quería mostraros.
Comments