Seguimos trabajando con el modelo que suelo usar habitualmente, quiero crear una matriz desbordada, donde en una columna aparezcan los centros, y, en la siguiente columna, las cantidades por centro, además, queremos que se agrega al final de la matriz desbordada una fila donde ponto la palabra total, y, en la columna de al lado la suma de la columna cantidad.
Vamos a hacerlo una primera vez, algo más larga.
En una celda, voy a usar la función HSTACK, que nos va a permitir crear una matriz desbordada, donde añadimos columnas, entonces, como argumento matriz1, son los valores únicos de la columna centro.
=HSTACK(UNIQUE(Ventas[Centro])
Punto y coma, como argumento matriz2, voy a usar la función SUMAR.SI.CONJUNTO (SUMIFS), para obtener la cantidad vendida para cada centro comercial, como argumento rango de suma, seleccionamos la columna de cantidad.
=HSTACK(UNIQUE(Ventas[Centro]);SUMIFS(Ventas[Cantidad]
Punto y coma, como argumento rango de criterios1, seleccionamos la columna de centro.
=HSTACK(UNIQUE(Ventas[Centro]);SUMIFS(Ventas[Cantidad];Ventas[Centro]
Punto y coma, como argumento criterios1, son los valores únicos de la columna centro.
=HSTACK(UNIQUE(Ventas[Centro]);SUMIFS(Ventas[Cantidad];Ventas[Centro];UNIQUE(Ventas[Centro]
Cerramos paréntesis, y, aceptamos.
=HSTACK(UNIQUE(Ventas[Centro]);SUMIFS(Ventas[Cantidad];Ventas[Centro];UNIQUE(Ventas[Centro])))
Obtenemos una matriz desbordada con cada centro, y, la cantidad vendida para cada centro.
Ahora, queremos que al final de la matriz desbordada aparezca la palabra de total, junto con la suma de la columna cantidad, que es el total.
Para ello, debemos de añadir una fila en vertical, por lo que usamos la función VSTACK, como argumento matriz1, es la expresión anterior.
=VSTACK(HSTACK(UNIQUE(Ventas[Centro]);SUMIFS(Ventas[Cantidad];Ventas[Centro];UNIQUE(Ventas[Centro])))
Punto y coma, como argumento matriz2, debemos de usar la función HSTACK, porque tenemos que añadir dos columnas, una llamada total, y, otro con la suma de la columna cantidad, pues, como argumento matriz, entre comillas dobles, ponemos la palabra de total.
=VSTACK(HSTACK(UNIQUE(Ventas[Centro]);SUMIFS(Ventas[Cantidad];Ventas[Centro];UNIQUE(Ventas[Centro])));HSTACK("Total"
Punto y coma, como argumento matriz2, sumamos la columna de cantidad.
=VSTACK(HSTACK(UNIQUE(Ventas[Centro]);SUMIFS(Ventas[Cantidad];Ventas[Centro];UNIQUE(Ventas[Centro])));HSTACK("Total";SUM(Ventas[Cantidad])
Cerramos paréntesis, y, aceptamos.
=VSTACK(HSTACK(UNIQUE(Ventas[Centro]);SUMIFS(Ventas[Cantidad];Ventas[Centro];UNIQUE(Ventas[Centro])));HSTACK("Total";SUM(Ventas[Cantidad])))
Vemos como se ha añadido la columna de total, con el total de la columna cantidad.
Ahora, vamos a hacerlo de otra forma, donde vamos a usar la función DROP, la cual vamos a explicar antes.
Esta función excluye el numero de filas, y, de columnas del inicio, o, del final de una matriz.
Sintaxis
=DROP(array, rows,[columns])
Array. La matriz desde la que se van a quitar filas o columnas.
Filas. El número de filas que se van a quitar. Un valor negativo se quita desde el final de la matriz.
Columnas. El número de columnas que se van a excluir. Un valor negativo se quita desde el final de la matriz.
Veamos un ejemplo sencillo, tenemos la siguiente matriz.
Quiero quedarme con la ultima columna, para ello, ponemos la función DROP, como argumento array, seleccionamos la matriz, omitimos el argumento filas, y, como argumento columnas, ponemos 2, quiere decir, que tiene que excluir las dos primeras columnas.
Cerramos paréntesis, y, aceptamos.
=DROP(B15:D16;;2)
Obtenemos una matriz desbordada con los valores de la última columna.
Si lo que quiero obtener es la primera columna, ponemos el número 2 en negativo.
=DROP(B19:D20;;-2)
Tenemos una matriz desbordada con los valores de la primera columna.
Si quiero obtener los valores 5, y, 6, debo de excluir una fila, y, una columna.
=DROP(B23:D24;1;1)
Si quiero obtener los valores 1, y, 2, ponemos los argumentos de la expresión anterior en negativos.
=DROP(B23:D24;-1;-1)
Continuamos con el ejercicio anterior.
Con la función VSTACK, voy a traerme los valores únicos de la columna centro, junto con la palabra total.
=VSTACK(UNIQUE(Ventas2[Centro]);"Total")
En la columna de al lado, uso la función SUMA.SI.CONJUNTO (SUMIFS), donde argumento rango de suma, seleccionamos la columna de cantidad, como argumento rango de criterios1, seleccionamos la columna de cantidad, y, como argumento criterios1, ponemos la celda G2, junto con el operador de rango derramado.
Cerramos paréntesis, y, aceptamos.
=SUMIFS(Ventas2[Cantidad];Ventas2[Centro];G2#)
Tenemos una matriz desbordada con el total de cantidad para cada centro, pero, vemos que en total aparece cero, porque total, no esta dentro de la columna centro.
Lo que voy a hacer es usar la función DROP, y, excluir la última fila.
=DROP(SUMIFS(Ventas2[Cantidad];Ventas2[Centro];G2#);-1)
Ahora, uso la función VSTACK, donde como argumento matriz1, es la expresión anterior, y, como argumento matriz2, sumamos la columna de cantidad.
=VSTACK(DROP(SUMIFS(Ventas2[Cantidad];Ventas2[Centro];G2#);-1);SUM(Ventas2[Cantidad]))
Ya tenemos añadida la columna de total, junto con la suma de la columna de cantidad.
Lo siguiente va a ser añadir bordes a la matriz desbordada, para ello, seleccionamos rango, vamos a la pestaña de inicio, desplegamos formato condicional, y, seleccionamos nueva regla.
En la siguiente ventana, seleccionamos la ultima opción, que nos permite introducir una formula.
En la ventana de formula, ponemos la celda G2, pero ponemos el símbolo dólar antes de la columna, de esta manera, afectara a toda la línea.
Hacemos clic en formato.
En la ventana de formato de celdas, seleccionamos la pestaña de bordes, y, marcamos todos los bordes.
Aceptamos, y, ya tenemos nuestros bordes, y, líneas.
Ahora, vamos a dar formato a la fila de formato, para ello, seleccionamos el rango, añadimos una nueva regla de formato condicional, y, en la ventana de formula, ponemos:
Le voy a dar el mismo formato que el del encabezado.
Lo podíamos haber realizado con una tabla dinámica, pero quiero que se actualice de forma automática, por lo que es mejor el uso de matrices desbordadas.
Ahora quiero calcular la diferencia porcentual de cada centro con respecto al total, para ello, seleccionamos la celda H2, junto con el operador de rango derramado, y, lo dividimos entre la suma de la columna cantidad.
=H2#/SUM(Ventas2[Cantidad])
Debemos de borrar las reglas de formato condicional, y, crearlas de nuevo.
Comments