Computación para los Negocios: Validación De Datos

En este artículo conoceremos un poco más de una herramienta tal vez un poco desconocida, pero con un gran potencial y de gran utilidad como lo es la Validación de Datos.

¿Qué es la Validación de Datos?

La validación de datos es una herramienta de Microsoft Excel mediante la cual podemos establecer criterios para el ingreso de datos en una celda o un rango de ellas. Así por ejemplo, podemos definir que en una determinada celda sólo podremos introducir números del 1 al 10. De esta forma, al introducir un número que no está en este rango, el programa mostrará un mensaje de error o alerta, de acuerdo a la configuración que el usuario haga usando esta herramienta.

¿Cómo funciona la Validación de Datos? ¿Cuáles son sus características principales?

Para hacer uso de esta herramienta, el usuario debe seguir los pasos que detallaremos a continuación, dependiendo del uso que le dará a ésta.

1. El usuario debe dirigirse a la pestaña Datos en la barra de herramientas, en donde encontrará la función validación de datos como se muestra a continuación.

2. Una vez seleccionada la herramienta Validación de Datos, ésta nos entregará 3 opciones: Validación de Datos, Rodear con un círculo datos no válidos y Borrar círculos de validación, las cuales detallaremos a continuación.

Validación de Datos

La primera opción que nos ofrece nuestra herramienta es la Validación de Datos. Aquí el usuario podrá definir los criterios según los cuales validará la información contenida en una o más celdas, es decir, establecerá los valores que podrán ser introducidos en ellas.

Al hacer clic en la opción, ésta nos mostrará una ventana en donde encontraremos tres pestañas: Configuración, Mensaje de entrada y Mensaje de error. En cada una de ellas el usuario podrá establecer los criterios de validación y los mensajes que desea que se muestren cuando no se estén cumpliendo éstos.

a. Configuración

En esta pestaña el usuario debe definir los criterios de validación para la o las celdas con las que trabajará. En permitir se definirá el tipo de dato que se puede ingresar. Es así como encontramos las opciones número entero, decimal, lista, fecha, hora, longitud de texto y personalizar, como vemos en la imagen a continuación.

Dependiendo de la opción seleccionada se habilitarán el resto de las opciones que entrega esta ventana. Así para cado tipo de dato tendremos lo siguiente:

Números: Tanto en la opción número entero como en la opción decimal se activan las mismas casillas a completar. En la lista desplegable Datos debemos seleccionar el criterio que utilizaremos. En este caso podemos indicar que deseamos limitar el contenido de la celda a números que se encuentren entre un rango establecido, que no se encuentren en este rango, que sean mayores, menores o iguales a determinado número o que sean mayores o iguales, menores o iguales o no iguales a cierto valor. Por ejemplo podemos definir que deseamos que en una celda sólo se puedan ingresar números que se encuentren entre 1 y 10. De esta forma, tendremos lo siguiente:

Fechas y Horas: En éstas opciones podrá establecer el rango de fechas u horas que limita el contenido de la celda utilizando las funciones mínimo y máximo o limitar los valores que pueden ser introducidos utilizando las funciones igual, mayor, menor, mayor o igual, menor o igual y no igual a una fecha u hora inicial que debe ser introducido. Así por ejemplo, si deseamos que en una celda sólo se puedan introducir fechas posteriores a 01/09/2009, tendremos que seleccionar la función mayor que, teniendo lo siguiente:

Longitud:Esta opción nos permite limitar la longitud de la palabra que podrá ser introducida en la celda en cuestión, es decir, el número de caracteres de ésta. Al igual que los casos anteriores, debemos establecer la función que deseamos aplicar en la lista desplegable Datos, teniendo como posible elección las funciones entre, no está entre, mayor, menor, igual, mayor o igual, menor o igual, no igual a. De esta forma, si deseamos que la longitud máxima de nuestra palabra sean 5 caracteres, tendremos lo siguiente:

Lista: Esta opción nos permite limitar los datos que podremos ingresar en una celda a los datos de una lista que elaboremos. La modalidad de lista nos ofrece dos opciones. La primera de ellas es que la persona escriba directamente en la celda, permitiéndole sólo ingresar los datos que están en la lista y la segunda es crear una lista desplegable con las opciones que la persona puede seleccionar, mostrando una flecha que al apretar desplegará la lista. Por ejemplo, si deseamos elaborar una lista de animales haciendo uso de la lista desplegable, tendremos lo siguiente:

Personalizada: La última opción nos permite personalizar los criterios de validación a lo que estamos buscando.

b. Mensaje de entrada

En la segunda pestaña encontraremos la opción mensaje de entrada. Aquí el usuario podrá personalizar el mensaje que desea mostrar al momento de seleccionar la celda o el rango de ellas que desea validar. De esta forma, podemos introducir un título y mensaje para mostrar, como se muestra a continuación:

Si la persona lo desea, puede deshabilitar esta opción desmarcando la casilla Mostrar mensaje de entrada al seleccionar la celda.

c. Mensaje de errorEn la última pestaña el usuario podrá personalizar el mensaje que desea que aparezca cuando se introduzcan valores que no cumplen los requisitos fijados previamente por el usuario. Al igual que en el mensaje anterior, podemos definir el título y contenido del mensaje.

Si la persona lo desea, puede deshabilitar esta opción desmarcando la casilla Mostrar mensaje de entrada al seleccionar la celda. Además, en esta pestaña el usuario encontrará una lista con tres tipos de mensajes de error que podrán desplegarse a la hora de introducir datos no válidos.

Grave: Al seleccionar este mensaje el usuario impedirá que se introduzcan datos no válidos en la celda que configuró. Al cometer un error en la introducción de datos, aparecerá una ventana de la imagen a continuación con el mensaje que se definió. En esta ventana se dará la oportunidad de Reintentar, es decir, volver a introducir los datos, Cancelar que restaura el valor que presentaba la celda antes de la modificación y Ayuda que nos lleva al centro de ayuda de Excel. Cabe hacer notar que este mensaje no nos permite introducir datos no válidos directamente, es decir, escribiendo y presionando Enter. Sin embargo, esta función puede ser eludida al copiar y pegar un dato de otra celda, aún cuando este dato no sea válido de acuerdo a los criterios establecidos por el usuario dado que al pegar el dato, la configuración de validación preestablecida deja de funcionar.

Advertencia: Este tipo de mensaje, como su nombre nos indica, nos entrega una advertencia cuando intentamos introducir datos no válidos de acuerdo a los criterios definidos. Sin embargo, este mensaje no impide que introduzcamos datos no válidos. En esta ventana se dará la oportunidad de continuar con la introducción de datos al presionar el botón Sí, no continuar con la introducción de datos y volver a la celda para hacer modificaciones mediante el botón No, Cancelar ésta y volver a los datos anteriores a la modificación o ir a Ayuda.

Información: Este mensaje nos entrega información sobre el error que estamos cometiendo de acuerdo al mensaje que estableció el usuario. De esta forma, al introducir valores fuera de los criterios de validación establecidos por el usuario, aparecerá una ventana con el mensaje. Aquí podremos Aceptar, lo que implica seguir con la introducción de datos aunque no sean válidos, Cancelar, es decir, volver a la celda sin hacer la modificación e ir a Ayuda, que nos envía al centro de ayuda de Excel. Al igual que el mensaje Advertencia, este mensaje no nos impide introducir datos no válidos.

NOTA: Al momento de utilizar la herramienta Validación de Datos, el usuario debe necesariamente completar los datos de la pestaña Configuración y establecer los criterios de validación. Sin embargo, las pestañas Mensaje de entrada y Mensaje de error no necesariamente deben ser establecidos por el usuario. De no hacerlo, no aparecerá ningún mensaje de entrada y Microsoft Excel arrojará un mensaje de error preestablecido por el programa. Además, debemos hacer notar que en la ventana de validación de datos encontraremos la casilla Borrar todos, la que nos ayudará a borrar la configuración de validación que hayamos establecido en caso de ya no ser necesaria o error.

Rodear con un círculo datos no válidos

La segunda opción que nos ofrece nuestra herramienta es Rodear con un círculo datos no válidos. Esta opción nos permite comprobar si en una hoja o en un rango de celdas existen datos no válidos.

Como su nombre lo indica, al existir datos no válidos, mediante esta herramienta se dibujarán círculos rojos alrededor de aquellos datos que no cumplen con los criterios establecidos por el usuario, ayudando a éste en la búsqueda de posibles equivocaciones en la introducción de datos. Por ejemplo, si definimos como criterio que sólo aceptaremos números enteros entre 1 y 10, los números fuera de este rango serán marcados con un círculo rojo, como se muestra en la imagen a continuación:

Una vez que corrijamos los datos de acuerdo al criterio establecido, los círculos desaparecerán.

2.3 BORRAR CÍRCULOS DE VALIDACIÓN

La última opción, como su nombre nos indica nos permite borrar los círculos que se dibujan con la herramienta que mencionamos anteriormente sin necesariamente hacer cambios en las celdas cuyos valores no son válidos.

Así por ejemplo, en el caso anterior, aún cuando no corrijamos los valores que no se encuentran entre 1 y 10, mediante esta opción podremos hacer que los círculos rojos desaparezcan.

Tips

3.1 Fórmulas: Al momento de trabajar con la herramienta validación de datos, debemos tener claro que no sólo podemos ingresar valores en las casillas, sino que también podemos ingresar fórmulas. Así por ejemplo, podemos limitar el contenido de una celda a la suma de una determinada columna como se muestra en la imagen.

3.2 Lista Desplegable: Otro punto que debemos tener presente es que al ocupar la opción Lista en Validación de Datos, no podemos ingresar directamente datos cuyo origen se encuentra en otras hojas, pues Excel no nos dejará. Podríamos escribir manualmente la ubicación de los datos, sin embargo, existe una forma más fácil de hacer esto. Podemos dirigirnos al lugar en donde se encuentra la lista que queremos usar y asignar un nombre a esta lista. Por ejemplo, si tenemos una lista de nombres en la hoja 1, tomamos toda la lista y en el cuadro de nombres le asignamos una nueva denominación. En este caso designaremos a la lista como nombres como se muestra a continuación.

Luego, vamos a la hoja 2 y en Validación de Datos, en la opción Lista, utilizaremos como Origen nuestra lista de la hoja 1. Así, en Origen pondremos =nombres, que es la ubicación de la lista que queremos usar. De esta forma, tendremos nuestra lista desplegable en la hoja 2 a partir de la información de la hoja 1.

3.3 Orden para comprobación de validez de datos: Cuando deseamos utilizar la opción Rodear con círculo datos no válidos, debemos tener en cuenta que primero debemos tener escritos los datos cuya validez queremos comprobar, luego establecer los criterios de validez y finalmente utilizar esta función, ya que de seguir otro orden, no se podrá comprobar la validez de los datos. Esto debido a que si, por ejemplo, primero establecemos los criterios de validez y luego ingresamos los datos, no podremos ingresar valores que no sean válidos y por ende, no se marcarán círculos rojos en ninguno de los datos.

3.4 Protección de hojas y libro: Debemos tener presente que si deseamos proteger hojas o libros de Excel para que terceras personas no puedan modificarlas, debemos llevar a cabo esta acción después de haber definido los criterios de validación. Además, debemos tener presente que debemos dejar desbloqueadas las celdas que las personas podrán completar, de tal manera que puedan escribir en ellas. Por otro lado, si al momento de crear una lista desplegable no queremos que la lista de origen quede expuesta, podemos ocultar ésta una vez que hayamos configurado nuestros criterios de validación. Además, podemos poner contraseña al libro de tal manera de aumentar aún más la seguridad e impedir modificaciones por parte de terceros.

3.5 Compartir el libro: Si se desea compartir el libro con otras personas, debemos tener en cuenta que una vez que hemos accedido a esto, no podremos cambiar la configuración de los criterios de validez que hemos establecido a menos que dejemos de hacerlo. Por ende, debemos tener en cuenta que antes de compartir el libro debemos establecer la validación de datos que ocuparemos.

Aplicación al Mundo Profesional

La herramienta Validación de Datos tiene múltiples usos profesionales. El principal de ellos es la creación de formularios en los cuales terceras personas podrán completar ciertos datos. Esto es muy útil para el área contable y de finanzas, ya que nos permite por ejemplo crear formularios para presupuestos, informes de gastos, controles de caja, inventarios, etc.

Por otro lado, el uso de la Validación de Datos es de mucha utilidad a la hora de trabajar con grandes bases de datos en donde el ingreso de datos incorrectos o la posibilidad de cometer errores es alta. Así por ejemplo, al trabajar con una base de datos de clientes para estudiar una posible personalización de la publicidad en el área de marketing, las herramientas de validación y la posibilidad de marcar en círculos rojos los datos no válidos es muy útil.

Otra aplicación profesional dice relación con el trabajo con clientes, marcas o cualquier cosa a la cual se le asigne un folio, RUT o número que debe ser único. Haciendo uso de esta herramienta podemos evitar la duplicación de datos, utilizando de forma personalizada la Validación de Datos la cual nos limitará el ingreso de valores sólo una vez, como se mostrará en los ejemplos.

Finalmente, una de las tantas aplicaciones profesionales más útil de esta herramienta dice relación con el uso de listas desplegables. Ésta nos puede ayudar por ejemplo a seleccionar el RUT o nombre de un trabajador y, mediante el uso de fórmulas en el resto de las celdas, devolver información referente a esta persona. Esto es de mucha ayuda por ejemplo en la administración de compensaciones ya que podríamos utilizarla en la asignación o cálculos de bonos, comisiones, etc., siendo de mucha utilidad en el área de recursos humanos.

Aquí tenemos por ejemplo una imagen de un libro de control de caja de una empresa de servicios de entretención que se puede descargar aquí como Excel, en el cual mediante el uso de Validación de Datos se han establecido las boletas diarias por evento de acuerdo a los producto que ofrece la empresa. Este libro construido en base a listas desplegables ha acelerado el proceso de control de caja de la empresa, haciendo más eficientes los procesos de la misma.

Ejemplos

5.1 Formularios (Descargar Aquí ejemplo resuelto)(Descargar Aquí base del ejemplo)

La empresa LexParibus necesita que le entreguemos un formulario simple en donde al ingresar el RUT de su trabajador, podamos obtener el monto total a pagar por concepto de servicios. Para esto nos ha proporcionado una base de datos con la información de sus trabajadores. En esta encontramos el RUT del trabajador, su nombre, apellidos, área de trabajo, ventas mensuales, número de clientes nuevos reclutados y bono asignado según el área de trabajo. Para realizar esta labor, haremos uso de la herramienta Validación de Datos.

Primero que todo, debemos asignar un nombre a la lista que deseamos trabajar. En este caso, seleccionaremos la columna RUT de la hoja Datos y la designaremos DATOS, tal como se muestra en la imagen.

Una vez que hemos realizado esto, iremos a la hoja Remuneraciones y nos pondremos en la celda designada para ingresar el RUT del trabajador. Aquí estableceremos nuestra Lista desplegable. Para esto vamos a Validación de Datos y en la opción Permitir seleccionamos Lista. En origen pondremos =DATOS, debido a que ésta es la lista que deseamos se desplegué y que corresponde a los RUT de los trabajadores. Deberíamos obtener algo como a continuación:

Una vez hecho esto, tendremos nuestra lista desplegable, gracias a la cual podremos completar nuestro formulario.

Hemos configurado de tal forma el resto de celdas que al momento de seleccionar el RUT del trabajador, aparezca de forma inmediata el resto de los datos. Para esto hemos utilizado la función BUSCARV en donde hemos establecido como matriz de búsqueda la que encontramos en la hoja Datos, como criterio de búsqueda la lista desplegable que creamos con el RUT del trabajador y como valor a buscar el dato según se necesite (nombre, apellido, número de clientes reclutados, ventas y bono). De esta forma, nuestra fórmula debería quedar de la siguiente manera:

Además, hemos definido que el porcentaje sobre ventas de la remuneración corresponde a un 10% de las ventas y la comisión por cliente nuevo corresponde a $3.000 por cada uno. El total a pagar es la suma del porcentaje sobre ventas, la comisión por cliente y el bono.

De esta forma, gracias a nuestra herramienta Validación de Datos (Lista Desplegable), en combinación con otras funciones básicas hemos creado un formulario simple para LexParibus.

5.2 Datos Duplicados (Descargar Aquí ejemplo resuelto) (Descargar Aquí base del ejemplo)

Supongamos que nos encontramos trabajando con una base de datos de clientes de nuestra empresa para clasificar a los clientes de acuerdo al nivel de compras que éstos realizan de tal forma de segmentarlos. Sin embargo, creemos tener clientes duplicados ya que éstos han realizado más de una compra en la empresa. Por ende, queremos primero comprobar si efectivamente tenemos clientes duplicados y segundo, crear una lista de clientes en donde cada dato sea único de tal manera de poder clasificarlos.

Para comprobar si existen datos duplicados, hemos configurado los criterios de validación utilizando la herramienta Validación de Datos de tal forma que, una vez determinado el crietrio de validación, podamos marcar aquellos datos que se encuentren duplicados utilizando la opción Rodear con círculo datos no válidos.

Para configurar nuestros criterios hemos utilizado la opción Personalizada de la ventana Validación de Datos. Aquí hemos establecido la fórmula CONTAR.SI =1 en la cual definimos como primer término la matriz a contar y en el segundo, el criterio bajo el cual se debe contar. En nuestro caso, en el primer término tomamos la columna cod_cliente fija y en el segundo término ponemos la celda A2 sin fijar, de tal manera que se vaya desplazando a lo largo de la columna. Al haber datos duplicados, esta función nos arrojará un valor mayor a 1, obteniendo así un dato NO válido. De esta forma, deberíamos tener lo siguiente:

Una vez definido nuestro criterio, iremos a la herramienta Rodear con círculo datos no válidos y haremos clic. En nuestra base, obtendremos lo siguiente:

Así, podemos apreciar que efectivamente existen datos de clientes duplicados. Por ende, debemos crear una nueva lista en donde tengamos el monto total de compras del cliente, siendo necesario que el código de éste sea único para clasificarlos en clientes A o B.

Iremos a la hoja Clasificación de Clientes en donde tenemos los códigos de los clientes. Aquí utilizaremos la función SUMAR.SI para obtener el monto total de ventas que se han realizado a cada cliente. Incorporaremos como primer término de la función el rango a trabajar, que será la columna cod_cliente de nuestra hoja Lista Clientes, el criterio a sumar que será el código de cliente disponible en la primera celda de la hoja Clasificación de Clientes y el rango a sumar que será la columna Compras de la hoja Lista Clientes que trabajamos en un comienzo. De esta forma, obtendremos los valores de ventas totales que se han hecho a los clientes. Nuestra fórmula debería quedar como sigue:

Ahora bien, para asegurarnos de que no tengamos códigos repetidos, nuevamente utilizaremos la opción Personalizada de Validación de Datos, en donde ocuparemos la fórmula CONTAR.SI explicada anteriormente. Además, aprovecharemos de definir los mensajes de entrada y error de tal manera de evitar que alguna persona ingrese en esta hoja a un cliente más de una vez. De acuerdo a esto, obtendremos lo siguiente:

Una vez establecido nuestro criterio de validación, comprobamos que no hayan datos repetidos. De esta forma, al aplicar la opción Rodear con círculos datos no válidos, ningún dato debería ser marcado. Además nos aseguramos de que no se puedan ingresar datos no válidos, que en este caso corresponden a códigos de clientes duplicados. Esto se debe cumplir gracias a nuestro mensaje de error configurado. Es así como tendremos lo siguiente:

Una vez establecidos nuestros criterios de validación y comprobados éstos, podemos clasificar a nuestros clientes de acuerdo al monto de compra utilizando la función condicional SI . Así, aquellos clientes a los cuales se les haya vendido montos sobre $1.500.000 serán catalogados como clientes A y el resto como clientes B, obteniendo lo siguiente:

De esta forma, haciendo uso de la herramienta Validación de Datos y complementándola con ciertas funciones, hemos podido segmentar nuestra base en categorías de clientes, de tal manera de enfocar distintos esfuerzos promocionales en ellos.

Video Resumen