Trabajamos con el modelo de ventas de productos en diferentes centros comerciales, provincias, y, fechas.
Vamos a crear una única lista desplegable, que debe de ser rellena por provincia, centro, o, producto.
Necesitamos tener activa la pestaña de programador, o, desarrollador si tenemos la versión 2013, para activarla, para ello, desplegamos la pestaña de archivo, y, seleccionamos opciones.
Se abre la ventana de opciones de Excel, en la ventana de la izquierda, hacemos clic en personalizar cinta de opciones.
En la ventana, pestañas principales, bajamos hasta desarrollador, o, programador, y, marcamos la casilla, y, aceptamos.
Ya debemos de tener la pestaña en la cinta de opciones, pues, hacemos clic en dicha pestaña.
Dentro del grupo controles, desplegamos insertar, y, seleccionamos botón de opción.
Lo colocamos dentro de la celda I2, borramos el texto del botón de comando.
Hacemos clic con botón secundario del ratón sobre el botón de comando, y, seleccionamos formato de control.
En la ventana vincular con la celda, seleccionamos una celda, donde poner el número de botón de comando pulsado.
Aceptamos.
Realizamos la misma operación anterior, e, insertamos dos botones de comandos más, lo colocamos al lado del primero, en este caso, no hace ir a formato de control, cuando ponemos varios botones de comandos, al primero se le asigna el número 1, al segundo el número 2, y, así sucesivamente.
Dicho valor es colocado en la celda que hemos vinculado.
Vamos a la pestaña de inicio, dentro del grupo edición, desplegamos buscar y seleccionar, y, seleccionamos, seleccionar objetos, con esto, cuando marquemos una zona, solo los objetos quedaran seleccionados.
Ahora, seleccionamos los tres botones de comandos con el ratón.
Vamos a la pestaña de formato de forma, desplegamos alinear, y, seleccionamos alinear en la parte inferior.
Volvemos a desplegar alinear, y, seleccionamos distribuir horizontalmente.
Ya tenemos los tres botones bien alineados.
Si hacemos clic en uno de los botones, veremos como en la celda vinculada, pone el número que le corresponda.
Cuando hagamos clic en el primer botón, quiero que se carguen las provincias únicas, y, ordenadas.
Cuando haga clic en el segundo botón, quiero que se carguen los centros comerciales únicos.
Cuando haga clic en el tercer botón, se deben de cargar los productos únicos, pero en el mismo rango.
En la celda I1, debe de aparecer el encabezado del botón que seleccionemos, si hago clic en el primer botón en la celda I1, debe de aparecer provincia.
En la celda S1, es donde he vinculado el valor de los botones, entonces, en la celda I1, debo de preguntar que si el valor de la celda S1, es igual a 1, debe de poner provincia, si es igual a 2, debe de poner centro, y, si es igual a 3, debe de poner producto.
Lo hare con el condicional SI anidado.
=SI(S1=1;"Provincia";SI(S1=2;"Centro";SI(S1=3;"Producto";"")))
Vemos que como tengo señalado el primer botón, en la celda I1, aparece provincia.
En la celda S2, es donde deben de aparecer los valores del botón seleccionado.
De nuevo, voy a usar el condicional SI anidado, donde pregunto que si el valor de la celda S1, es igual a 1, aplico la función ORDENAR y UNICOS en la columna de provincia, si es igual a 2, aplico la función UNICOS, en la columna centro, y, si es igual a 3, aplico la función UNICOS, a la columna de productos.
=SI(S1=1;ORDENAR(UNICOS(Datos[Provincia]));SI(S1=2;UNICOS(Datos[Centro]);SI(S1=3;UNICOS(Datos[Producto]);"")))
Como he seleccionado el primero botón, podemos ver las provincias ordenadas, si selecciono otro botón, vemos como el valor de la celda I1, cambia al valor del botón que corresponde, y, como la lista queda rellenada, por ejemplo, si hemos elegido el segundo botón, con los centros comerciales únicos.
En la celda I3, voy a crear la lista desplegable, con los valores de la celda S2, donde usare el operador de rango derramado (#), para ello, vamos a la pestaña de datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Desplegamos permitir, y, seleccionamos lista, en la ventana de origen, ponemos la celda S2, seguido del operador de rango derramado (#).
Aceptamos.
Tengo seleccionado el segundo botón, es decir, el centro comercial, si desplegamos la lista, veremos cómo se ha rellenado con los centros comerciales únicos.
Lo siguiente va a ser filtrar por el botón que hemos seleccionado, para ello, usare la función FILTRAR, donde como argumento array, es la tabla, que se llama datos, y, como argumento include, debemos de convertir el valor de la celda I1, en una referencia de columna, es decir, [Centro].
Vamos a ver antes, como convertir en valor de la celda I1, en una referencia estructurara, por ejemplo, si tengo seleccionado el botón de centro, deberá de aparecer el nombre de la tabla, y, entre corchetes, el nombre del encabezado, es decir, Datos[Centro].
Quiere decir que he de concatenar el nombre de la tabla, Datos, con una apertura de corchete, con el valor de la celda I1, y, concatenado con un cierre de corchete.
Es decir:
="Datos" & "[" & I1 & "]"
Y, obtenemos:
Pero, para recuperar los valores de la columna debemos de usar la función INDIRECTO, como sigue:
=INDIRECTO("Datos" & "[" & I1 & "]")
Pues vamos a filtrar la tabla datos, siempre que la columna devuelta por INDIRECTO sea igual al valor seleccionado.
=FILTRAR(Datos;(INDIRECTO("Datos"&"["&I1&"]")=I3)
Ya tenemos filtrado la tabla datos, por el elemento seleccionado.
Podemos probar cambiando de botón.
En el modelo los años del año 2017 al año 2021, ahora, voy a insertar dos casillas de verificación, voy a realizar el ejemplo con los dos primeros años, para el resto de los años, es repetir la operación.
Vamos de nuevo a la pestaña de programador, o, desarrollador, dentro del grupo controles, desplegamos insertar, y, seleccionamos casilla.
La colocamos dentro de la celda J2, insertamos otra casilla, y, la insertamos en la celda J3.
Hacemos clic con botón alternativo de ratón, y, seleccionamos formato de control.
En este caso, lo voy a vincular con la celda T1, y, aceptamos.
En este caso, cuando la casilla este activada, nos devolverá VERDADERO, en caso contrario, devolverá FALSO.
Como voy a trabajar con los dos primeros años, en la celda J2, voy a preguntar si el valor de la celda T1 es igual a VERDADERO, en ese caso, me traigo los años únicos de la columna fecha, transpongo los años, y, con la función INDICE, le digo que me devuelva la primera fila, y, primera columna, lo cual me devolverá el primer año.
=SI(T1=VERDADERO;INDICE(TRANSPONER(UNICOS(AÑO(Datos[Fecha])));1;1);"")
Si activamos la casilla vemos como aparece el año 2017, y, si no está activada no aparece nada.
Ahora, en la celda J3, uso la misma función anterior, pero cambio T1, por T2, porque aquí debemos de vincular cada casilla con una celda, y, en la función INDICE, le digo que me devuelva la columna 2.
=SI($T$2=VERDADERO;INDICE(TRANSPONER(UNICOS(AÑO(Datos[Fecha])));1;2);"")
Funciona igual que para el año 2017.
Lo siguiente es que, si marcamos un año, el modelo debe de quedar filtrado por el botón seleccionado, y, el año seleccionado, si selecciono las dos casillas, debe de quedar filtrado por el botón seleccionado, y, los dos años, y, si no selecciono ninguna casilla, que devuelva todos los datos.
Volvemos a la función FILTRAR, borramos le cierre de paréntesis, encerramos entre paréntesis, la primera condición.
=FILTRAR(Datos;(INDIRECTO("Datos"&"["&I1&"]")=I3)
Ponemos el símbolo de asterisco, abrimos un paréntesis para poner la siguiente condición, que es que el año de la columna fecha, donde usare la función VALOR, junto con la función TEXTO, debe de ser igual al valor de la celda J2, es decir, a 2017, y, cerramos paréntesis.
=FILTRAR(Datos;(INDIRECTO("Datos"&"["&I1&"]")=I3)*(VALOR(TEXTO(Datos[Fecha];"aaaa"))=J2)
Ahora, pongo el símbolo de más (+), que es igual al operador lógico O, es decir, que devuelve VERDADERO, si una de las condiciones se cumple, abro otro paréntesis, de nuevo uso la función VALOR, junto con la función TEXTO, pero debe de ser igual al valor de la celda J3, es decir, a 2018.
=FILTRAR(Datos;(INDIRECTO("Datos"&"["&I1&"]")=I3)*(VALOR(TEXTO(Datos[Fecha];"aaaa"))=J2)+(VALOR(TEXTO(Datos[Fecha];"aaaa"))=J3)
Y, para el argumento si vacío, filtramos todo el modelo.
=FILTRAR(Datos;(INDIRECTO("Datos"&"["&I1&"]")=I3)*(VALOR(TEXTO(Datos[Fecha];"aaaa"))=J2)+(VALOR(TEXTO(Datos[Fecha];"aaaa"))=J3);FILTRAR(Datos;(INDIRECTO("Datos"&"["&I1&"]")=I3);""))
Antes de seguir, las dos condiciones que hemos puesto con el signo mas (+), la vamos a encerrar entre paréntesis, en caso contrario, no nos dará los resultados correctos, cuando tengamos marcado el año 2018, y, cambiemos de encabezado.
=FILTRAR(Datos;(INDIRECTO("Datos"&"["&I1&"]")=I3)*((VALOR(TEXTO(Datos[Fecha];"aaaa"))=J2)+(VALOR(TEXTO(Datos[Fecha];"aaaa"))=J3));FILTRAR(Datos;(INDIRECTO("Datos"&"["&I1&"]")=I3);""))
Aceptamos, si no tenemos marcada ninguna casilla, aparecen todos los datos, si seleccionamos el año 2017, aparecerá por el botón seleccionado y el año 2017, si marcamos también el año 2018, aparecerán por los años 2017 y 2018, si marcamos solo el año 2018, aparecerá solo por el año 2018, y, si no marcamos ninguna casilla, aparecerán todos los datos.
Pues hemos concluido el ejercicio.
Comments