¿Alguna vez has sentido que tus hojas de cálculo se complican a medida que aumentan los datos? Las listas desplegables son una herramienta poderosa para organizar información, pero cuando se trata de múltiples filas y dependencias entre ellas, la tarea puede volverse abrumadora. En este artículo, te enseñaremos cómo crear listas desplegables dependientes utilizando matrices dinámicas en Excel, una técnica que te permitirá optimizar tu flujo de trabajo y mejorar la precisión de tus datos. Prepárate para descubrir cómo transformar tu Excel en una herramienta aún más potente y funcional. ¡Sigamos adelante!
¿Te gustaría aprender cómo crear una lista desplegable dependiente para varias filas utilizando matrices dinámicas de Excel? ¡Estás en el lugar correcto! En este artículo, te mostraremos paso a paso cómo aprovechar esta poderosa función de Excel para optimizar tus tareas y ahorrar tiempo. Descubre cómo hacer que tus listas desplegables se actualicen automáticamente en función de tus datos y cómo aplicarlo a varias filas de tu hoja de cálculo. ¡No te lo pierdas, sigue leyendo!
En la web, hay muchas preguntas sobre cómo crear una lista desplegable expandible que se puede copiar en varias filas y muy pocas respuestas. Aquí tienes una solución funcional.
La introducción de matrices dinámicas nos ha hecho la vida mucho más fácil como usuarios de Excel. Entre muchas otras cosas, simplifican significativamente la creación de listas desplegables dinámicas. El tutorial vinculado anteriormente muestra cómo configurar rápidamente un menú desplegable múltiple con matrices dinámicas y hacerlo expandible para incluir automáticamente nuevas entradas.
Me sentí muy orgulloso del trabajo que habíamos realizado hasta que recibimos algunos comentarios como este: «Eso funciona muy bien para una fila, pero ¿cómo hacer que funcione para toda la columna?» Una pregunta justa. De hecho, las listas de selección de Excel se utilizan con mayor frecuencia en hojas de trabajo con cientos de filas para facilitar la entrada de datos. Entonces, reconsideramos todo el enfoque, elaboramos una fórmula que se ajusta automáticamente para cada fila y ¡estoy encantado de compartir esta nueva solución con ustedes!
¡Nota IMPORTANTE! Debido a que esta solución se basa en la función de matriz dinámica, solo se aplica en Excel para Microsoft 365 y Excel 2021. En las versiones predinámicas de Excel, utilice el enfoque tradicional para crear varias listas desplegables dependientes.
Datos fuente
Para empezar, consigamos algunos datos de origen con los que trabajar. En nuestra tabla principal a la izquierda, queremos tener dos listas de selección en cada fila, de modo que al seleccionar una Departamento en la primera lista solo se muestra Gerentes para ese departamento seleccionado en la segunda lista. Si cambia la selección en la lista principal, los nombres en el menú desplegable dependiente se actualizarán en consecuencia.
>
Paso 1. Estructurar los datos de origen
Los datos de origen de las listas desplegables a menudo provienen de diferentes fuentes y están organizados de manera diferente. Entonces, nuestro primer paso es estructurar los datos originales según nuestras necesidades. Para esto, vamos a crear una especie de tabla de preparación que enumerará todos los nombres de departamentos diferentes en la fila del encabezado y debajo de cada departamento. nombre habrá una lista de los empleados que trabajan en ese departamento específico. Para automatizar el trabajo y evitar errores humanos, utilizaremos las siguientes fórmulas.
Para obtener el departamentosingrese esta fórmula en H2.
=TRANSPOSE(SORT(UNIQUE(E3:E15)))
Aquí, la función ÚNICA extrae todos los diferentes departamentos de E3:E15. La función CLASIFICAR organiza los resultados en orden alfabético para que los elementos de su lista principal se ordenen de la A a la Z (si no desea eso, puede eliminar CLASIFICAR de la fórmula y los nombres de los departamentos aparecerán en la lista). mismo orden que en su tabla fuente). Finalmente, TRANSPOSE cambia la orientación de salida de vertical a horizontal.
Tenga en cuenta que la fórmula debe ingresarse solo en una celda y los resultados se extienden automáticamente a las celdas vecinas (esta característica se llama rango de dispersión).
De esta manera, tenemos los elementos de nuestra lista desplegable principal:
>
para tirar del nombres de gerentesla fórmula en H3 es:
=SORT(FILTER($F$3:$F$15, $E$3:$E$15=H$2))
Aquí, utilizamos la función FILTRO para filtrar los empleados que pertenecen a un departamento en particular ($E$3:$E$15=H$2). La función ORDENAR organiza los nombres en orden alfabético (si desea conservar el orden existente, elimínelo de la fórmula).
Al igual que la fórmula anterior, ésta también es dinámica y toda la furia del derrame se llena con los resultados a la vez. Todo lo que necesitas hacer es arrastrar la fórmula hacia la derecha para obtener una lista de gerentes para cada departamento.
Y esto nos da los elementos de la lista desplegable dependiente:
>
Consejos y notas:
- En este ejemplo, tenemos todos los datos en la misma hoja para facilitarle el seguimiento. En sus hojas de cálculo reales, será mejor que coloque una tabla de preparación una hoja separada para asegurarse de que haya suficientes celdas vacías hacia abajo y hacia la derecha para acomodar todos los datos. Como ya se mencionó, las fórmulas de matrices dinámicas se ingresan solo en una celda y no se puede saber en cuántas celdas se extenderán los resultados.
- Si tu objetivo es hacer ampliable listas desplegables, luego use un método ligeramente diferente para la tabla de preparación, que se analiza en este ejemplo.
Paso 2. Haz una lista desplegable principal
Con los datos de origen correctamente organizados, cree la primera lista desplegable de la forma habitual con la ayuda de Validación de datos de Excel:
La lista desplegable principal para la primera celda está lista:
>
Paso 3. Crea una lista desplegable de dependientes
Técnicamente, configurar una lista de selección sucesiva parece bastante fácil: sólo necesita crear una regla de validación de datos más para otra columna. Sin embargo, hay una parte complicada: la fórmula para Fuente caja. Esta vez, no puede utilizar una referencia de rango de derrame normal porque la segunda lista desplegable debe tener en cuenta la selección en el primer menú desplegable. Para resolver esta necesidad, nos referiremos indirectamente a un rango de derrame relevante con esta fórmula:
INDIRECTO(DIRECCIÓN(número_filaCOLUMNA(columna_anterior) + COINCIDIR(celda_desplegable, dropdown_spill_range0), 4) y «#»)
Dónde:
- número_fila – el número de la fila de la tabla de preparación en la que reside la fórmula que devuelve los elementos del menú desplegable dependiente. Nuestra fórmula está en la fila 3 (H3:K3), por lo que es 3. En lugar de codificar el número de fila, puedes usar la función FILA(), pero no complicaremos las cosas más de lo que ya están.
- columna_anterior – una referencia de celda a la columna que precede a la columna más a la izquierda de la tabla de preparación. En este ejemplo, la tabla de preparación comienza en H2. La columna anterior es G, por lo que usamos COLUMNA (G1). Aquí solo importa la coordenada de la columna, el número de fila puede ser cualquiera.
- celda_desplegable – es la dirección de la celda superior que contiene la lista desplegable principal, B3 en nuestro caso. Asegúrese de utilizar una referencia de celda relativa sin el signo $, para que la fórmula se ajuste correctamente para cada fila donde copiará su menú desplegable.
- dropdown_spill_range – la referencia al rango de derrame en la tabla de preparación que contiene los elementos de la lista desplegable principal. En nuestro ejemplo, son los departamentos devueltos por la fórmula de matriz dinámica en H2, por lo que usamos esta referencia de rango de derrame: $H$2#
Al juntar todas las piezas anteriores, obtenemos la siguiente fórmula para la regla de validación desplegable dependiente:
=INDIRECT(ADDRESS(3, COLUMN(G1) + MATCH(B3, $H$2#, 0), 4) & «#»)
Ahora, simplemente seleccione la celda superior para la lista desplegable secundaria (C3), vaya a la Datos pestaña > Validación de datosy configura la regla así:
>
¡Eso es todo! La lista desplegable de dependientes está lista para funcionar:
>
Consejo. Si sus datos de origen y la tabla de preparación están en otra hojaluego incluya el nombre de la hoja en el matriz_buscada argumento de COINCIDIR, y también concatenarlo con la referencia de celda dentro de INDIRECT de esta manera:
=INDIRECT(«Sheet1!»&ADDRESS(3, COLUMN(G1) + MATCH(B3, Sheet1!$H$2#, 0), 4) & «#»)
En la fórmula anterior, ¡simplemente reemplace la Hoja1! con el nombre de la hoja de trabajo donde se guardan sus datos de origen y la tabla de preparación.
Cómo funciona esta fórmula:
Según mi experiencia, muchos de los visitantes de nuestro blog están ansiosos por saber no sólo cómo aplicar tal o cual fórmula sino también entender su lógica interna. Para usuarios tan curiosos y reflexivos, les proporciono el desglose detallado de la fórmula:
=INDIRECT(ADDRESS(3, COLUMN(G1) + MATCH(B3, $H$2#, 0), 4) & «#»)
En un nivel alto, utilizamos la función INDIRECTA para referirnos «dinámicamente» al rango de gerentes correspondientes al departamento seleccionado en el menú desplegable principal.
Supongamos que escogiste Planificación desde el menú desplegable en B3. Los empleados de la Planificación departamento se enumeran en la columna J comenzando en J3. Eso significa que debemos proporcionar de alguna manera la referencia J3# a INDIRECT, y así es como lo hacemos:
Para obtener la letra de la columna, se utilizan juntas las siguientes 3 funciones:
ADDRESS(3, COLUMN(G1) + MATCH(B3, $H$2#, 0), 4)
La función COLUMNA devuelve el número de columna de G1, que es 7.
La función COINCIDIR busca el valor B3 en la lista de departamentos que comienzan en H2 y devuelve su posición relativa, que es 3 (Análisis, Diseño, Planificación,…).
La suma de estos dos números nos da 10, por lo que la función DIRECCIÓN toma esta forma:
ADDRESS(3, 10, 4)
Donde 3 es el número de fila, 10 es el número de columna y 4 es el tipo de referencia de celda relativa. El resultado es la referencia «J3» que va directamente a INDIRECTO 🙂 y reduce nuestra complicada fórmula a una fácil de entender:
INDIRECT(«J3″&»#»)
La concatenación de una referencia de celda con una etiqueta hash produce una referencia de rango de desbordamiento, de modo que INDIRECT devuelve una matriz completa en lugar de un solo valor de celda. ¡Hecho!
La belleza de esta fórmula es que funciona perfectamente para una columna de una sola letra, dos letras e incluso tres letras (¡gracias Jonathan por indicarnos la dirección correcta!).
Paso 4. Copie las listas desplegables en varias filas
Esta es la parte más fácil. Para que las listas de selección aparezcan en varias celdas, simplemente puede copiarlas como cualquier otro contenido de celda usando la función Copiar/Pegar o arrastrándolas por las celdas adyacentes. Para practicar, probaremos ambas técnicas.
Para copiar el lista desplegable principalseleccione la celda que lo contiene (B3) y arrastre el controlador de relleno (un pequeño cuadrado en la esquina inferior derecha de la celda) a través de tantas celdas como sea necesario:
>
Para copiar el menú desplegable dependiente lista, hagamos una copia y pegado regular:
>Ahora, ambas listas de selección aparecen en cada fila, lo que le permite seleccionar un departamento y un gerente para cada proyecto.
Tenga en cuenta que los métodos anteriores copian todo el contenido de una celda, incluida la Validación de datos y la selección actual. Por lo tanto, es mejor utilizarlos cuando todavía no se ha seleccionado ningún elemento en la lista.
Si ya seleccionó los elementos, puede usar la función Pegado especial para copiar la regla de validación de datos.
Resultado: lista desplegable dependiente de varias filas
Aunque nuestra solución de matrices dinámicas no es una configuración de un solo clic, es mucho más rápida de lo que parece y definitivamente mucho más rápida que el enfoque antiguo con rangos con nombre. De todos modos, el resultado merece la pena, ¿de acuerdo?
Aquí está: un menú desplegable de varios niveles para varias filas donde la selección de un elemento de la primera lista determina qué elementos aparecerán en un menú desplegable secundario.
>
Cómo hacer que varias listas desplegables sean expandibles
Si es probable que se agreguen más datos a su tabla de origen en el futuro y desea que se incluyan nuevas entradas en las listas desplegables automáticamente, entonces tendrá que usar versiones un poco más complejas de las fórmulas para la tabla de preparación. Aquí hay dos enfoques posibles, un rango regular y una tabla de Excel completa, cada uno con sus propias advertencias.
Enfoque 1. Organizar los datos de origen en una tabla
Una de las ventajas clave de las tablas de Excel es la expansión automática para dar cabida a nuevos datos, y vamos a aprovechar esta capacidad.
Lo primero que debe hacer es convertir los datos de origen en una tabla. La forma más rápida es seleccionar el rango E2:F15 y presionar Ctrl+T. Por conveniencia, llamamos a la tabla. Datos fuente.
A continuación, cree las fórmulas para la tabla de preparación utilizando referencias estructuradas. Si no estás familiarizado con esta sintaxis, ¡no hay problema! Simplemente comience a escribir una fórmula en una celda, seleccione el rango requerido en su tabla y Excel creará una referencia estructurada apropiada automáticamente según los nombres de las columnas.
para extraer el departamentosla fórmula en H2 es:
=TRANSPOSE(SORT(UNIQUE(Source_data[Dept.])))
Para obtener el nombres de gerentesingrese la siguiente fórmula en H3 y arrástrela hacia la derecha a través de algunas celdas más que departamentos actualmente (por ejemplo, a través de P3):
=IFERROR(SORT(FILTER(Source_data[[Manager]:[Manager]], (Source_data[[Dept.]:[Dept.]]=H$2))),»»)
Preste atención a que se deben utilizar referencias estructuradas absolutas para el Departamento y Gerente columnas para que las referencias no se desplacen cuando la fórmula se copie horizontalmente. El valor predeterminado en las tablas de Excel es una referencia de columna relativa, como mesa[column]. Para que la referencia sea absoluta, debe repetir el nombre de la columna de esta manera: mesa[[column]:[column]].
La función SIERROR se utiliza para evitar errores cuando la fórmula se copia en columnas adicionales a la derecha.
Si todo se hace correctamente, la tabla de preparación se expande automáticamente para incorporar nuevos registros:
>
Enfoque 2. Utilice un rango pero mejore las fórmulas
Si las referencias de la tabla le parecen demasiado complejas, también puede utilizar un rango normal. En este caso, es necesario realizar un par de mejoras en las fórmulas:
- incluir algunos filas vacías en los rangos suministrados para ambas fórmulas.
- Filtre las celdas en blanco para que no entren en sus listas de selección.
para extraer el nombres de departamentola fórmula en H2 es:
=TRANSPOSE(SORT(UNIQUE(FILTER(E3:E30, E3:E30<>»»))))
Preste atención a que utilizamos el rango E3:E30, aunque actualmente hay muchos menos registros en nuestra tabla de origen.
Para obtener el nombres de gerentesingrese la siguiente fórmula en H3 y arrástrela a través de algunas columnas más que las entradas que hay actualmente en la fila del encabezado:
=IFERROR(SORT(FILTER($F$3:$F$30, ($E$3:$E$30=H$2)*($E$3:$E$30<>»»))),»»)
En este caso, bloqueamos las referencias de rango con el signo $ para evitar que cambien al copiar la fórmula.
Al igual que con la solución anterior, los nuevos registros aparecen en la tabla de preparación una vez que se agregan a la tabla de origen.
>
Resultado: lista desplegable múltiple expandible
Cualquiera que sea el enfoque que haya elegido, configure y copie dos reglas de validación de datos como se explica en los pasos 2 a 4 anteriores, y los elementos recién agregados se mostrarán en las listas desplegables sin ningún esfuerzo adicional de su parte.
>
Esa es mi opción para crear un menú desplegable de varios niveles para cada fila en Excel. Con suerte, esta solución también te será útil. De todos modos, te agradezco por leer y espero verte en nuestro blog la próxima semana.
Libro de práctica para descargar.
Menú desplegable dependiente de varias filas (archivo .xlsx)
Usted también podría estar interesado en:
Lista desplegable dependiente para varias filas utilizando matrices dinámicas de Excel
Lista desplegable dependiente para varias filas utilizando matrices dinámicas de Excel
En este artículo, exploraremos cómo crear una lista desplegable dependiente para varias filas utilizando matrices dinámicas en Excel. Esta técnica es extremadamente útil cuando trabajamos con conjuntos de datos grandes y queremos simplificar la selección de datos específicos.
¿Qué es una lista desplegable dependiente?
Una lista desplegable dependiente es aquella en la que los valores que aparecen en la lista desplegable dependen de una selección previa realizada por el usuario. Por ejemplo, si tenemos una lista desplegable de países y queremos que la lista desplegable de ciudades muestre solo las ciudades del país seleccionado, estaríamos creando una lista desplegable dependiente.
¿Qué son las matrices dinámicas en Excel?
Las matrices dinámicas en Excel son una función poderosa que nos permite realizar cálculos y operaciones en conjunto de datos multidimensionales. Esta función nos ayuda a simplificar y agilizar tareas que requerirían fórmulas repetitivas o la necesidad de crear tablas adicionales.
Para utilizar matrices dinámicas, primero debemos asegurarnos de tener una versión de Excel compatible, a partir de Excel 365.
Creando lista desplegable dependiente para varias filas utilizando matrices dinámicas de Excel
A continuación, te mostraremos los pasos para crear una lista desplegable dependiente para varias filas utilizando matrices dinámicas en Excel:
Para crear la fórmula de matriz, puedes utilizar la función FILTRAR de Excel, junto con las referencias a las columnas de países y ciudades. Esto hará que la lista desplegable dependiente muestre solo las ciudades que corresponden al país seleccionado.
Asegúrate de que la fórmula de matriz esté correctamente escrita y que las referencias a las columnas y filas sean precisas. Una vez hecho esto, podrás probar tu lista desplegable dependiente seleccionando diferentes países y viendo cómo cambian las opciones de la lista de ciudades.
Recuerda que también puedes aplicar esta técnica para más de dos filas de datos. Simplemente repite el proceso para cada columna adicional que deseas incluir en tu lista desplegable dependiente.
Conclusión
En resumen, las matrices dinámicas en Excel nos permiten crear listas desplegables dependientes para varias filas, lo que facilita la selección de datos específicos en conjuntos de datos extensos. Esta función es especialmente útil cuando trabajamos con datos que cambian con frecuencia o actualizamos nuestras listas regularmente.
Esperamos que este artículo te haya proporcionado una comprensión clara de cómo crear listas desplegables dependientes utilizando matrices dinámicas en Excel. ¡Pruébalo en tu próximo proyecto y mejora tu eficiencia en el trabajo!
[automatic_youtube_gallery type=»search» search=»Lista desplegable dependiente para varias filas utilizando matrices dinámicas de Excel» limit=»1″]
Para crear una lista desplegable dependiente en Excel utilizando matrices dinámicas que permita seleccionar en varias filas, sigue estos pasos detallados:
Paso 1: Organizar tus datos
Asegúrate de que tus datos estén bien organizados en columnas. Por ejemplo, podrías tener una columna con «Departamentos» (por ejemplo, en E2:E15) y otra con «Gerentes» (por ejemplo, en F2:F15).
Paso 2: Crear la lista desplegable principal
- Selecciona la celda donde deseas que aparezca tu primera lista desplegable (por ejemplo, B3).
- Ve a la pestaña Datos y luego selecciona Validación de Datos.
- En Configuración, elige Lista y en el campo Origen, ingresa el rango que contiene los departamentos. Por ejemplo,
=UNIQUE(E2:E15)o simplemente el rangoE2:E15si prefieres.
Paso 3: Crear la lista desplegable dependiente
- Selecciona la celda para la lista desplegable dependiente (por ejemplo, C3).
- Abre de nuevo Validación de Datos.
- En Configuración, elige Lista.
- En el campo Origen, ingresa la siguiente fórmula para hacer la referencia dependiente:
excel
=INDIRECT(ADDRESS(3, COLUMN(G1) + MATCH(B3, UNIQUE($E$2:$E$15), 0), 4) & "#")
Notas:
– Asegúrate de que la referencia de celda sea correcta según tus datos.
– Utiliza UNIQUE para eliminar duplicados y obtener solo los departamentos.
Paso 4: Copiar las listas desplegables a filas adicionales
- Para copiar la lista desplegable principal a otras celdas, selecciona B3 y arrastra el controlador de relleno hacia abajo.
- Para copiar la lista desplegable dependiente, selecciona C3, copia (Ctrl+C) y luego selecciona las celdas donde deseas pegarla (por ejemplo, C4:C12) y pega (Ctrl+V).
Consejos para manejar expansiones de datos
Si anticipas que los datos de origen pueden cambiar o expandirse:
- Usa tablas de Excel: Selecciona tu rango de datos (por ejemplo, E2:F15) y presiona
Ctrl+Tpara convertirlo en una tabla. Esto hará que Excel actualice automáticamente los rangos.
- Fórmulas mejoradas: Si decides no usar tablas, asegúrate de incluir espacio adicional en tu rango. Por ejemplo:
excel
=SORT(UNIQUE(FILTER(E3:E30, E3:E30<>"")))
Esto permitirá que nuevos datos aparezcan en tus listas desplegables.
Resultado Final
Al final de estos pasos, tendrás un sistema de listas desplegables en varias filas donde seleccionar un elemento en la primera lista determina qué elementos aparecerán en la segunda lista, facilitando así la selección de datos en tu hoja de cálculo de Excel.
Así puedes manejar listas desplegables dependientes en Excel de manera efectiva utilizando matrices dinámicas.
Elokvenswb: ¡Totalmente de acuerdo, orga! A mí también me pasó que al principio no entendía cómo hacer listas desplegables y menos que dependieran unas de otras. Pero una vez que le agarras la onda, es súper práctico. Después de ver algunos videos, logré hacer un seguimiento de mis gastos y es una maravilla. ¡Gracias por compartir! – elokvenswb.
Me encantó el artículo, ¡muy útil! Yo también he usado listas desplegables en Excel y al principio me costó un montón entender cómo hacer que dependieran de otras celdas. Pero con un poco de práctica y siguiendo algunos tutoriales, logré armar un sistema bien chévere en mi hoja de cálculo para organizar inventarios. ¡Sigue compartiendo este tipo de contenido! – orga.
Aizpurua: ¡Totalmente de acuerdo! Al principio me parecía un lío, pero cuando empecé a usar las listas desplegables, me di cuenta de cuánto facilitaban la vida. Recuerdo que una vez tenía que organizar un evento y con ellas pude coordinar todos los detalles sin perderme. Es una herramienta increíble si le dedicas un poco de tiempo. ¡Gracias por compartir este artículo!
Illelmibetb: ¡Exacto, chicos! Al principio me sentía igual de perdido, pero luego me di cuenta de lo útil que son las listas desplegables para gestionar datos. A mí me ayudaron un montón para una hoja donde llevaba el control de mis proyectos. ¡Una vez que le pones el empeño, se vuelve pan comido! ¡Gracias por el contenido! – illelmibetb.