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.
¡¡ Excelente archivo ¡¡
ResponderEliminarmuchas gracias
Por favor. En Excel 2003 donde está la opción de "crear datos desde una selección" que no lo encuentro.
ResponderEliminarGracias,
Andrés
Hola Andrés.
ResponderEliminarEn Excel 2007 y 2010 está en la ficha Fórmulas, en el grupo "Nombres definidos" y se llama "Crear desde la selección".
En Excel 2003, tienes que ir a Insertar, Nombre, Crear.
Un saludo.
Muchas gracias.
ResponderEliminarAndrés
Buenas noches Adolfo:
ResponderEliminarMe quiero reciclar de manera avanzada tanto en Excel como en Access. La duda es que manejo las versiones del 2003 en ambas y no sé si en las versiones de 2007,2010o 2013 estamos hablando de otra generación de hoja y base de datos. Me aconsejas dejar las versiones de 2003 y, en caso de que así sea valdría 2007, 2010 o 2013?
Un saludo,
Muchas gracias,
Andrés
Estimado Andrés.
ResponderEliminarTe aconsejo que pases de la versión 2003 a una de las nuevas. Cualquiera de las otras tres está bien.
Actualmente la mayoría de usuario utilizan la versión 2010.
Buenas noches:
ResponderEliminarHe visto en varios tutoriales que los bordes de un rango de celdas que se escoge, están redondeados. Dónde está la opción para redondear los bordes?
Muchas gracias y feliz año
Andrés
Buenas tardes Adolfo:
ResponderEliminarCreo haber visto en alguna ocasión, una opción para hacer sumas condicionales en función de una parte de los dígitos de una celda. Me explico con un ejemplo:
11150001....... 1.000 €
11150002....... 800 €
11160001....... 500 €
Cuál sería la fórmula a utilizar para sumar los valores de los registros que empiezan por 1115_____.
Saludos y gracias,
Andrés Gómez
Hola Andrés.
ResponderEliminarSi en A1 pones 11150001. En B1 pones 1.000 € y así sucesivamente, según los valores que me proporcionas, la fórmula podría ser la siguiente:
=SUMA(--(IZQUIERDA(A1:A3;4)="1115")*(B1:B3))
Esta es una fórmula Matricial y cuando valides no se hace pulsando Intro, sino pulsando tres teclas a la vez:
Control + Shift + Enter
Un saludo.
Buenas noches:
ResponderEliminarLa opción de herramientas, opciones, "buscar objetivos" que había en Excel 2003, sabéis en qué parte de Excel 2010 se encuentra?
Saludos y Gracias,
Andrés
Hola Andrés.
ResponderEliminar"Buscar objetivo" en Excel 2010 está en Datos, Análisis y si.
Un saludo.
Gracias Adolfo.
ResponderEliminarAndrés
Me ha servido de mucho este articulo Adolfo, Enhorabuena y gracias!
ResponderEliminarAhora me surge un problema, hice todo correctamente pero no me pasa los datos por un detalle, yo lo que tengo es una lista de productos y quiero que en una pestaña aparte al seleccionar ese producto me introduzca todos los datos del mismo, pero al crear los nombres de la selección me cambia el nombre de los productos, por ejemplo:
Si mi producto se llama ACA-45SF al hacer la selección para realizar la función, me cambia el nombre a ACA_45SF por lo que en mi lista desplegable que está con el nombre correcto (ACA-45SF) y seleccionar este articulo no me copia los datos del producto.
Si intento cambiar el nombre en el administrador de nombres por el nombre correcto (ACA-45SF) me da el error de que no es nombre válido, no se permite para esta función el guión - ?? alguna solución?
Gracias por tu ayuda!
Gracias por el artículo, muy bueno
ResponderEliminarYo ya tengo creada la lista desplegable que llama datos de otras hojas del mismo libro, la lista trae históricos de meses anteriores (cierres de mes) aún y cuando me trae la data del último cierre de mes no me está mostrando este último en la lista.
Es decir, trae el saldo del 30/09/2014 de todas las transacciones pero en la lista no muestra sino hasta el 31/08/2014???
Podrías ayudarme con esto. gracias
Hola Yumat.
EliminarMira el tercer vídeo de esta entrada.
Insertar Tabla en Excel 2010
Excelente es un muy grato saber que personas con tanto conocimiento lo comparten para que todos podamos aprender de una aplicación tan importante como es Excel.
ResponderEliminarMuchas Gracias
hola me puedes ayudar tengo una hoja de excel con una celda de validacion de datos en la cual se selecionan las delegaciones de la ciudad pero aparte tengo otra donde van las colonias como puedo hacer q al seleccionar las delegaciones en la celda de colonias me traiga las colonias de esa delegacion? que sea tambien desplegable para selecionar la colonia?
ResponderEliminarmuchachos puedo hacer esto con una macro?
ResponderEliminarBuenas. Voy a rizar el rizo. En una misma fila tengo los 12 meses del año, pero en la primera celda una lista con ellos (Enero, Febrero,...), me gustaría que al elegir el mes que desee, automáticamente se cambien el resto
ResponderEliminarMe sale #REF
ResponderEliminarQue tal, no me funciona esta enlace, como hacer para tener el archivo?... mi correo es: blackmangel@gmail.com.
ResponderEliminarGracias !!
Hola. Lo he probado y la descarga se produce correctamente. Mira a ver si en tu navegador tienes permitidas las descargas de ficheros y mira a ver en que ubicación se produce esa descarga. Un saludo.
ResponderEliminarQue tal amigo,
ResponderEliminarSigo teniendo el mismo problema... la cuestión es entonces, al utilizar el INDIRECTO, solo es posible con valores numéricos o también con alfabéticos?
Gracias
Hola Miguel Angel.
EliminarArriba, donde dice "Descargar el fichero: desplegable.xlsx" he probado el enlace desde otro ordenador y la descarga me ha funcionado perfectamente. Este ordenador usa Windows 10 y el navegador el Chrome, también lo he probado en FireFox y va bien.
Por favor, intenta la descarga desde otro ordenador, ya verás como te funciona correctamente.
Un cordial saludo.
Hola Miguel Angel.
EliminarLa función INDIRECTO lo que hace es hacer una referencia 'fija' a una celda, y es indiferente si la celda contiene valores numéricos o alfanuméricos.
Un cordial saludo.
Que tal Adolfo, una pregunta: ¿Puedo usar la función INDIRECTO con dos (2) filas al mismo tiempo?
ResponderEliminarGracias !
Hola Miguel Angel.
EliminarImagino que lo que necesitas es seleccionar dos elementos de un desplegable y que en el gráfico aparezcan los dos. No es posible seleccionar dos elementos en un mismo desplegable pero podrías crear dos desplegables y elegir un elemento en cada uno de ellos. Otra solución es usar la denominada "Casilla (control de formulario)" que es el típico cuadradillo que puedes marcar o desmarcar y que acepta que marques varios a la vez.
Un coridal saludo.