6.9. Validación de datos

6.9. Validación de datos 

En la ficha Datos, dentro del grupo Herramientas de datos se encuentra el comando  Validación de datos que proporciona un medio eficaz para establecer qué tipos de datos están permitidos y cuáles no, en una celda o en un rango.

De las tres pestañas disponibles, la más importante es la primera (Configuración), las otras dos simplemente configuran los mensajes de advertencia que el usuario verá en la pantalla cuando intente violar las normas de inserción de datos.

Los mensajes de entrada y de error sólo aparecen cuando los datos se escriben directamente en las celdas. No aparecen en los siguientes casos:

    • El usuario escribe datos en la celda mediante copia o relleno.
    • Una fórmula en la celda calcula un resultado que no es válido.
    • Una macro  especifica datos no válidos en la celda.

6.9.1. Configuración

La pestaña etiquetada como Configuración es la que ofrece distintas posibilidades en lo referente a tipos de datos disponibles en una celda.Cuadro de diálogo Validación de datos

La zona del cuadro de diálogo que  tiene el nombre de Criterio de validación, variará en función del contenido de Permitir. Algunas de las posibilidades son las siguientes: 

Selección de valores a permitir en la celda o rango

En la pestaña Configuración, Criterio de validación aparecen dos casillas de verificación:

  • Omitir blancos:
  • Aplicar estos cambios a otras celdas con la misma configuración: si esta seleccionada esta opción, se transmiten los cambios que se realicen a todas las celdas que tengan las mismas restricciones.

6.9.1.1. Cualquier valor

Este estado no impone ningún tipo de restricción. La celda puede contener cualquier tipo de datos.

6.9.1.2. Número entero y decimal.

La celda sólo puede aceptar como entradas válidas aquellas que definan números enteros. 

Puedes establecer restricciones a los números enteros y decimales

Escribir números enteros en las casillas Mínimo y Máximo.

En lugar de números, indicar valores contenidos en celdas, mediante el cuadro contraer cuadro de diálogo

6.9.1.3. Lista

Su interés estriba en el gran número de alternativas que ofrece a la hora de configurar la entrada de la celda.

Si quieres que sólo se puedan escribir ciertos valores dentro de una celda, la opción de lista es la adecuada, Ofreciendo los valores a través de una lista vinculada con la celda.

Para elegir una u otra posibilidad lo único que tienes que hacer es activar o desactivar la casilla de verificación Celda con lista desplegable.

Para utilizar una lista como validación,  a través del siguiente ejemplo:

Realiza una hoja de cálculo con el nombre Becas, para organizar las Becas de la Unión Europea, como la siguiente:

Ejemplo de diseño para ejercicio Becas

En el mismo libro hay otras dos hojas de cálculo, una con el nombre de Comunidades Autónomas y la otra  con el nombre de Países Unión Europea, en donde están relacionados por orden alfabético las Comunidades Autónomas (al rango le denominas comunidades) y los países de la Unión Europea (al rango  le denominas países).

Introduce en Procedencia y País Destino  una Lista validada, hay dos procedimientos: seleccionando el rango donde se encuentran los valores o escribiendo directamente la lista de valores.

6.9.1.3.1.  Seleccionando el rango donde se encuentran los valores

Los pasos son:

1 ► Selecciona el rango en la columna Procedencia donde vas a introducir alguna de las comunidades autónomas.

2 ► Selecciona el comando Validación del menú datos.

3 ► En la pestaña Configuración, en Permitir elegir Lista, en Origen:  selecciona el rango de la hoja CC.AA donde están la relación de comunidades autónomas.Ejemplo de validación Lista

A partir de este momento, solo podrás introducir en la columna Procedencia alguna de las comunidades autónomas de la lista.

Para introducir los países de la Unión Europea el procedimiento será similar.

Debes tener en cuenta que si los elementos de la lista varían en el origen, también variarán en la lista de validación.

Igualmente si los elementos  de la lista pueden aumentar en origen, debes tener la precaución  de indicar un rango más amplio, para que los nuevos elementos estén incluidos en el rango indicado en la validación.

 

6.9.1.3.2. Escribiendo directamente la lista de valores

Este procedimiento tiene la ventaja que los valores no dependen de un rango, el procedimiento  utilizando el ejemplo del punto anterior, sería:

1 ► Selecciona el rango en la columna Procedencia donde se van a introducir alguna de las comunidades autónomas.

2 ► Selecciona el comando Validación del menú datos.Cuadro de diálogo Validación de datos - Lista

3 ► En la pestaña Configuración, en Permitir elige Lista, en Origen escribir los valores  de la lista separados por ; (punto y coma).

 

 

 

 

 

6.9.1.4. Personalizada

De  lo que se trata es de escribir una fórmula que devuelva un valor verdadero o falso. En función de este criterio, Excel permitirá la entrada o no de datos. 

Cuadro de diálogo Validación de datos - Personalizada

Ejemplo 1: en el ejercicio de Becas UE el número de días mínimo de permanencia en el país debe ser de 30 días.

Para introducir una validación personalizada se debe tener en cuenta:

Nº Días = Fecha Fin – Fecha Inicio

La validación personalizada la debes introducir en el rango  Fecha Fin de tal modo que si intentas introducir una fecha que no de cómo resultado  mayor de 30 en el Nº días, Excel dará un mensaje de error y no permitirá la entrada del dato.Cuadro de diálogo Mensaje Advertencia

La Configuración del criterio de validación en el ejemplo será la siguiente:

  • Permitir: Personalizada
  • Fórmula: =H5>30

Ejemplo 2:   para evitar la introducción de valores duplicados  en el campo NIF del ejercicio Becas UE

En el ejemplo cada alumno solo puede ser beneficiario de una beca, en ocasiones  por error se introducen  valores que ya   se encuentran registrados. Para evitarlo en el rango correspondiente al encabezado NIF,  introduce una validación personalizada con los siguientes criterios:

  • Permitir: Personalizada
  • Fórmula: =CONTAR.SI($C$5:$C$30;C5)<2
Cuadro de diálogo Validación de datos - Evitar duplicados
 
 
Descarga ejercicio resuelto Becas
 

6.9.1.4.1.  Omitir Blancos

Si los valores permitidos se basan en un rango de celdas con un nombre definido y existe una celda en blanco en cualquier lugar del rango, la configuración de la casilla de verificación Omitir blancos permite escribir cualquier valor en la celda validada. Lo mismo puede decirse de las celdas a las que se haga referencia mediante fórmulas de validación: si una celda a la que se hace referencia está en blanco, la activación de la casilla de verificación Omitir blancos permite escribir cualquier valor en la celda validada.

Ejemplo Omitir Blancos en Lista

1 ► En una hoja con el nombre colores, escribe en cada celda diferentes colores y  deja en blanco alguna de las celdas del rango.Ejemplo para Omitir blancos

2 ► Asigna al rango un Nombre, en este ejemplo, color.

3 ► En otra hoja con el nombre Pedidos crea dos columnas una con los siguientes encabezados: Producto y Color.

4 ► En Color configura una Validación de datos que permita elegir los datos dentro de una lista con origen en el rango con el nombre Color.

 

 

Ejemplo activando casilla Omitir blancos

4►a◄ Activando la casilla Omitir blancos.

Si en la selección del color de la lista seleccionas la posición en blanco en la celda podrías introducir cualquier valor, por ejemplo, amarillo.

Ejemplo activando casilla Omitir blancos   Ejemplo activando casilla Omitir blancos

4►b◄ Desactivada la casilla Omitir blancos.

Si en la selección del color de la lista seleccionas la posición en blanco, no sería posible la introducción de otro valor.

Ejemplo desactivando casilla Omitir blancos

Ejemplo Omitir Blancos en permitir numero entero

1 ► En un rango escribes valores y dejas en blanco alguna de las celdas del rango.

2 ► Asigna al rango un Nombre, en este ejemplo, aver.Asignación de nombre a rango

3 ► En otro rango  crean dos columnas una con los siguientes encabezados: Máquina y Nº averías.

4 ► En Nº averías configura una Validación de datos que permita elegir los datos dentro de una lista con origen en el rango con el nombre aver.

 

Ejemplo  averías se configura una Validación de datos que permita elegir los datos dentro de una lista con origen en el rango con el nombre aver.

4►a◄ Activando la casilla Omitir blancos.

Si en la selección del color de la lista seleccionas la posición en blanco en la celda podrías introducir cualquier valor, por ejemplo, 9.

 Resultado activando Omitir blancos

4►b◄ Desactivada la casilla Omitir blancos.

Si en la selección de  la lista seleccionas la posición en blanco, no sería posible la introducción de otro valor.

 

6.9.2. Los mensajes entrante

En el cuadro de diálogo  Validación de datos hay dos pestañas que sirven para configurar los mensajes que Excel  mostrará cuando selecciones una celda o cuando se produzca un valor de entrada erróneo.

Pasos:

1 ► Selecciona  las celdas para las que desees que aparezca un mensaje.

2 ► En la ficha Datos, dentro del grupo Herramientas de datos haz clic en el comando  Validación de datos.

3 ► Elige la ficha Mensaje entrante.

Cuadro de diálogo Mensaje de entrada

4 ► Comprueba que la casilla de verificación Mostrar mensaje al seleccionar la celda está activada.

5 ► Para que aparezca un título en negrita en el mensaje, escríbelo en el cuadro Título.

6 ► Escribe el texto del mensaje en el cuadro Mensaje de entrada, con un máximo de 255 caracteres. Para comenzar una nueva línea en el mensaje, presiona INTRO.

7 ► Para que aparezca el mensaje, deberás introducir el texto en el cuadro Mensaje de entrada

 

 

 

6.9.3. Mensaje de error.

Pasos:

1 ► Selecciona  las celdas para las que  desees que aparezca un mensaje.

2 ► En la ficha Datos, dentro del grupo Herramientas de datos haz clic en el comando  Validación de datos.

3 ► Especifica las restricciones de datos.

4 ► Haz clic en la ficha Mensaje de error.

5 ► Comprueba que la casilla de verificación Mostrar mensaje de error si se introducen datos no válidos está activada.

6 ► En el cuadro Estilo, especificar el tipo de mensaje que quieras.

7 ► Para que aparezca un mensaje de información que tenga los botones Aceptar y Cuadro de diálogo Mensaje de errorCancelar y el botón predeterminado sea Aceptar, haz clic en Información.

8 ► Para que aparezca un mensaje de aviso con el texto “¿Desea continuar?”, seguido de los botones , No y Cancelar, siendo No el botón predeterminado, haz clic en Advertencia.

9 ► En ambos tipos de mensaje, los botones Aceptar y introducen datos no válidos en la celda. El botón No le lleva de nuevo a la celda para modificar los datos. El botón Cancelar restaura el valor anterior en la celda.

10 ► Para mostrar un mensaje de detención que tenga los botones Reintentar y Cancelar, siendo Reintentar el botón predeterminado, haz clic en Detener.

11 ► Si  deseas que aparezca un título en la barra de título del mensaje o en el globo del Ayudante de Office, si está activado, escribe el texto en el cuadro Título. Si dejas en blanco el cuadro Título, el valor predeterminado del título será Microsoft Excel.

12 ► Si quieres que aparezca tu propio texto en el mensaje, escríbelo en el cuadro Mensaje de error. Para comenzar una nueva línea en el mensaje, presiona INTRO. Si no se introduce ningún texto en el cuadro Mensaje de error, el mensaje presentará lo siguiente: “El valor introducido no es válido. Un usuario tiene valores restringidos que no pueden introducirse en esta celda”. Cuadro de diálogo Mensaje de error

{jcomments on}

Licencia Creative Commons