Excel

Guía de validación de datos de Excel

Excel Data Validation Guide

Fórmulas de validación | Listas desplegables dependientes | Fórmulas generales

Introducción

La validación de datos es una función en Excel que se usa para controlar lo que un usuario puede ingresar en una celda. Por ejemplo, puede utilizar la validación de datos para asegurarse de que un valor sea un número entre 1 y 6, asegurarse de que una fecha se produzca en los próximos 30 días o asegurarse de que una entrada de texto tenga menos de 25 caracteres.

La validación de datos puede simplemente mostrar un mensaje a un usuario indicándole lo que está permitido como se muestra a continuación:



Ejemplo de mensaje de validación de datos que se muestra cuando se selecciona la celda

La validación de datos también puede detener la entrada de usuario no válida. Por ejemplo, si un código de producto falla en la validación, puede mostrar un mensaje como este:

alerta de error de validación de datos ejemplo de código de producto no válido

Además, la validación de datos se puede utilizar para presentar al usuario una opción predefinida en un menú desplegable:

Ejemplo de menú desplegable de validación de datos

Esta puede ser una forma conveniente de proporcionar al usuario exactamente los valores que cumplen con los requisitos.

Controles de validación de datos

La validación de datos se implementa mediante reglas definidas en la interfaz de usuario de Excel en la pestaña Datos de la cinta.

mi pedido del 15 de junio de 2015

Controles de validación de datos en la pestaña de datos de la cinta

Limitación importante

Es importante comprender que la validación de datos se puede derrotar fácilmente. Si un usuario copia datos de una celda sin validación a una celda con validación de datos, la validación se destruye (o se reemplaza). La validación de datos es una buena forma de que los usuarios sepan lo que se permite o se espera, pero no es una forma infalible de garantizar la entrada.

Definición de reglas de validación de datos

La validación de datos se define en una ventana con 3 pestañas: Configuración, Mensaje de entrada y Alerta de error:

La ventana de validación de datos tiene tres pestañas principales

La pestaña de configuración es donde ingresa los criterios de validación. Hay una serie de reglas de validación integradas con varias opciones, o puede seleccionar Personalizado y usar su propia fórmula para validar la entrada como se muestra a continuación:

Ejemplo de pestaña de configuración de validación de datos

La pestaña Mensaje de entrada define un mensaje para mostrar cuando se selecciona una celda con reglas de validación. Este mensaje de entrada es completamente opcional. Si no se establece ningún mensaje de entrada, no aparece ningún mensaje cuando un usuario selecciona una celda con la validación de datos aplicada. El mensaje de entrada no tiene ningún efecto sobre lo que el usuario puede ingresar; simplemente muestra un mensaje para que el usuario sepa lo que está permitido o esperado.

Pestaña de configuración de validación de datos

La pestaña Alerta de error controla cómo se aplica la validación. Por ejemplo, cuando el estilo se establece en 'Detener', los datos no válidos activan una ventana con un mensaje y la entrada no está permitida.

Pestaña de alerta de error de validación de datos

El usuario ve un mensaje como este:

Ejemplo de mensaje de alerta de error de validación de datos

Cuando el estilo se establece en Información o Advertencia, se muestra un icono diferente con un mensaje personalizado, pero el usuario puede ignorar el mensaje e ingresar valores que no pasan la validación. La siguiente tabla resume el comportamiento de cada opción de alerta de error.

Estilo de alerta Comportamiento
Detener Evita que los usuarios ingresen datos no válidos en una celda. Los usuarios pueden volver a intentarlo, pero deben ingresar un valor que pase la validación de datos. La ventana Detener alerta tiene dos opciones: Reintentar y Cancelar.
Advertencia Advierte a los usuarios que los datos no son válidos. La advertencia no hace nada para detener los datos no válidos. La ventana de alerta de advertencia tiene tres opciones: Sí (para aceptar datos no válidos), No (para editar datos no válidos) y Cancelar (para eliminar los datos no válidos).
Información Informa a los usuarios que los datos no son válidos. Este mensaje no hace nada para detener los datos no válidos. La ventana de alerta de información tiene 2 opciones: Aceptar para aceptar datos no válidos y Cancelar para eliminarlos.

Opciones de validación de datos

Cuando se crea una regla de validación de datos, hay ocho opciones disponibles para validar la entrada del usuario:

Algún valor - no se realiza ninguna validación. Nota: si la validación de datos se aplicó previamente con un Mensaje de entrada establecido, el mensaje aún se mostrará cuando se seleccione la celda, incluso cuando se seleccione Cualquier valor.

Número entero - solo se permiten números enteros. Una vez que se selecciona la opción de número completo, otras opciones están disponibles para limitar aún más la entrada. Por ejemplo, puede requerir un número entero entre 1 y 10.

Decimal - funciona como la opción de números enteros, pero permite valores decimales. Por ejemplo, con la opción Decimal configurada para permitir valores entre 0 y 3, se permiten valores como .5, 2.5 y 3.1.

Lista - solo se permiten valores de una lista predefinida. Los valores se presentan al usuario como un control de menú desplegable. Los valores permitidos pueden codificarse directamente en la pestaña Configuración o especificarse como un rango en la hoja de trabajo.

Fecha - Solo se permiten fechas. Por ejemplo, puede requerir una fecha entre el 1 de enero de 2018 y el 31 de diciembre de 2021, o una fecha posterior al 1 de junio de 2018.

Hora - Solo se permiten tiempos. Por ejemplo, puede requerir una hora entre las 9:00 a. M. Y las 5:00 p. M., O solo permitir horas después de las 12:00 p. M.

Longitud del texto - valida la entrada según el número de caracteres o dígitos. Por ejemplo, podría requerir un código que contenga 5 dígitos.

Personalizado - valida la entrada del usuario mediante una fórmula personalizada. En otras palabras, puede escribir su propia fórmula para validar la entrada. Las fórmulas personalizadas amplían enormemente las opciones para la validación de datos. Por ejemplo, puede utilizar una fórmula para asegurarse de que un valor esté en mayúsculas, que un valor contenga 'xyz' o que una fecha sea un día de la semana en los próximos 45 días.

La pestaña de configuración también incluye dos casillas de verificación:

Ignorar en blanco - le dice a Excel que no valide las celdas que no contienen ningún valor. En la práctica, esta configuración parece afectar solo al comando 'circule datos no válidos'. Cuando está habilitado, las celdas en blanco no se rodean con un círculo incluso si no pasan la validación.

Aplicar estos cambios a otras celdas con la misma configuración - esta configuración actualizará la validación aplicada a otras celdas cuando coincida con la validación (original) de las celdas que se están editando.

cómo configurar la tabla dinámica

Nota: También puede seleccionar manualmente todas las celdas con validación de datos aplicada usando Ir a + Especial, como se explica a continuación.

Menú desplegable simple

Puede proporcionar un menú desplegable de opciones codificando los valores en el cuadro de configuración o seleccionando un rango en la hoja de trabajo. Por ejemplo, para restringir las entradas a las acciones 'COMPRAR', 'MANTENER' o 'VENDER', puede ingresar estos valores separados por comas como se muestra a continuación:

Menú desplegable de validación de datos con valores codificados

Cuando se aplica a una celda en la hoja de trabajo, el menú desplegable funciona así:

Valores codificados del menú desplegable de validación de datos en uso

Otra forma de proporcionar valores a un menú desplegable es utilizar una referencia de hoja de trabajo. Por ejemplo, con tamaños (es decir, pequeño, mediano, etc.) en el rango F3: F6, puede proporcionar este rango directamente dentro de la ventana de configuración de validación de datos:

Valores del menú desplegable de validación de datos con referencia de hoja de trabajo

Tenga en cuenta que el rango se ingresa como dirección absoluta para evitar que cambie a medida que la validación de datos se aplica a otras celdas.

Sugerencia: Haga clic en el icono de flecha pequeña en el extremo derecho del campo de origen para hacer una selección directamente en la hoja de trabajo para que no tenga que ingresar el rango manualmente.

También puedes usar rangos con nombre para especificar valores. Por ejemplo, con el rango con nombre llamado 'tamaños' para F3: F7, puede ingresar el nombre directamente en la ventana, comenzando con un signo igual:

Valores del menú desplegable de validación de datos con rango con nombre

Rangos con nombre son automáticamente absolutos, por lo que no cambiarán a medida que la validación de datos se aplique a diferentes celdas. Si los rangos con nombre son nuevos para usted, esta página tiene una buena descripción general y una serie de consejos relacionados .

Tú también puedes crear listas desplegables dependientes con una fórmula personalizada.

Sugerencia: si usa una tabla para valores desplegables, Excel seguirá expandiendo o contrayendo la tabla automáticamente cuando se agreguen o eliminen valores desplegables. En otras palabras, Excel mantendrá automáticamente el menú desplegable sincronizado con los valores de la tabla a medida que los valores se cambian, agregan o eliminan. Si es nuevo en las tablas de Excel, puede ver un demostración en este video en los atajos de tabla.

Validación de datos con una fórmula personalizada

Las fórmulas de validación de datos deben ser fórmulas lógicas que devuelvan VERDADERO cuando la entrada es válida y FALSO cuando la entrada no es válida. Por ejemplo, para permitir cualquier número como entrada en la celda A1, puede usar la función ISNUMBER en una fórmula como esta:

|_+_|

Si un usuario ingresa un valor como 10 en A1, ISNUMBER devuelve VERDADERO y la validación de datos se realiza correctamente. Si ingresan un valor como 'manzana' en A1, ISNUMBER devuelve FALSE y falla la validación de datos.

Para habilitar la validación de datos con una fórmula, seleccione 'Personalizado' en la pestaña de configuración, luego ingrese una fórmula en la barra de fórmulas que comience con un signo igual (=) como de costumbre.

Fórmulas de resolución de problemas

Excel ignora las fórmulas de validación de datos que devuelven errores. Si una fórmula no funciona y no puede averiguar por qué, configure fórmulas ficticias para asegurarse de que la fórmula funcione como espera. Las fórmulas ficticias son simplemente fórmulas de validación de datos ingresadas directamente en la hoja de trabajo para que pueda ver lo que devuelven fácilmente. La siguiente pantalla muestra un ejemplo:

Prueba de validación de datos con fórmulas ficticias

Una vez que la fórmula ficticia funcione como lo desea, simplemente cópiela y péguela en el área de la fórmula de validación de datos.

Si esta idea de fórmula ficticia le confunde, mira este video , que muestra cómo utilizar fórmulas ficticias para perfeccionar fórmulas de formato condicional. El concepto es exactamente el mismo.

Ejemplos de fórmulas de validación de datos

Las posibilidades de las fórmulas personalizadas de validación de datos son prácticamente ilimitadas. Aquí hay algunos ejemplos para inspirarte:

Para permitir solo 5 valores de caracteres que comiencen con 'z', puede usar:

|_+_|

Esta fórmula devuelve VERDADERO solo cuando un código tiene 5 dígitos y comienza con 'z'. Los dos valores encerrados en un círculo devuelven FALSO con esta fórmula.

Para permitir solo una fecha dentro de los 30 días de hoy:

|_+_|

Para permitir solo valores únicos:

|_+_|

Para permitir solo una dirección de correo electrónico

|_+_| Haga clic para obtener más ejemplos de fórmulas y explicaciones detalladas

Validación de datos para marcar con un círculo las entradas no válidas

Una vez aplicada la validación de datos, puede pedirle a Excel que encierre en un círculo los valores no válidos ingresados ​​previamente. En la pestaña Datos de la cinta, haga clic en Validación de datos y seleccione 'Círculo de datos no válidos':

Marque con un círculo los valores no válidos con validación de datos - menú

Por ejemplo, la siguiente pantalla muestra valores encerrados en un círculo que fallan la validación con esta fórmula personalizada:

|_+_|

Valores inválidos de validación de datos encerrados en un círculo en la hoja de trabajo

Encuentra celdas con validación de datos

Para encontrar celdas con validación de datos aplicada, puede usar el cuadro de diálogo Ir a> Especial. Escribe el atajo de teclado Control + G, luego haz clic en el botón Especial. Cuando aparezca el cuadro de diálogo, seleccione 'Validación de datos':

Ir al botón especial

cómo crear una tabla dinámica en Excel 2007

Seleccione la validación de datos con ir al diálogo especial

Copie la validación de datos de una celda a otra

Para copiar la validación de una celda a otras celdas. Copie las celdas que normalmente contienen la validación de datos que desea, luego use Pegado especial + Validación. Una vez que aparezca el cuadro de diálogo, escriba 'n' para seleccionar la validación o haga clic en validación con el mouse.

Uso de pegar especial para copiar la validación de datos

Nota: puede usar el método abreviado de teclado Control + Alt + V para invocar Pegado especial sin el mouse.

Borrar toda la validación de datos

Para borrar toda la validación de datos de un rango de celdas, haga la selección, luego haga clic en el botón Validación de datos en la pestaña Datos de la cinta. Luego haga clic en el botón 'Borrar todo':

Utilice el botón Borrar todo para eliminar la validación de datosimg / excel / 59 / excel-data-validation-guide-16.png

Para borrar toda la validación de datos de una hoja de trabajo, seleccione la hoja de trabajo completa y luego siga los mismos pasos anteriores.

Autor Dave Bruns


^