Cómo usar una tabla de Excel dentro de una lista de validación de datos (3 formas)
Las tablas de Excel se expanden automáticamente cada vez que se agregan nuevos datos en la parte inferior. Esta característica por sí sola hace de las tablas una de las herramientas más poderosas dentro del conjunto de herramientas de los usuarios de Excel. Una tabla se puede usar como datos de origen para un gráfico y dentro de un rango con nombre, ambos que se benefician de la función de expansión automática. Las listas de validación de datos también se beneficiarían de la función de expansión automática... pero simplemente no funciona; un descuido por parte de Microsoft (en mi opinión).
Parece que las siguientes configuraciones de validación de datos deberían funcionar. Debería usar la columna llamada Animales de una tabla llamada myList .
Pero Excel mostrará un mensaje de error en su lugar.
¿Qué puedes usar para resolver este problema? Tengo tres soluciones para ti:
- Referencias de celda normales sobre una tabla
- Rango con nombre de la columna Tabla
- función INDIRECTA
Echemos un vistazo a cada uno de estos y podrá decidir cuál es la mejor opción para su situación.
Tabla de contenido
- Datos de ejemplo
- Referencias de celda normales sobre una tabla
- Problemas con este enfoque
- Rango con nombre de la columna Tabla
- Crear el rango con nombre
- Agregue el rango nombrado a la lista de validación de datos
- Puntos clave sobre este método
- Función INDIRECTA
- Advertencia sobre este método
- ¿Qué método elegir?
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre del archivo: 0076 Validación de datos con Tables.xlsx
Tener acceso
Los ejemplos de esta publicación utilizan los siguientes datos:
El nombre de la tabla es myList (lo sé, no es un buen nombre, pero funcionará para los ejemplos).
Referencias de celda normales sobre una tabla
Si utilizamos referencias de celda estándar, la lista de validación de datos será estática. Cada vez que se agregue un nuevo elemento a la lista, será necesario actualizar la fuente de validación de datos o los nuevos elementos deberán agregarse dentro del rango de la lista existente. Sin embargo, si creamos la lista de validación de datos utilizando referencias de celdas normales sobre una columna de Tabla, se expandirá.
Mire la captura de pantalla a continuación. Las celdas $A$2:$A$7 son las mismas celdas que la referencia myList[Animals] sería para una tabla.
Parece que se comportaría como una lista estática estándar, pero de alguna manera Excel lo sabe y la lista de validación de datos se expande cuando se agregan valores a la tabla. Pruébelo usted mismo.
Problemas con este enfoque
Hay algunos grandes problemas con este enfoque:
- Si la lista de validación de datos está en una hoja separada de la Tabla, no se expandirá.
- Este método solo funciona si las celdas seleccionadas incluyen toda la columna de la Tabla (es decir, en nuestro ejemplo, si hace referencia a $A$4:$A$7, no se expandirá cuando se agregue un nuevo elemento, ya que no es toda la columna de la Tabla).
Debido a los problemas mencionados anteriormente, probablemente sea mejor evitar este método.
Rango con nombre de la columna Tabla
Los rangos con nombre aceptarán una columna de tabla como fuente y una lista de validación de datos aceptará un rango con nombre. Por lo tanto, podemos adoptar un enfoque de dos pasos para lograr el mismo resultado.
Crear el rango con nombre
Para crear el rango con nombre, haga clic en Fórmulas – Definir nombre
Se abrirá la ventana Nuevo nombre. Asigne un nombre al rango nombrado ( myDVList en el ejemplo siguiente) y configure el cuadro Se refiere al nombre de la tabla y la columna.
Finalmente, haga clic en Aceptar . El rango con nombre ya ha sido creado.
La fórmula utilizada en la captura de pantalla anterior es.
=miLista[Animales]
Si la lista solo tiene una columna, es posible hacer referencia solo a la tabla sin el nombre de la columna (como en la captura de pantalla siguiente).
=miLista
Agregue el rango nombrado a la lista de validación de datos
La siguiente captura de pantalla muestra el rango con nombre agregado al cuadro de lista de validación de datos.
La lista de validación de datos ahora se expandirá cada vez que se agreguen nuevos elementos a la tabla.
Puntos clave sobre este método
En relación con este método, hay algunas cosas a tener en cuenta:
- Este es un proceso de dos etapas, cuya configuración lleva un poco más de tiempo.
- Si los nombres de la tabla o la columna cambian, el rango nombrado se actualiza automáticamente para reflejar los cambios.
Si bien la configuración lleva más tiempo, este método es el que tiene menos probabilidades de fallar.
Función INDIRECTA
La función INDIRECTA convertirá el texto en un Rango que Excel reconocerá y usará en los cálculos. Por ejemplo, si la celda A2 contiene el texto "A1", puede escribir =INDIRECTO(A2) y Excel lo convertirá a la referencia de celda =A1.
Podemos usar la función INDIRECTA con la referencia de Tabla y columna como texto y Excel lo entenderá.
La fórmula utilizada en la captura de pantalla anterior es.
=INDIRECTO("miLista[Animales]")
Si la lista solo tiene una columna, es posible hacer referencia solo a la Tabla.
=INDIRECTO("miLista")
La lista de validación de datos ahora se expandirá cada vez que se agreguen nuevos elementos a la tabla.
Advertencia sobre este método
Hay una cosa clave a tener en cuenta; Los nombres de tablas y columnas ahora están codificados en la fórmula INDIRECTA. Si cualquiera de estos cambia, la lista de validación de datos dejará de funcionar ya que la fórmula no puede encontrar los datos de origen.
¿Qué método elegir?
Entonces, con tres opciones para elegir, la pregunta es cuál es la mejor. Personalmente, prefiero la opción de rango con nombre. Utilizo rangos con nombre a menudo, por lo que se adapta a mi forma de trabajar. Sin embargo, si tengo que crear muchos rangos con nombre, puede que sea perezoso y elija el método INDIRECTO. No recomiendo usar las referencias de celda estándar en un tabla, ya que no funcionará en todas las hojas de trabajo.
Deja una respuesta