TOP
Listas desplegables vinculadas
Descripción
En Excel puedes rápida y fácilmente crear una lista desplegable , pero ¿alguna vez has intentado crear una lista desplegable dependiente? Hay varias formas de crear una lista de este tipo, así que echémosle un vistazo.
Método 1. Función INDIRECT
Esta técnica se basa en la aplicación de la función INDIRECT, que puede hacer una cosa simple: convertir el contenido de cualquier celda especificada en una dirección de rango que Excel entienda. Es decir, si la celda contiene el texto " А1 ", entonces la función dará una referencia a la celda como resultado А1 . Si la celda contiene la palabra " Auto ", entonces la función generará una referencia al rango nombrado con el nombre Auto etc.
Tomemos, por ejemplo, esta lista de modelos de automóviles. Toyota, Ford y Nissan :
Seleccione la lista completa de modelos Toyota (desde el celular А2 y hasta el final de la lista) y asigne un nombre a este rango Toyota en el menú Insertar - Nombre - Asignar (Insert - Name - Define) . Luego repetiremos lo mismo con las listas. Ford y Nissan , especificando los nombres de los rangos en consecuencia Ford y Nissan .
Al asignar nombres, recuerde que los nombres de los rangos en Excel no deben contener espacios, signos de puntuación y deben comenzar con una letra. Por lo tanto, si hubiera una brecha en una de las marcas de automóviles (por ejemplo Ssang Yong), entonces tendría que ser reemplazado en la celda y en el nombre del rango con un guión bajo (es decir Ssang_Yong).
Ahora creemos la primera lista desplegable para elegir una marca de automóvil. Seleccione una celda vacía y abra el menú. Datos - Verificar (Data - Validation) , luego de la lista desplegable Tipo de datos Seleccione una opción Lista y en el campo Fuente - resaltar las celdas con nombres de marcas (celdas amarillas en nuestro ejemplo). Después de hacer clic en ОК la primera lista desplegable está lista:
Ahora creemos una segunda lista desplegable, que mostrará los modelos de la marca seleccionada en la primera lista. Al igual que en el caso anterior, seleccione una celda vacía y abra el menú Datos - Verificar - más Lista . En el campo Fuente deberá ingresar la siguiente fórmula:
=INDIRECT(F3)
Dónde:
- F3 - la dirección de la celda con la primera lista desplegable - reemplácela por la suya.
Todo. Después de hacer clic en DE ACUERDO el contenido de la segunda lista será seleccionado por el nombre del rango seleccionado en la primera lista.
Contras de este método:
- Los rangos dinámicos especificados por fórmulas de tipo OFFSET no pueden actuar como rangos secundarios (dependientes). Puede usarlos para la lista primaria (independiente), pero la lista secundaria debe definirse de manera rígida, sin fórmulas.
- Los nombres del rango secundario deben coincidir con los elementos de la lista desplegable principal. Es decir, si contiene texto con espacios en blanco, habrá que sustituirlos por subrayados, etc.
- Debe crear manualmente muchos rangos con nombre.
Método 2. Lista de coincidencias (OFFSET y MATCH)
Este método requiere una lista ordenada de coincidencias de marca y modelo del siguiente tipo:
Para crear una lista desplegable principal de marcas, puede utilizar el método habitual descrito anteriormente, es decir:
- Indique el nombre del rango D1:D3 (p. ej. Marcas )
- Seleccione en la pestaña Datos (Data) equipo Verificación de datos (Data validation)
- Seleccione una opción de verificación de la lista desplegable Lista (List) y especificar como calidad Fuentes (Source) = Marcas o simplemente seleccione las celdas D1:D3 (si están en la misma hoja que la lista).
Pero para una lista dependiente de modelos, tendrá que crear un rango con nombre con la función OFFSET, que se referirá dinámicamente solo a celdas de modelos de una determinada marca. Para esto:
- Presione Ctrl + F3 o use el botón Administrador de nombres (Name manager) en la pestaña Fórmulas (Formulas) . En versiones anteriores a 2003, este era un comando de menú. Insertar - Nombre - Asignar (Insert - Name - Define)
- Cree un nuevo rango con nombre con cualquier nombre (p. ej. Modelos ) y en el campo Enlace (Reference) en la parte inferior de la ventana, ingrese manualmente la siguiente fórmula:
=OFFSET( $1$ ; MATCH($G$7;$A:$A;0)-1 ; 1 ; COUNTIF($A:$A;$G$7) ; 1 )
Las referencias deben ser absolutas (con signos $ ). Después de presionar Enter, los nombres de las hojas se agregarán automáticamente a la fórmula.
La función OFFSET es capaz de emitir una referencia a un rango del tamaño deseado, desplazado con respecto a la celda inicial por el número dado de filas y columnas. En una versión más comprensible, la sintaxis de esta función es la siguiente:
=OFFSET( celda_inicial ; shift_down ; shift_right ; rango_tamaño_en_filas ; rango_tamaño_en_columnas )
Entonces:
- celda_inicial - tomamos la primera celda de nuestra lista, es decir A1 ;
- shift_down - contamos la función MATCH, que, en pocas palabras, genera el número de serie de la celda con la marca seleccionada (G7) en el rango dado (columnas Y );
- shift_right =1 , por lo que queremos hacer referencia a los modelos en la columna adyacente ( EN );
- rango_tamaño_en_filas - calculamos usando la función COUNTIF, que es capaz de contar el número de apariciones en la lista (columna A) de los valores que necesitamos - marcas de automóviles ( G7 );
- rango_tamaño_en_columnas =1 , entonces necesitamos una columna con modelos.
El resultado debería ser algo como esto:
Queda por agregar una lista desplegable basada en la fórmula creada a la celda G8 . Para esto:
- Seleccione una celda G8
- Seleccione en la pestaña Datos (Data) equipo Verificación de datos (Data validation) o en el menú Datos - Verificar (Data - Validation)
- Seleccione la opción de verificación de la lista desplegable Lista (List) y entrar como Fuentes (Source) el signo es igual al nombre de nuestro rango, es decir = Modelos .
Artículos sobre el tema: