Vamos a trabajar con el modelo de contoso.
Tenemos una hoja llamada ventas, donde tenemos los ID, como IDtienda, IDproducto entre otros.
Nos han encargado que hagamos una lista con los nombres de los canales, y, debemos de filtrar por el nombre de dicho canal.
El problema que se presenta es que en la hoja ventas, no tenemos el nombre del canal sino su ID, y, el cliente no quiere que toquemos el modelo, es decir, que no añadamos columnas, ni hojas, etc.
Debemos de trabajar tal cual está el modelo.
Este ejercicio lo haremos en una hoja nueva.
Vamos a crear un desplegable para seleccionar un canal.
Nos colocamos, por ejemplo, en la celda B1, vamos a la pestaña datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Desplegamos permitir, y, seleccionamos lista.
Hacemos clic en el siguiente icono.
Seleccionamos los canales.
Aceptamos.
A mi se me ocurre buscar el IDCanal de la hoja ventas, buscarlo en la hoja de canal, y, que me devuelva el nombre de dicho canal.
Como el valor a rescatar es el nombre del canal, y, está en la derecha, no puedo usar la función BUSCARV porque ignora todas las columnas que haya a la izquierda a partir de la columna donde buscamos el valor, por lo que voy a usar la función BUSCARX.
Como argumento valor buscado, es el valor de la celda B1, el argumento matriz de búsqueda es desde B2 a B5 de la hoja canal, y, como argumento matriz devuelta, es desde A2 a A5 de la hoja canal.
=BUSCARX(B1;Canal!B2:B5;Canal!A2:A5)
Ya tenemos el id a que corresponde el canal que hemos seleccionado.
Ahora, podemos usar la función FILTRAR, donde filtro la columna fechapedido, y, en el argumento include, comparo la columna IDCanal con el resultado de la función BUSCARX.
FILTRAR(Tabla1[FechaPedido];Tabla1[IDCanal]=BUSCARX(A1;Canal!B:B;Canal!A:A)))
Aceptamos.
Vemos que ya tenemos filtrado las fechas del modelo por el canal que hemos seleccionado.
Ponemos la columna de fecha en formato de fecha corta.
Vamos a dejar las fechas únicas, para ello, usamos la función UNICOS.
=UNICOS(FILTRAR(Tabla1[FechaPedido];Tabla1[IDCanal]=BUSCARX(A1;Canal!B:B;Canal!A:A)))
Ordenamos, con los argumentos por defecto.
=ORDENAR(UNICOS(FILTRAR(Tabla1[FechaPedido];Tabla1[IDCanal]=BUSCARX(A1;Canal!B:B;Canal!A:A))))
Ahora nos vamos a quedar solo con el nombre del mes y el año, para ello, voy a hacer uso de la función TEXTO.
Esta función tiene dos argumentos, que es valor, y formato, pues el argumento valor es la función que tenemos, y, el argumento formato, ponemos entre comillas el formato de la fecha, es decir, “mmmm\aaaa”, quiere decir, que debe de poner el nombre del mes y del año.
=TEXTO(ORDENAR(UNICOS(FILTRAR(Tabla1[FechaPedido];Tabla1[IDCanal]=BUSCARX(A1;Canal!B:B;Canal!A:A))));"mmmm\aaaa")
Ya tenemos lo que buscamos.
Pero claro, los meses de repiten, pues, después del signo igual, usamos la función UNICOS, para quitar los duplicados.
=UNICOS(TEXTO(ORDENAR(UNICOS(FILTRAR(Tabla1[FechaPedido];Tabla1[IDCanal]=BUSCARX(A1;Canal!B:B;Canal!A:A))));"mmmm/aaaa"))
Ya tenemos desde enero a diciembre para cada año.
Ahora, vamos a calcular el total para cada mes de cada año.
Vamos a volver a usar la función FILTRAR.
Como argumento array, señalamos la columna de total, pero vamos a escribir los argumentos.
=FILTRAR(Tabla1[Total];
Abrimos un paréntesis para poner la primera condición del argumento include, donde vamos a usar la función AÑO para extraer el año de la columna fechapedido y lo comparamos con el año de la primera fecha obtenida, y cerramos paréntesis.
=FILTRAR(Tabla1[Total];(AÑO(Tabla1[FechaPedido])=AÑO(C3)
Ponemos el símbolo de asterisco para poner la siguiente condición, y, abrimos un paréntesis, donde vamos a comparar cada mes con la función MES de la columna fechapedido con el primer mes de la matriz obtenida.
Debemos de seleccionar la celda que contiene el primer mes, no la matriz desbordada, en ese caso, nos devolverá error.
=FILTRAR(Tabla1[Total];(AÑO(Tabla1[FechaPedido])=A4)*(MES(Tabla1[FechaPedido])=MES(C3)))
Aceptamos.
Sumamos todos los resultados.
=SUMA(FILTRAR(Tabla1[Total];(AÑO(Tabla1[FechaPedido])=AÑO(C3)*(MES(Tabla1[FechaPedido])=MES(C3)))))
Vemos el total para el mes de enero del año 2019.
Arrastramos, y, tenemos los totales para cada mes de cada año.
Pero tenemos los totales para todos los canales, porque aun no hemos filtrado por canal.
Por lo que a la expresión anterior, le vamos a añadir la función BUSCARX, es decir, vamos a comparar la expresión anterior con el resultado de BUSCARX.
=SUMA(FILTRAR(Tabla1[Total];(AÑO(Tabla1[FechaPedido])=AÑO(C3)*(MES(Tabla1[FechaPedido])=MES(C3)*(Tabla1[IDCanal]=BUSCARX($B$1;Canal!B:B;Canal!A:A))))))
Ahora tenemos los totales para el canal seleccionado.
Ahora vamos a sacar los totales por trimestres pero para cada año.
Voy a usar de nuevo la función FILTRAR.
Como argumento array, seleccionamos los valores.
=FILTRAR($M$5:$M$40;
Tenemos que especificar 4 condiciones, que es el año, y, los números de trimestres.
Si para realizar esta operación usásemos los operadores lógicos Y/O, usaría el operador lógico O, para preguntar si el numero de mes es 1, 2, o, 3.
Después usaría el operador lógico Y, para preguntar si el año es igual a 2019, que sería la primera condición, y, la segunda condición es una de las coincidencias que ha encontrado el operador lógico O, quiere decir, que el operador lógico O es la segunda condición del operador lógico Y.
Hagámoslo en una celda.
De momento, uso el operador lógico O, para preguntar si el mes de la primera fecha es 1,2, o, 3.
=O(MES(C4)=1;MES(C4)=2;MES(C4)=3)
Me devuelve VERDADERO, porque una de las condiciones se cumple, que es el mes de enero, o sea, el mes 1.
Ahora, debo de preguntar que el año sea 2019, que es el primer año.
Tengo que usar el operador lógico Y, debido a que se debe de cumplir tanto el año como el mes.
Entones quedaría:
=Y(AÑO(C4)=2019;O(MES(C4)=1;MES(C4)=2;MES(C4)=3))
Me vuelve a devolver VERDADERO, porque el operador lógico O, devuelve VERDADERO, y, el año, es cierto, que es 2019, por lo que ambas condiciones se cumplen, y el operador lógico Y, solo va a devolver VERDADERO, si todas las condiciones se cumplen.
Pues así debemos de especificarlo dentro de la función FILTRAR.
La sintaxis quedaría usando de momento el operador lógico O, que en la función FILTRAR en el símbolo de más (+).
=FILTRAR(D4:D39;(MES(C4#)=1)+(MES(C4#)=2)+(MES(C4#)=3)
Ahora, vamos a añadir el primer año.
=FILTRAR(D4:D39;(AÑO(C4#)=F4)*((MES(C4#)=1)+(MES(C4#)=2)+(MES(C4#)=3)))
Aceptamos y tenemos una matriz desbordada con los valores de los tres primeros meses del primer año.
Pero como queremos el total usamos la función SUMA.
=SUMA(FILTRAR(D4:D39;(AÑO(C4#)=F4)*((MES(C4#)=1)+(MES(C4#)=2)+(MES(C4#)=3))))
Fijamos todas las referencias, excepto, la referencia F4, que solo debemos de fijar la columna, para, que cuando copiemos hacia la derecha no se actualice, en caso contrario, no tomaría el año.
=SUMA(FILTRAR($D$4:$D$39;(AÑO($C$4#)=$F4)*((MES($C$4#)=1)+(MES($C$4#)=2)+(MES($C$4#)=3))))
Copiamos hacia la derecha.
Cambiamos los números de meses.
Arrastramos hacia abajo.
Ya tenemos los totales por trimestres.
Ahora, vamos a calcular la diferencia porcentual de cada año respecto al total.
Para ello, dividimos el total de cada año entre el total de todos los años.
=SUMA(G4:J4)/SUMA(G4:J6)
Antes de arrastrar, debemos de fijar el rango G4:J6, de la función SUMA, ya que al arrastrar no se deben de actualizar, para que siempre tome los mismos valores.
=SUMA(G4:J4)/SUMA($G$4:$J$6)
Ya lo tenemos.
Por último, graficamos los años, y, el porcentaje, donde tenemos que usar el eje secundario.
Pero antes, lo que voy a hacer es calcular los totales por año, para ello, selecciono el siguiente rango.
Presión ALT más Igual, que es el atajo de auto suma.
Ya tenemos los totales.
Seleccionamos dichos totales.
Insertamos un gráfico de columnas 2D. desde la pestaña insertar, y, dentro del grupo gráficos.
Quitamos líneas de división, para ello, seleccionamos el grafico, hacemos clic en el símbolo mas (+) en la parte superior derecha del gráfico, y, desmarcamos, líneas de cuadricula.
Quitamos el título, para ello, simplemente lo seleccionamos, y, pulsamos la tecla de suprimir.
Vamos a graficar los porcentajes.
Seleccionamos el grafico, se habilitan las pestañas de diseño de gráfico, y, formato.
Dentro de diseño de gráfico, hacemos clic en seleccionar datos.
Se abre la ventana de seleccionar origen de datos, pues, hacemos clic en agregar.
Se abre la ventana modificar serie.
Hacemos clic en el siguiente icono.
Señalamos la celda donde pone porcentaje, y, es el título de la serie.
Hacemos clic en el siguiente icono.
Señalamos los valores de porcentajes, y, hacemos clic en el siguiente icono.
Aceptamos.
Vuelve a la ventana seleccionar origen de datos, pues, aceptamos.
Si nos fijamos en el gráfico, parece que no ha sido graficado nada.
Pero, si lo ha sido, lo que ocurre, es que los datos de porcentajes son tan pequeños, respecto al resto de datos, que apenas se ven, si nos damos cuenta, esta el sitio del porcentaje, con una columna mínima, que apenas se ve.
Por supuesto, vamos a verlo.
Para ello, con el botón alternativo del ratón, dentro de una columna, seleccionamos cambiar tipo de gráfico de series.
Elegimos combinado, en la ventana elija tipo de grafico y el eje para la serie de datos, vemos el tipo de grafico que tenemos seleccionado, siendo todos de columnas agrupadas.
Lo primero, que vamos a hacer, es cambiar el tipo de grafico para porcentajes, para ello, hacemos clic el siguiente icono.
Elegimos grafico de líneas.
Vemos que ya ha sido cambiado.
Pero, esto no hará que se vea, si nos fijamos en la derecha, en la parte superior, pone eje secundario, esto quiere decir, que agregara un nuevo eje de valores a la derecha, con los datos que le digamos, en este caso, de los porcentajes, por lo que marcamos la casilla de eje secundario.
En la parte central, tenemos una ventana, llamada combinación personalizada, donde vemos una vista previa, y, podemos ver como ahora si se ven los porcentajes.
Aceptamos, y, ya lo tenemos.
Pero, vemos que no pone los nombres de los años, cosa que es correcta, porque no se lo hemos indicado, pone 1, 2, y, 3.
Pues vamos a cambiarlo.
Hacemos clic en seleccionar datos.
Vemos de donde salen estos valores.
Pues, hacemos clic en editar.
Se abre la ventana rótulos del eje, pues hacemos clic en el siguiente icono.
Señalamos los valores que van a componer las categorías.
Aceptamos.
Aceptamos, de nuevo.
Ya tenemos los nombres de los años.
Vamos a bajar un poco la línea de los porcentajes.
Para ello, hacemos doble clic sobre el eje vertical del eje secundario.
A la derecha, se abre la ventana de dar formato a eje.
Hacemos clic en el siguiente icono.
Dentro de opciones de eje, vemos que como mínimo pone 0, y, como máximo pone 0,4.
Pues, voy a poner como máximo 0,5.
Podemos ver cómo ha bajado.
Por último, vamos a añadirle líneas de unión, estas líneas van desde cada punto del grafico de líneas, hasta cada categoría.
Para ello, vamos a la pestaña, teniendo el grafico de líneas seleccionado, diseño de gráfico, hay un grupo llamado diseños de gráfico, pues desplegamos agregar elemento de gráfico, se abre una ventana con una serie de opciones, nos movemos hasta líneas, se despliega a la derecha un menú, donde elegimos líneas de unión.
Ya lo tenemos.
Hacemos doble clic sobre una de las líneas de unión, elegimos línea sólida, le damos un color negro, y, cambiamos el ancho.
Quitamos el borde al gráfico.
Quitamos líneas de cuadricula.
Con esto, damos por finalizado nuestro ejemplo.
Comments