Cómo crear dos listas dinámicas avanzadas en Excel

Las soluciones de listas avanzadas son fáciles gracias al objeto Table de Excel. Si necesita una lista dinámica, pruebe una de estas técnicas.

El artículo Cinco maneras de aprovechar las características de la lista de Excel muestra cinco características básicas de la lista incorporadas directamente en Excel. No hay que hacer mucho para aprovecharlas, pero a veces se necesita algo más sofisticado. En este artículo, te mostraré dos características avanzadas de la lista usando una lista de validación y una función de búsqueda para generar una lista dinámica. Ninguna de las dos técnicas es superior – sus necesidades dictarán su elección.

Más información sobre Office

Estoy usando Excel 2019 (escritorio), pero ambas técnicas funcionarán en versiones anteriores. Para su comodidad, puede descargar la demostración.xlsx. La función VLOOKUP() está disponible en versiones de menú anteriores, pero ambas técnicas se basan en el objeto Table para que sea dinámico, y las versiones de menú no soportan el objeto Table. La edición del navegador admite listas de validación y objetos de tabla, pero insertar nuevos datos en una tabla es complicado: esa edición ignora la envolvente de la pestaña para el nuevo comportamiento del registro cuando se introduce un nuevo registro.

Una lista de validación dinámica

Una lista de validación permite limitar los usuarios a valores específicos. Usted querrá usar estas listas para facilitar la entrada de datos y evitar errores. No se puede evitar que un usuario elija un valor incorrecto, pero al menos se escribirá correctamente. Y aunque esto pueda parecer una tontería, un valor mal escrito devuelve resultados erróneos, tanto si está filtrando como si utiliza funciones complejas para analizar los datos.

En su forma más simple, una lista de validación no es dinámica, pero combinando la característica con el objeto Table cambia eso. Si tanto el set de datos como la fuente de la lista son objetos Tabla, Excel actualiza todo a medida que se trabaja.

La muestra una hoja simple con dos objetos Table. Tenemos un conjunto de datos en las columnas B a E y una lista de valores de códigos de estantería únicos. Añadiremos el control de validación a la columna E y lo usaremos para introducir el código de estantería apropiado para cada registro.

Usaremos estos dos objetos Table para construir una lista de validación dinámica.

Estos dos conjuntos de datos son objetos de tabla en el archivo de demostración. Si está trabajando con sus propios datos, puede convertir un rango de datos en una Tabla como se indica a continuación:

1. Haga clic en cualquier parte del conjunto de datos.
2. Haga clic en la ficha Insertar.
3. En el grupo Tablas, haga clic en Tabla.
4. En el cuadro de diálogo resultante, marque o desmarque la opción Mi tabla tiene encabezados ) y haga clic en Aceptar.

Especifique si su tabla tiene cabeceras.

Hay un paso más antes de que pueda construir la lista de validación. No se puede especificar un objeto Tabla como fuente de una lista de validación, pero sí se puede hacerlo si se le da a la Tabla un nombre de rango, como se indica a continuación:

1. Seleccione H2:H7 (su fuente de lista.
2. Haga clic en la ficha Fórmulas.
3. En el grupo Nombres definidos, haga clic en la opción Crear a partir de selección.
4. En el diálogo resultante, marque Fila superior (si es necesario), y Excel usará el texto del encabezado para nombrar el rango Shelf_Code_List .

Asigne a la lista un nombre de rango.

Ahora está listo para crear la lista de validación, como se indica a continuación:

1. Seleccione E2:E15-estas son las celdas existentes que desea llenar utilizando el control de validación. (Su Tabla podría no tener ningún dato todavía, y en ese caso, estará seleccionando una sola celda.)
2. Haga clic en la ficha Datos.
3. En el grupo Herramientas de datos, seleccione Validación de datos de la lista desplegable Validación de datos.
4. En el menú desplegable Permitir, seleccione Lista.
5. En el control Source, ingrese =Shelf_Code_List, como se muestra en la . Está haciendo referencia al rango nombrado, no al objeto Table (aunque sean el mismo rango.

Figura

D

Haga referencia al rango nombrado que le dio a la fuente de la lista.

Con la lista de validación en su sitio, puede empezar a rellenar las celdas de la columna E con los valores de código de estantería apropiados.

Además, puede ingresar cada uno manualmente seleccionando el código apropiado del menú desplegable, como se muestra en la . Una vez que haya usado un valor, confíe en Autocompletar e ingrese uno o dos caracteres y deje que la función complete el valor.

Seleccione valores de la lista desplegable.

Tal vez se esté preguntando por qué eran necesarios los objetos Table. Cuando se agrega un nuevo registro a la Tabla, Excel extiende el control de validación automáticamente (. Puede actualizar la fuente de la lista y el control de validación se actualizará automáticamente, como se muestra en la . Si no puede convertir el conjunto de datos en una Tabla, debe trabajar mucho más duro para obtener los mismos resultados dinámicos, pero es posible. Puede obtener más información sobre una técnica sin tabla leyendo Cómo utilizar la función de validación de datos de Excel para evitar errores en la entrada de datos.

La lista de validación está disponible para nuevos registros.

Actualice el libro de pedidos.

En un ejemplo tan simple, no es necesaria una lista ordenada alfabéticamente. Si desea una lista ordenada alfabéticamente, simplemente ordene la fuente de la lista, como se muestra en la .

Clasificar la fuente de lista para clasificar la lista de validación.

Una función de búsqueda dinámica

Usando una tabla de búsqueda, podemos negar la tarea de elegir un código de estantería por completo. La desventaja es que esta técnica es un poco lenta en un libro de trabajo complejo con miles de filas. Pero para la mayoría de nosotros, funcionará bien. Sólo requiere un buen entendimiento de cómo funciona la función VLOOKUP() de Excel. He aquí un breve tutorial que explica cómo solucionar problemas con la fórmula VLOOKUP() gotchas.

Los valores de búsqueda requieren un poco de actualización antes de que podamos empezar. Como puede ver en la , hay una nueva columna Artículo a la izquierda de la columna de código de estantería. Esta es una columna de valores unívocos que coinciden con los valores del conjunto de datos. Los valores de la derecha -los códigos de estantería- son los valores que la función devolverá cuando encuentre una coincidencia. Por ejemplo, cuando el valor de posición en la columna C es Manzanas, la función de la columna E devolverá LPA.

La tabla de consulta necesita por lo menos dos columnas.

Para continuar, introduzca la siguiente función en el E3 y copie al conjunto de datos restante, como se muestra en la :

=VLOOKUP(C:C,$G$3:$H$8,2,FALSE)

Entre en la función VLOOKUP(.

Si ingresa un nuevo registro, como se muestra en la , Excel extiende la fórmula al nuevo registro. Si esto no sucede en su caso, marque la siguiente opción:

1. Haga clic en la ficha Archivo y seleccione Opciones.
2. Seleccione Avanzado en el panel izquierdo.

1. En la sección Opciones de edición, asegúrese de que la

1. opción Extender Formatos de Rango de Datos y Fórmulas esté marcada .
2. Haga clic en Aceptar.

Excel amplía la fórmula.

Ampliar fórmulas.

Tal como está, la solución de búsqueda no es dinámica, por lo que si añade una nueva fruta a la tabla de búsqueda (columnas G y H), la función de la columna E no se actualizará para incluir esa nueva fila. Intentémoslo ahora y veamos qué pasa. Después de añadir Coconut; CNT a la tabla de búsqueda, como se muestra en la Figura M, introduciendo un nuevo registro que hace referencia a Coconut no se actualiza como se esperaba.

Figura M

La función no hace referencia al nuevo elemento de búsqueda en la fila 9

. Aquí es donde un poco de magia de mesa puede ayudar. Simplemente convierta la tabla de búsqueda (G2:H8) en un objeto Tabla antes de añadir un nuevo elemento. A continuación, añada el nuevo elemento. Como puede ver en la , la función se actualiza automáticamente para hacer referencia a la nueva fila en el objeto Lookup Table. La función original hacía referencia a H8, pero después de convertir el rango de datos en una Tabla, la función hace referencia a la última fila de la Tabla, la fila 9 – ¡no tenía que actualizar las funciones de la columna E! Puede convertir el conjunto de datos de fruta en un objeto Tabla, pero no es necesario que esta técnica funcione dinámicamente.

Convierta el conjunto de datos de la tabla de búsqueda en un objeto Tabla.

El objeto de la mesa es el héroe

Ambas soluciones de lista avanzadas son dinámicas gracias al objeto Table. Sin ese objeto, usted tendría que trabajar mucho más duro para hacer que cualquiera de estas soluciones de lista avanzada sea tan flexible. Para obtener más información sobre los objetos de tabla de Excel, lea 10 razones para utilizar el objeto de tabla de Excel.

Boletín semanal de Microsoft

Conviértase en un experto en Microsoft de su empresa con la ayuda de estos tutoriales de Windows y Office y de los análisis de nuestros expertos sobre los productos empresariales de Microsoft. Entregado Lunes y Miércoles

Envíeme su pregunta sobre Office

Respondo a las preguntas de los lectores cuando puedo, pero no hay garantía. No envíe archivos a menos que se le solicite; las solicitudes iniciales de ayuda que lleguen con archivos adjuntos se eliminarán sin leer. Puede enviar capturas de pantalla de sus datos para ayudar a aclarar su pregunta. Cuando se ponga en contacto conmigo, sea lo más específico posible. Por ejemplo, «Please troubleshoot my workbook and fix what’s wrong» probablemente no obtendrá respuesta, pero «Can you tell me why this formula is’ t returning the expected results? Por favor, menciona la aplicación y la versión que estás usando. ConsejoTecnologico.com no me reembolsa por mi tiempo o experiencia en ayudar a los lectores, ni tampoco pido una cuota a los lectores a los que ayudo. Puede ponerse en contacto conmigo en susansalesharkins@gmail.com.

Lea también….