A partir del siguiente modelo de datos, que son ventas de teléfonos en diferentes países, vamos a crear un cuadro, donde aparezcan en horizontal los años, y, en vertical los meses, pero, esto lo vamos a hacer como una matriz dinámica, es decir, que, si aumentan los años, en dicha matriz quedaran reflejados.
Lo primero que vamos a hacer, es como crear los años, en horizontal, pero de manera dinámica.
Todo tiene que salir de una formula.
Lo vamos a realizar paso a paso.
Vamos a usar la función TEXTO, esta función, tiene dos argumentos, que es valor, y, formato, la vamos a usar para extraer el año de la columna fecha, para ello, como argumento valor, usamos la columna fecha, y, como argumento formato, entre comillas dobles, ponemos “AAAA”.
=TEXTO(Fecha;"aaaa")
Obtenemos una matriz, con el mismo numero de filas que la columna fecha, pero solo con los años, esto de momento, no nos vale para mucho, debido a que los años se repiten.
Lo siguiente, va a ser dejar solo los valores únicos, para ello, delante de la formula, vamos a poner la función UNICOS, de la siguiente manera:
=UNICOS(TEXTO(Fecha;"aaaa"))
Ya tenemos los años únicos.
Tenemos que usar la función SECUENCIA, y, debemos de decirle de cuantos números va a estar formada la secuencia, en este caso, será el numero de años, entonces, ¿Cuántos valores son?, pues, a la expresión anterior, después del signo igual, vamos a usar la función CONTARA, esta función nos hace un recuento dentro de un rango de celdas alfanuméricas, la usamos para que nos devuelva el número de valores, lo haremos de la siguiente manera:
=CONTARA(UNICOS(TEXTO(Fecha;"aaaa")))
Y como resultado tenemos le valor 5, que es la cantidad de años que hay.
Pues, ya tenemos el argumento ancho de la función SECUENCIA.
Escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.
=SECUENCIA(
Como argumento filas, ponemos 1 fila, es decir, nuestra matriz desbordada, va a estar compuesta de una fila.
=SECUENCIA(1;
Ahora, viene el argumento columnas, es decir, de cuantas columnas va a estar formada nuestra matriz desbordada, pues lo va a decidir, la expresión CONTARA.
=SECUENCIA(1;CONTARA(UNICOS(TEXTO(Fecha;"aaaa")));
Lo siguiente es el argumento inicio, es decir, donde debe de empezar, pues va a ser el primer año, que es el año 2010.
=SECUENCIA(1;CONTARA(UNICOS(TEXTO(Fecha;"aaaa")));2010;
Como ultimo argumento, es paso, es decir, a partir de 2010, cuanto debe de contar, pues ponemos 1.
Cerramos paréntesis.
=SECUENCIA(1;CONTARA(UNICOS(TEXTO(Fecha;"aaaa")));2010;1)
Es decir, nos va a crear una matriz desbordada, de una fila de alto, en este caso, de 5 columnas, empezando desde 2010, e, incrementando 1, hasta llegar al resultado de la función CONTARA, como he dicho, en este caso 5.
Podemos ver que todo queda en una formula.
Pues ya tenemos los años.
Esta matriz ya es dinámica, prueben a añadir en el modelo de datos, una fecha más, pero del año 2015, verán que ya aparece de forma automática en esta matriz dinámica.
Bien, ahora vamos con los meses, también, por supuesto como matriz dinámica.
Usaremos también la función TEXTO, junto con la función FECHA, para extraer el mes, la función FECHA, tiene tres argumentos, que son año, mes, y, día, como argumento año, usamos la función AÑO, para extraer el año de la fecha actual, como argumento mes, ponemos 1, es decir, enero, y, como argumento día, ponemos 1.
=FECHA(AÑO(HOY());1;1)
Obtenemos la fecha:
Esto solo nos devolverá el mes de enero, no es nuestra matriz dinámica, para que sea dinámica, dentro de la función FECHA, en el argumento mes, vamos a usar la función SECUENCIA, para que nos genere los 12 meses, para ello, en el argumento filas de la función SECUENCIA, ponemos 12, en el argumento columnas, ponemos 1, en el argumento inicio, también ponemos 1, es decir, va a crear una matriz desbordada, de 12 filas, 1 columna, empezando por el número 1, pero, como lo hemos puesto dentro de la función FECHA, en el argumento mes, pues, nos va a devolver una matriz desbordada, de 12 filas, empezando por el día 1 de enero del año actual para la primera celda, el día 1 de febrero del año actual para la siguiente fecha, y, así con el resto.
=FECHA(AÑO(HOY());SECUENCIA(12;1;1);1)
Ahora, englobamos esta expresión, dentro de la función TEXTO, y, como argumento formato, entre comillas dobles, ponemos “MMMM”, para que nos devuelva el nombre del mes.
Ya tenemos nuestra matriz dinámica, lista para ser rellenada.
Si en la celda H7, pongo la siguiente expresión, donde comparo el nombre del mes de la columna fecha con el valor de G7, que es el mes de enero.
=SI(TEXTO(Fecha;"mmmm")=G7;Cantidad))
Me va a devolver una matriz desbordada, con todos los valores vendidos en el mes de enero, pero de todos los años, y, donde no hay coincidencia, nos devuelve el valor da FALSO.
Vamos a poner después del signo igual, la función SUMA, para obtener el total.
Aquí lo tenemos.
Pero, es el total del mes de enero de todos los años, y, debe de aparecer el total de enero, solo del año 2010.
Quiere decir, que debemos de hacer otra pregunta, que será que el año de la columna fecha, debe de ser igual al valor de la celda H6, que es el año 2010.
Podemos concatenar tanto preguntas, como respuestas dentro del condicional SI.
Esto, quiere decir que si a la primera condición, le concateno, con el símbolo de ampersand, otra función TEXTO para extraer el año de la columna fecha, como sigue:
=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"AAAA")
Estamos preguntando, que si el mes de la columna fecha, y, el año de la columna fecha, pues, ahora después del signo igual, en el argumento valor si verdadero, vamos a concatenar el siguiente valor a comparar, que es el año.
=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"AAAA")=G7&H6
Entonces, ahora lo compara con el mes, y, el año.
Por último, en el argumento valor si falso, ponemos, de nuevo, la columna cantidad.
=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"AAAA")=G7&H6;Cantidad))
Aceptamos, y, tenemos una matriz desbordada, con el total del mes de enero del año 2010.
Solo queda copiar la función a todo el rango, hacia abajo, y, hacia la derecha, pero antes tenemos que fijar algunas filas y algunas columnas para que esto funcione bien, debemos de fijar la columna de G7, para que al copiar hacia la derecha, no se actualice, porque los meses, siempre van a estar en la columna G, y, tenemos que fijas la fila de H6, para que no actualice el año al copiar hacia la abajo, porque los años, siempre van a estar en la fila 2, quedando de la siguiente manera:
=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"AAAA")=$G7&H$6;Cantidad))
Nos colocamos en la celda H7, presionamos CTRL más C para copiar, a continuación, seleccionamos todo el rango, y, pulsamos CTRL más V para pegar, y, ya tenemos nuestro cuadro relleno.
Lo siguiente que voy a hacer es añadir la fecha 01/01/2015 al modelo de datos con un importe de 2000.
Vemos que el año aparece en el cuadro.
Ahora, para que se actualicen los datos, nos colocamos en la celda H7, copiamos y volvemos a pegar, pero en este caso, seleccionamos también el año 2015, y, vemos como la cantidad de 2000, ya aparece.
Si añadimos valores, nuevos, o, modificamos los ya existentes, se actualizarán.
También, podríamos añadir una lista desplegable con los países, para ello, creamos una lista única de países con la función UNICOS.
=UNICOS(País)
Creamos una validación de datos con los países, para ello, nos colocamos en la celda, donde vamos a crear la validación, vamos a la pestaña de datos, dentro del grupo nombres definidos, hacemos clic en validación de datos, desplegamos permitir, y, elegimos lista, y, en la ventana origen, seleccionamos los países únicos.
Dejamos seleccionado un país.
Ahora, modificamos la formula, y, añadimos país, como hemos hecho antes, concatenamos la columna de pais en el argumento prueba lógica, y, concatenamos el pais seleccionado en el argumento valor si verdadero.
=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"AAAA")&País=$H7&I$6&$I$21;Cantidad))
Me pongo en la celda I7, copio y pego a todo el rango, como lo hemos hecho anteriormente.
En este caso, tenemos las cantidades vendidas por el país Paraguay.
Podemos cambiar el país, y, veremos que los datos cambian.
Por último, podíamos añadir los días de la semana, como sigue:
Modificamos la formula, y, añadimos el día, de la siguiente manera:
=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"AAAA")&País&TEXTO(Fecha;"dddd")=$H7&I$6&$I$21&I$19;Cantidad))
Ahora, tenemos las cantidades vendidas por mes, año, país, y, día.
Lo siguiente que vamos a realizar, es que vamos a tomar el modelo de datos donde tenemos los datos por años.
Y vamos a crearle unos gráficos.
Lo primero que voy a hacer es crear unos mini gráficos para cada mes del año, para ello, selecciono los valores del mes de enero.
Me coloco en la celda H7, y, clic en la pestaña insertar, y, en el grupo de mini gráficos, elijo de columnas.
Se abre la siguiente ventana.
Seleccionamos el rango que va a componer el mini gráfico.
Aceptamos, y, ya lo tenemos.
Lo siguiente es ir a la pestaña de mini gráficos, y, en el grupo mostrar, marcamos punto alto, para que el valor mas alto quede de otro color.
Quedaría:
Arrastramos hacia abajo.
Ahora, tenemos los años, y, los totales de para cada año.
Vamos a crear unos mini gráficos, pero personalizados, en este caso, dependiendo del valor del año, queremos que aparezcan unas manos con el dedo hacia arriba, pero dependiendo de la cantidad, así será el número de manos.
Para ello, vamos a usar la función REPETIR, la cual repite un numero de veces un carácter, la sintaxis es simplemente el carácter para repetir, y, número de veces a repetir.
Nuestras reglas para este mini grafico van a ser:
· Si el valor es mayor o igual a 15000, o, el valor es menor o igual a 17000, se debe de repetir 2 veces.
· Si el valor es mayor a 17000, o, el valor es menor o igual a 19000, se debe de repetir 4 veces,
· Si el valor es mayor a 19000, se debe de repetir 6 veces.
Para ello, vamos a usar el condicional SI, junto con el operador lógico Y, y, la función REPETIR.
Para usar el carácter de la mano, debemos de hacerlo de la siguiente manera, si ponemos el signo igual, la función REPETIR, abrimos paréntesis, y, a continuación, pulsamos la tecla de Windows junto con el punto, se abre la siguiente ventana:
En nuestro caso, vamos a seleccionar la mano con el dedo hacia arriba, y, por ejemplo, numero de repeticiones va a ser 3.
El símbolo, debe de ir entre comillas.
=REPETIR("👍";3)
Esta función, la vamos a poner dentro del condicional SI, quedando de la siguiente manera:
=SI(Y(H22>=15000;H22<=17000);REPETIR("👍";2);SI(Y(H22>17000;H22<=19000);REPETIR("👍";4);SI(H22>19000;REPETIR("👍";6))))
De esta manera, cumple con las reglas que habíamos citado.
Quedaría:
Lo siguiente va a ser crear un grafico de personas, este tipo de grafico es un complemento, el cual si no lo tenemos instalado, debemos de hacerlo.
Para ello, vamos a la pestaña insertar – obtener complementos.
Se abre la siguiente ventana:
En buscar, vamos a poner la palabra “persona”, y, clic en la lupa.
Aparece la siguiente ventana donde está el complemento.
Simplemente, tendríamos que hacer clic en agregar, en mi caso no, porque ya lo tengo instalado.
Bueno, pues vamos a crear ese grafico de personas.
Clic en insertar – mis complementos, se abre la siguiente ventana.
Seleccionamos el grafico de personas.
Aparece la siguiente imagen.
Si hacemos clic en cualquier parte del gráfico, podemos ver que se habilitan dos pestañas.
Si hacemos clic en el icono que es una hoja de cálculo, se abre la siguiente ventana:
Donde debemos de seleccionar los datos que van a componer nuestro grafico de personas.
Para seleccionar los datos, debemos de seleccionar tanto los encabezados como los datos.
Clic en cerrar.
Este es el resultado.
Tanto podemos cambiar el nombre, o título, si volvemos a hacer clic en el icono de una hoja de cálculo, lo haremos:
Si hacemos clic en la rueda dentada, podemos cambiar el formato, por ejemplo, vamos a cambiar el icono de una persona por otro.
Para ello, hacemos clic en forma, y, voy a elegir el ordenador.
También, podía cambiarle el fondo en la pestaña tema.
Y, también, podría cambiarle el tipo.
Y este sería el resultado final.
Por último, vamos a crear un gráfico de columnas, también para mostrar los valores.
Para ello, seleccionamos los valores.
Vamos a la pestaña insertar – gráficos – columnas.
El título, líneas de división, y, eje vertical lo quitamos.
En la pestaña de diseño de gráfico, clic en seleccionar datos, se abre la siguiente ventana.
Clic en editar, de etiquetas del eje horizontal.
En rango de rótulos del eje, seleccionamos los años.
Aceptamos.
Con el botón alternativo del ratón en uno de los laterales del gráfico, elegimos, formato del área del gráfico.
En la ventana de la derecha, en relleno, elegimos relleno sólido, y, elegimos un color.
Clic en una columna, y, también le cambiamos el color a las columnas.
Botón alternativo de ratón en una columna, y, elegimos añadir etiquetas de datos, y, observaremos que no se ven.
Volvemos a botón alternativo de ratón en una columna, y, elegimos formato de etiquetas de datos, las cuales quedaran señaladas.
Vamos a la pestaña de inicio, y, cambiamos color y tamaño.
Ahora, en la ventana de la derecha, elegimos número, y, seleccionamos moneda, y, sin decimales.
Seleccionamos el eje horizontal, y, también cambiamos tamaño, y, color.
Clic en uno de los laterales del gráfico, y, ponemos las esquinas redondeadas.
Ya tendríamos nuestro gráfico, pero con otra imagen.
Con esto, datos por terminado este ejemplo.
Comments