Cómo crear una lista desplegable a partir de una columna de datos en Excel

La herramienta de validación de datos de Microsoft Excel le permite agregar una lista desplegable a una celda basada en los datos existentes en una columna. Sin embargo, cómo funciona esto depende de si los datos de origen son parte de una tabla de Excel formateada. Echemos un vistazo a algunos ejemplos.

Usando una columna en una tabla de Excel formateada

Imagine que tiene esta tabla de Excel formateada llamada «puntajes» que contienen nombres de jugadores, naciones y puntajes, y necesita extraer algunos datos de resumen. Específicamente, en la celda H2, desea crear una lista desplegable de todas las naciones enumeradas en la columna B, y en las celdas I2 y J2, mostrar los nombres de los jugadores y la puntuación promedio, respectivamente, según la nación seleccionada.

Para crear una lista desplegable, seleccione la celda donde desee que esté (en este caso, Cell I2) y en la pestaña Datos en la cinta, haga clic en «Validación de datos» en la opción desplegable con el mismo nombre.

Luego, en el campo Permitir la pestaña Configuración, seleccione «Lista».

El siguiente paso implica ingresar las opciones que desea aparecer en la lista desplegable en el campo de origen. Para hacer esto, puede escribir las opciones manualmente, seleccionar las celdas relevantes en su hoja de cálculo o usar una fórmula.

Idealmente, podrías escribir:

=Scores[Nation]

En el campo de origen, exactamente como lo haría al hacer referencia a esa columna en una fórmula, donde «puntajes» representa el nombre de la tabla, y la «nación» representa el encabezado de la columna. Sin embargo, desafortunadamente, esto devuelve un mensaje de error, ya que la herramienta de validación de datos solo reconoce las referencias de celdas, las fórmulas y los rangos nombrados como fuentes de datos, no encabezados de columna de tabla.

Afortunadamente, hay dos formas de evitar esto.

Método 1: Referencia a las celdas en la tabla

Primero, active el campo «Fuente» en el cuadro de diálogo Validación de datos para que el cursor parpadee, y pasee sobre el encabezado de la columna relevante en la fila 1 hasta que vea una pequeña flecha negra hacia abajo. Cuando lo haga, haga clic una vez para seleccionar todas las celdas de datos en esa columna.

Tenga cuidado de no seleccionar toda la columna haciendo clic en la flecha que aparece cuando se desplaza sobre la letra de referencia de la columna (en este caso, «B»), ya que esto seleccionará toda la columna, incluido el encabezado en la fila 1 y las celdas debajo de su tabla. Ha seleccionado con éxito el rango correcto si solo las celdas en la columna dentro de la tabla están rodeadas por una línea punteada.

Ahora, después de hacer clic en «Aceptar», cuando selecciona Cell H2, aparece un botón desplegable, que puede hacer clic para seleccionar una opción de la fuente. Observe, también, cómo se muestran los valores únicos en la lista; en otras palabras, Excel reconoce los valores duplicados y solo los muestra una vez.

Las listas desplegables de validación de datos en Excel adopten el mismo orden que la fuente. En este ejemplo, para ordenar a las naciones alfabéticamente, necesitaría ordenar los datos de origen por columna B.

La belleza de usar este método es que si agrega más filas a la tabla de Excel formateada, la fuente de validación de datos se ajusta automáticamente para incluir las celdas o celdas adicionales.

Ahora, puede usar la función de filtro en la celda i2 para enumerar los jugadores de la nación seleccionada:

=FILTER(Scores[Player],Scores[Nation]=H2)

Finalmente, use la función promedio en la celda J2 para mostrar el puntaje promedio de jugadores de esta nación:

=AVERAGEIF(Scores[Nation],H2,Scores[Total])

Método 2: Nombra el rango

La segunda forma de crear una lista desplegable a partir de una columna en una tabla formateada implica nombrar el rango de origen.

Antes de iniciar el cuadro de diálogo de validación de datos, seleccione todas las celdas en la tabla que contienen los valores que desea incluir en la lista desplegable, escriba un nombre para el rango en el cuadro de nombre en la esquina superior izquierda de la ventana Excel y presione Entrar.

Para mantener las cosas simples (¡y memorables!), El nombre que ingresa para el rango seleccionado debe ser el mismo que el encabezado de la columna.

Luego, en el campo de origen del cuadro de diálogo Validación de datos, escriba un signo igual (=), seguido del nombre que acaba de asignar al rango, y presione Entrar. En este ejemplo, debe escribir:

=Nation

Al igual que con el método anterior, Excel reconoce que los datos están en una tabla formateada, por lo que expande automáticamente el rango si los datos se agregan a la siguiente fila.

El uso de rangos con nombre en Excel tiene muchos beneficios. Por ejemplo, los rangos de nombres hacen que su libro de trabajo sea más accesible para las personas que usan lectores de pantalla. Además, puede navegar rápidamente a los rangos con nombre escribiéndolos en el cuadro de nombre o seleccionándolos de las opciones cuando hace clic en la flecha hacia abajo, y también se pueden usar en fórmulas.

Ahora, como con el Método 1, puede usar las funciones Filter y promedio para completar la extracción de datos.

Sin embargo, esta vez, las fórmulas pueden ser más directas, ya que puede hacer referencia al rango que llamó «nación» sin hacer referencia a la tabla en la que se encuentra el rango.

Entonces, para el filtro en la celda i2, es:

=FILTER(Scores[Player],Nation=H2)

Y para el promedio de la celda J2, es:

=AVERAGEIF(Nation,H2,Scores[Total])

Uso de una columna en un conjunto de datos sin formato

Ciertos tipos de datos, como las matrices derramadas, no se forman como una tabla de Excel, por lo que puede haber momentos en que necesite encontrar una manera de crear una lista desplegable a partir de una columna dentro de un conjunto de datos sin formato.

Después de seleccionar la celda que contendrá la lista desplegable y hacer clic en «Validación de datos» en la pestaña Datos en la cinta, seleccione «Lista» en el campo Permitir.

A continuación, en el campo de origen, use las funciones indirectas y counta juntas para decirle a Excel dónde encontrar las opciones para la lista desplegable.

En este caso, escribiendo:

=INDIRECT("B2:B"&COUNTA(B:B))

Incluye todos los valores en la columna B desde la celda B2 hasta la celda B22 como fuente.

Rompamos esta fórmula de origen para ver cómo funciona.

• = Indirecto (: Esto le dice a Excel que desea que se determine la fuente utilizando una referencia dinámica.
• «B2: B»: Específicamente, la referencia dinámica comienza en la celda B2 y termina en otra celda en la columna B.
• & Countta (b: b)): Esto cuenta todas las celdas en la columna B que no están en blanco y agrega el total a la referencia. En este caso, 22 celdas en la columna B contienen valores, por lo que esto convierte B2: B en B2: B22.

Entonces, si agrega otra fila de datos en la parte inferior, la función Countta elegirá esto e informará la referencia indirecta en la regla de validación de datos que la fuente se ha expandido hacia abajo por una fila adicional.

El uso de una fórmula para determinar qué valores incluir en la fuente de validación de datos, en lugar de simplemente seleccionar la columna completa, se considera la mejor práctica, ya que evita que la fila del encabezado y las filas en blanco se incluyan en la lista desplegable.

Puede verificar esto seleccionando la celda que aloja la lista desplegable y reabriendo el cuadro de diálogo Validación de datos. Cuando selecciona el campo de origen, aunque la fórmula no ha cambiado, la línea punteada en la hoja de cálculo confirma que recoge los nuevos valores en la fila adicional.

Ahora que su lista desplegable está lista para funcionar, use las funciones indirectas y countas junto con la función de filtro en la celda I2 y la función promedio en la celda J2 para completar la hoja de cálculo.

---

Las listas desplegables agregadas a través de la herramienta de validación de datos son increíblemente potentes y versátiles, y pueden usarse en muchos escenarios en Excel. Por ejemplo, puede usar listas desplegables para hacer que los gráficos regulares sean dinámicos, una forma segura de impresionar a sus amigos y compañeros de trabajo.