En este artículo se incluye información avanzada acerca de la validación de datos. La Validación de datos (Data Validation) en Microsoft Excel es una técnica que nos permite controlar y restringir el ingreso de datos en una celda. Puede usar la validación de datos para restringir el tipo de datos o los valores que los usuarios escriben en las celdas. La validación de datos es sumamente útil cuando quiere compartir un libro con otros usuarios y quiere que los datos que se escriban en él sean exactos y coherentes.
De manera predeterminada, las celdas de nuestra hoja están listas para recibir cualquier tipo de dato, ya sea un texto, un número, una fecha o una hora. Este tipo de error puede ser prevenido si utilizamos la validación de datos en Excel al indicar que la celda B5 solo aceptará fechas válidas. Imaginemos que la empresa Contoso, empresa que se dedica a la fabricación, venta y soporte técnico informático, quiere recopilar el número de teléfono de sus comerciales. Validar datos según fórmulas o valores de otras celdas: por ejemplo, puede usar la validación de datos para establecer un límite máximo para comisiones y bonificaciones, en función del valor de nómina general proyectado.
Configuración Básica de la Validación de Datos
Al pulsar dicho comando se abrirá el cuadro de diálogo Validación de datos donde, de manera predeterminada, la opción Cualquier valor estará seleccionada, lo cual significa que está permitido ingresar cualquier valor en la celda. Para seleccionar una columna completa será suficiente con hacer clic sobre el encabezado de la columna.
Absolutamente todos los criterios de validación mostrarán una caja de selección con el texto Omitir blancos. De manera predeterminada, la opción Omitir blancos estará seleccionada para cualquier criterio, lo cual significará que al momento de entrar en el modo de edición de la celda podremos dejarla como una celda en blanco es decir, podremos pulsar la tecla Entrar para dejar la celda en blanco. Sin embargo, si quitamos la selección de la opción Omitir blancos, estaremos obligando al usuario a ingresar un valor válido una vez que entre al modo de edición de la celda.
Tipos de Criterios de Validación
Para analizar los criterios de validación de datos en Excel podemos dividirlos en dos grupos basados en sus características similares. Excel nos permite establecer varios tipos de reglas o condiciones para controlar el ingreso de datos en una celda:
Lea también: Definición Detallada del Proceso Contable
Para las opciones “entre” y “no está entre” debemos indicar un valor máximo y un valor mínimo pero para el resto de las opciones indicaremos solamente un valor. Entre las restricciones a validar se encuentra la opción de determinar la fecha de inicio y de fin para establecer un rango definido entre fechas.
A continuación, se presenta una tabla con los principales tipos de validación de datos:
| Criterio de Validación | Descripción | Ejemplo |
|---|---|---|
| Número entero | Permite ingresar solo números enteros, sin decimales. | 50 |
| Decimal | Permite ingresar enteros con decimales. | 50.24 |
| Lista | Permite seleccionar un elemento de una lista de datos. | =ciudades |
| Fecha | Permite sólo el ingreso de fechas en la celda. | 24/08/2023 |
| Hora | Permite sólo el ingreso de horas en la celda. | 14:22 |
| Longitud de texto | Permite controlar el largo mínimo y máximo de ingreso de datos en la celda. | Mínimo 5 y Máximo 7 caracteres |
| Personalizada | Permite crear una fórmula o una función para controlar las condiciones del texto a ingresar en la celda. | =esnumero(A10) |
Criterio de Validación "Lista"
A diferencia de los criterios de validación mencionados anteriormente, la Lista es diferente porque no necesita de un valor máximo o mínimo sino que es necesario indicar la lista de valores que deseamos permitir dentro de la celda. Puedes colocar tantos valores como sea necesario y deberás separarlos por el carácter de separación de listas configurado en tu equipo. En mi caso, dicho separador es la coma (,) pero es probable que debas hacerlo con el punto y coma (;).
Para que la lista desplegable sea mostrada correctamente en la celda deberás asegurarte que, al momento de configurar el criterio validación de datos, la opción Celda con lista desplegable esté seleccionada. En caso de que los elementos de la lista sean demasiados y no desees introducirlos uno por uno, es posible indicar la referencia al rango de celdas que contiene los datos. Un rango que contiene la lista de elementos, ej.: $A$1.$A$20. Un nombre de un rango, ej. Cuando crea una lista desplegable, puede usar el comando Definir nombre (pestaña Fórmulas, grupo Nombres definidos) para definir un nombre para el rango que contiene la lista. Muchos usuarios de Excel utilizan la lista de validación con los datos ubicados en otra hoja. En realidad es muy sencillo realizar este tipo de configuración ya que solo debes crear la referencia adecuada a dicho rango. Supongamos que la misma lista de días de la semana la he colocado en una hoja llamada DatosOrigen y los datos se encuentran en el rango G1:G7. El ancho de la lista desplegable está determinado por el ancho de la celda que tiene la validación de datos.
¿Cuál es la novedad con la nueva validación de datos? Que Microsoft agregó la opción de buscar texto en un campo de Validación de datos basado en una Lista. Antes, cuando se creaba una Validación de datos basado en una lista, era necesario desplazarnos a lo largo de la lista hasta encontrar el registro que necesitábamos seleccionar. Ahora, en el campo de ingreso de la Validación de datos, podemos ingresar los primeros caracteres de una palabra para que nos busque en toda la lista. El proceso para establecer Validación de datos no ha cambiado, pero lo que si cambió es la facilidad que ahora tenemos para buscar información.
Lea también: Proceso de Auditoría Interna
Mensajes de Entrada y de Error Personalizados
Para un registro óptimo, Excel permite anunciar las condiciones de entrada de datos mediante un «mensaje de entrada» y análogamente avisar con un mensaje si el dato introducido no es de los permitidos mediante un «mensaje de error» o «alerta de error». Puede decidir mostrar un mensaje de entrada cuando el usuario seleccione la celda. Los mensajes de entrada se usan normalmente para ofrecer instrucciones a los usuarios sobre el tipo de datos que quiere que escriban en la celda. Este tipo de mensaje aparece cerca de la celda. Informa a los usuarios de que los datos indicados no son válidos, pero sin evitar que los escriban.
Tal como lo mencioné al inicio del artículo, es posible personalizar el mensaje de error mostrado al usuario después de tener un intento fallido por ingresar algún dato. Otra opción interesante que se comentaba al inicio de este epígrafe, es poder configurar un mensaje de error para poder avisar en el caso de que el dato introducido no cumpla con los requerimientos establecidos. La caja de texto Título nos permitirá personalizar el título de la ventana de error que de manera predeterminada se muestra como Microsoft Excel. En el apartado Título escribiremos lo queremos que salga, en nuestro caso: «Introduce el número de teléfono».
Para la opción Estilo tenemos tres opciones: Detener, Advertencia e Información. Cada una de estas opciones tendrá dos efectos sobre la venta de error: en primer lugar realizará un cambio en el icono mostrado y en segundo lugar mostrará botones diferentes.
- La opción Detener mostrará los botones Reintentar, Cancelar y Ayuda.
- La opción Advertencia mostrará los botones Si, No, Cancelar y Ayuda.
- La opción Información mostrará los botones Aceptar, Cancelar y Ayuda.
Consideraciones sobre Protección y Uso Compartido
Usaremos la herramienta Permitir editar datos en Excel para proteger las celdas y hojas del documento. En el apartado Contraseña para desproteger la hoja le damos una contraseña. Seguidamente, en el apartado Correspondiente a las celdas, indicaremos el rango donde queremos que se aplique la contraseña. Finalmente en Contraseña del rango, crearemos nuestra contraseña.
Si tiene previsto proteger la hoja de cálculo o el libro, hágalo después de haber terminado de configurar la validación. Asegúrese de desbloquear cualquier celda validada antes de proteger la hoja de cálculo. De lo contrario, los usuarios no podrán escribir en las celdas. Si tiene previsto compartir el libro, hágalo únicamente después de haber configurado la validación y la protección de datos.
Lea también: Más sobre el Proceso de Auditoría
La hoja de cálculo puede estar protegida o compartida: no se puede cambiar la configuración de validación de datos si el libro está compartido o protegido. Si se hereda un libro con validación de datos, puede modificarla o eliminarla, salvo en los casos en los que la hoja de cálculo esté protegida. Si está protegida con una contraseña y no la conoce, debería ponerse en contacto con el propietario anterior para poder desproteger la hoja de cálculo, ya que Excel no tiene ningún método de recuperación de contraseñas.
Gestión y Solución de Problemas
Puede aplicar la validación de datos a celdas en las que ya se han escrito datos. No obstante, Excel no le notificará automáticamente que las celdas existentes contienen datos no válidos. En este escenario, puede resaltar los datos no válidos indicando a Excel que los marque con un círculo en la hoja de cálculo. Una vez que haya identificado los datos no válidos, puede ocultar los círculos nuevamente. Para buscar las celdas de la hoja de cálculo que tienen validación de datos, en la pestaña Inicio en el grupo Modificar, haga clic en Buscar y seleccionar y a continuación en Validación de datos.
Si cambia la configuración de validación para una celda, automáticamente se pueden aplicar los cambios a todas las demás celdas que tienen la misma configuración.
Existen ciertas limitaciones a tener en cuenta:
- Los usuarios no están copiando datos ni rellenando celdas: la validación de datos está diseñada para mostrar mensajes y evitar entradas no válidas solo cuando los usuarios escriben los datos directamente en una celda. Cuando se copian datos o se rellenan celdas, no aparecen mensajes.
- La actualización manual está desactivada: si la actualización manual está activada, las celdas no calculadas pueden impedir que los datos se validen correctamente.
- Las fórmulas no contienen errores: asegúrese de que las fórmulas de las celdas validadas no causen errores, como #REF! o #DIV/0!.
- Es posible que una tabla de Excel esté vinculada a un sitio de SharePoint: no se puede agregar una validación de datos a una tabla de Excel que esté vinculada a un sitio de SharePoint.
- Es posible que esté escribiendo datos en este momento: el comando Validación de datos no se encuentra disponible mientras está escribiendo datos en una celda.
Eliminación de la Validación de Datos
Al pulsar el botón Aceptar habrás removido cualquier validación de datos aplicada sobre las celdas seleccionadas. Espero que con esta guía tengas una mejor y más clara idea sobre cómo utilizar la validación de datos en Excel.