17. Casilla de verificación y la función FILTRAR

CASILLA_FILTRAR.xlsx

Descargar XLSX • 258KB

Vamos a usar las casillas de verificación para crear un informe, o, reporte con el centro comercial, que hayamos seleccionado.

Trabajamos con el siguiente modelo.

En una hoja nueva, vamos a crear una lista de centros únicos, para ello, vamos a usar la función UNICOS, lo vamos a hacer en la celda A2.

Escribimos la siguiente expresión:

=UNICOS(DATOS!C2:C21)

Tenemos una lista única de centros comerciales.

Lo siguiente es insertar la casilla de verificación, que se encuentra en la pestaña de programador, si tenemos una versión de 2013, se llamara desarrollador.

Si no tenemos la pestaña activada, debemos de hacerlo, para ello, vamos a:

· Archivo

· Opciones

· personalizar cinta de opciones

Y marcamos la casilla de programador, o, desarrollador.

Aceptamos.

Ya debemos de ver dicha pestaña en nuestra cinta de opciones.

Una vez que ya la tenemos, vamos a dicha pestaña, desplegamos insertar y seleccionamos casilla dentro de controles de formulario.

La posicionamos en la celda siguiente al primer centro.

Borramos el título, para ello, hacemos clic en la casilla, y, simplemente, lo borramos.

Arrastramos.

Si hacemos clic en algunos de las casillas vemos que no pasa nada.

Pues, vamos a configurarlo.

Hacemos clic en la primera casilla con el botón alternativo del ratón, y, seleccionamos formato de control.

En la ventana que se abre, vemos la ventana valor, y, el valor, que es sin activar.

Debajo, vemos vincular con la celda, esto quiere decir que en la celda que le digamos, pondrá verdadero, o, falso según este activado o no.

Lo voy a vincular con la misma celda donde he puesto la casilla.

Aceptamos.

Vemos que si marcamos el primer centro, en la misma celda aparece el valor de verdadero, o, falso.

Como no quiero que se vea, lo voy a poner de color blanco la letra, y, repetimos el proceso para el resto de las casillas.

Lo siguiente que voy a hacer, es aplicar un formato condicional a los centros comerciales, de modo, que cuando pulsemos en una casilla, cambie el formato del centro a que corresponda.

Para ello, seleccionamos los centros comerciales, desplegamos formato condicional, y, elegimos nueva regla.

En la ventana que se abre, elegimos la última opción, que es la que nos permite introducir una formula, o, función.

Ponemos el signo igual, la celda B2, que es donde están los verdaderos, y, falsos, pero le ponemos el símbolo solar delante de la columna, para que en caso de coincidencia, señale las dos celdas, volvemos a poner el signo igual, y, que sea igual a verdadero.

Si no ponemos el signo igual e igual a verdadero el resultado es el mismo.

Le voy a dar un tipo de letra gris y tachado.

Aceptamos.

Si probamos veremos que el centro que marquemos se queda tachado de color gris.

Ya tenemos la primera parte.

En la celda C2, voy a preguntar si en el rango de los verdaderos y falsos, es igual a verdadero que ponga el centro comercial, para ello, usamos el condicional SI, donde vamos a preguntar que si algún valor del rango B2:B7, es igual a verdadero, pues que ponga el valor desde A2 a A7, podemos apreciar, que se usa el operador de rango derramado.

=SI(B2:B7=VERDADERO;A2#;"")

De esta manera, cuando seleccionemos un centro aparecerá en C2, pero como no quiero que se vea, pongo el color de la letra en blanco.

Pero vemos que si seleccionamos un centro que no es el primero, no se coloca en la primera celda.

Pues vamos a usar la función CONCAT, que ignora los blancos.

=CONCAT(SI(B2:B7=VERDADERO;A2#;""))

Este es el valor que vamos a usar para filtrar.

Lo siguiente que vamos a hacer es filtrar.

=FILTRAR(DATOS!D2:E21;DATOS!C2:C21=Hoja1!C2;"")

Vemos que nos devuelve los productos y cantidades.

Vemos que hay productos que se repiten, pero aquí no podemos usar la función UNICOS, porque las cantidades no se repiten.

Por lo que a la función filtrar, le voy a decir que solo me devuelva los productos.

=FILTRAR(DATOS!D2:D21;DATOS!C2:C21=Hoja1!C2;"")

Ahora, aplico la función UNICOS.

=UNICOS(FILTRAR(DATOS!D2:D21;DATOS!C2:C21=Hoja1!C2;""))

Ya tengo los productos únicos.

Para calcular los totales, voy a usar la función sumar.si.conjunto, porque van a hacer dos criterios, el producto y el centro.

El primer rango de criterios va a ser los productos, y, para el criterio1, usamos el operador de rango derramado, para que seleccione todos los productos que vemos.

El segundo rango de criterios va a ser los centros comerciales, y, para el criterio2, va a ser el valor de la celda C2.

=SUMAR.SI.CONJUNTO(DATOS!E2:E21;DATOS!D2:D21;Hoja1!E2#;DATOS!C2:C21;Hoja1!C2)

Ya lo tenemos.

Ahora vamos a representar el valor máximo y el mínimo en un gráfico de anillo.

Para el valor mínimo, voy a usar la función mínimo aplicado al rango de totales.

=MIN(F2#)

Para el valor máximo, voy a usar la función máximo aplicado al rango de totales.

=MAX(F2#)

Seleccionamos ambos valores.

Vamos a la pestaña de insertar, e, inserto un gráfico de anillos.

Quito el título, y, leyendas.

Selecciono el anillo pequeño.

Damos un relleno de color oscuro.

Quitamos los bordes.

Selecciono el anillo grande, le damos un relleno de color mas claro, y, quitamos los bordes.

De momento, este es el resultado.

Voy a crear una elipse al lado más o menos del tamaño del círculo central, para ello, vamos a la pestaña de insertar, dentro del grupo ilustraciones, desplegamos forma, y, seleccionamos la elipse.

Para que esta elipse sea perfecta, dejamos pulsada la tecla SHIFT, o, MAYUSC, mientras hacemos la elipse.

Le doy el mismo formato que al anillo mayor, y, quitamos contorno.

Selecciono el grafico y la elipse.

Vamos a la pestaña formato de forma.

Desplegamos alinear, y, elijo alinear verticalmente.

Desplegamos, de nuevo, alinear, y, seleccionamos alinear horizontalmente.

Vemos como las dos figuras, ahora, son una.

Lo siguiente que vamos a hacer, es agrupar ambas figuras, para que realmente sean una, es decir, que, por ejemplo, al moverla, se muevan las dos.

Siguen las dos figuras seleccionadas.

Desplegamos agrupar, y, elegimos agrupar, para que las dos figuras sean una.

En esta elipse, vamos a poner el valor menor.

Seleccionamos la elipse, vamos a la barra de formula, pone el signo igual y señalamos la celda de menor valor.

=$G$2

Seleccionamos el valor, dentro de la elipse, y, le damos formato, por ejemplo, le vamos a poner una letra de color blanca, la centramos, y, le damos algo de más tamaño.

Quitamos los bordes del gráfico, para ello, hacemos doble clic sobre uno de los lados del gráfico, se abre la ventana formato del área del gráfico.

Desplegamos el icono, relleno, y, línea, desplegamos borde, y, marcamos sin línea.

Por último, quitamos las líneas de cuadricula, para ello, vamos a la pestaña vista, y, hacemos clic en líneas de cuadricula, es decir, la desmarcamos.

Con esto, hemos terminado nuestro ejemplo.

Enlace video de como realizar el grafico descrito anteriormente.

Clic