Cuadro Combinado con Contenido Variable

Descargar el Archivo

En el post de hoy veremos como realizar cuadros combinados cuyo contenido dependa de lo que seleccionemos en los botones de opción. Para entender mejor nuestro objetivo veamos la siguiente figura:

Lo que queremos conseguir es que una vez seleccionemos la opción deseada en el botón de opción correspondiente, nos aparezca el contenido adecuado en el cuadro combinado.

Los pasos que debemos seguir son los siguientes:

1. Creamos los Botones de Opción (si no recuerda cómo insertar dichos botones consulte Formato Condicional con Botones de Opción) y los vinculamos con la celda D11.

2. Dibujamos el cuadro combinado (véase el post Cálculos con Rangos Dinámicos y Cuadros Combinados) pero todavía no procedemos a configurarlo.

3. Nos situamos en la celda D13 y escribimos la siguiente fórmula:

=DIRECCION(12;D11)&":"&DIRECCION(19;D11)

La función DIRECCION, ya tratada en este blog, crea una referencia de celda con formato de texto, una vez indicada la fila y la columna. En nuestro ejemplo la fila en la que comienzan las tres listas (fijos, eventuales y extras) es la número 12. La columna dependerá de lo que seleccionemos en el botón de opción. Dicho botón lo hemos vinculado con la celda D11 y nos devolverá 1, 2 ó 3. Si por ejemplo seleccionamos la opción Fijos entonces D11 valdrá 1 por lo que la función Direccion nos devolverá como texto la referencia de la columna 1 y fila 12, es decir, A12. De esta manera ya tenemos la celda en la que comienza el rango seleccionado por medio del botón de opción. Ahora tenemos que conseguir la celda en la que concluye dicho rango y eso lo logramos con DIRECCION (19;D11).

Ya tenemos la celda inicial y la celda final de un rango. Para unir ambas utilizamos la función CONCATENAR (o lo que es lo mismo &) y colocamos en medio los dos puntos que identifican un rango:

celda inicial A12

celda final A19

A12&":"&A19

Nos devuelve el rango requerido en forma de texto: A12:A19. Para transformar esta referencia de texto en referencia válida para excel utilizaremos la función INDIRECTO.

4. Vamos al menú Insertar/Nombre/Definir. Escribimos el nombre, por ejemplo, Rango_lista. En el cuadro Se refiere a escribimos la siguiente fórmula y pulsamos Aceptar:

=INDIRECTO($D$13)

5. Nos situamos encima del Cuadro Combinado, hacemos clic en el botón derecho y seleccionamos Formato de Control. En Rango de entrada introducimos Rango_lista (que es precisamente el rango variable que hemos generado) y vinculamos, por ejemplo, con la celda D12. Pulsamos Aceptar.

A partir de este momento cuando pulsemos un botón de opción la lista que aparecerá en el cuadro combinado será la correspondiente al botón seleccionado:

Formato Condicional con Botones de Opción

Descargar el Archivo

Hoy vamos a ver como podemos resaltar, con la herramienta Formato Condicional vinculada aBotones de Opción, diferentes registros dentro de una tabla. 

Tenemos una tabla con la siguiente información: Nombre del cliente; importe que se le ha facturado; la cantidad que ha pagado; y, finalmente, la cantidad que adeuda (Pendiente):

Queremos tener la opción de controlar visualmente los clientes que nos adeudan hasta un 50% del importe facturado y, por otro lado, los clientes que nos adeudan un 50% o más de dicho importe. Para conseguir este objetivo debemos seguir los siguientes pasos (doy por hecho que no es necesario explicar la fórmula del rango D9:D23):

1. En la celda C2 escribimos el rótulo Opción (más tarde utilizaremos la celda D2 para vincular los botones de opción). Utilizamos estas celdas para visualizar fácilmente el ejemplo pero este tipo de entrada se suele ocultar.

2. En la celda C3 escribimos el rótulo Límite e introducimos, en la celda D3, el valor 50%.

3. Abrimos el menú Ver/Barra de herramientas/Formularios.

4. Hacemos un clic encima del botón de opción (que es el que puede ver en la imagen):

5. Volvemos a la hoja y hacemos un clic y sin soltar el clic arrastramos el ratón hacia abajo y hacia la derecha. Al soltar el clic habrá creado el primer botón de opción.

6. Puede repetir el paso 5 dos veces más o copiar el botón de opción creado y pegarlo dos veces. De esta manera tendremos los tres botones de opción que vamos a necesitar.

7. Cambie los textos de los botones para dejarlos como se muestra en la imagen:

8. Haga clic con el botón derecho del ratón sobre el primer botón de opción denominado Ninguna, y seleccione la opción Formato de control. Dentro de la pestaña Control marque el Valor Sin activar y en el cuadro Vincular con la celda haga clic sobre D2 (le aparecerá como =$D$2). Pulse Aceptar. Dado que los botones de opción son excluyentes entre si no tendrá que repetir este paso para el resto de botones de opción que, automáticamente, quedarán vinculados también con la celda D2.

9. Seleccione el rango A9:D23 y vaya al menú Formato/Formato condicional.

10. En el primer cuadro seleccione Fórmula y en el cuadro de la derecha escriba lo siguiente:

=SI($D$2=1;1;0)

11. No seleccione ningún formato para esta condición. Con el uso de este condicional estamos verificando si la celda D2, que es la que hemos vinculado a los botones de opción, contiene el número 1. Si es así significará que hemos marcado el botón de opción Ninguna, y por lo tanto al pulsarlo no se resalta ninguna operación.

12. Dentro de la herramienta de Formato Condicional pulse el botón Agregar. Seleccione Fórmula en el primer cuadro y escriba lo siguiente:

=SI(Y($D$2=2;$D9/$B9<$D$3;$D9/$B9>0);1;0)

Fíjese que hemos utilizado, además de la función SI, la función Y. Como ya sabrá, esta función nos permite realizar varias pruebas a la vez, y deben cumplirse todas para que el resultado sea VERDADERO. En nuestro caso tenemos que comprobar tres cosas que deben cumplirse, a saber: a) si hemos seleccionado el botón de opción 2; b y c) si el porcentaje de pendiente sobre facturado es mayor que cero y menor que el 50%.

13. Pulse el botón Formato y seleccione Trama, por ejemplo, amarilla y pulse Aceptar.

14. Dentro de la herramienta de Formato Condicional pulse el botón Agregar. Seleccione Fórmula en el primer cuadro y escriba lo siguiente:

=SI(Y($D$2=3;$D9/$B9>=$D$3);1;0)

De esta manera estamos comprobando si el botón de opción pulsado es el tercero y si el ratio entre pendiente y facturado supera o no el 50%.

15. Pulse el botón Formato y seleccione Trama, por ejemplo, roja y pulse Aceptar.

Si ha seguido correctamente estos pasos la herramienta Formato Condicional mostrará el siguiente aspecto:

16. Pulse Aceptar.

Ahora ya puede probar esta solución:

a) Al pulsar el botón de opción Ninguna, la tabla se queda con el formato inicial.

b) Al pulsar el botón de opción Hasta el 50%, los registros de la tabla que presenten un ratio entre el campo Pendiente y el campo Facturado mayor que cero pero menor que el 50% se destacaran en amarillo.

c) Al pulsar el botón de opción 50% o más, los registros de la tabla que presenten un ratio entre el campo Pendiente y el campo Facturado mayor o igual que el 50% se destacaran en rojo.

Resumen de ventas con Botones de Opción

Además de lo visto en el anterior artículo, nos puede interesar tener un cuadro con botones de opción que se correspondan, por ejemplo, a las distintas delegaciones y que al pulsar uno de dichos botones nos aparezca la información relativa a ventas totales, importe medio por operación y el número total de operaciones realizadas por la delegación seleccionada. Partimos nuevamente de los siguientes datos:

Para realizar este ejercicio tenemos que:

1. En el rango K10:K15 escribimos una lista de números del 1 al 6.

2. A la derecha, en el rango L10:L15 escribimos las ciudades que se corresponden con cada número, a saber, 1 La Coruña; 2 Oviedo; 3 Santander; 4 Barcelona; 5 Valencia; y 6 Madrid.

3. Seleccionamos el rango K10:L15 y le damos el nombre tabla_delegaciones (en el cuadro de nombres haciendo un clic, escribiendo el nombre propuesto y pulsando finalmente enter).

4. Vamos al menú Ver/Barra de herramientas/Formularios. Aparecerá la siguiente barra de herramientas:5. Hacemos un clic encima del botón de opción (es el icono con un círculo blanco y un punto negro dentro).

6. Volvemos a la hoja y hacemos un clic en el botón derecho del ratón y sin soltar el clic arrastramos el ratón para dibujar el botón.

7. Una vez dibujado lo seleccionamos y lo copiamos 5 veces (de esta manera tendremos seis botones que se corresponderán con las 6 delegaciones).

8. hacemos clic con el botón DERECHO del ratón encima del primer botón y, del menú emergente que aparece, seleccionamos la última opción: formato de control:

9. en el recuadro de vincular con la celda hacemos clic en la celda L8 y pulsamos aceptar. Los botones de opción, como verá enseguida, son excluyentes entre si, es decir, si pulsa uno se desactivarán el resto (sólo puede estar activado uno de los seis). Al hacer clic en cualquiera de ellos verá que transforma dicho clic en un número de orden que le aparecerá precisamente en la celda L8. Lo que haremos a continuación es relacionar dicho número con la delegación correspondiente.

10. Seleccionamos con el botón derecho el primer botón y hacemos clic con el botón izquierdo dentro del texto. De esta manera podemos cambiar dicho texto y escribir en cada botón el nombre de cada delegación:

11. Ya sólo nos queda realizar las fórmulas. Nos situamos en la celda K4 y escribimos:

=SUMAR.SI($C$4:$C$20;BUSCARV(L8;tabla_delegaciones;2;FALSO);$B$4:$B$20)

Fíjese que el criterio de la suma será la delegación que marquemos en el botón de opción. Como el botón de opción traduce la selección en un número de orden lo que hacemos es buscar dicho número en la tabla de delegaciones y ver con cuál se corresponde.

12. Nos situamos ahora en M4 y escribimos:

=CONTAR.SI($C$4:$C$20;BUSCARV(L8;tabla_delegaciones;2;FALSO))
13. Finalmente nos situamos en la celda L4 y escribimos: =K4/M4