Saltar al contenido

Automatización de las listas de validación de datos en Excel

Trabajar en una hoja de cálculo de Excel puede ser algo así como el Salvaje Oeste – a menos que se hagan otras previsiones, los usuarios pueden introducir cualquier valor en cualquier celda. Una forma de restringir a los usuarios a un conjunto predefinido de valores es a través de la función de validación de datos de Excel. Explicaré cómo puede crear listas desplegables dentro de la celda, junto con la forma de “probar en el futuro” la lista de validación de datos para que la función no requiera mantenimiento si añade elementos adicionales en el futuro. Esta técnica también resuelve un molesto problema en Excel 2007 en el que las listas de validación de datos no se pueden colocar en otras hojas de trabajo.

Antes de ver la Validación de Datos, establezcamos primero nuestra lista. Digamos que queremos que el usuario elija un tipo de fruta. En la segunda hoja de trabajo de un libro de trabajo en blanco, crear una lista, como se muestra en la Figura 1. Una vez que lo haga, convierta la lista en una Tabla en Excel 2007 y posterior, o en una Lista en Excel 2003 y anterior. Después de hacer clic en cualquier celda de la lista:

Automatización de las listas de validación de datos en Excel
Automatización de las listas de validación de datos en Excel
  • Excel 2007 y posteriores – Elija Insertar y luego Tabla. Asegúrate de que Mi tabla tiene encabezados esté seleccionada y luego haz clic en Aceptar.
  • Excel:Mac 2011 – En la pestaña Tablas de la cinta, haz clic en la flecha que aparece junto al comando Nuevo y luego elige Insertar tabla con encabezados.
  • Excel 2003 y anteriores – Elija Datos, Lista, y luego Crear Lista.

Figura 1: Introduce unos cuantos elementos en la segunda hoja de trabajo de un libro de trabajo.

Una ventaja de los cuadros (y de las listas en Excel 2003 y anteriores) es que si se añaden elementos al final de la lista, el cuadro se ampliará automáticamente para abarcar los nuevos elementos. Sin embargo, no podemos utilizar esta tabla o lista autoexpandible directamente con la validación de datos. Para ello, debemos crear un nombre de rango que abarque todo menos la primera fila de la tabla. Selecciona la segunda hasta la última fila de tu rango, y luego:

  • Excel 2007 y posteriores – En la pestaña Fórmulas seleccione Definir nombre.
  • Excel 2003 y anteriores, o Excel – Mac 2011: Elija Insertar, Nombre, y luego Definir.

Una vez que aparezca el cuadro de diálogo Definir nombre que se muestra en la Figura 2, introduzca un nombre como Fruta, asegúrese de que el campo Referencias hace referencia a la segunda a la última fila de la tabla y luego haga clic en Aceptar.

Figura 2: Asigna un nombre a la segunda y última celda de tu tabla o lista.

Echemos primero un vistazo a la función de validación de datos. En la primera hoja de trabajo del libro de trabajo donde creaste tu lista, haz clic en la celda A1 y escribe la palabra Fruta. A continuación, selecciona la celda A2 y escoge Datos y luego Validación de Datos. Aparecerá el cuadro de diálogo de la Figura 3. En la pestaña Configuración, elige Lista en el campo Permitir. Cuando aparezca el campo Fuente, escribe un signo igual junto con el nombre del rango que asignaste antes, como =Fruta.

Figura 3: Elija Lista y luego especifique el nombre de su rango como Fuente en la pestaña de Ajustes.

En la pestaña Mensaje de entrada, introduzca un título, como la palabra Fruta, para describir el campo de entrada, junto con un mensaje, como “Elija un elemento de la lista”, como se muestra en la figura 4. No es necesario utilizar comillas en el campo Descripción.

Figura 4: Aunque es opcional, la pestaña Mensaje de entrada permite documentar la validación.

Por último, en la pestaña Alerta de error, introduzca un Título, como “Entrada inválida”, y un Mensaje de error, como “Debe hacer una selección de la lista”, como se muestra en la Figura 5. Mantenga el estilo establecido como Stop para evitar que el usuario pase por alto los elementos de la lista. Haga clic en OK para cerrar el cuadro de diálogo de validación de datos.

Figura 5: Asegúrese de completar la pestaña Alerta de error; de lo contrario, las entradas no válidas desencadenarán un aviso de error genérico y críptico.

Al avanzar, cuando se hace clic en la celda A2, debe aparecer una nota como se muestra en la Figura 6. Esto documenta la hoja de cálculo. Si el usuario hace una elección válida, podrá entonces moverse a otra celda; de lo contrario, el aviso mostrado en la Figura 7 detendrá al usuario en su camino.

Figura 6: Nuestras opciones en la pestaña de Entrada producen un útil aviso cuando el usuario hace clic en la celda A2.

Figura 7: El usuario verá este aviso si intenta introducir algo que no aparece en la lista.

Ahora, vuelva a su lista original y añada dos elementos más, como se muestra en la figura 8. Cuando vuelva a su celda con la lista de validación de datos, los nuevos elementos deberían aparecer automáticamente. La combinación de las características de la tabla/lista y los nombres de los rangos automatiza este aspecto. Dentro del cuadro de diálogo de validación de datos, puede proporcionar un conjunto específico de coordenadas de celda en el campo Fuente, pero entonces tendría que cambiar manualmente el ajuste si más tarde expandiera la lista. Además, Excel 2007 y anteriores no le permitirán hacer referencia a las coordenadas de las celdas en otras hojas de trabajo. Las mejores prácticas en el diseño de hojas de cálculo exigen separar las listas de apoyo de los datos reales en hojas de trabajo separadas. Cualquiera que utilice Excel 2007 y versiones posteriores ya no se enfrenta a la lucha de dónde colocar con seguridad las listas de apoyo para la validación de datos en la misma hoja de trabajo en la que se está produciendo la entrada real.

Figura 8: Como la fuente última de una lista de validación es una tabla/lista, los nuevos elementos aparecen en la lista de validación de datos automáticamente.