En este ejemplo, vamos a totalizar por centro comercial, y, por año.
Vamos a trabajar con el modelo que uso a menudo, que es ventas de productos, en diferentes centros comerciales, provincias, y, fechas.
Este modelo está en formato de tabla, y, tienen como nombre datos, ya sabemos, que al estar en formato de tabla, si añadimos datos nuevos, se añadirán de forma automática.
En el modelo tenemos la columna cantidad, precio, total.
Queremos elegir un encabezado, y, ver los totales para cada centro comercial, y, año por el encabezado seleccionado.
Ya tenemos el modelo preparado.
En A1 vamos a poner como título ELEMENTO.
En B1, vamos a crear un desplegable con el nombre de cada encabezado, para ello, vamos a la pestaña de 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, vamos a escribir los valores, separados por punto y coma.
Voy a usar la función SUMAR.SI.CONJUNTO, esta función nos devolverá un total que cumplan con la o las condiciones, que le indiquemos, para calcular el total para cada centro comercial, y, año, lo haremos, de forma matricial, ya sabemos que una función matricial es aquella que devuelve más de un valor.
Escribimos el signo igual, seguido de la función, y, abrimos un paréntesis.
=SUMAR.SI.CONJUNTO(
El primer argumento es rango de suma, pues seleccionamos la columna de total.
=SUMAR.SI.CONJUNTO(DATOS!H2:H1475
Punto y coma, el siguiente argumento es rango de criterios 1, que son los centros comerciales.
=SUMAR.SI.CONJUNTO(DATOS!H2:H1475;DATOS!D2:D1475
Punto y coma, el siguiente argumento es criterios1, pues seleccionamos la lista única de centros, al seleccionar más de un valor, obligamos a la función a devolver más de un valor, lo que la convierte en matricial.
=SUMAR.SI.CONJUNTO(DATOS!H2:H1475;DATOS!D2:D1475;Hoja3!A6:A11
Punto y coma, le siguiente argumento es rango de criterios 2, pues seleccionamos la columna de los años.
=SUMAR.SI.CONJUNTO(DATOS!H2:H1475;DATOS!D2:D1475;Hoja3!A6:A11;DATOS!B2:B1475
Y, como criterios2, seleccionamos los años, cerramos paréntesis, y, aceptamos.
=SUMAR.SI.CONJUNTO(DATOS!H2:H1475;DATOS!D2:D1475;Hoja3!A6:A11;DATOS!B2:B1475;Hoja3!B5:F5)
Tenemos los totales por centros y años.
Pero, lo que queremos es ver este total, no solo por la columna total, sino por cantidad, o, precio.
Ya tenemos hecha la validación de datos para seleccionar un encabezado.
De alguna manera, tenemos que modificar la expresión, para que nos devuelva los totales por el encabezado seleccionado.
A mí se me ocurre, usar la función LET.
Sabemos que podemos definir varias variables y asignarle un valor a la función LET.
· El rango de cantidad va desde F2 a F1475.
· El rango de precio va desde G2 a G1475.
· El rango de total va desde H2 a H1475.
Por lo que puedo usar la función LET para crear tres variables, y, a cada variable le asigno un rango.
Para ello, escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.
=LET(
Vamos a crear una primera variable, la voy a llamar “a”.
=LET(a;
como argumento, calculo, seleccionamos el rango de cantidad.
=LET (a; DATOS! F2:F1476;
Creamos una segunda variable, la llamamos “b”.
=LET (a;DATOS!$F$2:$F$1476;b;
Le asignamos el rango de precios.
=LET(a;DATOS!$F$2:$F$1476;b;DATOS!$G$2:$G$1476;
Creamos una tercera variable, la llamamos, “c”.
=LET(a;DATOS!$F$2:$F$1476;b;DATOS!$G$2:$G$1476;c;
Le asignamos, el rango de los totales.
=LET(a;DATOS!$F$2:$F$1476;b;DATOS!$G$2:$G$1476;c;DATOS!$H$2:$H$1476;
Como argumento, calculo, vamos a preguntar, que si B1 es igual a cantidad, que ponga el valor de la variable A, si es igual a precio, que ponga la variable B, y, si es igual a total, que ponga la variable C, esta pregunta la haremos, con el condicional SI.
=LET(a;DATOS!$F$2:$F$1476;b;DATOS!$G$2:$G$1476;c;DATOS!$H$2:$H$1476;SI(B1="Cantidad";a;SI(B1="Precio";b;c)))
Aceptamos.
Pues, este va a ser el argumento rango de suma de la función SUMAR.SI.CONJUNTO.
=SUMAR.SI.CONJUNTO(LET(a;DATOS!$F$2:$F$1476;b;DATOS!$G$2:$G$1476;c;DATOS!$H$2:$H$1476;SI(B1="Cantidad";a;SI(B1="Precio";b;c)));DATOS!$D$2:$D$1476;RESULTADOS!A6:A11;DATOS!$B$2:$B$1476;RESULTADOS!B5:F5)
El argumento, rango de criterios1, va a ser la columna de los centros comerciales, de la hoja datos.
=SUMAR.SI.CONJUNTO(LET(a;DATOS!$F$2:$F$1476;b;DATOS!$G$2:$G$1476;c;DATOS!$H$2:$H$1476;SI(B1="Cantidad";a;SI(B1="Precio";b;c)));DATOS!$D$2:$D$1476;RESULTADOS!A6:A11;DATOS!$B$2:$B$1476;RESULTADOS!B5:F5)
Como argumento, criterios1, va a ser desde A6 hasta A11, de la hoja RESULTADOS, es decir, los centros comerciales.
=SUMAR.SI.CONJUNTO(LET(a;DATOS!$F$2:$F$1476;b;DATOS!$G$2:$G$1476;c;DATOS!$H$2:$H$1476;SI(B1="Cantidad";a;SI(B1="Precio";b;c)));DATOS!$D$2:$D$1476;RESULTADOS!A6:A11;DATOS!$B$2:$B$1476;RESULTADOS!B5:F5)
Como argumento, rango de criterios2, va a ser la columna de los años.
=SUMAR.SI.CONJUNTO(LET(a;DATOS!$F$2:$F$1476;b;DATOS!$G$2:$G$1476;c;DATOS!$H$2:$H$1476;SI(B1="Cantidad";a;SI(B1="Precio";b;c)));DATOS!$D$2:$D$1476;RESULTADOS!A6:A11;DATOS!$B$2:$B$1476;RESULTADOS!B5:F5)
Y, como argumento, criterios2, va a ser desde B5 hasta F5, de la hoja RESULTADOS, es decir, los años.
=SUMAR.SI.CONJUNTO(LET(a;DATOS!$F$2:$F$1476;b;DATOS!$G$2:$G$1476;c;DATOS!$H$2:$H$1476;SI(B1="Cantidad";a;SI(B1="Precio";b;c)));DATOS!$D$2:$D$1476;RESULTADOS!A6:A11;DATOS!$B$2:$B$1476;RESULTADOS!B5:F5)
Aceptamos, y, tenemos los totales, por el encabezado seleccionado.
Lo siguiente, va a ser calcular los totales por años, pero ,en este caso, vamos a hacer uso de la función BYCOL, donde vamos a seleccionar un rango, y, va a tratar a cada columna de forma independiente.
Escribimos el signo igual seguido de la función BYCOL, y, abrimos un paréntesis.
=BYCOL(
El primer argumento es, array, pues seleccionamos todos los valores, que empiezan a partir de la celda B6, podemos seleccionar los datos con el ratón, o, poner la referencia B6, y, añadirle el operador de rango derramado (#), y se seleccionaran todos los datos, si seleccionamos el rango con el ratón, también, pondrá el operador de rango derramado (#).
=BYCOL(B6#
El siguiente argumento es, función, donde ponemos la función LAMBDA, y, abrimos un paréntesis.
Declaramos una variable, y, usamos la función SUMA, para que nos sume cada columna.
=BYCOL(B6#;LAMBDA(a; SUMA(a)))
Obtenemos una matriz desbordada en horizontal, con la suma de cada columna, por lo que ya tenemos los totales por año.
Vamos a calcular también los totales por centro comercial, para ello, hacemos uso de la función BYROW, que es igual que la función BYCOL, pero trabaja por filas, los argumentos son los mismos que para la función BYCOL.
=BYROW(B6#;LAMBDA(a; SUMA(a)))
Ahora vamos a calcular la diferencia porcentual de cada valor respecto al total.
Para ello, debemos de dividir cada total de cada año, por el total de todos los años.
Como los datos están en horizontal, voy a usar la función BYROW.
Lo haremos en la celda B14.
Escribimos el signo igual, seguido de la celda B13, seguido del operador de rango derramado, esto me creara una matriz desbordada con cada valor.
=B13#
Ahora, tengo que dividir cada valor entre la suma total, que es donde vamos a usar la función BYROW.
Como argumento, matriz, ponemos B13#.
=B13#/BYROW(B13#;
Ponemos, la función LAMBDA, y, definimos una variable, que almacenara la matriz desbordada, y, sumamos dicha variable con la función SUMA.
=B13# / BYROW(B13#;LAMBDA(a; SUMA(a)))
Acepto, lo ponemos en formato de porcentaje, y, ya tengo los porcentajes.
Si selecciono todos los porcentajes, y, miramos en la barra de estado, podremos ver que la suma nos da el 100%, quiere decir que el cálculo es correcto.
Ahora, vamos a sacar el promedio de los porcentajes, lo haremos en la celda B17, para ello, escribimos el signo igual, seguido del nombre de la función, abrimos un paréntesis, y como argumento, ponemos la celda B14, seguido del operador de rango derramado, para que seleccione todos los porcentajes, cerramos paréntesis, y, aceptamos.
=PROMEDIO(B14#)
Y, en vez de ver los porcentajes, vamos a poner dos símbolos, uno debe de aparecer cuando este por debajo del promedio, y, otro cuando esté por encima.
Estos son los símbolos que vamos a usar.
Para insertar estos símbolos debemos de ir a la pestaña insertar, dentro del grupo símbolos, hacemos clic en símbolo.
Se abre la ventana de símbolo, en la ventana fuente, debemos de seleccionar Wingdings 2, podemos ver en la ventana inferior, los símbolos de dicha fuente, pues seleccionamos el primero, hacemos clic en insertar, y, después, en cerrar.
Seguimos los pasos, para insertar el segundo símbolo.
Decir, que aunque veamos símbolos, si vamos a la hoja de cálculo, seleccionamos una de las celdas, donde hemos insertado uno de los símbolos, y, lo ponemos, por ejemplo, en fuente ARIAL, veremos que le corresponde una letra, es decir, a todos estos símbolos, le corresponde una letra.
En la siguiente imagen, vemos las letras que le corresponde a cada símbolo.
Vamos a usar el condicional SI, para preguntar si el valor de la celda B14 es menor al promedio, pues, en dicho caso, debe de poner el símbolo cuya punta mira hacia abajo.
=SI(B14<PROMEDIO(B14#);"q"
En caso contrario, que ponga el símbolo con la punta hacia arriba.
=SI(B14<PROMEDIO(B14#);"q";"p")
Fijamos B14 dentro de la función PROMEDIO, y, el primer B14, lo dejamos en relativa, para que se actualice al copiar.
Aceptamos.
=SI(B14<PROMEDIO($B$14#);"q";"p")
Arrastramos hacia la derecha, y, ya tenemos los símbolos correspondientes, bueno, de momento las letras que pertenecen a cada símbolo.
Pero, a mí me gustaría, no estar pendiente de estar sabiendo si he de fijar celdas o no, y, después arrastrar, sino que lo haga todo de una vez.
Pues en este caso, vamos a hacer uso de la función LET.
Lo haremos en la celda B15.
Escribimos el signo igual, seguido del nombre de la función LET, y, abrimos un paréntesis.
=LET(
Vamos a definir una variable y le asignamos, la matriz desbordada con los valores.
=LET (a; B14#;
Declaramos otra variable, y, le asignamos, la suma de la matriz desbordada, con la función SUMA.
=LET (a; B14#; b; SUMA(B14#);
Como argumento calculo, dividimos a entre b.
Cerramos paréntesis, y, aceptamos.
=LET (a; B14#; b; SUMA(B14#); a/b)
Obtenemos una matriz desbordada con todos los porcentajes.
Ahora, seleccionamos los caracteres de los símbolos y lo ponemos en Wingdings 2, y, tenemos nuestros símbolos.
Ahora, vamos a aplicar un formato condicional a los valores, para que queden señalados los valores que estén por encima del promedio.
Para ello, seleccionamos los datos.
Vamos a la pestaña inicio, desplegamos formato condicional, y, seleccionamos nueva regla.
Se abre la ventana, nueva regla de formato, seleccionamos la ultima opción, que es la que nos permite insertar una función, o, formula.
Vamos a usar el condicional SI, donde preguntamos, que si el valor de la celda B6 es mayor al promedio del rango B6:F11, entonces, que ponga el valor de la celda B6.
SI(B6>PROMEDIO(B6:F11);B6)
Damos un formato, y, aceptamos.
Podemos ver que las celdas que cumplen con la regla quedan resaltadas.
Pero, como usamos dos veces la referencia B6, podemos usar la función LET, para almacenar la referencia B6 en una variable.
Aceptamos, y, ya lo tenemos.
Lo siguiente va a ser seleccionar un año, y, comparar el año seleccionado con el periodo previo.
Creamos una validación de datos para seleccionar un año, siguiente los pasos dados anteriormente.
Seleccionamos un año.
Tenemos que usar la función INDICE y COINCIDIR, para encontrar el valor del año seleccionado.
¿Para que usamos la función COINCIDIR?
Pues, la vamos a usar, para encontrar la posición que ocupa el año seleccionado en la validación de datos, celda B19, en el rango de los años, celdas desde B5 a F5, lo haremos con una coincidencia exacta, si en una celda escribimos, =COINCIDIR(B19;B5:F5;0), teniendo el año 2018, veremos que nos devuelve la posición 2.
Pues esta expresión, será el argumento, numero de columna de la función INDICE, como argumento, array, de INDICE, seleccionaos desde B13 a F13, que son los totales que calculamos anteriormente, para cada año, como argumento número de fila, ponemos 1, o, lo omitimos, ya que el valor 1, es el valor predeterminado, la expresión quedaría:
=INDICE(B13:F13;1;COINCIDIR(B19;B5:F5;0))
Obtenemos el total para el año seleccionado, que es el año 2018.
893278
Ahora, debemos de encontrar, el año anterior, para calcular la diferencia, para encontrar el año anterior a la función COINCIDIR, debemos de restarle 1, lo haremos en otra celda para probar, la expresión quedaría como sigue:
=INDICE(B13:F13;COINCIDIR(B19;B5:F5;0)-1)
Vemos que nos devuelve el año anterior, en este caso, el año 2017.
1000414
Ahora restamos ambas funciones.
=INDICE(B13:F13;COINCIDIR(B19;B5:F5;0))-INDICE(B13:F13;COINCIDIR(B19;B5:F5;0)-1)
Como resultado, obtenemos una cantidad negativa.
Vemos que usamos dos veces la función COINCIDIR, pues, vamos a usar la función LET, donde creamos una primera variable, y, le asignamos el primer COINCIDIR, es decir, sin restarle 1.
=LET(a;COINCIDIR(B19;B5:F5;0);
Como argumento, calculo, en la función INDICE, sustituimos las funciones COINCIDIR, por el nombre de la variable.
=LET(a; COINCIDIR(B19;B5:F5;0);INDICE(B13:F13;a)-INDICE(B13:F13;a-1))
Obtenemos el mismo resultado pero con una sintaxis más corta.
¿Qué ocurre, si selecciono el año 2017?
Pues, que no hay un año anterior, por lo que vamos a usar un condicional SI, para que cuando se seleccione el año 2017, no pueda comparar, debemos de preguntar, que si el valor de la celda B19, es igual a 2017, que ponga un texto en blanco, en caso contrario, que ejecute la función INDICE.
=LET(a;COINCIDIR(B19;B5:F5;0);SI(B17=2017;"";INDICE(B13:F13;a)-INDICE(B13:F13;a-1)))
Lo siguiente es hacer un informe donde pueda ver los valores del año seleccionado y del año anterior para cada centro.
Seleccionamos, por ejemplo, el año 2021.
Vamos a usar la función DESREF, en esta ocasión, que a partir de una celda, podemos indicar el numero de filas, y, de columnas a moverse.
Escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.
=DESREF(
Como argumento, referencia, es decir, el punto de partida va a ser la celda B5 que es donde se encuentra el primer año.
=DESREF(B5
En el argumento filas, ponemos 1, para que baje una fila, y, se coloque en el primer valor del primer año.
=DESREF(B5;1
En el argumento columna, debemos de usar la función COINCIDIR, para indicarle a que columna debe de ir, es la misma función COINCIDIR, que hemos usado para el ejemplo anterior, es decir, encontrar la posición del año seleccionado, dentro del rango de los años.
=DESREF(B5;1;COINCIDIR(B19;B5:F5;0)
El argumento alto, le ponemos 6, que es el número de centros comerciales.
=DESREF(B5;1;COINCIDIR(B19;B5:F5;0);6
Y el argumento ancho, de momento le ponemos 1.
=DESREF(B5;1;COINCIDIR(B19;B5:F5;0);6;1)
Aceptamos, y, vemos que nos devuelve los totales por centros comerciales, y, el año seleccionado.
¿es correcto el resultado?
Si nos fijamos en el modelo, nos esta devolviendo el siguiente año.
Y, no está mal, el resultado es correcto, porque cuando le indicamos el argumento columna, se mueve, pero no cuenta la columna donde esta, si nos fijamos en la función, en el argumento columna hemos puesto la función COINCIDIR, imaginemos, que la función COINCIDIR devuelve 1, quiere decir que a partir de la celda B5, bajara una fila, porque en el argumento, fila, hemos puesto 1, es decir, se colocara en la celda B6, en la celda B6, tenemos el primer valor del año 2017, el siguiente argumento es columna, que es el resultado de la función COINCIDR, hemos dicho, que devuelve 1, quiere decir, que de B6, se va a mover a C6, que es el valor del siguiente año.
Si lo dejamos así, siempre será ignorado el valor del año 2017, pues, a la función COINCIDIR, le vamos a restar 1, para, que retroceda una columna.
=DESREF(B5;1;COINCIDIR(B19;B5:F5;0)-1;6;1)
Ahora, tenemos los valores correctos.
Pero, tenemos el año seleccionado, y, queremos ver también el año previo.
Pues para ello, en el argumento ancho, vamos a poner, -2, para que retroceda dos columnas, decir, que cuando el valor es positivo, se mueve hacia la derecha, y, cuando es negativo, se mueve hacia la izquierda.
=DESREF(B5;1;COINCIDIR(B19;B5:F5;0)-1;6;-2)
Ahora, tenemos los valores del año seleccionado y del previo.
Pero si seleccionamos el año 2017, como no hay año previo, aparecen los centros comerciales.
Esto lo vamos a arreglar con un condicional SI, ,donde preguntamos que si el valor de la celda B19, es igual a 2017, entonces, que aparezca el texto “No hay datos que comparar”, en caso contrario, que ejecútela función DESREF.
=SI(B19=2017;"No hay datos que comparar";DESREF(B5;1;COINCIDIR(B19;B5:F5;0)-1;6;-2))
Lo siguiente es saber cuál es el supermercado de más ventas del año seleccionado, y, del año previo.
Para ello, tenemos que preguntar, si el valor máximo de todos los valores es igual a uno de los valores devueltos por la función MAX, en caso, de coincidencia, pues que devuelva el centro comercial.
Tenemos hacer referencia a a la celda F20, seguida del operador de rango derramado, para que seleccione los valores del año actual, y, previo, pero, debemos de usarla dos veces, una para sacar el valor máximo, y, otra para comparar con cada valor, por lo que vamos a hacer uso de la función LET.
Donde, vamos a definir una variable, y, le asignamos el valor de F20, seguido del operador de rango derramado, en el argumento calculo, es donde vamos a usar el condicional SI, donde preguntamos, si el valor máximo de F20 #, es igual a uno de los valores de F20#, entonces, que ponga dicho valor, en caso contrario, que ponga un texto en blanco.
=LET(a;F20#;SI(MAX(a)=a;E20:E25;""))
Aceptamos, y, obtenemos una matriz desbordada con el centro comercial de más ventas, para el año seleccionado.
Pero, si no es el primer centro comercial, aparece en la posición que le corresponde, y, queremos que aparezca en la primera posición.
Pues usamos la función CONCAT que ignora las celdas en blanco.
=CONCAT(LET(a;F20#;SI(MAX(a)=a;E20:E25;"")))
Y ya lo tenemos.
Lo siguiente va a ser insertar un gráfico de columnas agrupadas.
Para ello, seleccionamos los datos, incluidos los encabezados.
Vamos a la pestaña insertar, e, insertamos un gráfico de columnas agrupadas.
Hacemos clic con el botón alternativo del ratón sobre una columna, y, elegimos cambiar tipo de gráfico de series.
Vemos en la ventana de la izquierda, que queda seleccionamos, combinado, queremos poner el año previo, como un grafico de líneas con marcadores.
Pues marcamos serie2, hacemos clic en la flecha de la derecha, se abre la ventana de gráficos, y, seleccionamos grafico de líneas.
Aceptamos.
Cambiamos el formato a las columnas, y, a la línea, si queremos.
Quitamos borde del gráfico, para ello, hacemos doble clic sobre uno de los lados del gráfico, se abre a la derecha, la ventana de formato del área del gráfico, seleccionamos el siguiente icono, y, marcamos sin línea.
Quitamos líneas de cuadricula del gráfico, para ello, simplemente, seleccionamos una de las líneas, y, pulsamos la tecla de suprimir.
Seleccionamos la línea, en la ventana de la derecha, formato de serie de datos, hacemos clic en marcador.
Desplegamos opciones de marcador, marcamos integrado, desplegamos la ventana tipo, y, seleccionamos otro icono, si queremos, y, le cambiamos el tamaño.
Desplegamos relleno, y, le cambiamos el color.
Desplegamos borde, y, marcamos sin línea.
Selecciono la línea, y, cambia el color, le pongo el mismo color que a los marcadores.
Decir, que esta es la configuración que yo prefiero, por supuesto, cada uno, puede adaptar el grafico a sus necesidades.
Lo último que vamos a hacer, es añadir una columna nueva al gráfico, para que quede señalado de otro color, la columna de mayor valor del año seleccionado.
Para ello, en los datos añadimos una nueva columna, para calcular el valor máximo, donde igual que antes, usamos la función MAX, para comparar cada valor, con el valor máximo.
=SI(MAX(G20:G25)=G20;G20;"")
Obtenemos una matriz desbordada, con el valor, donde hay coincidencia, y, blanco, donde no la hay.
Arrastramos, pero antes, fijamos el rango dentro de la función MAX.
=SI(MAX($G$20:$G$25)=G20;G20;"")
Seleccionamos el grafico, vamos a la pestaña diseño de gráfico, y, hacemos clic en seleccionar datos.
Se abre la ventana, seleccionar origen de datos, hacemos clic en agregar.
Añadimos la serie, en la ventana valores de la serie, y, aceptamos.
Pero, aparece la columna al lado de la de mayor, y, debemos de poner esta columna, encima de la columna de mayor valor, para ello, seleccionamos la columna con botón alternativo del ratón, y, seleccionamos, dar formato a serie de datos.
En la ventana de la derecha, vemos superposición de series, que es la distancia ente columna, y, columna, pues ponemos 100%.
Y ya tenemos, siempre, la columna de mayor valor de otro color.
Comments