domingo, 16 de octubre de 2011

Seleccinar datos con un desplegable

Descargar el fichero: desplegable.xlsx

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.


Para saber más

Puede consultar el siguiente enlace.

19 comentarios:

  1. ¡¡ Excelente archivo ¡¡

    muchas gracias

    ResponderEliminar
  2. Por favor. En Excel 2003 donde está la opción de "crear datos desde una selección" que no lo encuentro.

    Gracias,



    Andrés

    ResponderEliminar
  3. Hola Andrés.

    En 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.

    ResponderEliminar
  4. Buenas noches Adolfo:

    Me 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

    ResponderEliminar
  5. Estimado Andrés.

    Te 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.

    ResponderEliminar
  6. Buenas noches:

    He 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

    ResponderEliminar
  7. Buenas tardes Adolfo:

    Creo 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

    ResponderEliminar
  8. Hola Andrés.

    Si 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.

    ResponderEliminar
  9. Buenas noches:

    La 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

    ResponderEliminar
  10. Hola Andrés.

    "Buscar objetivo" en Excel 2010 está en Datos, Análisis y si.

    Un saludo.

    ResponderEliminar
  11. Me ha servido de mucho este articulo Adolfo, Enhorabuena y gracias!

    Ahora 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!

    ResponderEliminar
  12. Gracias por el artículo, muy bueno

    Yo 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

    ResponderEliminar
  13. 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.

    Muchas Gracias

    ResponderEliminar
  14. 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?

    ResponderEliminar
  15. Buenas. 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

    ResponderEliminar