Este ejemplo, va a consistir en crear un gráfico dinámico, a través de una matriz dinamina creada previamente, en esa matriz, van a aparecer solo los países que elijamos a través de una validación de datos, y, debajo de ellos, aparecerán las ventas realizadas por esos países, y, el promedio de los países seleccionados.
Bien, tenemos el siguiente modelo de datos, en formato de tabla.
El listado es algo más grande.
Vamos a crear una lista de países únicos, para ello, vamos a usar la función UNICOS, y, ORDENAR, para que los países estén ordenados.
Con la función UNICOS, obtenemos una matriz desbordada con los países únicos.
=UNICOS(País)
Usamos la función ORDENAR, con los argumentos predeterminados, para ordenar los países.
Lo siguiente va a ser ir a una hoja en blanco, donde vamos a crear nuestro grafico dinámico.
Lo primero que vamos a hacer es crear de forma dinámica la impresión de los países, para ello, vamos a empezar poniendo lo siguiente:
Si utilizo la función CONTARA, para saber el número de países que hay, seria:
=CONTARA(DATOS!I2#)
Si nos damos cuenta, al seleccionar la matriz completa, en vez de poner de I2 a I25, pone el operador de rango derramado2#, que quiere decir que seleccione desde la fila 2 hasta la ultima celda con datos, nos devuelve el valor 24, es decir, hay 24 países, también, podemos poner la referencia I2, y, manualmente, poner el operador de rango derramado (#).
Pero, la idea no es poner todos los países, sino los países que nosotros queramos, por lo que la función CONTARA no nos va a valer.
Si usamos la función INDICE de la siguiente manera, donde como argumento matriz, seleccionamos la lista de países únicos, como argumento número de fila, ponemos 1, y, como argumento número de columna, ponemos también 1, aunque, podemos omitir el argumento número de columna, y, de forma predeterminada pondrá 1, obtendremos el primer país, que es Alemania.
=INDICE(DATOS!I2#;1;1)
Si en el argumento número de fila de la función INDICE, en vez de poner 1, usamos la función SECUECIA, donde le decimos que genere una matriz desbordada, de una fila, cinco columnas, empezando por el número 1, que vaya incrementando 1, hasta 5, pues, tendremos los cinco primeros países.
=INDICE(DATOS!I2#;SECUENCIA(1;5;1;1);1)
Lo siguiente que vamos a hacer es crear una validación de datos (lista desplegable), compuesta desde el numero 1 hasta el numero 10, que van a ser los países que vamos a poder elegir.
Para ello, vamos al a pestaña datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Se abre la ventana de validación de datos.
Desplegamos permitir, y, seleccionamos lista.
En la ventana origen escribimos los valores desde el 1 hasta el 10, separados por punto y coma.
Aceptamos, y, ya tenemos nuestra lista de valores.
Lo siguiente, va a ser es volver a la función, y, en la función SECUENCIA, en el argumento columna de la función SECUENCIA, vamos a poner el valor que haya seleccionado en la validación de datos, de la siguiente manera:
=INDICE(DATOS!I2#;SECUENCIA(1;C2;1;1);1)
Bien, si ahora cambiamos el valor de la lista creada, veremos que solo quedan el numero de países indicado en la validación de datos.
Con esto ya tenemos la lista de países creadas.
Lo siguiente va a ser calcular las cantidades vendidas por los países seleccionados, para ello, vamos a usar la función SUMAR.SI, donde como argumento rango de criterios, usamos la columna de pais, para que seleccione la columna de los países, como criterio, señalamos la celda B4, junto con el operador de rango derramado (#), para que seleccione desde la celda B4 hasta la última celda ocupada, en este caso, seleccionara los países, que están en columnas, y, como argumento rango de suma, ponemos la columna de cantidad, quedaría de la siguiente manera:
=SUMAR.SI(País;B4#;Cantidad)
El resultado:
Que ocurre, si cambiamos el número de países.
Pues, que, de forma automática, se añaden las cantidades vendidas por esos países nuevos.
Lo siguiente, va a ser crear los porcentajes de cada país seleccionado, respecto, al total de todos los países, para ello, debemos de dividir desde B5 hasta la última columna, por lo que usaremos el operador de rango derramado (#), y, lo dividimos entre la suma de todos los valores de los países seleccionados, donde usaremos la función SUMA.
=B5#/SUMA(B5#)
Si nos damos cuenta, estamos usando funciones que devuelven matrices dinámicas, también, podemos observar que el tiempo de formulación es más rápido.
Como resultado, tenemos la diferencia porcentual de cada pais, respecto al total.
Podemos comprobar que, si cambiamos el valor de la validación de datos, los datos se actualizan de forma automática.
Lo siguiente va a ser crear nuestro gráfico, en blanco, es decir, sin datos, para ello, vamos a usar un gráfico de columnas agrupadas.
Vamos a la ficha insertar, dentro del grupo gráficos, inserto el grafico de columna agrupadas, de momento, sin datos.
¿Por qué el grafico en blanco?
Si seleccionamos los datos de la matriz, seria un gráfico, que seria correcto, pero que no sería dinámico, siempre presentaría los mismos datos, y, lo que nosotros queremos es que cuando seleccionemos los países, esos sean los países para graficar, para ello, debemos de hacer uso, en mi caso, de la función DESREF.
La función DESREF, lo que hace es que a partir de una celda inicial, nos podemos ver un numero de filas, y, columnas que le indiquemos.
La función DESREF, tiene 4 argumentos.
· Ref, es la celda de donde vamos a empezar.
· Filas, numero de filas a bajar.
· Columnas, numero de columnas a movernos, si el valor es positivo, nos movemos hacia la derecha, y, si es negativo hacia la izquierda.
· Alto, numero de filas a tomar.
· Ancho, numero de columnas a tomar.
Por ejemplo, si estando en la celda A2, escribo la siguiente función, donde como argumento filas, ponemos 2,como argumento columnas, ponemos 1, como alto, vamos a poner 2, y, como argumento ancho, pues, ponemos 2.
=DESREF(A2;2;1;2;2)
Me genera una matriz, de la siguiente manera:
Es decir, desde A2, baja dos filas, se queda en la misma columna, ahora, toma un alto de dos filas, por eso, toma los valores de 8, y, 9, y un ancho, también, de dos, de ahí que tome los valores de 13 y 14.
Pero si el resultado que queremos es que sea una suma, ponemos delante la función SUMA.
=SUMA(DESREF(A2;2;1;2;2))
Y el resultado:
Que es la suma de 8, 9, 13 y 14.
Una vez que ya sabemos para que vale la función DESREF, ya podemos entrar de lleno en nuestro grafico dinámico.
Lo primero que vamos a ser es crear un nombre de rango, se va a llamar columnas_dinámicas, y, con la función DESREF, vamos a conseguir que solo queden seleccionados los países que hayamos seleccionado en la validación de datos.
Pero, antes de crear el nombre de rango, voy a crearlo en una celda, para ver que funciona correctamente.
Escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.
=DESREF(
Como celda de partida, es la celda B6.
=DESREF(Resuelto!B6;
Nos movemos cero filas, y, cero columnas, por lo que nos quedamos en la celda B6.
=DESREF(Resuelto!B6;0;0;
El argumento alto lo vamos a omitir, por que lo que necesitamos es el ancho, ya que los países se encuentran en columnas, por lo ponemos punto y coma, para pasar al argumento ancho, donde seleccionamos la celda C3, que es quien indica el número de países.
=DESREF(Resuelto!B6;0;0;;Resuelto!C3)
Aceptamos.
Vemos que tenemos una matriz desbordada con los valores de los países seleccionados.
Si cambiamos el numero de países, veremos como se actualizan los valores.
Lo que tenemos que hacer ahora, es seleccionar la función, y, pulsar CTRL más C para copiar, pero antes, vamos a fijar las referencias para que no se actualicen al copiar.
=DESREF(Resuelto!$B$6;0;0;;Resuelto!$C$3)
Ahora, vamos a la pestaña formulas, dentro del grupo nombres definidos, elegimos asignar nombre.
Se abre la ventana nombre nuevo, como nombre, ponemos, columnas dinámicas , y, en la ventana se refiere a, pulsamos CTRL mas V para pegar.
Aceptamos.
Es importante, delante poner el numero de hoja, esta función significa que tomara los datos de dicha RESUELTO, esto es cuando tenemos más de una hoja.
Lo siguiente será crear otra etiqueta dinámica, la podemos llamar, paises_dinámicos, pero en este caso, para que se seleccionen los países seleccionados, la formula a usar es la misma que la anterior, con la excepción de que tenemos que cambiar el numero de fila, la formula quedaría:
Aceptamos.
Ya tenemos creada nuestras etiquetas dinámicas.
Vamos a seleccionar el gráfico, vemos que se habilitan las pestañas de diseño de gráfico, y, formato, dentro de la pestaña diseño de gráfico, hacemos clic en seleccionar datos.
Se abre la ventana seleccionar origen de datos, pues, hacemos clic en agregar.
Se abre la ventana modificar serie.
En valores de la serie, vamos a poner el nombre de rango columnas_dinamicas, decir, que cuando vamos a usar un nombre de rango dentro de un rango, debemos de especificar el nombre del archivo, mas la extensión, y, el signo de exclamación.
En caso, de no acordarnos de que nombre de rango, podemos pulsar la tecla de F3, y, se abrirá una ventana donde aparecerán todos los nombres de rangos creados, seleccionamos el que queremos y aceptamos.
=GRAFICO_DINAMICO.xlsx!columnas_dinamicas
Aceptar.
Vemos que aparecen seis columnas, que equivale a los valores de los seis países que hemos seleccionado, si cambiamos, si ponemos, por ejemplo, 4, el resultado sería:
Lo siguiente va a ser que aparezcan los nombres de los países seleccionados en la leyenda horizontal, para ello, volvemos al grupo diseño de gráfico, y, hacemos clic en seleccionar datos.
Se abre la ventana seleccionar origen de datos.
En etiquetas del eje horizontal, vamos a poner los nombres de los países seleccionados.
Hacemos clic en editar.
Se abre la ventana rótulos del eje, y, en rango de rotulo del eje, ponemos la siguiente expresión, es decir, el nombre de rango paises_dinamicos, recordar, que debemos de poner el nombre del libro, seguido de la extensión, y, el signo de exclamación.
=GRAFICO_DINAMICO.xlsx!paises_dinamicos
Aceptamos, y, dentro de la ventana etiquetas del eje horizontal, podemos ver los nombres de los países.
Aceptar.
Vemos el resultado:
Ya aparece el país en cada columna.
Bien, ya tenemos nuestro grafico dinámico, a partir de una matriz dinámica, lo que vamos a ser ahora, es crear, o, calcular el promedio, lo haremos con la siguiente expresión, donde vamos a usar el condicional SI, donde preguntamos, que si el valor de la celda B6, es distinta a blanco, entonces, que calcule el promedio desde B6 hasta la última celda ocupada, donde usamos el operador de rango derramado (#), en caso contrario, que ponga un texto en blanco.
=SI(B6<>"";PROMEDIO(B6#);"")
Con esta formula conseguimos que solo ponga el promedio en aquellas celdas que no estén vacías, por lo que la hacemos dinámica.
También, debemos de fijas la columna B dentro de la función PROMEDIO, para que al copiar hacia la derecha, no se actualice.
=SI(B6<>"";PROMEDIO($B6#);"")
Lo hacemos con el condicional SI, por si hubiera países que no tuviera ventas, porque si no lo hacemos de esta manera, el calculo de la media aritmética no seria correcta.
Como resultado, vemos debajo de la cantidad de cada pais, el promedio, que es el mismo para cada pais.
Ahora, vamos a añadir ese promedio al gráfico, para que veamos de un simple vistazo, cuales son aquellos países que han sobrepasado el promedio o no, por lo que debemos de crear otro nombre de rango, con la expresión anterior, para ello, seleccionamos la expresión, pulsamos CTRL mas C para copiar, vamos a la pestaña de fórmulas, dentro del grupo nombres definidos, hacemos clic en asignar nombre.
Como nombre, ponemos, promedio, en la ventana se refiere a, tenemos que usar la misma función DESREF, usada anteriormente, lo único que debemos de cambiar es la celda de partida, que en este caso, es la celda B8, la celda de los promedios.
=DESREF(Resuelto!$B$8;0;0;;Resuelto!$C$3)
Aceptamos.
Vamos a la pestaña diseño de gráfico, y, hacemos clic en seleccionar datos.
Se abre la ventana seleccionar origen de datos, pues, hacemos clic en agregar.
Se abre la ventana modificar serie, pues, en valores de la serie, ponemos el nombre del libro, seguido de la extensión, ponemos el signo de exclamación, y, el nombre de rango promedio.
Aceptamos, y, como resultado, vemos el promedio añadido, pero como grafico de columnas agrupadas.
La columna naranja es el promedio, la vamos a cambiar por una línea, para ello, hacemos clic con botón alternativo de ratón dentro de una columna, y, seleccionamos cambiar tipo de gráfico de series.
Se abre la siguiente ventana cambiar tipo de gráfico.
Hacemos clic en combinado.
En la ventana elija el tipo de grafico y el eje para la serie de datos, cambiamos la serie2, a un grafico de líneas.
Aceptamos, y, podemos ver el promedio como una línea horizontal del mismo tamaño.
Aceptamos, y, ya tenemos también el promedio dinámico.
Bien, pues con esto, ya tenemos nuestro ejemplo realizado.
Comments