Para el siguiente ejemplo, tenemos una columna con fechas para el año 2022, otra columna con el nombre que corresponde con cada fecha, y, una última columna unas cantidades vendidas para cada fecha.
Si creamos una tabla dinámica para resumir por meses, una segmentación de datos, y, un gráfico de líneas con marcadores, al seleccionar un mes, aparece un punto que representa el mes seleccionado.
Para verlo mejor, vamos a insertar una tabla dinámica, con una celda dentro del modelo vamos a la pestaña de insertar, y, hacemos clic en tabla dinámica.
Se abre la ventana tabla dinámica desde la tabla o el rango, marcamos nueva hoja de calculo y aceptamos.
Llevamos a filas la columna mes, y, a valores la columna cantidad.
Tenemos la cantidad vendida por mes.
Hacemos clic sobre una celda de la columna cantidad con botón alternativo de ratón, y, seleccionamos formato de número.
Se abre la ventana de formato de celdas, en la ventana categoría, seleccionamos moneda, en la ventana posiciones decimales, ponemos 2.
Aceptamos.
Con una celda dentro de la tabla dinámica, vamos a la pestaña analizar tabla dinámica, y, hacemos clic en insertar segmentación de datos.
Se abre la ventana de insertar segmentación de datos, marcamos la casilla de fecha.
Aceptamos.
Con una celda dentro de la tabla dinámica, vamos a la pestaña de insertar, desplegamos grafico de líneas, y, seleccionamos líneas con marcadores.
Vemos graficado las ventas para cada mes, vamos a la segmentación de datos, y, seleccionamos un mes, vemos que aparece el marcador para dicho mes.
El ejercicio consiste en que si seleccionamos un mes, debe de aparecer desde el mes de enero hasta el mes seleccionado, y, si seleccionamos enero, debe de aparecer todos los meses.
Ocurre que los cálculos que necesitamos no podemos actuar sobre la tabla dinámica, además, el grafico al provenir de una tabla dinámica, esta bloqueado, no podemos añadir series.
Bien, borramos el grafico.
Debemos de crear una tabla de dos columnas, la primera columna contendrá el numero de mes, y, la segunda columna contendrá el nombre del mes, para ello, vamos a usar la función APILARH (HSTOCK), donde como argumento matriz1, usamos la función SECUENCIA para crear una lista de desde el numero 1 al numero 12, por lo que en el argumento filas de SECUENCIA, ponemos 12, y, cerramos paréntesis.
=APILARH(SECUENCIA(12)
Punto y coma, como argumento matriz2, son los valores únicos de la columna mes.
Cerramos paréntesis.
=APILARH(SECUENCIA(12);UNICOS(Ventas[Mes]))
En la segmentación de datos, he seleccionado el mes de abril, por lo que deben de aparecer los meses enero, febrero, marzo, y, abril.
Lo siguiente va a ser aplicar la función COINCIDIR, donde como argumento valor buscado, es el mes que aparece en la tabla dinámica.
=COINCIDIR(A4
Punto y coma, como argumento matriz buscada, usamos la función INDICE aplicada a la matriz desbordada obtenida anteriormente, que es el argumento matriz, omitimos el argumento numero de fila, y, como argumento numero de columna, ponemos 2, que son los nombres de los meses.
=COINCIDIR(A4;INDICE(R2#;;2)
Punto y coma, seleccionamos coincidencia exacta.
Cerramos paréntesis.
=COINCIDIR(A4;INDICE(R2#;;2);0)
Aceptamos, y, nos devuelve la posición 4.
Ahora con la función SECUENCIA, vamos a crear una matriz desbordada, donde como argumento filas, es el resultado de la función COINCIDIR.
=SECUENCIA(COINCIDIR(A4;INDICE(R2#;;2);0);;;1)
Aceptamos y tenemos los números de meses a recuperar el nombre.
Cosa que haremos con la función BUSCARX, donde como argumento valor buscado es el resultado de la función COINCIDIR.
=BUSCARX(SECUENCIA(COINCIDIR(A4;INDICE(R2#;;2);0);;;1)
Punto y coma, como argumento matriz buscada es la primera columna de la matriz desbordada con los números de meses, y, nombres de meses.
=BUSCARX(SECUENCIA(COINCIDIR(A4;INDICE(R2#;;2);0);;;1);INDICE(R2#;;1)
Punto y coma, como argumento matriz devuelta, es la segunda columna de la matriz desbordada.
Cerramos paréntesis.
=BUSCARX(SECUENCIA(COINCIDIR(A4;INDICE(R2#;;2);0);;;1);INDICE(R2#;;1);INDICE(R2#;;2))
Aceptamos, y, ya tenemos los meses a graficar.
Vamos a seleccionar en la segmentación de datos el mes de junio, y, vemos como aparecen los meses desde enero hasta junio.
Lo siguiente es calcular el total para los meses obtenidos, para ello, vamos a usar la función SUMAR.SI.CONJUNTO, aunque también podríamos usar la función SUMAR.SI, porque solo tenemos un criterio, como argumento rango de suma, seleccionamos la columna de cantidad.
=SUMAR.SI.CONJUNTO(Ventas[Cantidad]
Punto y coma, como argumento rango de criterios1, seleccionamos la columna de mes.
=SUMAR.SI.CONJUNTO(Ventas[Cantidad];Ventas[Mes]
Punto y coma, como argumento criterios1, seleccionamos la matriz desbordada con los meses.
Cerramos paréntesis.
=SUMAR.SI.CONJUNTO(Ventas[Cantidad];Ventas[Mes];Hoja2!D3#)
Aceptamos, y, tenemos el total de los meses obtenidos en la matriz desbordada.
Ahora debemos de graficar estos datos que dependen de la segmentación de datos, pero para que el grafico sea dinámico, debemos de crear nombres de rangos, vamos a crear un primer nombre de rango para los nombres de los meses, para ello, vamos a la pestaña formulas, dentro del grupo nombres definidos, hacemos clic en asignar nombre.
Se abre la ventana nombre nuevo, en la ventana nombre, voy a poner nombre_meses, y, en la ventana se refiere a, seleccionamos la celda con el primer nombre del mes junto con el operador de rango derramado.
Aceptamos.
Vamos a crear otro nombre de rango, en este caso, contendrá el total para cada mes, vamos de nuevo a la pestaña formulas, y, hacemos clic en asignar nombre.
En la ventana nombre, voy a poner total_meses, y, en la ventana se refiera a, señalamos el primer total junto con el operador de rango derramado.
Aceptamos.
Teniendo una celda en blanco seleccionada, vamos a la pestaña de insertar, desplegamos grafico de líneas, y, seleccionamos líneas con bordes.
Se habilitan las pestañas diseño de gráfico, y, formato cuando tenemos el gráfico seleccionado.
Dentro de la pestaña diseño de gráfico, hacemos clic en seleccionar datos.
Dentro de la ventana entradas de leyenda (series), hacemos clic en agregar.
En la siguiente ventana debemos de poner los valores a graficar en la ventana valores de la serie, que es el nombre de rango total_meses, pero para poner un nombre de rango a graficar, debemos de poner el nombre del libro seguido de la extensión, signo de exclamación, y, nombre de rango, donde podemos pulsar F3, y, se abrirá una ventana con todos los nombres de rangos existentes, solo debemos de seleccionar el nuestro y aceptar.
Quedaría como sigue:
Aceptamos, y, vemos graficado los meses hasta el mes seleccionado en la segmentación de datos.
Volvemos a seleccionar datos dentro de la pestaña diseño de gráfico, en este caso, hacemos clic en editar en la ventana etiquetas del eje horizontal (categoría).
Vamos a poner los nombres de los meses graficados.
Se abre la ventana rótulos del eje, donde ponemos el nombre de rango nombre_meses.
Aceptamos, y, ya lo tenemos.
Si vamos cambiando de mes en la segmentación de datos, veremos como se actualiza el grafico.
El problema es que, si seleccionamos enero, no se grafican todos los meses.
Para solventarlo, vamos a la siguiente expresión:
=BUSCARX(SECUENCIA(COINCIDIR(A4;INDICE(R2#;;2);0);;;1);INDICE(R2#;;1);INDICE(R2#;;2))
Después del signo igual, usamos el condicional SI, donde preguntamos que si el mes seleccionado en la segmentación de datos es igual a enero, como argumento valor si verdadero, usamos la función UNICOS para traernos los meses únicos de la columna mes.
=SI(A4="enero";UNICOS(Ventas[Mes])
Como argumento valor si falso, es la expresión BUSCARX.
Cerramos paréntesis.
=SI(A4="enero";UNICOS(Ventas[Mes]);BUSCARX(SECUENCIA(COINCIDIR(A4;INDICE(R2#;;2);0);;;1);INDICE(R2#;;1);INDICE(R2#;;2)))
Aceptamos, y, tenemos resuelto el problema.
Nos quedaría dar un formato, pero eso lo dejo a vuestra elección.
Miguel Angel Franco Garcia
Comentários