COMPUTACIÓN APLICADA II: VALIDACIÓN DATOS EN EXCEL

¿QUÉ ES LA VALIDACIÓN DE DATOS?

La validación de datos es una función de Excel que permite establecer restricciones respecto a los datos que se pueden o se deben escribir en una celda. La validación de datos puede configurarse para impedir que los usuarios escriban datos no válidos. Si lo prefiere, puede permitir que los usuarios escriban datos no válidos en una celda y advertirles cuando intenten hacerlo. También puede proporcionar mensajes para indicar qué tipo de entradas se esperan en una celda, así como instrucciones para ayudar a los usuarios a corregir los errores.

Por ejemplo, en un libro de marketing, puede configurar una celda para permitir únicamente números de cuenta de tres caracteres. Cuando los usuarios seleccionan la celda, puede mostrarles un mensaje como el siguiente:

Si los usuarios no tienen en cuenta este mensaje y escriben datos no válidos en la celda, como un número de dos o de cinco dígitos, puede mostrarles un mensaje de error específico.

En un escenario un poco más avanzado, podría usar la validación de datos para calcular el valor máximo permitido en una celda según un valor que se encuentra en otra parte del libro. En el siguiente ejemplo, el usuario ha escrito 4.000 dólares en la celda E7, lo cual supera el límite máximo especificado para comisiones y bonificaciones.

Si se aumentara o redujera el presupuesto de nómina, el máximo permitido en E7 también aumentaría o se reduciría automáticamente.

Las opciones de validación de datos se encuentran en el grupo Herramientas de datos.

La validación de datos se configura en el cuadro de diálogo Validación de datos.

CUANDO ES ÚTIL LA VALIDACIÓN

La validación de datos es sumamente útil cuando desea compartir un libro con otros miembros de la organización y desea que los datos que se escriban en él sean exactos y coherentes.

Puede usar la validación de datos para lo siguiente, entre otras aplicaciones:

Restringir los datos a elementos predefinidos de una lista : Por ejemplo, puede limitar los tipos de departamentos a Ventas, Finanzas, Investigación y desarrollo y TI. De forma similar, puede crear una lista de valores a partir de un rango de celdas que se encuentran en otra parte del libro. Para obtener más información, vea Crear una lista desplegable a partir de un rango de celdas.

Restringir los números que se encuentren fuera de un intervalo específico Por ejemplo, puede especificar un límite mínimo de deducciones de dos veces el número de hijos en una celda específica.
Restringir las fechas que se encuentren fuera de un período de tiempo específico Por ejemplo, puede especificar un período de tiempo entre la fecha actual y los 3 días siguientes.
Restringir las horas que se encuentren fuera de un período de tiempo específico Por ejemplo, puede especificar un período de tiempo para servir el desayuno entre la hora en que abre el restaurante y cinco horas después.
Limitar la cantidad de caracteres de texto Por ejemplo, puede limitar el texto permitido en una celda a 10 caracteres o menos. De forma similar, puede establecer la longitud específica de un campo de nombre completo (C1) en la longitud actual de un campo de nombre (A1) y un campo de apellidos (B1), más 10 caracteres.
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 de 3.600 dólares, según el valor de nómina proyectado general. Si los usuarios escriben un valor de más de 3.600 dólares en la celda, aparecerá un mensaje de validación.

IMPORTANCIA DE LA VALIDACIÓN DE DATOS EN EXCEL

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. Sin embargo, los cálculos de nuestras fórmulas dependerán de los datos contenidos en las celdas por lo que es importante asegurarnos que el usuario ingrese el tipo de dato correcto.

Por ejemplo, en la siguiente imagen puedes observar que la celda C5 muestra un error en el cálculo de la edad ya que el dato de la celda B5 no corresponde a una fecha válida.

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. Una vez creada la validación de datos, al momento de intentar ingresar una cadena de texto, obtendremos un mensaje de advertencia como el siguiente:

Más adelante veremos que es factible personalizar los mensajes enviados al usuario de manera que podamos darle una idea clara del problema, pero este pequeño ejemplo nos muestra la importancia de la validación de datos en Excel al momento de solicitar el ingreso de datos de parte del usuario.

EL COMANDO VALIDACIÓN DE DATOS EN EXCEL

El comando Validación de datos que utilizaremos a lo largo de este artículo se encuentra en la ficha Datos y dentro del grupo Herramientas 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. Sin embargo, podremos elegir alguno de los criterios de validación disponibles para hacer que la celda solo permita el ingreso de un número entero, un decimal, una lista, una fecha, una hora o una determinada longitud del texto.

CÓMO APLICAR LA VALIDACIÓN DE DATOS

Para aplicar la validación de datos sobre una celda específica, deberás asegurarte de seleccionar dicha celda y posteriormente ir al comando Datos > Herramientas de Datos > Validación de datos.

Por el contrario, si quieres aplicar el mismo criterio de validación a un rango de celdas, deberás seleccionar dicho rango antes de ejecutar el comando Validación de datos y eso hará que se aplique el mismo criterio para todo el conjunto de celdas.

Ya que es común trabajar con una gran cantidad de filas de datos en Excel, puedes seleccionar toda una columna antes de crear el criterio de validación de datos.

Para seleccionar una columna completa será suficiente con hacer clic sobre el encabezado de la columna. Una vez que hayas hecho esta selección, podrás crear la validación de datos la cual será aplicada sobre todas las celdas de la columna.

LA OPCIÓN OMITIR BLANCOS

Absolutamente todos los criterios de validación mostrarán una caja de selección con el texto Omitir blancos. Ya que esta opción aparece en todos ellos, es conveniente hacer una breve explicación.

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. Podrá pulsar la tecla Esc para evitar el ingreso del dato, pero no podrá pulsar la tecla Entrar para dejar la celda en blanco.

La diferencia entre dejar esta opción marcada o desmarcada es muy sutil y casi imperceptible para la mayoría de los usuarios al momento de introducir datos, así que te recomiendo dejarla siempre seleccionada.

CREAR VALIDACIÓN DE DATOS EN EXCEL

Para analizar los criterios de validación de datos en Excel podemos dividirlos en dos grupos basados en sus características similares. El primer grupo está formado por los siguientes criterios:

Número entero
Decimal
Fecha
Hora
Longitud de texto

Estos criterios son muy similares entre ellos porque comparten las mismas opciones para acotar los datos que son las siguientes: Entre, No está entre, Igual a, No igual a, Mayor que, Menor que, Mayor o igual que, Menor o igual que.

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. Por ejemplo, podemos elegir la validación de números enteros entre los valores 50 y 100 para lo cual debemos configurar del criterio de la siguiente manera:

Por el contrario, si quisiéramos validar que una celda solamente acepte fechas mayores al 01 de enero del 2015, podemos crear el criterio de validación de la siguiente manera:

Una vez que hayas creado el criterio de validación en base a tus preferencias, será suficiente con pulsar el botón Aceptar para asignar dicho criterio a la celda, o celdas, que hayas seleccionado previamente.

LISTA DE VALIDACIÓN DE DATOS

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. Por ejemplo, en la siguiente imagen he creado un criterio de validación basado en una lista que solamente aceptará los valores sábado y domingo.

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 (;). Al momento de hacer clic en el botón Aceptar podrás confirmar que la celda mostrará un botón a la derecha donde podrás hacer clic para visualizar la lista de opciones disponibles:

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. Por ejemplo, en la siguiente imagen puedes observar que he introducido los días de la semana en el rango G1:G7 y dicho rango lo he indicado como el origen de la lista.

LISTA DE VALIDACIÓN CON DATOS DE OTRA HOJA

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. Para hacer referencia a dicho rango desde otra hoja, debo utilizar la siguiente referencia:

Para crear una lista desplegable con esos datos deberás introducir esta referencia al momento de crear el criterio de validación.

PERSONALIZAR EL MENSAJE DE ERROR

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. Para personalizar el mensaje debemos ir a la pestaña Mensaje de error que se encuentra dentro del mismo cuadro de diálogo Validación de datos.

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.

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. Y finalmente la caja de texto Mensaje de error nos permitirá introducir el texto que deseamos mostrar dentro de la ventana de error.

Por ejemplo, en la siguiente imagen podrás ver que he modificado las opciones predeterminadas de la pestaña Mensaje de error:

Como resultado de esta nueva configuración, obtendremos el siguiente mensaje de error:

CÓMO ELIMINAR LA VALIDACIÓN DE DATOS

Si deseas eliminar el criterio de validación de datos aplicado a una celda o a un rango, deberás seleccionar dichas celdas, abrir el cuadro de diálogo Validación de datos y pulsar el botón Borrar todos.

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. Esta funcionalidad será de gran ayuda para controlar el ingreso de datos por parte de los usuarios y disminuir en gran medida la probabilidad de que se ingrese información incorrecta lo cual podría tener un efecto catastrófico sobre nuestras fórmulas y el análisis de los datos.

La validación de datos de Excel que se encuentra en la pestaña “datos” grupo “herramienta de datos”, permite definir el tipo de datos que se desea introducir en una celda. Se puede configurar la validación de datos para evitar que los usuarios introduzcan datos no válidos o permitir su entrada, de esa manera Excel nos restringe a ciertos valores que le especificamos. También se pueden proporcionar mensajes que definan la entrada que se espera en la celda así como instrucciones para ayudar a los usuarios a corregir errores. Excel permite designar los siguientes tipos de datos válidos para una celda:

Número entero: Esta opción tiene una sub_casilla de texto: “datos”, que dependiendo cual lija, será los límites a validar.

Decimal: al igual que el anterior las opciones dependen de que se elija.
Lista: esta opción es una de la más usadas, ya que al seleccionarla, y hacer clic en origen, y luego poner el rango de la lista a la que queremos validar (luego aceptamos) se convertirá en una lista desplegable.

Al hacer clic en el triangulo se nos desplegará la lista, la que tenemos que elegir uno y nuevamente desaparecerá. Si ingresáramos manualmente las palabras podríamos equivocarnos, lo que hará que no funcionen determinadas fórmulas.

Si quisiéramos que no funcione como lista desplegable, tendremos que destildar la casilla “celda con lista desplegable” de la ventana de validación de datos. De esta manera seguirá la celda validada, pero no se desplegará la lista.

Fecha: al igual que número entero y decimal, dependerá que opción se elija.
Hora: igual que las anteriores.
Longitud de texto: igual que las anteriores.
Personalizada: esta opción nos permite usar una fórmula para validar datos.

Para cada celda que se valida, se pueden mostrar dos mensajes diferentes: uno que aparece antes de que el usuario introduzca los datos estando posicionados en dicha celda y otro que aparece después de que el usuario intente introducir los datos que no cumplen los requisitos.

En la primera ventana, “mensaje de entrada”, ponemos un título, y más abajo una descripción de cómo proceder. Esta ventana aparecerá al posesionarnos sobre la celda una vez que dimos ENTER.

En la pestaña de mensaje de error podemos elegir entre tres estilos: grave, advertencia e información, y a la derecha el mensaje de error con su titulo, pulsamos aceptar. Si introdujéramos un valor no valido saldrá un cartel con las palabras que hemos elegido.

El mensaje grave es el más recomendable cuando no queremos ingresar un dato no válido, ya que no permite de ninguna manera introducir un valor no válido.

Si no queremos que siga apareciendo el mensaje de entrada, seleccionamos la celda, volvemos a validación de datos, y destildamos la opción “mostrar mensaje de entrada al seleccionar la celda”, aceptamos y ya no aparecerá el mensaje.

Para desvalidar validación de datos hay dos maneras, estando en la celda que queremos desvalidar, vamos a validación de datos, “borrar todos” y aceptar; o ir a permitir y poner la opción “cualquier valor” y aceptar.

LISTA DESPLEGABLE CON VALORES EN UN RANGO

Si tu lista tiene muchos elementos puedes colocarlos dentro de una hoja de Excel y especificar el rango en el cuadro de diálogo Validación de datos y de esta manera tener una lista desplegable con los valores extraídos de dicho rango:

No es mala idea invertir un poco de tiempo para agregar esta funcionalidad a tus hojas de Excel, recuerda que una lista desplegable te evitará muchos problemas al permitir el ingreso de valores previamente validados.

EL MENSAJE DE ENTRADA EN EXCEL

Si no deseas que el mensaje se muestre al pasar el cursor sobre una celda sino que prefieres que el mensaje se muestre solamente cuando se hace clic sobre ella, entonces puedes utilizar el mensaje de entrada de Excel.

Para utilizar este tipo de mensajes debes seleccionar la celda que tendrá el mensaje e ir a la ficha Datos y pulsar el botón Validación de datos. Dentro del cuadro de diálogo Validación de datos debes ir a la sección Mensaje de entrada:

En la caja de texto Título colocaremos el título de nuestro mensaje el cual será mostrado en negritas. En la caja de texto Mensaje de entrada colocaremos nuestro mensaje. Debes asegurarte de marcar la caja de selección Mostrar mensaje de entrada al seleccionar la celda.

Al pulsar el botón Aceptar se creará el mensaje de entrada asociado a la celda el cual se mostrará solamente cuando se seleccione dicha celda: