Utilizar un desplegable es una tarea cotidiana e intuitiva. Lo que pretendemos es que al elegir con el desplegable un año, la consecuencia sea que una tabla con datos se rellene automáticamente con datos de ese año.
Conseguiremos nuestro proposito con el uso de la función INDIRECTO de la que podemos ver unos cuantos Post publicados en este mismo Blog. Para verlos simplemente hemos de seguir este enlace:
En la tabla verde disponemos de los datos correspondientes a tres años. Deseamos que al elegir el año en el desplegable la tabla azul se alimente automáticamente con los datos correspondientes al año elegido.
Observar que la zona azul se ha obtenido con la función INDIRECTO y se ha introducido como función matricial. Esto se puede comprobar al ver las llaves {} que son el rasgo distintivo de las funciones matriciales.
Datos de partida
Los datos de partida son los que se encuentran en la tabla superior (rango B4:E9). Lo que hacemos es crear nombres de rango con los rótulos de columna:
- Año2009
- Año2010
- Año2010
Para crearlos de forma masiva lo que hacemos en la versión de Excel 2007 es seleccionar el rango C4:E9 e ir al menú Fórmulas, y luego elegir Crear desde una selección. De esta forma llegaremos a esta ventana:
Marcamos únicamente donde pone 'Fila superior'.
Si tuviéramos Excel 2003, para llegar a esta ventana el recorrido es: Insertar, Nombre, Crear.
Aceptando esta ventana lo que hemos logrado es crear los tres rangos siguientes:
- Año2009 =Hoja1!$C$5:$C$9
- Año2010 =Hoja1!$D$5:$D$9
- Año2011 =Hoja1!$E$5:$E$9
Tabla para el BUSCARV
Creamos la tabla amarilla para luego poder usar un BUSCARV que nos proporcione el año al elegir desde el desplegable.
Creamos el desplegable
Creamos el desplegable o ComboBox,o también llamado 'Cuadro combinado'.
Esto en Excel 2007 se consigue desde la ficha Programador, y luego en Insertar uno de los Controles de formulario denominado Cuadro combinado.
En Excel 2003 se consigue obteniendo la barra de Formularios y luego eligiendo Cuadro combinado.
Al pulsar con el botón izquierdo del ratón sobre el icono del Cuadro combinado conseguiremos que el cursor se convierta en una cruz finita, y en ese momento crearemos la diagonal del desplegable arrastrando con el ratón sobre la hoja.
Luego, pulsamos el desplegable con el botón derecho del ratón y elegimos 'Formato de control'.
Como rango de entrada ponemos G13:G15 que es donde están escritos los nombres de cabecera que previamente hemos creado.
Donde pone 'Vincular con la celda' ponemos la celda G11. Esto permitirá que al elegir en el desplegable la primera opción en la celda G11 aparezca un 1; si elegimos la segunda opción aparecerá un 2; y si elegimos la tercera opción del desplegable en la celda G11 aparecerá un 3.
BUSCARV
En la celda C11 escribimos la siguiente función:
=BUSCARV(G11;tabla;2;0)
Donde el rango tabla es: F13:G15.
Con ello conseguiremos que al cambiar el valor de la celda G11 según las elecciones que hagamos del desplegable, podamos poner en esta celda (C11) la cabecera de los datos que deseamos obtener.
Son tres posibles cabeceras que podemos obtener:
- Año2009
- Año2010
- Año2010
INDIRECTO como función matricial
Finalmente hemos de emplear la función INDIRECTO. Para familiarizarnos con esta función avanzada de Excel conviene revisar algunos post donde se habla de ella o se utiliza. Para ello se puede seguir el enlace que se ha indicado al inicio de este artículo.
Como vamos a tratarla como una función matricial hemos de seguir los tres pasos típicos de toda función matricial. Para familiarizarnos con esto se aconseja ver el siguiente artículo:
Primero, señalamos el rango C12:C16, que es donde la función matricial dejará su resultado.
Segundo, escribimos la función matricial que empleamos en este caso, que es la siguiente:
=INDIRECTO(C11)
Tercero, para validar no pulsamos Enter, sino que hemos de pulsar tres teclas simultaneamente: CONTROL+MAYUCULAS+ENTER.
Resultado
Si todo ha ido bien hemos conseguido un desplegable donde al elegir el año la tabla de abajo toma los datos correspondientes a ese año de la tabla de arriba. Con ello, hemos conseguido disponer de un desplegable que permite traernos los datos que deseamos.
Esto con tablas de datos realmente grandes puede llegar a ser muy interesante.
Hoja2
Proponemos un segundo método que incluso puede ser mejor que el primero por ser más sencillo.
Consiste en utilizar como desplegable una celda con Validación de datos de tipo LISTA.
En el siguiente vídeo puede ver el proceso de creación por los dos métodos.
El 10%
Podemos crear fórmulas matriciales que hagan referencia a todo un rango o matriz. Veamos cómo se calcula el 10% del rango C12:C16.
Al variar los datos con el desplegable de la celda C11 nuestro 10% también varía.
