Cinco formas de aprovechar las funciones de listas en Excel

El artículo "Cinco maneras de aprovechar las funciones de listas de Excel" mostró cinco características básicas de listas incorporadas directamente en Excel. No tienes que hacer mucho para aprovecharlas, pero a veces necesitarás algo más sofisticado. En este artículo, te mostraré dos funciones avanzadas de listas utilizando una lista de validación y una función de búsqueda para generar una lista dinámica. Ninguna técnica es superior, tus necesidades dictarán tu elección.

Estoy usando Excel 2016 (escritorio), pero ambas técnicas funcionarán en versiones anteriores. Para tu comodidad, puedes descargar el archivo de demostración .xlsx. La función VLOOKUP() está disponible en versiones anteriores del menú, pero ambas técnicas dependen de que el objeto Tabla sea dinámico, y las versiones del menú anterior no admiten el objeto Tabla. La edición del navegador admite listas de validación y objetos de tabla, pero insertar nuevos datos en una tabla es incómodo; esa edición ignora el salto de pestaña al nuevo comportamiento de registro al ingresar un nuevo registro.

VER: 30 cosas que nunca debes hacer en Microsoft Office (PDF gratuito de Newsmatic)

Una lista de validación dinámica

Una lista de validación te permite limitar a los usuarios a valores específicos. Querrás usar estas listas para facilitar la entrada de datos y evitar errores. No puedes evitar que un usuario elija un valor incorrecto, pero al menos estará bien escrito. Y aunque eso pueda parecer tonto, un valor mal escrito produce resultados erróneos, ya sea que estés filtrando o usando funciones complejas para analizar tus datos.

En su forma más simple, una lista de validación no es dinámica, pero combinar la función con el objeto Tabla cambia eso. Si tanto el conjunto de datos como la fuente de la lista son objetos de tabla, Excel actualizará todo mientras trabajas.

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

Figura A

Utilizaremos estos dos objetos de tabla para crear una lista de validación dinámica.

Estos dos conjuntos de datos son objetos de tabla en el archivo de demostración. Si estás trabajando con tus propios datos, puedes convertir un rango de datos en una tabla de la siguiente manera:

Cómo reinstalar macOS sin usar el ratón: guía paso a paso

1. Haz clic en cualquier lugar dentro del conjunto de datos.
2. Haz clic en la pestaña Insertar.
3. En el grupo Tablas, haz clic en Tabla.
4. En el cuadro de diálogo resultante, marca o desmarca la opción Mi tabla tiene encabezados (Figura B), y haz clic en Aceptar.

Figura B

Especifica si tu tabla tiene encabezados.

Hay un paso más antes de poder crear la lista de validación. No puedes especificar un objeto de tabla como fuente de una lista de validación, pero puedes hacerlo si le das a la tabla un nombre de rango, de la siguiente manera:

1. Selecciona H2:H7 (tu fuente de lista).
2. Haz clic en la pestaña Fórmulas.
3. En el grupo Nombres definidos, haz clic en la opción Crear a partir de selección.
4. En el cuadro de diálogo resultante, marca la opción Fila superior (si es necesario), y Excel usará el texto del encabezado para nombrar el rango Shelf_Code_List (Figura C).

Figura C

Dale un nombre de rango a la lista.

Ahora estás listo para crear la lista de validación, de la siguiente manera:

1. Selecciona E2:E15: estas son las celdas existentes que deseas llenar utilizando el control de validación. (Es posible que tu tabla aún no tenga datos, y en ese caso, seleccionarás una sola celda.)
2. Haz clic en la pestaña Datos.
3. En el grupo Herramientas de datos, elige Validación de datos desde el menú desplegable Validación de datos.
4. En el menú desplegable Permitir, elige Lista.
5. En el control Fuente, ingresa =Shelf_Code_List, como se muestra en Figura D. Estás referenciando el rango con nombre, no el objeto de tabla (aunque sean el mismo rango).

Figura D

Referencia el rango con nombre que le diste a la fuente de la lista.

Con la lista de validación en su lugar, puedes comenzar a llenar las celdas en la columna E con los valores de código de estantería correspondientes. Además, puedes ingresar cada uno manualmente eligiendo el código apropiado de la lista desplegable, como se muestra en Figura E. Una vez que hayas utilizado un valor, simplemente confía en la función Autocompletar e ingresa un carácter o dos y deja que la función complete el valor.

Figura E

Elige valores de la lista desplegable.

Puede que te preguntes por qué los objetos de tabla eran necesarios. Cuando agregas un nuevo registro a la tabla, Excel extiende automáticamente el control de validación (Figura F). Puedes actualizar la fuente de la lista y el control de validación se actualizará automáticamente, como se muestra en Figura G. Si no puedes convertir el conjunto de datos en una tabla, deberás trabajar mucho más duro para obtener los mismos resultados dinámicos, pero es posible. Puedes aprender más sobre una técnica sin tabla leyendo Cómo usar la función de validación de datos de Excel para prevenir errores de entrada de datos.

Figura F

El control de validación está disponible para nuevos registros.

Figura G

Actualiza la lista de origen.

En un ejemplo tan simple, no es necesario tener una lista ordenada alfabéticamente. Si deseas una lista ordenada alfabéticamente, simplemente ordena la fuente de la lista, como se muestra en Figura H.

Figura H

Ordena la fuente de la lista para ordenar la lista de validación.

Una función de búsqueda dinámica

Utilizando una tabla de búsqueda, podemos evitar por completo la tarea de elegir un código de estantería. El inconveniente es que esta técnica puede ser un poco lenta en un libro de trabajo complejo con miles de filas. Pero para la mayoría de nosotros, funcionará bien. Solo se requiere una buena comprensión de cómo funciona la función VLOOKUP() de Excel. Aquí tienes un breve tutorial que explica cómo solucionar problemas comunes de la fórmula VLOOKUP().

Cómo maximizar el uso de Apple Pages: 3 consejos clave

Los valores de búsqueda requieren una pequeña actualización antes de que podamos empezar. Como puedes ver en Figura I, hay una nueva columna de "Elemento" a la izquierda de la columna de código de estantería. Esta es una columna de valores únicos que coinciden con los valores del conjunto de datos. Los valores a 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 un elemento en la columna C es "Manzanas", la función en la columna E devolverá "MNA".

Figura I

La tabla de búsqueda necesita al menos dos columnas.

Para continuar, ingresa la siguiente función en E3 y copia al resto del conjunto de datos, como se muestra en Figura J:

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

Figura J

Ingresa la función VLOOKUP().

Si ingresas un nuevo registro, como se muestra en Figura K, Excel extiende la fórmula al nuevo registro. Si esto no sucede, verifica la siguiente opción:

1. Haz clic en la pestaña Archivo y elige Opciones.
2. Elige Avanzadas en el panel izquierdo.
3. En la sección Opciones de edición, asegúrate de que la opción Extender formatos y fórmulas de rango de datos esté marcada (Figura L).
4. Haz clic en Aceptar.

Figura K

Excel extiende la fórmula.

Figura L

Extiende fórmulas.

Como está, la solución de búsqueda no es dinámica, por lo que si agregas una nueva fruta a la tabla de búsqueda (columnas G y H), la función en la columna E no se actualizará para incluir esa nueva fila. Intentemos eso ahora y veamos qué sucede. Después de agregar "Coco; CNT" a la tabla de búsqueda, como se muestra en Figura M, ingresar un nuevo registro que haga referencia a "Coco" 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 la tabla puede ayudar. Simplemente convierte la tabla de búsqueda (G2:H8) en un objeto de tabla antes de agregar un nuevo elemento. Luego, agrega el nuevo elemento. Como puedes ver en Figura N, la función se actualiza automáticamente para hacer referencia a la nueva fila en el objeto de tabla de búsqueda. 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 tuviste que actualizar las funciones en la columna E! Puedes convertir el conjunto de datos de frutas en un objeto de tabla, pero no es necesario que esta técnica funcione de manera dinámica.

Microsoft Edge supera a Chrome y Firefox en duración de la batería en Windows 10

Figura N

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

El objeto de tabla es el héroe

Ambas soluciones avanzadas de lista son dinámicas gracias al objeto de tabla. Sin ese objeto, tendrías que trabajar mucho más para hacer que cualquiera de estas soluciones de lista avanzadas sean flexibles. Para obtener más información sobre los objetos de tabla de Excel, lee 10 razones para utilizar el objeto de tabla de Excel.

Envíame tu pregunta sobre Office

Respondo las preguntas de los lectores cuando puedo, pero no hay garantía de respuesta. No envíes archivos a menos que se soliciten; las solicitudes iniciales de ayuda que lleguen con archivos adjuntos se eliminarán sin leer. Puedes enviar capturas de pantalla de tus datos para ayudar a aclarar tu pregunta. Al contactarme, sé lo más específico posible. Por ejemplo, "Revise mi libro de trabajo y corrija lo que está mal" probablemente no recibirá una respuesta, pero "¿Puedes decirme por qué esta fórmula no devuelve los resultados esperados?" podría sí recibirla. Por favor, menciona la aplicación y la versión que estás utilizando. No recibo reembolso de Newsmatic por mi tiempo o experiencia al ayudar a los lectores, ni solicito una tarifa de los lectores a los que ayudo. Puedes contactarme en [email protected].

También lee...

• 10 ajustes que puedes cambiar para hacer que Excel 2016 funcione a tu manera (Newsmatic)
• Cómo agregar un campo de prioridad personalizado a las tareas de Outlook (Newsmatic)
• Preguntas y respuestas de Office: Dos formas fáciles de repetir texto en un documento de Word (Newsmatic)
• Errores de Excel: Cómo la hoja de cálculo de Microsoft puede ser perjudicial para tu salud (ZDNet)
• Mejora tus habilidades en Excel con estos 10 consejos avanzados (PDF gratuito de Newsmatic)

En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Software, allí encontraras muchos artículos similares a Cinco formas de aprovechar las funciones de listas en Excel , tenemos lo ultimo en tecnología 2023.