En el siguiente ejemplo, queremos elegir un encabezado, que puede ser centro o producto, y, debe de aparecer una lista única de dicho encabezado, y, después de la ultima línea, queremos que aparezca la palabra total.
Trabajamos con el modelo que suelo usar habitualmente, ventas de productos en diferentes centros comerciales, provincias, y, fechas.
Vamos a crear una validación de datos, con los encabezados de centro y producto, para ello, vamos a la pestaña de datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Desplegamos la ventana permitir, y, seleccionamos lista, y, en la ventana origen, seleccionamos los encabezados de centro y producto.
Aceptamos.
Podemos hacerlo con la función FILTRAR, donde como argumento array, selecciono la tabla ventas.
=FILTRAR(Ventas
Como argumento include, seleccionamos los encabezados, y, lo igualamos al valor de J1.
=FILTRAR(Ventas;Ventas[# Encabezados]=J1
Cerramos paréntesis, y, aceptamos.
=FILTRAR(Ventas;Ventas[# Encabezados]=J1)
Obtenemos una matriz desbordada con los elementos del encabezado seleccionado.
En principio, podemos usar esta función tal cual esta, pero que ocurre si borro J1 de la función, y, pongo entre comillas dobles, Fecha.
=FILTRAR(Ventas;Ventas[# Encabezados]="Fecha")
Pues, me devuelve la columna de fecha, quiere decir, que no solo puedo pedir los encabezados de la validación de datos, sino cualquiera, de alguna manera, debemos de limitar el resultado de la función, para que solo acepte los encabezados de centro y producto.
Voy a poner de nuevo la función FILTRAR, donde como argumento array, selecciono los encabezados de centro y producto.
=FILTRAR(Ventas[[Centro]:[Producto]];
Y, como argumento include, selecciono los encabezados.
=FILTRAR(Ventas[[Centro]:[Producto]];Ventas[# Encabezados]=J1
Cerramos paréntesis y aceptamos.
=FILTRAR(Ventas[[Centro]:[Producto]];Ventas[# Encabezados]=J1)
Obtenemos un error, porque no encuentra el encabezado seleccionado dentro de los encabezados, si seleccionamos el argumento include, y, pulsamos F9, nos devolverá FALSO donde no hay coincidencia, y, VERDADERO donde la hay.
Es decir, que nos devuelve VERDADERO en el encabezado que hemos seleccionado, pero por algún motivo, no llega a comprenderlo.
En el argumento include, voy a reducir los encabezados a dos, a centro y producto, para ello, usare la función INDICE, y, como argumento numero de fila, voy a usar una constante de matriz, donde pongo el numero 3, y, 4, separado la barra invertida.
=FILTRAR(Ventas[[Centro]:[Producto]];INDICE(Ventas[# Encabezados];{3\4})=J1)
Vemos como ahora si tenemos los resultados correctos, una matriz desbordada con los elementos del encabezado seleccionado.
Nos quedamos con los valores únicos.
=UNICOS(FILTRAR(Ventas[[Centro]:[Producto]];(INDICE(Ventas[# Encabezados];{3\4})=J1)))
Recordad, que lo que queremos es que después de la ultima línea, aparezca la palabra de total.
Para ello, podemos usar la función APILARV (VSTACK), donde como argumento matriz1, es la función FILTRAR, y, como argumento matriz, entre comillas dobles, ponemos total.
=APILARV( UNICOS(FILTRAR(Ventas[[Centro]:[Producto]];(INDICE(Ventas[# Encabezados];{3\4})=J1)));"Total")
Ahora, vamos a usar la función LET para conseguirlo, aunque la sintaxis será algo más larga, volvemos a la función FILTRAR, y, después del signo igual, ponemos la función LET, y, declaramos una variable.
=LET(valores_unicos
En dicha variable, almacenamos la expresión anterior.
=LET(
Valores_unicos;UNICOS(FILTRAR(Ventas[[Centro]:[Producto]];(INDICE(Ventas[# Encabezados];{3\4})=J1)))
Voy a crear otra variable, llamada recuento, donde con la función CONTARA, voy a contar los valores de la variable únicos.
=LET(
Voy a crear otra variable, llamada recuento, donde con la función CONTARA, voy a contar los valores de la variable únicos.
=LET(
Valores_unicos;UNICOS(FILTRAR(Ventas[[Centro]:[Producto]];(INDICE(Ventas[# Encabezados];{3\4})=J1)));
recuento;CONTARA(unicos)
Como tengo seleccionado el encabezado centro, la función CONTARA, devolverá 6.
Punto y coma, y, como argumento calculo, voy a usar el condicional SI para preguntar que, si cada valor de la función CONTARA, es decir, la variable recuento, lo que obtenemos con la función SECUENCIA, mas 1, es igual a la variable recuento, si examinamos la expresión, la función SECUENCIA nos va a devolver una matriz desbordada, en este caso, 6 +1, una matriz desbordada desde 1 hasta 7, y, la comparamos con el valor de la variable recuento, es decir, 6, en otras palabras, pregunto si cada valor de la función SECUENCIA es mayor a 7, cuando llegue al ultimo valor de la variable recuento (6), se cumplirá la condición que 7 es mayor a 6, entonces, pondrá la palabra de total, mientras no se cumpla la condición, ira poniendo cada valor de la variable valores_unicos.
=LET(valores_unicos;UNICOS(FILTRAR(Ventas[[Centro]:[Producto]];(INDICE(Ventas[# Encabezados];{3\4})=J1)));
recuento;CONTARA(valores_unicos);
SI(SECUENCIA(recuento+1)>recuento;"Total";valores_unicos))
Aceptamos, y, tenemos los mismos resultados que hemos obtenido anteriormente.
Lo siguiente es calcular el total para cada elemento, y, el total general, para ello, vamos a usar la expresión anterior, aunque tenemos que hacer algunos cambios.
Hasta este punto, no hay que cambiar nada.
=LET(valores_unicos;UNICOS(FILTRAR(Ventas[[Centro]:[Producto]];(INDICE(Ventas[# Encabezados];{3\4})=J1)));
Creo una variable llamada total donde usare la función SUMAR.SI.CONJUNTO, como argumento rango de suma, selecciono la columna de total, como argumento rango de criterios1, voy a usar la función INDIRECTO, ya que el encabezado puede cambiar, y, tenemos que calcular el total de los elementos que vemos, la función INDIRECTO, convertirá una referencia en formato de texto, en una referencia real, por lo que ponemos INDIRECTO, entre comillas dobles, el nombre de la tabla, y, una apertura de corchete, lo concatenamos con la celda J1, y, lo concatenamos entre comillas dobles, con un cierre de corchete.
=LET(valores_unicos;UNICOS(FILTRAR(Ventas[[Centro]:[Producto]];(INDICE(Ventas[# Encabezados];{3\4})=J1)));
total;SUMAR.SI.CONJUNTO(Ventas[Total];INDIRECTO("Ventas["&J1&"]")
Y como argumento criterios1, ponemos la variable valores_unicos.
Cerramos parentisis.
=LET(valores_unicos;UNICOS(FILTRAR(Ventas[[Centro]:[Producto]];(INDICE(Ventas[# Encabezados];{3\4})=J1)));
total;SUMAR.SI.CONJUNTO(Ventas[Total];INDIRECTO("Ventas["&J1&"]");valores_unicos)
Punto y coma, y, como argumento calculo de la función LET, usamos la función APILARV (VSTACK), donde como argumento matriz1, ponemos la variable total, y, como argumento matriz2, sumamos la variable total.
Cerramos paréntesis, y, aceptamos.
=LET(valores_unicos;UNICOS(FILTRAR(Ventas[[Centro]:[Producto]];(INDICE(Ventas[# Encabezados];{3\4})=J1)));
total;SUMAR.SI.CONJUNTO(Ventas[Total];INDIRECTO("Ventas["&J1&"]");valores_unicos);
APILARV(total;SUMA(total)))
Ya tenemos los totales para cada elemento.
Comments