Seguimos trabajando con el modelo que uso habitualmente.
Queremos calcular los totales para cada provincia, y, cada centro comercial.
Lo primero que voy a hacer es unir las columnas provincia, y, centro con la función HSTACK, este ejemplo, volveré a hacerlo con la versión en inglés, para poder usar las nuevas funciones.
Escribimos el signo igual, seguido de la función HSTACK, y, abrimos un paréntesis.
=HSTACK(
Como argumento matriz1, seleccionamos la columna de provincia.
=HSTACK(Table1[Provincia];
Como argumento matriz2, seleccionamos la columna de centro.
Cerramos paréntesis y aceptamos.
=HSTACK(Table1[Provincia];Table1[Centro])
Tenemos una matriz desbordada de dos columnas, que son provincia, y, centro.
Nos quedamos con los valores únicos, y, ordenamos.
=SORT(UNIQUE(HSTACK(Table1[Provincia];Table1[Centro])))
Vemos, por ejemplo, que la provincia de Albacete aparece cinco veces, una para cada centro.
Pues, queremos calcular el total para cada provincia, y, centro.
Voy a traerme al lado las columnas provincia, y, centro del modelo con la función HSTACK.
Vamos a ir haciéndolo paso a paso.
Lo siguiente que voy a hacer es preguntar, si el valor de las celdas A2, y, B2 es igual a la celda E2, seguido del operador de rango derramado, para que compare la primera fila, Albacete Carrefour, con todo el modelo.
=(A2:B2=E2#)
Obtenemos una matriz desbordada con verdaderos, y, falsos, como esta matriz está formada por dos columnas, cuando aparecen dos verdaderos, quiere decir, que la provincia es Albacete, y, el centro es Carrefour.
Cuando aparece un verdadero a la izquierda, quiere decir que solo la provincia coincide.
Pues, a la expresión anterior, voy a indicarle que me devuelva la columna de total.
=(A2:B2=E2#)*(Table1[Total])
Vemos que cuando ambas celdas son verdaderas, nos devuelve el total en ambas celdas, cuando devuelve un solo valor, quiere decir, que no cumplen ambas condiciones.
De las dos columnas, vamos a quedarnos con los valores que aparecen dos veces, para ello, vamos a usar la función FILTER, donde el argumento matriz, es la expresión anterior.
=FILTER((A2:B2=E2#)*(Table1[Total]);
El argumento include, vamos a usar una constante de matriz, para indicar que columnas queremos ver, y, cuales no, en este caso, solo queremos ver la segunda columna.
Cerramos paréntesis, y, aceptamos.
=FILTER((A2:B2=E2#)*(Table1[Total]);{0\1})
Vemos solo la columna 2.
Lo siguiente va a ser preguntar que, si el valor de cada celda es mayor a cero, en ese caso, que me devuelva el valor de la columna 1, para ello, usamos el condicional IF (SI), y, preguntamos que si el resultado de la función FILTER, es mayor a cero, que nos devuelva la función FILTER, pero que nos devuelva la columna 1, en caso contrario, que ponga un texto en blanco.
=IF(FILTER((A2:B2=E2#)*(Table1[Total]);{0\1})>0;FILTER((A2:B2=E2#)*(Table1[Total]);{1\0});"")
Tenemos una matriz desbordada, con los valores para Albacete, y, Carrefour.
Ahora, después del signo igual, usamos la función SUM(SUMA), para que nos de el total.
=SUM(IF(FILTER((A2:B2=E2#)*(Table1[Total]);{0\1})>0;FILTER((A2:B2=E2#)*(Table1[Total]);{1\0});""))
Fijamos la referencia E2, y, arrastramos.
=SUM(IF(FILTER((A2:B2=$E$2#)*(Table1[Total]);{0\1})>0;FILTER((A2:B2=$E$2#)*(Table1[Total]);{1\0});""))
Ya tenemos el total para cada provincia, y, centro comercial.
Vamos a hacerlo de otra forma, abro un paréntesis, y, pregunto que si en la columna provincia, hay algún valor igual al de la celda A2 de la hoja donde estamos trabajando, y, cerramos paréntesis.
=(Table1[Provincia]=Sheet1!A2)
Ponemos el símbolo de asterisco, abrimos otro paréntesis, y, preguntamos si algún valor de la columna centro, es igual al valor de la celda B2, de la hoja donde estamos trabajando.
=(Table1[Provincia]=Sheet1!A2)*(Table1[Centro]=Sheet1!B2)
Volvemos a poner el símbolo de asterisco, abrimos otro paréntesis, y, ponemos la columna de total.
=(Table1[Provincia]=Sheet1!A2)*(Table1[Centro]=Sheet1!B2)*(Table1[Total])
Obtenemos una matriz desbordada con los valores donde hay coincidencia, ahora, después del signo igual, usamos la función SUM, para obtener el total.
=SUM((Table1[Provincia]=Sheet1!A2)*(Table1[Centro]=Sheet1!B2)*(Table1[Total]))
Vemos que tenemos el mismo resultado.
Arrastramos, y, tenemos los mismos resultados.
También, podríamos seleccionar un año, y, ver los totales para provincia, centro, y, año.
Vamos a crear una lista de única de años, con la función TEXT(TEXTO).
=UNIQUE(TEXT(Table1[Fecha];"aaaa"))
Creamos una validación de datos, con los años, y, seleccionamos un año.
Lo único que tenemos que hacer, es añadir una nueva condición, como sigue, en la primera forma, donde fijamos la referencia I1:
=SUM(IF(FILTER((A2:B2=$F$2#)*(TEXT(Table1[Fecha];"aaaa")=Sheet1!$I$1)*(Table1[Total]);{0\1})>0;FILTER((A2:B2=$F$2#)*(TEXT(Table1[Fecha];"aaaa")=Sheet1!$I$1)*(Table1[Total]);{1\0});""))
Y, así, en la segunda forma, donde también fijamos la referencia I1.
=SUM((Table1[Provincia]=Sheet1!A2)*(Table1[Centro]=Sheet1!B2)*(TEXT(Table1[Fecha];"aaaa")=Sheet1!$I$1)*(Table1[Total]))
Ahora, vemos el total por provincia, centro, y, año.
También, podríamos crear un desplegable con las provincias, y, otro con los centros, elegimos una provincia, y, un centro, y calculamos el total, donde la expresión seria la misma, lo único que cambia es el valor para buscar.
=SUM(IF(FILTER((K2:L2=$F$2#)*(TEXT(Table1[Fecha];"aaaa")=Sheet1!$I$1)*(Table1[Total]);{0\1})>0;FILTER((K2:L2=$F$2#)*(TEXT(Table1[Fecha];"aaaa")=Sheet1!$I$1)*(Table1[Total]);{1\0});""))
コメント